Ошибка при вызове команды select из базы h2

125
12 ноября 2019, 10:40

Всем привет, нужна помощь.

Имеется следующий элемент веб страницы:

При добавлении на этой странице в критерий поиска позиции "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);
}
READ ALSO
WebView и CSS переменные

WebView и CSS переменные

Столкнулся с проблемой что WebView игнорирует CSS переменные

161
JavaScript. 2 ошибки Uncaught ReferenceError

JavaScript. 2 ошибки Uncaught ReferenceError

Новичок в JavaScriptНе могу понять в чем проблема

130