aspose file tools*
The moose likes JDBC and the fly likes Mysql Java and Date Formats Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Mysql Java and Date Formats" Watch "Mysql Java and Date Formats" New topic
Author

Mysql Java and Date Formats

Russ Wright
Greenhorn

Joined: Dec 20, 2003
Posts: 7
Hi All!
I'm having some trouble getting a date from a Java application into a mySQL table. I have 2 fields on a form Donation Date and Donation Amount. The dates get all screwed up because the mysql format is YYYY-MM-DD and I'm entering the date MM/DD/YYYY and attempting to insert it into the database. Below is my code, can someone show me how to fix?
public void addDonation()
{
//test for values in both fields
if (txtDonationDate.getText().equals("") || txtDonationAmount.getText().equals(""))
{
lblStatus.setText("Incomplete Values! You must enter values in the Date and Amount field");
}
else
{
//test for a valid date
if (dateIsValid(txtDonationDate.getText())==false )
{
lblStatus.setText("No Donation Values!! You must enter values in the Date and Amount field");
}
else
{
try
{
//if both conditions pass insert rec
//TO DO ADD INSERT
Statement state = m_DBCnn.createStatement();
//build the sql statement to insert
String strSQL = "INSERT INTO donations (";
strSQL += "donorID, amount, donation_date ) VALUES ('";
//first name
strSQL += lblID.getText() + "', '" ;
//last Name
strSQL += txtDonationAmount.getText() + "', '" ;
//address1
strSQL += txtDonationDate.getText() + "')" ;
//give the user some feedback
lblStatus.setText("Sending query: " + m_DBCnn.nativeSQL(strSQL));
//exec the SQL String
int result = state.executeUpdate(strSQL);
//test for sucess
if (result == 1)
{
lblStatus.setText("Submitted - Insertion successful");

}
else
{
lblStatus.setText("Submitted Insertion unsuccessful");
}
//clear the fields
txtDonationAmount.setText("");
txtDonationDate.setText("");

//reget the donations
getDonations();
//display them
displayDonations();
}
catch(SQLException sqlx)
{
sqlx.printStackTrace();
}

}//end else date is valid
}//end else blank fields
}//end add donation

private static boolean dateIsValid(String p_Date)
{
boolean result = true;
try
{
DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US);
df.setLenient(false);

final String dateString = p_Date;
df.parse(dateString);
}
catch (Exception e)
{
result = false;
}

return result;
}//end date is valid
Faisal Khan
Ranch Hand

Joined: Jun 29, 2003
Posts: 285
I have not looked at your code but there are two solutions:
1. If you wish to leave the MySQL date format as is then use the approprotae formatters in Java to format the date into a suitable string, you can use SimpleDateFormat from java.text package.
2. Your other option, if you have acces, is to change the format of the date in the MySQL table.
HTH


The secret to creativity is knowing how to hide your sources.
Russ Wright
Greenhorn

Joined: Dec 20, 2003
Posts: 7
Faisal
I appreciate your response and indeed I agree with your assessment. However, I do not know how to change the date format in java. (I'm a bit of a beginner) Perhaps I should ask how do I through java code change the user input date format from MM/DD/YYYY to match the mysql format YYYY-MM-DD?
Regards
Russ
Faisal Khan
Ranch Hand

Joined: Jun 29, 2003
Posts: 285
Russ,
Like I said before you can use the SimpleDateFormat and here is an example of how you may use it, for more details, please refer to the API.

Then you can insert the nowDate into your table.
HTH
Ken Robinson
Ranch Hand

Joined: Dec 23, 2003
Posts: 101
Use a PreparedStatment with the setDate method. Once you parse the String into a java.util.Date, you are done. The underlying driver will take care of what is needed to get the Date object into the database.
This is preferable as it not only takes care of the insert, but also verifies the String is actually a Date as it will not parse from a String to java.util.Date if it is not.
Russ Wright
Greenhorn

Joined: Dec 20, 2003
Posts: 7
Kenneth
DO you have an example? I'm very new to the java language and I need some coaching.
Regards
Russ
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30764
    
156

Russ,
Sun's tutorial has a good example of using prepared statement. I agree with Kenneth that it is better to go with prepared statement than tweaking the date format. As an added bonus, your code will still work if you decide to use a different database in the future.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Faisal Khan
Ranch Hand

Joined: Jun 29, 2003
Posts: 285
The only thing I would add is that PreparedStatements can actually be significantly slower in many cases but that is not the subject matter being discussed, one can run a search in this forum to find some good discussions about that.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by Faisal Khan:
The only thing I would add is that PreparedStatements can actually be significantly slower in many cases but that is not the subject matter being discussed, one can run a search in this forum to find some good discussions about that.

The advantages to PreparedStatement far outweigh the performance cost. For dates, you abstract yourself from having to know which date format is acceptable for the specific database you are currently using( as is the problem here ), as well, preparedStatement does some special character escaping that will prevent your program from blowing up, and reduce the risk of SQL injection. If you include these steps manually using a statement, and the gap in performance slowly diminishes.
Jamie
majid nakit
Ranch Hand

Joined: Jun 26, 2001
Posts: 164
what about:
java.lang.Date javaDate;
PreparedStatement ps = connection.prepareStatement(QUERY);
ps.setDate(index, new java.sql.Date(javaDate.getTime()));
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Mysql Java and Date Formats