View Javadoc

1   /* ==============================================================================
2    *   JDBCAdmin, data management software.
3    *   Copyright (C) 2005  Norsys S.A
4    *
5    *   This library is free software; you can redistribute it and/or
6    *   modify it under the terms of the GNU Lesser General Public
7    *   License as published by the Free Software Foundation; either
8    *   version 2.1 of the License, or (at your option) any later version.
9    *
10   *   This library is distributed in the hope that it will be useful,
11   *   but WITHOUT ANY WARRANTY; without even the implied warranty of
12   *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
13   *   Lesser General Public License for more details.
14   *
15   *   You should have received a copy of the GNU Lesser General Public
16   *   License along with this library; if not, write to the Free Software
17   *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
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      /////////// Constants ////////////
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      ////////// Static attributes //////////////
92      /*** Logger */
93      private static Log logger = LogFactory.getLog(DataManager.class);
94  
95      ///////// Instance attributes  ////////
96      /*** Access authorizations . */
97      private IAccessAuthorization authorizations;
98  
99      ////////// Constructors //////////////
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     ////////// Public methods //////////////
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         // Computing the SQL Order
140         StringBuffer buff = new StringBuffer();
141         buff.append(SELECT);
142         checkFilled(aTable);
143         // Column names
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         // Nom de la table
153         buff.append(FROM);
154         buff.append(aTable.getCompleteName());
155         // execution de la requete
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             // Treatment of the results
179             // Skipping first lines.
180             if (aIdxDebut > 0) {
181                 // If not the last line
182                 if (rs.next()) {
183                     // If the drive rsupports scrolling, we move the cursor
184                     // Otherwise, fetching all lines :-(
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             // Getching the asked lines, if there is not enougth lines, we return the
209             // fetched lines without throwing any exception.
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                 // Getting all columns
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                 // nothing to do
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                 // nothing to do
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                     //valWhere.add(aLigne.getValeur(col.getNom()));
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                     //valUpdate.add(aLigne.getValeur(col.getNom()));
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                 // Nothing to do
454             }
455             try {
456                 if (pstmt != null) {
457                     pstmt.close();
458                 }
459             } catch (SQLException sqle2) {
460                 // Nothing to do
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                 // Nothing to do
533             }
534             try {
535                 if (pstmt != null) {
536                     pstmt.close();
537                 }
538             } catch (SQLException sqle2) {
539                 // Nothing to do
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 }