Java Date - Insert into database

Question

Hi all,

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

Answer 1

Use prepared statements, they have methods to set correctly parameters for each native Java type.

Look at the api for setDate and the examples

Answer 2

You should definitely use a prepared statement.

That way you can invoke.

pstmt.setDate( 1, aDate );

And the JDBC driver will do it for you ( plus you will help to prevent SQL injection )

It should look like this:

    java.util.Date myDate = new java.util.Date("10/10/2009");

    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.getFirtName() ); // please use getFir.. instead of GetFir ... 
    pstmt.setString( 4, myUser.getSex() );
    pstmt.setDate( 5, new java.sql.Date( myDate.getTime() ) ); // corrected!!

Ant that's it, the jdbc driver will create the right syntax for you.

EDIT

As per the format question, using SimpleDateFormat will do:

String s = new SimpleDateFormat("dd/MM/yyyy").format( aDate );

Answer 3

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

Answer 4

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')
)

Answer 5

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).

Answer 6

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 ... :)

Answer 7

thank you this has been very unhelpful need to convert date format not hard code