IDE: IntelliJ SDK: JAVA 11.0 SQL Client: postgreSQL
My DAO contains the following @Override and I'm running into a case-insensitive issue. As much as I try, I have not been able to figure out how to make the postgreSQL search case-insensitive. My testing condition is also at the bottom. Any suggestions?
@Override
public List<Employee> searchEmployeesByName(String firstNameSearch, String lastNameSearch) {
List<Employee> employees = new ArrayList<>();
String sql = "SELECT employee_id, department_id, first_name, last_name, birth_date, hire_date " +
"FROM employee " +
"WHERE first_name LIKE ? AND last_name LIKE ?;";
firstNameSearch = "%" + firstNameSearch + "%";
lastNameSearch = "%" + lastNameSearch + "%";
SqlRowSet results = jdbcTemplate.queryForRowSet(sql, firstNameSearch, lastNameSearch);
while (results.next()) {
employees.add(mapRowToEmployee(results));
}
return employees;
}
@Test
public void employee_added_to_project_is_in_list_of_employees_for_project() {
sut.addEmployeeToProject(1L, 3L);
List<Employee> employees = sut.getEmployeesByProjectId(1L);
Assert.assertEquals("addEmployeeToProject didn't increase number of employees assigned to project",
2, employees.size());
assertEmployeesMatch("addEmployeeToProject assigned wrong employee to project", EMPLOYEE_3, employees.get(1));
}
If you want case-insensitive matches with PostgreSQL, use ILIKE instead of LIKE:
The key word ILIKE can be used instead of LIKE to make the match case-insensitive according to the active locale. This is not in the SQL standard but is a PostgreSQL extension.