CODEWITHSUNDEEP
javasqlpostgresqljdbcprepared-statement
PeterB
PeterB

Reputation: 2414

Using setTime() or setNull() in PreparedStatement depending on user input

I am making a filtered search in my JAVA application and PostgreSQL database.

I have following PostgreSQL query for selecting data about flights:

String query =  "SELECT flight.flight_id, route.from_location, route.to_location, flight.base_cost, flight.departure_date, flight.departure_time, flight.arrival_date, flight.arrival_time, aircraft.manufacturer, aircraft.seats " +
                "FROM flight " +
                "INNER JOIN route ON flight.route_id = route.route_id " +
                "INNER JOIN aircraft ON flight.aircraft_id = aircraft.aircraft_id " +
                "WHERE " +
                "(from_location = CASE WHEN ? IS NULL THEN from_location ELSE ? END) AND " +
                "(to_location = CASE WHEN ? IS NULL THEN to_location ELSE ? END) AND " +
                "(base_cost = CASE WHEN ? IS NULL THEN base_cost ELSE ? END) AND " +
                "(departure_date = CASE WHEN ? IS NULL THEN departure_date ELSE ? END) AND " +
                "(departure_time = CASE WHEN ? IS NULL THEN departure_time ELSE ? END) AND " +
                "(arrival_date = CASE WHEN ? IS NULL THEN arrival_date ELSE ? END) AND " +
                "(arrival_time = CASE WHEN ? IS NULL THEN arrival_time ELSE ? END)";

Then I have a Java code like this:

    ResultSet rs = null;
    PreparedStatement prepStatement = connection.prepareStatement(query);
    try {
                if (!showFromTxtField.getText().trim().isEmpty()) {
                    prepStatement.setString(1, showFromTxtField.getText());
                    prepStatement.setString(2, showFromTxtField.getText());
                } else {
                    prepStatement.setNull(1, java.sql.Types.VARCHAR );
                    prepStatement.setNull(2, java.sql.Types.VARCHAR );
                }
                if (!showToTxtField.getText().trim().isEmpty()) {
                    prepStatement.setString(3, showToTxtField.getText());
                    prepStatement.setString(4, showToTxtField.getText());
                } else {
                    prepStatement.setNull(3, java.sql.Types.VARCHAR );
                    prepStatement.setNull(4, java.sql.Types.VARCHAR );
                }
                if (!showCostTxtField.getText().trim().isEmpty()) {
                    prepStatement.setFloat(5, Float.parseFloat(showCostTxtField.getText()));
                    prepStatement.setFloat(6, Float.parseFloat(showCostTxtField.getText()));
                } else {
                    prepStatement.setNull(5, java.sql.Types.FLOAT );
                    prepStatement.setNull(6, java.sql.Types.FLOAT);
                }
                LocalDate depDate = showDepDateDatePicker.getValue();
                if (depDate != null) {
                    prepStatement.setDate(7, Date.valueOf(depDate));
                    prepStatement.setDate(8, Date.valueOf(depDate));
                } else {
                    prepStatement.setNull(7, java.sql.Types.DATE );
                    prepStatement.setNull(8, java.sql.Types.DATE);
                }
                if (!showDepTimeTxtField.getText().trim().isEmpty()) {
                    DateFormat formatter = new SimpleDateFormat("HH:mm:ss");
                    try {
                        Time timeValue = new Time(formatter.parse(showDepTimeTxtField.getText()).getTime());
                        prepStatement.setTime(9, timeValue);
                        prepStatement.setTime(10, timeValue);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                } else {
                    prepStatement.setNull(9, java.sql.Types.TIME );
                    prepStatement.setNull(10, java.sql.Types.TIME );
                }
                LocalDate arrDate = showArrDatePicker.getValue();
                if (arrDate != null) {
                    prepStatement.setDate(11, Date.valueOf(arrDate));
                    prepStatement.setDate(12, Date.valueOf(arrDate));
                } else {
                    prepStatement.setNull(11, java.sql.Types.DATE );
                    prepStatement.setNull(12, java.sql.Types.DATE);
                }
                if (!showArrTimeTxtField.getText().trim().isEmpty()) {
                    DateFormat formatter = new SimpleDateFormat("HH:mm:ss");
                    try {
                        Time timeValue = new Time(formatter.parse(showArrTimeTxtField.getText()).getTime());
                        prepStatement.setTime(13, timeValue);
                        prepStatement.setTime(14, timeValue);
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                } else {
                    prepStatement.setNull(13, java.sql.Types.TIME );
                    prepStatement.setNull(14, java.sql.Types.TIME );
                }

                rs = prepStatement.executeQuery();
      } catch (SQLException e) {
                e.printStackTrace();
            }

So the point is to return only those flights, which met the criteria from text fields. If any of text fields is left empty then query should return all flights for that particular criteria, but filtered with other non-empty criteria.

I have an error at parameter $9. When I convert String (eg. "18:00:00") entered into showDepTimeTxtField, to java.sql.Time object which I can use as input parameter for prepStatement method setTime() my query should return only flights (which had met other criteria) with departure_time same as time entered into text field. If text field is left empty with no String input, my query should return flights with whatever departure_time they have, from my database.

My problem is, that when I run my code I get a following error: org.postgresql.util.PSQLException: ERROR: could not determine data type of parameter $9 which I get wheter I fill departureTimeTxtField with string or leave it empty so the parameter departure_time should be set to NULL and my query should return flights with all departure_time's.

So I assume I have a problem with setTime() method argument timeValue and conversion from String to java.sql.Time.

How to make a proper conversion so I'll get rid of that ERROR?

EDIT: SQL query and Java source code updated.

Upvotes: 2

Views: 852

Answers (2)

Darshan Mehta
Darshan Mehta

Reputation: 30819

The error is generated because of invalid CASE syntax in SQL (and not because of time format), have a look at CASE syntax here.

Also, from the example, it seems you are trying to fetch all the flights whose departure time is either null or time present in text field, in this case, I would recommend the following approach:

ResultSet rs = null;
PreparedStatement prepStatement = null;
if (!departureTimeTxtField.getText().trim().isEmpty()) {
    DateFormat formatter = new SimpleDateFormat("HH:mm:ss");
    Time timeValue = new Time(formatter.parse(departureTimeTxtField.getText()).getTime());
    prepStatement  = connection.prepareStatement("SELECT * FROM flights WHERE departure_time = ?");
    prepStatement.setTime(1, timeValue);
}else{
    prepStatement  = connection.prepareStatement("SELECT * FROM flights WHERE departure_time IS NULL");
}
rs = prepStatement.executeQuery();

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123654

I was able to work around the issue by using COALESCE() instead of CASE WHEN ...:

String query = "SELECT * FROM flights WHERE departure_time = COALESCE(?, departure_time)";

Upvotes: 3

Related Questions