Support for data access for non-dynamic usage patters

Hi,

i would like to suggest or start a(-nother) open discussion about data access in cases where i do not want to explicitly define JPQL statements, but instead act on a slightly higher abstraction level.
I know that the platform relies (and advocates) heavily on JPQL (and even puts it up to the UI with the datasource concept) and oftentimes i really appreciate that.
Nevertheless, i think there are use cases where it is not necessary to know about the details of a JPQL query.

There are different options for something like this (not meaning that the libs can be used (although sometimes the could, but more as an example of the approach they took). I will just pick a few here, that i’m aware of (which does not mean that this is a representative choice):

GORM - gorm.grails.org

Basically there are two major options in GORM for doing queries.

  1. dynamic finders. This option basically looks like this Book.findByTitleLike(“Harry Pot%”). Dynamic Finders allow super easy access for easy queries, more medium advanced queries they are mostly not used.
  2. where queries. Where queries are similar to dynamic finders but they allow more complex expressions. Here’s an example:

Person.where {
    (lastName != "Simpson" && firstName != "Fred") || (firstName == "Bart" && age > 9)
}

Where queries have the advantage that compared to a JPQL (or HQL in this case as well as hibernates criteria API) they allow
compile time checks (like dynamic finders as well).

Spring Data JPA - projects.spring.io/spring-data-jpa

In the Spring Data world normally a interface has to be created to defines access methods. This interface
will gets translated dynamically to JPQL through Spring, so there does not need to be an implementation of this interface.
It is the same like the configuration interfaces that can be defined in CUBA for receiving app properties.


public interface SimpleUserRepository extends CrudRepository<User, Long> {

	List<User> findByLastname(String lastname);

	@Query("select u from User u where u.firstname = :firstname")
	List<User> findByFirstname(String firstname);
}

JPA Named Queries - www.objectdb.com/java/jpa/query/named

In the JPA world there is the possibility to define named queries. Those queries are just names for JPQL expression, meaning that there are no compile time checks e.g. Here’s an example:


@Entity
@NamedQueries({
    @NamedQuery(name="Country.findAll",
                query="SELECT c FROM Country c"),
    @NamedQuery(name="Country.findByName",
                query="SELECT c FROM Country c WHERE c.name = :name"),
}) 
public class Country {
  //...
}

CUBA predefined JPQL REST queries

In the REST API (v2) there is the possibility to do actually the exact same thing as JPA named queries + the knowledge of the views.
Those are just names as well, not type checked etc. But they are adjustable at runtime, which is interesting in some cases.

Here’s an example:


<queries>
    <query name="carByVin" entity="ref$Car" view="carEdit">
        <jpql><![CDATA[select c from ref$Car c where c.vin = :vin]]></jpql>
        <params>
            <param name="vin" type="java.lang.String"/>
        </params>
    </query>
</queries>

I don’t want to favor any kind of framework here, because basically to some degree they have all the same features.
In Django and Rails are very similar to the grails approach (or the other way around actually :)).

The whole point is just to list different options, because i want to show the different abstraction levels. When looking at options for something like this in CUBA i always thing of it with support for the security layer (otherwise i could just most of the libraries straight away - without CUBA support).

As there are more of these requests (like myself a fairly long time ago or a request about named queries) i would like to start a conversation about the topic and what we can do about it.

What to you think about these thoughts?

Bye
Mario

Hi Mario,

Thank you for raising this question again. I have some thoughts about it:

  • First of all, I think that such additional querying mechanism is worth implementing if it saves developers from the main drawback of JPQL - the fact that it is just a string and hence not type-safe.

  • The queries defined in the new way should be available for use in all existing data access mechanisms: GUI datasources, DataManager queries and EntityManager queries.

  • The previous requirement means that we need to be able to translate new queries to standard JPQL. It will also ensure that platform security restrictions will be applied automatically.

From what I have seen so far, I like the approach and the resulting API of Querydsl the most. It’s type-safe, concise and easily readable. I think Studio could generate query model classes on-the-fly when you are working with data model, which would streamline coding queries.

The main question here is whether we can extract JPQL from Querydsl queries to pass to the standard platform mechanisms. If it’s possible, we also have to design a way to call such queries in datasources XML and other declarative mechanisms like predefined REST queries - but it’s certainly not a hard problem.

Of course, I would like to hear more opinions on this topic before starting any research and implementation.

3 Likes

:ticket: See the following issue in our bug tracker:

https://youtrack.cuba-platform.com/issue/PL-8027