3

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

}
Freeman
  • 83
  • 1
  • 3
  • 9
  • Why not use the MySQL date datatype (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-types.html) and make configure the column as default? – Micha Wiedenmann May 20 '13 at 08: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. May 20 '13 at 08:50
  • Check your Query syntax.. something is wrong there.. post your query code – Lakshmi May 20 '13 at 08:55
  • dear Micha Wiedenmann column type is date – Freeman May 20 '13 at 09:25
  • dear N.B I read it & tried but i didn't get correct result – Freeman May 20 '13 at 09:26
  • dear Lakshmi I edited my code please tell me your note – Freeman May 20 '13 at 09:27
  • **Avoid** using `Statement`. Rather, used `PreparedStatement` when storing `Objects` that aren't primitive. – Buhake Sindi May 20 '13 at 09:48
  • 1
    Also try enclosing your date String 'date_str' in single quotes. – Raza May 20 '13 at 09:52
  • dear Raza now I can insert to MSQL table 'Thu Feb 02 00:00:00 GMT-08:00 2012' but how I can convert this type to 05-05-2012 some thing like that – Freeman May 20 '13 at 10:18

2 Answers2

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.

Community
  • 1
  • 1
Buhake Sindi
  • 87,898
  • 29
  • 167
  • 228
  • 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 May 20 '13 at 09:45
  • 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 – Freeman May 20 '13 at 10:10
  • 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`. – Buhake Sindi May 20 '13 at 11:00
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);
Enrico Bianchi
  • 1,901
  • 1
  • 10
  • 8