英文:
Parameterized query in PostgreSQL
问题
我是 PostgreSQL 新手，正在尝试使参数化查询起作用。我有以下 SQL 语句，其中类型 A 似乎是正常的，但类型 B 不起作用。
SQL 类型 A）
StringBuilder buffer = new StringBuilder("Select * from JOB where")
buffer.append(" START_TIME ");
buffer.append(" BETWEEN '2020-08-17' AND '2020-08-18'");
String sql = buffer.toString();
SQL 类型 B）- 参数化
StringBuilder buffer = new StringBuilder("Select * from DA_JOB where")
buffer.append("START_TIME");
buffer.append(" BETWEEN ? AND ?");
String sql = buffer.toString();
根据类型执行查询 - 执行类型 A 是成功的，但类型 B 不成功
try (Connection dbConnection = getConnection();
PreparedStatement statement = dbConnection.prepareStatement(sql)) {
if (typeB) {
// myHashMap<String, String> 保存一些键值
StringBuilder st = new StringBuilder("'");
String value = myHashMap.get("startTime");
st.append(value);
st.append("'");
statement.setString(1, st.toString());
StringBuilder st1 = new StringBuilder("'");
String value2 = myHashMap.get("endTime");
st1.append("'");
statement.setString(2, st1.toString());
}
try (ResultSet rs = statement.executeQuery()) {
while (rs.next()) {
jobs.add(sanitize(rs));
}
}
}
执行参数化查询（类型 B）时，我收到以下错误：
> "org.postgresql.util.PSQLException: ERROR: operator does not exist:
> timestamp without time zone >= character varying\n Hint: No operator
> matches the given name and argument type(s). You might need to add
> explicit type casts.
这似乎是在设置字符串后最终查询中的引号问题。您有关于如何修复这个问题的任何提示吗？非常感谢。
英文:
I am new in PostgreSQL and trying to get parameterized query to work. I have following sql statements where type A seems to be ok but type B does not work.
sql type A)
StringBuilder buffer = new StringBuilder("Select * from JOB where")
buffer.append(" START_TIME ");
buffer.append(" BETWEEN '2020-08-17' AND '2020-08-18'");
String sql = buffer.toString();
sql type B) - parameterized
StringBuilder buffer = new StringBuilder("Select * from DA_JOB where")
buffer.append("START_TIME");
buffer.append(" BETWEEN ? AND ?");
String sql = buffer.toString();
execute query based on the type - executing type A is successful but type B is not
try (Connection dbConnection = getConnection();
PreparedStatement statement = dbConnection.prepareStatement(sql)) {
if (typeB) {
//myHashMap<String, String> holds some key value
StringBuilder st = new StringBuilder("'");
String value = myHashMap.get("startTime")
st.append(value);
st.append("'");
statement.setString(1, st.toString());
StringBuilder st1 = new StringBuilder("'");
String value2 = myHashMap.get("endTime")
st1.append("'");
statement.setString(2, st1.toString());
}
try (ResultSet rs = statement.executeQuery()) {
while (rs.next()) {
jobs.add(sanitize(rs));
}
}
}
the error I get from executing parameterized query which is type B is
> "org.postgresql.util.PSQLException: ERROR: operator does not exist:
> timestamp without time zone >= character varying\n Hint: No operator
> matches the given name and argument type(s). You might need to add
> explicit type casts.
which seams to be quotation problem in the final query after setting string.
Any tips on how I can fix this? It is really appriciated.
答案1
得分: 3
基本上，问题是数据库引擎拒绝将您的字符串转换为日期。您可以采取两种方法来解决这个问题。其中一种方法是在服务器端添加转换。BETWEEN ?::date AND ?::date 应该可以工作。:: 是 PostgreSQL 中的非标准转换符号；您也可以使用符合 SQL 标准的 CAST 语法。
另一种方法是使用 preparedStatement 的 setDate，从存储的字符串创建一个 java.sql.Date 对象（或将日期存储在您的 HashMap 中）。
英文:
Basically, the problem is that the DB engine is refusing to cast your string into a date. There are two approaches you can take to fix this. One is to add a cast server-side. BETWEEN ?::date AND ?::date should work. The :: is a PostgreSQL non-standard cast; you can also use SQL-compliant CAST syntax if you want.
An alternative would be to use preparedStatement's setDate, creating a java.sql.Date object from your stored String (or storing a Date in your HashMap).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论