|
1
25
26 package org.snipsnap.snip.storage;
27
28 import org.radeox.util.logging.Logger;
29 import org.snipsnap.app.Application;
30 import org.snipsnap.jdbc.*;
31 import org.snipsnap.user.Roles;
32 import org.snipsnap.user.User;
33 import org.snipsnap.util.ConnectionManager;
34 import org.snipsnap.util.log.SQLLogger;
35
36 import javax.sql.DataSource  ;
37 import java.sql.*;
38 import java.util.ArrayList ;
39 import java.util.List ;
40
41
47
48 public class JDBCUserStorage implements UserStorage {
49 private FinderFactory finders;
50 private DataSource ds;
51
52 public JDBCUserStorage(DataSource ds) {
53 this.ds = ds;
54 finders = new FinderFactory(ds, "SELECT applicationOid, login, passwd, email, status, roles, " +
55 " cTime, mTime, lastLogin, lastAccess, lastLogout " +
56 " FROM SnipUser ");
57 }
58
59 public static void createStorage() {
60 DataSource datasource = ConnectionManager.getDataSource();
61 System.err.println("JDBCUserStorage: dropping SnipUser SQL table");
62 JDBCTemplate droptemplate = new JDBCTemplate(datasource);
63 try {
64 droptemplate.update("DROP TABLE SnipUser");
65 } catch (Exception e) {
66 SQLLogger.warn("JDBCUserStorage: unable to drop table (new install?)", e);
67 }
68
69 System.err.println("JDBCUserStorage: creating SnipUser SQL table");
70 JDBCTemplate template = new JDBCTemplate(datasource);
71 template.update(
72 " CREATE TABLE SnipUser ( " +
73 " login VARCHAR(100) NOT NULL, " +
74 " applicationOid VARCHAR(100) NOT NULL," +
75 " cTime TIMESTAMP, " +
76 " mTime TIMESTAMP, " +
77 " lastLogin TIMESTAMP, " +
78 " lastAccess TIMESTAMP, " +
79 " lastLogout TIMESTAMP, " +
80 " passwd VARCHAR(100), " +
81 " email VARCHAR(100)," +
82 " status VARCHAR(50), " +
83 " roles VARCHAR(200) )");
84 return;
85 }
86
87 public void storageStore(final User user) {
88 JDBCTemplate template = new JDBCTemplate(ds);
89 template.update(
90 "UPDATE SnipUser SET login=?, passwd=?, email=?, status=?, roles=?, " +
91 " cTime=?, mTime=?, lastLogin=?, lastAccess=?, lastLogout=? " +
92 " WHERE login=? AND applicationOid=?",
93 new PreparedStatementSetter() {
94 public void setValues(PreparedStatement ps) throws SQLException {
95 ps.setString(1, user.getLogin());
96 ps.setString(2, user.getPasswd());
97 ps.setString(3, user.getEmail());
98 ps.setString(4, user.getStatus());
99 ps.setString(5, user.getRoles().toString());
100 ps.setTimestamp(6, user.getCTime());
101 ps.setTimestamp(7, user.getMTime());
102 ps.setTimestamp(8, user.getLastLogin());
103 ps.setTimestamp(9, user.getLastAccess());
104 ps.setTimestamp(10, user.getLastLogout());
105 ps.setString(11, user.getLogin());
106 ps.setString(12, user.getApplication());
107 }
108 });
109 return;
110 }
111
112 public User storageCreate(String login, String passwd, String email) {
113 String applicationOid = (String ) Application.get().getObject(Application.OID);
114
115 final User user = new User(login, passwd, email);
116 final Timestamp cTime = new Timestamp(new java.util.Date ().getTime());
117 user.setCTime(cTime);
118 user.setMTime(cTime);
119 user.setLastLogin(cTime);
120 user.setLastAccess(cTime);
121 user.setLastLogout(cTime);
122 user.setApplication(applicationOid);
123
124 JDBCTemplate template = new JDBCTemplate(ds);
125 template.update(
126 "INSERT INTO SnipUser " +
127 " (login, passwd, email, status, roles, " +
128 " cTime, mTime, lastLogin, lastAccess, lastLogout, applicationOid) " +
129 " VALUES (?,?,?,?,?,?,?,?,?,?,?)",
130 new PreparedStatementSetter() {
131 public void setValues(PreparedStatement ps) throws SQLException {
132 ps.setString(1, user.getLogin());
133 ps.setString(2, user.getPasswd());
134 ps.setString(3, user.getEmail());
135 ps.setString(4, "");
136 ps.setString(5, "");
137 ps.setTimestamp(6, cTime);
138 ps.setTimestamp(7, cTime);
139 ps.setTimestamp(8, cTime);
140 ps.setTimestamp(9, cTime);
141 ps.setTimestamp(10, cTime);
142 ps.setString(11, user.getApplication());
143 }
144 });
145 return user;
146 }
147
148 public void storageRemove(final User user) {
149 JDBCTemplate template = new JDBCTemplate(ds);
150 template.update(
151 "DELETE FROM SnipUser WHERE login=? AND applicationOid=?",
152 new PreparedStatementSetter() {
153 public void setValues(PreparedStatement ps) throws SQLException {
154 ps.setString(1, user.getLogin());
155 ps.setString(2, user.getApplication());
156 }
157 });
158 return;
159 }
160
161 public int storageUserCount() {
162 final String applicationOid = (String ) Application.get().getObject(Application.OID);
163 final IntHolder holder = new IntHolder(-1);
164
165 JDBCTemplate template = new JDBCTemplate(ds);
166 template.query(
167 "SELECT count(*) FROM SnipUser WHERE applicationOid=?",
168 new RowCallbackHandler() {
169 public void processRow(ResultSet rs) throws SQLException {
170 holder.setValue(rs.getInt(1));
171 }
172 },
173 new PreparedStatementSetter() {
174 public void setValues(PreparedStatement ps) throws SQLException {
175 ps.setString(1, applicationOid);
176 }
177 });
178 return holder.getValue();
179 }
180
181 public User storageLoad(final String login) {
182 final String applicationOid = (String ) Application.get().getObject(Application.OID);
184 final List users = new ArrayList ();
185 JDBCTemplate template = new JDBCTemplate(ds);
186 template.query(
187 "SELECT applicationOid, login, passwd, email, status, roles, cTime, mTime, " +
188 " lastLogin, lastAccess, lastLogout " +
189 " FROM SnipUser " +
190 " WHERE login=? AND applicationOid=?",
191 new RowCallbackHandler() {
192 public void processRow(ResultSet rs) throws SQLException {
193 users.add(createUser(rs));
194 }
195 },
196 new PreparedStatementSetter() {
197 public void setValues(PreparedStatement ps) throws SQLException {
198 ps.setString(1, login);
199 ps.setString(2, applicationOid);
200 }
201 });
202 return (users.size() > 0 ? (User) users.get(0) : null);
203 }
204
205 public List storageAll() {
206 String applicationOid = (String ) Application.get().getObject(Application.OID);
207
208 Finder finder = finders.getFinder(" WHERE applicationOid=? ORDER BY login ")
209 .setString(1, applicationOid);
210 List list = createObjects(finder.execute());
211 finder.close();
212 return list;
213 }
214
215
216 public List createObjects(ResultSet result) {
217 return createObjects(result, Integer.MAX_VALUE);
218 }
219
220 public List createObjects(ResultSet result, int size) {
221 List resultList = new ArrayList ();
222 if (null != result) {
223 try {
224 User user = null;
225 while (result.next() && size-- > 0) {
226 user = createUser(result);
227 resultList.add(user);
228 }
229 } catch (SQLException e) {
230 Logger.warn("Finder: SQL Error", e);
231 }
232 }
233 return resultList;
234 }
235
236 public User createUser(ResultSet result) throws SQLException {
237 String applicationOid = result.getString("applicationOid");
238 String login = result.getString("login");
239 String passwd = result.getString("passwd");
240 String email = result.getString("email");
241 Timestamp cTime = result.getTimestamp("cTime");
242 Timestamp mTime = result.getTimestamp("mTime");
243 Timestamp lastLogin = result.getTimestamp("lastLogin");
244 Timestamp lastAccess = result.getTimestamp("lastAccess");
245 Timestamp lastLogout = result.getTimestamp("lastLogout");
246 String status = result.getString("status");
247 User user = new User(login, passwd, email);
248 user.setApplication(applicationOid);
249 user.setStatus(status);
250 user.setRoles(new Roles(result.getString("roles")));
251 user.setCTime(cTime);
252 user.setMTime(mTime);
253 user.setLastLogin(lastLogin);
254 user.setLastAccess(lastAccess);
255 user.setLastLogout(lastLogout);
256 return user;
257 }
258 }
259 |
Popular Tags |
Java API By Example, From Geeks To Geeks.