JDBC date to SQL date

Question

I have been using Java to store some records from a csv file. One of these records is a date. The problem here is I am using JDBC to store these records in a database. Now, the Date object of Java is showing an error while putting it into the database. I have been stuck on this for a while. Please let me know how to solve this. I have used type Date in mysql for storing it into the database. Here is the part creating the problem.

DateFormat d1 = new SimpleDateFormat("yyyy-MM-dd");
Date d = new Date(); 
System.out.println(d1.format(d));
String sql = "INSERT INTO TESTING"+
            "VALUES("+"DATE_FORMAT("+d+","+"'%Y-%m-%d'"+"))";
stmt.executeUpdate(sql);

If I execute this directly in MySqlWorkBench, it is storing the date properly. But through JDBC it is a problem.

The error is as shown below :

com.mysql.jdbc.exceptions.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 'Jun 30 14:04:03 IST 2016,'%Y-%m-%d'))' at line 1
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
    at com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
    at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1402)
    at com.mysql.jdbc.Statement.executeUpdate(Statement.java:1317)
    at payex.writeDB(payex.java:221)
    at payextestdrive.main(payextestdrive.java:11)

Answer 1

The error message is pretty clear

right syntax to use near 'Jun 30 14:04:03 IST 2016,'%Y-%m-%d'))'

The format you provided is MMM DD HH:mm:ss Z YYYY, but the system expects %Y-%m-%d

Try

DateFormat d1 = new SimpleDateFormat("yyyy-MM-dd");
Date d = new Date(); 
System.out.println(d1.format(d));
String sql = "INSERT INTO TESTING"+
            " VALUES("+"DATE_FORMAT('"+d1.format(d)+"',"+"'%Y-%m-%d'"+"))";
stmt.executeUpdate(sql);

or simply

DateFormat d1 = new SimpleDateFormat("yyyy-MM-dd");
Date d = new Date(); 
System.out.println(d1.format(d));
String sql = "INSERT INTO TESTING"+
            " VALUES('"+d1.format(d)+"')";
stmt.executeUpdate(sql);

Answer 2

You add d.toString() to the SQL command when you want to add the formatted date string. Also a space is missing between the table name and VALUES:

String sql = "INSERT INTO TESTING VALUES(DATE_FORMAT(" + d1.format(d) +",'%Y-%m-%d'))";