Java JDBC using SQLite/Working with data

JDBC provides a number of different ways of approaching data and data object manipulation. Typically these fall into three categories: methods which broker Prepared statements, methods which expect data to be returned from the database (these are methods which will invariably return a ResultSet) and finally methods which act on the database without expecting a return of information, e.g. an instruction to create a table or to delete some data from a table.

Methods using PreparedStatements

When and why to use PreparedStatement methods

There are strong reasons for using PreparedStatement (PS) methods to interact with the database, and this is particularly the case when the interactions are repetitious since the PS is sent to the database for compilation, which means that on subsequent calls the database can access the call without having to recompile it, resulting in faster execution time.

How to implement a PreparedStatement

As with other method calls using Statements, which are associated with a Connection object,a PreparedStatement also is associated with the Connection:

[.. pseudocode ..]
Connection conn = new Connection(); // whatever it might be
String psString = "UPDATE myTable SET myText = 'First Update'";
String psStringWithParms = "UPDATE myTable SET myText = ?";

// prepare & execute the psString 
PreparedStatement ps = conn.prepareStatement(psString);
ps.executeUpdate();

// now we will overwrite this using the parameterised psString
PreparedStatement psParms = conn.prepareStatement(psStringWithParms);
psParms.setString(1,"Revised update");
psParms.executeUpdate();

Understanding the data

It is not unusual to find yourself in a situation when working with databases to not know the column names or the types of data which will be returned by a given sql query. Fortunately, the JDBC class provides two very useful interfaces which can be exploited to resolve this: ResultSetMetaData and DatabaseMetaData.

The Java documentation for the resultset interface can be found here: [1]. The interface usefully exposes column names, and data types (and much more) in any given resultset instance.

You can for instance expose your column names with the following example method:

public static String[] getColumnNameArray(ResultSet rs) {
  String sArr[] = null;
  try {
    ResultSetMetaData rm = rs.getMetaData();
    String sArray[] = new String[rm.getColumnCount()];
    for (int ctr = 1; ctr <= sArray.length; ctr++) {
      String s = rm.getColumnName(ctr);
      sArray[ctr - 1] = s;
    }
    return sArray;
  } catch (Exception e) {
    System.out.println(e);
    return sArr;
  }
}

You can do the same thing for the datatypes:

public static String[] getColumnTypeArray(ResultSet rs) {
  String sArr[] = null;
  try {
    ResultSetMetaData rm = rs.getMetaData();
    String sArray[] = new String[rm.getColumnCount()];
    for (int ctr = 1; ctr <= sArray.length; ctr++) {
      String s = rm.getColumnTypeName(ctr);
      sArray[ctr - 1] = s;
    }
    return sArray;
  } catch (Exception e) {
    System.out.println(e);
    return sArr;
  }
}

By using the ResultSetMetaData getColumnCount() method we can save some unnecessary handing off to a Vector and recasting back to a String[] array because we can size our String[] array appropriately right from the get-go.

It may have occurred to you by now that apart from the ResultSetMetaData method invoked, these two blocks of code are functionally close to identical and that some rationalisation could be done here either with a third method which handles the iteration, or, possibly, uses java.lang.reflect to load the methods dynamically. In a production system you probably would want to steer clear of adding to the work done by the JVM in reflection (Java reflection is inherently expensive) but you could easily code a switch block to do the appropriate trivialisation, passing an additional argument to indicate which method is to be invoked in constructing the String[] array.

Now let's put this together a little by pretending we're creating a class to query a database table called testme, whose column names and types are unknown to us...

import java.sql.ResultSetMetaData;

public class MyDbTest
{
  public void main(String[] args)
  {
    SqliteDb db = new Sqlite("C:/DbStore/test.db");
    String sqltest = "SELECT * FROM testme";
    Vector<Object> v = new Vector<Object>();

  try
  {
    ResultSet rs = db.executeQry(sqltest);
    String[] columnNames = getColumnNameArray(rs);
    String[] columnTypes = getColumnTypeArray(rs);
    //iterate the rows in the result set...
    while(rs.next())
    {
      // now walk each column in the array...
      for (int i = 1; i < columnNames.length; i++)
      {
        Object o = rs.getObject(columnNames[i]);

        if (columnTypes[i].toUpper().equals("VARCHAR"))
        {
          sVal = o.toString();
        }
[etcetera ..]

[..]
    }
  }
  catch (Exception e)
  {
[..]
  } 
}

For a full list of column types see the Appendix.