Spring is not scary, or how to ask a database question

CONTENTS OF THE CYCLE OF ARTICLES Today we are finalizing the work with the database as part of our project. If you did everything correctly, then you should have a pom with the following dependencies:

<properties>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
    <java.version>1.8</java.version>
</properties>
<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.0.RELEASE</version>
    <relativePath/><!-- lookup parent from repository -->
</parent>
<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
    </dependency>
</dependencies>

And here is the structure of the project: Do you know who we haven’t remembered for a long time? This is application.yml, we didn't talk much about it in one of the previous articles. Now let's go ahead and delete it! Yes, yes delete and that's it! If we launch the project now, everything will work as before, try this. This happened because Spring itself was configured with default settings. Now we need to return our yml file back to the resources folder, we will still need it: application.yml (the name should be the same)

spring:
  datasource:
    driverClassName: org.h2.Driver
    url: jdbc:h2:mem:test;
    username: sa
    password:
    h2:
      console:
        enabled: true
  jpa:
    hibernate.ddl-auto: create
    generate-ddl: true
    show-sql: false
    properties:
      hibernate:
        dialect: org.hibernate.dialect.H2Dialect

Last time, we implemented several queries to the database using methods of the JpaRepository<> interface:

//сохранить одну запись в таблицу фруктов
public void save(FruitEntity fruitEntity){
    fruitRepository.save(fruitEntity);
}
//получить все записи из таблицы фруктов
public List<FruitEntity> getAll(){
   return fruitRepository.findAll();
}
//сохранить несколько записей в таблицу фруктов
public void saveAll(List<FruitEntity> fruits){
    fruitRepository.saveAll(fruits);
}

If you read about SQL , as I asked you to do last time, then you should know that such actions with the database must be performed using SQL queries. But there is no hint of this in the project; even in the console logs there is nothing similar. Let's find them, open application.yml, find the show-sql: line there (show sql) and change false to true. We launch the project and look at the console, the logs are filled with new entries very similar to SQL, in fact, most of them are not difficult to understand, for example:

Hibernate: drop table fruit_table if exists //удалить таблицу fruit_table  если она есть
Hibernate: drop table provider_table if exists
Hibernate: create table fruit_table (id_fruit integer not null, fruit_name varchar(255), provider_code integer, primary key (id_fruit))//создать таблицу fruit_table с полями id_fruit тип integer not null, fruit_name  тип varchar(255), provider_code тип integer, назначить первичным ключем поле id_fruit
Hibernate: create table provider_table (id_provider integer not null, provider_name varchar(255), primary key (id_provider))

But this entry may raise a lot of questions because of its question marks:

Hibernate: insert into fruit_table (fruit_name, provider_code, id_fruit) values (?, ?, ?)

Let's think logically: Firstly, we see the word Hibernate, which means this secretive guy has put his furry paw here. After reading about him on the Internet, we learn that Mr. Hiber is an implementation of the ORM model. The object-relational model describes the relationships between software objects and records in the database. Having fixed this idea, we continue to think logically: On the one hand, we have a FruitEntity object , it has three fields: Integer id; String fruitName; Integer providerCode. On the other hand, we have a table in the database fruit_table with fields id_fruit type integer, fruit_name type varchar(255) , provider_code type integer. Roughly speaking, Hibernate takes a FruitEntity object , pulls out the values ​​of the object's fields and writes them to the corresponding table fields. I have a question for you: Look, in the InitiateUtils class we implemented filling the fruit table, but for some reason we set the value to only two fields, where is the third?

new FruitEntity()
        .setFruitName("Fruit1")//раз
        .setProviderCode(Math.abs(new Random().nextInt() % 10)),//два
                                            //три???

I’m sure you’ll figure this out for yourself, besides, we briefly touched on this issue in the article before last. First, figure out which field is not here, and then you will understand everything. Well, well done, Hiber generated a bunch of requests for us. But we are not blind, let’s implement a few more methods from the JpaRepository<> interface in the FruitService class

//возвращает запись из таблицы по id
public Optional<FruitEntity> getById(Integer id){
   return fruitRepository.findById(id);
}

//удаляет запись из таблицы по id
public void delById(Integer id){
    fruitRepository.deleteById(id);
}

//возвращает true or false при поиске в таблице Фруктов an object который соответствует типу FruitEntity or принадлежит к типу an object который наследуется от FruitEntity
public Boolean exist(Example<? extends FruitEntity> example){
    return fruitRepository.exists(example);
}

Implement the same methods in the ProviderService class. Then use them in the InitiateUtils class for FruitEntity and ProviderEntity and print the result to the console. (By the way, if you didn’t know, you can quickly write “System.out.println()” by typing sout and pressing enter, the same thing works with “public static void main(String[] args){}” just type psvm and there you go etc ). I think you have already dealt with it and we are ready to move on. Let's go to the FruitRepository interface and start typing in it (namely typing and not copying) the following method: List<FruitEntity> f You should get the following thing. Just call the method as if you were composing a query: findById(Integer id) - finds an object by id; countFruitEntityByFruitName(String name) - will count the number of fruits with a specific name; These are queries generated by the name of the method; be sure to read about them and implement the method between(Integer from, Integer to) in the FruitService class to search List<FruitEntity> by the values ​​of the provider_code field included in a certain interval, and display the result of the work in the console. For example: find all the fruits whose supplier number is between 5 and 7. Don't rush to read further until you implement the method, it won't take long. As you may have read in the article about queries by method name: “You can’t write all queries like this, but simple ones can be written.” For more complex queries, the @Query annotation is used and JPQL is used instead of SQL (also take note of this article). For our project, you can make JOIN queries, like this:

package ru.java.rush.repositories;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import ru.java.rush.entities.FruitEntity;

import java.util.List;

@Repository
public interface FruitRepository extends JpaRepository<FruitEntity,Integer> {

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

}

A standard SQL query would be: "select fruit_table.fruit_name, provider_table.provider_name from fruit_table left join provider_table on fruit_table.provider_code = provider_table.id". Here you can easily establish a correspondence: f ruit_table is FruitEntiy f, where FruitEntiy is the type of the variable, f is its name, that is, SQL works with tables and fields, and JPQL with objects and their fields. Again fruit_table.fruit_name is f.fruitName ; Since we work with objects, we can output the object: Let’s write another FruitRepository method

@Query("select f from  FruitEntity f  join ProviderEntity p on f.providerCode = p.id")
List<FruitEntity> joinFruit();

Let's implement both methods in the FruitService class

public List<String> joinString(){
   return fruitRepository.joinSting();
}

public List<FruitEntity> joinFruit(){
    return fruitRepository.joinFruit();
}

It doesn’t sound bad, but they still use good old SQL for complex queries

@Query(
        value = "select fruit_table.fruit_name, provider_table.provider_name from  fruit_table  join provider_table on fruit_table.provider_code = provider_table.id_provider",  //по идее эту портянку надо засунуть в Howой нибудь  Enum
        nativeQuery = true) //нужно только пометить только nativeQuery = true
ListList<String> joinSqlFruit();

And we use them all in the InitiateUtils class

System.out.println("\nТаблица фруктов и их поставщиков");
for (String join : fruitService.joinString()) {
    System.out.println(join);
}

System.out.println("\nТаблица фруктов и их поставщиков");
for (FruitEntity join : fruitService.joinFruit()) {
    System.out.println(join);
}

System.out.println("\nТаблица фруктов и их поставщиков");
        for (String join : fruitService.joinSqlFruit()) {
            System.out.println(join);
        }

We launch the project and see new logs in the console: Table of fruits and their suppliers Fruit1,null Fruit2,Provider5 Fruit3,Provider2 Fruit4,Provider5 Fruit5,null Fruit6,null Fruit7,null Fruit8,null Fruit9,null Table of fruits and their suppliers FruitEntity(id= 2, fruitName=Fruit2, providerCode=5) FruitEntity(id=3, fruitName=Fruit3, providerCode=2) FruitEntity(id=4, fruitName=Fruit4, providerCode=5) Table of fruits and their suppliers Fruit2,Provider5 Fruit3,Provider2 Fruit4 ,Provider5 Yes, if you are already tired of “pseudo” and just SQL queries in the console, you can return false in its place in the yaml file. Why there is null in the first table, you will find out if you read about JOIN SQL . And so, we are done with queries to the database, I am sure that you still have many questions, but I hope that you will look for answers to them, I tried to highlight the search paths. Let's try to summarize everything we've learned during this time: 1. You can run a web server in Spring and it's not difficult. 2. To understand how it all works, you need to dive into theory. About the book Article about spring Article about useful things 3. In order for understanding to turn into physical code, you need to code, and before moving on, get your hands on simple projects on spring-boot. And it’s better not to copy the written code, but to rewrite it. I will post the project that you and I have been working on here, but I will rely on your awareness and I am sure you will not mindlessly copy-paste. Link to the git clone repository https://FromJava@bitbucket.org/FromJava/jd.git For those who do not know how to use this link I recommend implementing two training projects: Project about painting cars: First class: CarEntity{ Integer id; String modelName; String color; } Second class: ColorEntity{ Integer id; String color; Integer price; } Fill the database (come up with realistic names so it will be easier to understand), implement: , entity, repositories, services, create standard and cross-table queries (How much does it cost to paint a BMW red? What color is the most expensive? Write models to the console in alphabetical order and etc.); Library project: First class: BookEntity{ Integer id; String nameBook; Integer yearCreat; Integer autorId; } Second class: AutorEntity{ Integer id; String firstNameAutor; String lastNameAutor; } Fill the database (come up with realistic names so it will be easier to understand), implement: entity, repositories, services, create standard and inter-table queries (Who wrote which book? Which book was written first? Which books were written from 1800 to 1900? Which of authors wrote the most books?); Examples for filling out the "Library" project database Book table BookEntity(id=1, nameBook=Woe from Wit, yearCreat=1824, authorId=1) BookEntity(id=2, nameBook=War and Peace, yearCreat=1863, authorId=2) BookEntity(id=3, nameBook= Mtsyri, yearCreat=1838, authorId=3) BookEntity(id=4, nameBook=Eugene Onegin, yearCreat=1833, authorId=4) Authors table AuthorEntity(id=1, firstNameAuthor=Alexander, lastNameAuthor=Griboyedov) AuthorEntity(id=2 , firstNameAuthor=Lev, lastNameAuthor=Tolstoy) AuthorEntity(id=3, firstNameAuthor=Mikhail, lastNameAuthor=Lermontov) AuthorEntity(id=4, firstNameAuthor=Alexander, lastNameAuthor=Pushkin) Good luck everyone, see you again!