Is it possible to map basic JDBC SQL types to a Java class?

Question

When retrieving a column from a ResultSet object using the getObject method, the class is chosen by the JDBC driver. I am looking for a way to choose which class the column is retrieved as at runtime.

In the example below type Class1 is created with variable T as an Integer.

Class Class1<T>
{
  public T getColumn(DataSource ds)
  {
    T value = null;
    Connection con = null;

    try
    {
      con = ds.getConnection();
      Statement st = con.createStatement();
      ResultSet rs = st.executeQuery("select 1 from dual");
      rs.next();
      Object o = rs.getObject(1); // I want an Integer but a BigDecimal is created!
      value = (T) o; // ClassCastException here!
      rs.close();
      st.close();
    }
    finally
    {
      if (con != null) { con.close(); }
    }

    return i;
  }
}

The ResultSet class provides a method called getObject that accepts a column argument and a type Map argument. This type Map is only used for UDT (user defined types). I need this functionality applied to basic SQL types as well. I know I can implement a solution to this using a switch/case statement but I'm trying to avoid that. Is this possible?

EDIT

I should've mentioned I am on Java 1.6. It looks like this may have been solved in 1.7. Thanks JB Nizet for the reference.

I wonder how the cast (T) could work. Because of type erasure I would expect that the cast does not work. There is even a common workaround for that: passing the desired type as a Class-Object into the Method. See http://stackoverflow.com/questions/14524751/cast-object-to-generic-type-for-returning — treeno, Aug 21 '14 at 20:37
http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getObject-int-java.lang.Class- — JB Nizet, Aug 21 '14 at 20:38
Is there a reason you aren't using a higher level API for data access? — Ben Potter, Aug 21 '14 at 20:49
JB - Thanks but I forgot to mention I'm on Java 1.6.Ben, can you be more specific? — Rob Benton, Aug 21 '14 at 22:05
Can you provide an example of why you don't know column type until runtime? It seems that you should always know type of column at compile time. — Jeff Miller, Aug 22 '14 at 13:12
Jeff, this logic is for developers who consume my application to add values to a list based on a SQL statement. In my example above Class1 is a class that represents an underlying Object type T and another developer would define a SQL statement that returns a list of T values also stored in Class1. That's a simplified explanation of what I'm doing. — Rob Benton, Aug 22 '14 at 14:15
letting you know i borrowed this code for another question http://stackoverflow.com/a/25448649/217324 in order to point out a bug in it — Nathan Hughes, Aug 22 '14 at 14:47

score 1 · Answer 1 · answered Aug 21 '14 at 21:16

You may have to write your own code. You may have to perform casting of the data you receive. To avoid switch/case, you can use an exact ORM tools like Hibernate, which will allow your Pojo to have exact names like that of table columns and they automatically populate your Pojo with the data from columns. After this you can write your data conversion code for each column. An ORM tool like Hibernate, MyBatis will do the mapping for you.

Rob Benton · Accepted Answer · 2014-08-22T15:39:00.087

JB Nizet has provided the answer for Java 1.7: http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getObject-int-java.lang.Class-

For earlier versions of Java, DolphinJava is correct. I will have to write my own code.

How I Solved It

Keeping with my example above using Class1, I created sub-classes that extend Class1 with T being the type needed and then made type-specific methods that call the getColumn method in Class1:

public Class1<T>
{
  public Object getColumn(DataSource ds)
  {
    Object o        = null;
    Connection con  = null;

    try
    {
      con           = ds.getConnection();
      Statement st  = con.createStatement();
      ResultSet rs  = st.executeQuery("select 1 from dual");
      rs.next();
      Object o      = rs.getObject(1);
      rs.close();
      st.close();
    }
    finally
    {
      if (con != null) { con.close(); }
    }

    return o;
  }
}

Class1Integer extends Class1<Integer>
{
  public Integer getIntegerColumn()
  {
    Object o  = getColumn(datasource);
    Integer i = new Integer(o);
    return i;
  }
}

Is it possible to map basic JDBC SQL types to a Java class?

2 Answers2

Linked