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

by

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.

More Related Contents:

  1. Hours and seconds are lost when storing a date in the database [closed]
  2. Simulate Result set closing failed [closed]
  3. Multiple queries executed in java in single statement
  4. Insert & fetch java.time.LocalDate objects to/from an SQL database such as H2
  5. Using setDate in PreparedStatement
  6. Efficient way to do batch INSERTS with JDBC
  7. How to execute IN() SQL queries with Spring’s JDBCTemplate effectively?
  8. How do I get the size of a java.sql.ResultSet?
  9. How can I get the SQL of a PreparedStatement?
  10. What is the best approach using JDBC for parameterizing an IN clause? [duplicate]
  11. How does a PreparedStatement avoid or prevent SQL injection?
  12. Variable column names using prepared statements
  13. MySQL and JDBC with rewriteBatchedStatements=true
  14. java.sql.SQLException: Exhausted Resultset
  15. How to enable logging for SQL statements when using JDBC
  16. Getting output from dbms_output.get_lines using JDBC
  17. handling DATETIME values 0000-00-00 00:00:00 in JDBC
  18. java.sql.SQLException: No suitable driver found for jdbc:mysql://localhost:3306/dbname [duplicate]
  19. JDBC ResultSet get columns with table alias
  20. Java – Can’t use ResultSet after connection close
  21. Timezones in SQL DATE vs java.sql.Date
  22. Getting an exception ORA-00942: table or view does not exist – when inserting into an existing table
  23. dll missing in JDBC
  24. How do I use PostgreSQL JSON(B) operators containing a question mark “?” via JDBC
  25. Inserting null to an Integer column using JDBC
  26. How to use prepared statement for select query in Java?
  27. A datetime equivalent in java.sql ? (is there a java.sql.datetime ?)
  28. Java JDBC connection status
  29. MyBatis executing multiple sql statements in one go, is that possible?
  30. Searching between dates in SQL with JDBC?

Leave a Comment