Java > Open Source Codes > org > ashkelon > db > DBUtils


1 package org.ashkelon.db;
2 /**
3  * Copyright UptoData Inc. 2001
4  * March 2001
5  */
6
7 import java.sql.Connection ;
8 import java.sql.PreparedStatement ;
9 import java.sql.ResultSet ;
10 import java.sql.SQLException ;
11 import java.sql.Statement ;
12 import java.util.ArrayList ;
13 import java.util.HashMap ;
14 import java.util.Iterator ;
15 import java.util.List ;
16 import java.util.Map ;
17 import java.util.Set ;
18
19 import org.ashkelon.util.Logger;
20 import org.ashkelon.util.StringUtils;
21
22 /**
23  * A class containing various static methods that simplify
24  * or encapsulate database calls including:
25  * database queries, batch executions (jdbc 2.0 only),
26  * inserts, deletes, getting sequence.nextval effortlessly,
27  * logging sql exceptions fully (works with org.ashkelon.util.Logger)
28  * and more.
29  *
30  * @author Eitan Suez
31  */
32 public class DBUtils
33 {
34    /**
35     * inserts a record using conn into tableName.
36     * @param conn connection handle
37     * @param tableName table name
38     * @param fieldInfo name/value pairs to insert
39     */
40    public static void insert(Connection conn, String tableName, Map fieldInfo) throws SQLException
41    {
42       String cmd = null;
43       if (fieldInfo == null || fieldInfo.isEmpty())
44       {
45          throw new SQLException ("Cannot insert a record without field information");
46       }
47       cmd = "INSERT INTO " + tableName;
48       String fieldList = StringUtils.join(fieldInfo.keySet().toArray(), ",");
49       cmd += " (" + fieldList + ")";
50       cmd += " VALUES (" + StringUtils.join("?", ",", fieldInfo.size()) + ")";
51
52       PreparedStatement pstmt = conn.prepareStatement(cmd);
53       bind(pstmt, fieldInfo.values().toArray());
54       pstmt.executeUpdate();
55       pstmt.close();
56    }
57    
58    /**
59     * binds parameters to a prepared statement
60     * @param pstmt the prepared statement
61     * @param parms the list of objects to bind
62     */
63    public static void bind(PreparedStatement pstmt, Object [] parms) throws SQLException
64    {
65       for (int i=0; i<parms.length; i++)
66       {
67          if (parms[i] == null)
68             parms[i] = "";
69          // issue with a number of databases that cannot insert text containing the null character
70 // go figure out why some javadoc comment in j2se actually encodes the null character in
71 // a summary description!
72 if (parms[i] instanceof String )
73          {
74            parms[i] = StringUtils.stripNull((String ) parms[i]);
75          }
76          pstmt.setObject(i+1, parms[i]);
77       }
78    }
79    
80    /**
81     * executes a query (select statement) where return fields and constraints are
82     * somewhat parametrized
83     * @param conn connection handle
84     * @param tableName table name
85     * @param returnFields list of return fields
86     * @param constraints equality constraints (key is field name; value is constraint)
87     * @param orderBy sort order specification
88     *
89     * @return a preparedstatement ready to executeQuery to be called on it. caller
90     * must close preparedstatement of course.
91     */
92    public static PreparedStatement select(Connection conn, String tableName, String [] returnFields, Map constraints, String orderBy[])
93         throws SQLException
94    {
95       String cmd = "SELECT ";
96       if (returnFields == null || returnFields.length == 0)
97       {
98          cmd += "*";
99       } else
100       {
101          cmd += StringUtils.join(returnFields, ",");
102       }
103       
104       cmd += " FROM " + tableName;
105       
106       PreparedStatement pstmt = null;
107       
108       if (constraints!=null && !constraints.isEmpty())
109       {
110          cmd += " WHERE ";
111          Set keyset = constraints.keySet();
112          Iterator itr = keyset.iterator();
113          ArrayList list = new ArrayList ();
114          while (itr.hasNext())
115          {
116             list.add(itr.next() + "=?");
117          }
118          cmd += StringUtils.join(list.toArray(), " AND ");
119          
120          pstmt = conn.prepareStatement(cmd);
121          bind(pstmt, constraints.values().toArray());
122       }
123       
124       if (orderBy!=null && orderBy.length>0)
125       {
126          cmd += " ORDER BY " + StringUtils.join(orderBy, ",");
127       }
128       
129       if (pstmt == null)
130          pstmt = conn.prepareStatement(cmd);
131       
132       return pstmt;
133    }
134    
135    public static PreparedStatement select(Connection conn, String tableName, String [] returnFields, Map constraints)
136         throws SQLException
137    {
138       return select(conn, tableName, returnFields, constraints, null);
139    }
140    
141    public static PreparedStatement select(Connection conn, String tableName, String [] returnFields, String [] orderBy)
142         throws SQLException
143    {
144       return select(conn, tableName, returnFields, null, orderBy);
145    }
146    
147    public static PreparedStatement select(Connection conn, String tableName, Map constraints) throws SQLException
148    {
149       String [] returnFields = {"*"};
150       return select(conn, tableName, returnFields, constraints, null);
151    }
152    
153    public static int getCount(Connection conn, String tableName) throws SQLException
154    {
155       String sql = "select count(*) from " + tableName;
156       Statement stmt = conn.createStatement();
157       ResultSet rset = stmt.executeQuery(sql);
158       rset.next();
159       int count = rset.getInt(1);
160       rset.close();
161       stmt.close();
162       return count;
163    }
164
165    /**
166     * @param tableName the table to query
167     * @param constraints input to the construction of a where clause that defines the query
168     * to perform
169     * @return whether a query to tableName with constraints returned any matching rows
170     */
171    public static boolean exists(Connection conn, String tableName, Map constraints)
172         throws SQLException
173    {
174       PreparedStatement pstmt = select(conn, tableName, constraints);
175       ResultSet rset = pstmt.executeQuery();
176       boolean found = false;
177       if (rset.next())
178       {
179          found = true;
180       }
181       rset.close();
182       pstmt.close();
183       return found;
184    }
185    
186    /**
187     * performs a select query against tableName using constraints, and retrieves targetField
188     * for example: if have a table named 'address' with a field named 'city' a call such as:
189     * getObject(conn, "address", "city", constraints)
190     * might return 'Milano' if constrains contains [the map equivalent of] COUNTRY="Italy".
191     * @param tableName table name
192     * @param targetField the target field
193     * @param constraints query constraints
194     */
195    public static Object getObject(Connection conn, String tableName, String targetField, Map constraints) throws SQLException
196    {
197       String [] returnFields = new String [1];
198       returnFields[0] = targetField;
199       PreparedStatement pstmt = select(conn, tableName, returnFields, constraints);
200       ResultSet rset = pstmt.executeQuery();
201       Object result = null;
202       if (rset.next())
203       {
204          result = rset.getObject(1);
205       }
206       rset.close();
207       pstmt.close();
208       return result;
209    }
210    
211    /**
212     * deletes rowos from tableName using constraints
213     */
214    public static int delete(Connection conn, String tableName, Map constraints) throws SQLException
215    {
216       String sql = "DELETE FROM " + tableName + " WHERE ";
217
218       Set keyset = constraints.keySet();
219       Iterator itr = keyset.iterator();
220       ArrayList list = new ArrayList ();
221       while (itr.hasNext())
222       {
223          list.add(itr.next() + "=?");
224       }
225       sql += StringUtils.join(list.toArray(), " AND ");
226       
227       PreparedStatement pstmt = conn.prepareStatement(sql);
228       bind(pstmt, constraints.values().toArray());
229       int num_deleted = pstmt.executeUpdate();
230       pstmt.close();
231       return num_deleted;
232    }
233    
234    /**
235     * fully logs a sql exception (sqlstate, error code, exception reason,
236     * chained exceptions, & stack traces using org.ashkelon.util.Logger
237     */
238    public static void logSQLException(SQLException ex)
239    {
240       Logger log = Logger.getInstance();
241       log.error("Exception: "+ex.getMessage());
242       log.error("Error Code: "+ex.getErrorCode());
243       log.error("SQL State: "+ex.getSQLState());
244       ex.printStackTrace(log.getWriter());
245
246       // if out of database resources - exit
247 if (ex.getErrorCode() == -1311 || ex.getErrorCode() == -1310)
248       {
249          // System.exit(1);
250 ex.printStackTrace();
251       }
252       
253       while ((ex = ex.getNextException()) != null)
254       {
255          log.error("Chained sql exception:");
256          logSQLException(ex);
257       }
258    }
259    
260    public static void logSQLException(SQLException ex, String sql)
261    {
262       Logger log = Logger.getInstance();
263       log.error("Sql Statement: "+sql);
264       logSQLException(ex);
265    }
266    
267    /**
268     * if need nextval for a sequence, this method will make the oracle database
269     * call (select sequence.nextval from dual) for you and return the nextVal
270     * @param conn connection
271     * @param sequence name of sequence
272     * @return next value for sequence
273     */
274    public static int getNextVal(Connection conn, String sequence) throws SQLException
275    {
276       Statement stmt = conn.createStatement();
277       String sql = "SELECT " + sequence + ".NEXTVAL FROM DUAL";
278       ResultSet rset = stmt.executeQuery(sql);
279       int nextVal = rset.getInt(1);
280       rset.close();
281       stmt.close();
282       return nextVal;
283    }
284
285    /**
286     * use to submit a list of statements in batch mode (at one time) to the database
287     * uses jdbc 2.0 api
288     */
289    public static void submitBatch(Connection conn, List commands) throws SQLException
290    {
291       Logger log = Logger.getInstance();
292       
293       if (commands.isEmpty()) return;
294       Statement stmt = conn.createStatement();
295       
296       // 2 ways to implement this:
297 try
298       {
299          for (int i=0; i<commands.size(); i++)
300             stmt.addBatch((String ) commands.get(i));
301          stmt.executeBatch();
302       }
303       catch (SQLException ex)
304       {
305          if (ex.getMessage().toLowerCase().indexOf("not implemented") == -1)
306          {
307             throw ex;
308          }
309          
310          // batching not supported by jdbc driver, use alternate slow method
311 for (int i=0; i<commands.size(); i++)
312          {
313             log.debug((String ) commands.get(i));
314             stmt.execute((String ) commands.get(i));
315          }
316       }
317       
318       if (!conn.getAutoCommit())
319          conn.commit();
320       stmt.close();
321
322    }
323    
324    
325    public static Object getObject(Connection conn, String sql) throws SQLException
326    {
327       Statement stmt = conn.createStatement();
328       ResultSet rset = stmt.executeQuery(sql);
329       Object result = rset.getObject(1);
330       rset.close();
331       stmt.close();
332       return result;
333    }
334    
335    // unit test
336 public static void main(String args[]) throws Exception
337    {
338       Connection conn = DBMgr.getInstance().getConnection();
339       /*
340       List commands = new ArrayList(2);
341       commands.add("select * from classtype where id = 101");
342       submitBatch(conn, commands);
343        */
344       
345       /* lesson learned from following test:
346        * with oracle, bassing a boolean parameters to store into a number() type succeeds
347        * with mysql, it succeeds but true values are inserted as 0 (false) -- basically doesn't work
348        * moral of the story: pass Integer parameters if the underlying type is numeric, since booleans
349        * do not usually have their own types in databases (use numbers 0/1 for t/f)
350        */
351       Map fieldinfo = new HashMap ();
352       fieldinfo.put("id", new Integer (13176));
353       fieldinfo.put("name", "eitan");
354       fieldinfo.put("qualifiedname", "eitan suez");
355       //fieldinfo.put("isstatic", new Boolean(true));
356 fieldinfo.put("isstatic", new Integer (1));
357       DBUtils.insert(conn, "member", fieldinfo);
358       
359       
360       DBMgr.getInstance().releaseConnection(conn);
361       
362       
363    }
364    
365 }
366

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags