|
1
6
7 package org.manentia.kasai.user;
8
9 import com.koala.commons.log.Log;
10 import com.koala.commons.persistence.DBUtil;
11 import java.sql.*;
12 import java.util.ArrayList  ;
13 import java.util.logging.Level ;
14 import org.apache.commons.lang.StringUtils;
15 import org.apache.commons.lang.exception.ExceptionUtils;
16 import org.manentia.kasai.User;
17 import org.manentia.kasai.exceptions.AlreadyExistsException;
18 import org.manentia.kasai.exceptions.DataAccessException;
19 import org.manentia.kasai.exceptions.InvalidAttributesException;
20 import org.manentia.kasai.util.CacheUsers;
21 import org.manentia.kasai.util.Constants;
22
23
27 public class JDBCMySQLUserDAO implements UserDAO {
28
29
30 public JDBCMySQLUserDAO() {
31 }
32
33 public boolean checkOperative(String login, String operative, String object) {
34 Connection con = null;
35 String sql;
36 ResultSet rs = null;
37 boolean result = false;
38 try{
39 org.manentia.kasai.User u = this.read(login,true);
40 if (u == null){
41 return false;
42 }
43 if(u.getSuperUser()){
44 return true;
45 }
46 if (u.getBlocked()){
47 return false;
48 }
49
50 sql = "select distinct(ARO.id_operative) as operative from kasai_roles_operatives ARO, " +
51 "kasai_users_groups AUG,kasai_objects_groups_roles AOGR,kasai_groups AG where " +
52 "AOGR.id_object='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(object) + "' and AOGR.id_group=AUG.id_group " +
53 "and AUG.id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "' and ARO.id_role=AOGR.id_role and AG.id=AUG.id_group and AG.blocked=0";
54
55 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
56 rs = con.createStatement().executeQuery(sql);
57 while (rs.next() && (!result)){
58 result = (operative.startsWith(rs.getString("operative")));
59 }
60
61 if (!result){
62 sql = "select distinct(ARO.id_operative) as operative from kasai_roles_operatives ARO, " +
63 "kasai_objects_users_roles AOUR where AOUR.id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "' and " +
64 "AOUR.id_object='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(object) + "' and ARO.id_role=AOUR.id_role";
65 rs = con.createStatement().executeQuery(sql);
66 while (rs.next() && (!result)){
67 result = (operative.startsWith(rs.getString("operative")));
68 }
69 }
70
71 return result;
72 }catch (DataAccessException dae){
73 return false;
74 } catch (SQLException sqlE){
75 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(),"checkOperative",ExceptionUtils.getStackTrace(sqlE),Level.SEVERE);
76 return false;
77 }finally{
78 try{
79 rs.close();
80 }catch(Exception e){}
81 try{
82 con.close();
83 }catch(Exception e){}
84 }
85 }
86
87 public void create(String login, String firstName, String lastName, String email, boolean blocked, String description, boolean superUser) throws org.manentia.kasai.exceptions.InvalidAttributesException, org.manentia.kasai.exceptions.AlreadyExistsException, DataAccessException {
88 Connection con = null;
89 String sql;
90 User u = null;
91 try{
92 u = new User();
93 u.setBlocked(blocked);
94 u.setDescription(description);
95 u.setEmail(email);
96 u.setFirstName(firstName);
97 u.setLastName(lastName);
98 u.setLogin(login);
99 u.setSuperUser(superUser);
100 u.validate();
101
102 u = this.read(login,true);
103 if (u != null){
104 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(),"create","Login already exist",Level.WARNING);
105 throw new AlreadyExistsException(this.getClass().getName() + ".userAlreadyExist");
106 }
107 sql = "INSERT INTO kasai_users (id, first_name, last_name, email,blocked,description,super_user) VALUES (?,?,?,?,?,?,?)";
108 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
109 PreparedStatement stm = con.prepareStatement(sql);
110 stm.setString(1,login);
111 stm.setString(2, firstName);
112 stm.setString(3, lastName);
113 stm.setString(4, email);
114 if (blocked){
115 stm.setInt(5, 1);
116 }else{
117 stm.setInt(5, 0);
118 }
119 stm.setString(6, description);
120 if (superUser){
121 stm.setInt(7, 1);
122 }else{
123 stm.setInt(7, 0);
124 }
125 stm.executeUpdate();
126 }catch (SQLException sqle){
127 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "create", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
128 throw new DataAccessException(sqle);
129 }finally{
130 try{
131 con.close();
132 }catch(Exception e){}
133 }
134 }
135
136 public void create (String login, String firstName, String lastName,
137 String email, boolean blocked, String description) throws InvalidAttributesException, AlreadyExistsException, DataAccessException{
138
139 Connection con = null;
140 String sql;
141 User u = null;
142 try{
143 if (StringUtils.isEmpty(login)){
144 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(),"apply","Login was not specified",Level.WARNING);
145 throw new InvalidAttributesException(User.class.getName() + ".emptyLogin");
146 }
147
148 u = new User();
149 u.setBlocked(blocked);
150 u.setDescription(description);
151 u.setEmail(email);
152 u.setFirstName(firstName);
153 u.setLastName(lastName);
154 u.setLogin(login);
155 u.validate();
156
157 u = this.read(login,true);
158 if (u != null){
159 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(),"apply","Login already exist",Level.WARNING);
160 throw new AlreadyExistsException(this.getClass().getName() + ".userAlreadyExist");
161 }
162
163 sql = "INSERT INTO kasai_users (id, first_name, last_name, email,blocked,description) VALUES (?,?,?,?,?,?)";
164 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
165 PreparedStatement stm = con.prepareStatement(sql);
166 stm.setString(1,login);
167 stm.setString(2, firstName);
168 stm.setString(3, lastName);
169 stm.setString(4, email);
170 if (blocked){
171 stm.setInt(5, 1);
172 }else{
173 stm.setInt(5, 0);
174 }
175 stm.setString(6, description);
176 stm.executeUpdate();
177 }catch (SQLException sqle){
178 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "create", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
179 throw new DataAccessException(sqle);
180 }finally{
181 try{
182 con.close();
183 }catch(Exception e){}
184 }
185 }
186
187 public void delete(String login) throws DataAccessException {
188 Connection con = null;
189 String sql;
190 try{
191 if (StringUtils.isNotEmpty(login)){
192 sql = "DELETE FROM kasai_users WHERE id='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "'";
193 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
194 con.createStatement().executeUpdate(sql);
195 CacheUsers.addUser(login,null);
196 }
197 }catch (SQLException sqle){
198 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "delete", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
199 throw new DataAccessException(sqle);
200 }finally{
201 try{
202 con.close();
203 }catch(Exception e){}
204 }
205 }
206
207 public java.util.Collection list(String login, String firstName, String lastName, String email, int blocked, String description, String group) throws DataAccessException {
208 Connection con = null;
209 String sql;
210 ResultSet rs = null;
211 User u = null;
212 ArrayList users = new ArrayList ();
213 try{
214 sql = "SELECT AU.* FROM kasai_users AU";
215 if (StringUtils.isNotEmpty(group)){
216 sql += ", kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) +"'";
217 }
218 else{
219 sql += " WHERE AU.id <> ''";
220 }
221 if (StringUtils.isNotEmpty(login)){
222 sql += " AND AU.id LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) +"%'";
223 }
224 if (StringUtils.isNotEmpty(firstName)){
225 sql += " AND AU.first_name LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(firstName) +"%'";
226 }
227 if (StringUtils.isNotEmpty(lastName)){
228 sql += " AND AU.last_name LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(lastName) +"%'";
229 }
230 if (StringUtils.isNotEmpty(email)){
231 sql += " AND AU.email LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(email) +"%'";
232 }
233 if (blocked != -1){
234 sql += " AND AU.blocked = " + blocked;
235 }
236 if (StringUtils.isNotEmpty(description)){
237 sql += " AND AU.description LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(description) +"%'";
238 }
239 sql += " order by AU.last_name, AU.first_name, AU.id ";
240 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
241 rs = con.createStatement().executeQuery(sql);
242 while (rs.next()){
243 u = new User (rs);
244 users.add(u);
245 }
246 return users;
247 }catch (SQLException sqle){
248 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "list", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
249 throw new DataAccessException(sqle);
250 }finally{
251 try{
252 rs.close();
253 }catch(Exception e){}
254 try{
255 con.close();
256 }catch(Exception e){}
257 }
258 }
259
260 public org.manentia.kasai.User read(String login, boolean cache) throws DataAccessException {
261 Connection con = null;
262 String sql;
263 ResultSet rs = null;
264 User u = null;
265 try{
266 if (StringUtils.isNotEmpty(login)){
267 if (cache){
268 u = CacheUsers.getUser(login);
269 }
270 if (u == null){
271 sql = "SELECT * FROM kasai_users WHERE id='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "'";
272 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
273 rs = con.createStatement().executeQuery(sql);
274 if (rs.next() && (rs.getString ("id").equals (login))){
275 u = new User (rs);
276 }
277 }
278 }
279 return u;
280 }catch (SQLException sqle){
281 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "read", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
282 throw new DataAccessException(sqle);
283 }finally{
284 try{
285 rs.close();
286 }catch(Exception e){}
287 try{
288 con.close();
289 }catch(Exception e){}
290 }
291 }
292
293 public void update(String login, String firstName, String lastName, String email, boolean blocked, String description, boolean superUser) throws org.manentia.kasai.exceptions.InvalidAttributesException, DataAccessException {
294 Connection con = null;
295 String sql;
296 User u = null;
297 try{
298 u = new User();
299 u.setBlocked(blocked);
300 u.setDescription(description);
301 u.setEmail(email);
302 u.setFirstName(firstName);
303 u.setLastName(lastName);
304 u.setLogin(login);
305 u.setSuperUser(superUser);
306 u.validate();
307
308 sql = "UPDATE kasai_users set first_name=?, last_name=?, email=?, blocked=?, description=?, super_user=? where id=?";
309 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
310 PreparedStatement stm = con.prepareStatement(sql);
311
312 stm.setString(1, firstName);
313 stm.setString(2, lastName);
314 stm.setString(3, email);
315 if (blocked){
316 stm.setInt(4, 1);
317 }else{
318 stm.setInt(4, 0);
319 }
320 stm.setString(5, description);
321 if (superUser){
322 stm.setInt(6, 1);
323 }else{
324 stm.setInt(6, 0);
325 }
326 stm.setString(7,login);
327 stm.executeUpdate();
328 u = this.read(login,false);
329 CacheUsers.addUser(login,u);
330
331 }catch (SQLException sqle){
332 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
333 throw new DataAccessException(sqle);
334 }finally{
335 try{
336 con.close();
337 }catch(Exception e){}
338 }
339 }
340
341 public void update(String login, String firstName, String lastName,
342 String email, boolean blocked, String description) throws InvalidAttributesException, DataAccessException{
343
344 Connection con = null;
345 String sql;
346 User u = null;
347 try{
348 u = new User();
349 u.setBlocked(blocked);
350 u.setDescription(description);
351 u.setEmail(email);
352 u.setFirstName(firstName);
353 u.setLastName(lastName);
354 u.setLogin(login);
355 u.validate();
356
357 sql = "UPDATE kasai_users set first_name=?, last_name=?, email=?, blocked=?, description=? where id=?";
358 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
359 PreparedStatement stm = con.prepareStatement(sql);
360
361 stm.setString(1, firstName);
362 stm.setString(2, lastName);
363 stm.setString(3, email);
364 if (blocked){
365 stm.setInt(4, 1);
366 }else{
367 stm.setInt(4, 0);
368 }
369 stm.setString(5, description);
370 stm.setString(6, login);
371 stm.executeUpdate();
372
373 u = this.read(login,false);
374 CacheUsers.addUser(login,u);
375
376 }catch (SQLException sqle){
377 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
378 throw new DataAccessException(sqle);
379 }finally{
380 try{
381 con.close();
382 }catch(Exception e){}
383 }
384 }
385 }
386 |
Popular Tags |
Java API By Example, From Geeks To Geeks.