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, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
8 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
8 Comment authors
FurqanManjultestUserAnyul RivasManwendra 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

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.