Intellipaat Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in Java by (10.2k points)

I am using prepared statements to execute mysql database queries. And I want to implement a search functionality based on a keyword of sorts.

For that I need to use LIKE keyword, that much I know. And I have also used prepared statements before, but I do not know how to use it with LIKE because from the following code where would I add the 'keyword%'?

Can I directly use it in the pstmt.setString(1, notes) as (1, notes+"%") or something like that. I see a lot of posts on this on the web but no good answer anywhere.

PreparedStatement pstmt = con.prepareStatement(

      "SELECT * FROM analysis WHERE notes like ?");

pstmt.setString(1, notes);

ResultSet rs = pstmt.executeQuery();

1 Answer

0 votes
by (46k points)

You need to set it in the value itself, not in the prepared statement SQL string.

So, this should do for a prefix-match:

notes = notes

    .replace("!", "!!")

    .replace("%", "!%")

    .replace("_", "!_")

    .replace("[", "![");

PreparedStatement pstmt = con.prepareStatement(

        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");

pstmt.setString(1, notes + "%");

or a suffix-match:

pstmt.setString(1, "%" + notes);

or a global match:

pstmt.setString(1, "%" + notes + "%");

Related questions

31k questions

32.8k answers

501 comments

693 users

Browse Categories

...