Its very common to write sql, but the code is always the same. Of course you can use Hybernate and other frameworks for persistence but is not always comfortable.

Here I have the common functions that I use for small apps. I use them so often after all that I rather have them here at hand.

Listing 1: Database access functions.

 // Functions for update
 private int dbUpdate(String sql, Object args[]) throws SQLException {
   PreparedStatement ps = conn.prepareStatement(sql);
   if (args != null)
     for (int i = 0; i < args.length; i++)
       ps.setObject(i + 1, args[i]);
   int rows = ps.executeUpdate();
   ps.close();    
   return rows;
 }  
 private int dbUpdate(String sql, int arg) throws SQLException {
   return dbUpdate(sql, new Object[] { new Integer(arg)});
 }
 private int dbUpdate(String sql, String arg) throws SQLException {
   return dbUpdate(sql, new Object[] { arg });
 }
 
 // Functions for retrieval
 private int getInt(String sql) throws SQLException {
   Number n = (Number) getResult(sql, null);
   if (n == null)
     return 0;
   return n.intValue();
 }  
 private List getList(String sql, Object args[]) throws SQLException {
   PreparedStatement ps = conn.prepareStatement(sql);
   if (args != null)
     for (int i = 0; i < args.length; i++)
       ps.setObject(i + 1, args[i]);
   ResultSet rs = ps.executeQuery();
   ArrayList list = new ArrayList();
   while (rs.next())
     list.add(rs.getObject(1));
   rs.close();
   ps.close();
   return list;
 }
 private Object getResult(String sql, Object args[]) throws SQLException {
   PreparedStatement ps = conn.prepareStatement(sql);
   if (args != null)
     for (int i = 0; i < args.length; i++)
       ps.setObject(i + 1, args[i]);
   ResultSet rs = ps.executeQuery();
   Object r = null;
   if (rs.next())
     r = rs.getObject(1);
   rs.close();
   ps.close();
   return r;
 }