Jpa命名本机查询无结果怎么办呢？

问题描述

我正在尝试将Java Spring Cloud微服务连接到oracle数据库。我的问题是，当我调用从函数传递参数的本机查询时，此查询不会返回任何内容，而如果我将其参数直接插入查询中，则一切正常。

ENTITY

@Entity
@Table(name = "######")
@SqlResultSetMapping(
        name    = "EmployeeJobDTO",
        classes = @ConstructorResult(
                targetClass = EmployeeJobDTO.class,
                columns     = {
                        @ColumnResult(name = "idStf",           type = String.class),
                        @ColumnResult(name = "personalNumber",  type = String.class),
                        @ColumnResult(name = "name",            type = String.class),
                        @ColumnResult(name = "surname",         type = String.class),
                        @ColumnResult(name = "functionMain",    type = String.class),
                        @ColumnResult(name = "idDrr",           type = String.class),
                        @ColumnResult(name = "functionShift",   type = String.class),
                        @ColumnResult(name = "shiftOldCode",    type = String.class),
                        @ColumnResult(name = "shiftCode",       type = String.class),
                        @ColumnResult(name = "idJob",           type = String.class),
                        @ColumnResult(name = "jobType",         type = String.class),
                        @ColumnResult(name = "jobStatus",       type = String.class),
                        @ColumnResult(name = "plannedFrom",     type = String.class),
                        @ColumnResult(name = "plannedTo",       type = String.class),
                        @ColumnResult(name = "actualFrom",      type = String.class),
                        @ColumnResult(name = "actualTo",        type = String.class)
                }) 
        )
@NamedNativeQueries({
    @NamedNativeQuery(
            name                = "findDayJobsByDateAndDepartment",
            resultClass         = EmployeeJobDTO.class,
            resultSetMapping    = "EmployeeJobDTO",
            query   = "select to_char(st.XXXX) as idStf," + 
                    "st.XXXX as personalNumber," + 
                    "st.XXXX as name," + 
                    "st.XXXX as surname," + 
                    "sp.XXXX as functionMain," + 
                    "dr.XXXX as idDrr," + 
                    "dr.XXXX as functionShift," + 
                    "dr.XXXX as shiftOldCode," + 
                    "dr.XXXX as shiftCode," + 
                    "jb.XXXX as idJob," + 
                    "jb.XXXX as jobType," + 
                    "substr(jb.XXXX, 0, 1) as jobStatus," + 
                    "jb.XXXX as plannedFrom," + 
                    "jb.XXXX as plannedTo," + 
                    "jb.XXXX as actualFrom," + 
                    "jb.XXXX as actualTo " + 
                    "from XXXXXX jb " + 
                    "inner join XXXXXX dr on jb.XXXX = dr.XXXX " + 
                    "inner join XXXXXX st on jb.XXXX = st.XXXX " + 
                    "inner join XXXXXX sr on sr.XXXX = jb.XXXX " + 
                    "inner join XXXXXX sp on sp.XXXX = jb.XXXX " +
                    "where dr.XXXX=?1 and dr.XXXX = 'A' and sr.XXXX=?2"
            ),
    @NamedNativeQuery(
            name                = "findDayJobsByDateAndDepartmentFixedParam",
            resultClass         = EmployeeJobDTO.class,
            resultSetMapping    = "EmployeeJobDTO",
            query   = "select to_char(st.XXXX) as idStf," + 
                    "st.XXXX as personalNumber," + 
                    "st.XXXX as name," + 
                    "st.XXXX as surname," + 
                    "sp.XXXX as functionMain," + 
                    "dr.XXXX as idDrr," + 
                    "dr.XXXX as functionShift," + 
                    "dr.XXXX as shiftOldCode," + 
                    "dr.XXXX as shiftCode," + 
                    "jb.XXXX as idJob," + 
                    "jb.XXXX as jobType," + 
                    "substr(jb.XXXX, 0, 1) as jobStatus," + 
                    "jb.XXXX as plannedFrom," + 
                    "jb.XXXX as plannedTo," + 
                    "jb.XXXX as actualFrom," + 
                    "jb.XXXX as actualTo " + 
                    "from XXXXXX jb " + 
                    "inner join XXXXXX dr on jb.XXXX = dr.XXXX " + 
                    "inner join XXXXXX st on jb.XXXX = st.XXXX " + 
                    "inner join XXXXXX sr on sr.XXXX = jb.XXXX " + 
                    "inner join XXXXXX sp on sp.XXXX = jb.XXXX " +
                    "where dr.XXXX='20200224' and dr.XXXX = 'A' and sr.XXXX='SFA'"
            )
})
public class EmployeeJobEntity {
    //.............. follows the code .....
}

存储库

public interface EmployeeJobEntityRepository extends CrudRepository {
    // This query return [] i tryed @Param annotation and the result not change.
    @Query(name = "findDayJobsByDateAndDepartment", nativeQuery = true)
    public List findDayJobsByDateAndDepartment(String date, String department);

    // This query return the data OK
    @Query(name = "findDayJobsByDateAndDepartmentFixedParam", nativeQuery = true)
    public List findDayJobsByDateAndDepartmentFixedParam();
}

APPLICATION.YML

logging:
  level:
    org:
      hibernate:
        SQL: DEBUG
        type:
          descriptor:
            sql:
              BasicBinder: TRACE
server:
  port: 8100
spring:
  application:
    name: employee
  datasource:
    driver-class-name: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@XXXX-XXX.XX.it::
    password: XXXX
    username: XXXX
    hikari:
      connection-test-query: select 1 from dual
      minimum-idle: 1
      idle-timeout: 240000
      max-lifetime: 360000
      maximum-pool-size: 10
      pool-name: XXXX
  jpa:
    hibernate:
      ddl-auto: none
    show-sql: true
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle10gDialect

思路：

您需要使用spring的注释@Param，对于您的情况，代码如下：

@Query(name = "findDayJobsByDateAndDepartment", nativeQuery = true)
    public List findDayJobsByDateAndDepartment(@Param("date") String date, @Param("department") String department);

以上内容就是爱站技术频道小编为大家分享的Jpa命名本机查询无结果怎么办呢？看完以上分享之后，大家应该都知道怎么操作了吧。