org.postgresql.util.PSQLException: ОШИБКА: оператор не существует: character varying = bytea
Делаю на Spring Boot с Thymeleaf и PostgreSQL CRUD приложение. Для составного select запроса на поиск по БД использую Criteria Builder. При нажатии на кнопку Найти на странице приложение падает с ошибкой:
org.postgresql.util.PSQLException: ОШИБКА: оператор не существует: character varying = bytea
Подсказка: Оператор с данными именем и типами аргументов не найден. Возможно, вам следует добавить явные приведения типов.
Позиция: 165
В интернете пишут, что такое может быть, если какие-то поля содержат null, но у меня на все поля стоит проверка на null. Как это исправить, учитывая использование Criteria Builder?
Представление (объект поискового запроса называется searchRequest):
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:th="https://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>Собаки</title>
<style>
table {
width: 100%;
border: 4px double black;
border-collapse: collapse;
}
th {
text-align: left;
background: #ccc;
padding: 5px;
border: 1px solid black;
}
td {
padding: 5px;
border: 1px solid black;
}
</style>
</head>
<body>
<div th:if="${isUserAuth}">Выйти</div>
<div th:unless="${isUserAuth}">Войти</div>
<div sec:authorize="isAuthenticated()">
<a href="/logout">Logout</a>
</div>
<div sec:authorize="!isAuthenticated()">
<p>Hello, guest! Please <a href="/login">Login</a> or <a href="/signup">SignUp</a></p>
</div>
<div>Выберите сущность:
<form method="post" action="">
<input type="radio" id="dogChoice" value="dog"/><label for="dogChoice">Собаки</label>
</form>
</div>
<div><form th:method="POST" th:object:"${searchRequest}" th:action="@{/search}">
<select th:field="*{searchRequest.tableName}">
<option th:value="dogs">Собаки</option>
</select>
<input type="text" placeholder="по имени или кличке"/ th:field="*{searchRequest.nicknameOrName}">
<td>Порода:<select th:field="*{searchRequest.breed}">
<option value="all">Все</option>
<option th:each="breedType : ${T(com.example.demo6.entities.Dog.Breed).values()}"
th:value="${breedType.name()}" th:text="${breedType.type}"></option>
</select>
<td>Пол:<select th:field="*{searchRequest.gender}">
<option value="all">Все</option>
<option th:each="genderType : ${T(com.example.demo6.entities.Dog.Gender).values()}"
th:value="${genderType.name()}" th:text="${genderType.type}"></option>
</select></td>
<input type="submit" value="Найти"/></form></div>
<table border="1" color="black" border-collapse="collapse">
<tr>
<td><b>ID</b></td>
<td><b>Кличка</b></td>
<td><b>Имя</b></td>
<td><b>Пол</b></td>
<td><b>Порода</b></td>
<td><b>Дата рождения</b></td>
<td><b>Щенки</b></td>
<td><b>Добавлено</b></td>
<td><b>Изменено</b></td>
<td>Изменить</td>
<td>Удалить</td>
</tr>
<tr>
<form modelAttribute="dog" th:method="POST" th:object:"${dog}" th:action="@{/}" action="#">
<td><input type="submit" value="Добавить"/></td>
<td><input type="text" th:field="*{dog.nickname}"/></td>
<td><input type="text" th:field="*{dog.name}"/></td>
<td><select th:field="*{dog.gender}">
<option th:each="genderType : ${T(com.example.demo6.entities.Dog.Gender).values()}"
th:value="${genderType}" th:text="${genderType.type}"></option>
</select></td>
<td><select th:field="*{dog.breed}">
<option th:each="breedType : ${T(com.example.demo6.entities.Dog.Breed).values()}"
th:value="${breedType}" th:text="${breedType.type}"></option>
</select>
</td>
<td><input type="date" th:field="*{dog.dateOfBirth}"/></td>
<td><input type="number" th:field="*{dog.puppies}"/></td>
<td></td>
<td></td>
</form>
</tr>
<tr th:each="dog : ${dogs}">
<form id="edit" modelAttribute="dog" th:method="DELETE" th:object:"${dog}" th:action="@{/{id}(id=${dog.getId()})}" action="#">
<td th:text="${dog.id}"></td>
<td th:unless="${dog.isModifying}" th:text="${dog.nickname}"></td>
<td th:if="${dog.isModifying}" th:text="${dog.nickname}"><input type="text" th:field="*{dog.nickname}"/></td>
<td th:unless="${dog.isModifying}" th:text="${dog.name}"></td>
<td th:if="${dog.isModifying}" th:text="${dog.name}"><input type="text" th:field="*{dog.name}"/></td>
<td th:unless="${dog.isModifying}" th:text="${dog.gender.type}"></td>
<td th:if="${dog.isModifying}"><select th:field="*{dog.gender}">
<option th:each="genderType : ${T(com.example.demo6.entities.Dog.Gender).values()}"
th:value="${genderType}" th:text="${genderType.type}"></option>
</select></td>
<td th:unless="${dog.isModifying}" th:text="${dog.breed.type}"></td>
<td th:if="${dog.isModifying}"><select th:field="*{dog.breed}">
<option th:each="breedType : ${T(com.example.demo6.entities.Dog.Breed).values()}"
th:value="${breedType}" th:text="${breedType.type}"></option>
</select></td>
<td th:unless="${dog.isModifying}" th:text="${dog.dateOfBirth}"></td>
<td th:if="${dog.isModifying}"><input type="date" th:field="*{dog.dateOfBirth}"/></td>
<td th:unless="${dog.isModifying}" th:text="${dog.puppies}"></td>
<td th:if="${dog.isModifying}"><input type="number" th:field="*{dog.puppies}"/></td>
<td th:unless="${dog.isModifying}" th:text="${dog.modifiedAt}"></td>
<td th:unless="${dog.isModifying}" th:text="${dog.createdAt}"></td>
<td><input type="button" id="toedit" value="Изменить" onclick="toEdit()" form="edit"/></td>
<td><input type="submit" value="Удалить"</td>
</form>
</tr>
<tr>
</tr>
</table>
<script>
toedit.onclick = function()
{
document.getElementById('eachDog').innerHTML = document.getElementById(document.getElementById('edit').value).innerHTML;
alert("pashet");
};
</script>
</body>
</html>
Класс поиска:
package com.example.demo6.search;
import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.List;
import jakarta.persistence.EntityManager;
import jakarta.persistence.EntityManagerFactory;
import jakarta.persistence.Persistence;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.TypedQuery;
import jakarta.persistence.criteria.CriteriaBuilder;
import jakarta.persistence.criteria.CriteriaQuery;
import jakarta.persistence.criteria.Predicate;
import jakarta.persistence.criteria.Root;
import com.example.demo6.entities.Dog;
public class Search {
private static final StringBuilder SELECT = new StringBuilder("SELECT * FROM ");
private StringBuilder tableName;
private Integer id;
private StringBuilder nicknameOrName;
private StringBuilder breed;
private StringBuilder gender;
private Integer minPuppiesCount;
private LocalDate dateOfBirthFrom;
private LocalDate dateOfBirthTo;
private OffsetDateTime dateOfCreationFrom;
private OffsetDateTime dateOfCreationTo;
private OffsetDateTime dateOfModificationFrom;
private OffsetDateTime dateOfModificationTo;
private StringBuilder orderBy;
private Boolean descDirection;
private StringBuilder query;
@PersistenceContext
private EntityManager entityManager;
public Search(){
this.descDirection = false;
this.query = SELECT;
}
public Search(StringBuilder tableName, Integer id, StringBuilder nicknameOrName, StringBuilder breed, StringBuilder gender,
Integer minPuppiesCount, LocalDate dateOfBirthFrom, LocalDate dateOfBirthTo,
OffsetDateTime dateOfCreationFrom, OffsetDateTime dateOfCreationTo, OffsetDateTime dateOfModificationFrom,
OffsetDateTime dateOfModificationTo, StringBuilder orderBy) {
super();
this.tableName = tableName;
this.id = id;
this.nicknameOrName = nicknameOrName;
this.breed = breed;
this.gender = gender;
this.minPuppiesCount = minPuppiesCount;
this.dateOfBirthFrom = dateOfBirthFrom;
this.dateOfBirthTo = dateOfBirthTo;
this.dateOfCreationFrom = dateOfCreationFrom;
this.dateOfCreationTo = dateOfCreationTo;
this.dateOfModificationFrom = dateOfModificationFrom;
this.dateOfModificationTo = dateOfModificationTo;
this.orderBy = orderBy;
this.descDirection = false;
this.query = SELECT;
}
public List<Dog> getResults() {
EntityManagerFactory emf = Persistence.createEntityManagerFactory("myPersistence");
EntityManager em = emf.createEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Dog> cq = cb.createQuery(Dog.class);
Root<Dog> root = cq.from(Dog.class);
List<Predicate> predicates = new ArrayList<>();
if (id != null) predicates.add(cb.like(root.get("id"), "%" + id + "%"));
if (nicknameOrName != null && !nicknameOrName.isEmpty()) {
predicates.add(cb.or(
cb.like(root.get("nickname"), "%" + nicknameOrName + "%")
, cb.like(root.get("name"), "%" + nicknameOrName + "%")
));
}
if (breed != null && !breed.isEmpty() && breed.toString() != "all") predicates.add(cb.equal(root.get("breed"), breed));
if (gender != null && !gender.isEmpty() && gender.toString() != "all") predicates.add(cb.equal(root.get("gender"), gender));
if (minPuppiesCount != null && minPuppiesCount > 0) predicates.add(cb.greaterThanOrEqualTo(root.get("minPuppiesCount"), minPuppiesCount));
if (dateOfBirthFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("dateOfBirthFrom"), dateOfBirthFrom));
if (dateOfBirthTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("dateOfBirthTo"), dateOfBirthTo));
if (dateOfCreationFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("dateOfCreationFrom"), dateOfCreationFrom));
if (dateOfCreationTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("dateOfCreationTo"), dateOfCreationTo));
if (dateOfModificationFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("dateOfModificationFrom"), dateOfModificationFrom));
if (dateOfModificationTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("dateOfModificationTo"), dateOfModificationTo));
if (orderBy != null) {
if (descDirection != null && descDirection) {
cq.orderBy(cb.desc(root.get(orderBy.toString())));
} else {
cq.orderBy(cb.asc(root.get(orderBy.toString())));
}
}
if (!predicates.isEmpty()) {
cq.select(root).where(predicates.toArray(new Predicate[predicates.size()]));
} else {
cq.select(root);
}
TypedQuery<Dog> typedQuery = em.createQuery(cq);
return typedQuery.getResultList();
}
public StringBuilder getTableName() {
return tableName;
}
public void setTableName(StringBuilder tableName) {
this.tableName = tableName;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public StringBuilder getNicknameOrName() {
return nicknameOrName;
}
public void setNicknameOrName(StringBuilder nicknameOrName) {
this.nicknameOrName = nicknameOrName;
}
public StringBuilder getBreed() {
return breed;
}
public void setBreed(StringBuilder breed) {
this.breed = breed;
}
public StringBuilder getGender() {
return gender;
}
public void setGender(StringBuilder gender) {
this.gender = gender;
}
public Integer getMinPuppiesCount() {
return minPuppiesCount;
}
public void setMinPuppiesCount(Integer minPuppiesCount) {
this.minPuppiesCount = minPuppiesCount;
}
public LocalDate getDateOfBirthFrom() {
return dateOfBirthFrom;
}
public void setDateOfBirthFrom(LocalDate dateOfBirthFrom) {
this.dateOfBirthFrom = dateOfBirthFrom;
}
public LocalDate getDateOfBirthTo() {
return dateOfBirthTo;
}
public void setDateOfBirthTo(LocalDate dateOfBirthTo) {
this.dateOfBirthTo = dateOfBirthTo;
}
public OffsetDateTime getDateOfCreationFrom() {
return dateOfCreationFrom;
}
public void setDateOfCreationFrom(OffsetDateTime dateOfCreationFrom) {
this.dateOfCreationFrom = dateOfCreationFrom;
}
public OffsetDateTime getDateOfCreationTo() {
return dateOfCreationTo;
}
public void setDateOfCreationTo(OffsetDateTime dateOfCreationTo) {
this.dateOfCreationTo = dateOfCreationTo;
}
public OffsetDateTime getDateOfModificationFrom() {
return dateOfModificationFrom;
}
public void setDateOfModificationFrom(OffsetDateTime dateOfModificationFrom) {
this.dateOfModificationFrom = dateOfModificationFrom;
}
public OffsetDateTime getDateOfModificationTo() {
return dateOfModificationTo;
}
public void setDateOfModificationTo(OffsetDateTime dateOfModificationTo) {
this.dateOfModificationTo = dateOfModificationTo;
}
public StringBuilder getOrderBy() {
return orderBy;
}
public void setOrderBy(StringBuilder orderBy) {
this.orderBy = orderBy;
}
public boolean isDescDirection() {
return descDirection;
}
public void setDescDirection(boolean descDirection) {
this.descDirection = descDirection;
}
public void setQuery(StringBuilder query) {
this.query = query;
}
@Override
public String toString() {
return "Search [tableName=" + tableName + ", id=" + id + ", nicknameOrName=" + nicknameOrName + ", breed="
+ breed + ", gender=" + gender + ", minPuppiesCount=" + minPuppiesCount + ", dateOfBirthFrom="
+ dateOfBirthFrom + ", dateOfBirthTo=" + dateOfBirthTo + ", dateOfCreationFrom="
+ dateOfCreationFrom + ", dateOfCreationTo=" + dateOfCreationTo + ", dateOfModificationFrom="
+ dateOfModificationFrom + ", dateOfModificationTo=" + dateOfModificationTo + ", orderBy=" + orderBy
+ ", descDirection=" + descDirection + ", query=" + query
+ "]";
}
}
Сервис:
package com.example.demo6.service;
import java.time.OffsetDateTime;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import com.example.demo6.entities.Dog;
import com.example.demo6.repositories.DogRepository;
import com.example.demo6.search.Search;
@Service
public class DogService extends AbstractEntityServiceImpl<Dog, DogRepository> {
private DogRepository dogRepository;
@Autowired
public DogService(DogRepository repository, DogRepository dogRepository) {
super(repository);
this.dogRepository = dogRepository;
}
public List<Dog> find(Search searchRequest) {
return searchRequest.getResults();
}
public Dog save(Dog dog){
return dogRepository.save(dog);
}
@Transactional(readOnly = false)
public Dog update(int id, Dog updatedDog){
Dog dogToUpdate = dogRepository.findById((long) id).get();
System.out.println(dogToUpdate.toString());
dogToUpdate.setNickname(updatedDog.getNickname());
dogToUpdate.setName(updatedDog.getName());
dogToUpdate.setGender(updatedDog.getGender());
dogToUpdate.setBreed(updatedDog.getBreed());
dogToUpdate.setDateOfBirth(updatedDog.getDateOfBirth());
dogToUpdate.setPuppies(updatedDog.getPuppies());
dogToUpdate.setModifiedAt(OffsetDateTime.now());
System.out.println(dogToUpdate.toString());
System.out.println(updatedDog.toString());
Dog savedDog = dogRepository.save(dogToUpdate);
System.out.println("done");
return savedDog;
}
@Transactional(readOnly = false)
public void delete(int id) {
dogRepository.deleteById((long) id);
}
public Dog findById(int id){
return dogRepository.findById((long) id).get();
}
}
Контроллер:
package com.example.demo6.controllers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PatchMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import com.example.demo6.entities.Dog;
import com.example.demo6.search.Search;
import com.example.demo6.service.DogService;
import jakarta.validation.Valid;
@Controller
@RequestMapping
public class DogController extends AbstractController<Dog, DogService> {
private DogService dogService;
@Autowired
public DogController(DogService service, DogService dogService) {
super(service);
this.dogService = dogService;
}
@GetMapping(path = {"/search", "", "/"})
public String index(Model model, @ModelAttribute("searchRequest") Search searchRequest) {
model.addAttribute("searchRequest", new Search());
model.addAttribute("dog", new Dog());
model.addAttribute("dogs", dogService.find(searchRequest));
return "index";
}
@GetMapping("/{id}")
public String edit(Model model, @PathVariable("id") int id) {
model.addAttribute("dog", dogService.findById(id));
return "editdog";
}
@PostMapping("/search")
public String search(Model model, @ModelAttribute("searchRequest") Search searchRequest) {
model.addAttribute("dogs", dogService.find(searchRequest));
return "redirect:";
}
@PostMapping
public String save(@ModelAttribute("dog")@Valid Dog dog, Model model) {
dogService.save(dog);
return "redirect:";
}
@PatchMapping("/{id}")
public String update(@ModelAttribute("dog")@Valid Dog updatedDog, @PathVariable("id") int id, Model model) {
dogService.update(id, updatedDog);
return "redirect:";
}
@DeleteMapping("/{id}")
public String delete(@PathVariable("id") int id) {
dogService.delete(id);
return "redirect:";
}
}
UPD изменил следующий код:
if (breed != null && !breed.isEmpty() && breed.toString() != "all") predicates.add(cb.equal(root.get("breed"), breed));
if (gender != null && !gender.isEmpty() && gender.toString() != "all") predicates.add(cb.equal(root.get("gender"), gender));
if (minPuppiesCount != null && minPuppiesCount > 0) predicates.add(cb.greaterThanOrEqualTo(root.get("minPuppiesCount"), minPuppiesCount));
if (dateOfBirthFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("dateOfBirthFrom"), dateOfBirthFrom));
if (dateOfBirthTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("dateOfBirthTo"), dateOfBirthTo));
if (dateOfCreationFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("dateOfCreationFrom"), dateOfCreationFrom));
if (dateOfCreationTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("dateOfCreationTo"), dateOfCreationTo));
if (dateOfModificationFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("dateOfModificationFrom"), dateOfModificationFrom));
if (dateOfModificationTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("dateOfModificationTo"), dateOfModificationTo));
if (orderBy != null) {
if (descDirection != null && descDirection) {
cq.orderBy(cb.desc(root.get(orderBy.toString())));
} else {
cq.orderBy(cb.asc(root.get(orderBy.toString())));
}
}
На:
if (breed != null && !breed.isEmpty() && breed.toString() != "all") predicates.add(cb.equal(root.get("breed"), breed));
if (gender != null && !gender.isEmpty() && gender.toString() != "all") predicates.add(cb.equal(root.get("gender"), gender));
if (minPuppiesCount != null && minPuppiesCount > 0) predicates.add(cb.greaterThanOrEqualTo(root.get("puppies"), minPuppiesCount));
if (dateOfBirthFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("dateOfBirth"), dateOfBirthFrom));
if (dateOfBirthTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("dateOfBirth"), dateOfBirthTo));
if (dateOfCreationFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("createdAt"), dateOfCreationFrom));
if (dateOfCreationTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("createdAt"), dateOfCreationTo));
if (dateOfModificationFrom != null) predicates.add(cb.greaterThanOrEqualTo(root.get("modifiedAt"), dateOfModificationFrom));
if (dateOfModificationTo != null) predicates.add(cb.lessThanOrEqualTo(root.get("modifiedAt"), dateOfModificationTo));
if (orderBy != null) {
if (descDirection) {
cq.orderBy(cb.desc(root.get(orderBy.toString())));
} else {
cq.orderBy(cb.asc(root.get(orderBy.toString())));
}
}
То есть поменял параметры методов root.get().
Источник: Stack Overflow на русском