|
1
6
7 package org.manentia.kasai.role;
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.Arrays ;
14 import java.util.Collection ;
15 import java.util.logging.Level ;
16 import org.apache.commons.lang.StringUtils;
17 import org.apache.commons.lang.exception.ExceptionUtils;
18 import org.manentia.kasai.Operative;
19 import org.manentia.kasai.Role;
20 import org.manentia.kasai.exceptions.AlreadyExistsException;
21 import org.manentia.kasai.exceptions.DataAccessException;
22 import org.manentia.kasai.exceptions.DoesntExistsException;
23 import org.manentia.kasai.exceptions.InvalidAttributesException;
24 import org.manentia.kasai.operative.OperativeHandler;
25 import org.manentia.kasai.util.Constants;
26
27
31 public class JDBCMySQLRoleDAO implements RoleDAO {
32
33
34 public JDBCMySQLRoleDAO() {
35 }
36
37 public void addOperativeToRole(
38 final String idOperative,
39 final int role
40 ) throws DoesntExistsException, DataAccessException {
41
42 Connection con = null;
43
44 if (this.read(role) == null) {
45
46 Log.getInstance(Constants.PROPERTY_FILE).write(
47 this.getClass().getName(), "apply", "Role doesn't exist", Level.WARNING
48 );
49 throw new DoesntExistsException(Role.class.getName() + ".roleDoesntExist");
50 }
51 if (StringUtils.isEmpty(idOperative) || (OperativeHandler.getInstance().list(idOperative).size() == 0)) {
52
53 Log.getInstance(Constants.PROPERTY_FILE).write(
54 this.getClass().getName(), "apply", "Operative doesn't exist", Level.WARNING
55 );
56 throw new DoesntExistsException(Operative.class.getName() + ".operativeDoesntExist");
57 }
58
59 if (this.listOperativesFromRole(role, idOperative).size() == 0) {
60
61 try {
62
63 String sql = "insert into kasai_roles_operatives (id_role,id_operative) values (" + role + ",'" +
64 org.apache.commons.lang.StringEscapeUtils.escapeSql(idOperative) + "')";
65 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
66 con.createStatement().executeUpdate(sql);
67 }catch (SQLException sqle){
68 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "addOperativeToRole", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
69 throw new DataAccessException(sqle);
70 } finally {
71
72 try {
73
74 con.close();
75 } catch (Exception e) {
76
77 }
78 }
79 }
80 }
81
82 public int create(String name, String description, String [] operatives) throws InvalidAttributesException, AlreadyExistsException, DoesntExistsException,DataAccessException{
83
84 Connection con = null;
85 String sql;
86 Role r = null;
87 ResultSet rs = null;
88 int idRole = -1;
89 boolean runSuccessfully = true;
90 try{
91 r = new Role();
92 r.setDescription(description);
93 r.setName(name);
94 r.validate();
95
96 if (this.list(name, true).size() > 0){
97 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(),"apply","Role name already exist",Level.WARNING);
98 throw new AlreadyExistsException(this.getClass().getName() + ".roleAlreadyExist");
99 }
100 sql = "INSERT INTO kasai_roles (name, description) VALUES (?, ?)";
101 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
102 con.setAutoCommit(false);
103 PreparedStatement stm = con.prepareStatement(sql);
104 stm.setString(1, name);
105 stm.setString(2, description);
106 stm.executeUpdate();
107
108 rs = stm.getGeneratedKeys();
109 if (rs.next()){
110 idRole = rs.getInt(1);
111 }
112
113 String idOperative = null;
114 if (operatives != null){
115 for (int i=0; i<operatives.length; i++) {
116 idOperative = operatives[i];
117
118 if (StringUtils.isEmpty(idOperative) || (OperativeHandler.getInstance().list(idOperative).size() == 0)) {
119 Log.getInstance(Constants.PROPERTY_FILE).write(
120 this.getClass().getName(), "apply", "La operativa no existe", Level.WARNING
121 );
122 throw new DoesntExistsException(Operative.class.getName() + ".operativeDoesntExist");
123 }
124
125 sql = "INSERT INTO kasai_roles_operatives (id_role, id_operative) VALUES (" + idRole + ",'" + org.apache.commons.lang.StringEscapeUtils.escapeSql(idOperative) + "')";
126 con.createStatement().executeUpdate(sql);
127 }
128 }
129 con.commit();
130 }catch (DataAccessException sqlE){
131 try{
132 con.rollback();
133 }catch (SQLException e){}
134 throw sqlE;
135 }catch (DoesntExistsException deE){
136 try{
137 con.rollback();
138 }catch (SQLException e){}
139 throw deE;
140 }catch (SQLException sqle){
141 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "create", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
142 throw new DataAccessException(sqle);
143 }finally{
144 try{
145
146 con.setAutoCommit(true);
147 con.close();
148 }catch(Exception e){}
149 }
150
151 return idRole;
152 }
153
154 public void deleteOperativeFromRole(String idOperative, int role) throws DataAccessException{
155 Connection con = null;
156 if (StringUtils.isNotEmpty(idOperative)){
157 try{
158 String sql = "delete from kasai_roles_operatives where id_role=" + role + " and id_operative='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(idOperative) + "'";
159 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
160 con.createStatement().executeUpdate(sql);
161 }catch (SQLException sqle){
162 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "deleteOperativeFromRole", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
163 throw new DataAccessException(sqle);
164 }finally{
165 try{
166 con.close();
167 }catch(Exception e){}
168 }
169 }
170 }
171
172 public void delete(int id) throws DataAccessException{
173 Connection con = null;
174 String sql;
175
176 try{
177 sql = "DELETE FROM kasai_roles WHERE id=" + id;
178 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
179 con.createStatement().executeUpdate(sql);
180 }catch (SQLException sqle){
181 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "delete", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
182 throw new DataAccessException(sqle);
183 }finally{
184 try{
185 con.close();
186 }catch(Exception e){}
187 }
188
189 }
190
191 public Collection listOperativesFromRole(int role, String operative) throws DataAccessException{
192 Connection con = null;
193 String sql;
194 ResultSet rs = null;
195 Operative o = null;
196 ArrayList operatives = new ArrayList ();
197 try{
198 sql = "SELECT AU.* FROM kasai_operatives AU,kasai_roles_operatives ARO " +
199 "WHERE AU.id=ARO.id_operative AND ARO.id_role=" + role;
200 if (StringUtils.isNotEmpty(operative)){
201 sql += " AND AU.id='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(operative) +"' ";
202 }
203 sql += " order by AU.sequence ";
204 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
205 rs = con.createStatement().executeQuery(sql);
206 while (rs.next()){
207 o = new Operative (rs);
208 operatives.add(o);
209 }
210 return operatives;
211 }catch (SQLException sqle){
212 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "listOperativesFromRole", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
213 throw new DataAccessException(sqle);
214 }finally{
215 try{
216 rs.close();
217 }catch(Exception e){}
218 try{
219 con.close();
220 }catch(Exception e){}
221 }
222
223 }
224
225 public Collection listOperativesNotInRole(int role) throws DataAccessException {
226
227 Connection con = null;
228 String sql;
229 ResultSet rs = null;
230 Operative o = null;
231 ArrayList currentOperatives = new ArrayList ();
232 ArrayList operatives = new ArrayList ();
233
234 try {
235 sql = "SELECT AU.* FROM kasai_operatives AU,kasai_roles_operatives ARO " +
236 "WHERE AU.id=ARO.id_operative AND ARO.id_role=" + role + " ORDER BY AU.sequence ";
237 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
238 rs = con.createStatement().executeQuery(sql);
239
240 while (rs.next()) {
241
242 o = new Operative(rs);
243 currentOperatives.add(o);
244 }
245
246 sql = "SELECT * FROM kasai_operatives ORDER BY sequence ";
247 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
248 rs = con.createStatement().executeQuery(sql);
249
250 while (rs.next()) {
251 o = new Operative(rs);
252 if (!currentOperatives.contains(o)) {
253 operatives.add(o);
254 }
255 }
256
257 return operatives;
258 }catch (SQLException sqle){
259 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "listOperativesNotInRole", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
260 throw new DataAccessException(sqle);
261 } finally {
262
263 try {
264
265 rs.close();
266 } catch (Exception e) {}
267
268 try {
269
270 con.close();
271 } catch (Exception e) {}
272 }
273 }
274
275 public Collection list(String name, boolean exactly) throws DataAccessException{
276 Connection con = null;
277 String sql;
278 ResultSet rs = null;
279 Role r = null;
280 ArrayList roles = new ArrayList ();
281 try{
282 sql = "SELECT * FROM kasai_roles WHERE id <> -1";
283 if (StringUtils.isNotEmpty(name)){
284 if (!exactly){
285 sql += " AND name LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(name) +"%'";
286 }else{
287 sql += " AND name LIKE '" + org.apache.commons.lang.StringEscapeUtils.escapeSql(name) +"'";
288 }
289 }
290
291 sql += " order by name ";
292 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
293 rs = con.createStatement().executeQuery(sql);
294 while (rs.next()){
295 r = new Role (rs);
296 roles.add(r);
297 }
298 return roles;
299 }catch (SQLException sqle){
300 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "list", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
301 throw new DataAccessException(sqle);
302 }finally{
303 try{
304 rs.close();
305 }catch(Exception e){}
306 try{
307 con.close();
308 }catch(Exception e){}
309 }
310
311 }
312
313 public Role read(int role) throws DataAccessException{
314 Connection con = null;
315 String sql;
316 ResultSet rs = null;
317 Role r = null;
318 try{
319
320 sql = "SELECT * FROM kasai_roles WHERE id=" + role;
321 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
322 rs = con.createStatement().executeQuery(sql);
323 if (rs.next()){
324 r = new Role (rs);
325 }
326 return r;
327 }catch (SQLException sqle){
328 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "read", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
329 throw new DataAccessException(sqle);
330 }finally{
331 try{
332 rs.close();
333 }catch(Exception e){}
334 try{
335 con.close();
336 }catch(Exception e){}
337 }
338 }
339
340 public void update(int id, String name, String description) throws InvalidAttributesException,DataAccessException{
341
342 Connection con = null;
343 String sql;
344 Role r = null;
345 try{
346 r = new Role();
347 r.setDescription(description);
348 r.setName(name);
349 r.validate();
350
351 sql = "UPDATE kasai_roles set name='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(name) + "', description='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(description) + "' where id=" + id;
352 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
353 con.createStatement().executeUpdate(sql);
354 }catch (SQLException sqle){
355 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
356 throw new DataAccessException(sqle);
357 }finally{
358 try{
359 con.close();
360 }catch(Exception e){}
361 }
362 }
363
364 public void update(int id, String name, String description, String [] operatives) throws InvalidAttributesException,DataAccessException{
365
366 Connection con = null;
367 String sql;
368 ResultSet rs = null;
369 Role r = null;
370 try{
371 r = new Role();
372 r.setDescription(description);
373 r.setName(name);
374 r.validate();
375
376 sql = "UPDATE kasai_roles SET name='" + name + "', description='" + description + "' WHERE id=" + id;
377 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
378 con.createStatement().executeUpdate(sql);
379
380 if (operatives != null) {
381
382 String idOperative = null;
383
384 ArrayList newOperatives = new ArrayList (Arrays.asList(operatives));
385
386 ArrayList currentOperatives = new ArrayList ();
387 sql = "SELECT id_operative FROM kasai_roles_operatives WHERE id_role=" + id;
388 rs = con.createStatement().executeQuery(sql);
389
390 while (rs.next()) {
391
392 currentOperatives.add(StringUtils.defaultString(rs.getString("id_operative")));
393 }
394
395 for (int i = 0; i < operatives.length; i++) {
396
397 idOperative = operatives[i];
398
399 if (!currentOperatives.contains(idOperative)) {
400
401 sql = "INSERT INTO kasai_roles_operatives (id_role, id_operative) VALUES (" + id + ",'" + idOperative +
402 "')";
403 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
404 con.createStatement().executeUpdate(sql);
405 }
406 }
407
408 for (int i = 0; i < currentOperatives.size(); i++) {
409
410 idOperative = (String ) currentOperatives.get(i);
411
412 if (!newOperatives.contains(idOperative)) {
413
414 sql = "DELETE FROM kasai_roles_operatives WHERE id_operative='" + idOperative + "' AND id_role=" + id;
415 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
416 con.createStatement().executeUpdate(sql);
417 }
418 }
419 }
420 }catch (SQLException sqle){
421 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
422 throw new DataAccessException(sqle);
423 } finally {
424 try {
425 con.close();
426 } catch(Exception e) {}
427 }
428 }
429 }
430 |
Popular Tags |
Java API By Example, From Geeks To Geeks.