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 Arshad · Accepted Answer · 2019-08-01 11:42:17Z

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 1, 2019 at 11:42

answered Aug 1, 2019 at 11:30

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

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

7 Comments

Dhirendra Singh Bisht Over a year ago

yes its workingnow, but its showing nothing in the table after the query execution

Salman Arshad Over a year ago

Does the table contain the specific string? like foo=textfieldcontentbar? Is the = sign intentional?

Dhirendra Singh Bisht Over a year ago

@SamlanA removed the = sign now its working. thanks. Can you please explain the reason for the = sign?

Salman Arshad Over a year ago

You had it in your original code so I added it as-is!

Dhirendra Singh Bisht Over a year ago

ok. One more thing can we do anything to ignore the case of the substring its searching

|

Collectives™ on Stack Overflow

MySQL like query for searching substring in a database table

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related