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.
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();
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
please help me i am cant able to convert the string into date using database connection
DateFormat formatter ;
formatter = new SimpleDateFormat(“yyyy-MM-dd”);
Date date=(Date) formatter.parse(bean1.getEmpdate());
System.out.println(“Employee date is presented=” + formatter.format(date));
cstmt.setDate(4, (Date) date);
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.
what if we have date in string what to do instead of SYSDATE / getCurrentDate
can we set SYSDATE using setDate or setRef in prepared statement.
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)
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
i m sorry it was my fault, didn’t follow the steps in correct order. its working now thanks
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.
Nice one!! The code snippet is useful. Keep posting such stuff.