Making JPA Play Nice With a DataTable

It can be done!

No... really... it can!

Lesson learned for today... The following two statements:

Query query = em.createQuery("Select p from Chapters p");
Query query = em.createNativeQuery("Select * from chapters");

are NOT interchangeable.

I'm serious.

don't believe me? Go ahead and try it. Do a

query.getResultList();

and then feed that output to a JSF DataTable and watch what happens.

The first one will work. The second: KABOOM.

You see, the getResultList() method returns a List. Simple, right? Nah. Not really. You see, at issue here is what, exactly that List contains.

Using the EclipseLink SQL-like language in the createQuery does two things: First, it abstracts the database so that the command will run no matter what kind of backend you're using. Second, it returns a List of objects of the type specified in the query. In the first statement, the createQuery method, the word 'Chapters' is capitalized because it refers to a class not a table. It will return a List of type Chapters (in this case) and when we wire that up later to the DataTable component it will happily cast the objects as Chapters objects (As specified in the faces-config.xml) and display them.

On the other hand, running that createNativeQuery method is asking for trouble. It's straight SQL, and in this example would run fine in prettymuch any database, but not all databases are created equal and the more complex your query is, the more committed you are to the database you originally wrote it for. Know what the other problem is? Yep, by now you should have guessed it... you'll get back a List of Objects.

Good luck trying to cast those as Chapters objects.

The EL isn't going to be much help so a list of generic Objects gets passed to your poor, unsuspecting DataTable and KABOOM! You'll be Googling this: "java.lang.NumberFormatException: For input string:"

See, when this big list of generic objects gets sent to the DataTable it doesn't know how to interpret these things, the EL is relying on the Chapters bean for guidance on finding the fields but these aren't Chapters objects. They're generic lumps of data that came from a database. It appears the EL is trying to stick the index of the elements into the first field you've defined in your table. That's an int. Bad mojo.

persistence.xml and the Errors Who Love it.

Have you ever done a Google search for a problem you're having and all of the replies you find in various blogs, forums and tutorials say basically the same thing?

Yeah, me too.

That's what I did when looking for the reason why my EclipseLink JPA project wasn't behaving itself as a web app.

Search for this string sometime:

javax.persistence.PersistenceException: No Persistence provider for EntityManager named

And you'll get a fascinating myriad of responses, most of which will tell you where to put your persistence.xml file (META-INF under WEB-INF/classes in the case of a web app, by the way, although typically your IDE will do that for you.) but what if you've done that already?

Some more will tell you to make sure you include the line

org.eclipse.persistence.jpa.PersistenceProvider

in your persistence.xml file and still others will tell you to verify that you're using the same Persistence Unit name in

persistence-unit name="myJPAname"

and the line in code where you're instantiating your EntityManager

emf = Persistence.createEntityManagerFactory("myJPAname");

But what if you've done all of that?

What to do?

Ready for the silly, stupid facepalm solution?

Make sure it's in your servlet container/web server lib folder.

For example, my Tomcat 6.0 has a folder in:

/home/mylogin/Portals/training_tomcat/apache-tomcat-6.0.20/lib

Yeah. Make sure your eclipselink and persistence jar files are in there too.

Eclipse Generates it, you fix it.

Lesson learned...

So as I continue my mad scientist like exploration of JPA, specifically EclipseLink, I have learned that while the Eclipse API offers a great deal of automation and management, you still have to watch it closely unless you want to spend time ripping your hair out.

Take the example I ran across this morning.

OS: Linux Ubuntu 9.10
Database: MySQL 5.1
API: Eclipse Ganymede
JPA Provider: EclipseLink

This was a simple console app designed to play around with columns while learning the EclipseLink SQL-ish query language. I created a table called "chapters" in my database designed to hold some simple data related to a wargame. (You Warhammer 40,000 fans will like this.) Columns include chapter_name, parent_legion, primarch... obviously all VARCHAR type columns.

One of my columns is called loyalist. It's a BIT column. Hold that in your mind for now...

Now, Eclipse has a nice little feature that allows you to automatically generate entities from your database. Very cool. Run the utility and watch that entity class grow. Nifty.

2 problems:

The first problem is that EclipseLink is going to assume your table name is in ALL CAPS. This is easy to get confused on because the select statement in a case like this reads:

Query query = em.createQuery("Select p from Chapters p");

That syntax is correct because "Chapters" here refers to the entity Chapters, not the table. That means that you're not directly controlling the name of the table the actual SELECT command will be run against. So if your table is named in all lowercase, like mine, running this code will blow up your app.

The solution: Pretty simple, actually. You just have to use an annotation to override the table name. Just add something like this under your @Entity annotation:

@Table(name="chapters")

Good to go. Now EclipseLink will run a SELECT command against table name "chapters" and not "CHAPTERS."

The second problem is a little easier to solve and yet I find it more annoying. When Eclipse generated the entity and tripped across that BIT column, it created a matching member in my class and made it type byte.

KABOOM.

So I manually changed it to boolean in my entity code and now it's happy as a clam.