I have been looking for a tool which allows defining JPA2 entities visually in a way similar to the various tools which generate SQL DDL script from a diagram of an entity-relationship model. I've tried various free tools including the Diagram Editor which is part of the Eclipse Dali project but all were rather flaky. So I decided to go a slightly indirect way.
Solution: Design entities in a ERD tool, export them to the Database and import them from the Database as JPA2 entities
Pre-requisites: MySQL Workbench, MySQL database, Eclipse (this solution was tested using Eclipse Java EE IDE for Web Developers, Juno Service Release 2), Eclipse Database connection, Eclipse JPA facet, Hibernate
Step 1. Creating the database tables
I created an ERD diagram, exported the DDL script and created the tables all within MySQL Workbench. The details (as well as the pre-requisites such as setting up a MySQL database) are beyond the scope of this post - I would just mention that no particular tweaking or coding is required. Also, it shall be possible to use any other JPA2 compliant database (e.g. Oracle).
Step 2. Importing the JPA2 entities
Pre-requisites for this step are:
- Establishing Database connection in Eclipse
- Creating a Java project and enabling the JPA facet for it in Eclipse (alternatively, a JPA project can be created from scratch). Since I chose to use Hibernate, rather than EclipseLink which is the default JPA implementation in Eclipse, I first had to set it up. The Eclipse Hibernate console needs to be set up, too.
I imported the tables as JPA2 entities using the functionality included in the JPA facet of Eclipse:
2.1. Click on the project and got to "JPA Tools"/"Generate Entities from Tables":
2.2. Set up the name of the output package (in my case "iw.pdfEx.persistence"):
Clicking on "Finish" starts the import from the database - this may take some time depending on the connection.
2.3. Remove the catalog entries from the generated Java code
The previous step generates a Java class (JPA2 entity) for each database table in the chosen package. It also automatically adds all the classes to persistence.xml.
It all sound well but in my case for each JPA entity Y I was getting an error "catalog X cannot be resolved for table Y" where X is the name of the database schema. I have no idea why as both the database connection and the persistence unit seem to be set up correctly. I found a quick workaround - I manually removed the catalog entry from each entity. The error disappeared - voila!
Some more troubleshooting
There is one more gotcha, however. Eclipse overwrites all JPA entities which already exist (I could not find a way of importing only some of the tables). In my case I had already two JPA2 entities. I had some simple logic added to some of the getter/setter methods - I had to recover it from the older git versions of the corresponding entities. Not nice. Also, all the named queries which I have defined were gone. Again, it was relatively easy to re-create them from the older git version. This prompted me to look for ways to define the JPQL queries separately from the code of the JPA2 entities. Fortunately, I found an easy way to do it - it is the subject of a separate post.
Conclusion
All in all, the above is a relatively easy and quick (generating the Java code from the ER diagram takes a few minutes) but somewhat dirty way of visually designing JPA2 entities. For more than 15 years it has been possible to visually design database models so I was really surprised that such a basic functionality is not readily provided by any of the tools I tried. Hopefully, Eclipse and the other JPA tools will catch up soon.
No comments:
Post a Comment