3

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.

John Humphreys
  • 37,047
  • 37
  • 155
  • 255
  • Have you considered creating a stored procedure that accepts a table as a parameter? (not sure if Java supports this, just throwing it out there) – DJ Quimby Sep 14 '12 at 18:49
  • I would recommend that you use a class to represent the fields in your table instead of an array as it will greatly increase the readability in comparison to using an array! – Chetan Kinger Sep 14 '12 at 19:00

3 Answers3

1

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.
matt b
  • 138,234
  • 66
  • 282
  • 345
  • The number of columns that a table has cannot be changed at runtime! Why take the overhead of constructing a query at runtime when you can use a PreparedStatement? – Chetan Kinger Sep 14 '12 at 19:09
  • Because the array passed to the method *is* changing at runtime, or at least I assume so, therefore sometimes different columns will need to be specified in the INSERT. Although if your suggestion is to use PreparedStatement.setNull(), that makes a lot of sense too. – matt b Sep 14 '12 at 19:18
  • Thanks :) I'm going to play with these two options and see how they work. – John Humphreys Sep 14 '12 at 19:35
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!

Sujay
  • 6,753
  • 2
  • 30
  • 49
  • Would the ResultSetMetaData provide column names for columns that were null in the result? – John Humphreys Sep 14 '12 at 19:33
  • @w00te: Yes. Since you're doing a `Select * from...`, it would fetch all the columns. – Sujay Sep 14 '12 at 19:34
  • @w00te: Sure, give it a try. I had an almost similar issue in which I came up with this solution. I'm guessing it would work for you as well :) :) – Sujay Sep 14 '12 at 19:36
0

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.

Alex
  • 1,470
  • 1
  • 11
  • 28