Spring is not scary, or how to understand what exactly the database meant

CONTENTS OF THE CYCLE OF ARTICLES To the database, as to a true woman, you need your own approach: not only do you need to ask her in your own way, you also have to think about the answers. In one of the previous articles , you implemented a project about books as a practice. Here we take it as a basis. If you haven't already, let's quickly implement the project skeleton: git clone https://FromJava@bitbucket.org/FromJava/book.git https://bitbucket.org/FromJava/book/src/master/ Download the project, look at its structure, run it. We will continue to work with him. Remember this entry from the article about database queries?

@Query("select f.fruitName, p.providerName from  FruitEntity f left join ProviderEntity p on f.providerCode = p.id")
    List<String> joinSting();

Let's do the same for the library. Go to BookRepository< > and write:

@Query("select b.nameBook, a.firstNameAuthor, a.lastNameAuthor, b.yearCreat
from  AuthorEntity a left join BookEntity b on a.id = b.authorId")
List<String> joinBookString();

Let's implement the method in BookService :

public List<String> joinBookSting() {
    return bookRepository.joinBookString();
}

Let's use it in InitialUtils and output it to the console:

System.out.println("\nТаблица книг и их авторов ,через строку");
for(String book: bookService.joinBookSting()){
    System.out.println(book);
}

Result: I think many of you have already understood that a string is not the most convenient format to work with, and probably many have already tried to remake the query to get an object. Let's declare a new joinBookObj() method in the BookService book repository with the same request, but instead of String we will put Object[] :

@Query("select b.nameBook, a.firstNameAutor, a.lastNameAutor,
b.yearCreat from  AutorEntity a left join BookEntity b on a.id = p.autorId")
    List<Object[]> joinBookObj ();

Let's implement it in BookService :

public List<Object[]> joinBookObj() {
    return bookRepository.joinBookObj();
}

And use in InitialUtils :

System.out.println("\nТаблица книг и их авторов, нечитаемый an object");
for(Object book: bookService.joinBookObj()){
    System.out.println(book);
}

Hurray, we got an object, only the output to the console of this entry is not at all pleasing. And it is not clear how to work with these objects further. It's time to map and use the StreamAPI (peace of mind). Let me remind you that in our case, mapping is the conversion of one object to another. One object is already there - for example, [Ljava.lang.Object;@48f2054d6 , it is an array of objects, with the following elements Object[b.nameBook , a.firstNameAutor , a.lastNameAutor , b.yearCreat] . And we will make another object with fields similar to array elements ourselves. Let's create the BookValueEntity class in the entities package :

package ru.java.rush.entities;

import lombok.Data;
import lombok.experimental.Accessors;

import javax.persistence.Entity;
import javax.persistence.Id;

@Accessors(chain = true)
@Entity
@Data
public class BookValueEntities {

    @Id
    Integer id;

    String nameBook;

    String firstNameAuthor;

That is, we wrote a class that contains fields similar to the fields requested from the database. Now in the BookService we implement the mapping itself using the stream. Rewrite this method in place with comments, read them and try to understand what this method does at each stage of execution.

public List<BookValueEntities> bookValueEntitiesList() {
    List<Object[]> objects = bookRepository.joinBookObj();//положим ответ от БД в переменную с типом Лист массивов Object-ов

    List<BookValueEntities> bookValueEntities = new ArrayList<>();//создадим лист конечных an objectов

    objects//берем переменную типа List<Object[]> (Лист массивов Object-ов), с ответом БД
            .stream()//превращаем Лист, состоящий из массивов Object-ов в стрим
            .forEach(//фор ич - терминальный оператор, выполняет указанное действие для каждого element стрима
                    //дальше идет лямбда, она говорит фор ичу - что делать для каждого element стрима
                    (obj) ->//объявляем(называем) переменную "obj" ей будут присваиваться an objectы стрима (массивы Object-ов)
                    {//так How запись в лямбде у нас в несколько строк, ставим {}
                        bookValueEntities.add(//фор ич возмет "obj" и добавит в List<BookValue>, предварительно сделав маппинг
                                new BookValueEntities()//создаем an object BookValueEntities
                                        //ниже происходит собственно маппинг
                                        //поля(элементы) "obj" записываются в соответсвующие поля созданного BookValueEntities
                                        //так How поле "obj" имеет тип Object  необходимо его привести к типу поля an object BookValueEntities т.е. String
                                        .setNameBook((String) obj[0])//записываем данные из одного поля в другое, [0] - значит первый элемент в массиве Object-ов
                                        //так How поле "obj" имеет тип Object  необходимо его привести к типу поля an object BookValue т.е. String
                                        .setFirstNameAuthor((String) obj[1])//записываем данные из одного поля в другое, [1] - значит второй элемент в массиве Object-ов
                                        //так How поле "obj" имеет тип Object  необходимо его привести к типу поля an object BookValue т.е. String
                                        .setLastNameAuthor((String) obj[2])//записываем данные из одного поля в другое, [2] - значит третий элемент в массиве Object-ов
                                        //так How поле "obj" имеет тип Object  необходимо его привести к типу поля an object BookValue т.е. Integer
                                        .setYearCreat((Integer) obj[3])//записываем данные из одного поля в другое, [3] - значит четвертый элемент в массиве Object-ов
                        );
                    }
            );
    return bookValueEntities;
}

We implement output to the console in InitiateUtils :

System.out.println("\nТаблица книг и их авторов , через стрим");
for(BookValueEntities book: bookService.bookValueEntitiesList()){
    System.out.println(book);
}

Click to execute. We get the output: You can now work with these objects normally. You don't have to think: why id = null, and how to do it? to be not null? As mentioned in previous articles, for complex queries, including inter-table queries, SQL is used. Let's see what we can do with it. First, let's rewrite the query to get the "Table of books and their authors" in SQL and put this query in the final variable in class BookService .

private final String SQL_COMPARISON = "select BOOKENTITY.id_book, BOOKENTITY.name_book, AUTHORENTITY.first_name, AUTHORENTITY.last_name,BOOKENTITY.year_creat from  " +
        "AUTHORENTITY left join BOOKENTITY on AUTHORENTITY.id_author = BOOKENTITY.author_id";

COMPARISON in Russian - comparison. Let's create a class in entities to match this request:

package ru.java.rush.entities;

import lombok.Data;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;


@Data
@Entity
public class BookValueEntitiesComparison {

    @Id
    @Column(name = "id_book")//назвали поле How в requestе
    Integer id;

    @Column
    String nameBook;//поле и так называется How в requestе, потому что Hibernate сгенерирует для него Name сам (name_book)

    @Column(name = "first_name")//назвали поле How в requestе
    String firstNameAuthor;

    @Column(name = "last_name")//назвали поле How в requestе
    String lastNameAuthor;

    @Column
    Integer yearCreat; //поле и так называется How в requestе

To implement the method in class BookService , we need to bring the EntityManager into the light of day . As the name implies, this is the boss of the entities. Let's write a variable in class BookService .

private final EntityManager entityManager;

I also found a boss, now we will build it. To do this, in class BookService we implement a method for reading the request:

public List<BookValueEntitiesComparison> bookValueEntitiesComparisonList() {
    return entityManager //зовем менеджера и начинаем ему указывать
            .createNativeQuery(//для начала создай пожалуйста "чистый"(native) SQL request
                    SQL_COMPARISON,//из этой строковой переменной возьми request
                    BookValueEntitiesComparison.class)// ответ замаппить в этот класс
            .getResultList();//а результат мне заверни в лист!!! И побыстрее!!!Шнеля, шнеля!!!
}

It seems to have cockroached to follow our instructions, let's see how he coped. InitiateUtils will be output to the console:

System.out.println("\nТаблица книг и их авторов, через сопоставление");
for(Object book: bookService.bookValueEntitiesComparisonList()){
    System.out.println(book);
}

Well done, did it. How did he do it? I just matched the names of the fields of the entity-class that we indicated to him and the result of the request. You can perform the same task in another way, through annotations: In the BookService class , create a new variable with a request:

private final String SQL_ANNOTATION = "select  BOOKENTITY.id_book as id_book_value, BOOKENTITY.name_book, AUTHORENTITY.first_name, AUTHORENTITY.last_name,BOOKENTITY.year_creat from  " +
        "AUTHORENTITY left join BOOKENTITY on AUTHORENTITY.id_author = BOOKENTITY.author_id";

Please note: it has changed a little, find the difference. Let's create a separate class in entities , where we will map.

package ru.java.rush.entities;


import lombok.Data;

import javax.persistence.*;

@SqlResultSetMapping(
        name = "BookValueMapping",//даем название нашему маппингу
        entities = @EntityResult(
                entityClass = BookValueEntitiesAnnotation.class,//указываем конечный класс куда будем маппить
                fields = {//в блоке полей указываем соответствие полей(name =) конечного класса и полей(colum =) результата requestа
                        @FieldResult(name = "id", column = "id_book_value"),
                        @FieldResult(name = "nameBook", column = "name_book"),
                        @FieldResult(name = "firstNameAuthor", column = "first_name"),
                        @FieldResult(name = "lastNameAuthor", column = "last_name"),
                        @FieldResult(name = "yearCreat", column = "year_creat")
                }
        )
)
@Data
@Entity
@Table(name = "BookValueEntitiesAnnotation")
public class BookValueEntitiesAnnotation {

    @Id
    @Column
    Integer id;

    @Column
    String nameBook;

    @Column
    String firstNameAuthor;

    @Column
    String lastNameAuthor;

    @Column
    Integer yearCreat;
}

In class BookService we implement the method:

public List<BookValueEntitiesAnnotation> bookValueEntitiesAnnotationList() {
        return entityManager//How и в прошлый раз зовем начальника
                .createNativeQuery(//давай нам чистый SQL request
                        SQL_ANNOTATION,//вот тебе текст requestа
                        "BookValueMapping")//вот тебе Name нашего маппинга
                .getResultList();//и How обычно заверни нам в лист!!! Ты еще тут?
    }
}

Did you see what a solid name of the method turned out? The longer you name the method, the more respect your colleagues treat you 😁 (there is some truth here). Let's control the manager's work as usual through InitiateUtils and output it to the console:

System.out.println("\nТаблица книг и их авторов, через аннотацию");
for(Object book: bookService.bookValueEntitiesAnnotationList()){
    System.out.println(book);
}

The result is similar to the previous ones: Well, the last option in the list, but not in value, is to do the mapping through the XML mapping file. The default mapping file is called orm.xml and will be used automatically if it is added to the META-INF directory of the jar file. As you can see below, this mapping is very similar to the annotation-based mapping we discussed earlier.

<sql-result-set-mapping name="BookMappingXml">
    <entity-result entity-class="ru.java.rush.entities
.BookValueEntitiesAnnotation ">
        <field-result name="id" column=" id_book_value "/>
        <field-result name=" nameBook " column=" name_book "/>
        <field-result name=" firstNameAuthor " column=" first_name"/>
        <field-result name=" yearCreat " column=" year_creat "/>
    </entity-result>
</sql-result-set-mapping>

That's all! At the current level, the simplest mapping is enough for you, you just need to know about the other methods. Here, in an article in the bourgeois language, variations of the methods of working with responses from the database, which we have analyzed, are described. Don’t worry about the language, click translate the page and you will get a completely readable translation. I know this for sure, because this article was prepared on the basis of it, and at school I studied German. For practice:

1. Add a new entity to the project, the book repository:

   BookStorageEntity
   Integer id;
   Integer bookId;
   String status; //книга выдана or нет

2. Fill in the table:
   Id = 1 bookId = 1 status = Issued;
   Id = 2 bookId = 2 status = In storage;
   Id = 3 bookId = 3 status = Under restoration;
   Id = 4 bookId = 4 status = Issued;
3. Create BookStorageRepository and BookStorageService .
4. Create cross-table JPQL and SQL queries that output the title of the book and whether it is checked out or not.
5. For JPQL, implement mapping according to the first option, for SQL - according to the second and third.

Bye everyone! See you!