replace string in java prepared mysql query statement?

user_1324115 0
Viewed 118

In my java code i want to use a SELECT statement to retrieve an ID from a table. To do this, i use the prepareStatement java functionality.

PreparedStatement pst = con.prepareStatement(sql);
pst.setString(1, entry1);
pst.setString(2, entry2);
pst.setString(3, entry3);
ResultSet rs = pst.executeQuery();

entry1, entry2 and entry3 are variables. However some of these variables can be "null". So my sql statement then becomes:

SELECT ID FROM some_table WHERE col1="val1" AND col2="val2" AND col3=null;

This query will fail and so i have to change my query to this:

SELECT ID FROM some_table WHERE col1="val1" AND col2="val2" AND col3 IS NULL;

How do i do this? This is a small example and i have many tables and many columns that can sometimes have a null value. So to replace this part of the query would be the easiest option i can see. Can this be done or do i need to check all my variables before. many thanks!

Linda

replaceall prepared-statement mysql java
2 Answers

String concatenation and ternary conditional operator.

String sql = "SELECT ID" +
              " FROM some_table" +
             " WHERE col1 " + (entry1 != null ? "= ?" : "IS NULL") +
               " AND col2 " + (entry2 != null ? "= ?" : "IS NULL") +
               " AND col3 " + (entry3 != null ? "= ?" : "IS NULL");
try (PreparedStatement pst = con.prepareStatement(sql)) {
    int paramIdx = 0;
    if (entry1 != null)
        pst.setString(++paramIdx, entry1);
    if (entry2 != null)
        pst.setString(++paramIdx, entry2);
    if (entry3 != null)
        pst.setString(++paramIdx, entry3);
    try (ResultSet rs = pst.executeQuery()) {
        ...
    }
}
user_52211490

You can also try the NULL-safe equal (IS DISTINCT FROM) operator <=>

SELECT ID FROM some_table WHERE col1 <=> ? AND col2 <=> ? AND col3 <=> ?;
user_51769920
Related