SQL 准备语句如何通过多个可能的菜单选择进行选择? [英] SQL prepared statement how to select via multiple possible menu selections?
问题描述
所以我有 4 个菜单选择(产品、位置、课程类型和类别)，所有这些都可以为空(使用 JSF 编程，但这应该与这个问题无关，因为它是一个 SQL 问题).
So I have 4 menu selections (product, location, courseType, and category), all of which can be null (programmed using JSF but that should be irrelevant to this question, as it is an SQL question).
菜单选择将向托管 bean 发送用户选择的变量，并使用准备好的语句使用用户选择的菜单(如果有)中的信息搜索数据库表.
The menu selection will send the managed bean the variable that the user selected, and using a prepared statement search a database table using the information from the menu that the user selected (if any).
如果用户将菜单项留空，它应该搜索所有内容.
If the user leaves the menu item null, it should search everything.
如果用户留下 1 或 2 或 3 个带有信息的菜单项，而另一个为空，则应进行相应的搜索.
If the user leaves 1 or 2 or 3 of the menu items with information, and the other one null, it should search accordingly.
我的问题是，如果 bean 中没有一堆 if/then 语句附加到每个人的适当 sql 语句，我该如何做到这一点?
My problem is how do I do this without a bunch of if/then statements in the bean attached to an the appropriate sql statement for every one?
或者有没有更好的 sql 语句可以完成所有这些工作?
Or is there one better sql statement I can make that does all of this?
我在 Java 中使用准备好的语句.
I am using a prepared statement in Java.
我试过了:
if (product != null && location != null && courseType != null && category != null) {
pstmt = conn.prepareStatement("select * FROM Courses WHERE "
+ "product = ? "
+ "and location = ? "
+ "and courseType = ? "
+ "and category = ?");
pstmt.setString(1, product);
pstmt.setString(2, location);
pstmt.setString(3, courseType);
pstmt.setString(4, category);
} else if (product == null && location != null && courseType != null && category != null) {
pstmt = conn.prepareStatement("select * FROM Courses WHERE "
+ "location = ? "
+ "and courseType = ? "
+ "and category = ?");
pstmt.setString(1, location);
pstmt.setString(2, courseType);
pstmt.setString(3, category);
}
等等，但是对于 1 为空而不是其他情况的每种情况，我都必须这样做 16 次?必须有更聪明的方法(通过使用 1 个 sql 语句还是仅使用几个 java if/then 语句?)
etc etc but I would have to do this like 16 times for each case of 1 being null and not the others? There must be a smarter way (either by using 1 sql statement or only a few java if/then statements?)
更新感谢 Luiggi Mendoza！我的代码是这样工作的:
UPDATE thanks to Luiggi Mendoza! My code works like this:
pstmt = conn.prepareStatement("select * FROM Courses WHERE "
+ "(product = ? or ? is null) "
+ "and (location = ? or ? is null) "
+ "and (courseType = ? or ? is null)"
+ "and (category = ? or ? is null)");
pstmt.setString(1, product);
pstmt.setString(2, product);
pstmt.setString(3, location);
pstmt.setString(4, location);
pstmt.setString(5, courseType);
pstmt.setString(6, courseType);
pstmt.setString(7, category);
pstmt.setString(8, category);
rset = pstmt.executeQuery();
更新是的，对于不同的 mysql 数据库(可能是不同的版本或其他东西)，我不得不使用 = "" 而不是 null
Update yeah I had to use = "" instead of is null for a different mysql database (maybe different versions or something)
推荐答案
我假设您的 NULL 选择值有一个默认值(因为我不知道使用PreparedStatement) 为 '0'(仅作为示例).
I assume that you have a default value for your NULL selected values (since I don't know another way to do it using PreparedStatement) as '0' (just as an example).
知道这一点，我倾向于使用这种SQL:
Knowing this, I tend to use this kind of SQL:
SELECT *
FROM Courses
WHERE
(product = ? or ? = '0')
and (location = ? or ? = '0')
and (courseType = ? or ? = '0')
and (category = ? or ? = '0')
通过这种方法，您可以利用 IF 用法并让数据库处理工作.唯一的缺点是你应该将每个变量设置两次(这辈子没有什么是免费的 =).
With this approach, you leverage the IF usage and let the database handle the work. The only bad point with this is that you should set every variable twice (nothing is free in this life = ).
我已经根据您的要求进行了测试.首先是SQL表和内容:
I've made a test based on your requirement. First the SQL table and content:
create table pruebaMultiSelect
(id int primary key auto_increment,
nombre varchar(50),
tipo varchar(10),
categoria varchar(10));
insert into pruebaMultiSelect values
(null, 'Luiggi', 'A', 'X');
insert into pruebaMultiSelect values
(null, 'Thomas', 'A', 'Y');
insert into pruebaMultiSelect values
(null, 'Jose', 'B', 'X');
insert into pruebaMultiSelect values
(null, 'Trina', 'B', 'Y');
现在，相关的Java代码:
Now, the relevant Java code:
public class DBTest {
public void testPreparedStatement(String p1, String p2) throws SQLException {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
con = getConnection();
pstmt = con.prepareStatement("SELECT * FROM pruebaMultiSelect WHERE (tipo = ? OR ? = 'EMPTY') "
+ "AND (categoria = ? OR ? = 'EMPTY')");
pstmt.setString(1, p1);
pstmt.setString(2, p1);
pstmt.setString(3, p2);
pstmt.setString(4, p2);
rs = pstmt.executeQuery();
while (rs.next()) {
System.out.printf("%5d %15s
", rs.getInt(1), rs.getString(2));
}
} catch(ClassNotFoundException e) {
e.printStackTrace();
} finally {
rs.close();
pstmt.close();
con.close();
}
}
public Connection getConnection() throws SQLException, ClassNotFoundException {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection("jdbc:mysql://localhost:3306/luiggi_test", "user", "password");
}
public static void main(String[] args) throws SQLException {
//this will return all the data
String p1 = "EMPTY";
String p2 = "EMPTY";
new DBTest().testPreparedStatement(p1, p2);
}
}
使用 MySQL 5.1.18 测试.
Tested using MySQL 5.1.18.
这篇关于SQL 准备语句如何通过多个可能的菜单选择进行选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
