JPA - Spring / Spring Boot externalize/Isolate query SQL-CodePudding

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: https://exchangetuts.com/clean-way-to-externalize-long-20-lines-sql-when-using-spring-jdbc-closed-1639816685252746

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.

CodePudding user response：

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();
}