Home > Net >  How to implement optional parameter of list type in native query using Spring Data JPA
How to implement optional parameter of list type in native query using Spring Data JPA

Time：02-26

In my use case I need to pass a parameter of type List<>, but this parameter needs to be optional and I'm having trouble implementing it, can you help me?

Endpoint:

http://localhost:8080/api/v1/census-population/find?state=1,2

it's work: required parameter

@Query(value = "SELECT s.id, s.code, s.name, s.uf, "  
            "SUM(cp.population) AS statePopulation, "  
            "COUNT(cp.id) AS countCities "  
            "FROM census_population cp "  
            "INNER JOIN cities c ON c.id = cp.city_id "  
            "INNER JOIN states s ON s.id = c.state_id "  
            "WHERE s.id IN (:state) "  
            "GROUP BY s.code, s.name, s.uf"
            , nativeQuery = true)
    List<CensusStateStats> censusByStateList(@Param("state") List<Long> state);

Result:

[
    {
        "id": 1,
        "code": 11,
        "name": "Rondônia",
        "statePopulation": 1815278,
        "countCities": 52
    },
    {
        "id": 2,
        "code": 12,
        "name": "Acre",
        "statePopulation": 906876,
        "countCities": 22
    }
]

it's doesn't work: optional parameter

@Query(value = "SELECT s.id, s.code, s.name, s.uf, "  
            "SUM(cp.population) AS statePopulation, "  
            "COUNT(cp.id) AS countCities "  
            "FROM census_population cp "  
            "INNER JOIN cities c ON c.id = cp.city_id "  
            "INNER JOIN states s ON s.id = c.state_id "  
            "WHERE (:state IS NULL OR s.id IN (:state)) "  
            "GROUP BY s.code, s.name, s.uf"
            , nativeQuery = true)
    List<CensusStateStats> censusByStateList(@Param("state") List<Long> state);

Result:

{
    "timestamp": "2022-02-24T19:49:28.895 00:00",
    "status": 500,
    "error": "Internal Server Error",
    "trace": "org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:280)\r\n\tat org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)\r\n\tat org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:551)\r\n\tat org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)\r\n\tat org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)\r\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:152)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:145)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:215)\r\n\tat com.sun.proxy.$Proxy109.censusByStateList(Unknown Source)\r\n\tat com.example.demo.service.CensusPopulationService.censusByStateList(CensusPopulationService.java:27)\r\n\tat com.example.demo.controller.CensusPopulationController.censusByStateList(CensusPopulationController.java:38)\r\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)\r\n\tat java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)\r\n\tat java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\r\n\tat java.base/java.lang.reflect.Method.invoke(Method.java:566)\r\n\tat org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)\r\n\tat org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895)\r\n\tat org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808)\r\n\tat org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)\r\n\tat org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067)\r\n\tat org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963)\r\n\tat org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)\r\n\tat org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:655)\r\n\tat org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)\r\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:764)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)\r\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)\r\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)\r\n\tat org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189)\r\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162)\r\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197)\r\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97)\r\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540)\r\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135)\r\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)\r\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)\r\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)\r\n\tat org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)\r\n\tat org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)\r\n\tat org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)\r\n\tat org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1722)\r\n\tat org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)\r\n\tat org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)\r\n\tat org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)\r\n\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)\r\n\tat java.base/java.lang.Thread.run(Thread.java:829)\r\nCaused by: org.hibernate.exception.DataException: could not extract ResultSet\r\n\tat org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:52)\r\n\tat org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)\r\n\tat org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)\r\n\tat org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)\r\n\tat org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)\r\n\tat org.hibernate.loader.Loader.getResultSet(Loader.java:2322)\r\n\tat org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075)\r\n\tat org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)\r\n\tat org.hibernate.loader.Loader.doQuery(Loader.java:956)\r\n\tat org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)\r\n\tat org.hibernate.loader.Loader.doList(Loader.java:2868)\r\n\tat org.hibernate.loader.Loader.doList(Loader.java:2850)\r\n\tat org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)\r\n\tat org.hibernate.loader.Loader.list(Loader.java:2677)\r\n\tat org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)\r\n\tat org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2195)\r\n\tat org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1190)\r\n\tat org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177)\r\n\tat org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)\r\n\tat org.hibernate.query.Query.getResultList(Query.java:165)\r\n\tat org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:128)\r\n\tat org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)\r\n\tat org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:155)\r\n\tat org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:143)\r\n\tat org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:137)\r\n\tat org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:121)\r\n\tat org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:159)\r\n\tat org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:138)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)\r\n\tat org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)\r\n\tat org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)\r\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)\r\n\tat org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)\r\n\t... 59 more\r\nCaused by: org.h2.jdbc.JdbcSQLDataException: Data conversion error converting \"ROW (1, TRUE)\"; SQL statement:\nSELECT s.id, s.code, s.name, s.uf, SUM(cp.population) AS statePopulation, COUNT(cp.id) AS countCities, FROM census_population cp INNER JOIN cities c ON c.id = cp.city_id INNER JOIN states s ON s.id = c.state_id WHERE (?, ? IS NULL OR s.id IN (?, ?)) GROUP BY s.code, s.name, s.uf [22018-200]\r\n\tat org.h2.message.DbException.getJdbcSQLException(DbException.java:457)\r\n\tat org.h2.message.DbException.getJdbcSQLException(DbException.java:429)\r\n\tat org.h2.message.DbException.get(DbException.java:194)\r\n\tat org.h2.value.Value.convertTo(Value.java:875)\r\n\tat org.h2.value.Value.convertTo(Value.java:737)\r\n\tat org.h2.value.Value.getBoolean(Value.java:561)\r\n\tat org.h2.expression.condition.ConditionAndOr.getValue(ConditionAndOr.java:101)\r\n\tat org.h2.expression.Expression.getBooleanValue(Expression.java:258)\r\n\tat org.h2.command.dml.Select.isConditionMet(Select.java:448)\r\n\tat org.h2.command.dml.Select.gatherGroup(Select.java:522)\r\n\tat org.h2.command.dml.Select.queryGroup(Select.java:487)\r\n\tat org.h2.command.dml.Select.queryWithoutCache(Select.java:839)\r\n\tat org.h2.command.dml.Query.queryWithoutCacheLazyCheck(Query.java:201)\r\n\tat org.h2.command.dml.Query.query(Query.java:489)\r\n\tat org.h2.command.dml.Query.query(Query.java:451)\r\n\tat org.h2.command.CommandContainer.query(CommandContainer.java:285)\r\n\tat org.h2.command.Command.executeQuery(Command.java:195)\r\n\tat org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:114)\r\n\tat com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\r\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\r\n\tat org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)\r\n\t... 90 more\r\nCaused by: java.lang.NumberFormatException: Character R is neither a decimal digit number, decimal point, nor \"e\" notation exponential mark.\r\n\tat java.base/java.math.BigDecimal.<init>(BigDecimal.java:518)\r\n\tat java.base/java.math.BigDecimal.<init>(BigDecimal.java:401)\r\n\tat java.base/java.math.BigDecimal.<init>(BigDecimal.java:834)\r\n\tat org.h2.value.Value.convertToBoolean(Value.java:908)\r\n\tat org.h2.value.Value.convertTo(Value.java:800)\r\n\t... 107 more\r\n",
    "message": "could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet",
    "path": "/api/v1/census-population/find"
}

My classes:

MODELS

State

package com.example.demo.model;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "states")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class State {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Integer code;
    private String name;

    @Column(length = 2)
    private String uf;

}

City

package com.example.demo.model;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "cities")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class City {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "state_id", nullable = false, foreignKey = @ForeignKey(name = "fk_cities_states1"))
    private State state;
    private String  code;
    private String name;

}

Cencus

package com.example.demo.model;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "census")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Census {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

}

CensusPopulation

package com.example.demo.model;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

@Entity
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "census")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Census {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

}

DTO

CensusStateStats

package com.example.demo.dto;

public interface CensusStateStats {
    Long getId();
    Integer getCode();
    String getName();
    Long getStatePopulation();
    Long getCountCities();
}

REPOSITORY

CensusPopulationRep

package com.example.demo.repository;

import com.example.demo.dto.CensusStateCitiesStats;
import com.example.demo.dto.CensusStateStats;
import com.example.demo.model.CensusPopulation;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface CensusPopulationRep extends JpaRepository<CensusPopulation, Long> {

    @Query(value = "SELECT s.id, s.code, s.name, s.uf, "  
            "SUM(cp.population) AS statePopulation, "  
            "COUNT(cp.id) AS countCities "  
            "FROM census_population cp "  
            "INNER JOIN cities c ON c.id = cp.city_id "  
            "INNER JOIN states s ON s.id = c.state_id "  
            "WHERE (:state IS NULL OR s.id IN (:state)) "  
            "GROUP BY s.code, s.name, s.uf"
            , nativeQuery = true)
    List<CensusStateStats> censusByStateList(@Param("state") List<Long> state);

}

SERVICE

CensusPopulationService

package com.example.demo.service;

import com.example.demo.dto.CensusStateCitiesStats;
import com.example.demo.dto.CensusStateStats;
import com.example.demo.model.CensusPopulation;
import com.example.demo.repository.CensusPopulationRep;
import lombok.RequiredArgsConstructor;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@RequiredArgsConstructor
public class CensusPopulationService {

    private final CensusPopulationRep censusPopulationRep;    

    public List<CensusStateStats> censusByStateList(List<Long> state) {
        return censusPopulationRep.censusByStateList(state);
    }

}

CONTROLLER

CensusPopulationController

package com.example.demo.controller;

import com.example.demo.dto.*;
import com.example.demo.model.CensusPopulation;
import com.example.demo.service.CensusPopulationService;
import lombok.RequiredArgsConstructor;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@RestController
@RequestMapping("api/v1/census-population")
@RequiredArgsConstructor
public class CensusPopulationController {

    private final CensusPopulationService censusPopulationService;

  

    @GetMapping("/find")
    public ResponseEntity<List<CensusStateStatsDto>> censusByStateList(@RequestParam(required = false) List<Long> state) {
        return ResponseEntity.ok(censusPopulationService.censusByStateList(state).stream()
                .map(CensusStateStatsDto::new)
                .collect(Collectors.toList()));
    }

}

CodePudding user response：

Assuming the values ​​(1,2) are going in your (:state), it would look like this: WHERE (1,2 IS NULL OR s.id IN(1,2))

Please review this code which should resolve your issue.

Ex: WHERE ((:state) IS NULL OR s.id IN (:state))

Page link：https//www.codepudding.com/net/315362.html

Prev:Is it possible to open a PDF file from a database-fetched Uri from Android API 28?
Next:How can I filter a search by a foreign key?
  • Related

About Us:  Contact Us      Terms of Service       Privacy Policy

Copyright © 2010-2023，Powered By CodePudding