1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package jdbcadmin.core.tools;
21
22 import java.sql.PreparedStatement;
23 import java.sql.ResultSet;
24 import java.sql.SQLException;
25 import java.sql.Statement;
26 import java.util.ArrayList;
27 import java.util.Iterator;
28 import java.util.List;
29
30 import jdbcadmin.core.access.IAccessAuthorization;
31 import jdbcadmin.core.data.Column;
32 import jdbcadmin.core.data.ConnectionInfo;
33 import jdbcadmin.core.data.Line;
34 import jdbcadmin.core.data.Table;
35 import jdbcadmin.core.exceptions.ConvertionException;
36 import jdbcadmin.core.exceptions.DangerousOperationException;
37 import jdbcadmin.core.exceptions.DataNotFoundException;
38 import jdbcadmin.core.exceptions.FunctionalException;
39 import jdbcadmin.core.exceptions.TechnicalException;
40 import jdbcadmin.core.util.SQLTypeConvertor;
41
42 import org.apache.commons.logging.Log;
43 import org.apache.commons.logging.LogFactory;
44
45 /***
46 * Class for managing data.
47 * @author Thomas Recloux (trecloux@norsys.fr)
48 */
49 public class DataManager extends AbstractCnxUser {
50
51
52 /*** SQL Keyword SELECT. */
53 private static final String SELECT = "SELECT ";
54 /*** SQL Keyword INSERT. */
55 private static final String INSERT = "INSERT ";
56 /*** SQL Keyword UPDATE. */
57 private static final String UPDATE = "UPDATE ";
58 /*** SQL Keyword DELETE. */
59 private static final String DELETE = "DELETE ";
60 /*** SQL Keyword FROM. */
61 private static final String FROM = " FROM ";
62 /*** SQL Keyword WHERE. */
63 private static final String WHERE = " WHERE ";
64 /*** SQL Keyword INTO. */
65 private static final String INTO = " INTO ";
66 /*** SQL Keyword VALUES. */
67 private static final String VALUES = " VALUES ";
68 /*** SQL Keyword SET. */
69 private static final String SET = " SET ";
70 /*** SQL Keyword AND. */
71 private static final String AND = " AND ";
72 /*** Comma. */
73 private static final String COMMA = ", ";
74 /*** Dot. */
75 private static final String SPACE = " ";
76 /*** SQL Keyword AS. */
77 private static final String AS = " AS ";
78 /*** Left parenthesis. */
79 private static final String LEFT_PARENTHESIS = " (";
80 /*** Rifght parenthesis. */
81 private static final String RIGHT_PARENTHESIS = ") ";
82 /*** Joker for prepared SQL orders (?). */
83 private static final String JOKER = "?";
84 /*** Equal. */
85 private static final String EQUAL = "=";
86 /*** Request timeout. */
87 private static final int TIMEOUT_REQUETE = 5;
88 /*** Maximum number of line to retrieve. */
89 private static final int MAX_LIGNE_NUMBER = 1000;
90
91
92 /*** Logger */
93 private static Log logger = LogFactory.getLog(DataManager.class);
94
95
96 /*** Access authorizations . */
97 private IAccessAuthorization authorizations;
98
99
100 /***
101 * Cosntructor which set up connection informations and access authorizations
102 * @param aCnxInfos connection informations
103 * @param aAuthorizations access authorizations
104 */
105 public DataManager (ConnectionInfo aCnxInfos, IAccessAuthorization aAuthorizations) {
106 super(aCnxInfos);
107 authorizations = aAuthorizations;
108 }
109
110
111
112
113 /***
114 * Retrieves some lines from the specified table.<br>
115 * The index of the first line is <i>0</i>.<br>
116 * Cannot retrieve more than 1000 lines <br>
117 * @param aTable The table to select
118 * @param aIdxDebut Index for the first line
119 * @param aNbLines Number of lines to retrieve
120 * @return List of lines {@link #jdbcadmin.core.data.Ligne}
121 * @throws TechnicalException Technical error
122 * @throws DataNotFoundException Data not found
123 */
124 public List select(Table aTable, int aIdxDebut, int aNbLines) throws TechnicalException
125 , DataNotFoundException {
126 if (logger.isDebugEnabled()) {
127 logger.debug ("BEGIN DataManager.getLines, aNbLignes = " + aNbLines);
128 }
129
130 boolean mustDeconnect = connect();
131 boolean scrollAutthorized;
132 boolean posError = false;
133 List lines = new ArrayList();
134 long t1 = 0;
135 int nbLines = aNbLines;
136 if (nbLines < 0 || nbLines > MAX_LIGNE_NUMBER) {
137 nbLines = MAX_LIGNE_NUMBER;
138 }
139
140 StringBuffer buff = new StringBuffer();
141 buff.append(SELECT);
142 checkFilled(aTable);
143
144 Iterator it = aTable.iterateColumnNames();
145 while (it.hasNext()) {
146 String nom = (String) it.next();
147 buff.append(nom);
148 if (it.hasNext()) {
149 buff.append(COMMA);
150 }
151 }
152
153 buff.append(FROM);
154 buff.append(aTable.getCompleteName());
155
156 Statement stmt = null;
157 ResultSet rs = null;
158 try {
159 scrollAutthorized = cnx.getMetaData().supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
160 if (scrollAutthorized) {
161 stmt = cnx.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
162 } else {
163 stmt = cnx.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
164 }
165 stmt.setMaxRows(aIdxDebut + nbLines);
166 stmt.setFetchSize(aIdxDebut + nbLines);
167 stmt.setQueryTimeout(TIMEOUT_REQUETE);
168 if (logger.isInfoEnabled()) {
169 logger.info("SQL Order : " + buff.toString());
170 }
171 t1 = System.currentTimeMillis();
172 rs = stmt.executeQuery(buff.toString());
173 if (logger.isDebugEnabled()) {
174 logger.debug ("Query executed in : " + (System.currentTimeMillis() - t1) + " ms");
175 }
176
177
178
179
180 if (aIdxDebut > 0) {
181
182 if (rs.next()) {
183
184
185 if (scrollAutthorized) {
186 posError = !rs.absolute(aIdxDebut - 1);
187 if (logger.isDebugEnabled()) {
188 logger.debug ("scroll ok");
189 }
190 } else {
191 for (int i = 0; i < aIdxDebut - 1; i++) {
192 if (!rs.next()) {
193 posError = true;
194 }
195 if (logger.isDebugEnabled()) {
196 logger.debug ("Pre-positionning fetch num " + i + " ok");
197 }
198 }
199
200 }
201 } else {
202 posError = true;
203 }
204 if (posError) {
205 throw new DataNotFoundException ("The asked zone is not accessible");
206 }
207 }
208
209
210 for (int i = 0; (rs.next() && i < nbLines); i++) {
211 if (logger.isDebugEnabled()) {
212 logger.debug ("Retrieve fetch num " + i + " ok");
213 }
214 Line ligne = new Line(aTable);
215
216 it = aTable.iterateColumnNames();
217 while (it.hasNext()) {
218 String nomCol = (String) it.next();
219 Column col = aTable.getColumn(nomCol);
220 Object o = rs.getObject(nomCol);
221 String val = SQLTypeConvertor.format(col, o);
222 ligne.addValue(nomCol, val);
223 }
224 lines.add(ligne);
225 }
226 } catch (SQLException sqle) {
227 throw new TechnicalException("SQL Error retrieving the lines", sqle);
228 } finally {
229 try {
230 if (rs != null) {
231 rs.close();
232 }
233 if (stmt != null) {
234 stmt.close();
235 }
236 } catch (SQLException sqle2) {
237
238 }
239 if (mustDeconnect) {
240 disconnect();
241 }
242 }
243 if (logger.isDebugEnabled()) {
244 logger.debug("END - DataManager.getLines");
245 }
246 return lines;
247 }
248
249 /***
250 * Insert the specified line in the database.
251 * @param aLine the line to insert
252 * @throws TechnicalException Technical error.
253 * @throws ConvertionException Convertion error (the exception specify the bad column)
254 */
255 public void insert(Line aLine) throws TechnicalException, ConvertionException {
256 boolean doitDeconnecter = connect();
257 StringBuffer buff = new StringBuffer();
258 StringBuffer logBuff = null;
259 if (logger.isInfoEnabled()) {
260 logBuff = new StringBuffer();
261 }
262 Iterator it;
263 PreparedStatement pstmt = null;
264 checkFilled(aLine.getTable());
265 try {
266 buff.append(INSERT);
267 buff.append(INTO);
268 buff.append(aLine.getTable().getCompleteName());
269 buff.append(LEFT_PARENTHESIS);
270 it = aLine.getTable().iterateColumnNames();
271 while (it.hasNext()) {
272 buff.append((String) it.next());
273 if (it.hasNext()) {
274 buff.append(COMMA);
275 }
276 }
277 buff.append(RIGHT_PARENTHESIS);
278 buff.append(VALUES);
279 buff.append(LEFT_PARENTHESIS);
280 for (int i = 0; i < aLine.getTable().getColumnNumber(); i++) {
281 buff.append(JOKER);
282 if ((i + 1) < aLine.getTable().getColumnNumber()) {
283 buff.append(COMMA);
284 }
285 }
286 buff.append(RIGHT_PARENTHESIS);
287 if (logger.isInfoEnabled()) {
288 logger.info("Insert request" + buff.toString());
289 }
290 pstmt = cnx.prepareStatement(buff.toString());
291 it = aLine.getTable().iterateColumnNames();
292 int i = 1;
293 while (it.hasNext()) {
294 String nomCol = (String) it.next();
295 String val = aLine.getValue(nomCol);
296 Column col = aLine.getTable().getColumn(nomCol);
297 if (val != null) {
298 Object o = SQLTypeConvertor.parse(col, val);
299 pstmt.setObject(i, o);
300 if (logger.isInfoEnabled()) {
301 logBuff.append(i + "=" + o + ";");
302 }
303 } else {
304 pstmt.setNull(i, aLine.getTable().getColumn(nomCol).getType());
305 if (logger.isInfoEnabled()) {
306 logBuff.append(i + "=null;");
307 }
308 }
309 i++;
310 }
311 if (logger.isInfoEnabled()) {
312 logger.info("Parameters ; " + logBuff.toString());
313 logBuff = new StringBuffer();
314 }
315 pstmt.execute();
316 } catch (SQLException sqle) {
317 throw new TechnicalException("SQL Error " + sqle.getErrorCode()
318 + " inserting the line", sqle);
319 } finally {
320 try {
321 if (pstmt != null) {
322 pstmt.close();
323 }
324 } catch (SQLException sqle2) {
325
326 }
327 if (doitDeconnecter) {
328 disconnect();
329 }
330 }
331 }
332
333 /***
334 * Update the database with the specified line
335 * @param aLine the line to update
336 * @throws TechnicalException Technical error
337 * @throws DangerousOperationException Dangerous operation (no primary key)
338 * @throws FunctionalException Functionnal error (lines updated <> 1)
339 * @throws ConvertionException Convertion error (the exception specify the bad column)
340 */
341 public void update (Line aLine) throws TechnicalException, FunctionalException
342 , DangerousOperationException, ConvertionException {
343 boolean doitDeconnecter = connect();
344 PreparedStatement pstmt = null;
345 StringBuffer buffUpdate = new StringBuffer ();
346 StringBuffer buffWhere = new StringBuffer ();
347 StringBuffer logBuff = null;
348 if (logger.isInfoEnabled()) {
349 logBuff = new StringBuffer();
350 }
351 Iterator it;
352 boolean firstKeyMet = false;
353 boolean firstUpdatedColMet = false;
354 checkFilled(aLine.getTable());
355 if (!aLine.getTable().hasPrimaryKey()) {
356 throw new DangerousOperationException("Update dangerous, no primary key");
357 }
358 try {
359 cnx.setAutoCommit(false);
360 buffUpdate.append(UPDATE);
361 buffUpdate.append(aLine.getTable().getCompleteName());
362 buffUpdate.append(SET);
363 if (aLine.getTable().hasPrimaryKey()) {
364 buffWhere.append(WHERE);
365 }
366 it = aLine.getTable().iterateColumns();
367 while (it.hasNext()) {
368 Column col = (Column) it.next();
369 if (aLine.getTable().isPKColumn(col.getName())) {
370 if (!firstKeyMet) {
371 firstKeyMet = true;
372 } else {
373 buffWhere.append(AND);
374 }
375 buffWhere.append(col.getName());
376 buffWhere.append(EQUAL);
377 buffWhere.append(JOKER);
378
379 } else {
380 if (!firstUpdatedColMet) {
381 firstUpdatedColMet = true;
382 } else {
383 buffUpdate.append(COMMA);
384 }
385 buffUpdate.append(col.getName());
386 buffUpdate.append(EQUAL);
387 buffUpdate.append(JOKER);
388
389 }
390 }
391 buffUpdate.append(buffWhere);
392 if (logger.isInfoEnabled()) {
393 logger.info("Update SQL Order : " + buffUpdate.toString());
394 }
395 pstmt = cnx.prepareStatement(buffUpdate.toString());
396 int i = 1;
397 it = aLine.getTable().iterateColumns();
398 while (it.hasNext()) {
399 Column col = (Column) it.next();
400 if (!aLine.getTable().isPKColumn(col.getName())) {
401 String val = aLine.getValue(col.getName());
402 Object o = SQLTypeConvertor.parse(col, val);
403 if (val == null) {
404 pstmt.setNull(i, col.getType());
405 if (logger.isInfoEnabled()) {
406 logBuff.append(i + "=null;");
407 }
408 } else {
409 pstmt.setObject(i, o);
410 if (logger.isInfoEnabled()) {
411 logBuff.append(i + "=<" + o + ">;");
412 }
413 }
414 i++;
415 }
416 }
417 it = aLine.getTable().iterateColumns();
418 while (it.hasNext()) {
419 Column col = (Column) it.next();
420 if (aLine.getTable().isPKColumn(col.getName())) {
421 String val = aLine.getValue(col.getName());
422 if (val == null) {
423 pstmt.setNull(i, col.getType());
424 if (logger.isInfoEnabled()) {
425 logBuff.append(i + "=null;");
426 }
427 } else {
428 pstmt.setString(i, val);
429 if (logger.isInfoEnabled()) {
430 logBuff.append(i + "=<" + val + ">;");
431 }
432 }
433 i++;
434 }
435 }
436 if (logger.isInfoEnabled()) {
437 logger.info("Parameters ; " + logBuff.toString());
438 }
439 int nbRow = pstmt.executeUpdate();
440 if (nbRow != 1) {
441 throw new FunctionalException("Error updating the database: " + nbRow + " lines updated");
442 } else {
443 cnx.commit();
444 }
445
446 } catch (SQLException sqle) {
447 throw new TechnicalException("SQL Error updating the line :"
448 + aLine.toString(), sqle);
449 } finally {
450 try {
451 cnx.rollback();
452 } catch (SQLException sqle2) {
453
454 }
455 try {
456 if (pstmt != null) {
457 pstmt.close();
458 }
459 } catch (SQLException sqle2) {
460
461 }
462 if (doitDeconnecter) {
463 disconnect();
464 }
465 }
466 }
467
468 /***
469 * Deletes the specified line from the database
470 * @param aLine the line to delete
471 * @throws TechnicalException Technical error
472 * @throws DangerousOperationException Dangerous operation (no primary key)
473 * @throws FunctionalException Functionnal error (lines updated <> 1)
474 * @throws ConvertionException Convertion error (the exception specify the bad column)
475 */
476 public void delete (Line aLine) throws TechnicalException, FunctionalException
477 , DangerousOperationException, ConvertionException {
478 boolean doitDeconnecter = connect();
479 PreparedStatement pstmt = null;
480 StringBuffer buff = new StringBuffer ();
481 Iterator it;
482 checkFilled(aLine.getTable());
483 if (!aLine.getTable().hasPrimaryKey()) {
484 throw new DangerousOperationException("Delete dangerous, no primary key");
485 }
486 try {
487 cnx.setAutoCommit(false);
488 buff.append(DELETE);
489 buff.append(FROM);
490 buff.append(aLine.getTable().getCompleteName());
491 buff.append(WHERE);
492 it = aLine.getTable().iterateColumns();
493 boolean premiereClefRencontree = false;
494 while (it.hasNext()) {
495 Column col = (Column) it.next();
496 if (aLine.getTable().isPKColumn(col.getName())) {
497 if (!premiereClefRencontree) {
498 premiereClefRencontree = true;
499 } else {
500 buff.append(AND);
501 }
502 buff.append(col.getName());
503 buff.append(EQUAL);
504 buff.append(JOKER);
505 }
506 }
507 pstmt = cnx.prepareStatement(buff.toString());
508 it = aLine.getTable().iterateColumns();
509 int i = 1;
510 while (it.hasNext()) {
511 Column col = (Column) it.next();
512 if (aLine.getTable().isPKColumn(col.getName())) {
513 String val = aLine.getValue(col.getName());
514 Object o = SQLTypeConvertor.parse(col, val);
515 pstmt.setObject(i, o);
516 i++;
517 }
518
519 }
520 int nbRow = pstmt.executeUpdate();
521 if (nbRow != 1) {
522 throw new FunctionalException("Error deleting the line : " + nbRow + " lines deleted");
523 } else {
524 cnx.commit();
525 }
526 } catch (SQLException sqle) {
527 throw new TechnicalException("SQL Error deleting the line ", sqle);
528 } finally {
529 try {
530 cnx.rollback();
531 } catch (SQLException sqle2) {
532
533 }
534 try {
535 if (pstmt != null) {
536 pstmt.close();
537 }
538 } catch (SQLException sqle2) {
539
540 }
541 if (doitDeconnecter) {
542 disconnect();
543 }
544 }
545 }
546
547 /***
548 * Checks that the table object is filled, otherwise fill it.
549 * @param aTable the table
550 * @throws TechnicalException technical error
551 */
552 private void checkFilled(Table aTable) throws TechnicalException {
553 if (!aTable.isFilled()) {
554 JdbcInspector inspector = new JdbcInspector(conInfos, authorizations);
555 inspector.fillTable(aTable);
556 }
557 }
558 }