SpringBoot data access

1, Introduction

for the data access layer, whether SQL or NOSQL, SpringBoot adopts the method of integrating Spring Data for unified processing by default, adding a large number of automatic configurations. xxxTemplate and xxxRepository are introduced to simplify the operation of data access layer.

<dependency>  <!-- mysql-connector-java Dependency is required (when used) Mysql Database) -->
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>

2, Using JDBC

1. Add dependency

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

2. Use

application.yml (mysql higher version must take the following url parameters, specify time zone, character, SSL)

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=utf8&useUnicode=true&useSSL=false
    username: xxxxxx
    password: ******

Test code

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestDatasource {
	
	@Autowired
	DataSource datasource;
	
	@Test
	public void contextLoads() throws SQLException {
		System.out.println("Data source class"+datasource.getClass());
        //com.zaxxer.hikari.HikariDataSource SpringBoot2 default data source
        //org.apache.tomcat.jdbc.pool.DataSource SpringBoot1.5 default data source
		Connection connection = datasource.getConnection();
		System.out.println(connection);
		connection.close();
	}
}

3. Common functions

Core package: org.org springframework. boot. autoconfigure. jdbc

3.1 selecting data sources

to configure	value
spring.datasource.type	org.apache.tomcat.jdbc.pool.DataSource com.zaxxer.hikari.HikariDataSource (default) org.apache.commons.dbcp2.BasicDataSource

3.2 execute sql file at project startup

spring:
  datasource:
  	platform: mysql  # Designated platform
    initialization-mode: always  #Auto execute schema - * sql data-*. SQL, no need to
    schema:
    - classpath:sql/schema-jk.sql  # Custom sql location

Description:

Default sql storage location: classpath
SQL: schema. Is executed by default sql,schema-all.sql,data.sql,data-all.sql
Customizable sql location and name
platform can be specified:

3.3 database operation

Core class: JdbcTemplate

@Autowired
JdbcTemplate jdbcTemplate;

@ResponseBody
@GetMapping("/jdbcSelect")
public List<Map<String, Object>> jdbcSelect() {
    List<Map<String, Object>> queryForList = jdbcTemplate.queryForList("select * from t_user");
    return queryForList;
}

3, Data source Druid

1. Switch data source

Add pom dependency:

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.21</version>
</dependency>

Modify data source: application yml

spring:
	datasource:
		type: com.alibaba.druid.pool.DruidDataSource

2. Add druid configuration

spring:
  datasource:
#   Other configurations of data source
    initialSize: 5  #Number of initial connections
    minIdle: 5		#Minimum idle
    maxActive: 20	#maximum connection
    maxWait: 60000	#Maximum waiting time ms
    timeBetweenEvictionRunsMillis: 60000 #Running idle collector interval ms
    minEvictableIdleTimeMillis: 300000	#The connection in the pool can be recycled after being idle for 5m
    validationQuery: SELECT 1 FROM DUAL #Verify the sql used
    testWhileIdle: true		#Indicates whether the connection is verified by the idle connection collector (if any) If the test fails, the connection will be removed from the pool  
    testOnBorrow: false		#Whether to verify the availability when getting an instance
    testOnReturn: false		#Whether to validate in advance when return ing to pool
    poolPreparedStatements: false #Cache cursor
#   Configure the filters for monitoring statistics interception. After removal, the sql in the monitoring interface cannot be counted, 'wall' is used for firewall  
    filters: stat,wall,slf4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true  
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

java code

@Configuration
public class ApplicationConfiguration {
	
	@Bean
	@ConfigurationProperties(prefix = "spring.datasource")
	public DruidDataSource getDataSource() {
		return new DruidDataSource();
	}
}

druid configuration resolution

to configure	explain
initialSize	Number of initial connections
minIdle	Minimum idle number
maxActive	maximum connection
maxWait	Configure the timeout time for getting connections
timeBetweenEvictionRunsMillis	How often is the configuration interval detected? Idle connections that need to be closed are detected in milliseconds
minEvictableIdleTimeMillis	Configure the minimum lifetime of a connection in the pool, in milliseconds
validationQuery	Verify the sql used
testWhileIdle	Indicates whether the connection is verified by the idle connection collector (if any) If the detection fails, the connection will be removed from the pool.
testOnBorrow	Whether to verify the availability when getting an instance
testOnReturn	Whether to validate in advance when return ing to pool
poolPreparedStatements	Open PSCache
maxPoolPreparedStatementPerConnectionSize	Specifies the size of the PSCache on each connection
filters	Configure the filters for monitoring statistics interception. After removing the filters, the sql in the monitoring interface cannot be counted, 'wall' is used for firewall
connectionProperties	Open the mergeSql function through the connectProperties property; Slow SQL record
useGlobalDataSourceStat	Merge monitoring data of multiple DruidDataSource

3. Configuration monitoring

//Configure Druid monitoring
//1. Configure the Servlet in the management background 
@Bean
public ServletRegistrationBean<StatViewServlet> statViewServlet(){
    ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<StatViewServlet>(new StatViewServlet(), "/druid/*");
    /* The value can be stored in the initial servlet, and the key is fixed. Class ResourceServlet can be viewed */
    Map<String, String> initParameters = new HashMap<>();
    initParameters.put("loginUsername", "jinkun");
    initParameters.put("loginPassword", "123456");
    initParameters.put("allow","");//Allow all by default
    initParameters.put("deny","192.168.56.1"); //Point to the machine that does not have access, and the value is ip address
    servletRegistrationBean.setInitParameters(initParameters);
    return servletRegistrationBean;
}

//2. Configure a web monitoring Filter
@Bean
public FilterRegistrationBean<WebStatFilter> webStatFilter(){
    FilterRegistrationBean<WebStatFilter> filterRegistrationBean = new FilterRegistrationBean<>();
    filterRegistrationBean.setFilter(new WebStatFilter());
    filterRegistrationBean.setUrlPatterns(Arrays.asList("/*")); //Filter all
    Map<String, String> initParameters = new HashMap<>();
    initParameters.put("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); //Exclude unfiltered	
    filterRegistrationBean.setInitParameters(initParameters);
    return filterRegistrationBean;
}

Monitoring page:

4, Integrate Mybatis

Help documentation: https://mybatis.org/mybatis-3/zh/

1. Introduce dependency

mybatis is not an official dependency of Spring Boot, so you need to add a version number.

<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.1</version>
</dependency>

2. Annotated version

2.1 notes

annotation	explain
@Mapper	It is marked in front of the class to indicate that this class is a class that operates the database
@MapperScan	@Mapper annotates each class and @ MapperScan(value = "package name") is placed before the startup class, so the classes under this package do not need @ mapper annotation.
@Insert	Insert statement
@Select	Query statement
@Update	UPDATE statement
@Delete	Delete statement
@Param	Method Parameter annotation, when the field name in the database is inconsistent with the field name of java bean
@Results and @ Result	Convert the values queried in the database into specific fields
#{} and ${}	#It is used to preprocess SQL and $is used to splice SQL1
@InsertProvider	Dynamically generate SQL, type: the class that dynamically generates SQL, method: the specific method name in the class
@SelectProvider	Dynamically generate SQL, type: the class that dynamically generates SQL, method: the specific method name in the class
@UpdateProvider	Dynamically generate SQL, type: the class that dynamically generates SQL, method: the specific method name in the class
@DeleteProvider	Dynamically generate SQL, type: the class that dynamically generates SQL, method: the specific method name in the class
@Options	useGeneratedKeys=true ; keyProperty = "id" select whether to auto increment and auto increment columns
xml configuration file	Core Bean: org apache. ibatis. session. Configuration. ConfigurationCustomizer

@Insert, @ Select, @ Update, @ Delete, @ Param examples

@Select("SELECT * FROM users WHERE user_sex = #{user_sex}")
List<User> getListByUserSex(@Param("user_sex") String userSex);

@Results and @ Result examples:

@Select("SELECT * FROM users")
@Results({
    @Result(property = "userSex",  column = "user_sex", javaType = UserSexEnum.class),
    @Result(property = "nickName", column = "nick_name")
})
List<UserEntity> getAll();

2.2 sql Statement Builder

sql Statement Builder is aimed at the use of dynamic sql: if, choose, foreach and trim in annotation mode

the sql Statement Builder will create a method to return sql and use it in combination with @ xxxProvider.

method	explain
SELECT(String) SELECT(String ...)	Start or insert into the SELECT clause. It can be called multiple times, and the parameter will be added to the SELECT clause. Parameters usually use comma separated column names and alias lists, but they can also be any type accepted by the database driver.
SELECT_DISTINCT(String) SELECT_DISTINCT(String...)	Start or insert into the SELECT clause, or insert the DISTINCT keyword into the generated query statement. It can be called multiple times, and the parameter will be added to the SELECT clause. Parameters usually use comma separated column names and alias lists, but they can also be any type accepted by the database driver.
FROM(String) FROM(String...)	Start or insert into the FROM clause. Can be called multiple times, and parameters are added to the FROM clause. Parameters are usually table names or aliases, or any type accepted by the database driver.
JOIN(String) JOIN(String...) INNER_JOIN(String) INNER_JOIN(String...) LEFT_OUTER_JOIN(String) LEFT_OUTER_JOIN(String...) RIGHT_OUTER_JOIN(String) RIGHT_OUTER_JOIN(String...)	Add a new join clause of the appropriate type based on the called method. The parameter can contain a standard join composed of column names and join on conditions.
WHERE(String) WHERE(String...)	Insert a new WHERE clause condition, linked by AND. It can be called many times, AND the new condition is linked by AND each time. Use OR() to separate OR.
OR()	Use OR to separate the current WHERE clause conditions. Can be called multiple times, but call multiple times in a row OR generate unstable SQL.
AND()	Use AND to separate the current WHERE clause condition. Can be called multiple times, but call multiple times in a row or generate unstable SQL. Because both WHERE AND HAVING will automatically link AND, this is a very rare method, which is only used for integrity.
GROUP_BY(String) GROUP_BY(String...)	Insert a new GROUP BY clause element, joined by commas. Can be called multiple times, each time with a comma connecting the new condition.
HAVING(String) HAVING(String...)	Insert a new HAVING clause condition. Connected by AND. Can be called multiple times, each time by AND to connect the new condition. Use OR() to separate OR
ORDER_BY(String) ORDER_BY(String...)	Insert new ORDER BY clause elements, joined by commas. Can be called multiple times, each time a new condition is concatenated by a comma.
LIMIT(String) LIMIT(int)	This method is valid when used with SELECT(), UPDATE(), and DELETE(). This method is designed to be used with OFFSET() when using SELECT(). (provided from 3.5.2)
OFFSET(String) OFFSET(long)	This method is valid when used with SELECT(). This method is used with LIMIT(). (provided from 3.5.2)
OFFSET_ROWS(String) OFFSET_ROWS(long)	Append an OFFSET n ROWS clause. This method is valid when used with SELECT(). This method is designed with fetch_ FIRST_ ROWS_ Use with only(). (provided from 3.5.2)
FETCH_FIRST_ROWS_ONLY(String) FETCH_FIRST_ROWS_ONLY(int)	Append a FETCH FIRST n ROWS ONLY clause. This method is valid when used with SELECT(). This method is designed to work with offset_ Use with rows(). (provided from 3.5.2)
DELETE_FROM(String)	Start a delete statement and specify the table name from which you want to delete. Usually it will be followed by WHERE statement!
INSERT_INTO(String)	Start an insert statement and specify the name of the table into which you want to insert data. One or more values () or into will follow_ COLUMNS() and INTO_ VALUES().
SET(String) SET(String...)	Insert the update statement into the "set" list
UPDATE(String)	Start an update statement and specify the indication that needs to be updated. It will be followed by one or more sets (), and usually there will be a WHERE().
VALUES(String, String)	Insert into the insert statement. The first parameter is the name of the column to be inserted, and the second parameter is the value of the column.
INTO_COLUMNS(String...)	Append a column phrase to the insert statement. This should be called together to_ VALUES ().
INTO_VALUES(String...)	Append a value phrase to the insert statement. This should be called with_ COLUMNS ().
ADD_ROW()	Add new rows for bulk insert. (provided from 3.5.2)

@InsertProvider, @ SelectProvider, @ UpdateProvider, @ DeleteProvider examples

//In interface
@SelectProvider(type=DepartmentMapperClass.class ,method = "sql")
public Department getDeptbyId(Integer id);
//In class
public String sql() {
    return "select * from department where id = #{id}";
}

sql Statement Builder example: don't care about accidental and, where

String sql = "SELECT P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME, "
"P.LAST_NAME,P.CREATED_ON, P.UPDATED_ON " +
"FROM PERSON P, ACCOUNT A " +
"INNER JOIN DEPARTMENT D on D.ID = P.DEPARTMENT_ID " +
"INNER JOIN COMPANY C on D.COMPANY_ID = C.ID " +
"WHERE (P.ID = A.ID AND P.FIRST_NAME like ?) " +
"OR (P.LAST_NAME like ?) " +
"GROUP BY P.ID " +
"HAVING (P.LAST_NAME like ?) " +
"OR (P.FIRST_NAME like ?) " +
"ORDER BY P.ID, P.FULL_NAME";

amount to:

private String selectPersonSql() {
  return new SQL() {{
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
    SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
    FROM("PERSON P");
    FROM("ACCOUNT A");
    INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
    INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
    WHERE("P.ID = A.ID");
    WHERE("P.FIRST_NAME like ?");
    OR();
    WHERE("P.LAST_NAME like ?");
    GROUP_BY("P.ID");
    HAVING("P.LAST_NAME like ?");
    OR();
    HAVING("P.FIRST_NAME like ?");
    ORDER_BY("P.ID");
    ORDER_BY("P.FULL_NAME");
  }}.toString();
}

2.3 xml version

Mybatis global configuration:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<!-- Global configuration: https://mybatis.org/mybatis-3/zh/configuration.html -->
</configuration>

MySQL configuration sql

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.mybatis.example.BlogMapper">
  <select id="selectBlog" resultType="Blog" parameterType="int">
    select * from Blog where id = #{id}
  </select>
  <!-- Related documents: https://mybatis.org/mybatis-3/zh/sqlmap-xml.html -->
</mapper>

Write relevant interface classes:

namespace: package name Class name

id: method name, resultType: return type, parameterType: parameter type

application.yml

mybatis:
  config-location: classpath:mybatis/mybatis-config.xml  #Global profile
  mapper-locations:			#xml Mapping File
  - classpath:mybatis/mapper/*.xml

5, SpringData JPA

1. Introduction

the purpose of Spring Data project is to simplify the construction of data access technology based on Spring framework applications. Including non relational database, map reduce framework, cloud data service, etc; It also includes access to relational databases.

spring data provides us with a unified API to operate the data access layer; This is mainly implemented by the Spring Data Commons project. Spring Data Commons allows us to use relational or non relational data access technologies based on the unified standards provided by spring. The standard includes CRUD, paging and sorting operations.

2. Advantages

Unified Repository interface

org.springframework.data.repository.Repository<T, ID>  //Unified interface
org.springframework.data.repository.CrudRepository<T, ID> //CRUD based operation
org.springframework.data.repository.PagingAndSortingRepository<T, ID> //Paging and sorting, also CRUD
org.springframework.data.jpa.repository.JpaRepository<T, ID> //There are CRUD, paging and sorting

Provide data access template class xxtemplate; Such as MongoTemplate and RedisTemplate*
The core is that you can complete the operation without writing sql.

3. Introduce dependency

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

application.yml

spring:
  jpa:
    hibernate:
      ddl-auto: update #Update or create data table structure
    show-sql: true  #The console displays sql

4. Build entity classes (JavaBean s)

This class automatically creates the table corresponding to this entity class in the database through some annotations.

annotation	explain
@Entity	Before labeling a class, indicate that the class should be mapped to the database
@Table	Class, attribute: name: points to the table
@Column	Attribute annotation, attribute: columnDefinition: database type to which the field belongs, name: column name lenght: Varchar length, precision=8, scale=2: floating point precision, range, Whether unique=true, nullable=false is unique and empty.
@Temporal	Calendar mapping date; Date mapping time; Date mapping timestamp
@Transient	Do not save properties to the database
@Id	Primary key column
@GeneratedValue	Automatic generation, strategy: generation strategy: IDENTITY
@SequenceGenerator	Create a sequence for use with @ GeneratedValue
@Lob	Java types are byte[] Byte [] and Serializable types that map to blobs, while char[] Character [] and String objects map to clobs
@Basic	Attribute: fetch: value lazy / Eagle delayed or eager loading; For @ Lob
Help documentation	https://www.w3cschool.cn/java/jpa-manytomany-map.html

Example:

Date type: supported time types include: Java sql. Date,java.sql.Time,java.sql.Timestamp,java.util.Date,java.util.Calendar.

@Column(name = "START_DATE", columnDefinition = "DATE DEFAULT CURRENT_DATE")
private java.sql.Date startDate;

@Temporal

@Temporal(TemporalType.DATE) //Calendar mapping date
private java.util.Calendar dob;
@Temporal(TemporalType.TIME) //Date mapping time
private java.util.Date dob;
@Temporal(TemporalType.TIMESTAMP) //Date mapping timestamp
private java.util.Date dob;

@GeneratedValue

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@SequenceGenerator

@SequenceGenerator(name="Emp_Gen", sequenceName="Emp_Seq")
@Id @GeneratedValue(generator="Emp_Gen")
private int id;

5. Data operation

write interface to inherit jparepository < entity class, primary key >

There are three ways to write sql in the interface: 1. Method in JpaRepository; 2. Custom method; 3. @ Query annotation

For the JpaRepository method, please check the source code
Custom method - SpringData JPA rule

key word	sample	JPQL fragment
And	findByLastnameAndFirstname	... where x.lastname = ?1 and x.firstname = ?2
Or	findByLastnameOrFirstname	... where x.lastname = ?1 or x.firstname = ?2
Is， Equals	findByFirstname，findByFirstnameIs，findByFirstnameEquals	... where x.firstname = ?1
Between	findByStartDateBetween	... where x.startDate between ?1 and ?2
LessThan	findByAgeLessThan	... where x.age < ?1
LessThanEqual	findByAgeLessThanEqual	... where x.age <= ?1
GreaterThan	findByAgeGreaterThan	... where x.age > ?1
GreaterThanEqual	findByAgeGreaterThanEqual	... where x.age >= ?1
After	findByStartDateAfter	... where x.startDate > ?1
Before	findByStartDateBefore	... where x.startDate < ?1
IsNull， Null	findByAge(Is)Null	... where x.age is null
IsNotNull， NotNull	findByAge(Is)NotNull	... where x.age not null
Like	findByFirstnameLike	... where x.firstname like ?1
NotLike	findByFirstnameNotLike	... where x.firstname not like ?1
StartingWith	findByFirstnameStartingWith	… where x.firstname like ?1 (parameter attached%)
EndingWith	findByFirstnameEndingWith	… where x.firstname like ?1 (prefix% for parameters)
Containing	findByFirstnameContaining	… where x.firstname like ?1 (parameter bound in%)
OrderBy	findByAgeOrderByLastnameDesc	... where x.age = ?1 order by x.lastname desc
Not	findByLastnameNot	... where x.lastname <> ?1
In	findByAgeIn(Collection ages)	... where x.age in ?1
NotIn	findByAgeNotIn(Collection ages)	... where x.age not in ?1
True	findByActiveTrue()	... where x.active = true
False	findByActiveFalse()	... where x.active = false
IgnoreCase	findByFirstnameIgnoreCase	... where UPPER(x.firstame) = UPPER(?1)

@Query annotation

//@Modifying
@Query("select u from User u where u.emailAddress = ?1")
User findByEmailAddress(String emailAddress);
//? 1 ? 2 indicates the position of the parameter. The attribute nativeQuery=true/false in @ Query indicates whether the field in the statement is a field of data (true) or an attribute in the entity class
//The modification statement needs to be annotated with @ Modifying

specification query to deal with dynamic sql construction. Implement interface jpaspecification executor

The method of JpaSpecificationExecutor needs to pass in the Specification object to build dynamic sql. The core method is as follows:

public interface Specification<T> extends Serializable{
    @Nullable
	Predicate toPredicate(Root<T> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder);
}
//root: used to get database fields
//query: used to combine conditions and return the Specification object
//criteriaBuilder: used to build relationships among multiple conditions: like, and, or