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

	<?xml version="1.0" encoding="UTF-8"?>
	<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
	<persistence-unit name="pdfEx" transaction-type="RESOURCE_LOCAL">
	<provider>org.hibernate.ejb.HibernatePersistence</provider>
	<mapping-file>META-INF/jpql_queries.xml</mapping-file>
	<!--validation-mode>AUTO</validation-mode-->
	<class>iw.pdfEx.persistence.Pdf</class>
	<class>iw.pdfEx.persistence.XmlConversion</class>
	<class>iw.pdfEx.persistence.BankAccount</class>
	<class>iw.pdfEx.persistence.Invoice</class>
	<class>iw.pdfEx.persistence.CvCurrencies</class>
	<class>iw.pdfEx.persistence.CompanyRegistration</class>
	<class>iw.pdfEx.persistence.Amount</class>
	<class>iw.pdfEx.persistence.Date</class>
	<class>iw.pdfEx.persistence.CompanyName</class>
	<class>iw.pdfEx.persistence.Currency</class>
	<class>iw.pdfEx.persistence.Email</class>
	<class>iw.pdfEx.persistence.Phone</class>
	<class>iw.pdfEx.persistence.Ap</class>
	<class>iw.pdfEx.persistence.Fax</class>
	<class>iw.pdfEx.persistence.Address</class>
	<class>iw.pdfEx.persistence.Www</class>
	<class>iw.pdfEx.persistence.VatId</class>
	<properties>
	<property name="hibernate.connection.username" value="icite"/>
	<property name="hibernate.connection.password" value="${hpass}"/>
	<property name="hibernate.connection.url" value="${hurl}"/>
	<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
	</properties>
	</persistence-unit>
	</persistence>

view raw persistence hosted with ❤ by GitHub

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:

	<?xml version="1.0" encoding="UTF-8"?>
	<entity-mappings version="2.0"
	xmlns="http://java.sun.com/xml/ns/persistence/orm"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd">
	<named-query name="Pdf.Queries.PdfByNameAndSize">
	<query><![CDATA[SELECT c FROM Pdf c WHERE c.name = :pdfname AND c.size = :pdfsize]]></query>
	</named-query>
	<named-query name="XmlConversion.Queries.XmlByPdfAndGit">
	<query><![CDATA[SELECT c FROM XmlConversion c WHERE c.pdf = :pdfname AND c.gitVersion = :git]]></query>
	</named-query>
	</entity-mappings>

view raw jpql_queries hosted with ❤ by GitHub