0

I have a MySQL database to which I connect via java program. In 'locations' table I have a 'ExpDate' column of type 'Date'. I cannot write a date back as, I guess there is a format clash. Code:

resultSet = statement.executeQuery("SELECT * FROM locations WHERE LocID = '" + oldLocation + "'");  
resultSet.first();  // Position to row. 
Date oldLocExpDate = resultSet.getDate("ExpDate");

statement.executeUpdate("UPDATE locations SET 'ExpDate' = '" + oldLocExpDate + "' WHERE LocID = '" + newLocation + "'");

I get exception saying there is something wrong with the SQL syntax. I have also tried to get date input as string, but that didn't work.

How do I rewrite the last line of code so it writes the date into the database.

Vlad
  • 820
  • 10
  • 29
  • 4
    one word: `PreparedStatement` –  Feb 07 '15 at 10:38
  • possible duplicate of [how to store java date type to mysql date type?](http://stackoverflow.com/questions/2942270/how-to-store-java-date-type-to-mysql-date-type) – Basil Bourque Feb 07 '15 at 23:47

2 Answers2

1

Use java.sql.date to convert the date to sql format.

java.sql.Date oldLocExpDate = new java.sql.Date(resultSet.getDate("ExpDate").getTime());

istead of Date oldLocExpDate = resultSet.getDate("ExpDate");

HTH!

karthik manchala
  • 13,492
  • 1
  • 31
  • 55
  • Thank you for your reply. I have an import statement at the top: import java.sql.Date; and then I use Date. Shouldn't that be the same as writing java.sql.Date ? – Vlad Feb 07 '15 at 10:54
  • Yes, that should work. Did you check the output format of the date? – karthik manchala Feb 07 '15 at 10:59
1

Using a date in this manner will effectively call toString() on the Date object. This will create a String that represents the date, but it may not be the format expected by the database, which will be specific to the database session etc.

I see two solutions:

  • Use the existing code, and format the string using something like DateFormat to convert the java.sql.Date to the required String format. You'll also have to find out what that is for your particular configuration.
  • Move to using prepared statements. Prepared Statements have an API which accepts a Date as a parameter to the queries. By using this API, the database specific JDBC library should convert the Date into the appropriate format for the update.

On a side note, for basic programming, using the Statement API as you are doing is ok but in general terms the PreparedStatement API is far more useful and helps protect against things like SQL Injection as well as helping with performance.

Mike Curry
  • 1,609
  • 1
  • 9
  • 12