PreparedStatement.setString() call behaving oddly

This might be really simple, but I do not understand what is going wrong.

As background, I use PreparedStatements with template queries for efficiency. I invoke setString(index, argument) to populate portions of the template, and after all fields are populated, I invoke execute statement.

Here's a snippet of how things currently are (and it works of course):

   stmnt.setInt(1, node.getI_ID());
   stmnt.setString(2, node.getTime());
   stmnt.setInt(3, node.getMemoryAddress());
   stmnt.executeUpdate();

getI_ID returns a 5 digit int number like 10014, same with getMemoryAddress. getTime returns a datetime like "2011-03-14_13:23:00".

Th开发者_开发百科e goal is that when the query finally executes for the hour of this datetime to but increased by one, but I'm contrained by the fact that the value returned from getTime cannot be modified, i.e. this should be done by adding some quick and dirty sql code.

Here's what I initially came up with:

   stmnt.setInt(1, node.getI_ID());
   stmnt.setString(2, node.getTime() + " interval 1 hour");
   stmnt.setInt(3, node.getMemoryAddress());
   stmnt.executeUpdate();

However, when executed, the unmodified value of getTime is should up in my newly added rows. Are there conditions on the string that apply when PreparedStatement converts the String into a VARCHAR or DATETIME? Or perhaps the string much not contain multiple tokens or something?

Also, I can't add " interval 1 hour" to the query template because not all nodes need to add an hour on.

Any help is much appreciated. Thanks.

In the statement

   stmnt.setString(2, node.getTime() + " interval 1 hour");

the string concatenation is happening in Java, not SQL. If node.getTime() is returning, say "2011-03-14_13:23:00" you are sending the following value in setString():

"2011-03-14_13:23:00 interval 1 hour"

which is probably not what you wanted. The "interval 1 hour" does not modify the SQL, it is part of the value given to the SQL statement.

If you want to increment the hour by 1 you should parse the string value into a GregorianCalendar, increment the hour there, then format the result and send that to the SQL statement. For example:

    DateFormat df = new SimpleDateFormat("yyyy-MM-dd_HH:mm:ss");
    String input = "2011-03-14_13:23:00";
    Date d = df.parse(input);
    GregorianCalendar c = new GregorianCalendar();
    c.setTime(d);
    c.add(Calendar.HOUR_OF_DAY, 1);
    String output = df.format(c.getTime());

In addition to what Jim Garrison has said:

Don't use setString() for date or timestamp columns.

Use setDate() or setTimestamp() with an instance of java.sql.Date or java.sql.Timestamp instead.

That way you are shielded from any String-to-Date conversion that might behave differently depending on the NLS settings of the client and the database. Passing first class date or timestamp objects is much safer.