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.

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:

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:. 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:

You can do the same thing for the datatypes:

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...

For a full list of column types see the ../Appendix/.