CODEWITHSUNDEEP
javamysqlsqldatejdbc
PeakGen
PeakGen

Reputation: 23025

JDBC: Inserting Date values into MySQL

I would like to know how to set Date values to MySQL database, using Java JDBC. Following is my code.

 String lastCrawlDate = "2014-01-28"
 PreparedStatement p = con.prepareStatement("insert into JsonData (last_crawl_date) values(?))");

 p.setDate(1, Date.valueOf(lastCrawlDate));

As you can see, the lastCrawlDate is a String and in the format of yyyy/mm/dd.

Following is how my table looks like and how the Date field is defined.

enter image description here

How can I do this?

Upvotes: 8

Views: 39628

Answers (3)

Sotirios Delimanolis
Sotirios Delimanolis

Reputation: 279960

PreparedStatement provides the methods setDate(..), setTimestamp(..), and setTime(..) to replace placeholders (?) with date type fields. Use them

String lastCrawlDate = "2014-01-28";
Date utilDate = new SimpleDateFormat("yyyy-MM-dd").parse(lastCrawlDate);
// because PreparedStatement#setDate(..) expects a java.sql.Date argument
java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); 

Connection con = ...;
PreparedStatement p = con.prepareStatement("insert into JsonData (last_crawl_date) values(?))");
p.setDate(1, sqlDate);

Upvotes: 13

user586399
user586399

Reputation:

Immediately insert your date as a string

p.setString(1, lastCrawlDate);

Exactly as

insert into Table1 (last_crawl_date) values ('2014-01-28');

MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Using this approach, you can write a method to obtain a string from a given Date:

public static String getString(Date d) {
   return new SimpleDateFormat("yyyy-MM-dd").format(d);
}

Upvotes: 4

Abdul Manaf
Abdul Manaf

Reputation: 4888

Do like 

insert into JsonData (last_crawl_date) values(DATE_FORMAT('2014/02/19','%Y-%m-%d'));

Upvotes: -1

Related Questions