CODEWITHSUNDEEP
javamysqlswingautocomplete
Elated Coder
Elated Coder

Reputation: 322

Searching the Mysql database with Autocomplete on JTable using textfield

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();} 
}

Upvotes: 0

Views: 1879

Answers (1)

Elated Coder
Elated Coder

Reputation: 322

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();} 
}

Upvotes: 2

Related Questions