CODEWITHSUNDEEP
javapostgresqldate
Ariel Grabijas
Ariel Grabijas

Reputation: 1512

Date in postgresql

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();
    } 
}

Upvotes: 0

Views: 1501

Answers (3)

Tom Anderson
Tom Anderson

Reputation: 47163

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.

Upvotes: 5

david a.
david a.

Reputation: 5291

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.

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1499770

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.

Upvotes: 4

Related Questions