Using Timestamp in java sql prepared statement

Question

I am trying to execute a select query using prepared statement in Java. In Where clause im checking for a condition on Timestamp type column as shown below.

String selectSQL = "select * from db.keycontacts WHERE CREATEDDATETIME>?";
PreparedStatement preparedStatement = connect.prepareStatement(selectSQL);
preparedStatement.setTimestamp(1, convertStrToTimestamp(lastSyncTimeStamp));
resultSet = preparedStatement.executeQuery(selectSQL );

//function to convert timestampString to java.sql.Timestamp

private java.sql.Timestamp convertStrToTimestamp(String dateTimeStr){

      java.sql.Timestamp timeStampDate = null;
      try {
            DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");//2015-05-11 18:26:55
            java.util.Date dateObj = (java.util.Date)formatter.parse(dateTimeStr);
            timeStampDate = new Timestamp(dateObj.getTime());
        } catch (ParseException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

      return timeStampDate;
  }

When the query is executed, getting following exception.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

So where exactly im going wrong here?

thanks in advance.

singhakash · Accepted Answer · 2015-05-14 06:29:43Z

3

Remove the parameter from

resultSet = preparedStatement.executeQuery(selectSQL );

and change to

resultSet = preparedStatement.executeQuery( );

The query you passed in preparedStatement.executeQuery(selectSQL ); takes priority over the query you passed in connect.prepareStatement(selectSQL); which is the simple string ("select * from db.keycontacts WHERE CREATEDDATETIME>?") in which you dint set any parameter so there is a syntax error for ?

and you can also say that statement is prepared at PreparedStatement preparedStatement = connect.prepareStatement(selectSQL); since executeQuery() is inherited from Statement it will execute query without preparing it.

edited May 14, 2015 at 6:29

answered May 14, 2015 at 6:15

singhakash

7,9196 gold badges33 silver badges65 bronze badges

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

1 Comment

Rajesh Rs Over a year ago

Thanks Akash for the solution and explaination.

OlivierMOREL OC1 · Accepted Answer · 2022-05-24 18:03:57Z

0

    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection con = DriverManager.getConnection(
            "jdbc:mysql://localhost:3306/test","root","rootpasswd");
    PreparedStatement ps = con.prepareStatement("select p.PARKING_NUMBER, p.TYPE, p.AVAILABLE, "
            + "t.PARKING_NUMBER, t.VEHICLE_REG_NUMBER, t.PRICE, t.IN_TIME, t.OUT_TIME "
            + "from parking p inner join ticket t on p.PARKING_NUMBER = t.PARKING_NUMBER "
            + "where t.In_TIME = ?");
    Timestamp ts = new Timestamp(BigDecimal.valueOf(expectedInTime.getTime()/1000d).setScale(0, RoundingMode.HALF_UP).longValue()*1000);
    //To Round Half Up from millisecond (d for double) to second (long so no d) because MySQL do this.
    ps.setTimestamp(1, ts);
    ResultSet rs = ps.executeQuery();

edited May 24, 2022 at 18:03

answered May 22, 2022 at 21:06

OlivierMOREL OC1

11 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Using Timestamp in java sql prepared statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related