How to insert Date into MySQL Database table in Java?

Question

How do I insert date, without time, to MySQL database table? I tried these codes but I get the following exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Mar 05 00:00:00 GMT-08:00 2014,1,1)' at line 1

NOTE: In MySQL table, data type of this column I chose date datatype

String Date = "\\d\\d\\d\\d\\D[0-1][0-9]\\D[0-3][0-9]";
while (DateMatcher.find())  {   
    String date = DateMatcher.group().trim();

    DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
    Date myDate = formatter.parse(date);

    db.insert_date(myDate,2,1);           
}

I've problem only with date part Query for inserting Date :

// insert DATE
public void insert_date(Date  date_str, int sentence_id ,int document_id ){
       // Statements allow to issue SQL queries to the database

    try {
        statement = connect.createStatement();

        System.out.println(  "insert into Date(date_Str,Sen_id,doc_id) " +
                 " values(" + date_str + "," +  sentence_id + "," + document_id +  ")"
                 );


        statement.executeUpdate(

         "insert into test.Date(date,Sen_id,doc_id)" +
         " values(" + date_str + "," +  sentence_id + "," + document_id + ")"
          );
    }
    catch(Exception e ){System.out.println(e);};
    // Result set get the result of the SQL query

}

Why not use the MySQL date datatype (dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html) and make configure the column as default? — Micha Wiedenmann
– Micha Wiedenmann, Commented May 20, 2013 at 8:49
It's visible where you went wrong, I just wonder why "programmers" don't read the error messages before asking questions. Pay attention to how you constructed MySQL query. Especially the quotes. Especially the one quote you didn't match. — N.B.
– N.B., Commented May 20, 2013 at 8:50
Check your Query syntax.. something is wrong there.. post your query code — Lakshmi
– Lakshmi, Commented May 20, 2013 at 8:55
Also try enclosing your date String 'date_str' in single quotes. — Raza
– Raza, Commented May 20, 2013 at 9:52

Community · Accepted Answer · 2017-05-23 12:00:16Z

11

First of all, to persist a java.util.Date into a database in Java, you will have to convert it to java.sql.Date. The fortunate thing about JDBC SQL Date is that it's a subclass of Java Date.

Therefore, to create a java.sql.Date from java.util.Date, you will have to do this:

DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
Date myDate = formatter.parse(date);
java.sql.Date sqlDate = new java.sql.Date(myDate.getTime());

db.insert_date(sqlDate);

Make sure that db.insert_date accepts only java.sql.Date.

Where you will have to call your PreparedStatement.setDate() function.

See the related question java.util.Date vs java.sql.Date.

edited May 23, 2017 at 12:00

CommunityBot

11 silver badge

answered May 20, 2013 at 8:51

Buhake Sindi

89.4k30 gold badges176 silver badges234 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Freeman Over a year ago

how i can convert this type of date ( Wed Mar 05 00:00:00 GMT-08:00 2014 ) cause while reading date it will converted to there type how I cab avoid this

Freeman Over a year ago

insert into Date(date_Str,Sen_id,doc_id) values(Wed Mar 05 00:00:00 GMT-08:00 2014,1,1) while inserting date it will converted to above type

Buhake Sindi Over a year ago

This is a different question all together. Just a hint, retrieve a java.sql.Date using PreparedStatement.getDate() and typecast it to java.util.Date.

Enrico Bianchi · Accepted Answer · 2019-01-20 16:07:46Z

0

NEVER use java.sql.Statement for insert data. Use ALWAYS java.sql.PrepartedStatement. This because is a better way to avoid SQL Injection (see this for an explain). So, in your case, you can insert the date in this way:

statement = connect.prepareStatement("INSERT INTO test.Date(date, Sen_id, doc_id) VALUES(?, ?, ?)");
statement.setDate(1, new java.sql.Date(date_str.getTime());
statement.setInt(2, sentence_id);
statement.setInt(3, document_id);

edited Jan 20, 2019 at 16:07

answered Jan 20, 2019 at 15:51

Enrico Bianchi

2,0311 gold badge12 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to insert Date into MySQL Database table in Java?

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related