Java Date - Insert into database

Question

I need to figure out a way to insert a record with a java util Date field into a database and i m stuck   Does anyone know how i can do this  Right now i have something like           java util Date myDate   new java util Date  01 01 2009             sb append  INSERT INTO USERS            sb append   USER ID  FIRST NAME  LAST NAME  SEX  DATE              sb append  VALUES               sb append         userId                 sb append         myUser GetFirstname                    sb append         myUser GetLastname                    sb append         myUser GetSex                    sb append         myDate                  sb append                Util executeUpdate sb toString       But when i run something like this i get the error      The syntax of the string representation of a datetime value is incorrect   Heres what the sql statement looks like    INSERT INTO USERS  USER ID       FIRST NAME       LAST NAME       SEX       CRDATE   VALUES           user         FirstTest          LastTest          M         Thu Jan 01 00 00 00 CST 2009     Thanks

User · Accepted Answer

Granted  PreparedStatement will make your code better  but to answer your question you need to tell the DBMS the format of your string representation of the Date   In Oracle  you don t name your database vendor  a string date is converted to Date using the TO DATE   function   INSERT INTO TABLE NAME    date column  values    TO DATE  06 06 2006    mm dd yyyy

User · Answer

You should be using java sql Timestamp instead of java util Date  Also using a PreparedStatement will save you worrying about the formatting

User · Answer

The Answer by OscarRyz is correct  and should have been the accepted Answer  But now that Answer is out-dated   java time  In Java 8 and later  we have the new java time package  inspired by Joda-Time  defined by JSR 310  with tutorial  extended by ThreeTen-Extra project    Avoid Old Date-Time Classes  The old java util Date  Calendar  SimpleDateFormat  and java sql Date classes are a confusing mess  For one thing  j u Date has date and time-of-day while j s Date is date-only without time-of-day  Oh  except that j s Date only pretends to not have a time-of-day  As a subclass of j u Date  j s Date inherits the time-of-day but automatically adjusts that time-of-day to midnight  00 00 00 000   Confusing  Yes  A bad hack  frankly   For this and many more reasons  those old classes should be avoided  used only a last resort  Use java time where possible  with Joda-Time as a fallback   LocalDate  In java time  the LocalDate class cleanly represents a date-only value without any time-of-day or time zone  That is what we need for this Question   s solution    To get that LocalDate object  we parse the input string  But rather than use the old SimpleDateFormat class  java time provides a new DateTimeFormatter class in the java time format package   String input    01 01 2009    DateTimeFormatter formatter   DateTimeFormatter ofPattern   MM dd yyyy      LocalDate localDate   LocalDate parse  input  formatter       JDBC drivers compliant with JDBC 4 2 or later can use java time types directly via the PreparedStatement  setObject and ResultSet  getObject methods   PreparedStatement pstmt   connection prepareStatement       INSERT INTO USERS   USER ID  FIRST NAME  LAST NAME  SEX  DATE             VALUES                      pstmt setString  1  userId    pstmt setString  3  myUser getLastName       pstmt setString  2  myUser getFirstName         please use  getFir     instead of  GetFir      per Java conventions  pstmt setString  4  myUser getSex      pstmt setObject  5  localDate         Pass java time object directly  without any need for java sql       But until you have such an updated JDBC driver  fallback on using the java sql Date class  Fortunately  that old java sql Date class has been gifted by Java 8 with a new convenient conversion static method  valueOf  LocalDate     In the sample code of the sibling Answer by OscarRyz  replace its  sqlDate    line with this one   java sql Date sqlDate   java sql Date valueOf  localDate

User · Answer

pst setDate 6  new java sql Date txtDate getDate   getTime        this is the code I used to save date into the database using jdbc works fine for me   pst is a variable for preparedstatement txtdate is the name for the JDateChooser

User · Answer

if you are using mysql    you can save date as  2009-12-31  for example    update person set birthday date    2009-12-31    but i prefer to use jdbc although you have to create java sql Date        Date is kind of evil in this world

User · Answer

you can use this code date and time time is 24 h   INSERT INTO TABLE NAME    date column  values    TO DATE  2016-10-05 10 53 56    SYYYY-MM-DD HH24 MI SS

User · Answer

Before I answer your question  I d like to mention that you should probably look into using some sort of ORM solution  e g   Hibernate   wrapped behind a data access tier  What you are doing appear to be very anti-OO  I admittedly do not know what the rest of your code looks like  but generally  if you start seeing yourself using a lot of Utility classes  you re probably taking too structural of an approach   To answer your question  as others have mentioned  look into java sql PreparedStatement  and use java sql Date or java sql Timestamp  Something like  to use your original code as much as possible  you probably want to change it even more    java util Date myDate   new java util Date  10 10 2009    java sql Date sqlDate   new java sql Date myDate getTime      sb append  INSERT INTO USERS    sb append   USER ID  FIRST NAME  LAST NAME  SEX  DATE      sb append  VALUES       sb append                   sb append        Connection conn          you ll have to get this connection somehow PreparedStatement stmt   conn prepareStatement sb toString     stmt setString 1  userId   stmt setString 2  myUser GetFirstName     stmt setString 3  myUser GetLastName     stmt setString 4  myUser GetSex     stmt setDate 5  sqlDate    stmt executeUpdate       optionally check the return value of this call   One additional benefit of this approach is that it automatically escapes your strings for you  e g   if were to insert someone with the last name  O Brien   you d have problems with your original implementation

User · Answer

VALUES     user         FirstTest          LastTest                 etc    You can use it to insert variables into sql query

User · Answer

PreparedStatement  You should definitely use a PreparedStatement   Tutorial   That way you can invoke   pstmt setDate  1  aDate      The JDBC driver will do date-time handling appropriate for your particular database    Also  a PreparedStatement stops any SQL injection hacking attempts     very important   humor   It should look like this   SimpleDateFormat format   new SimpleDateFormat   MM dd yyyy         United States style of format  java util Date myDate   format parse   10 10 2009         Notice the   util   of package name   PreparedStatement pstmt   connection prepareStatement   INSERT INTO USERS   USER ID  FIRST NAME  LAST NAME  SEX  DATE         values                      pstmt setString  1  userId    pstmt setString  3  myUser getLastName       pstmt setString  2  myUser getFirstName         please use  getFir     instead of  GetFir      per Java conventions  pstmt setString  4  myUser getSex      java sql Date sqlDate   new java sql Date  myDate getTime         Notice the   sql    not  util   in package name  pstmt setDate  5  sqlDate       And that s it  the JDBC driver will create the right SQL syntax for you   Retrieving  When retrieving a Date object  you can use a SimpleDateFormat to create a formatted string representation of the date-time value    Here is one quick example line  but search StackOverflow for many more   String s   new SimpleDateFormat  dd MM yyyy   format  aDate

User · Answer

Use prepared statements  they have methods to set correctly parameters for each native Java type    Look at the api for setDate and the examples

[java] Java Date - Insert into database

Examples related to java

Examples related to sql

Examples related to date