Date in postgresql

Question

How to insert java Date object into Postgresql DATE cell?

When I do it my way, it's wrong:

Calendar date = Calendar.getInstance(); // wrong
Date date = new Date(); // wrong         

String addRecord = "INSERT INTO " + GlobalFields.PACJENCI_TABLE +
                                "VALUES (" + date + ");";
stat.executeUpdate(addRecord)

I have no more ideas...

PS:

public static void CreatePacjenciTable()
{
    try
    {
        Connection conn = DataBase.Connect();

        try
        {
            Statement stat = conn.createStatement();

            String createTable = "CREATE TABLE " + GlobalFields.PACJENCI_TABLE 
                + "(dataUrodzenia DATE);";

            stat.executeUpdate(createTable);
        }
        finally
        {
            conn.close();
        }
    }
    catch(SQLException e)
    {
        e.printStackTrace();
    } 
}

This is where SQL injection starts... That's so 20th century, don't make the mistakes others made before you, learn from them. — Frank Heikens
– Frank Heikens, Commented Aug 29, 2012 at 17:57
Java SE 8 (JDBC 4.2) onwards, you should never use the legacy Date-Time API. Check this answer and this answer to learn how to use java.time API with JDBC. — Arvind Kumar Avinash
– Arvind Kumar Avinash, Commented Jun 20, 2021 at 17:35

Tom Anderson · Accepted Answer · 2012-08-29 16:53:27Z

5

You shouldn't do it like that at all!

Never, ever, construct SQL strings with their parameters in them. Instead, create a PreparedStatement and set the parameters on it. Like this:

    String addRecord = "INSERT INTO " + GlobalFields.PACJENCI_TABLE + "VALUES (?);";
    PreparedStatement stmt = conn.prepareStatement(addRecord);
    stmt.setDate(1, new Date(System.currentTimeMillis()));
    stmt.executeUpdate();

Note that the Date there is a java.sql.Date, which is a subclass of the normal java.util.Date in which the time of day is always midnight UTC; it is used to represent an actual date, not a moment in time. You can alternatively use a java.sql.Timestamp, which represents a moment in time.

answered Aug 29, 2012 at 16:53

Tom Anderson

47.4k17 gold badges96 silver badges139 bronze badges

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

Comments

Jon Skeet · Accepted Answer · 2012-08-29 16:49:03Z

4

This is the problem:

String addRecord = "INSERT INTO " + GlobalFields.PACJENCI_TABLE +
                   "VALUES (" + date + ");";

You're converting the Date into a string and including that within the SQL. Don't do that. There's no need to use a string conversion here at all.

Instead, use a PreparedStatement and insert the value as a java.sql.Date or possibly a java.sql.Timestamp via a parameter.

You should almost always avoid including values in your SQL as strings - use parameterized SQL instead, and set the values in the statement. This keeps your SQL (code) separate from the parameters (data), avoids the risk of data conversion issues, and also avoids SQL injection attacks.

answered Aug 29, 2012 at 16:49

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Comments

david a. · Accepted Answer · 2012-08-29 16:52:25Z

1

Instead of creating the SQL by concatenating strings (which is the problem here, as Java's string representation of date is surely not understood by Postgres), use PreparedStatement and pass the date as a parameter. Aside of other advantages (pre-compiled statements, ...), it'll allow you to pass the date as Date and let JDBC and the underlying database driver do all the necesary data conversion work for you.

answered Aug 29, 2012 at 16:52

david a.

5,28925 silver badges25 bronze badges

Collectives™ on Stack Overflow

Date in postgresql

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related