replace string in java prepared mysql query statement?

Question

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

Andreas · Accepted Answer · 2020-12-16 15:36:22Z

2

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()) {
        ...
    }
}

answered Dec 16, 2020 at 15:36

Andreas

160k13 gold badges164 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eritrean · Accepted Answer · 2020-12-16 16:10:02Z

2

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

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

answered Dec 16, 2020 at 16:10

Eritrean

16.6k3 gold badges25 silver badges28 bronze badges

Collectives™ on Stack Overflow

replace string in java prepared mysql query statement?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related