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!

Answer 2

A high-level algorithm:

1. Store an array of the column names (in same order as input parameters) - let's call this allColumnNames - as a field of the class.
2. When the method is called, create two new, empty List<String>s, one to represent the column names, one to represent the values - let's call these columnNamesList and columnValuesList.
3. Iterate over the input array, for each non-null element, add allColumnNames[i] to columnNamesList and values[i] to the columnValuesList.
4. Build your insert statement by concatenating together INSERT INTO tablename, a comma-separated list of each of the values in columnNames, VALUES, and a comma-separated list of each of values in columnValuesList.

Answer 3

Not sure about other database sytems, but in postgres you can insert a null value as follows:

CREATE TABLE test(
  id       SERIAL PRIMARY KEY,
  val1     INTEGER DEFAULT NULL,
  val2     INTEGER DEFAULT NULL,
  val3     INTEGER DEFAULT NULL
);

INSERT INTO test(val1, val2, val3) VALUES(DEFAULT, DEFAULT, DEFAULT);

You can also do set statements in the same way:

UPDATE test SET val1 = DEFAULT, val2 = 123, val3 = DEFAULT WHERE id = 3;

Thus,

Stringbuilder sqlStmt = new StringBuilder();
sqlStmt.append("INSERT INTO test(val1, val2, val3) VALUES(");
sqlStmt.append(((val1 == null) ? "DEFAULT" : val1.toString()));
sqlStmt.append(" , ");
sqlStmt.append(((val2 == null) ? "DEFAULT" : val2.toString()));
sqlStmt.append(" , ");
sqlStmt.append(((val3 == null) ? "DEFAULT" : val3.toString()));
sqlStmt.append(";");
statement.execute(sqlStmt.toString());

If you had a lot of these, it might make more sense to do it with a for-each loop.