Searching the Mysql database with Autocomplete on JTable using textfield

Question

Could you please tell me what to add on my code so that when i type a letter in the textfield, before i finish typing the search result already start showing on jtable without waiting for me to type the whole word?

Below please find my code for key released event on the textbox. Thank you for your help.

private void jTextFieldSearchKeyReleased(java.awt.event.KeyEvent evt) {                                             
   try{
        String selected=(String)jComboBoxSelected.getSelectedItem();
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"
        + "employee_certificate","root","");

          String sql="SELECT stuff.Emp_Id,stuff.Emp_Name, stuff.Department, "
            + "certificate.Cert_Code, certificate.Cert_Name,\n" +
            "certificate.Cert, certificate.Vendor, certificate.Date_Taken, "
            + "certificate.Expiry_Date FROM stuff LEFT JOIN certificate"
              + " ON stuff.Emp_Id=certificate.Emp_Id  "
                + "WHERE "+selected+" =? ORDER BY stuff.Emp_Name\n" ;

  PreparedStatement  pstmt=con.prepareStatement(sql);
   pstmt.setString(1, jTextFieldSearch.getText());
     ResultSet rs=pstmt.executeQuery();
       jTable1.setModel(DbUtils.resultSetToTableModel(rs));
        pstmt.close();
           //con.close();
    }
    catch(Exception ex){ex.printStackTrace();} 
}

For better help sooner, post a [MCVE] or [Short, Self Contained, Correct Example](http://www.sscce.org/). Note that for this problem, it is only necessary to deal with code to the point that an action is performed on typing in the text field, so the DB itself is redundant. — Andrew Thompson, Jul 03 '16 at 18:40
Hi Andrew...that's the compete code i am using for key released event on my text field.i have copy pasted as it is — Elated Coder, Jul 03 '16 at 19:07
`that's the compete code` - how to you expect us to compile and test that code? Read the comment and read the links provided. The code you posted is NOT an MCVE/SSCCE. You were also asked to NOT post the DB code since we don't have access to your database. Where is your debug code? Did you verify the value of the text field when the method is invoked? We can't test your code. Only you have access to the database. — camickr, Jul 03 '16 at 19:10
Hi camickr...nothing is wrong with my code...it is working fine....i only need to know what to add to the code above so that autocomplete can work.the result are showing after i finish typing not as i type.No error is returned.and as i said nothing is wrong with the code i just need to adjust it to allow autocomplete.i hope you understand my need now.tell me if you need more info — Elated Coder, Jul 03 '16 at 20:17
The code should update as you type each letter. If it doesn't we can't help you which is why you need to do basic debugging. As I already suggested you need to verify that the code is executed for every character typed. — camickr, Jul 03 '16 at 21:42
Hi canickr. i am sorry i forgot to say that i am so new in java programming.i even don't know how to debug...i wish you can help on that and i would be glad if you share a similar code because i don't know exactly how to do that — Elated Coder, Jul 04 '16 at 06:40
You could try to add a `DocumentListener` to your `JTextField` like here http://stackoverflow.com/a/3953219/1368690 ... and everytime someone types something, you query the database. In general: **THIS CAN BE VERY INEFFICIENT**. If possible, query all results, save it in an a list of objects and if the user types, look if the result matches one of the objects in the list. But it's hard to give good advice here like Andrew and camickr said, you need to post more code so we understand what and how you want to do. — hamena314, Jul 05 '16 at 07:34

score 2 · Accepted Answer · edited Dec 28 '17 at 07:35

After a two day struggle finally i got an answer...i just needed to use LIKE '%' as shown.No one should suffer as i did

private void jTextFieldSearchKeyReleased(java.awt.event.KeyEvent evt) {                                         
    try{
        String selected=(String)jComboBoxSelected.getSelectedItem();
       Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+ "employee_certificate","root","");

          String sql="SELECT stuff.Emp_Id,stuff.Emp_Name, stuff.Department, "
            + "certificate.Cert_Code, certificate.Cert_Name,\n" +
            "certificate.Cert, certificate.Vendor, certificate.Date_Taken, "
            + "certificate.Expiry_Date FROM stuff LEFT JOIN certificate"
              + " ON stuff.Emp_Id=certificate.Emp_Id  "
               +"WHERE "+selected+" LIKE ? ORDER BY stuff.Emp_Name\n" ; 
       PreparedStatement  pstmt=con.prepareStatement(sql);
       pstmt.setString(1,jTextFieldSearch.getText() + "%");
       ResultSet rs=pstmt.executeQuery();
       jTable1.setModel(DbUtils.resultSetToTableModel(rs));

        pstmt.close();
        con.close();
    }
    catch(Exception ex){ex.printStackTrace();} 
}

Searching the Mysql database with Autocomplete on JTable using textfield

1 Answers1