Hello everyone, I need help.
The following web page element is available: 
When adding a "roles" position to the search criteria on this page, the following error is thrown:
[SearchCriteriaForSpecification(key=firstName, operation=:, value=), SearchCriteriaForSpecification(key=lastName, operation=:, value=), SearchCriteriaForSpecification(key=email, operation=:, value=), SearchCriteriaForSpecification(key=enabled, operation=:, value=true), SearchCriteriaForSpecification(key=roles, operation=:, value=[ADMIN])]123 Hibernate: select count(user0_.id) as col_0_0_ from users user0_ cross join user_roles roles1_ where user0_.id=roles1_.user_id and (upper(user0_.first_name) like ?) and (upper(user0_.first_name) like ?) and (upper(user0_.last_name) like ?) and (upper(user0_.email) like ?) and user0_.enabled=1 and .=? 2019-03-07 08:40:40.732 WARN 8380 --- [nio-8080-exec-7] ohengine.jdbc.spi.SqlExceptionHelper : SQL Error: 42001, SQLState: 42001 2019-03-07 08:40:40.732 ERROR 8380 --- [nio-8080-exec-7] ohengine.jdbc.spi.SqlExceptionHelper : Π‘ΠΈΠ½ΡΠ°ΠΊΡΠΈΡΠ΅ΡΠΊΠ°Ρ ΠΎΡΠΈΠ±ΠΊΠ° Π² Π²ΡΡΠ°ΠΆΠ΅Π½ΠΈΠΈ SQL "SELECT COUNT(USER0_.ID) AS COL_0_0_ FROM USERS USER0_ CROSS JOIN USER_ROLES ROLES1_ WHERE USER0_.ID=ROLES1_.USER_ID AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.LAST_NAME) LIKE ?) AND (UPPER(USER0_.EMAIL) LIKE ?) AND USER0_.ENABLED=1 AND .[*]=? "; ΠΎΠΆΠΈΠ΄Π°Π»ΠΎΡΡ "NOT, EXISTS, INTERSECTS, SELECT, FROM, WITH" Syntax error in SQL statement "SELECT COUNT(USER0_.ID) AS COL_0_0_ FROM USERS USER0_ CROSS JOIN USER_ROLES ROLES1_ WHERE USER0_.ID=ROLES1_.USER_ID AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.LAST_NAME) LIKE ?) AND (UPPER(USER0_.EMAIL) LIKE ?) AND USER0_.ENABLED=1 AND .[*]=? "; expected "NOT, EXISTS, INTERSECTS, SELECT, FROM, WITH"; SQL statement: select count(user0_.id) as col_0_0_ from users user0_ cross join user_roles roles1_ where user0_.id=roles1_.user_id and (upper(user0_.first_name) like ?) and (upper(user0_.first_name) like ?) and (upper(user0_.last_name) like ?) and (upper(user0_.email) like ?) and user0_.enabled=1 and .=? [42001-192] 2019-03-07 08:40:40.753 ERROR 8380 --- [nio-8080-exec-7] oaccC[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select count(user0_.id) as col_0_0_ from users user0_ cross join user_roles roles1_ where user0_.id=roles1_.user_id and (upper(user0_.first_name) like ?) and (upper(user0_.first_name) like ?) and (upper(user0_.last_name) like ?) and (upper(user0_.email) like ?) and user0_.enabled=1 and .=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement] with root cause org.h2.jdbc.JdbcSQLException: Π‘ΠΈΠ½ΡΠ°ΠΊΡΠΈΡΠ΅ΡΠΊΠ°Ρ ΠΎΡΠΈΠ±ΠΊΠ° Π² Π²ΡΡΠ°ΠΆΠ΅Π½ΠΈΠΈ SQL "SELECT COUNT(USER0_.ID) AS COL_0_0_ FROM USERS USER0_ CROSS JOIN USER_ROLES ROLES1_ WHERE USER0_.ID=ROLES1_.USER_ID AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.LAST_NAME) LIKE ?) AND (UPPER(USER0_.EMAIL) LIKE ?) AND USER0_.ENABLED=1 AND .[*]=? "; ΠΎΠΆΠΈΠ΄Π°Π»ΠΎΡΡ "NOT, EXISTS, INTERSECTS, SELECT, FROM, WITH" Syntax error in SQL statement "SELECT COUNT(USER0_.ID) AS COL_0_0_ FROM USERS USER0_ CROSS JOIN USER_ROLES ROLES1_ WHERE USER0_.ID=ROLES1_.USER_ID AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.FIRST_NAME) LIKE ?) AND (UPPER(USER0_.LAST_NAME) LIKE ?) AND (UPPER(USER0_.EMAIL) LIKE ?) AND USER0_.ENABLED=1 AND .[*]=? "; expected "NOT, EXISTS, INTERSECTS, SELECT, FROM, WITH"; SQL statement: select count(user0_.id) as col_0_0_ from users user0_ cross join user_roles roles1_ where user0_.id=roles1_.user_id and (upper(user0_.first_name) like ?) and (upper(user0_.first_name) like ?) and (upper(user0_.last_name) like ?) and (upper(user0_.email) like ?) and user0_.enabled=1 and .=? [42001-192] Below is the code that causes this error. Please tell me what could be the reason and how can I fix it?
<div class="container"> <div class="row"> <div class="col-md-2" style="padding-bottom: 10px"> <input id="showFilters" type="button" class="btn btn-default" th:value="#{StudentController.filters}"/> </div> </div> <div class="row" hidden="hidden" id="searchForm"> <form id="searchForm"> <div class="col-md-12 well"> <div class="row"> <div class="col-xs-3 form-group"> <input id="firstName" type="text" class="form-control" th:placeholder="#{UserController.name}" name="firstName" th:value="${userCriteria.firstName}"/> </div> <div class="col-xs-3 form-group"> <input id="lastName" type="text" class="form-control" th:placeholder="#{UserController.surname}" name="lastName" th:value="${userCriteria.lastName}"/> </div> <div class="col-xs-3 form-group"> <input id="email" type="text" class="form-control" th:placeholder="#{UserController.mail}" name="email" th:value="${userCriteria.email}"/> </div> <div class="col-xs-3 form-group"> <select name="enabled" id="enabled_input" class="form-control"> <option value="true" th:selected="${userCriteria.enabled == true}" th:text="#{yes}"></option> <option value="false" th:selected="${userCriteria.enabled == false}" th:text="#{no}"></option> <option value="" th:selected="${userCriteria.enabled == null}" th:text="#{soso}"></option> </select> </div> <div class="col-xs-3 form-group"> <label for="roles" th:text="#{UserController.add.roles}"></label> <select id="roles" class="form-control" name="roles" th:readonly="${successAddUser}" th:disabled="${successAddUser}"> <option value="" th:selected="${userCriteria.roles == null}" th:text="#{soso}"> </option> <option th:value="COUCH" th:selected="${userCriteria.roles == 'COUCH'}" th:text="#{user.couch}"> </option> <option value="ADMIN" th:selected="${userCriteria.roles == 'ADMIN'}" th:text="#{user.admin}"> </option> <option value="VIEWER" th:selected="${userCriteria.roles == 'VIEWER'}" th:text="#{user.user}"> </option> <option value="TUTOR" th:selected="${userCriteria.roles == 'TUTOR'}" th:text="#{user.tutor}"> </option> <option value="MODER" th:selected="${userCriteria.roles == 'MODER'}" th:text="#{user.moder}"> </option> </select> </div> <div class="row"> <div class="col-md-12" align="center"> <input type="submit" style="margin-right: 10px" class="btn btn-default filters" th:value="#{apply}"/> <input id="clearButton" type="button" style="margin-left: 10px" class="btn btn-primary" th:value="#{clear}"/> </div> </div> </div> </div> </form> </div> .
@Controller @Secured("ROLE_ADMIN") @RequestMapping(USERS) public class UserController extends BaseController { @Autowired private UserService userService; @Autowired private UserValidator userValidator; @RequestMapping(value = LISTING) public String userListing(@ModelAttribute("userCriteria") UserCriteria userCriteria, Pageable pageable, @RequestParam(required = false) String successAddUser, Model model) { Page<UserListingDTO> users = userService.getAllUsersForListing(pageable, userCriteria); model.addAttribute("userListing", users); if (successAddUser != null) { model.addAttribute("successAddUser", true); } return "user/users"; } } .
@Data @Accessors(chain = true) public class UserCriteria implements SearchCriteria { private String firstName; private String lastName; private String email; private Boolean enabled = true; private Set <UserRole> roles; } .
public interface UserService{ UserDTO byId(Long id); UserDTO findByEmail(String Email); List<UserDTO> list(); UserDTO saveOrUpdate(UserDTO userDTO); void delete(Long id); long count(); Page<UserListingDTO> getAllUsersForListing(Pageable pageable, UserCriteria userCriteria); List<UserFullNameDTO> getListOfUsersByRole(UserRole userRole); UserFullNameDTO getForStudyGroup(Long id); } .
import static com.jborned.vverh.service.util.UserUtility.buildUserSpecification; @Service @Transactional public class UserServiceImpl implements UserService { @Autowired UserRepository userRepository; @Autowired PasswordEncoder passwordEncoder; @Override public Page<UserListingDTO> getAllUsersForListing(Pageable pageable, UserCriteria userCriteria) { PageRequest pageRequest = new PageRequest(pageable.getPageNumber(), pageable.getPageSize()); Page<User> userList; if (userCriteria == null) { userList = userRepository.findAll(pageRequest); } else { SpecificationBuilder<User> builder = buildUserSpecification(userCriteria); userList = userRepository.findAll(builder.buildSpecification(), pageRequest); } Page<UserListingDTO> userPage = userList.map(this::convertToListingDTO); return userPage; } } .
public class UserUtility { public static SpecificationBuilder<User> buildUserSpecification(UserCriteria userCriteria) { SpecificationBuilder<User> builder = new SpecificationBuilder<>(); if (userCriteria.getFirstName() != null) { builder.add("firstName", ":", userCriteria.getFirstName()); } if (userCriteria.getLastName() != null) { builder.add("lastName", ":", userCriteria.getLastName()); } if (userCriteria.getEmail() != null) { builder.add("email", ":", userCriteria.getEmail()); } if (userCriteria.getEnabled() != null) { builder.add("enabled", ":", userCriteria.getEnabled()); } if (userCriteria.getRoles() != null) { builder.add("roles",":", userCriteria.getRoles());//todo del? not true } return builder; } } .
@NoArgsConstructor @AllArgsConstructor public class SpecificationBuilder<T> { private List<SearchCriteriaForSpecification> criteriaList = new ArrayList<>(); public void add(String key, String operation, Object value) { SearchCriteriaForSpecification specification; if (value instanceof String) { String name = (String) value; name = name.trim(); String[] nameSplit = name.split(" +"); for (int i = 0; i < nameSplit.length; i++) { specification = new SearchCriteriaForSpecification() .setKey(key) .setOperation(operation) .setValue(nameSplit[i]); criteriaList.add(specification); } } else { specification = new SearchCriteriaForSpecification() .setKey(key) .setOperation(operation) .setValue(value); criteriaList.add(specification); } } public Specification<T> buildSpecification() { if (criteriaList.size() == 0) { return null; } List<Specification<T>> specifications = new ArrayList<>(); for (SearchCriteriaForSpecification criteria : criteriaList) { specifications.add(new UpSpecification<T>(criteria)); } Specification<T> result = specifications.get(0); for (int i = 0; i < specifications.size(); i++) { result = Specifications.where(result).and(specifications.get(i)); } return result; } } .
@Data @Accessors(chain = true) @AllArgsConstructor @NoArgsConstructor public class SearchCriteriaForSpecification { private String key; private String operation; private Object value; } .
public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> { User findByEmail(String email); } 