Всем привет, нужна помощь.
Имеется следующий элемент веб страницы:
При добавлении на этой странице в критерий поиска позиции "roles" выбрасывается следующая ошибка:
[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] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 42001, SQLState: 42001
2019-03-07 08:40:40.732 ERROR 8380 --- [nio-8080-exec-7] o.h.engine.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] o.a.c.c.C.[.[.[/].[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]
Ниже код, который эту ошибку вызывает. Подскажите пожалуйста, в чем может быть причина и как это можно исправить?
<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);
}
Виртуальный выделенный сервер (VDS) становится отличным выбором