Spring Boot + Spring Data JPA + MySQL(Hikari Connection Pool) - Leo Dev Blog

В этой статье мы научимся создавать приложение с помощью Spring Boot + Spring Data JPA + MySQL + пул соединений HikariCP
Технологии:

Spring Boot 1.5.9.RELEASE
Spring Data 1.11.9.RELEASE
Hibernate 5.0.12 Final
MySQL Server 5.7.19-17
MySQL JDBC driver 5.1.44
Hikari Connection Pool 2.7.6
Maven
Java 8

1. Структура каталогов

2. Зависимости

Объявляем spring-boot-starter-data-jpa, он объединяет в себе Spring Data, Hibernate и соответственно JPA.

<em><strong>Hibernate</strong> является одной из многих реализаций спецификации <strong>JPA.
</strong></em>

1 2	<em><strong>Hibernate</strong> является одной из многих реализаций спецификации <strong>JPA. </strong></em>

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>

	<groupId>ru.leodev.examples.springboot</groupId>
	<artifactId>springboot-jpa-mysql</artifactId>
	<version>0.0.1</version>
	<packaging>jar</packaging>

	<name>springboot-jpa-mysql</name>
	<description>Demo Spring Boot + Spring Data JPA + MySQL</description>
	<url>http://leodev.ru/blog/spring-boot/spring-boot-spring-data-jpa-mysql/</url>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>1.5.9.RELEASE</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
		<java.version>1.8</java.version>
	</properties>

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-data-jpa</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<!-- HikariCP connection pool -->
		<dependency>
			<groupId>com.zaxxer</groupId>
			<artifactId>HikariCP</artifactId>
			<version>2.7.6</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>


</project>

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

<groupId>ru.leodev.examples.springboot</groupId>

<artifactId>springboot-jpa-mysql</artifactId>

<name>springboot-jpa-mysql</name>

<description>Demo Spring Boot + Spring Data JPA + MySQL</description>

<url>http://leodev.ru/blog/spring-boot/spring-boot-spring-data-jpa-mysql/</url>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-parent</artifactId>

<version>1.5.9.RELEASE</version>

</parent>

<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>

<java.version>1.8</java.version>

</properties>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-starter-data-jpa</artifactId>

</dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<scope>runtime</scope>

</dependency>

<groupId>com.zaxxer</groupId>

<artifactId>HikariCP</artifactId>

</dependency>

</dependencies>

<build>

<groupId>org.springframework.boot</groupId>

<artifactId>spring-boot-maven-plugin</artifactId>

</plugin>

</plugins>

</build>

</project>

3. Java Persistence API – JPA

3.1 Модель User JPA

[stextbox id=’alert’ caption=’Требования к Entity классам’]

Должен иметь пустой конструктор (public или protected);

Не может быть вложенным, интерфейсом или enum;

Не может быть final и не может содержать final-полей/свойств;

Должен содержать хотя бы одно @Id-поле.

[/stextbox]

package ru.leodev.examples.springboot.springbootjpamysql.model;


import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.util.Date;

@Entity
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private String email;

    @Column(name = "CREATED_DATE")
    private Date date;

    // наличие пустого конструктора обязательно при работе с JPA
    public User() {
    }

    public User(String name, String email, Date date) {
        this.name = name;
        this.email = email;
        this.date = date;
    }

    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", email='" + email + '\'' +
                ", date=" + date +
                '}';
    }
}

package ru.leodev.examples.springboot.springbootjpamysql.model;

import javax.persistence.Column;

import javax.persistence.Entity;

import javax.persistence.GeneratedValue;

import javax.persistence.GenerationType;

import javax.persistence.Id;

import java.util.Date;

@Entity

public class User {

@Id

@GeneratedValue(strategy = GenerationType.AUTO)

private Long id;

private String name;

private String email;

@Column(name = "CREATED_DATE")

private Date date;

// наличие пустого конструктора обязательно при работе с JPA

public User() {

}

public User(String name, String email, Date date) {

this.name = name;

this.email = email;

this.date = date;

}

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public Date getDate() {

return date;

}

public void setDate(Date date) {

this.date = date;

}

@Override

public String toString() {

return "User{" +

"id=" + id +

", name='" + name + '\'' +

", email='" + email + '\'' +

", date=" + date +

'}';

}

4. Configuration + Database Initialization

4.1 Configure Oracle data source, HikariCP settings and show Hibernate query.

spring.main.banner-mode=off

# настройки доступа к базе через MySQL
spring.datasource.url=jdbc:mysql://leodev.ru/leodes_spring?useSSL=false
spring.datasource.username=leodes_spring
spring.datasource.password=spring
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# Поддерживаем соединение живым, если долго не было запросов мождет отвалиться
spring.datasource.testWhileIdle = true
spring.datasource.validationQuery = SELECT 1

# ===============================
# = JPA / HIBERNATE
# ===============================
# создавать и удалять базу, используя import.sql как дамп
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
# Naming strategy
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
# Allows Hibernate to generate SQL optimized for a particular DBMS
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# HikariCP
# spring.datasource.hikari.*
spring.datasource.hikari.connection-timeout=60000
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.type=com.zaxxer.hikari.HikariDataSource

# логи
logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n
logging.level.org.hibernate.SQL=debug
#logging.level.org.hibernate.type.descriptor.sql=trace
logging.level.=error

spring.main.banner-mode=off

# настройки доступа к базе через MySQL

spring.datasource.url=jdbc:mysql://leodev.ru/leodes_spring?useSSL=false

spring.datasource.username=leodes_spring

spring.datasource.password=spring

spring.datasource.driver-class-name=com.mysql.jdbc.Driver

# Поддерживаем соединение живым, если долго не было запросов мождет отвалиться

spring.datasource.testWhileIdle = true

spring.datasource.validationQuery = SELECT 1

# ===============================

# = JPA / HIBERNATE

# ===============================

# создавать и удалять базу, используя import.sql как дамп

spring.jpa.hibernate.ddl-auto=create-drop

spring.jpa.show-sql=true

# Naming strategy

spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy

# Allows Hibernate to generate SQL optimized for a particular DBMS

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5Dialect

# HikariCP

# spring.datasource.hikari.*

spring.datasource.hikari.connection-timeout=60000

spring.datasource.hikari.maximum-pool-size=5

spring.datasource.type=com.zaxxer.hikari.HikariDataSource

# логи

logging.pattern.console=%d{yyyy-MM-dd HH:mm:ss} %-5level %logger{36} - %msg%n

logging.level.org.hibernate.SQL=debug

#logging.level.org.hibernate.type.descriptor.sql=trace

logging.level.=error

4.2 Если import.sql найден, Hibernate автоматически его импортирует

INSERT INTO `user` (ID, NAME, EMAIL, CREATED_DATE) VALUES(1, 'leodev','111@leodev.ru', STR_TO_DATE('2018-03-21', '%Y-%m-%d'));
INSERT INTO `user` (ID, NAME, EMAIL, CREATED_DATE) VALUES(2, 'alex','222@leodev.ru', STR_TO_DATE('2018-04-22', '%Y-%m-%d'));
INSERT INTO `user` (ID, NAME, EMAIL, CREATED_DATE) VALUES(3, 'kirito','333@leodev.ru', STR_TO_DATE('2018-05-23', '%Y-%m-%d'));

INSERT INTO `user` (ID, NAME, EMAIL, CREATED_DATE) VALUES(1, 'leodev','111@leodev.ru', STR_TO_DATE('2018-03-21', '%Y-%m-%d'));

INSERT INTO `user` (ID, NAME, EMAIL, CREATED_DATE) VALUES(2, 'alex','222@leodev.ru', STR_TO_DATE('2018-04-22', '%Y-%m-%d'));

INSERT INTO `user` (ID, NAME, EMAIL, CREATED_DATE) VALUES(3, 'kirito','333@leodev.ru', STR_TO_DATE('2018-05-23', '%Y-%m-%d'));

Подробнее о инициализации баз данных в <a href="https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html" target="_blank" rel="noopener">Spring Database initialization</a>

1	Подробнее о инициализации баз данных в <a href="https://docs.spring.io/spring-boot/docs/current/reference/html/howto-database-initialization.html" target="_blank" rel="noopener">Spring Database initialization</a>

5. @Repository

5.1 Создаем интерфейс и наследуемся от Spring Data CrudRepository

package ru.leodev.examples.springboot.springbootjpamysql.dao;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import ru.leodev.examples.springboot.springbootjpamysql.model.User;

import java.util.Date;
import java.util.List;
import java.util.stream.Stream;

public interface UserRepository extends CrudRepository<User, Long>{
    List<User> findByEmail(String email);

    List<User> findByDate(Date date);

    // пример кастомного запроса который возвращает Stream (Java 8 Stream API)
    @Query("SELECT u FROM User u WHERE u.email = :email")
    Stream<User> findByEmailReturnStream(@Param("email") String email);
}

package ru.leodev.examples.springboot.springbootjpamysql.dao;

import org.springframework.data.jpa.repository.Query;

import org.springframework.data.repository.CrudRepository;

import org.springframework.data.repository.query.Param;

import ru.leodev.examples.springboot.springbootjpamysql.model.User;

import java.util.Date;

import java.util.List;

import java.util.stream.Stream;

public interface UserRepository extends CrudRepository<User, Long>{

List<User> findByEmail(String email);

List<User> findByDate(Date date);

// пример кастомного запроса который возвращает Stream (Java 8 Stream API)

@Query("SELECT u FROM User u WHERE u.email = :email")

Stream<User> findByEmailReturnStream(@Param("email") String email);

}

Нет необходимости в реализации, данные Spring создадут общую реализацию по имени поля, например findByfieldName (). Подробнее working with Spring Data Repositories

6. Spring Boot Starter

package ru.leodev.examples.springboot.springbootjpamysql;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.transaction.annotation.Transactional;
import ru.leodev.examples.springboot.springbootjpamysql.dao.UserRepository;
import ru.leodev.examples.springboot.springbootjpamysql.model.User;

import java.text.SimpleDateFormat;
import java.util.stream.Stream;

@SpringBootApplication
public class SpringbootJpaMysqlApplication {

    private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    public static void main(String[] args) {
        SpringApplication.run(SpringbootJpaMysqlApplication.class, args);
    }

    @Bean
    public CommandLineRunner demo(UserRepository repository) {
        return new CommandLineRunner() {
            // начиная со spring boot 1.5 ридонли над методом который вызывает репозиторий нужен обязательно
            // что бы не закрывалась сессия.
            @Override
            @Transactional(readOnly = true)
            public void run(String... args) throws Exception {

                System.out.println("\n1.findAll()...");
                for (User user : repository.findAll()) {
                    System.out.println(user);
                }

                System.out.println("\n2.findByEmail(String email)...");
                for (User user : repository.findByEmail("111@leodev.ru")) {
                    System.out.println(user);
                }

                System.out.println("\n3.findByDate(Date date)...");
                for (User user : repository.findByDate(sdf.parse("2018-03-22"))) {
                    System.out.println(user);
                }

                // For Stream, need @Transactional
                System.out.println("\n4.findByEmailReturnStream(@Param(\"email\") String email)...");
                try (Stream&lt;User&gt; stream = repository.findByEmailReturnStream("333@leodev.ru")) {
                    stream.forEach(System.out::println);
                }

                System.out.println("Done!");
            }
        };


    }
}

package ru.leodev.examples.springboot.springbootjpamysql;

import org.springframework.boot.CommandLineRunner;

import org.springframework.boot.SpringApplication;

import org.springframework.boot.autoconfigure.SpringBootApplication;

import org.springframework.context.annotation.Bean;

import org.springframework.transaction.annotation.Transactional;

import ru.leodev.examples.springboot.springbootjpamysql.dao.UserRepository;

import ru.leodev.examples.springboot.springbootjpamysql.model.User;

import java.text.SimpleDateFormat;

import java.util.stream.Stream;

@SpringBootApplication

public class SpringbootJpaMysqlApplication {

private static final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

public static void main(String[] args) {

SpringApplication.run(SpringbootJpaMysqlApplication.class, args);

}

@Bean

public CommandLineRunner demo(UserRepository repository) {

return new CommandLineRunner() {

// начиная со spring boot 1.5 ридонли над методом который вызывает репозиторий нужен обязательно

// что бы не закрывалась сессия.

@Override

@Transactional(readOnly = true)

public void run(String... args) throws Exception {

System.out.println("\n1.findAll()...");

for (User user : repository.findAll()) {

System.out.println(user);

}

System.out.println("\n2.findByEmail(String email)...");

for (User user : repository.findByEmail("111@leodev.ru")) {

System.out.println(user);

}

System.out.println("\n3.findByDate(Date date)...");

for (User user : repository.findByDate(sdf.parse("2018-03-22"))) {

System.out.println(user);

}

// For Stream, need @Transactional

System.out.println("\n4.findByEmailReturnStream(@Param(\"email\") String email)...");

try (Stream<User> stream = repository.findByEmailReturnStream("333@leodev.ru")) {

stream.forEach(System.out::println);

}

System.out.println("Done!");

}

};

}

Начиная с версии Spring Boot 1.5 методы вызывающие репозитории должны быть помечены аннотацией, что бы была открыта сессия во время их вызова

@Transactional(readOnly = true)

1	@Transactional(readOnly = true)

8. Запуск приложения

2018-01-21 15:31:00 DEBUG org.hibernate.SQL - drop table if exists user
Hibernate: drop table if exists user
2018-01-21 15:31:00 DEBUG org.hibernate.SQL - create table user (id bigint not null auto_increment, created_date datetime, email varchar(255), name varchar(255), primary key (id))
Hibernate: create table user (id bigint not null auto_increment, created_date datetime, email varchar(255), name varchar(255), primary key (id))
DATASOURCE = HikariDataSource (HikariPool-1)

1.findAll()...
2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_
Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_
User{id=1, name='leodev', email='111@leodev.ru', date=2018-03-21 00:00:00.0}
User{id=2, name='alex', email='222@leodev.ru', date=2018-04-22 00:00:00.0}
User{id=3, name='kirito', email='333@leodev.ru', date=2018-05-23 00:00:00.0}

2.findByEmail(String email)...
2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?
Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?
User{id=1, name='leodev', email='111@leodev.ru', date=2018-03-21 00:00:00.0}

3.findByDate(Date date)...
2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.created_date=?
Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.created_date=?

4.findByEmailReturnStream(@Param("email") String email)...
2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?
Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?
User{id=3, name='kirito', email='333@leodev.ru', date=2018-05-23 00:00:00.0}
Done!
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 4.657 s
[INFO] Finished at: 2018-01-21T15:31:01+03:00
[INFO] Final Memory: 22M/54M
[INFO] ------------------------------------------------------------------------
2018-01-21 15:31:01 DEBUG org.hibernate.SQL - drop table if exists user
Hibernate: drop table if exists user

2018-01-21 15:31:00 DEBUG org.hibernate.SQL - drop table if exists user

Hibernate: drop table if exists user

2018-01-21 15:31:00 DEBUG org.hibernate.SQL - create table user (id bigint not null auto_increment, created_date datetime, email varchar(255), name varchar(255), primary key (id))

Hibernate: create table user (id bigint not null auto_increment, created_date datetime, email varchar(255), name varchar(255), primary key (id))

DATASOURCE = HikariDataSource (HikariPool-1)

1.findAll()...

2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_

Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_

User{id=1, name='leodev', email='111@leodev.ru', date=2018-03-21 00:00:00.0}

User{id=2, name='alex', email='222@leodev.ru', date=2018-04-22 00:00:00.0}

User{id=3, name='kirito', email='333@leodev.ru', date=2018-05-23 00:00:00.0}

2.findByEmail(String email)...

2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?

Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?

User{id=1, name='leodev', email='111@leodev.ru', date=2018-03-21 00:00:00.0}

3.findByDate(Date date)...

2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.created_date=?

Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.created_date=?

4.findByEmailReturnStream(@Param("email") String email)...

2018-01-21 15:31:01 DEBUG org.hibernate.SQL - select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?

Hibernate: select user0_.id as id1_0_, user0_.created_date as created_2_0_, user0_.email as email3_0_, user0_.name as name4_0_ from user user0_ where user0_.email=?

User{id=3, name='kirito', email='333@leodev.ru', date=2018-05-23 00:00:00.0}

Done!

[INFO] ------------------------------------------------------------------------

[INFO] BUILD SUCCESS

[INFO] ------------------------------------------------------------------------

[INFO] Total time: 4.657 s

[INFO] Finished at: 2018-01-21T15:31:01+03:00

[INFO] Final Memory: 22M/54M

[INFO] ------------------------------------------------------------------------

2018-01-21 15:31:01 DEBUG org.hibernate.SQL - drop table if exists user

Hibernate: drop table if exists user

1. Структура каталогов

2. Зависимости

3. Java Persistence API – JPA

4. Configuration + Database Initialization

5. @Repository

6. Spring Boot Starter

8. Запуск приложения

Ссылки