Dynamic population of SQL in Java from array with nulls

Question

To give a simple example of my problem:

//Create database table allowing nulls.
CREATE TABLE test(one int not null, two int null, three int not null);

Now, in Java I have an array of objects matching the database content. I need to create an insert/update/delete statement for this database table using the array values.

Object[] arr = { 4, null, 1 };
String sql = GenereateInsert(arr);

This gets difficult though because I need to leave out null fields from the insert in advance. E.g. if two above is not null:

 INSERT INTO test(4, 2, 1);

everything is easy. But if it is null, I need the statement to be:

 INSERT INTO test(one, three) values(4, 1);

The tables I'm dealing with are 50 columns and up, so I don't want to do this manually, it will be unmanageable. So, how do people address this problem in general? It must be a common situation.

PS: I don't want to use a full ORM library, that seems like overkill.

Answer 1

I would suggest something like this:

1. Run a query like SELECT * FROM <TABLE_NAME> LIMIT 1. Use the ResultSetMetaData to retrieve the columnNames and their columnType. Store it in a HashMap and the keys in a ArrayList

2. Now create a PreparedStatement using the keys from the ArrayList as the columnNames. I guess that would not be hard to do

3. Now given your data in the array, the fact that you know what SQL type they're from the HashMap, you can easily use this information to map the values that you get from your array in the ? of your generated PreparedStatement.

Now the question is how to deal with null. Well since you know the type of column, if the value that you get is null then you can use PreparedStatement.setNull(...) to set them as null.

Also, just wanted to point out that according to this document, there's an important guide to the setXXX(...) of JDBC:

6.1.5 Sending JDBC NULL as an IN parameter

The setNull method allows a programmer to send a JDBC NULL (a generic SQL NULL) value to the database as an IN parameter. Note, however, that one must still specify the JDBC type of the parameter.

A JDBC NULL will also be sent to the database when a Java null value is passed to a setXXX method (if it takes Java objects as arguments). The method setObject, however, can take a null value only if the JDBC type is specified.

This would imply that you can send null as the value for the parameter of your setXX(...) method and you really not have to call the setNull(..) method explicitly.

I hope this works for you!