Sample JPA Spring Boot

This is a sample of how to access the database from spring boot using JPA.

Here is the database structure:

Note a couple of things:

One to many first -> second
The field in the second table that links to the first is first_id
There is a foreign key constraint from second to the first

OK, now the application. Here are the maven dependencies (basically just add spring-java-jpa and that is it)

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <!-- for starting with web, use starter-web -->
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <!-- for starting with web, use starter-web -->
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client -->
        <dependency>
            <groupId>org.mariadb.jdbc</groupId>
            <artifactId>mariadb-java-client</artifactId>
            <version>3.1.4</version>
        </dependency>
    </dependencies>

The entity classes are FirstDt and SecondDt, and here they are:

package org.example.dt;

import jakarta.persistence.*;

import java.util.ArrayList;
import java.util.List;

@Entity
@Table(name = "first")
@NamedQueries(
        {@NamedQuery(name="first.query", query="select a from FirstDt a where a.comments = :cm1")}
)
public class FirstDt {
    @Id
    @Column(name = "first_id")
    private String id;

    @Column(name = "first_coments")
    private String comments;

    @Column(name = "shoe_size")
    private int shoeSize;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "first_id")
    private final List<SecondDt> items;

    public FirstDt() {
        this.items = new ArrayList<>();
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getComments() {
        return comments;
    }

    public void setComments(String comments) {
        this.comments = comments;
    }

    public int getShoeSize() {
        return shoeSize;
    }

    public void setShoeSize(int shoeSize) {
        this.shoeSize = shoeSize;
    }

    @Override
    public String toString() {
        final StringBuilder sb = new StringBuilder("FirstDt{");
        sb.append("id='").append(id).append('\'');
        sb.append(", comments='").append(comments).append('\'');
        sb.append(", shoeSize=").append(shoeSize);
        sb.append('}');
        return sb.toString();
    }
}

And second dt is here:

package org.example.dt;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "second")
public class SecondDt {

    @Id
    @Column(name = "second_id")
    private String id;

    @Column(name = "second_info")
    private String info;

    @Column(name = "salary")
    private double salary;

    public SecondDt() {
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getInfo() {
        return info;
    }

    public void setInfo(String info) {
        this.info = info;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }
}

And this is the service that is used to access through those

package org.example.service;

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.Query;
import jakarta.persistence.TypedQuery;
import jakarta.transaction.Transactional;
import org.example.dt.FirstDt;
import org.example.persist.IFirstRepo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public class InfoService {

    @PersistenceContext
    private EntityManager em;

    @Autowired
    private IFirstRepo firstRepo;

    @Transactional(Transactional.TxType.REQUIRED)
    public String createFirst() throws Exception {
        FirstDt first = new FirstDt();
        first.setId("id4");
        first.setComments("the comments for id4");
        first.setShoeSize(25);

        em.persist(first);

        return "current";
    }

    @Transactional(Transactional.TxType.REQUIRED)
    public List<FirstDt> getAllFirst() {
        List<FirstDt> items = firstRepo.findAll();

        return items;
    }

    @Transactional(Transactional.TxType.REQUIRED)
    public List<FirstDt> getAllSorted() {
        List<FirstDt> items = firstRepo.findAll(Sort.by(Sort.Direction.DESC, "shoeSize").and(Sort.by(Sort.Direction.ASC, "comments")));

        return items;
    }

    @Transactional(Transactional.TxType.REQUIRED)
    public List<FirstDt> getThatOne(String id) {
        return firstRepo.getThatOne(id);
    }

    @Transactional(Transactional.TxType.REQUIRED)
    public double getShoeSum() {
        return firstRepo.getSum();
    }

    @Transactional(Transactional.TxType.REQUIRED)
    public double getNativeShoeSum() {
        return firstRepo.getNativeSum();
    }

    public FirstDt searchThatFirst() {
        TypedQuery<FirstDt> query = em.createQuery("select a from FirstDt a where a.id = :itemId", FirstDt.class);
        query.setParameter("itemId", "id1");
        List<FirstDt> items = query.getResultList();

        FirstDt res = null;
        if (items.size() > 0) {
            res = items.get(0);
        }

        return res;
    }

    public FirstDt searchId() {
        FirstDt res = null;

        TypedQuery<FirstDt> query = em.createNamedQuery("first.query", FirstDt.class);
        query.setParameter("cm1", "the comments for id2");
        List<FirstDt> items = query.getResultList();

        if (items.size() > 0) {
            res = items.get(0);
        }

        return res;
    }

    public FirstDt getAllFirst(String id) {
        TypedQuery<FirstDt> query = em.createQuery("select f from FirstDt f left join fetch f.items where f.id = :id", FirstDt.class);
        query.setParameter("id", id);

        List<FirstDt> items = query.getResultList();
        FirstDt res = null;

        if (items.size() > 0) {
            res = items.get(0);
        }

        return res;

    }

}