Database first ORM for Play Framework (Java) with auto DB model generating

Question

Several years I develop at work in C#, MVC, Entity Framework, database first. Now I want to try Java and choose Play Framework and IDEA as IDE. Now I search such ORM system as:

• easy integrating in Play Framework;
• have class generation from database (reverse engineering) as main tool;
• have easy language like LINQ in C# (ex: from x in context.MY_TABLE select x)

Answer 1

Play has build-in ORM - it's Ebean, all you need to use it just uncomment several lines in application.conf (and optionally choose database engine other then build-in H2 ie. MySQL like described in this question)

Next create models package in app folder and start to add your models.

More details in official docs.

Unfortunately it doesn't support reverse engineering...

Answer 2

I'm not aware if IDEA supports DB -> JPA entities reverse-engineering, but Eclipse Dali does this fine - I have used this approach on several projects and were happy with it.

http://www.eclipse.org/webtools/dali/

Play2 works ok with full JPA if wished. Ebean uses only JPA annotations. I like EBean since even if I'm not completely convinced about JPA's criteria API (I have went it thru on JPA 2.0, so don't know how much easier it has come on JPA 2.1) & usefulness of EntityMananager (all hassle with connected / disconnedted entities) I'm big fan of JPA annotations.

For anyone interested in JPA I recommend this book

http://www.amazon.com/Pro-JPA-2-Mike-Keith/dp/1430249269/

BTW: JPA's named queries / JPQL might be ok for sql like simple query needs. Ebean doesn't support JPQL, so if one wants to use it then full JPA implementation like Hibernate or EclipseLink is needed.

Answer 3

I strongly advice using jOOQ:

• Support to generate model classes from console by only one command: java -classpath jooq-3.1.0.jar;jooq-meta-3.1.0.jar;jooq-codegen-3.1.0.jar;postgresql-9.2-1003.jdbc4.jar;. org.jooq.util.GenerationTool /jooq_config.xml
• Full control of your SQL queries.
• Easy SQL debugging. Very easy: see here.
• Flexible and powerfull API. Full documentation.
• Typefase.
• Ideal choose for SQL indepth programming.
• Supports Java and Scala.
• Out-of-box support for advanced SQL types without problems.
• Build-in exporting to xml, html, excel
• Build-in support to batch inserting.
• Good support.
• Opensource
• Many database engines supported.

Personal feelings

I always loved SQL and I really had chance to work with many ORM with many technologies (.NET: NHibernate, Entity Framework, Linq. Java: Hibernate, JPA. Scala: Anorm SQL) and there were no good solution for me. I used model first and database first. Everytime I used raw SQL and store procedures in most critical points of applications. ORM generate a lot of rubbish which is very difficult to profile and optimize.

When I found jOOQ I was very skeptical. After about 6-8 months working with it I knew that was it. This tool allow you to write every query similar to raw SQL and it's very productive tool. Next thing is that this tool is really fast growing.

Answer 4

Jooq doesn't support nested objects. The idea is good, but not very useful.