Did you know: programmers convert coffe to code?

If you like my articles, support me with a small amount.


Buy me a coffee

JPA Queries in XML files

We have a codebase where we use Spring Data JPA to leverage our database access code. And as of this we have queries in interface definitions — and these  queries can become quite big.

That’s why I wanted to have them stored in separate files.

Why would one have them in a separate file?

Good question.

Thats mainly because you have big native queries which sometimes don’t work as you expect them. Or you have a big change in your codebase (or requirements) and have to modify these queries too. If you know how Spring Data JPA works then you may guess what my problem is.

If you do not know this library let me tell you some words about it: You create an interface definition which extends one of the already defined Spring Data interfaces, for example JpaRepository. In this interface you declare a method which will be your query method. There are various ways with JPA — however let’s stick with a basic native query for the sake of simplicity. In a real-world application you would add a mega native query with massive joins, filters, grouping and ordering. And this would look awfully in your Java class — and if you have a bug you want to copy it to your favorite SQL editor to fix the issues but you have to deal there with the problem that query values are Strings and eventually you format them into multiple lines…

I hope you get my problem. When you extract these queries into an external file you get the option to simply copy-paste it between your SQL editor of choice and the project source code.

How to achieve this?

I thought this will be a simple task because there will be many examples and tutorials on the internet. I was wrong. Of course there were some examples but they did not mention my special case where I want a structured set of files where I can put queries to make distinguishing and finding them easier. But let’s go the step-by-step way and see how you can achieve your goal too.

Example

It is time for the example. First of all let’s define a simple entity:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

/**
 * The example entity for this article.
 *
 * @author GHajba
 *
 */
@Entity
public class ExampleEntity {

    @Id
    @GeneratedValue
    private Long id;

    private String createdBy;

    /**
     * This empty constructor is because of hibernate.
     */
    ExampleEntity() {
    }

    /**
     * Simple constructor
     *
     * @param createdBy
     *            the name of the creator
     */
    public ExampleEntity(String createdBy) {
        this.createdBy = createdBy;
    }
    // getters and setters omitted
}

As you can see this is a very basic entity. Now let’s define the Spring Data repository:

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * A basic Spring Data JPA repository
 *
 * @author GHajba
 *
 */
public interface ExampleRepository extends JpaRepository {
}

Very basic version too, you can use the default queries like finding one by ID, find all and so on. But let’s add a “complex” query for the example:

@Query(nativeQuery = true, value = "SELECT id, createdBy FROM ExampleEntity where createdBy LIKE %:createdBy%")
List findInformationBasedOnCreator(@Param("createdBy") String createdBy);

As you can see the example is simple but if you replace this query with a more complicated one then you will see why extracting into a separate file is a good idea.

What is the goal?

The goal is to remove the query from the ExampleRepository Interface and have it in a defined XML file. We will achieve this with a named query so the result would look like this:

@Query(nativeQuery = true, name="ExampleRepository.findInformationBasedOnCreator")
List findInformationBasedOnCreator(@Param("createdBy") String createdBy);

If you change the application now you will see that when you try to use the repository you get the following exception:

org.springframework.data.mapping.PropertyReferenceException: No property findInformationBasedOnCreator found for type ExampleEntity!

This is because the mapping does not exist and in this case Spring Data looks for a property which has the same signature as the repository method (well, an implementation of the interface’s method).

Extract to orm.xml

First step is to extract the query into an orm.xml file as it is in most examples. That’s the easiest way because Hibernate loads orm.xml files automagically.

Where to place the orm.xml file? This is simple to answer: where you like. To have something more rational answer I would say place it into the project’s META-INF folder. This is what the file looks like:

<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings 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"
	version="2.0">
	<description>Extracted queries for repositories</description>
	<named-native-query name="ExampleRepository.findInformationBasedOnCreator">
		<query>
			SELECT id, createdBy FROM ExampleEntity where createdBy LIKE %:createdBy%
		</query>
	</named-native-query>
</entity-mappings>

One problem with this approach

If you try to run the application with this version of the orm.xml file you will get an exception:

java.lang.IllegalArgumentException: Parameter with that name [createdBy] did not exist

This is because for Hibernate in a string literal named parameters are not resolved. This is quite bad, isn’t it?

However there is a workaround for this which I do not like because it is a workaround but without this it won’t work.

The idea is to move the wildcard of the query (%) to the method call parameter. Sounds easy? Good.

First of all let’s remove the % signs from the query in the orm.xml file:


  SELECT id, createdBy FROM ExampleEntity where createdBy LIKE :createdBy

Now add these signs to the method call like this example:

this.repository.findInformationBasedOnCreator("%" + createdBy + "%")

Drawback: you have to do this every time when you call this method: at every place where you use this method in your repository. This is crap but we can live with it.

Extract to a different file

This is nice but what about you want to have multiple files to have some structure and differentiation in your repository files? Woodcutter approach is to create a folder structure under META-INF and have a lot of orm.xml files. Bad idea: you can have only one orm.xml file in the base case for the automagic — and this file has to be under right META-INF.

However there is a way to have multiple files containing query statements and you can tell it your JPA provider too. You could name all of them orm.xml and place them into separate folders but this would be a bad idea for your future.

There are many approaches how you can structure these files: one file for each repository, one file for all repositories under a package, one file for each maven module… Choose one and write it up in your decision log why you have chosen this way.

Now you have your file. In this example we simply create a jpql folder right under META-INF, move the orm.xml file to this folder and rename it to example.xml:

project_structure

If you start the application with this configuration you will get the same exception as previously:

org.springframework.data.mapping.PropertyReferenceException: No property findInformationBasedOnCreator found for type ExampleEntity!

The cause is that now Hibernate does not load the example.xml automatically because a) it is not located right under META-INF and b) it is not called orm.xml.

To fix this we have to add an entry for a mapping file into the persistence.xml file:

<?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="primary">
      <jta-data-source>java:jboss/datasources/exampleDS</jta-data-source>
      <mapping-file>META-INF/jpql/example.xml</mapping-file>
      <properties>
         <!-- Properties for Hibernate -->
         <!-- use validate if you run the application for the first time, it will create your tables in the database -->
<!--          <property name="hibernate.hbm2ddl.auto" value="update" /> -->
         <property name="hibernate.hbm2ddl.auto" value="validate" />
         <property name="hibernate.show_sql" value="false" />
      </properties>
   </persistence-unit>
</persistence>

As you can see in line 10 we added a mapping-file entry to the persistence.xml. The order of the tags does matter: you cannot simply have the mapping-files anywhere: for example if you put it after the properties tag, you’ll get an error because it is located at a wrong place.

The content of mapping-file has to be the file inclusive the path which you want to use as a mapping file. The mapping files have to have the structure of an orm.xml file (well, because it is in fact an orm.xml file but with a different name). The full path can start with a / but this is optional. So in the example META-INF/jpql/example.xml and /META-INF/jpql/example.xml would result in the same file loading.

A pitfall

Take care that query names you use are unique. If they are not (meaning you have to query definitions with the same name) you will get the following exception for example:

org.hibernate.DuplicateMappingException: Duplicate query mapping ExampleRepository.findInformationBasedOnCreator

So take care to have uniquely named queries. Such an error can happen if you use method overloading for your queries where you differentiate by their parameters and you follow my approach for example: the name of the query is ..

The application running

If you run the application you can expect to see something like these screenshots:

Conclusion

Extracting queries into external mapping files while using Spring Data JPA (or any other solution where you can use named queries) comes quite handy: you can leverage the size of interfaces, avoid //@formatter commands to keep indentation level of your formatted string-queries, and to easily copy-paste the native queries between your favorite SQL editor and your IDE.

The only drawback is with parameters combined with the LIKE operator where you have to add the % wildcard character manually to the parameters.

Sources

You can find the whole running example application at my GitHub account.

GHajba
 

Senior developer, consultant, author, mentor, apprentice. I love to share my knowledge and insights what I achieve through my daily work which is not trivial -- at least not for me.

Click Here to Leave a Comment Below 1 comments