1

I want to load data into a jTable when user searches for some relevant details by entering name (a person can have a first name and a last name). I have used below query and it is not giving me results. Please tell me what's wrong here.

Query - "SELECT id, personal_id , first_name , last_name FROM person_profile WHERE first_name LIKE ?'%' OR last_name LIKE ?'%';"

public static Profile searchProfileByName(String name) {
    Profile profile = new Profile();
    try {
        Connection conn = DbConnect.getDBConnection();
        PreparedStatement pst = conn.prepareStatement("SELECT  `id`, `personal_id` , `first_name` , `last_name` FROM  `person_profile` WHERE  `first_name` LIKE  ?'%' OR  `last_name` LIKE  ?'%';");
        pst.setString(1, name);
        pst.setString(2, name);
        ResultSet rs = pst.executeQuery();

        while (rs.next()) {
            profile.setId(rs.getString(1));
            profile.setPersonalId(rs.getString(2));
            profile.setFirstName(rs.getString(3));
            profile.setLastName(rs.getString(4));
        }
        conn.close();

        return profile;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }
}
Erandi Ganepola
  • 303
  • 3
  • 12
  • 1
    http://stackoverflow.com/questions/8247970/using-like-wildcard-in-prepared-statement – Reimeus Oct 23 '16 at 22:03
  • Your query doesn't make sense. What are you trying to do? – shmosel Oct 23 '16 at 22:08
  • Thank you, the link has the answer. – Erandi Ganepola Oct 23 '16 at 22:11
  • 1
    You should have provided the stacktrace given by the program. It most likely says, that the SQL syntax is wrong: `LIKE ?'%'` should be `LIKE ?||'%'` since the questionmark is a variable, which must be (syntactically correct) concatinated with the SQL-wildcard literal string. – Timothy Truckle Oct 23 '16 at 22:13

1 Answers1

1

I am used to just concatenate the '%' in the parameter like this:

PreparedStatement pst = conn.prepareStatement("SELECT first_name FROM  person_profile WHERE first_name LIKE ?");
...
pst.setString(1, StringUtils.defaultIfBlank(name, "") + "%");
Henrique Smoco
  • 71
  • 2