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