15

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

zSynopsis
  • 4,854
  • 21
  • 69
  • 106

10 Answers10

13

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

Jack Leow
  • 21,945
  • 4
  • 50
  • 55
  • Why is it considered anti oop? Can you explain. I'm new to this so it would be helpful. Thanks! – zSynopsis Jul 04 '09 at 01:19
  • 1
    @Jack Leow: That's not anti OO, It may just have a different level of OO abstraction. Martin Fowler explains it brilliantly in his book PEAA as Domain Logic Patterns: http://www.martinfowler.com/eaaCatalog/ while some systems may find helpful to use TransactionScript ( pretty much what zSysop is using ) for other it may be too hard or even impossible to maintain it and use "Domain Model" instead. For small apps "Domain Model" may be overkill. It is very interesting subject. – OscarRyz Jul 04 '09 at 01:30
10

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 );
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
OscarRyz
  • 196,001
  • 113
  • 385
  • 569
  • 1
    the method `setDate` accepts a java.sql.Date and not a java.util.Date. – cd1 Jul 04 '09 at 01:04
  • 1
    @CD1: Thanks. It is corrected now. @zSisop:java.sql.Date and java.sql.TimeStamps are different objects, if you want to store the date and the hour, you should use the later. – OscarRyz Jul 04 '09 at 01:09
  • This should be the accepted Answer. Using a `PreparedStatement` cures the problem of SQL injection hacking, so it should almost always be used. The use of `java.sql.Date` rather than strings is a better practice. – Basil Bourque Jul 03 '15 at 17:53
  • I did some significant editing of this Answer, such as replacing deprecated code. Also, users of Java 8 and later should read [my own Answer] as an addendum to this Answer (making use of new java.time package). – Basil Bourque Jul 03 '15 at 19:00
  • I was getting invalid datatype error even when the same object value would work with spring jpa but won't work with jdbctemplate, so using format for simple insert values worked. – Smart Coder Apr 08 '22 at 13:38
7

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')
)
Bozho
  • 588,226
  • 146
  • 1,060
  • 1,140
Brian
  • 13,412
  • 10
  • 56
  • 82
  • Do you know if there is anyway i can get the java.util.Date to output the date in the format '01/01/2009'? – zSynopsis Jul 04 '09 at 01:08
  • 3
    @zSyspo: Yes String s = new SimpleDateFormat("dd/MM/yyyy").format( aDate ); will do – OscarRyz Jul 04 '09 at 01:11
  • 1
    Caution: SimpleDateFormat is not Thread safe. Make a simple wrapper of your own which is Thread safe. public class ThreadSafeSimpleDateFormat { private DateFormat df; public ThreadSafeSimpleDateFormat(String format) { this.df = new SimpleDateFormat(format); } public synchronized String format(Date date) { return df.format(date); } public synchronized Date parse(String string) throws ParseException { return df.parse(string); } } – Brian Jul 04 '09 at 01:26
  • Thanks alot Brian! You've been vry helpful. :D – zSynopsis Jul 04 '09 at 01:35
  • it's not good to impose any format on the database - the format is pure presentation reponsibility – Bozho Aug 30 '10 at 08:52
  • 1
    When used in the manner suggested here, the fact that SimpleDateFormat is not thread safe is not an issue. It is only a problem if you plan to share an instance between threads. Forcing threads to synchronize on a shared instance is probably a worse alternative than just instantiating a new one for each thread. – Nate C-K Oct 11 '11 at 15:58
6

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 ) ;
Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
1

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

nightingale2k1
  • 10,095
  • 15
  • 70
  • 96
0

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

Look at the api for setDate and the examples

fortran
  • 74,053
  • 25
  • 135
  • 175
0

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

objects
  • 8,637
  • 4
  • 30
  • 38
0
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
Kjuly
  • 34,476
  • 22
  • 104
  • 118
user3629370
  • 1
0

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')
)
Madhuka Dilhan
  • 1,396
  • 1
  • 14
  • 21
-1
VALUES ('"+user+"' , '"+FirstTest+"'  , '"+LastTest+"'..............etc)

You can use it to insert variables into sql query.

Nugroho Prayogo
  • 1
  • 2