英文:
How can i put variable into function in SQL query by preparedStatement in JDBC template?
问题
我使用Oracle数据库。你能帮助我吗？谁可以帮助，请帮帮我，我对这个问题感到很疲倦=(
我有以下SQL查询，需要将我的变量放入函数TO_DATE中：
select p.mass_id, p.*
from cpm_problem p
where category = '9154812121313217606'
and type = '9154812121313217612'
and created_when > TO_DATE(lastPpr = ?, 'YYYY-MM-DD HH24:MI:SS')
order by created_when desc
这是我在IDE中的代码：
private static final String LAST_PPR_FROM_RDB = "select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = ?, 'YYYY-MM-DD HH24:MI:SS') order by created_when desc";
latestPprStr 返回的值是 2022-12-31 12:00:00。
public Ppr getCpmProblems() {
String latestPprStr = senderJdbcTemplate.queryForObject(LAST_PPR_FROM_SENDER, String.class);
Ppr ppr1 = rdbJdbcTemplate.query(LAST_PPR_FROM_RDB, ps -> ps.setString(1, latestPprStr),
new BeanPropertyRowMapper<>(Ppr.class)).stream().findAny().orElse(null);
return ppr1;
}
关键问题是这个值似乎没有被正确转换为日期。
错误出现在这里：
Caused by: Error : 907, Position : 153, Sql = select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = :1 , 'YYYY-MM-DD HH24:MI:SS') order by created_when desc,
OriginalSql = select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = ?, 'YYYY-MM-DD HH24:MI:SS') order by created_when desc,
Error Msg = ORA-00907: missing right parenthesis
用" 或类似的方式进行屏蔽似乎不起作用，我也尝试使用更简单的构造，例如：
select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = ?
只是为了检查我的IDE中的代码是否有效，确保所有内容都成功添加到SQL查询中。但在to_date(...) 中放入变量时总是导致SQL语法错误和":1"。
英文:
I use Oracle DB. Can you help me? Help pleease who can, i'm tired of this problem =(
I have such SQL query and i need to put my variable into function - converter to_date:
select p.mass_id, p.*
from cpm_problem p
where category = '9154812121313217606'
and type = '9154812121313217612'
and created_when > TO_DATE(lastPpr = ?, 'YYYY-MM-DD HH24:MI:SS')
order by created_when desc
This is my code in idea:
private static final String LAST_PPR_FROM_RDB = "select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = ?, 'YYYY-MM-DD HH24:MI:SS') order by created_when desc";
latestPprStr is return 2022-12-31 12:00:00
public Ppr getCpmProblems() {
String latestPprStr = senderJdbcTemplate.queryForObject(LAST_PPR_FROM_SENDER, String.class);
Ppr ppr1 = rdbJdbcTemplate.query(LAST_PPR_FROM_RDB, ps -> ps.setString(1, latestPprStr),
new BeanPropertyRowMapper<>(Ppr.class)).stream().findAny().orElse(null);
return ppr1;
}
The key problem is that the value doesn't converts into date, how i think...
Error is here:
> Caused by: Error : 907, Position : 153, Sql = select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = :1 , 'YYYY-MM-DD HH24:MI:SS') order by created_when desc,
OriginalSql = select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = '9154812121313217612' and created_when > to_date(latestPprStr = ?, 'YYYY-MM-DD HH24:MI:SS') order by created_when desc, <BR>
Error Msg = ORA-00907: missing right parenthesis
Any screening by "'" or similar is not working, also i'm tried to use more simple construction like:
select p.mass_id, p.* from cpm_problem p where category = '9154812121313217606' and type = ?
just to check that my code in idea is working and everything was added in sql query successfully. But exactly in to_date(...) i cant to put variable. Every time i have bad sqlgrammar and ":1"
答案1
得分: 0
"to_date(latestPprStr = :1 , 'YYYY-MM-DD HH24:MI:SS')" 不是有效的SQL表达式。如果你想要将一个字符串绑定到这里，预准备的语句SQL字符串应该是：
"to_date(:1 , 'YYYY-MM-DD HH24:MI:SS')"
然后你可以绑定一个字符串到其中。
英文:
"to_date(latestPprStr = :1 , 'YYYY-MM-DD HH24:MI:SS')"
is not a valid SQL expression. If you wanted to bind a string into this, the prepared statement SQL string would be:
"to_date(:1 , 'YYYY-MM-DD HH24:MI:SS')"
into which you would then bind a string
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论