SQL准备语句如何通过多种可能的菜单选择进行选择？ [英] SQL prepared statement how to select via multiple possible menu selections?
问题描述
所以我有4个菜单选项（产品，位置，courseType和类别），所有这些都可以为null（使用JSF编程，但这应该与此问题无关，因为它是一个SQL问题）。
菜单选择将向托管bean发送用户选择的变量，并使用预准备语句使用用户选择的菜单中的信息搜索数据库表（如果有的话）。
如果用户将菜单项保留为null，则应搜索所有内容。
如果用户将1或2或3个菜单项留下信息，而另一个留空，则应进行相应的搜索。
我的问题是如何在没有附加到适当的sql语句的bean中的一堆if / then语句的情况下执行此操作？
还是有一个更好的sql语句我可以做到这一切吗？
我在Java中使用预备语句。
我试过这个：
if（product！= null& amp; ;& location！= null&& courseType！= null&& category！= null）{
pstmt = conn.prepareStatement（select * FROM Courses WHERE
+product = ？
+和location =？
+和courseType =？
+和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课程WHERE
+location =？
+and courseType =？
+and category =？）;
pstmt.setString（1，location）;
pstmt.setString（2，courseType）;
pstmt.setString（3，category）;
}
等等但我必须为每个案例做16次1为空而不是其他？
必须有一个更聪明的方法（通过使用1个sql语句或只有几个java if / then语句？）
更新感谢Luiggi Mendoza！我的代码是这样的：
pstmt = conn.prepareStatement（select * FROM Courses WHERE
+（ product =？或？为null）
+和（location =？或？为null）
+和（courseType =？或？为null）
+和（category =？或？为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（）;
更新是的我不得不使用=而不是null为不同的mysql数据库（可能不同
我假设您的 NULL 选择的值（因为我不知道使用 PreparedStatement 的另一种方式）为'0' （仅作为示例）。
知道这一点，我倾向于使用这种SQL：
SELECT *
FROM Courses
WHERE
（product =？或？='0'）
and（location =？或？='0'）
和（courseType =？或？='0'）
和（category =？或？='0'）
使用此方法，您可以利用 IF 用法，让数据库处理工作。唯一不好的一点是你应该设置每个变量两次（这一生中没有任何东西是空的= \）。
<编辑：我已根据您的要求进行了测试。首先是SQL表和内容：
create table pruebaMultiSelect
（id int primary key auto_increment，
nombre varchar（50），
tipo varchar（10），
categoria varchar（10））;
插入pruebaMultiSelect值
（null，'Luiggi'，'A'，'X'）;
插入pruebaMultiSelect值
（null，'Thomas'，'A'，'Y'）;
插入pruebaMultiSelect值
（null，'Jose'，'B'，'X'）;
插入pruebaMultiSelect值
（null，'Trina'，'B'，'Y'）;
现在，相关的Java代码：
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 \ n，rs.getInt（1），rs.getString（2））;
}
} catch（ClassNotFoundException e）{
e.printStackTrace（）;
} finally {
rs.close（）;
pstmt.close（）;
con.close（）;
}
}
public Connection getConnection（）抛出SQLException，ClassNotFoundException {
Class.forName（com.mysql.jdbc.Driver）;
返回DriverManager.getConnection（jdbc：mysql：// localhost：3306 / luiggi_test，user，password）;
}
public static void main（String [] args）抛出SQLException {
//这将返回所有数据
String p1 =EMPTY;
String p2 =EMPTY;
new DBTest（）。testPreparedStatement（p1，p2）;
}
}
使用MySQL 5.1.18测试。
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).
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.
If the user leaves 1 or 2 or 3 of the menu items with information, and the other one null, it should search accordingly.
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?
Or is there one better sql statement I can make that does all of this?
I am using a prepared statement in Java.
I tried this:
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);
}
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?)
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();
Update yeah I had to use = "" instead of is null for a different mysql database (maybe different versions or something)
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).
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')
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 =\ ).
EDIT: 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');
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\n", 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);
}
}
Tested using MySQL 5.1.18.
这篇关于SQL准备语句如何通过多种可能的菜单选择进行选择？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
