SQL预处理语句如何通过多个可能的菜单选择进行选择？

Question

SQL预处理语句如何通过多个可能的菜单选择进行选择？

javasqljdbc

8

所以我有四个菜单选项（产品、位置、课程类型和类别），所有这些都可以为空（使用JSF编写，但这与此问题无关，因为这是一个SQL问题）。

菜单选择将向托管bean发送用户选择的变量，并使用准备好的语句使用用户选择的菜单信息（如果有）搜索数据库表。

如果用户将菜单项留空，则应搜索所有内容。

如果用户留下1个或2个或3个带有信息的菜单项，并使其他一个为空，则应相应地进行搜索。

我的问题是如何在不使用大量if / then语句的情况下完成这项工作，而是将其附加到适当的SQL语句中？

还是有更好的SQL语句可以做到这一点？

我在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次？肯定有更聪明的方法（可以使用一个SQL语句或者只用几个Java if/else语句）。

感谢Luiggi Mendoza！我的代码现在是这样工作的：

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数据库中使用 = "" 而不是 is null（可能是不同的版本或其他原因）。

- Armando Perea

2个回答

1

我倾向于使用像这样的辅助方法来构建动态查询：

StringBuilder query = new StringBuidler();
query.append("SELECT foo, bar FROM baz");
query.append(buildProductClause(product));
query.append(buildLocationClause(location));
query.append(buildCourseTypeClause(courseType));
// etc...

如果您仍想使用预处理语句，可以使用成员字段来构建参数数组。

- jahroy

这是一个不错的方法，但我发现当你想/需要添加新列来检查时（尤其是在FROM部分添加新表时），它很难维护。我只在FROM或ORDER BY栏需要动态填充时才尝试使用此方法。 - Luiggi Mendoza

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Luiggi Mendoza · Accepted Answer

假设您对于选择的 NULL 值有一个默认值（因为我不知道使用 PreparedStatement 进行其他方式），例如 '0'。

了解这一点后，我倾向于使用此类 SQL：

SELECT *
FROM Courses
WHERE
    (product = ? or ? = '0')
    and (location = ?  or ? = '0')
    and (courseType = ? or ? = '0')
    and (category = ? or ? = '0')

使用这种方法，您可以利用IF使用方法，并让数据库处理工作。唯一不好的是，您应该将每个变量设置两次（在这个生活中没有免费的午餐= \）。

编辑：我已根据您的要求进行了测试。首先是SQL表和内容：

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代码如下：

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);
    }
}

本测试使用MySQL 5.1.18进行。