Brennan Bennett Brennan Bennett - 4 months ago 8
MySQL Question

How do I search a database using "LIKE %" using jdbc/Java?

I am trying to something like this, but I am getting a bad sql grammar error.

private static final String SQL_SEARCH_KEYWORD = "SELECT * FROM job_posting where job_title like ?% ";


I can get it working using this, but it only returns exact macthes. I need a query with the % after the string that is entered.

private static final String SQL_SEARCH_KEYWORD = "SELECT * FROM job_posting where job_title like ? ";


Here is the rest of the code

@Override
public List<JobPosting> searchKeyword(String search){
return jdbc.query(SQL_SEARCH_KEYWORD, new JobMapper(), search);
}

private static final class JobMapper implements RowMapper<JobPosting> {

@Override
public JobPosting mapRow(ResultSet rs, int i) throws SQLException {

JobPosting jp = new JobPosting();

jp.setId(rs.getInt("id"));
jp.setJobTitle(rs.getString("job_title"));
jp.setDescription(rs.getString("job_desc"));
jp.setCityState(rs.getString("city_state"));
jp.setZip(rs.getString("zip"));
jp.setJobType(rs.getString("job_type"));
jp.setSalary(rs.getString("salary"));
jp.setDateCreated(rs.getDate("date_created"));

return jp;

}

}


and here is the test I am running to test it

List<JobPosting> results = jpDao.searchKeyword("g");
Assert.assertTrue(results.size() > 0);

Answer

Add % in the string inside java.

In your case it would be:

private static final String SQL_SEARCH_KEYWORD = "SELECT * FROM job_posting where job_title like ? ";

...

return jdbc.query(SQL_SEARCH_KEYWORD, new JobMapper(), search + "%");
Comments