Using setDate in PreparedStatement

Question

In order to make our code more standard  we were asked to change all the places where we hardcoded our SQL variables to prepared statements and bind the variables instead    I am however facing a problem with the setDate      Here is the code           DateFormat dateFormatYMD   new SimpleDateFormat  yyyy MM dd HH mm ss            DateFormat dateFormatMDY   new SimpleDateFormat  MM dd yyyy HH mm ss            Date now   new Date            String vDateYMD   dateFormatYMD format now           String vDateMDY   dateFormatMDY format now           String vDateMDYSQL    vDateMDY           java sql Date date   new java sql Date 0000-00-00       requestSQL    INSERT INTO CREDIT REQ TITLE ORDER  REQUEST ID                        ORDER DT  FOLLOWUP DT        values                             prs   conn prepareStatement requestSQL                    prs setInt 1 new Integer requestID                     prs setDate 2 date valueOf vDateMDYSQL                    prs setDate 3 date valueOf sqlFollowupDT      I get this error when the SQL gets executed       java lang IllegalArgumentException     at java sql Date valueOf Date java 138      at com cmsi eValuate TAF TAFModuleMain CallTAF TAFModuleMain java 1211    Should I use setString   instead with a to date

User · Accepted Answer

x2750  Using java sql Date  If your table has a column of type DATE    java lang String  The method java sql Date valueOf java lang String  received a string representing a date in the format yyyy- m m- d d  e g    ps setDate 2  java sql Date valueOf  2013-09-04      java util Date  Suppose you have a variable endDate of type java util Date  you make the conversion thus   ps setDate 2  new java sql Date endDate getTime      Current  If you want to insert the current date   ps setDate 2  new java sql Date System currentTimeMillis          Since Java 8 ps setDate 2  java sql Date valueOf java time LocalDate now           x2750  Using java sql Timestamp  If your table has a column of type TIMESTAMP or DATETIME    java lang String  The method java sql Timestamp valueOf java lang String  received a string representing a date in the format yyyy- m m- d d hh mm ss  f      e g    ps setTimestamp 2  java sql Timestamp valueOf  2013-09-04 13 30 00     java util Date  Suppose you have a variable endDate of type java util Date  you make the conversion thus   ps setTimestamp 2  new java sql Timestamp endDate getTime       Current  If you require the current timestamp   ps setTimestamp 2  new java sql Timestamp System currentTimeMillis          Since Java 8 ps setTimestamp 2  java sql Timestamp from java time Instant now      ps setTimestamp 2  java sql Timestamp valueOf java time LocalDateTime now

User · Answer

tl dr  With JDBC 4 2 or later and java 8 or later   myPreparedStatement setObject        myLocalDate         and     myResultSet getObject        LocalDate class     Details  The Answer by Vargas is good about mentioning java time types but refers only to converting to java sql Date  No need to convert if your driver is updated   java time  The java time framework is built into Java 8 and later  These classes supplant the old troublesome date-time classes such as java util Date   Calendar   amp  java text SimpleDateFormat  The Joda-Time team also advises migration to java time   To learn more  see the Oracle Tutorial  And search Stack Overflow for many examples and explanations   Much of the java time functionality is back-ported to Java 6  amp  7 in ThreeTen-Backport and further adapted to Android in ThreeTenABP   LocalDate  In java time  the java time LocalDate class represents a date-only value without time-of-day and without time zone   If using a JDBC driver compliant with JDBC 4 2 or later spec  no need to use the old java sql Date class  You can pass fetch LocalDate objects directly to from your database via PreparedStatement  setObject and ResultSet  getObject   LocalDate localDate   LocalDate now  ZoneId of   America Montreal       myPreparedStatement setObject  1   localDate          and     LocalDate localDate   myResultSet getObject  1   LocalDate class      Before JDBC 4 2  convert  If your driver cannot handle the java time types directly  fall back to converting to java sql types  But minimize their use  with your business logic using only java time types   New methods have been added to the old classes for conversion to from java time types  For java sql Date see the valueOf and toLocalDate methods   java sql Date sqlDate   java sql Date valueOf  localDate         and      LocalDate localDate   sqlDate toLocalDate      Placeholder value  Be wary of using 0000-00-00 as a placeholder value as shown in your Question   s code  Not all databases and other software can handle going back that far in time  I suggest using something like the commonly-used Unix Posix epoch reference date of 1970  1970-01-01    LocalDate EPOCH DATE   LocalDate ofEpochDay  0       1970-01-01 is day 0 in Epoch counting      About java time  The java time framework is built into Java 8 and later  These classes supplant the troublesome old legacy date-time classes such as java util Date  Calendar   amp  SimpleDateFormat   The Joda-Time project  now in maintenance mode  advises migration to the java time classes   To learn more  see the Oracle Tutorial  And search Stack Overflow for many examples and explanations  Specification is JSR 310   You may exchange java time objects directly with your database  Use a JDBC driver compliant with JDBC 4 2 or later  No need for strings  no need for java sql   classes   Where to obtain the java time classes     Java SE 8  Java SE 9  Java SE 10  and later   Built-in   Part of the standard Java API with a bundled implementation  Java 9 adds some minor features and fixes   Java SE 6 and Java SE 7   Much of the java time functionality is back-ported to Java 6  amp  7 in ThreeTen-Backport   Android   Later versions of Android bundle implementations of the java time classes  For earlier Android   lt 26   the ThreeTenABP project adapts ThreeTen-Backport  mentioned above   See How to use ThreeTenABP        The ThreeTen-Extra project extends java time with additional classes  This project is a proving ground for possible future additions to java time  You may find some useful classes here such as Interval  YearWeek  YearQuarter  and more

User · Answer

The problem you re having is that you re passing incompatible formats from a formatted java util Date to construct an instance of java sql Date  which don t behave in the same way when using valueOf   since they use different formats   I also can see that you re aiming to persist hours and minutes  and I think that you d better change the data type to java sql Timestamp  which supports hours and minutes  along with changing your database field to DATETIME or similar  depending on your database vendor    Anyways  if you want to change from java util Date to java sql Date  I suggest to use  java util Date date   Calendar getInstance   getTime    java sql Date sqlDate   new java sql Date date getTime             more code here prs setDate sqlDate

User · Answer

Not sure  but what I think you re looking for is to create a java util Date from a String  then convert that java util Date to a java sql Date   try this   private static java sql Date getCurrentDate String date         java util Date today      java sql Date rv   null      try            SimpleDateFormat format   new SimpleDateFormat  dd-MM-yyyy            today   format parse date           rv   new java sql Date today getTime             System out println rv getTime            catch  Exception e            System out println  Exception      e getMessage           finally           return rv                 Will return a java sql Date object for setDate     The function above will print out a long value   1375934400000

User · Answer

The docs explicitly says that java sql Date will throw         IllegalArgumentException - if the date given is not in the JDBC date escape format  yyyy- m m- d d       Also you shouldn t need to convert a date to a String then to a sql date  this seems superfluous  and bug-prone    Instead you could   java sql Date sqlDate    new java sql Date now getTime     prs setDate 2  sqlDate   prs setDate 3  sqlDate

User · Answer

If you want to add the current date into the database  I would avoid calculating the date in Java to begin with  Determining  now  on the Java  client  side leads to possible inconsistencies in the database if the client side is mis-configured  has the wrong time  wrong timezone  etc  Instead  the date can be set on the server side in a manner such as the following   requestSQL    INSERT INTO CREDIT REQ TITLE ORDER                         REQUEST ID  ORDER DT  FOLLOWUP DT                       VALUES    SYSDATE  SYSDATE   30          prs setInt 1  new Integer requestID      This way  only one bind parameter is required and the dates are calculated on the server side will be consistent   Even better would be to add an insert trigger to CREDIT REQ TITLE ORDER and have the trigger insert the dates  That can help enforce consistency between different client apps  for example  someone trying to do a fix via sqlplus

[java] Using setDate in PreparedStatement

❐ Using `java.sql.Date`

❐ Using `java.sql.Timestamp`

Examples related to java

Examples related to sql

Examples related to oracle

Examples related to jdbc

Examples related to prepared-statement