Search Filter in Java and MySQL database

Question

I am writing a code in Java where user can type a last name in a JTextField named lastname and then search for possible match in MySQL database. Say for example, user begins to type letter "M" (case insensitive and without double quotes), all the last name that starts with letter "M*" will display on JTable. If user types a second letter, say for example "A", the results on JTable will only display last names with "MA", then if user types third letter, say for example "N", JTable will only display all the last names with "MAN***" and so on..

I have read about

SELECT * FROM table WHERE lastname LIKE value

and tried to use it on my program, however, I am getting an error.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxError.Exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%' at line 1

Here's my partial code in event when there's key pressed in JTextField lastname:

private void lastNameKeyPressed(java.awt.event.KeyEvent evt) {                                    

  Connection conn = null;

    String url = "jdbc:mysql://localhost:3306/";
    String dbName = "students_dbs";
    Statement stmt = null;
    ResultSet result = null;
    String driver = "com.mysql.jdbc.Driver";
    String databaseUserName = "user1";
    String databasePassword = "test";
    PreparedStatement pst = null;

    try{
    conn = DriverManager.getConnection(url + dbName, databaseUserName, databasePassword);
    stmt = conn.createStatement();
    System.out.println("Connected to the database.");
    }catch(Exception e){
        System.out.println("Failed to connect ot the database.");
    }


try{
String sql = "SELECT studentno, lastname, firstname, middlename FROM student WHERE lastname LIKE '%" + lastname.getText() + "'%";
pst = conn.prepareStatement(sql);
result = pst.executeQuery();
studentsTable.setModel(DbUtils.resultSetToTableModel(result));
}
catch(Exception e){
JOptionPane.showMessageDialog(null, e);
}  
}

I googled it for a day or two now, however, I am stuck. I'm kinda new in using LIKE in MySQL. Any kind help is very much appreciated! Thank you in advance!

Answer 1

The position of the last ' char is wrong:

String sql = "SELECT studentno, lastname, firstname, middlename FROM student 
              WHERE lastname LIKE '% " + lastname.getText() + " '% ";

should be:

String sql = "SELECT studentno, lastname, firstname, middlename FROM student 
              WHERE lastname LIKE '% " + lastname.getText() + " %' ";