Generating a XSD schema from a XML file using the free community version of IntelliJ

I have been working with XML files produced by a third-party tool which had almost no documentation. I wanted to find ways to automatically parse them in Java and it seemed that this involves getting or re-creating the XSD schema.There is a thread on Stackoverflow where multiple possible solutions are suggested. Unfortunately, the thread is closed for further contributions so I am going to describe my solution here.

I stumbled almost by chance on the XML toolset in IntelliJ which includes such functionality. My solution involved the following simple steps:

1. Open a (hopefully representative) XML file in IntelliJ 

I used the free community version but I presume that the same functionality is included in the commercial version, too.

2. Go to "Tools"/"XML actions"/"Generate XSD schema from XML file"

I used the default values for all parameters apart from "Detect enumerations limit" which I changed from 10 to 2:

3. (optional) Verify the resulting XSD schema

The resulting XSD schema seems OK but optional elements may cause issues. If the XML file you have opened is missing them than the XSD schema will miss them, too. A better solution would be to create the XSD schema based on a set of files (I have dozens) rather than just on a single file. Unfortunately, I know of no tool that does that. Instead, I decided to verify the schema using the following method (based on Grzegorz Szpetkowski's excellent post on Stackoverflow):


4. (optional) Add optional elements to the XSD schema and adjust the data types

It turned out that a few of the other XML files did, indeed, contain optional elements that were not part of the XSD schema (i.e. they failed the xml validation) so I added them manually. Another manual change was setting the data type for the elements. I tried initially running the IntelliJ wizzards with automatic data type detection enabled ("Detect simple content type" set to "smart") but it did not work well. So I used in step 2 the "dumb" option of setting all data types to "string". Once I got the XSD schema I than manually set the data types.

Conclusion

The above is a relatively simple recipe - it takes about 20 minutes. Still, I am somewhat disappointed that currently no tool seems to be able to generate a XSD schema based on multiple files - such functionality would cut the manual effort by half and would make the recipe more robust.

Storing JPQL queries separately from the code of the JPA2 entities

I was recently interested about visually creating JPA2 entities in a way similar to creating database tables from an ER diagram. Turns out, this is not so well supported by the tools I tried but I found a workaround which involved exporting all the tables from a database. The solution had, however, the unintended side effect of overwriting those of the existing JPA2 entities which had a match to a table and this removed, among others, the named queries which I have already written. This happened because I have defined the queries in the same files in which I have stored the code of the JPA2 entities. This is how it is mostly done and I have simply followed the mainstream. I remember finding this solution rather inelegant as the queries tend to involve more than one JPA2 entity - so which one you assign it to?

Turns out, I was not the only one asking this question and there is a very nice solution:

Storing the query text in a separate XML mapping file(s)

I base my solution with minor changes on Arjan's excellent post. There are, essentially, two steps:

1. Define a separate XML mapping file(s) in persistence.xml

The relevant line is

<mapping-file>META-INF/jpql_queries.xml</mapping-file>


It refers to the file containing the named queries. It is placed in the META-INF folder, which is where persistence.xml is located, too. I thought initially that since both persistence.xml and the query file (jpql_queries.xml) are in the same folder I can use

<mapping-file>jpql_queries.xml</mapping-file>

but that's wrong - than the query file is not retrieved during the build time.

2. Storing the named queries in the respective file

The syntax of the queries can be the same as when the named queries are stored in the file containing the JPA2 entity. In my case, I decided to use the advice included in Mkyong's excellent post, namely, to   wrap the query text with CDATA, so that the XML parser will not prompt error for some special XML characters like ‘>’ , <’. For completeness sake I am including also the query file:

Generating JPA2 entities from database tables

Problem: How to design JPA2 entities in a visual way

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.