JPA - Spring / Spring Boot externalize/Isolate query SQL

Question

I am trying to isolate JPQL queries in Spring Boot.

I am in a SpringBoot project. There are many ways to query from @Query(...) on the entity itself, @Query(...) on the repository method (with HQL(query hibernate POO) or JPQL(query native POO) . Also with the properties (file.properties with the queries) + method name...

I am looking for this way: I've been reading: Similar to how Java EE does it. https://www.baeldung.com/jpa-query-parameters

I put a simple example, so that it can be appreciated.

Note: This class does not extend from "JPA". It's a regular DAO with @Repository as used in JEE.

  @Override
    public List<User> findAllUsersByRole(String roleName, Long roleID) { 
        TypedQuery<User> query = entityManager.createQuery(
                " SELECT u FROM User u " +
                " RIGHT JOIN UserRoles ur ON ur.userId = u.id " +
                " RIGHT JOIN Role r ON r.id = ur.roleId " +
                " WHERE r.name like :roleName AND r.id =:roleID ", User.class);
        //query.setParameter(1, roleName).getSingleResult();
        //query.setParameter(1, roleID).getSingleResult();
        query.setParameter("roleName", roleName);
        query.setParameter("roleID", roleID);
        List<User> user = query.getResultList();
        return user;
    }

Java quoting is pretty annoying.

The problem is that when there are many tables and many lines this is quite cumbersome (with the single quotes ""+..., I miss the JS interpolation in these cases), but sometimes, you need something native in "JPQL" to fetch different tables with certain aspects.

I also read this to supplement the separation: Clean way to externalize long (+20 lines sql) when using spring jdbc?

I have read that I can do it with:

<util:properties id="sqls" location="classpath:oracle/sqls.xml" />

.

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Employee Queries</comment>
    <string key="employee.insert">
         INSERT INTO......
</string>
</properties>

.

@Autowired
@Qualifier("sqls")
private Properties sqls;
String sql = sqls.getProperty("employee.insert");

I have the problem with "<util:properties id="sqls" location="classpath:oracle/sqls.xml" />". Assuming that I "can't" create an XML, how can I add that to the Spring Boot context and have it detect it for me?

My question is, how can I separate the JPQL code in SpringBoot into an xml file, just like this:

--------XML

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>User Queries</comment>
    <entry key="user.get">
        SELECT ....
    </entry>
</properties>

------Java DAO (custom in Spring Boot)

Note: This class does not extend from "JPA". It's a regular DAO with @Repository as used in JEE.

...
  @Autowired
    @Qualifier("sqls")
    private Properties sqls;
    String sql = sqls.getProperty("user.get");

    @Override
    public List<User> findAllUsersByRole(String roleName, Long roleID) {
        TypedQuery<User> query = entityManager.createQuery(sql)

        query.setParameter("roleName", roleName);
        query.setParameter("roleID", roleID);
        List<User> user = query.getResultList();
        return user;
    }
 ...

I've read this, but I'm still not clear. https://docs.spring.io/spring-boot/docs/1.0.1.RELEASE/reference/html/boot-features-external-config.html

• I know how to do it in Spring/Springboot with:
  • JPA method naming conventions for queries
  • JPA method naming conventions for queries + properties

But with the entityManager I am not able to isolate the query to another file. Therefore, this section would help me a lot.

I know that there are other ways, but I am only looking for the one that I comment on in the post.

Answer 1

Instead of reinventing a crappy wheel by providing an XML based properties file. Use the proper techniques and write an orm.xml (which is a JPA standard) and write the named query in there.

<?xml version="1.0" encoding="UTF-8" ?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm
                                     http://xmlns.jcp.org/xml/ns/persistence/orm_2_0.xsd" version="2.1">

    <!-- JPA Named Native Queries -->
    <named-native-query name="User.findAllUsersByRole">
        <query>SELECT u FROM User u
               RIGHT JOIN UserRoles ur ON ur.userId = u.id
               RIGHT JOIN Role r ON r.id = ur.roleId
               WHERE r.name like :roleName AND r.id =:roleID
        </query>
    </named-native-query>
</entity-mappings>

Then in your Java code just reference this named query

@Override
public List<User> findAllUsersByRole(String roleName, Long roleID) {
   TypedQuery<User> query = entityManager.createNamedQuery("User. findAllUsersByRole", User.class);
   query.setParameter("roleName", roleName);
   query.setParameter("roleID", roleID);
   return query.getResultList();
}

No need to bolt something additional on top with properties files, additional XML files etc. Just use the plain JPA standards and provide the queries in the orm.xml.

If the only thing you are trying to do is to remove the string concat upgrade to a newer Java version and just use text blocks. No need for concat just a large block of text (just as in JavaScript).

@Override
public List<User> findAllUsersByRole(String roleName, Long roleID) { 
    TypedQuery<User> query = entityManager.createQuery("""
               SELECT u FROM User u
                RIGHT JOIN UserRoles ur ON ur.userId = u.id
                RIGHT JOIN Role r ON r.id = ur.roleId
                WHERE r.name like :roleName AND r.id =:roleID""", User.class);        
    query.setParameter("roleName", roleName);
    query.setParameter("roleID", roleID);
    return query.getResultList();
}

Answer 2

In the end I was able to incorporate multiple ORMs which is the important thing. I didn't do it with properties, because it simply gave too many errors (SAXParse errors, file, persistence...). I did it with a @Configuration class starting from this example:

Spring Data JPA having multiple orm.xml files

I got something like this:

 entityManagerFactoryBean.setMappingResources("META-INF/orms/custom-orm-1.xml", "META-INF/orms/custom-orm-2.xml","META-INF/orm.xml");

At this point, the JPQL will behave strangely (I suppose because it will be missing some type of configuration property that I am overlooking) and the fields/columns that have camel case will have to be pondered @Column(name = "mysql_column_name"), such as

firstName = does not detect it as first_name, it detects it as firstName.

In this section you will have to put so that it detects it correctly

@Column(name = "first_name")
private String firstName;

and the same for the other fields/columns that carry camelCase. Same for entities.

I guess there is some property or properties that does that "firstName -> first_name" conversion for you. But for me, it's not a problem. If I find it, I'll post it. However, it works perfectly with the @Column and @Table with their respective camelcases and I can already have what I was looking for for this post.

Thanks to M. Deinum for the help.

-------------------- EDITED ----------------------

After reading a lot I found the solution why. It seems that if you declare LocalContainerEntityManagerFactoryBean, since there is no @Autowired for this case to be able to add the properties one by one to the other ones it had. So you have to add it back, both the dialect and the strategy. Looking at a lot of information I came to the same conclusion as our partner @genki98

EntityManager doesn't translate camel case to snake case

@Bean
public LocalContainerEntityManagerFactoryBean businessEntityManagerFactory(DataSource businessDataSource) {
    LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
    emf.setDataSource(businessDataSource);
    emf.setPackagesToScan("com.xxx.yyy");

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setShowSql(Boolean.valueOf(env.getProperty("spring.jpa.show-sql")));
    emf.setJpaVendorAdapter(vendorAdapter);

    HashMap<String, Object> properties = new HashMap<>();
    properties.put("hibernate.dialect", env.getProperty("spring.jpa.properties.hibernate.dialect"));
    properties.put("hibernate.format_sql", env.getProperty("spring.jpa.properties.hibernate.format_sql"));
    properties.put("hibernate.hbm2ddl.auto", env.getProperty("spring.jpa.properties.hibernate.hbm2ddl.auto"));
    properties.put("hibernate.physical_naming_strategy", "org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy");
    properties.put("hibernate.implicit_naming_strategy", "org.springframework.boot.orm.jpa.hibernate.SpringImplicitNamingStrategy");
    emf.setJpaPropertyMap(properties);
    // Multiples ORMS mapping
    emf.setMappingResources("META-INF/orms/custom-orm-1.xml", "META-INF/orms/custom-orm-2.xml","META-INF/orm.xml");
    return emf;
}

If you add

Properties properties = new Properties();
....
entityManagerFactoryBean.setJpaProperties(properties);

You get nulls you have to do it with HashMap. It looks like the entityManagerFactoryBean.setJpaProperties(properties); does not merge correctly: "ollectionUtils.mergePropertiesIntoMap(jpaProperties, this.jpaPropertyMap);"

So the Hibernate dialect for ORM queries works correctly without having to put the

@Column(name = "first_name")
private String firstName;

I hope this post helps as much as it helped me.