1
2
3
4
5
6
7
8
9 package com.buckosoft.BSAccountMan.db.jdbc;
10
11 import java.sql.ResultSet;
12 import java.sql.SQLException;
13 import java.sql.Types;
14
15 import javax.sql.DataSource;
16
17 import org.springframework.dao.DataAccessException;
18 import org.springframework.jdbc.core.SqlParameter;
19 import org.springframework.jdbc.object.MappingSqlQuery;
20 import org.springframework.jdbc.object.SqlFunction;
21 import org.springframework.jdbc.object.SqlUpdate;
22
23 import com.buckosoft.BSAccount.domain.BSAccount;
24 import com.buckosoft.BSAccountMan.db.AccountDao;
25 import com.buckosoft.BSAccountMan.db.DbType;
26
27
28
29
30
31 public class AccountDaoJdbc extends JdbcBaseClass implements AccountDao {
32
33 public BSAccount getAccount(String username) throws DataAccessException {
34 if (sql_getAccountQUERYr == null)
35 sql_getAccountQUERYr = new AccountsQuery(ds, username);
36 if (DEBUG)
37 logger.info("Getting account '" + username + "'");
38
39 return (BSAccount)sql_getAccountQUERYr.findObject(username);
40 }
41 private AccountsQuery sql_getAccountQUERYr;
42
43 public BSAccount getAccount(int userid) throws DataAccessException {
44 if (sql_getAccountQUERYint == null)
45 sql_getAccountQUERYint = new AccountsQuery(ds, userid);
46 if (DEBUG)
47 logger.info("Getting account by id'" + userid + "'");
48
49 return (BSAccount)sql_getAccountQUERYint.findObject(new Object[] {new Integer(userid)});
50 }
51 private AccountsQuery sql_getAccountQUERYint;
52
53
54
55
56
57 @Override
58 public BSAccount getAccountByToken(int token) {
59 if (sql_getAccountQUERYtoken == null)
60 sql_getAccountQUERYtoken = new AccountsQuery(ds, token, token);
61 if (DEBUG)
62 logger.info("Getting account by token '" + token + "'");
63
64 return (BSAccount)sql_getAccountQUERYtoken.findObject(new Object[] {new Integer(token)});
65 }
66 private AccountsQuery sql_getAccountQUERYtoken;
67
68 public BSAccount getAccountByEmail(String email) throws DataAccessException {
69 if (sql_getAccountQUERYemail == null)
70 sql_getAccountQUERYemail = new AccountsQuery(ds, 1, email);
71 if (DEBUG)
72 logger.info("Getting account '" + email + "'");
73
74 return (BSAccount)sql_getAccountQUERYemail.findObject(email);
75 }
76 private AccountsQuery sql_getAccountQUERYemail;
77
78 private AccountsQuery sql_getAccountQUERYup;
79 public BSAccount getAccount(String username, String password) throws DataAccessException {
80 if (sql_getAccountQUERYup == null)
81 sql_getAccountQUERYup = new AccountsQuery(ds, username, password);
82 if (DEBUG)
83 logger.info("Getting account '" + username + "'");
84
85 return (BSAccount)sql_getAccountQUERYup.findObject(new Object[] {username, password});
86 }
87
88 public void insertAccount(BSAccount account) throws DataAccessException {
89 AccountInsert ai = new AccountInsert(ds);
90 ai.insert(account);
91 }
92
93 public void updateAccount(BSAccount account) throws DataAccessException {
94 AccountUpdate au = new AccountUpdate(ds);
95 au.update(account);
96 }
97
98 public void updateAccountPassword(BSAccount account, String newPassword) throws DataAccessException {
99 AccountPasswordUpdate apu = new AccountPasswordUpdate(ds);
100 if (DEBUG)
101 logger.info("UpdateAccountPassword: " + account.getUsername() + " : " + newPassword);
102 apu.update(account.getUsername(), newPassword);
103 }
104
105 @Override
106 public int getUserCount() {
107 if (sql_getAccountCountCOUNTr == null)
108 sql_getAccountCountCOUNTr = new AccountCount(ds);
109 if (DEBUG)
110 logger.info("getFilterCount:");
111 return(sql_getAccountCountCOUNTr.run());
112 }
113 private AccountCount sql_getAccountCountCOUNTr;
114
115
116
117 class AccountsQuery extends MappingSqlQuery<Object> {
118
119 AccountsQuery(DataSource ds) {
120 super(ds, (dbType == DbType.MySQL)
121 ? "SELECT * from accounts"
122 : "SELECT * from \"accounts\"");
123 compile();
124 }
125
126 AccountsQuery(DataSource ds, String userid) {
127 super(ds, (dbType == DbType.MySQL)
128 ? "SELECT * from accounts where username = ?"
129 : "SELECT * from \"accounts\" where \"username\" = ?");
130 declareParameter(new SqlParameter(Types.VARCHAR));
131 compile();
132 }
133
134 AccountsQuery(DataSource ds, int userid) {
135 super(ds, (dbType == DbType.MySQL)
136 ? "SELECT * from accounts where userid = ?"
137 : "SELECT * from \"accounts\" where \"userid\" = ?");
138 declareParameter(new SqlParameter(Types.INTEGER));
139 compile();
140 }
141
142 AccountsQuery(DataSource ds, int token, int unused) {
143 super(ds, (dbType == DbType.MySQL)
144 ? "SELECT * from accounts where registerToken = ?"
145 : "SELECT * from \"accounts\" where \"registerToken\" = ?");
146 declareParameter(new SqlParameter(Types.INTEGER));
147 compile();
148 }
149
150 AccountsQuery(DataSource ds, String userid, String password) {
151 super(ds, (dbType == DbType.MySQL)
152 ? "SELECT * from accounts where username = ? AND password = PASSWORD(?)"
153 : "SELECT * from \"accounts\" where \"username\" = ? AND \"password\" = ?");
154 declareParameter(new SqlParameter(Types.VARCHAR));
155 declareParameter(new SqlParameter(Types.CHAR));
156 compile();
157 }
158
159 AccountsQuery(DataSource ds, int unused, String userid) {
160 super(ds, (dbType == DbType.MySQL)
161 ? "SELECT * from accounts where email = ?"
162 : "SELECT * from \"accounts\" where \"email\" = ?");
163 declareParameter(new SqlParameter(Types.VARCHAR));
164 compile();
165 }
166
167 protected Object mapRow(ResultSet rs, int rowNum) throws SQLException {
168 BSAccount a = new BSAccount();
169 a.setUsername(rs.getString("username"));
170 a.setUserId(rs.getInt("userid"));
171
172 a.setEmail(rs.getString("email"));
173 a.setWebPage(rs.getString("webPage"));
174 a.setFirstName(rs.getString("firstName"));
175 a.setLastName(rs.getString("lastName"));
176 a.setStatus(rs.getString("status"));
177 a.setAddress1(rs.getString("addr1"));
178 a.setAddress2(rs.getString("addr2"));
179 a.setCity(rs.getString("city"));
180 a.setState(rs.getString("state"));
181 a.setZip(rs.getString("zip"));
182 a.setCountry(rs.getString("country"));
183 a.setPhone(rs.getString("phone"));
184 a.setRegisterToken(rs.getInt("registerToken"));
185 a.setLastAccess(rs.getTimestamp("lastAccess"));
186 a.setRegisterDate(rs.getTimestamp("registerDate"));
187 String s = rs.getString("password");
188 a.setHasPassword(false);
189 if (s != null && s.length() > 0)
190 a.setHasPassword(true);
191 return a;
192 }
193 }
194
195
196
197 protected class AccountInsert extends SqlUpdate {
198
199
200
201
202
203 protected AccountInsert(DataSource ds) {
204 super(ds, (dbType == DbType.MySQL)
205 ? "INSERT INTO accounts VALUES(?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?)"
206 : "INSERT INTO \"accounts\" VALUES(?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?, ?,?,?)");
207 declareParameter(new SqlParameter(Types.VARCHAR));
208 declareParameter(new SqlParameter(Types.INTEGER));
209 declareParameter(new SqlParameter(Types.VARCHAR));
210 declareParameter(new SqlParameter(Types.VARCHAR));
211 declareParameter(new SqlParameter(Types.VARCHAR));
212 declareParameter(new SqlParameter(Types.VARCHAR));
213 declareParameter(new SqlParameter(Types.VARCHAR));
214 declareParameter(new SqlParameter(Types.VARCHAR));
215 declareParameter(new SqlParameter(Types.VARCHAR));
216 declareParameter(new SqlParameter(Types.VARCHAR));
217 declareParameter(new SqlParameter(Types.VARCHAR));
218 declareParameter(new SqlParameter(Types.VARCHAR));
219 declareParameter(new SqlParameter(Types.VARCHAR));
220 declareParameter(new SqlParameter(Types.VARCHAR));
221 declareParameter(new SqlParameter(Types.VARCHAR));
222 declareParameter(new SqlParameter(Types.INTEGER));
223 declareParameter(new SqlParameter(Types.TIMESTAMP));
224 declareParameter(new SqlParameter(Types.TIMESTAMP));
225 compile();
226 }
227
228 protected void insert(BSAccount a) {
229 Object[] objs = new Object[] {
230 a.getUsername(), null, null, a.getEmail(), a.getWebPage(),
231 a.getFirstName(), a.getLastName(), a.getStatus(),
232 a.getAddress1(), a.getAddress2(),
233 a.getCity(), a.getState(), a.getZip(), a.getCountry(),
234 a.getPhone(), new Integer(a.getRegisterToken()),
235 a.getLastAccess(), a.getRegisterDate(),
236 };
237 super.update(objs);
238
239 }
240 }
241
242
243
244
245
246 protected class AccountUpdate extends SqlUpdate {
247
248
249
250
251
252 protected AccountUpdate(DataSource ds) {
253 super(ds, (dbType == DbType.MySQL)
254 ? "UPDATE accounts SET email=?, webPage=?, firstName=?, lastName=?, status=?, addr1=?, addr2=?, city=?, state=?, zip=?,country=?, phone=?, registerToken=?, lastAccess=? WHERE userid = ? LIMIT 1"
255 : "UPDATE \"accounts\" SET email=?, webPage=?, firstName=?, lastName=?, status=?, addr1=?, addr2=?, city=?, state=?, zip=?,country=?, phone=?, registerToken=?, lastAccess=? WHERE userid = ? LIMIT 1");
256 declareParameter(new SqlParameter(Types.VARCHAR));
257 declareParameter(new SqlParameter(Types.VARCHAR));
258 declareParameter(new SqlParameter(Types.VARCHAR));
259 declareParameter(new SqlParameter(Types.VARCHAR));
260 declareParameter(new SqlParameter(Types.VARCHAR));
261 declareParameter(new SqlParameter(Types.VARCHAR));
262 declareParameter(new SqlParameter(Types.VARCHAR));
263 declareParameter(new SqlParameter(Types.VARCHAR));
264 declareParameter(new SqlParameter(Types.VARCHAR));
265 declareParameter(new SqlParameter(Types.VARCHAR));
266 declareParameter(new SqlParameter(Types.VARCHAR));
267 declareParameter(new SqlParameter(Types.VARCHAR));
268 declareParameter(new SqlParameter(Types.INTEGER));
269 declareParameter(new SqlParameter(Types.TIMESTAMP));
270 declareParameter(new SqlParameter(Types.INTEGER));
271 compile();
272 }
273
274
275
276
277
278
279 protected int update(BSAccount a) {
280 return this.update(new Object[] {
281 a.getEmail(), a.getWebPage(),
282 a.getFirstName(), a.getLastName(), a.getStatus(),
283 a.getAddress1(), a.getAddress2(),
284 a.getCity(), a.getState(), a.getZip(), a.getCountry(),
285 a.getPhone(), new Integer(a.getRegisterToken()), null,
286 a.getUserId()});
287 }
288 }
289
290
291
292
293 protected class AccountPasswordUpdate extends SqlUpdate {
294
295
296
297
298
299 protected AccountPasswordUpdate(DataSource ds) {
300 super(ds, (dbType == DbType.MySQL)
301 ? "UPDATE accounts SET password=PASSWORD(?), lastAccess=? WHERE username = ? LIMIT 1"
302 : "UPDATE \"accounts\" SET password=?, lastAccess=? WHERE username = ? LIMIT 1");
303 declareParameter(new SqlParameter(Types.CHAR));
304 declareParameter(new SqlParameter(Types.TIMESTAMP));
305 declareParameter(new SqlParameter(Types.VARCHAR));
306 compile();
307 }
308
309
310
311
312
313
314
315 public int update(String username, String password ) {
316 return this.update(new Object[] {
317 password, null,
318 username});
319 }
320 }
321 protected class AccountCount extends SqlFunction<Object> {
322 AccountCount(DataSource ds) {
323 super(ds, "SELECT COUNT(*) from accounts");
324 compile();
325 }
326 }
327
328
329
330
331 public void resetTable() {}
332
333 }