[JAVA] Memo after the first Spring project-Database-

Introduction

Only the basic knowledge of pure Java and DB The Spring project from the start has settled down after the turbulence, so please give us your impressions and memorandum. : speech_balloon: The content of the article is more than a tutorial and less than a practice. The posted code has not been confirmed to work, so it is for reference only. : pray:

Development environment

Red Hat (development environment is Cent) 7 series Java8 SpringBoot2.1.6 MySQL8 series STS

About SpringDataJPA

Related terms are difficult. SpringDataJPA is a framework that uses a JPA (specification) implementation (Hibernate). I'm not confident. The Criteria API used for dynamic query creation is a JPA function, so you can refer to it. EclipseLink seen in the sample is an implementation different from Hibernate, so it seems better not to refer to it. But [Be careful because I recommend the Spring function instead of the Hibernate function. ] (https://qiita.com/suke_masa/items/392976749fce94a8ef1f#hibernate-validator%E3%81%AE-notblank-%E3%81%A7%E3%81%AF%E3%81%AA%E3%81%8Fbean-validation%E3%81%AE-notblank-%E3%82%92%E4%BD%BF%E3%81%86%E3%81%B9%E3%81%97)

Knowing the life cycle of an entity may help resolve suspicious behavior such as adding or removing parent-child entities, or explicit transactions. https://qiita.com/Maruo110/items/4dc4a49aedd6323ebfdb

H2DB In-memory database.

Since it is included in Spring, you can use the database without preparing RDBMS in the environment. It is initialized every time you run the app. Since it can be used casually, it can be used for prototypes and tests.

The SQL grammar is unique to H2DB, but you can use other RDBMS-like grammars with the mode option, but it's not perfect. http://www.h2database.com/html/features.html#compatibility I was able to absorb how to specify the index of the create statement in mysql mode, I couldn't absorb stored, triggers, tinyint type mappings, etc. In the first place, if it seems to be a problem here, it means that it depends too much on the DB. : kissing:

About Entity

If you make it simple, it will be a class with only table information, but you can do quite a lot. : blush: It looks like you can process the data. : blush: Callback methods that can be called at the timing before data update (persistence) are also convenient. If you use: confused: AuditingEntityListener, you can use DI target class, but it will be difficult to test. : confused: It can be inherited, but it doesn't work well with lombok. : angry :: angry :: angry: "I tried to move it for the time being" If you try to use it as a form that is sometimes seen in the sample, it will be complicated in a blink of an eye, so separate the form and Entity properly.

Employee.java

@Entity
@Data
class Employee {
    @Id
    private long id;

    private String lastName;

    private String firstName;

    //Data processing
    public String getFullName(){
      return this.lastName + this.firstName;
    }

    private Date updateDate;

    //Callback method
    @PreUpdate
    private void preUpdate() {
      setUpdateDate(new Date());
    }
}

The association is Onimon. Information on the east and west is confused. If you don't check early whether it's One or Many, cross-reference, deferred fetch, delete behavior, etc., the range of influence will be crying. : joy:

About Repository

If you declare a method according to the naming convention, the query will be implemented automatically. If you have defined associations for count, exist, and Entity, you can also specify columns for related tables (https://qiita.com/opengl-8080/items/05d9490d6f0544e2351a#%E3%83%A1%E3%82% BD% E3% 83% 83% E3% 83% 89% E5% 90% 8D% E3% 81% 8B% E3% 82% 89% E3% 81% AE% E3% 82% AF% E3% 82% A8% E3% 83% AA% E8% 87% AA% E5% 8B% 95% E7% 94% 9F% E6% 88% 90). https://spring.pleiades.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.query-creation

The name tends to be long, so it seems better to wrap it with an appropriate name.

Optional can be used as the return value, so use it positively.

findAll can pass search conditions, paging information, and sort information as arguments. But when paging and sorting, put the sorting condition in the paging information. Note that select * ~ and select count ~ are issued internally when paging.

About dynamic queries

I used Specification. This is also a demon gate.

The basic usage can be introduced easily, but there are many things that you have to understand when trying to change a little. Specifically, CriteriaAPI or lambda expression. The metamodel seems to be useful, but I didn't use it this time because it was too late to know.

** ・ distinct **

hogeSpecification.java

public Specification<Hoge> distinct() {
    @Override
    public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        cb.distinct(true);
        return cb.equal(root.get("id"),root.get("id")); //Must be true. last resort.
    }
}

** ・ fetch ** You can join with root.join, but if you don't specify this

select *
   from a 
    innner join b1 on a.id = b1.id
    innner join b2 on a.id = b2.id
  where b1.id = {1}
    and b2.name = {2};

SQL that joins the same table has been issued many times. I don't understand the cause or the solution. : hankey:

Also, if you want to page with a fetched query, you need to enter the following description. if (query.getResultType() != Long.class && query.getResultType() != long.class) Because select * ~ and select count ~ are issued at the time of paging, but it cannot be fetched at the time of count, so it is necessary to avoid it. .. .. Eh? : hankey: https://coderanch.com/t/656073/frameworks/Spring-Data-fetch-join-Specification

Based on that, the code looks like this.

hogeSpecification.java

public Specification<Hoge> fetch() {
    @Override
    public Predicate toPredicate(Root<Users> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        if (query.getResultType() != Long.class && query.getResultType() != long.class){
            root.fetch("childTable", JoinType.LEFT); //The table to be joined is defined by Entity
        }
        return cb.equal(root.get("id"),root.get("id")) //Must be true. last resort.
    };
}

It seems that two or more cannot be fetched, and it could not be used for parent-child relationships, so I ended up processing the query results and cheating. It's difficult. .. .. : hankey :: hankey:

** ・ Control (conditional branching, looping, etc.) ** If it is a sample, you can connect everything with a method chain, but of course you can cut it in the middle. The multi-column specification of the IN clause in SQL (such as where (name, id) in ((hoge, 1), (fuga, 2))) is now implemented.

hogeService.java

public List<Hoge> serch(HogeForm form) {
    Specification<hoge>Search conditions= 
        Specification
            .where(Search condition 1)
            .and(Search condition 2);

    //Conditional branching
    if(Branch condition)Search conditions=Search conditions.and(Additional search conditions);

    //Like a loop
    Specification<hoge>Search conditions for loops= null;
    for(Loop){
Search conditions for loops=Search conditions for loops.or(Repeated search conditions);
    }
Search conditions=Search conditions.and(Search conditions for loops);

    return hogeRepository.findAll(Search conditions);
}

Other notes

Memo after the first Spring project-What is Spring- Memo after the first Spring project-MVC-