时间戳

首页 > 解决方案 > NamedNativeQuery JPA 空主键异常

postgresql - NamedNativeQuery JPA 空主键异常

问题描述

我使用结果集映射创建了一个如下所示的命名查询：

@NamedNativeQueries({ @NamedNativeQuery(name = "Q_INSTRUMENTS", query = "SELECT i.ID, i.TICKER, i.ISIN, i.SEDOL, i.NAME, i.COUNTRY_ID, i.CONTRACT_SIZE, i.EXPIRY_DATE, i.TYPE_ID FROM INSTRUMENT i INNER JOIN COUNTRY c ON i.COUNTRY_ID = c.ID"
        + " GROUP BY i.ID, i.TICKER, i.ISIN, i.SEDOL, i.COUNTRY_ID, i.CONTRACT_SIZE, i.EXPIRY_DATE, i.TYPE_ID, c.NAME HAVING i.ID = MAX(i.ID) ORDER BY i.NAME, c.NAME ASC", resultClass = Instrument.class, resultSetMapping = "InstrumentMapping") })
@SqlResultSetMapping(name = "InstrumentMapping", entities = { @EntityResult(entityClass = Instrument.class, fields = {
        @FieldResult(name = "id", column = "ID"), @FieldResult(name = "ticker", column = "TICKER"),
        @FieldResult(name = "sedol", column = "SEDOL"), @FieldResult(name = "isin", column = "ISIN"),
        @FieldResult(name = "name", column = "NAME"), @FieldResult(name = "countryId", column = "COUNTRY_ID"),
        @FieldResult(name = "contractSize", column = "CONTRACT_SIZE"),
        @FieldResult(name = "expiryDate", column = "EXPIRY_DATE"), @FieldResult(name = "typeId", column = "TYPE_ID") }) })

这是使用此命名查询注释的类

public class Instrument extends ManagedEntityBase{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(length = 10)
    private String ticker;

    @Column(length = 30)
    private String isin;

    @Column(length = 10, unique = true, nullable = false)
    private String sedol;

    @Column(length = 60, nullable = false)
    private String name;

    @Column(name = "COUNTRY_ID", nullable = false, insertable = false, updatable = false)
    private long countryId;

    @Column(name = "TYPE_ID", nullable = false, insertable = false, updatable = false)
    private byte typeId;

    @Column(name = "CONTRACT_SIZE", nullable = false)
    private Long contractSize;

    @Transient
    private String contractSizeString;

    @Temporal(TemporalType.DATE)
    @Column(name = "EXPIRY_DATE")
    private Date expiryDate;

    public Instrument() {
        // Default constructor
        this.contractSize = 1L;
    }

    ublic Instrument(String sedol, Country country, InstrumentType type) {
    this();
    this.sedol = sedol;
    this.country = country;
    this.type = type;
}

/**
 * Creates an {@link Instrument} with the given sedol, isin, country and type.
 * 
 * @param sedol instrument sedol
 * @param isin instrument isin
 * @param country instrument country
 * @param name
 * @param ticker
 * @param type instrument type
 */
public Instrument(String sedol, String isin, Country country, String name, String ticker, InstrumentType type) {
    this(sedol, country, type);
    this.isin = isin;
    this.name = name;
    this.ticker = ticker;
}

}

当我尝试调用名称查询时：

public static List<Instrument> getInstruments() {
        return DatabaseUtility.getEntityManager().createNamedQuery("Q_INSTRUMENTS").getResultList();
    }

我收到以下错误：

[EL Warning]: 2020-01-31 15:55:40.732--UnitOfWork(1788318093)--Exception [EclipseLink-6044] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.QueryException
Exception Description: The primary key read from the row [ArrayRecord(
     => 3000000002285
     => 00577
     => BMG8827A1045
     => BYX9N24
     => 13 HOLDINGS LTD, THE
     => 2
     => 1
     => null
     => 1)] during the execution of the query was detected to be null.  Primary keys must not contain null.
Query: ReadAllQuery(name="Q_INSTRUMENTS" referenceClass=Instrument sql="SELECT i.ID, i.TICKER, i.ISIN, i.SEDOL, i.NAME, i.COUNTRY_ID, i.CONTRACT_SIZE, i.EXPIRY_DATE, i.TYPE_ID FROM INSTRUMENT i INNER JOIN COUNTRY c ON i.COUNTRY_ID = c.ID GROUP BY i.ID, i.TICKER, i.ISIN, i.SEDOL, i.COUNTRY_ID, i.CONTRACT_SIZE, i.EXPIRY_DATE, i.TYPE_ID, c.NAME HAVING i.ID = MAX(i.ID) ORDER BY i.NAME, c.NAME ASC")

在表中，expiry_date 确实为空，但这不参与主键，默认情况下，到期日期的可为空值是 true。我将 EclipseLink 与 PostreSQL 一起使用。我尝试将当前代码从旧 Sybase 迁移到新 PostreSQL。使用 Sybase 数据源不会发生错误。

我尝试使用

<property name="eclipselink.jpa.uppercase-column-names" value="true"/>

确保到列的映射没有出错，但它没有解决我的问题

标签： postgresqljpaeclipselinksybase

解决方案

看来我的问题已经有了答案。问题是由于 id 映射错误。映射是使用“id”完成的，但数据库中的列是 ID（大写）。这就是为什么我的 id 为 null 的原因。

解决方案是把

<property name="eclipselink.jpa.uppercase-column-names" value="true"/>

在正确的持久性单元中。

推荐阅读