Prepared Statement to DB setting variable type handling

Question

This question is to do with generating a preparedstatement from a hash of strings and dealing with some strings that are Date, Times and Int.

I have a Database, the column names of this database are stored in list "columns".

I also have a hashmap "pdf" which stores fieldnames and values from a PDF document.

The code below looks for a matching column name from the DB against the pdf hashmap and if found inserts it.

StringJoiner col = new StringJoiner(",");
StringJoiner val = new StringJoiner(",");
//First Iteration: Create the Statement
for(String c : columns) {
//Your PDF has a matching formfield 
   if(pdf.hasKey(c)) {
   col.add(c);
   val.add("?");
   }
 }
 String sql = String.format("INSERT INTO table (%s) VALUES (%s)",     col.toString(), val.toString());
 try(PreparedStatement insert = con.prepareStatement(sql)) {
   //Insert position in statement
   int pos = 0;
   //Second iterations: Bind the values to the statement
   for(String c : columns) {
     //Your PDF has a matching formfield 
    if(pdf.hasKey(c)) {
       insert.setString(++pos, pdf.get(c));
   }
 }
     insert.executeUpdate();
     } catch (SQLException e) {
       e.printStackTrace();
 }

This works well, but I need to handle a few cases. The pdf has the following fields which are Int and Date, Time:

Age (Int) DOB (Date) Score (Int) DateStart (Date) TimeStart (Time)

I also want to modify the DB to just have a single DateTimeStart field which will hold both.

Now I did have a go at handling this with something like:

                if (c.toLowerCase().contains("date")) {
                System.out.println("A Date field has been found: " +c);
                DateTimeFormatter formatter = DateTimeFormat.forPattern ("dd/mm/yyyy");
                DateTime startdt = formatter.parseDateTime(pdf.get(c));
                insert.setDate(++pos, startdt);
            }
            if (pdf.containsKey(c) && !c.toLowerCase().contains("date")) {
                insert.setString(++pos, pdf.get(c));
            }

But this doesnt work. For starters setDate doesnt accept DateTime objects, even if they only contain a date. Also trying to get my head around the new prepared statement with the database now only containing "DateTimeStart" instead of "DateStart" and "TimeStart" gave me a headache.

Any help here would be appreciated. I am already using Joda since its Java7.

Cheers

-Al

                if (c.toLowerCase().contains("date")) {
                    System.out.println("A Date field has been found: " +c);
                    DateTimeFormatter formatter = DateTimeFormat.forPattern("dd/mm/yyyy kk:mm");
                    long millis = formatter.parseMillis(pdf.get("DateStart") +" " +pdf.get("TimeStart"));
                    Timestamp timeStamp = new Timestamp(millis);
                    insert.setTimestamp(++pos, timeStamp);
            }

score 1 · Accepted Answer · answered Mar 10 '16 at 21:23

1

Standard JDBC accepts date/time only as instances of

java.sql.Date       (date only)
java.sql.Time       (date/time with 1 second resolution)
java.sql.Timestamp  (date/time with fractional second resolution)

You must convert your Joda objects into one of these, and use the correct method (setDate, setTime or setTimestamp) on PreparedStatement.

answered Mar 10 '16 at 21:23

Jim Garrison

85,615
20
155
190

Thanks @Jim So it would appear I need to use setTimestamp to do the insert? – Al Grant Mar 10 '16 at 21:27
1

I think that's what I said in my answer... you choose what precision you want, convert your timestamp to one of the `java.sql` classes and use the correct `set` method on `PreparedStatement`. – Jim Garrison Mar 10 '16 at 21:58
Thanks @Jim so I will end up storing my DateTime in the DB (MS Access) as a milliseconds time value. Thats pretty user unfriendly? Still working out how to convert my date string to a milliseconds value thats accepted by JDBC. – Al Grant Mar 10 '16 at 22:03
No, it will be stored in whatever native format the database uses for date/time. You're confusing the interface (`java.sql.Timestamp`) with the implementation at the back end. If your DB column is a date/time it will stay that way and JDBC will ensure the conversion is correct. You really should experiment with this to see what you're going to get. You could answer a lot of your own questions. Experimentation is the best way to learn. – Jim Garrison Mar 10 '16 at 22:15
Hi @Jim. Thanks for the replies. I have done a bit of experimenting and I think I am close with the code I have added to the question (see above) – Al Grant Mar 10 '16 at 23:21
Hi @Jim. Thanks I got it to go. Cheers again. – Al Grant Mar 10 '16 at 23:30
If you found my answer helpful it is expected that you will upvote it and if it solved your problem, mark it accepted by clicking on the checkmark above left. – Jim Garrison Mar 11 '16 at 16:32

Prepared Statement to DB setting variable type handling

1 Answers1