java根据用户输入在PreparedStatement中使用setTime()或setNull()
我正在JAVA应用程序和PostgreSQL数据库中进行筛选搜索
我有以下PostgreSQL查询用于选择航班数据:
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)";
然后我有一个如下的Java代码:
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();
}
因此,关键是只返回那些符合文本字段标准的航班。若任何文本字段留空,则查询应返回该特定条件的所有航班,但使用其他非空条件进行过滤
我在参数$9处有一个错误。
当我将输入的String
(例如“18:00:00”)转换成showDepTimeTxtField
时,转换成java.sql.Time
对象,我可以将该对象用作prepStatement
方法setTime()
的输入参数。我的查询应该只返回departure_time
与输入文本字段的时间相同的航班(已满足其他条件)。如果文本字段为空且没有String
输入,则我的查询应返回航班,并从我的数据库返回航班中的任何departure_time
我的问题是,当我运行代码时,会出现以下错误:
org。postgresql。util。PSQLException:错误:无法确定参数$9的数据类型,当我用字符串填充departureTimeTxtField
或将其保留为空时,我将获取该参数,因此参数departure_time
应设置为NULL,并且我的查询应返回所有departure_time
的航班
所以我假设我在setTime()
方法参数timeValue
和从String
到java.sql.Time
的转换方面有问题
如何进行适当的转换以消除该错误
编辑:更新SQL查询和Java源代码强>
# 1 楼答案
我能够通过使用
COALESCE()
而不是CASE WHEN ...
来解决这个问题:# 2 楼答案
生成错误的原因是SQL中的
CASE
语法无效(而不是时间格式),请查看CASE
语法here此外,从示例中可以看出,您似乎正在尝试获取起飞时间为null或文本字段中存在时间的所有航班,在这种情况下,我建议采用以下方法: