Conditional Inserts with JDBC Prepared Statements

Published: July 16, 2008

Hey Mike:

I would like to write a conditional INSERT in SQL so I can use a prepared statement through JDBC. However, SQL supports IF only in SPL, the Stored Procedures Language. Is there another way to insert the record if doesn't exist?

--Avdhesh

For the reader's benefit, here is the SQL statement Avdhesh wants to run in JDBC:

if not exists (select 1 from cotable where coekey = ?) then
    insert into cotable
    (coekey, cocode, coname, coactv)
    values (?, ?, ?, 1);
end if

Unfortunately DB2 for i5/OS doesn't support dynamic scripting. Therefore conditional statements such as IF are not supported with the JDBC PreparedStatement.

One way to handle the problem would be to wrap the SQL code into an SQL stored procedure and then pass appropriate parameter values to the stored procedure. But if you don't want to hassle with writing a stored procedure, you can cram this conditional insert into one statement as follows:

insert into cotable
(coekey, cocode, coname, coactv)
Select ?,?,?,1
  From SysDummy1
 Where Not Exists
    (Select 1
       From cotable
      Where coekey=?)

The question marks represent parameter markers (roughly equivalent to host variables in pre-compiled SQL.) Values need to be assigned to each of these markers before the statement can execute successfully.

Take note that SysDummy1 is a special IBM one row table that can be used as a trick for these one row operation situations! This is because inserting parameter values from a one row table is equivalent to the INSERT/VALUES statement. Placing the NOT EXISTS predicate in the WHERE clause instead of using an IF statement still allows us to condition if the row should be inserted by testing whether the row already exists.

As a side note, SysDummy1 resides in the SysIBM schema so it should be part of the library list when using the *SYS naming convention or fully qualified (SYSIBM.SysDummy1) when using the *SQL naming convention. Alternatively, if you have a one row table in your own schema it can be substituted for SYSDUMMY1 as well.

Finally, there is one more thing to consider about parameter markers. In some cases, the data types of parameter markers can be inferred from the SQL statement itself, such as when a parameter marker is compared to a column in the WHERE clause. In this case the parameter marker is assumed to have a data type that is compatible with the data column it is being compared with. However, parameter markers in a SELECT list need to have their data types explicitly defined for DB2. This can easily be done with the CAST function as follows:

insert into cotable
(coekey, cocode, coname, coactv)
Select Cast(? As Int),
       Cast(? As VarChar(30)),
       Cast(? As Char(10)),
       1
  From SysDummy1
 Where Not Exists
    (Select 1
       From cotable
      Where coekey=?)

If you do not provide DB2 with the data type of the parameter marker in a Select list, the SQL statement execution will fail with the message "SQL0418 Use of parameter marker not valid." Notice in this case the fourth parameter marker (in the Where clause) does not need an explicit CAST because it is inferred from the data type of column COEKEY.

Here is a snippet of Java code that illustrates usage of a PreparedStatement against a similar conditional Insert to the one shown above:

Connection conn=DriverManager.getConnection("jdbc:as400://
myas400/i3;transaction isolation=none;driver=native;
naming=system","user","pwd");
String sql="Insert Into i3/Test1 (C1,C2,C3) " +
  "Select Cast(? As Int)," +
  "       Cast(? As VarChar(30))," +
  "       Cast(? As Double)" +
  "  From SysIBM/SysDummy1 " +
  " Where Not Exists " + 
  " (Select * " + 
  "    From i3/Test1 " + 
  "   Where C1=?)";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setInt(1, 3);               // Column 1
ps.setString(2, "Test It");    // Column 2
ps.setFloat(3,1.52f);          // Column 3
ps.setInt(4, 3);               // Where Clause
ps.execute();                  // Run the statement
ps.close();
conn.close();

For those unfamiliar with JDBC, the PreparedStatement object has a variety of set methods (setInt, setString, etc.) that are used to assign values to the parameter markers. Parameter markers are evaluated in a SQL statement from left to right, top to bottom. The first parameter to these set methods is the parameter number in the statement and the second parameter is the value to be assigned to that parameter marker.

So even though DB2 for i5/OS doesn’t support dynamic SQL, with a few tricks an acceptable substitute can be made.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Email him at the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot