CODEWITHSUNDEEP
javamysqlswing
Elated Coder
Elated Coder

Reputation: 322

Search the MYSQL Database filtering using either first name or last name typed in JTextfield on JTable

I want to use either last name or first name to filter result shown on JTable from the database such that when i type either Nikola or Tesla in the JTextfield, the row with either of the names is filtered.

I have stored name as one field in the database i.e 'Nikola Tesla' when i type Nikola, it is working right and when i type Tesla it shows no result.

I have one field for name that stores both names.

I don't want to have separate First_Name and Last_Name field.

Please suggest what i should add on my code shown below:

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: 1

Views: 715

Answers (2)

Youcef LAIDANI
Youcef LAIDANI

Reputation: 59960

The true solution is this, you should to use %% inside quots '%%', without quotes you get that error:

LIKE '%Name%'

so, with prepared statement we should to change Name with ? and the result is like that:

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";

i hope this can help you, good luck.

Upvotes: 1

SamB
SamB

Reputation: 1710

You need to add % before the jTextFieldSearch.getText() also. See below

pstmt.setString(1, "%" + jTextFieldSearch.getText() + "%");

Upvotes: 1

Related Questions