org.eclipse.persistence.exceptions.QueryException: The primary key read from the row during the execution of the query was detected to be null

Question

I'm trying to query some data on JPA, this is my query:

@Override
    public List<HorarioAtencion> findByPeriodo(Person person, int dia, int mes, int anno) {
        Query busqueda = em.createNativeQuery("Select * from HorariosAtencion where " +
                                              "idPerson = ?1 AND "+
                                              "DAY( fechaInicio) <= ?2 AND MONTH( fechaInicio ) <= ?3 AND YEAR( fechaInicio) <= ?4 AND "+
                                              "DAY( fechaFin) >= ?2 AND MONTH( fechaFin ) >= ?3 AND YEAR( fechaFin ) >= ?4 ORDER BY TIME(fechaInicio)", HorarioAtencion.class);
       busqueda.setParameter(1,person.getId());
       busqueda.setParameter(2, dia);
       busqueda.setParameter(3, mes);
       busqueda.setParameter(4, anno);
       return busqueda.getResultList();
    }

This query returns results, but the next exception is fired:

Caused by: 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(
     => 1
     => 13
     => 2013-01-04 12:25:00.0
     => 2013-01-04 12:25:00.0
     => true
     => true
     => true
     => true
     => true
     => true
     => true)] during the execution of the query was detected to be null.  Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=HorarioAtencion sql="Select * from HorariosAtencion where idPerson = ? AND DAY( fechaInicio) <= ? AND MONTH( fechaInicio ) <= ? AND YEAR( fechaInicio) <= ? AND DAY( fechaFin) >= ? AND MONTH( fechaFin ) >= ? AND YEAR( fechaFin ) >= ? ORDER BY TIME(fechaInicio)")

How is this caused and how can I solve it?

Columns definition on entity class:

private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Integer id;

    @Basic(optional = false)
    @Column(name = "fechaInicio")
    @Temporal(TemporalType.TIMESTAMP)
    private Date fechaInicio;

    @Basic(optional = false)
    @Column(name = "fechaFin")
    @Temporal(TemporalType.TIMESTAMP)
    private Date fechaFin;

    @Basic(optional = false)
    @Column(name = "domingo")
    private boolean domingo;

    @Basic(optional = false)
    @Column(name = "lunes")
    private boolean lunes;

    @Basic(optional = false)
    @Column(name = "martes")
    private boolean martes;

    @Basic(optional = false)
    @Column(name = "miercoles")
    private boolean miercoles;

    @Basic(optional = false)
    @Column(name = "jueves")
    private boolean jueves;

    @Basic(optional = false)
    @Column(name = "viernes")
    private boolean viernes;

    @Basic(optional = false)
    @Column(name = "sabado")
    private boolean sabado;

    @JoinColumn(name = "idPersona", referencedColumnName = "id")
    @ManyToOne(optional = false)
    private Persona idPersona;

Answer 1

What database are you using, and what does it return for the column names?

EclipseLink is case sensitive in less recent versions by default unless the eclipselink.jpa.uppercase-column-names persistent property is set to true.

This might be the problem for native queries if the database returns the column name as uppercase "ID" when you have it defined as lowercase "id".

Try changing the column definitions in the annotations to match what your database uses or adding the property with a value of true.

Answer 2

The problem is that eclipselink is case sensitive. There is a "hint" available that will address this. Place the following between your properties element in the persistence.xml file:

<!-- Hint to solve "The primary key read from the row [DatabaseRecord(...)] during the execution of the query was detected to be null." errors.-->
<property name="eclipselink.jpa.uppercase-column-names" value="true"/>

That will treate the column names in a case insensitive manner and eliminate the error.