0

I'm trying to use entities with a MySQL ndbcluster table. This table type doesn't allow foreign keys, but up until now it hasn't been a problem with my entities.

However, I have run into a bit of a problem, when I try to load an entity using the EntityManager's createNativeQuery method. I need to use this method due to my inability to do this: How to make a CriteriaBuilder join with a custom "on" condition?

My MySQL table looks like this:

CREATE TABLE `category` (
    `id` SMALLINT(6) NOT NULL AUTO_INCREMENT,
    `category_id` SMALLINT(6) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `category_id` (`category_id`)
)
COLLATE='utf8_general_ci'
ENGINE=ndbcluster
ROW_FORMAT=DEFAULT

If I change the table engine to innodb, and add foreign keys, the createNativeQuery method works fine.

My entity class looks like this:

@Entity
@Table(name = "category")
public class Category implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "id")
    private Short id;
    @OneToMany(mappedBy = "categoryId")
    private List<Category> categoryList;
    @JoinColumn(name = "category_id", referencedColumnName = "id")
    @ManyToOne
    private Category categoryId;

    public Category() {
    }

    // getters and setters
}

Even without foreign keys, this entity works fine when I use the CriteriaBuilder for a query, but unfortunately not everything is possible with the CriteriaBuilder.

I get an error when I call getResultList on a Query object created with createNativeQuery. I don't know if this is a bug, or if something should be added to my entity class to make this work.

The error says:

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(
 => 2519
 => 2463
 => Tools)] during the execution of the query was detected to be null.  Primary keys must not contain null.
Query: ReadAllQuery(referenceClass=Category sql="select * from `category`")
at org.eclipse.persistence.exceptions.QueryException.nullPrimaryKeyInBuildingObject(QueryException.java:895)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:584)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:560)
at org.eclipse.persistence.queries.ObjectLevelReadQuery.buildObject(ObjectLevelReadQuery.java:717)
at org.eclipse.persistence.queries.ReadAllQuery.registerResultInUnitOfWork(ReadAllQuery.java:769)
...

My table contains 1 row, where id=1 and category_id=null, so there are no primary keys with a null-value, despite what the error says. If I remove that row or set category_id=1, I don't get an error.

Need help, please.

Community
  • 1
  • 1
Bjørn Stenfeldt
  • 1,432
  • 1
  • 18
  • 25
  • Seems I made a mistake in one of my tests. Using a relational table or not makes no difference. Both ndbcluster and innodb with a set foreign key throw the same exception. Thus the problem seems to be an issue with use of the createNativeQuery method. – Bjørn Stenfeldt Apr 13 '13 at 20:21

2 Answers2

1

Managed to make it work by switching from EclipseLink (JPA 2.0) to OpenJPA (JPA 2.0). Seems like there is a bug somewhere in EclipseLink 2.3.2 and/or GlassFish 3.1.2.2.

I've used EclipseLink (JPA 2.0) in another project of mine, using a slightly different version Netbeans + GlassFish 3.1.1, where I used createNativeQuery on an entity class for a non-relational myisam table. This never caused any problem. It really must be a bug.

But problem solved. Bye, bye EclipseLink, hello OpenJPA.

Bjørn Stenfeldt
  • 1,432
  • 1
  • 18
  • 25
0

The issue is case sensitivity. In MySQL your column "id" will be defined in the database as "ID" unless you quote it. If you switch your mappings to upper case it should fix the issue (i.e. "ID").

You could also quote the column name ("'id'")

or set the persistence unit property,

"eclipselink.jpa.uppercase-column-names"="true"

James
  • 17,965
  • 11
  • 91
  • 146