How Spring Data Jdbc joins tables

In this post, we will take a look at how Spring Data Jdbc builds sql queries to retrieve related entities.



The post is designed for novice programmers and does not contain any super tricky things.

I invite everyone to the demo day of the online course “Java Developer. Professional " . As part of the event, I will tell you in detail about the course program, as well as answer your questions.

A very part of the solution like Hibernate is used, because this is very convenient for working with nested objects.



For example, there is a class RecordPackage, one of the fields of this class is a collection of child (or nested) objects: records.



If you use Jdbc, you will have to write a lot of routine code. Few people like it, which is partly why they use Hiberhate.



In Hibernate, you can call one method at once to get a RecordPackage with all its children records.



On the one hand, I want to use one method to get the entire object, and on the other hand, I do not want to mess with the Hibernate monster.



Spring Data Jdbc allows you to get the best of these two worlds (or at least something acceptable).



Consider two cases:

• one-to-many relation
• one-to-one relationship

It is these connections that are most common in practice.



The complete code of examples can be found on GitHub, here I will give only the very minimum.

First of all, it's worth noting that Spring Data Jdbc is not a magic tool to solve any problem. It certainly has its drawbacks and limitations.

However, for a number of typical tasks, this is a perfectly suitable solution.

One-to-many relationship

As a real example, you can consider: the header of a packet of some data and the data lines included in this packet. For example, file is a package and file lines are data lines that go into that package.



The structure of the tables is as follows:

create table record_package
(
    record_package_id bigserial    not null
        constraint record_package_pk primary key,
    name              varchar(256) not null
);

create table record
(
    record_id         bigserial    not null
        constraint record_pk primary key,
    record_package_id bigint       not null,
    data              varchar(256) not null
);

alter table record
    add foreign key (record_package_id) references record_package;

two tables: record_package(header of a certain packet) and record(records included in the packet).

How this relationship is displayed in java code:

@Table("record_package")
public class RecordPackage {
    @Id
    private final Long recordPackageId;
    private final String name;

    @MappedCollection(idColumn = "record_package_id")
    private final Set<Record> records;
….
}

Here we are interested in defining a one-to-many relationship. This is encoded using annotation @MappedCollection.



This annotation has two parameters:



idColumn - the field by which the connection is made

; keyColumn - the field by which the records in the child table are ordered.



This ordering is worth mentioning separately. In this example, it doesn't matter to us in what order the child records will be inserted into the record table, but in some case it may be important. For such an ordering, the record table will have a field like record_no, this is the field that will need to be written in the keyColumn of the MappedCollection annotation. When you execute insert, Spring Data Jdbc will generate the values ​​of this field. In addition to annotation, Set <Record >will need to be replaced with List<Record >, which is quite logical and understandable. The explicitly specified sequence of child rows will be taken into account when forming the select, but we will come back to this later.



So, we have identified the connections and are ready to try it out.



We create related entities and get them from the base:

  var record1 = new Record("r1");
  var record2 = new Record("r2");
  var record3 = new Record("r3");

   var recordPackage = new RecordPackage( "package", Set.of(record1, record2, record3));
   var recordPackageSaved = repository.save(recordPackage);

   var recordPackageLoaded = repository.findById(recordPackageSaved.getRecordPackageId());

Note that we only need to call one method repository.findByIdto get an instance RecordPackagewith a filled collection of records.



Of course, we are interested in what kind of sql query was executed to get the nested collection records.



Compared to Hibernate, Spring Data Jdbc is good for its simplicity. It can be easily debugged to reveal the main points.



After a little investigation in the org.springframework.data.jdbc.core.convert package we find the DefaultDataAccessStrategy class . This class is responsible for generating SQL queries based on class information. Now in this class we are interested in the method

Iterable <Object> findAllByPath

Or more precisely the line:

String findAllByProperty = sql(actualType) 
    .getFindAllByProperty(identifier, path.getQualifierColumn(), path.isOrdered());

Here the required SQL query is retrieved from the internal cache.



In our case, it looks like this:

SELECT "record"."data" AS "data", "record"."record_id" AS "record_id", "record"."record_package_id" AS "record_package_id" 
FROM "record" 
WHERE "record"."record_package_id" = :record_package_id

Everything is clear and predictable.



What would it look like if we used the ordering of records in the child table? Obviously, order by would be required.



Let's move on to the BasicRelationalPersistentProperty class of the org.springframework.data.relational.core.mapping package. This class has a method that determines whether to add order by to the query or not.

	
public boolean isOrdered() {
  return isListLike();
}

and

private boolean isListLike() {
  return isCollectionLike() && !Set.class.isAssignableFrom(this.getType());
}

isCollectionLike verifies that we really have a "collection" (including an array).

And from the condition ! Set.class.isAssignableFrom (this.getType ()); it becomes clear that Set is not used by chance, but to exclude unnecessary sorting. And someday we will intentionally use List to enable sorting.



I think one-to-many is more or less clear, let's move on to the next case.

One-to-one relationship

Let's say we have such a structure.

create table info_main
(
    info_main_id bigserial    not null
        constraint info_pk primary key,
    main_data    varchar(256) not null
);

create table info_additional
(
    info_additional_id bigserial    not null
        constraint additional_pk primary key,
    info_main_id       bigint       not null,
    additional_data    varchar(256) not null
);

alter table info_additional
    add foreign key (info_main_id) references info_main;

There is a table with basic information on a certain object (info_main) and there is additional information (info_additional).



How can this be represented in code:

@Table("info_main")
public class InfoMain {
    @Id
    private final Long infoMainId;
    private final String mainData;

    @MappedCollection(idColumn = "info_main_id")
    private final InfoAdditional infoAdditional;
…
}

At first glance it looks like the first one-to-many case, but there is a difference. This time, the child is really an object, not a collection as in the previous case.



The code for testing looks like this:

  var infoAdditional = new InfoAdditional("InfoAdditional");

  var infoMain = new InfoMain("mainData", infoAdditional);

  var infoMainSaved = repository.save(infoMain);
  var infoMainLoaded = repository.findById(infoMainSaved.getInfoMainId());

Let's see which sql expression is generated this time. To do this, we will unearth the findById method to the location:



Package org.springframework.data.jdbc.core.convert class DefaultDataAccessStrategy . We are already familiar with this class, now we are interested in the method.

public <T> T findById(Object id, Class<T> domainType)

We see that the following request is retrieved from the cache:

SELECT "info_main"."main_data" AS "main_data", "info_main"."info_main_id" AS "info_main_id", "infoAdditional"."info_main_id" AS "infoadditional_info_main_id", "infoAdditional"."additional_data" AS "infoadditional_additional_data", "infoAdditional"."info_additional_id" AS "infoadditional_info_additional_id" 
FROM "info_main" 
LEFT OUTER JOIN "info_additional" "infoAdditional" 
ON "infoAdditional"."info_main_id" = "info_main"."info_main_id" 
WHERE "info_main"."info_main_id" = :id

Right now, the left outer join suits us, but what if not. How do I get inner join?

Creating functional join-s is in the package org.springframework.data.jdbc.core.convert , class SqlGenerator , method:

private SelectBuilder.SelectWhere selectBuilder(Collection<SqlIdentifier> keyColumns)

We are interested in this fragment:

		
for (Join join : joinTables) {
  baseSelect = baseSelect.leftOuterJoin(join.joinTable).on(join.joinColumn).equals(join.parentId);
}

If you need to join tables, then there is an option only with a left outer join.

It seems that inner join cannot be done yet.

Conclusion

We have considered two most typical cases of how you can join tables in Spring Data Jdbc.

In principle, the functionality that is now available is quite suitable for solving practical problems, although there are non-critical limitations.



The full text of the example can be found here .



And here is a video version of this post.