Spring Boot persistence layer knowledge comb

Review the concepts and usage of common persistence layer techniques in SpringBoot.

1. JDBC

JDBC (Java Data Base Connectivity) is the Java database connection, the official interpretation of it is the Java programming language and a wide range of database independent connection standard Java API, basically said JDBC is a specification, it provides an interface, a complete set of, Allows easy access to the underlying database. Simply said it is JAVA and database connection bridge or plug-in, with JAVA code can operate the database to add, delete, change, stored procedures, transactions and so on.

Common interfaces:

JAVA API: provides administrative links to JDBC;
JAVA Driver API: supports JDBC management of Driver connections.
DriverManager: This class manages the list of database drivers to see if the loaded drivers conform to the JAVA Driver API specifications.
Connection: All communication with the database is through a unique Connection object.
Statement: Converts the created SQL object to a database.
ResultSet: This is an iterator used to retrieve query data.

Use process:

2. JPA

JPA (Java Persistence API) is a JDK 5.0 annotation or XML mapping describing object-relational tables and persisting run-time entity objects to a database. Is a standard interface.

3. The ORM framework

The Object Relational Mapping (ORM) framework uses metadata to describe the details of object-relational Mapping. Metadata is generally in XML format and stored in a special object-relational Mapping file. Simply understood as a framework format

3.1 MyBatis

The biggest feature is the freedom to customize SQL statements in Mapper.

3.1.1 Basic Configuration and Usage

Maven configuration:

<dependency>	           	 
  <groupId>org.mybatis.spring.boot</groupId>	
  <artifactId>mybatis-spring-boot-starter</artifactId>	
  <version>2.0.1</version>	
</dependency>
Copy the code

YAML configuration:

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource Druid connection pool
    driver-class-name: com.mysql.jdbc.Driver MySQL > alter database
    url: jdbc:mysql://localhost:3306/test? serverTimezone=GMT%2B8&useSSL=true
    username: root
    password: 123456
        
mybatis:
  mapperLocations: classpath:mapper/*Mapper.xml Mapper XML file address
  type-aliases-package: com.example.entity Entity file address
Copy the code

The entity class:

@Data
public class User {
    private Integer id;
    private String userName;
    private String passWord;
    private String realName;
}
Copy the code

mapper xml:


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.mapper.UserMapper">
 
    <select id="select" resultType="com.example.entity.User">
        select * from user where id = #{id}
    </select>
 
</mapper>
Copy the code

Mapper interfaces:

@Repository
public interface UserMapper {
    User select(int id);
}
Copy the code

Service types:

@Service
public class UserService {
    @Autowired
    UserMapper userMapper;
    public User Sel(int id){
        returnuserMapper.Sel(id); }}Copy the code

The controller class:

@RestController
@RequestMapping("/testBoot")
public class UserController {
 
    @Autowired
    private UserService userService;
 
    @RequestMapping("getUser/{id}")
    public String GetUser(@PathVariable int id){
        returnuserService.Sel(id).toString(); }}Copy the code

Application class:

@MapperScan("com.example.mapper") // Scan mapper
@SpringBootApplication
public class DemoApplication {
 
	public static void main(String[] args) { SpringApplication.run(DemoApplication.class, args); }}Copy the code

3.1.2 resultMap

1) Basic use

The entity class:

@Data
public class product  {
    private Long id;
    private String name;
    private Long categoryId;
}
Copy the code

Corresponding mapper:

<resultMap id="BaseResultMap" type="com.exemple.entity.Product">
    <id column="ID" jdbcType="BIGINT" property="id" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="CATEGORY_ID" jdbcType="BIGINT" property="categoryId" />
</resultMap> 
Copy the code

2) Add a collection of attributes

The entity class:

@Data
public class Product  {
	private Long id;
	private String name;
	private Long categoryId;
	private List<Attribute> attributes;
}
Copy the code

There are two ways to add a collection to a resultMap:

Nested results

mapper:

<resultMap id="BasePlusResultMap" type="com.exemple.entity.Product">
    <id column="ID" jdbcType="BIGINT" property="id" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="CATEGORY_ID" jdbcType="BIGINT" property="categoryId" />
    <collection property="attributes" ofType="com.exemple.entity.Attribute" > 
        <id column="AttributeID" jdbcType="BIGINT" property="id" />
        <result column="attribute_NAME" jdbcType="VARCHAR" property="attributeName" />
    </collection>
</resultMap>

<select id="getById"  resultMap="basePlusResultMap">
    select s.ID,s.NAME,s.CATEGORY_ID,a.ID,a.ATTRIBUTE_NAME
    from t_shop_sku s,t_shop_attribute a 
    where s.ID =a.ID and s.ID = #{id,jdbcType =BIGINT};
</select>
Copy the code

Associated nested queries (add select attribute to collection)

product mapper:

<resultMap id="ProductResultMap" type="com.exemple.entity.TShopSku">
    <id column="ID" jdbcType="BIGINT" property="id" />
    <result column="NAME" jdbcType="VARCHAR" property="skuName" />
    <result column="CATEGORY_ID" jdbcType="BIGINT" property="categoryId" />
    <collection column="{skuId=ID}" property="attributes" ofType="com.exemple.entity.Attribute" select="getAttribute" > 
    </collection>
</resultMap>

<select id="getById"  resultMap="ProductResultMap">
    select s.ID,s.SKU_NAME,s.CATEGORY_ID
    from t_shop_sku s
    where  s.ID = #{id,jdbcType =BIGINT};
</select>
Copy the code

attribute mapper

<resultMap id="AttributeResultMap" type="com.exemple.entity.Attribute">
    <id column="ID" jdbcType="BIGINT" property="id" />
    <result column="ATTRIBUTE_NAME" jdbcType="VARCHAR" property="attributeName" />
</resultMap>

<select id="getAttribute"  resultMap="AttributeResultMap">
    select a.ID,s.ATTRIBUTE_NAME
    from t_shop_attribute a
    where  a.ID = #{id,jdbcType =BIGINT};
</select>
Copy the code

3.2 the Spring Data JPA

3.2.1 Basic Configuration and Usage

maven:

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

YAML:

spring:
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource Druid connection pool
    driver-class-name: com.mysql.jdbc.Driver MySQL > alter database
    url: jdbc:mysql://localhost:3306/test? serverTimezone=GMT%2B8&useSSL=true
    username: root
    password: 123456
  jpa:
    database: MySQL
    database-platform: org.hibernate.dialect.MySQL5InnoDBDialect
    show-sql: true
    hibernate:
      ddl-auto: update
Copy the code

Ddl-auto:

Create: Each time the program runs, the table is recreated, so data is lost
Create-drop: The table structure is created each time the program is run, and then the table is cleared when the program ends
Upadte: Every time the program runs, the table will be created when there is no table. If the object changes, the table structure will be updated. The original data will not be emptied, only updated.
Validate: The program verifies whether the data and database field types are the same. If the fields are different, an error is reported
One: disables DDL processing

The entity class:

@Entity
@Table(name = "user")
@Data
public class User {

    @Id
    @GenericGenerator(name = "idGenerator", strategy = "uuid") // The primary key generation policy is uuid
    @GeneratedValue(generator = "idGenerator")
    private String id;

    @Column(name = "username", unique = true, nullable = false, length = 64)
    private String username;

    @Column(name = "password", nullable = false, length = 64)
    private String password;

    @Column(name = "email", length = 64)
    private String email;

}
Copy the code

The DAO:

public interface UserRepository extends JpaRepository<User.String> {}Copy the code

Service:

public class UserService {

    @Autowired
    private UserRepository userRepository;

    public User saveUser(User user) {
        return userRepository.save(user);
    }

    public void deleteUser(String userId) {
        userRepository.deleteById(userId);
    }

    public User updateUser(String userId, User user) {
        user.setId(userId);
        return userRepository.save(user);
    }

    public User getUserInfo(String userId) {
        return userRepository.findById(userId);
    }

    public Page<User> pageQuery(Integer pageNum, Integer pageSize) {
        return userRepository.findAll(PageRequest.of(pageNum - 1, pageSize)); }}Copy the code

Controller:

@RestController
@RequestMapping("/users")
public class UserController {

    @Autowired
    private UserService userService;

    @PostMapping()
    public User saveUser(@RequestBody User user) {
        return userService.save(user);
    }

    @DeleteMapping("/{id}")
    public void deleteUser(@PathVariable("id") String userId) {
        userService.deleteById(userId);
    }

    @PutMapping("/{id}")
    public User updateUser(@PathVariable("id") String userId, @RequestBody User user) {
        user.setId(userId);
        return userService.save(user);
    }

    @GetMapping("/{id}")
    public User getUserInfo(@PathVariable("id") String userId) {
        return userService.getUserInfo(userId);
    }

    @GetMapping("/list")
    public Page<User> pageQuery(@RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum,
                                @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize) {
        returnuserService.pageQuery(pageNum, pageSize); }}Copy the code

3.3 Differences and connections among Hibernate, JPA and Spring Data JPA

Hibernate is a persistence implementation technology and an ORM framework.

JPA is the standard for persistence, one is the implementation, the other is the interface protocol;

Spring Data JPA is a higher level encapsulation implementation based on Hibernate.

4. Database connection pool

4.1 Druid

Ali open source project Druid, its own monitoring interface.

maven:

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.1.9</version>
</dependency>
Copy the code

Application configuration:

spring:
  datasource:
    druid:
      url: JDBC: mysql: / / 127.0.0.1:3306 / tmall_springboot? characterEncoding=UTF-8
      username: root
      password: 123456
      driver-class-name: com.mysql.jdbc.Driver

      Connection pool configuration information
      initialSize: 5
      minIdle: 5
      maxActive: 20
      Set the connection wait timeout
      maxWait: 60000
      Configure how often to detect idle connections that need to be closed, in milliseconds
      timeBetweenEvictionRunsMillis: 60000
      Set the minimum time for a connection to live in the pool in milliseconds
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: false
      testOnReturn: false
      Turn on PSCache and specify the size of PSCache on each connection
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      useGlobalDataSourceStat: true
      Enable mergeSql via connectProperties; Slow SQL record
      connectionProperties: druid.stat.mergeSql=true; druid.stat.slowSqlMillis=5000
      # Configure the filters for monitoring statistics interception. After removing the filters, the MONITORING interface SQL cannot be counted. 'wall' is used for the firewall
      filters: stat,wall

      # configuration monitoring properties: the druid - starter: com. Alibaba. The druid. Spring. The boot. Autoconfigure. Stat package under the logic configuration
      web-stat-filter: # WebStatFilter configuration,
        enabled: true The default value is false, indicating that the WebStatFilter configuration is not used
        url-pattern: / * Block all requests under this heading
        exclusions: /druid/*,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico  Allow these requests
        session-stat-enable: true
        principal-session-name: session_name
        principal-cookie-name: cookie_name

      # Monitor interface StatViewServlet configuration
      stat-view-servlet: 
        enabled: true The default value is false, indicating that the StatViewServlet configuration is not used
        url-pattern: /druid/*  Configure the DruidStatViewServlet access address. Access address of the background monitoring page
        reset-enable: false # Disable the "reset" function on HTML pages, which will clear all the monitored data. It is generally not used
        login-username: admin # Monitor the login user name of the page
        login-password: 123456 # Monitor page login password
        allow:   #IP whitelist (not configured or empty, all access allowed) Druid allows all users to access the druid background.
        deny:  #IP blacklist (deny takes precedence over allow when common) Do not allow anyone to access the Druid background,
      # Druid Github Wiki for Spring monitoring configuration
      aop-patterns: com.wtychn.tmall.service.*
Copy the code

Database of 5.

MySQL 5.1

Maven configuration:

<dependency>	
    <groupId>mysql</groupId>	
    <artifactId>mysql-connector-java</artifactId>	
</dependency>
Copy the code

Spring Boot persistence layer knowledge comb

1. JDBC

2. JPA

3. The ORM framework

3.1 MyBatis

3.1.1 Basic Configuration and Usage

3.1.2 resultMap

1) Basic use

2) Add a collection of attributes

3.2 the Spring Data JPA

3.2.1 Basic Configuration and Usage

3.3 Differences and connections among Hibernate, JPA and Spring Data JPA

4. Database connection pool

4.1 Druid

Database of 5.

MySQL 5.1

Related Posts

go: go.mod file not found in current directory or any parent directory; see ‘go

PyTorch 1.3 release: Mobile deployment, Colab cloud TPU support, ali Cloud also available

Explore Scala’s non-strict evaluation and streaming data structures