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:

  public int getColumnCount(ResultSet rs) {
    int iOutput = 0;
        try {
            ResultSetMetaData rsMetaData = rs.getMetaData();
            iOutput = rsMetaData.getColumnCount();
        } catch (Exception e) {
            System.out.println(e);
            return iOutput = -1;
        }
    return iOutput;
    }

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

[..]
   String mySqlSelect = "SELECT * FROM some_arbitrary_table" ;
   ResultSet myRs = db.executeQry(mySqlSelect);
   int iResult = getColumnCount(myRs);
   System.out.println(iResult);

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

 public int[] getType(ResultSet rs) {
        int iType[] = null;
        try {
            ResultSetMetaData rm = rs.getMetaData();
            int iArray[] = new int[rm.getColumnCount()];
            for (int ctr = 1; ctr <= iArray.length; ctr++) {
                int iVal = rm.getColumnType(ctr);
                iArray[ctr - 1] = iVal;
            }
            return iArray;
        } catch (Exception e) {
            System.out.println(e);
            return iType;
        }
    }

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:

  public 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;
        }
    }

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:

 public 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;
        }
    }

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

  [..]
  String mySqlSelect = "SELECT * FROM some_arbitrary_table" ;  
  ResultSet myRs = db.executeQry(mySqlSelect);  
  int iResult = getColumnCount(myRs);  
  String[] colNames = getColumnNameArray(myRs);
  String[] colTypes = getColumnTypeArray(myRs);
  for (int i=1;i<iResult;i++)
  {
     System.out.println("Column: " + i + " is " + colNames[i] + " and is type: " + colTypes[i]);
  }