solving the problem of a dynamically changing request to databases

solving the problem of a dynamically changing request to databases

Hello, Habre! My name is Mykola Piskunov, I am a leading developer in the Big Data division. My colleagues have already introduced you to the beeline cloud blog SOLIDlooked in under the hood of Python I will talk about how to get a dynamic query to the database using Spring Data JPA and Hibernate with the lowest costs.

The simplest example is the implementation of an endpoint that processes filter requests of an online store. For example, when you specify the characteristics according to which certain items are returned.

We determine the tasks by levels

Level 1

What is given: the front from which requests are sent to the back on Spring boot, and the back, in its turn, must make an appropriate selection in the Postgresql database and return the result to the front.

If someone does not remember, Spring boot is a java framework sharpened for the Internet. Although many things are solved with its help other tasks.

Suppose we have a database, and it contains one simple table:

From it for the front we have to give records, filtered fields fooFieldOne. For this, Spring Data JPA implements a rather nice syntax:

public interface FooEntityRepository extends JpaRepository<FooEntity, UUID> {
   List<FooEntity> findByFooFieldOne(String fooFieldOne);
}

When the findByFooFieldOne method is called, Hibernate constructs a query equivalent to a native SQL query:

SELECT * FROM foo WHERE foo_field_one = ‘blabla’;

As a result, we will receive an array of elements that satisfy the request, and after processing, we return the data to the front.

If you need to filter data by two fields, then Spring boot JPA will also please you. Nothing complicated:

public interface FooEntityRepository extends JpaRepository<FooEntity, UUID> {
   List<FooEntity> findByFooFieldOne(String fooFieldOne);
 
   List<FooEntity> findByFooFieldOneAndFooFieldTwo(String fooFieldOne, String fooFieldTwo);
}

Level 2

And now, to what was given above, we add an additional bar table, which is related to the foo table. The structure of the database will be as follows:

Now the front will need filtering on the bar_field_one field. In this case, Spring Data JPA provides the @Query instruction, with which we can extend the functionality of our repository methods as follows:

public interface FooEntityRepository extends JpaRepository<FooEntity, UUID> {
 
   @Query(value = """
       SELECT f.* FROM foo f
       JOIN bar b on f.id = b.foo_id
       WHERE f.foo_field_one = ?1 and b.bar_field_one = ?2
  """, nativeQuery = true)
   List<FooEntity> findByFooFieldOne(String fooFieldOne, String barFieldOne);
 
   @Query(value = """
       SELECT f.* FROM foo f
       JOIN bar b on f.id = b.foo_id
       WHERE f.foo_field_one = ?1 and f.foo_field_two = ?2 and b.bar_field_one = ?3
  """, nativeQuery = true)
   List<FooEntity> findByFooFieldOneAndFooFieldTwo(String fooFieldOne, String fooFieldTwo, String barFieldOne);
}

As you can see, it is enough to know SQL to get the desired result for the front.

Level 3

But what if we need to retrieve data based on dynamically populated fields? That is, we may receive a request from the front where not all fields are filled out.

Of course, we can process all variants of filled fields approximately as follows:

public interface FooEntityRepository extends JpaRepository<FooEntity, UUID> {
 
   List<FooEntity> findByFooFieldOne(String fooFieldOne);
 
   List<FooEntity> findByFooFieldTwo(String fooFieldTwo);
 
   List<FooEntity> findByFooFieldOneAndFooFieldTwo(String fooFieldOne, String fooFieldTwo);
 
   @Query(value = """
       SELECT f.* FROM foo f
       JOIN bar b on f.id = b.foo_id
       WHERE f.foo_field_one = ?1 and b.bar_field_one = ?2
  """, nativeQuery = true)
   List<FooEntity> findByFooFieldOneAndBarFieldOne(String fooFieldOne, String barFieldOne);
 
   @Query(value = """
       SELECT f.* FROM foo f
       JOIN bar b on f.id = b.foo_id
       WHERE f.foo_field_two = ?1 and b.bar_field_one = ?2
  """, nativeQuery = true)
   List<FooEntity> findByFooFieldTwoAndBarFieldOne(String fooFieldTwo, String barFieldOne);
 
   @Query(value = """
       SELECT f.* FROM foo f
       JOIN bar b on f.id = b.foo_id
       WHERE f.foo_field_one = ?1 and f.foo_field_two = ?2 and b.bar_field_one = ?3
  """, nativeQuery = true)
   List<FooEntity> findByFooFieldOneAndFooFieldTwoAndBarFieldOne(String fooFieldOne, String fooFieldTwo, String barFieldOne);
 
  @Query(value = """
       SELECT f.* FROM foo f
       JOIN bar b on f.id = b.foo_id
       WHERE b.bar_field_one = ?1
  """, nativeQuery = true)
   List<FooEntity> findByBarFieldOne(String barFieldOne);
}

For this, in the service class that will process the request, you need to implement the algorithm for choosing the desired method. And, in fact, we get the desired result.

But what if there are other dynamically populated fields for filtering? For example, there will be six of them, and this is already 21 options of different combinations. That is, our repo class will be very large. At the same time, the complexity of the service class will increase. It will simply be impossible to maintain the code.

The @PersistenceContext directive is provided to solve this problem. This means that we will be able to pull from Hibernate’s EntityManager context.

Let’s assume that all the filter fields from the query are in dto:

@Data
@Builder
@FieldDefaults(level = AccessLevel.PRIVATE)
public class GetParams {
   String fooFieldOne;
   String fooFieldTwo;
   String barFieldOne;
}

And, having created an additional repo class, we need to implement the necessary request to the database:

@Repository
public class FooEntityRepository {
   @PersistenceContext
   EntityManager entityManager;
 
   public List<FooEntity> findFoos(GetParams params) {
   Map<String, Object> parameterMap = new HashMap<>();
   StringBuilder builder = new StringBuilder();
 
   // WHERE 1 = 1 требуется на случай, если с фронта не будет получено никаких значений фильтра
   StringBuilder whereBuilder = new StringBuilder("WHERE 1 = 1").append(System.lineSeparator())
 
   // Первая строка селекта
   builder
       .append("SELECT f.* FROM foo f ")
       .append(System.lineSeparator())
  
   // Добавим условие «если было передано поле fooFieldOne»
   if (StringUtils.isNotBlank(params.getFooFieldOne)) {
      whereBuilder
       .append("AND f.foo_field_one = :fooFieldOne")
       .append(System.lineSeparator());
      parameterMap.put("fooFieldOne", params.getFooFieldOne());
   }
 
   // Добавим условие «если было передано поле fooFieldTwo»
   if (StringUtils.isNotBlank(params.getFooFieldTwo)) {
      whereBuilder
       .append("AND f.foo_field_two = :fooFieldTwo")
       .append(System.lineSeparator());
      parameterMap.put("fooFieldTwo", params.getFooFieldTwo());
   }
  
   // Приджойним таблицу bar и добавим условие «если было передано поле barFieldOne»
   if (StringUtils.isNotBlank(params.getBarFieldOne)) {
      builder
       .append("JOIN bar b on f.id = b.foo_id")
       .append(System.lineSeparator())
 
      whereBuilder
       .append("AND b.bar_field_one = :barFieldOne")
       .append(System.lineSeparator());
      parameterMap.put("barFieldOne", params.getBarFieldOne());
   }
  
   // Соберем и подготовим весь запрос
   builder
   	.append(whereBuilder);
   String nativeQuery = builder.toString();
  
   Query query = entityManager.createNativeQuery(nativeQuery, RentCarCarEntity.class);
   for (Map.Entry<String, Object> entry : parameterMap.entrySet()) {
      nativeQuery = nativeQuery.replace(":" + entry.getKey(), entry.getValue().toString());
      query.setParameter(entry.getKey(), parameterMap.get(entry.getKey()));
   }
   log.debug("\n{}\n", nativeQuery);
  
   // Выполним и вернем результат
   return query.getResultList();
}

As a result, we get a dynamically formed native query to the database, which will return the desired result to the front.

And if we will have additional fields for filtering or additional tables and relationships, and even more so, we will need to use SQL statements order, having or receive data page by page, then it is better to rewrite this class using one of those give birth to design patterns.

Well, if the number of requests increases to 100 per second and above, it is better to implement Redis and Elastic. Although in my practice there were cases of using exclusively the capacities of the database, which processed similar requests at a load of ~300 rps.


Other development articles

How to deploy CRM in 7 steps? CRM helps the company to lead customers from acquaintance to building loyalty, without losing information along the way and automating part of the process. We tell you how to implement a CRM platform in 7 steps so that employees do not stop using it.

Why do middle and senior developers need to participate in hackathons? If you think that hackathons are competitions only for novice professionals, it’s time to change your mind. We tell you what benefits participation in hackathons can bring to advanced developers. Creating something new, gaining useful contacts, gaining recognition are only a small part of the arguments.

How does Jmix help build business applications? Jmix is ​​an open framework based on Java and Spring Boot. It helps to develop high-performance applications, work with data and design the interface. In the material, we talk about why you should choose a framework, what advantages it gives when working with clients, and how to apply it in practice.

beeline cloud – Secure cloud provider. We develop cloud solutions so that you provide customers with the best services.

Related posts