CODEWITHSUNDEEP
javamysqlsql
The Time
The Time

Reputation: 737

put time as string to the query

The query works fine but since I must test my project several time and I dont want always to adjust the arrivaltime table to the current time I would integrate a string time to the query but I am getting the following error

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 ':56,'%H:%i') and name LIKE 'hbf'' at line 1

The time must include only the hours as well minutes digits.

            String time = "12:56:00";
            PreparedStatement preparedTime = con
                                .prepareStatement("SELECT route from arrivaltimes INNER JOIN stops"
                                        + " ON arrivaltimes.stop_id=stops.stop_id "
                                        + "WHERE weekday = '"
                                        + day
                                        + "'"
                                        //+ " and time_format(arrivaltime,'%H:%i')= time_format(curtime() ,'%H:%i') and name LIKE '"
                                        + " and time_format(arrivaltime,'%H:%i')= time_format("+ time+ ",'%H:%i') and name LIKE '"

                                        + stop_name + "'");

            ResultSet rsArrivaletime = preparedTime.executeQuery();

Upvotes: 0

Views: 50

Answers (1)

Luiggi Mendoza
Luiggi Mendoza

Reputation: 85779

When using PreparedStatement, NEVER CONCATENATE STRINGS TO GET THE QUERY, EVER. Pass the proper time as java.sql.Time or java.sql.Timestamp as parameters.

String sql =
    "SELECT route FROM arrivaltimes INNER JOIN stops"
    + " ON arrivaltimes.stop_id = stops.stop_id"
    + " WHERE weekday = ?"
    + " and arrivaltime = ?"
    + " and name LIKE ?";
PreparedStatement preparedTime = con
    .prepareStatement(sql);
preparedTime.setString(1, day);
//preparing the proper time using java.util.Calendar
Calendar cal = Calendar.getInstance();
cal.set(Calendar.HOUR_OF_DAY, 12);
cal.set(Calendar.MINUTE, 56);
cal.set(Calendar.SECOND, 0);
//create an instance of java.sql.Time
//Calendar#getTime returns an instance of java.util.Date
//Date#getTime returns the time in millis (long)
Time time = new Time(cal.getTime().getTime());
//setting the time
preparedTime.setTime(2, time);
preparedTime.setString(3, stopname);
ResultSet rs = preparedTime.executeQuery();

Upvotes: 2

Related Questions