SqlUtil


1   package jodd.db;
2   
3   import java.sql.Array;
4   import java.sql.Connection;
5   import java.sql.PreparedStatement;
6   import java.sql.ResultSet;
7   import java.sql.SQLException;
8   import java.sql.Statement;
9   
10  import jodd.db.pool.ConnectionPool;
11  
12  /**
13   * Smart-and-slick SQL facade.
14   * Class is <b>not</b> fool-proof and threadsafe.
15   *
16   * @2do Add more <code>PreparedStatement</code> setXXX() methods
17   */
18  public class SqlUtil {
19  
20      // ---------------------------------------------------------------- properties
21  
22      private String[] debugSql;
23      private String[] debugSqlParams;
24      private Connection conn = null;
25      private Statement st = null;                // because of speed optimization...
26      private PreparedStatement pst = null;       // instanceof and castings are avoid
27      private ResultSet resultSet = null;
28  
29      // ---------------------------------------------------------------- constructors
30  
31      private ConnectionPool pool = null;
32      
33      private Connection providedConnection = null;
34  
35      /**
36       * Default constructor from ConnectionPool.
37       *
38       * @param cp     connection pool instance
39       */
40      public SqlUtil(ConnectionPool cp) {
41          pool = cp;
42      }
43      /**
44       * Default constructor from common Connection.
45       *
46       * @param conenction opened db connection
47       */
48      public SqlUtil(Connection conenction) {
49          providedConnection = conenction;
50      }
51  
52      // ---------------------------------------------------------------- init
53  
54      /**
55       * Sets and prepares a SQL command by using forward only and read only
56       * prepared statement.
57       *
58       * @param sql    sql query string.
59       *
60       * @exception SQLException
61       * @see #setSql(String, int, int)
62       */
63      public void setSql(String sql) throws SQLException {
64          setSql(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
65      }
66  
67      /**
68       * Sets and prepares a SQL command by using prepared statements.
69       * <p>
70       *
71       * It first closes existing resultset, if any availiable. Then it
72       * closes existing prepared statement. After, obtains connection
73       * from database if not already taken. Sets commit mode, if necessary.
74       *
75       * @param sql        sql query string.
76       * @param resultType result type
77       * @param resultSetConcurrency
78       *                   result concurrency
79       *
80       * @exception SQLException
81       */
82      public void setSql(String sql, int resultType, int resultSetConcurrency) throws SQLException {
83          if (resultSet != null) {
84              resultSet.close();
85              resultSet = null;
86          }
87          if (pst != null) {
88              pst.close();
89              pst = null;
90          }
91          if (conn == null) {
92              if (pool != null) {
93                  conn = pool.getConnection();
94              } else {
95                  conn = providedConnection;
96              }
97          }
98          setCommitMode();
99          pst = conn.prepareStatement(sql, resultType, resultSetConcurrency);
100         createDebugSql(sql);
101     }
102 
103     /**
104      * Sets and prepares a SQL command by using forward only and read only
105      * statement.
106      *
107      * @param sql    sql query string.
108      *
109      * @exception SQLException
110      * @see #setStaticSql(String, int, int)
111      */
112     public void setStaticSql(String sql) throws SQLException {
113         setSql(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
114     }
115 
116 
117     /**
118      * Sets and prepares a SQL command by using simple statements.
119      * <p>
120      *
121      * It first closes existing resultset, if any availiable. Then it
122      * closes existing statement. After, obtains connection
123      * from database if not already taken. Sets commit mode, if necessary.
124      *
125      * @param sql        sql query string.
126      * @param resultType result type
127      * @param resultSetConcurrency
128      *                   concurency type
129      *
130      * @exception SQLException
131      */
132     public void setStaticSql(String sql, int resultType, int resultSetConcurrency) throws SQLException {
133         if (resultSet != null) {
134             resultSet.close();
135             resultSet = null;
136         }
137         if (st != null) {
138             st.close();
139             st = null;
140         }
141         if (conn == null) {
142             if (pool != null) {
143                 conn = pool.getConnection();
144             } else {
145                 conn = providedConnection;
146             }
147         }
148         setCommitMode();
149         st = conn.createStatement(resultType, resultSetConcurrency);
150         debugSql = new String[] { sql };
151         debugSqlParams = new String[] {""};
152     }
153 
154     // ---------------------------------------------------------------- execute
155 
156     /**
157      * Executes SELECT statements, but works with all statements. Previously closes
158      * existing result set.
159      *
160      * @return ResultSet
161      * @exception SQLException
162      */
163     public ResultSet executeQuery() throws SQLException {
164         if (resultSet != null) {
165             resultSet.close();
166             resultSet = null;
167         }
168         if (st != null) {
169             resultSet = st.executeQuery(debugSql[0]);
170             return resultSet;
171         }
172         if (pst != null) {
173             resultSet = pst.executeQuery();
174             return resultSet;
175         }
176         return null;
177     }
178 
179     /**
180      * Executes UPDATE, INSERT or DELETE statements.
181      *
182      * @return result of executeUpdate()
183      * @exception SQLException
184      */
185     public int executeUpdate() throws SQLException {
186         if (st != null) {
187             return st.executeUpdate(debugSql[0]);
188         }
189         if (pst != null) {
190             return pst.executeUpdate();
191         }
192         return -1;
193     }
194 
195 
196     // ---------------------------------------------------------------- close
197 
198     /**
199      * Closes result set and the statement. Connection is not closed.
200      *
201      * @param rs     ResultSet created by executeQuery(), or null if ResultSet is not being used.
202      *
203      * @see #close()
204      * @deprecated use close() instead
205      */
206     public void close(ResultSet rs) {
207         try {
208             if (rs != null) {
209                 rs.close();
210             }
211         } catch (SQLException sex) {
212         }
213 
214         try {
215             if (pst != null) {
216                 pst.close();
217             }
218         } catch (SQLException sex) {
219         } finally {
220             pst = null;
221         }
222 
223         try {
224             if (st != null) {
225                 st.close();
226             }
227         } catch (SQLException sex) {
228         } finally {
229             st = null;
230         }
231     }
232 
233     /**
234      * Closes result set (if previously created) and the statement. Connection is not closed.
235      */
236     public void close() {
237         // result set
238         try {
239             if (resultSet != null) {
240                 resultSet.close();
241             }
242         } catch (SQLException sex) {
243         } finally {
244             resultSet = null;
245         }
246 
247         // prepared statement
248         try {
249             if (pst != null) {
250                 pst.close();
251             }
252         } catch (SQLException sex) {
253         } finally {
254             pst = null;
255         }
256 
257         // statement
258         try {
259             if (st != null) {
260                 st.close();
261             }
262         } catch (SQLException sex) {
263         } finally {
264             st = null;
265         }
266     }
267 
268     /**
269      * Closes result set, preparead statements, sets default commit mode for
270      * connection and returns connection to db pool.
271      *
272      * @param rs     ResultSet created by executeQuery(), or null if ResultSet is not being used.
273      *
274      * @deprecated use closeAll() instead.
275      */
276     public void closeAll(ResultSet rs) {
277         close(rs);
278         resetCommitMode();
279         if (conn != null) {
280             if (pool != null) {
281                 pool.freeConnection(conn);
282             } else {
283                 try {
284                     conn.close();
285                 } catch (SQLException e) {
286                 }
287             }
288             conn = null;            
289         }
290     }
291 
292     /**
293      * Closes result set (if created), statements and returns connection to db
294      * pool when no ResultSets are used.
295      */
296     public void closeAll() {
297         close();
298         resetCommitMode();
299         if (conn != null) {
300             if (pool != null) {
301                 pool.freeConnection(conn);
302             } else {
303                 try {
304                     conn.close();
305                 } catch (SQLException e) {
306                 }
307             }
308             conn = null;            
309         }
310     }
311 
312     // ---------------------------------------------------------------- transactions
313 
314     private static Boolean defaultAutoCommit = null;
315     /**
316      * Sets default auto-commit that SqlUtil <b>must</b> provide before executing
317      * new statement, neverness if <code>setAutoCommit</code> is used or not. By
318      * using this method and setting default auto-commit value to be the same as
319      * it is for database, it is guaranteed that if it is not explicitly set
320      * different, the default mode will be used.
321      *
322      * <p>
323      * If default auto-commit mode is not used, connection will be examined on
324      * first usage, and that commit status will be taken as the default one.
325      *
326      * @param autoCommit default auto-commit mode
327      */
328     public static void setDefaultAutoCommit(boolean autoCommit) {
329         defaultAutoCommit = Boolean.valueOf(autoCommit);
330     }
331     /**
332      * Returns default auto-commit mode or <code>null</code> if nothing set.
333      *
334      * @return default auto-commit mode
335      */
336     public static Boolean getDefaultAutoCommit() {
337         return defaultAutoCommit;
338     }
339 
340 
341     private Boolean storedAutoCommit = null;            // stored auto commit for the current session
342 
343     private Boolean currentAutoCommit = null;           // current value of auto-commit
344 
345     /**
346      * Sets this connection's auto-commit mode to the given state. If a
347      * connection is in auto-commit mode, then all its SQL statements will be
348      * executed and committed as individual transactions. Otherwise, its SQL
349      * statements are grouped into transactions that are terminated by a call to
350      * either the method <code>commit</code> or the method <code>rollback</code>.
351      *
352      * <p>
353      * If this method is not used, then default database commit mode is used,
354      * and this class doesn't change anything.
355      *
356      * <p>
357      * NOTE: if this method is called <b>before</b> very first
358      * <code>setSql()</code> or <code>setStaticSql()</code>, than it will just
359      * 'remember' the commit state that will be actually set in those commands,
360      * after connection is retrieved from database. This is prefered method.
361      *
362      * <p>
363      * If it is called after, then this method will directly change connection
364      * commit mode, with all consequences.
365      *
366      * @param autoCommit true to enable auto-commit mode; false to disable it
367      *
368      * @exception SQLException
369      */
370     public void setAutoCommit(boolean autoCommit) throws SQLException {
371         currentAutoCommit = Boolean.valueOf(autoCommit);
372         setCommitMode();
373     }
374 
375     /**
376      * Retrieves the current auto-commit mode for this object.
377      * If <code>null</code> is returned then default database mode is used.
378      *
379      * @return the current auto-commit mode
380      */
381     public Boolean getAutoCommit() {
382         return currentAutoCommit;
383     }
384 
385     /**
386      * Returns the stored auto-commit mode for the connection. This value should
387      * match database initial commit mode. If <code>null</code> is returned, then
388      * connection is not yet obtained. Therefore, this method has to be called
389      * after <code>setSql</code> methods.
390      *
391      * @return default commit mode
392      */
393     public Boolean getStoredAutoCommit() {
394         return storedAutoCommit;
395     }
396 
397     /**
398      * Intelligently sets the commit mode. When called for the first time on
399      * availiable connection it stores default values.
400      *
401      * @exception SQLException
402      */
403     private void setCommitMode() throws SQLException {
404         if (conn == null) {
405             return;
406         }
407         // remember default
408         if (storedAutoCommit == null) {                                 // very first time on this connection
409             if (defaultAutoCommit == null) {
410                 storedAutoCommit = Boolean.valueOf(conn.getAutoCommit());   // remember default auto-commit mode...
411             } else {
412                 storedAutoCommit = defaultAutoCommit;                       // ...or use specified default mode
413             }
414         }
415         // set new if required
416         boolean newMode;
417         if (currentAutoCommit != null) {                        // is there a new setting?
418             newMode = currentAutoCommit.booleanValue();
419         } else {
420             newMode = storedAutoCommit.booleanValue();
421         }
422         if (newMode != conn.getAutoCommit()) {                  // If new mode is different then current mode..
423             conn.setAutoCommit(newMode);                        // ...then specify new, specified setting
424         }
425     }
426 
427     /**
428      * Resets connection commit mode to default, when connection is returned back
429      * to database pool.
430      */
431     private void resetCommitMode() {
432         if (conn == null) {
433             return;
434         }
435         try {
436             if (currentAutoCommit != null) {
437                 boolean currentMode = conn.getAutoCommit();             // if current mode...
438                 boolean storedMode = storedAutoCommit.booleanValue();   // ...and stored mode...
439                 if (currentMode != storedMode) {                        // ...are not equals...
440                     conn.setAutoCommit(storedMode);                     // ...set mode to remembered default mode
441                 }
442             }
443         } catch (SQLException sex) {
444         } finally {
445             currentAutoCommit = null;
446             storedAutoCommit = null;
447         }
448     }
449 
450 
451     /**
452      * Makes all changes made since the previous commit/rollback permanent and
453      * releases any database locks currently held by this object. This method
454      * should be used only when auto-commit mode has been disabled.
455      *
456      * @exception SQLException
457      */
458     public void commit() throws SQLException {
459         conn.commit();
460     }
461 
462     /**
463      * Undoes all changes made in the current transaction and releases any
464      * database locks currently held by this object. This method should be used
465      * only when auto-commit mode has been disabled.
466      */
467     public void rollback() {
468         try {
469             conn.rollback();
470         } catch (SQLException sex) {
471         }
472     }
473 
474     // ---------------------------------------------------------------- debug sql
475 
476     private static boolean debugMode = false;
477     /**
478      * Sets debug mode.
479      *
480      * @param b
481      */
482     public static void setDebugMode(boolean b) {
483         debugMode = b;
484     }
485     /**
486      * Returns current debug mode.
487      *
488      * @return current debug mode
489      */
490     public static boolean getDebugMode() {
491         return debugMode;
492     }
493     
494     private void createDebugSql(String sql) {
495         if (getDebugMode() == false) {
496             debugSql = new String[] {sql};
497             return;
498         }
499         debugSql = sql.split("\\?");
500         debugSqlParams = new String[debugSql.length];
501         int i = 0;
502         int limit = debugSql.length - 1;
503         while (i < limit) {
504             debugSqlParams[i] = "?";
505             i++;
506         }
507         if (sql.endsWith("?")) {
508             debugSqlParams[i] = "?";
509         } else {
510             debugSqlParams[i] = "";
511         }
512     }
513 
514     private void setDebugSql(int ndx, String s) {
515         if (getDebugMode() == false) {
516             return;
517         }
518         debugSqlParams[ndx - 1] = s;
519     }
520 
521     private void setDebugSql(int ndx, int i) {
522         if (getDebugMode() == false) {
523             return;
524         }
525         debugSqlParams[ndx - 1] = Integer.toString(i);
526     }
527 
528     private void setDebugSql(int ndx, boolean i) {
529         if (getDebugMode() == false) {
530             return;
531         }
532         debugSqlParams[ndx - 1] = Boolean.toString(i);
533     }
534 
535     private void setDebugSql(int ndx, long i) {
536         if (getDebugMode() == false) {
537             return;
538         }
539         debugSqlParams[ndx - 1] = Long.toString(i);
540     }
541 
542     private void setDebugSql(int ndx, byte i) {
543         if (getDebugMode() == false) {
544             return;
545         }
546         debugSqlParams[ndx - 1] = Byte.toString(i);
547     }
548 
549     private void setDebugSql(int ndx, double i) {
550         if (getDebugMode() == false) {
551             return;
552         }
553         debugSqlParams[ndx - 1] = Double.toString(i);
554     }
555 
556     private void setDebugSql(int ndx, float i) {
557         if (getDebugMode() == false) {
558             return;
559         }
560         debugSqlParams[ndx - 1] = Float.toString(i);
561     }
562 
563 
564 
565     /**
566      * Returns formated pseudo-SQL string. When <code>PreparedStatement</code>
567      * is used, all ? are replaced with String representation of assigned values.
568      * Just for debugging purposes.
569      *
570      * @return formated pseudo-SQL string
571      */
572     public String toString() {
573         if (getDebugMode() == false) {
574             return debugSql[0];
575         }
576         StringBuffer result = new StringBuffer();
577         int i = 0;
578         while (i < debugSql.length) {
579             result.append(debugSql[i]);
580             result.append(debugSqlParams[i]);
581             i++;
582         }
583         return result.toString();
584     }
585 
586     // ---------------------------------------------------------------- prepared statement (primitives)
587 
588 
589     /**
590      * Sets value of int parameter in sql string.
591      *
592      * @param ndx    1-based index od parameter
593      * @param value  int value
594      *
595      * @exception SQLException
596      */
597     public void setInt(int ndx, int value) throws SQLException {
598         pst.setInt(ndx, value);
599         setDebugSql(ndx, value);
600     }
601 
602     /**
603      * Sets value of boolean parameter in sql string.
604      *
605      * @param ndx    1-based index of parameter
606      * @param value  parameter value
607      *
608      * @exception SQLException
609      */
610     public void setBoolean(int ndx, boolean value) throws SQLException {
611         pst.setBoolean(ndx, value);
612         setDebugSql(ndx, value);
613     }
614 
615     /**
616      * Sets value of long parameter in sql string.
617      *
618      * @param ndx    1-based index of parameter
619      * @param value  parameter value
620      *
621      * @exception SQLException
622      */
623     public void setLong(int ndx, long value) throws SQLException {
624         pst.setLong(ndx, value);
625         setDebugSql(ndx, value);
626     }
627 
628     /**
629      * Sets value of byte parameter in sql string.
630      *
631      * @param ndx    1-based index of parameter
632      * @param value  parameter value
633      *
634      * @exception SQLException
635      */
636     public void setByte(int ndx, byte value) throws SQLException {
637         pst.setByte(ndx, value);
638         setDebugSql(ndx, value);
639     }
640 
641     /**
642      * Sets value of double parameter in sql string.
643      *
644      * @param ndx    1-based index of parameter
645      * @param value  parameter value
646      *
647      * @exception SQLException
648      */
649     public void setDouble(int ndx, double value) throws SQLException {
650         pst.setDouble(ndx, value);
651         setDebugSql(ndx, value);
652     }
653 
654 
655     /**
656      * Sets value of float parameter in sql string.
657      *
658      * @param ndx    1-based index of parameter
659      * @param value  parameter value
660      *
661      * @exception SQLException
662      */
663     public void setFloat(int ndx, float value) throws SQLException {
664         pst.setFloat(ndx, value);
665         setDebugSql(ndx, value);
666     }
667 
668 
669     // ---------------------------------------------------------------- prepared statement (objects)
670 
671     /**
672      * Sets value of String parameter in sql string.
673      *
674      * @param ndx    1-based index of parameter
675      * @param value  parameter value
676      *
677      * @exception SQLException
678      */
679     public void setString(int ndx, String value) throws SQLException {
680         if (value != null) {
681             pst.setString(ndx, value);
682         } else {
683             pst.setNull(ndx, java.sql.Types.VARCHAR);
684         }
685         setDebugSql(ndx, value);
686     }
687 
688 
689     /**
690      * Sets value of java.sql.Date parameter in sql string.
691      *
692      * @param ndx    1-based index of parameter
693      * @param value  parameter value
694      *
695      * @exception SQLException
696      */
697     public void setDate(int ndx, java.sql.Date value) throws SQLException {
698         if (value != null) {
699             pst.setDate(ndx, value);
700         } else {
701             pst.setNull(ndx, java.sql.Types.DATE);
702         }
703         setDebugSql(ndx, value.toString());
704     }
705 
706     /**
707      * Sets value of java.sql.Timestamp parameter in sql string.
708      *
709      * @param ndx    1-based index of parameter
710      * @param value  parameter value
711      *
712      * @exception SQLException
713      */
714     public void setTimestamp(int ndx, java.sql.Timestamp value) throws SQLException {
715         if (value != null) {
716             pst.setTimestamp(ndx, value);
717         } else {
718             pst.setNull(ndx, java.sql.Types.TIMESTAMP);
719         }
720         setDebugSql(ndx, value.toString());
721     }
722 
723     /**
724      * Sets value of Array parameter in sql string.
725      *
726      * @param ndx    1-based index of parameter
727      * @param value  parameter value
728      *
729      * @exception SQLException
730      */
731     public void setArray(int ndx, Array value) throws SQLException {
732         if (value != null) {
733             pst.setArray(ndx, value);
734         } else {
735             pst.setNull(ndx, java.sql.Types.ARRAY);
736         }
737         setDebugSql(ndx, value.toString());
738     }
739 
740     
741     /**
742      * Sets value of BigDecimal parameter in sql string.
743      *
744      * @param ndx    1-based index of parameter
745      * @param value  parameter value
746      *
747      * @exception SQLException
748      */
749     public void setBigDecimal(int ndx, java.math.BigDecimal value) throws SQLException {
750         if (value != null) {
751             pst.setBigDecimal(ndx, value);
752         } else {
753             pst.setNull(ndx, java.sql.Types.NUMERIC);
754         }
755         setDebugSql(ndx, value.toString());
756     }
757 
758     /**
759      * Sets value of Blob parameter in sql string.
760      *
761      * @param ndx    1-based index of parameter
762      * @param value  parameter value
763      *
764      * @exception SQLException
765      */
766     public void setBlob(int ndx, java.sql.Blob value) throws SQLException {
767         if (value != null) {
768             pst.setBlob(ndx, value);
769         } else {
770             pst.setNull(ndx, java.sql.Types.BLOB);
771         }
772         setDebugSql(ndx, value.toString());
773     }
774 
775     /**
776      * Sets value of Clob parameter in sql string.
777      *
778      * @param ndx    1-based index of parameter
779      * @param value  parameter value
780      *
781      * @exception SQLException
782      */
783     public void setClob(int ndx, java.sql.Clob value) throws SQLException {
784         if (value != null) {
785             pst.setClob(ndx, value);
786         } else {
787             pst.setNull(ndx, java.sql.Types.CLOB);
788         }
789         setDebugSql(ndx, value.toString());
790     }
791 
792 
793 }
794
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Java Books Remove Frame
Popular Tags