MySQL like query for searching substring in a database table

Question

I have saved my data in my MySQL database table. I am using jTextField to take user input. I want to display the rows in jtable where the user input sub-string is a part.

Code I tried:

private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {                                         

        try{

            Class.forName("com.mysql.jdbc.Driver");
            Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/date","root","");
            String query="SELECT * FROM ok where day like '%=?%'";
            PreparedStatement prepstmt=conn.prepareStatement(query);
           prepstmt.setString(1,jTextField2.getText());   // include this for showing only particular row else remove this line
            ResultSet rs=prepstmt.executeQuery(); 
            jTable1.setModel(DbUtils.resultSetToTableModel(rs));

        }catch(Exception e){

            JOptionPane.showMessageDialog(null, e);
        }

    }

In my case the substring to be searched is entered by user in jTextField1 and I want to use that user input.

Please correct my **String query=""; **

Salman A · Answer 1 · 2019-08-01T11:42:17.670

1

I believe the library will not replace the ? inside strings. You need to do something like:

String query="SELECT * FROM ok WHERE day LIKE CONCAT('%', ?, '%')";

Alternately you can build the string using Java:

String query="SELECT * FROM ok WHERE day LIKE ?";
PreparedStatement prepstmt = conn.prepareStatement(query);
prepstmt.setString(1, '%' + jTextField2.getText() + '%');

You might want to remove % and _ characters from the user supplied string.

edited Aug 01 '19 at 11:42

answered Aug 01 '19 at 11:30

Salman A

262,204
82
430
521

yes its workingnow, but its showing nothing in the table after the query execution – Dhirendra Singh Bisht Aug 01 '19 at 11:35
Does the table contain the specific string? like `foo=textfieldcontentbar`? Is the `=` sign intentional? – Salman A Aug 01 '19 at 11:37
@SamlanA removed the = sign now its working. thanks. Can you please explain the reason for the = sign? – Dhirendra Singh Bisht Aug 01 '19 at 11:41
You had it in your original code so I added it as-is! – Salman A Aug 01 '19 at 11:42
ok. One more thing can we do anything to ignore the case of the substring its searching – Dhirendra Singh Bisht Aug 01 '19 at 11:44
if you mean to do case-insensitive select try modify query to SELECT * FROM ok WHERE LOWER(day) LIKE LOWER(?) and then do prestmt.setString – Raso Aug 01 '19 at 12:23
The case sensitivity of `=` and `LIKE` depends on how you have setup the database, tables and columns. See https://stackoverflow.com/questions/2876789/how-can-i-search-case-insensitive-in-a-column-using-like-wildcard – Salman A Aug 01 '19 at 14:04

MySQL like query for searching substring in a database table

1 Answers1