Spring数据 JPA - JDBC 类型的无方言映射：2003 年

Question

我得到以下错误

JDBC 类型的无方言映射：2003 年;嵌套的异常是组织Hibernate映射异常：JDBC 类型没有方言映射：2

回购代码如下

    String chrPackageId = "select\n" +
         "\tcombination_hr_id as \"chRuleId\",\n" +
         "\tcombination_hr_name as \"chRuleName\", \n" +
        "\tholdingrule_list as \"selectedRules\"\n" +
         "from\n" +
         "\tcombination_holding_rule chr\n" +
         "where\n" +
         "\tpackage_id =:packageId";

@Query(value=chrPackageId,nativeQuery = true)
List<CHRfromPackageIdDTO> repoCHRFromPackageId(int packageId);

DTO 对象如下所示

public interface CHRfromPackageIdDTO {

    int getChRuleId();
    String getChRuleName();
    Integer[] getSelectedRules();
}

我们使用 Postgres DB，实际上在获取整数 [] 值时存在一些问题。

Stackoverflow中的其他答案是特定于Hibernate的。但我们使用spring数据jpa。

Answer 1

实体类

import com.vladmihalcea.hibernate.type.array.IntArrayType;
import com.vladmihalcea.hibernate.type.array.StringArrayType;
import com.vladmihalcea.hibernate.type.json.JsonBinaryType;
import com.vladmihalcea.hibernate.type.json.JsonStringType;
import lombok.*;
import org.hibernate.annotations.Type;
import org.hibernate.annotations.TypeDef;
import org.hibernate.annotations.TypeDefs;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@TypeDefs({
        @TypeDef(
                name = "string-array",
                typeClass = StringArrayType.class
        ),
        @TypeDef(
                name = "int-array",
                typeClass = IntArrayType.class
        ),
        @TypeDef(name = "json", typeClass = JsonStringType.class),
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
@Entity
@Table(name="combination_holding_rule")
public class CombHoldingRule {

    @Id
    @Column(name="combination_hr_id")//Checked
    private Integer combHoldingRuleId;
    @Column(name="combination_hr_name")//Checked
    private String combHoldingRuleName;
    @Column(name="jurisdiction_id")//Checked
    private Integer  jurisdictionId;
    @Column(name="function_group_id")//Checked
    private Integer  functionGroupId;
    @Column(name="overall_netting_type")//Checked
    private String overallNettingType;
    @Column(name="package_id")//Checked
    private Integer  packageId;

    @Type(type = "int-array")
    @Column(
            name = "holdingrule_list",
            columnDefinition = "integer[]"
    )
    private int[] holdingRuleList;

}

在存储库中

@Query(value="from CombHoldingRule where packageId=:packageId")
    List<CombHoldingRule> repoCHRFromPackageId(@Param("packageId") int packageId);

我将JPAQuery的结果放入实体中，然后在服务层中执行以下操作

public List<CHRfromPackageIdDTO> getCHRFromPackageIdService(int packageId) {
       
        List<CombHoldingRule> combHoldingRuleList = combinationHRrepo.
                                           repoCHRFromPackageId(packageId);
        List<CHRfromPackageIdDTO> combDTO = new ArrayList<>();
        for ( CombHoldingRule combHoldingRule : combHoldingRuleList) {            
            CHRfromPackageIdDTO temp = new CHRfromPackageIdDTO(combHoldingRule.getCombHoldingRuleId(),
                    combHoldingRule.getCombHoldingRuleName(),
                    combHoldingRule.getHoldingRuleList());
            combDTO.add(temp);
        }       
        return combDTO;
    }

也请检查这里

注意:我认为，这是一种工作，但我不确定，如何直接将值从本地查询中提取到自定义Pojo中，而不是实体类中。我真的很感激，如果有人张贴的答案。我会接受这个答案。