英文:
Prepared statement with some where condition in case of any value
问题
我有一个准备好的语句，类似这样：
select * from books where author = ? and theme = ?
但是我想不通，如果用户选择了“任何作者”或“任何主题”选项，我应该将准备好的语句设置为什么？
英文:
I have prepared statement like
select * from books where author = ? and theme = ?
and i cant figure out what to do, if user select option "any author" or "any theme", what should i set to prepared statement?
答案1
得分: 2
这是一个使用“动态SQL”的案例。您可以手动执行，也可以使用ORM。
让我们来看看手动执行的情况：
String sql;
if (author == null) {
if (theme == null) {
sql = "select * from books";
} else {
sql = "select * from books where theme = ?";
}
} else {
if (theme == null) {
sql = "select * from books where author = ?";
} else {
sql = "select * from books where author = ? and theme = ?";
}
}
PreparedStatement ps = con.createStatement(sql);
int param = 1;
if (author != null) {
ps.setString(param++, author);
}
if (theme != null) {
ps.setString(param++, theme);
}
// 剩下的部分只是执行SQL并读取结果集。
现在，如果您有10个参数，ORM确实会有很大帮助。几乎所有的ORM都以非常好的方式支持动态SQL。
英文:
This is a case for "dynamic SQL". You can do it manually, or use an ORM.
Let's see the manual case:
String sql;
if (author == null) {
if (theme == null) {
sql = "select * from books";
} else {
sql = "select * from books where theme = ?";
}
} else {
if (theme == null) {
sql = "select * from books where author = ?";
} else {
sql = "select * from books where author = ? and theme = ?";
}
}
PreparedStatement ps = con.createStatement(sql);
int param = 1;
if (author != null) {
ps.setString(param++, author);
}
if (theme != null) {
ps.setString(param++, theme);
}
// The rest is just running the SQL and read the ResultSet.
Now, if you have 10 parameters, an ORM really helps a lot. Pretty much all of them support dynamic SQL in a really nice way.
答案2
得分: 1
除了使用预编译语句（除非你进行创意性处理），SQL 语句中应该存在的部分没有被涵盖。通常的解决方案是动态生成 where 子句中的条件，例如：
String sql = "select * from books where 1=1";
if (author != null) {
sql += " and author=?";
}
if (theme != null) {
sql += " and theme=?";
}
在准备好语句之后，你需要设置参数，确保使用正确的索引：
int parameterIndex = 1;
if (author != null) {
preparedStatement.setString(parameterIndex, author);
parameterIndex++;
}
if (theme != null) {
preparedStatement.setString(parameterIndex, theme);
parameterIndex++;
}
英文:
Which parts of the SQL statement should be present is not covered by prepared statements (unless you get creative). Typically the solution is generating the conditions in the where clause dynamically, for example:
String sql = "select * from books where 1=1";
if (author != null) {
sql += " and author=?";
}
if (theme != null) {
sql += " and theme=?";
}
After you've prepared the statement, you need to set the parameters, taking care of using the right indexes:
int parameterIndex = 1;
if (author != null) {
preparedStatement.setString(parameterIndex, author);
parameterIndex++;
}
if (theme != null) {
preparedStatement.setString(parameterIndex, theme);
parameterIndex++;
}
答案3
得分: 0
我通过根据输入数据使用4个不同的预处理语句来解决这个问题。
英文:
I solve this problem by using 4 different prepared statements according to input data.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论