SQL prepared statement how to select via multiple possible menu selections?

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.

I tend to build dynamic queries with helper methods like this:

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

You could use a member field to build an array of arguments if you want to still use a prepared statement.

SQL prepared statement how to select via multiple possible menu selections?

Related

Recent Posts