Spring Boot составной SELECT запрос
Делаю CRUD приложение на Spring Boot с PostgreSQL и Thymeleaf. Пытаюсь при помощи конкатенации составить сложный составной SQL запрос для поиска по БД. Но в результате почему-то происходит излишняя конкатенация и некоторые участки запроса повторяются, плюс сам запрос почему-то дублируется. После одного запроса на поиск по имени или кличке в консоль помощи System.out.println() выводится это:
SELECT * FROM dogs WHERE nickname LIKE :nickname OR name LIKE :name
SELECT * FROM dogs WHERE nickname LIKE :nickname OR name LIKE :name nickname LIKE :nickname OR name LIKE :name
Не пойму почему дублируется сам запрос и почему неправильно конкатенируется (во втором варианте часть запроса повторяется и если дальше продолжать выполнять запросы, то она будет только разрастаться). То есть насколько я понял один и тот же запрос почему-то гоняется по кругу. Как результат выдается ошибка:
org.postgresql.util.PSQLException: ОШИБКА: ошибка синтаксиса (примерное положение: "nickname")
Представление:
<!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=>Все</option>
<option th:each="breedType : ${T(com.example.demo6.entities.Dog.Breed).values()}"
th:value="${breedType}" th:text="${breedType.type}"></option>
</select>
<td>Пол:<select th:field="*{searchRequest.gender}">
<option value=>Все</option>
<option th:each="genderType : ${T(com.example.demo6.entities.Dog.Gender).values()}"
th:value="${genderType}" 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.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("searchRequest", new StringBuilder());
model.addAttribute("dog", dogService.findById(id));
return "editdog";
}
@PostMapping("/search")
public String search(Model model, @ModelAttribute("searchRequest") Search searchRequest) {
dogService.find(searchRequest);
model.addAttribute("searchRequest", new Search());
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:";
}
}
Сервис:
package com.example.demo6.service;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import com.example.demo6.Demo6Application;
import com.example.demo6.entities.Dog;
import com.example.demo6.repositories.DogRepository;
import com.example.demo6.search.Search;
import com.example.demo6.jdbctemplateaddons.DogMapper;
@Service
public class DogService extends AbstractEntityServiceImpl<Dog, DogRepository> {
private DogRepository dogRepository;
private JdbcTemplate jdbcTemplate;
private NamedParameterJdbcTemplate npjt;
@Autowired
public DogService(DogRepository repository, DogRepository dogRepository
, JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate npjt) {
super(repository);
this.dogRepository = dogRepository;
this.jdbcTemplate = jdbcTemplate;
this.npjt = npjt;
}
public List<Dog> find(Search searchRequest) {
if (searchRequest == null || searchRequest.getTableName() == null) return dogRepository.findAll();
searchRequest.getQuery();
//System.out.println(searchRequest.getNamedParameters().toString());
List<Dog> dogs = npjt.queryForList(searchRequest.getQuery(), searchRequest.getNamedParameters(), Dog.class);// new DogMapper());
searchRequest.setQuery(new StringBuilder(""));;
return dogs;
}
/*
public List<Dog> find(String searchRequest, String searchBreed, String sortBy, boolean sortAsc){
Sort sort = Sort.by(sortBy).ascending();
if (!sortAsc) sort = Sort.by(sortBy).descending();
return dogRepository.findAll(sort).stream()
.filter(dog -> ((searchRequest == null) || (dog.getName().toLowerCase().contains(searchRequest.toLowerCase())
|| dog.getNickname().toLowerCase().contains(searchRequest.toLowerCase())))
&& (searchBreed == null || searchBreed.equals("all") || dog.getBreed().getType().contains(searchBreed)))
.collect(Collectors.toList());
}
*/
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(LocalDateTime.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();
}
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
}
Класс поиска:
package com.example.demo6.search;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.OffsetDateTime;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
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 Dog.Breed breed;
private Dog.Gender 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;
private MapSqlParameterSource namedParameters = new MapSqlParameterSource();
public Search(){
this.descDirection = false;
this.query = SELECT;
}
public Search(StringBuilder tableName, Integer id, StringBuilder nicknameOrName, Dog.Breed breed, Dog.Gender 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;
//this.namedParameters = new MapSqlParameterSource();
}
public String getQuery() {
String preparedQuery = this.fromTable(tableName).withId(id)
.withNicknameOrName(nicknameOrName).withBreed(breed)
.withGender(gender).withPuppiesMoreThan(minPuppiesCount)
.withDateOfBirthBetween(dateOfBirthFrom, dateOfBirthTo)
.withDateOfCreationBetween(dateOfCreationFrom, dateOfCreationTo)
.withDateOfModificationBetween(dateOfModificationFrom, dateOfModificationTo)
.orderBy(orderBy, descDirection).replaceLastAnd().replaceWhereIfQueryIsEmpty().query.toString();
System.out.println(preparedQuery);
return preparedQuery;
}
public Search fromTable(StringBuilder tableName) {
if (query.indexOf(tableName.toString()) == -1)
query.append(tableName + " WHERE ");
return this;
}
public Search withId(Integer id) {
if (id != null) {
query.append("id=:id AND ");
namedParameters.addValue("id", id);
}
return this;
}
public Search withNicknameOrName(StringBuilder nicknameOrNameLike) {
if (nicknameOrNameLike != null && !nicknameOrNameLike.isEmpty()) {
query.append("nickname LIKE :nickname OR name LIKE :name AND ");
namedParameters.addValue("nickname", "%" + nicknameOrNameLike + "%");
namedParameters.addValue("name", "%" + nicknameOrNameLike + "%");
}
return this;
}
public Search withBreed(Dog.Breed breed) {
if (breed != null && breed.toString() != "all") {
query.append("breed=:breed AND ");
namedParameters.addValue("breed", breed);
}
return this;
}
public Search withGender(Dog.Gender gender) {
if (gender != null && gender.toString() != "all") {
query.append("gender=:gender AND ");
namedParameters.addValue("gender", gender);
}
return this;
}
public Search withPuppiesMoreThan(Integer minPuppiesCount) {
if (minPuppiesCount != null && minPuppiesCount > 0) {
query.append("puppies_count > :minPuppiesCount AND ");
namedParameters.addValue("minPuppiesCount", minPuppiesCount);
}
return this;
}
public Search withDateOfBirthBetween(LocalDate dateOfBirthFrom, LocalDate dateOfBirthTo) {
if (dateOfBirthFrom != null) {
query.append("date_of_birth >= :dateOfBirthFrom AND ");
namedParameters.addValue("dateOfBirthFrom", dateOfBirthFrom);
}
if (dateOfBirthTo != null) {
query.append("date_of_birth <= :dateOfBirthTo AND ");
namedParameters.addValue("dateOfBirthTo", dateOfBirthTo);
}
return this;
}
public Search withDateOfCreationBetween(OffsetDateTime dateOfCreationFrom, OffsetDateTime dateOfCreationTo) {
if (dateOfCreationFrom != null) {
query.append("created_at >= :dateOfCreationFrom AND ");
namedParameters.addValue("dateOfCreationFrom", dateOfCreationFrom);
}
if (dateOfCreationTo != null) {
query.append("created_at <= :dateOfCreationTo AND ");
namedParameters.addValue("dateOfCreationTo", dateOfCreationTo);
}
return this;
}
public Search withDateOfModificationBetween(OffsetDateTime dateOfModificationFrom, OffsetDateTime dateOfModificationTo) {
if (dateOfModificationFrom != null) {
query.append("modified_at >= :dateOfModificationFrom AND ");
namedParameters.addValue("dateOfModificationFrom", dateOfModificationFrom);
}
if (dateOfModificationTo != null) {
query.append("modified_at <= :dateOfModificationTo AND ");
namedParameters.addValue("dateOfModificationTo", dateOfModificationTo);
}
return this;
}
public Search orderBy(StringBuilder orderBy, boolean descDirection) {
if (orderBy != null && !orderBy.isEmpty()) {
query.append("ORDER BY :orderBy ");
namedParameters.addValue("orderBy", orderBy);
}
if (descDirection) query.append("DESC"); {
//namedParameters.addValue("dateOfCreationFrom", dateOfCreationFrom);
}
return this;
}
public Search replaceLastAnd() {
if (query.lastIndexOf("AND ") != -1)
query.replace(query.lastIndexOf("AND "), query.lastIndexOf("AND ") + 5, "");
return this;
}
public Search replaceWhereIfQueryIsEmpty() {
if (query.length() - query.lastIndexOf("WHERE ") <= 7)
query.replace(query.lastIndexOf("WHERE "), query.lastIndexOf("WHERE ") + 7, "");
return this;
}
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 Dog.Breed getBreed() {
return breed;
}
public void setBreed(Dog.Breed breed) {
this.breed = breed;
}
public Dog.Gender getGender() {
return gender;
}
public void setGender(Dog.Gender 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 MapSqlParameterSource getNamedParameters() {
return namedParameters;
}
public void setNamedParameters(MapSqlParameterSource namedParameters) {
this.namedParameters = namedParameters;
}
public void setQuery(StringBuilder query) {
this.query = query;
}
}
Источник: Stack Overflow на русском