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 - stackoverflow.com/questions/530012/… — Sahil Dave
– Sahil Dave, Commented Jul 2, 2013 at 10:38

Loša · Accepted Answer · 2013-07-02 10:37:20Z

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 2, 2013 at 10:37

Loša

2,6212 gold badges16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2110714 Over a year ago

Thanks, but by using java.sql.date i cannot store hours and minutes is there any way to do this?

Loša Over a year ago

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.

Juned Ahsan · Accepted Answer · 2013-07-02 10:34:14Z

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 2, 2013 at 10:34

Juned Ahsan

68.9k12 gold badges101 silver badges138 bronze badges

2 Comments

user2110714 Over a year ago

i am using java.util.date, but when i use sql.date i cannot store minutes and hour right? How can i do it?

Juned Ahsan Over a year ago

@constructor If you can, then consider stroring the time in milliseconds in DB and format it when you read it.

MelanieMunden · Accepted Answer · 2013-07-02 10:57:09Z

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 2, 2013 at 10:57

answered Jul 2, 2013 at 10:40

MelanieMunden

112 bronze badges

4 Comments

user2110714 Over a year ago

Thanks, but by using java.sql.date i cannot store hours and minutes is there any way to do this?

MelanieMunden Over a year ago

Use the Calendar, and then convert it to a date when you need to store it in the database.

user2110714 Over a year ago

yes, but this can be done in java.util.date, java.sql.date has no proper constructors for this, or i do not know

MelanieMunden Over a year ago

Sorry, you should be using java.sql.Timestamp.

Niju · Accepted Answer · 2013-07-02 12:57:10Z

0

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

edited Jul 2, 2013 at 12:57

answered Jul 2, 2013 at 12:38

Niju

4871 gold badge9 silver badges18 bronze badges

Comments

t7bdh3hdhb · Accepted Answer · 2013-07-02 15:23:48Z

0

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

answered Jul 2, 2013 at 15:23

t7bdh3hdhb

4383 silver badges15 bronze badges

Collectives™ on Stack Overflow

Cannot store Date variable in database in Java

5 Answers 5

2 Comments

2 Comments

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

2 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related