Java JDBC using SQLite/Metadata

Working with metadata
The point always comes with working with database objects when you need to understand more about the nature of the database structure than perhaps you do at the time of writing the code. Database tables get changed, a column type may be changed from a date to string type for instance, or a new column may be added or deleted; potentially you may have to reference a table which has not even yet been built or which is constructed on the fly. It is at this juncture that a rather handy facet of JDBC can be brought to bear: the metadata interfaces ResultSetMetaData and DatabaseMetaData.

It is important to remember when working with resultsets is that arrays are constructed from a SQL perspective, i.e. they start with an index with a value of 1 and not 0. You should also note that there are potentially issues with SqLite and JDBC when working with metadata on a table with 0 rows; rsmd.getColumnType for example, won't work, whereas rsmd.getColumnName curiously will.

Let's consider the following rudimentary method from which we can know the extent of the columns which any arbitrary ResultSet is returning:

So, using the previous Db.executeQry(java.lang.String) wrapper method we can now do something very simple like:

Now we need to know the column types.....

This will return an array of integers expressing the column types which are in our ResultSet. We may need to handle these integer values as strings, so the alternative way of doing this is as follows:

The correlation between the integer array and the string array is an aspect of the java.sql.Types class. Either way will work for programmatic purposes.

So we now know the ResultSet's column extent and its data types. Now we need to know the column names so we can reference them. Here again ResultSetMetaData provides the answer:

So, using the previous Db.executeQry wrapper method we can now begin to handle our unknown and/or unforeseeable database structure: