Insert date value in PreparedStatement

Problem

A simple table script in Oracle database.


CREATE TABLE DBUSER ( 
  USER_ID       NUMBER (5)    NOT NULL, 
  USERNAME      VARCHAR2 (20)  NOT NULL, 
  CREATED_BY    VARCHAR2 (20)  NOT NULL, 
  CREATED_DATE  DATE          NOT NULL, 
  PRIMARY KEY ( USER_ID ) 
 )

No idea how to insert current date value, e.g. “04/04/2011” into “CREATED_DATE” field, via JDBC PreparedStatement.


String insertTableSQL = "INSERT INTO DBUSER"
		+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
		+ "(?,?,?,?)";
preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setDate(4, ???);

Solution

The “preparedStatement.setDate()” method is accept a java.sql.Date parameter, so, you have to convert from java.util.Date to java.sql.Date.

For example, create a method to return current date, and convert it java.sql.Date :


private static java.sql.Date getCurrentDate() {
    java.util.Date today = new java.util.Date();
    return new java.sql.Date(today.getTime());
}

And set the returned date via preparedStatement.setDate().


String insertTableSQL = "INSERT INTO DBUSER"
	+ "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
	+ "(?,?,?,?)";
preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setDate(4, getCurrentDate());

Done.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
9 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
9 Comment authors
Tan Wee TeckFurqanManjultestUserAnyul Rivas Recent comment authors
newest oldest most voted
ajit
Guest
ajit

sir i hav a doubt
can u pls help me
pls tellme how to auto increment rollno field no for training n placement db
what value shoud i pas from jsp page using servelet

Tan Wee Teck
Guest
Tan Wee Teck

if (variable.getText().trim().isEmpty())
{ JOptionPane.showMessageDialog(null,”Please key-in the Data, Please.. \n”
+ “Thank you. “);
return false;}

hi, above is for if text field is empty.
may i know how does we do in date when using jdatechooser?
please.

Furqan
Guest
Furqan

what if we have date in string what to do instead of SYSDATE / getCurrentDate

Manjul
Guest
Manjul

String testdate=”20/06/2016″;
PreparedStatement stmt = con.prepareStatement(
“INSERT INTO records ”
+ “(id, name, birthday)”
+ “VALUES(?,?,STR_TO_DATE(?,’%Y/%m/%d’))”);
stmt.setString(1, “EN123”);
stmt.setString(2, “Prabhat”);
stmt.setString(3, testdate);
stmt.executeUpdate();

testUser
Guest
testUser

can we set SYSDATE using setDate or setRef in prepared statement.

Anyul Rivas
Guest
Anyul Rivas

I tried doing as explained, but every time i check the prepared Statement it passes the date value as string with unknown data type (0)

Manwendra
Guest
Manwendra

Hi mkyong thanx for the tutorial it is really useful. I am having a problem: when I insert the current date it stores 1970-01-01 in mysql database always

Manwendra
Guest
Manwendra

i m sorry it was my fault, didn’t follow the steps in correct order. its working now thanks

praveen
Guest
praveen

Im getting a com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: ‘1999’ for column…. when im trying to insert a java.util.date value.

kishor
Guest
kishor

Nice one!! The code snippet is useful. Keep posting such stuff.