Error inserting into PostgreSQL time type using JDBC

Question

I have a table with a "time without time zone" data type in a postgres database and I'm getting a syntax error when trying to insert using prepared statements. I've tried creating a new java.sql.Time object rather than using inputs from a web page and I am still getting the same error. What am I doing wrong?

Error:

org.postgresql.util.PSQLException: ERROR: invalid input syntax for type time: "1970-01-01 +01:00:00"

Table:

                                     Table "public.reservation"
   Column   |          Type          |                          Modifiers
------------+------------------------+--------------------------------------------------------------
 res_id     | integer                | not null default nextval('reservation_res_id_seq'::regclass)
 cust_id    | character varying(50)  | not null
 date       | date                   | not null
 time       | time without time zone | not null
 num_people | integer                | not null
Indexes:
    "reservation_pkey" PRIMARY KEY, btree (res_id)
Foreign-key constraints:
    "reservation_cust_id_fkey" FOREIGN KEY (cust_id) REFERENCES customer(cust_id)
Referenced by:
    TABLE "reservation_table" CONSTRAINT "reservation_table_res_id_fkey" FOREIGN KEY (res_id) REFERENCES reservation(res_id)

Reservation Java:

 public static boolean createReservation(String custID, Date date, Time time, int numPeople) throws ServletException {

    //TODO add checking
    //TODO need to add determing table and if reservation time is free

    boolean succeeded = false;

    //checks if reservation is possible to be booked
    if(checkReservationPossible()){

        //convet date to correct format for database
        //SimpleDateFormat dateFormatter = new SimpleDateFormat("yyyy/MM/dd");
        //String formattedDate = dateFormatter.format(date);

        //convert time to correct format for database
        //SimpleDateFormat timeFormatter = new SimpleDateFormat("HH:mm");
        //String formattedTime = timeFormatter.format(time);

        DatabaseAccess db = new DatabaseAccess();
        String sql = "INSERT INTO reservation (cust_id, date, time, num_people)"
                + "VALUES (?,?,?,?)";

        LinkedList<Object> params = new LinkedList<Object>();
        params.add(custID);
        params.add(date);
        params.add(time);
        params.add(numPeople);

        succeeded = db.runUpdateQuery(sql,params);

        db.close();
    }

    return succeeded;
}

DB Access Java:

public boolean runUpdateQuery(String sql, LinkedList<Object> params) throws ServletException
{
    // Using try {...} catch {...} for error control
    try{
        // Create a statement variable to be used for the sql query
        //Statement statement = this.connection.createStatement();

        // Perform the update

        PreparedStatement pst = this.connection.prepareStatement(sql);

        Iterator iter = params.iterator();

        for(int i = 1;iter.hasNext(); i++){
            Object curr = iter.next();
            //TODO may need to add more for different types
            if(curr instanceof String){
                pst.setString(i, curr.toString());
            }else if (curr instanceof Integer){
                pst.setInt(i, (Integer)curr);
            }else if (curr instanceof java.util.Date){
                //convert to sql date
                java.util.Date tempDate = (java.util.Date)curr;
                java.sql.Date sqlDate = new java.sql.Date(tempDate.getTime());
                pst.setDate(i, sqlDate);
            }else if (curr instanceof Time){
                pst.setTime(i, Time.valueOf("11:30:00"));
            }
        }

        int numRows = pst.executeUpdate();

        pst.close();

        if(numRows > 0){
            return true;
        }else{
            return false;
        }
    } catch (SQLException e){
        // Deal with the error if it happens
        throw new ServletException(String.format("Error: Problem running query... "), e);
    }
}

For what it's worth, I can't see anything wrong with your code. Make sure you're importing java.sql.Time, and not some other class. — Bohemian
– Bohemian ♦, Commented Feb 11, 2012 at 21:34

Bohemian · Accepted Answer · 2012-02-11 21:36:01Z

3

You don't need all that checking. I recommend you change you code to simply this:

for (int i = 1; iter.hasNext(); i++) {
    Object curr = iter.next();    
    pst.setObject(i, curr);
}

JDBC knows what to do with all the various usual java types - no need to convert.

The only time you need to check the type is if you want to use some custom class and you want to get some JDBC compliant type from it. This is not the case here.

answered Feb 11, 2012 at 21:36

Bohemian♦

427k103 gold badges604 silver badges750 bronze badges

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

5 Comments

Bohemian Over a year ago

@Chris Did you try it? Also, is your JDBC driver the correct version for your database server version? I know this code works because I've used exactly this with postgres and it works.

Chris Over a year ago

I have, I've created another table without the time field in it to make sure and that works correctly. I don't know the version on the server but I'm using 8.3-603.

Bohemian Over a year ago

Just confirming - so you tried using only the setObject() method, like in my suggested code?

Chris Over a year ago

Seems to be working today, not sure what I did wrong before but thanks :) It was getting late :P

ist_lion Over a year ago

Not doing all the checking fixed my exact same problem +1

Diego · Accepted Answer · 2012-02-11 21:39:44Z

1

I think the problem is that java is sending the date and time, not just the time. A nice way to fix this is to create a class derived from Time and override the toString() method, which is used by JDBC internally to replace ? with param values:

public class PosgreSqlTime inherits java.sql.Time
{
     private static SimpleDateFormat SDF = new SimpleDateFormat("HH:mm:ss:SSS");
     @Override
     public String toString()
     {
          return SDF.format(this);
     }
}

answered Feb 11, 2012 at 21:39

Diego

18.5k5 gold badges67 silver badges68 bronze badges

1 Comment

Chris Over a year ago

it will not let me set it as a String because it is expecting a Time object

Collectives™ on Stack Overflow

Error inserting into PostgreSQL time type using JDBC

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related