Cannot store Date variable in database in Java

Question

I am trying to keep the last login date of a user in the database. Here are the necessary parts:

In my login page, I create a Date object and put it into my session, I import java.util.Date:

<% 
Calendar c = Calendar.getInstance();

Date l = new Date(c.get(Calendar.YEAR),c.get(Calendar.MONTH),c.get(Calendar.DAY_OF_MONTH),c.get(Calendar.HOUR),c.get(Calendar.MINUTE)); //the midnight, that's the first second of the day.


//Globals.customer.lastlogin=l;
session.setAttribute("lastlogin", l);   %>

Then when login is successful, I update my database in my servlet, this servlet is called when login button is clicked:

try {
    int result=-1;
    PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(
            "UPDATE users set lastlogin='"+(Date)session.getAttribute("lastlogin")+"' where username='"+session.getAttribute("username")+"'");


        result= checkDB.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

I keep lastlogin as a Date variable in my Database. But I get the following error when trying to update last login:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: 'Wed Jul 02 05:49:00 VET 3913' for column 'lastlogin' at row 1
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2936)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1601)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1710)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2436)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1402)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1694)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1608)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1593)
at classes.LoginServlet.service(LoginServlet.java:94)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:662)

Can anyone help me with this? Thanks

May be this can help - http://stackoverflow.com/questions/530012/how-to-convert-java-util-date-to-java-sql-date — Sahil Dave, Jul 02 '13 at 10:38

score 1 · Answer 1 · answered Jul 02 '13 at 10:37

1

You should take advantage of the PreparedStatement and use ? placeholders for your parameters.

PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(
                "UPDATE users set lastlogin=? where username=?");

checkDB.setDate(1, (Date)session.getAttribute("lastlogin"));
checkDB.setString(2, session.getAttribute("username"));

That will format the date correctly as required by your database.

answered Jul 02 '13 at 10:37

Loša

2,621
2
14
19

Thanks, but by using java.sql.date i cannot store hours and minutes is there any way to do this? – Jul 02 '13 at 10:42
Use java.sql.Timestamp to store date with time. `java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(utilDate.getTime());` and then use `checkDB.setTimestamp` instead of `checkDB.setDate`. – Loša Jul 02 '13 at 10:45

score 0 · Answer 2 · answered Jul 02 '13 at 10:34

0

You may need to check whether you are using java.sql.Date or java.util.Date. Use java.sql.date to avoid any compatibility issues.

answered Jul 02 '13 at 10:34

Juned Ahsan

67,789
12
98
136

i am using java.util.date, but when i use sql.date i cannot store minutes and hour right? How can i do it? – Jul 02 '13 at 10:35
@constructor If you can, then consider stroring the time in milliseconds in DB and format it when you read it. – Juned Ahsan Jul 02 '13 at 10:42

MelanieMunden · Answer 3 · 2013-07-02T10:57:09.017

0

You should use a question mark to parametrise PreparedStatements.

Try this (Make sure the Date is java.sql.Date):

String getUserLastLogin = "UPDATE users set lastlogin=? where username=?";
Date lastLogin = (Date)session.getAttribute("lastlogin");
String username = session.getAttribute("username");

PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(getUserLastLogin);
checkDB.setDate(1, lastLogin);
checkDB.setString(2, username);

result= checkDB.executeUpdate();

Also, add a finally block after the catch and make sure you close your PreparedStatement, and then your Connection so you don't leave open database connections.

edited Jul 02 '13 at 10:57

answered Jul 02 '13 at 10:40

MelanieMunden

11
2

Thanks, but by using java.sql.date i cannot store hours and minutes is there any way to do this? – Jul 02 '13 at 10:42
Use the Calendar, and then convert it to a date when you need to store it in the database. – MelanieMunden Jul 02 '13 at 10:43
yes, but this can be done in java.util.date, java.sql.date has no proper constructors for this, or i do not know – Jul 02 '13 at 10:44
Sorry, you should be using java.sql.Timestamp. – MelanieMunden Jul 02 '13 at 10:56

Niju · Answer 4 · 2013-07-02T12:57:10.563

try {
    int result=-1;
     String dateToInsert = new SimpleDateFormat(yyyy-MM-dd).parse((Date)session.getAttribute("lastlogin"));
    PreparedStatement checkDB = (PreparedStatement) con.prepareStatement(
            "UPDATE users set lastlogin='"+dateToInsert+"' where username='"+session.getAttribute("username")+"'");


        result= checkDB.executeUpdate();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

Try with this . The problem was you have date object in your session in the format that cannot insert into D.B. The date should be in format that compatible with D.B

score 0 · Answer 5 · answered Jul 02 '13 at 15:23

0

you need to take the java.sql.timestamp to get the time as well ...

answered Jul 02 '13 at 15:23

t7bdh3hdhb

438
3
15

Cannot store Date variable in database in Java

5 Answers5