How to insert datetime field in mySql database using PreparedStatement in java?

Question

I red here about similar questions, but I didnt find solution for my problem, how to insert datetime field in mySql database using PreparedStatement in java? I didnt find anything related to this question and PreparedStatement.

So, I have this code:

public void sacuvajDezurstva(List<Dezurstvo> listaDezurstava) throws SQLException {
        String sqlSacuvajDezurstva = "INSERT INTO dezurstvo (Datum,IspitniRokID, NastavnikID, PredmetID) VALUES (?,?,?,?)";
        PreparedStatement ps = konekcija.prepareStatement(sqlSacuvajDezurstva);

        for (Dezurstvo dezurstvo : listaDezurstava) {
            ps.setDate(1, new Date(dezurstvo.getDatum().getTime()));
            ps.setLong(2, dezurstvo.getIspitniRok().getIspitniRokID());
            ps.setLong(3, dezurstvo.getNastavnik().getNastavnikId());
            ps.setLong(4, dezurstvo.getPredmet().getPredmetID());

            ps.executeUpdate();
        }


        konekcija.commit();
    }

This code works, but the first ps parametar here which is Date is problem, because in database only part of this attribute - Date is saved, precisely day, month and year are saved when I use this method. But hour, minutes and seconds are only 00:00:00. So whole date in database looks like this:

2016-06-26 00:00:00

This column in my datatable where I insert Date is datetime type. And when I do debug, I see that this - dezurstvo.getDatum().getTime()) has the normal value of date inserted through the form. So it has, day, month, year, hour, minutes and seconds, but this ps.setDate, acctualy ps, has only the value of day, month and year.

I presume the problem is in SQL query but I don`t know how to do this when I have only ? in VALUES and not some actual date.

Thanks for help!

You can use `java.sql.Timestamp` instead of `Date`. – Titus Oct 03 '16 at 17:22 — Titus, Oct 03 '16 at 17:22

score 5 · Accepted Answer · answered Oct 03 '16 at 17:25

5

You need to format your date. Try the following

Date date = new Date();
SimpleDateFormat format = new           SimpleDateFormat("yyyy-MM-dd HH:mm:ss") ;
String currentDateTime = format.format(date);

Then use setString in your prepared statement.

answered Oct 03 '16 at 17:25

Alan

243
1
11

Cristian Greco · Answer 2 · 2016-10-03T17:34:54.963

0

To insert a Date into a DATETIME field it should be formatted like "yyyy-MM-dd HH:mm:ss".

Another option is to use a TIMESTAMP field instead.

edited Oct 03 '16 at 17:34

answered Oct 03 '16 at 17:24

Cristian Greco

2,566
1
19
24

`TIMESTAMP` fields are really limited compared to `DATETIME` and can't represent values beyond the year 2038. This might not be a big deal right now, but like Y2K there's no reason to set yourself up for failure. – tadman Oct 03 '16 at 17:33
But my date is already formatted like this. When date is read from the form, it is in this format. But problem appears when I should put this date read from the form in database. I suppose that in this part of code - ps.setDate is mistake. Because of wrong query...So you think that I should this - new Date(dezurstvo.getDatum().getTime()) - convert into String...But how can I use setString when my field in datatable is datetime... – Svetlana Oct 03 '16 at 17:41
You should format the date using a SimpleDateFormat as suggested by @Alan, and insert it as a string with the `.setString` method. Have a look at this simila question: http://stackoverflow.com/questions/2400955/how-to-store-java-date-to-mysql-datetime – Cristian Greco Oct 03 '16 at 18:27
Ok, now I succeeded, thanks to all. Yes, Alans answer was wright. – Svetlana Oct 03 '16 at 20:16

How to insert datetime field in mySql database using PreparedStatement in java?

2 Answers2