JDBCUtil


1   /*************************************************************************
2    *                                                                       *
3    *  EJBCA: The OpenSource Certificate Authority                          *
4    *                                                                       *
5    *  This software 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 any later version.                    *
9    *                                                                       *
10   *  See terms of license at gnu.org.                                     *
11   *                                                                       *
12   *************************************************************************/
13  
14  package org.ejbca.util;
15  
16  import java.sql.Connection  ;
17  import java.sql.PreparedStatement  ;
18  import java.sql.ResultSet  ;
19  import java.sql.SQLException  ;
20  
21  import javax.sql.DataSource  ;
22  
23  import org.apache.log4j.Logger;
24  import org.ejbca.core.ejb.ServiceLocator;
25  import org.ejbca.core.ejb.ServiceLocatorException;
26  
27  
28  
29  /**
30   * Utility methods for jdbc calls.
31   * <p/>
32   * close() methods do not close quietly but log errors (exceptions) as warning. Closing
33   * quietly could mask potential errors that could have no impact depending on GC
34   * stress conditions, or simply exhaust database resources, so it is better to have some
35   * warning than nothing at all.
36   *
37   * @version $Id: JDBCUtil.java,v 1.2 2006/08/06 12:37:01 anatom Exp $
38   */
39  public class JDBCUtil {
40  
41      private static final Logger log = Logger.getLogger(JDBCUtil.class);
42      
43      public interface Preparer {
44          void prepare(PreparedStatement   ps) throws Exception  ;
45          String   getInfoString();
46      }
47      
48      public static void execute(String   sqlCommandTemplate, Preparer preparer, String   dataSource) throws Exception   {
49          if ( sqlCommandTemplate!=null ) {
50              Connection   connection = null;
51              ResultSet   result = null;
52              PreparedStatement   ps = null;
53              try {
54                  connection = ServiceLocator.getInstance().getDataSource(dataSource).getConnection();
55                  ps = connection.prepareStatement(sqlCommandTemplate);
56                  preparer.prepare(ps);
57                  if ( ps.execute() )
58                      result = ps.getResultSet();
59              } finally {
60                  JDBCUtil.close(connection, ps, result);
61              }
62          }
63      }
64      public static String   executeSelectString(String   sqlCommandTemplate, Preparer preparer, String   dataSource) throws Exception   {
65          String   ret = null;
66          if ( sqlCommandTemplate!=null ) {
67              Connection   connection = null;
68              ResultSet   result = null;
69              PreparedStatement   ps = null;
70              try {
71                  connection = ServiceLocator.getInstance().getDataSource(dataSource).getConnection();
72                  ps = connection.prepareStatement(sqlCommandTemplate);
73                  preparer.prepare(ps);
74                  if ( ps.execute() ) {
75                      result = ps.getResultSet();
76                      if (result.next()) {
77                          ret = result.getString(1);
78                      }
79                  }
80              } finally {
81                  JDBCUtil.close(connection, ps, result);
82              }
83          }
84          return ret;
85      }
86  
87  
88      /**
89       * Return the requested datasource name. It assumes that the dsName returns a reference (java.lang.String)
90       * to the real datasource instance, otherwise you're likely to get a ClassCastException.
91       *
92       * @param dsName the name of the requested datasource
93       * @return the requested datasource
94       * @throws ServiceLocatorException if there is an error locating the datasource
95       */
96      public static DataSource   getDataSource(String   dsName)
97              throws ServiceLocatorException {
98          String   dataSourceName = ServiceLocator.getInstance().getString(dsName);
99          return ServiceLocator.getInstance().getDataSource(dataSourceName);
100     }
101 
102     /**
103      * return a requested database connection
104      *
105      * @param dsName the name of the datasource
106      * @return a database connection
107      * @throws ServiceLocatorException if it cannot get the datasource connection
108      * @see #getDataSource(java.lang.String)
109      */
110     public static Connection   getDBConnection(String   dsName) throws ServiceLocatorException {
111         try {
112             return getDataSource(dsName).getConnection();
113         } catch (SQLException   e) {
114             throw new ServiceLocatorException("Error while getting db connection", e);
115         }
116     }
117 
118     /**
119      * Close a db connection logging closing errors as warning.
120      *
121      * @param con the connection to close (can be null)
122      */
123     public static void close(Connection   con) {
124         if (con != null)
125             try {
126                 con.close();
127             } catch (SQLException   e) {
128                 log.warn("Could not close connection", e);
129             }
130     }
131 
132     /**
133      * Close a resultset logging closing errors as warning.
134      *
135      * @param rs the resultset to close (can be null)
136      */
137     public static void close(ResultSet   rs) {
138         if (rs != null)
139             try {
140                 rs.close();
141             } catch (SQLException   e) {
142                 log.warn("Could not close ResultSet", e);
143             }
144     }
145 
146     /**
147      * Close a prepared statement logging closing errors as warning.
148      *
149      * @param ps the prepared statement to close (can be null)
150      */
151     public static void close(PreparedStatement   ps) {
152         if (ps != null)
153             try {
154                 ps.close();
155             } catch (SQLException   e) {
156                 log.warn("Could not close PreparedStatement", e);
157             }
158     }
159 
160     /**
161      * Close a connection, prepared statement and result set, logging potential
162      * closing errors as warning.
163      *
164      * @param con the connection to close (can be null)
165      * @param ps  the prepared statement to close (can be null)
166      * @param rs  the result set to close (can be null)
167      */
168     public static void close(Connection   con, PreparedStatement   ps, ResultSet   rs) {
169         close(rs);
170         close(ps);
171         close(con);
172     }
173     /**
174      * Check if a certain column exists in the database.
175      * @param con the connection to use, not closed by this method
176      * @param table the table where the column perhaps exists
177      * @param col the name of the column to check for
178      * @return true if the column exists, falst if not (the database throws an SQLException)
179      */
180     public static boolean columnExists(Connection   con, String   table, String   col) {
181         PreparedStatement   st = null;
182         try {
183             st = con.prepareStatement("select "+col+" from "+table+" where 1=0");
184             st.executeQuery();
185             return true;
186         } catch (SQLException   e) {
187             return false;
188         } finally {
189             JDBCUtil.close(st);
190         }
191     }
192 }
193
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags