1, Improvement from JDBC to Mybatis
(1) What is Mybatis?
MyBatis is an excellent persistence layer framework that supports customized SQL, stored procedures, and advanced mapping. MyBatis avoids almost all JDBC code and manually setting parameters and getting result sets. MyBatis can use simple XML or annotations for configuration and native maps to Map interfaces and Java POJOs(Plain Old Java Objects) into records in the database.
Mybatis architecture
(2) Steps of JDBC programming
Load database driver
Create and get database links
Create a jdbc statement object
Set sql statement
Setting parameters in sql statements (using preparedStatement)
Execute sql through statement and get results
Parse the sql execution results
Release resources (resultSet, preparedstatement, connection)
(3) Improvement from JDBC to Mybatis
Let's talk about the problems one by one, and then talk about the improvement process one by one.
1. Problem description I:
The frequent creation and release of database links cause a waste of system resources, which affects the system performance
solve the problem:
We can use database connection pool to solve the problem of resource waste. Through the connection pool, you can repeatedly use the established connections to access the database. Reduce the opening and closing time of the connection.
2. Problem description II:
SQL statements are hard coded in the code, which makes the code difficult to maintain. The actual application of SQL may change greatly. SQL changes need to change the java code.
solve the problem:
Mybatis writes SQL statements in the configuration file, and uses xml or annotations to execute various statements (statements
preparedStatemnt and CallableStatement) are configured, and the final executed SQL statement is generated by mapping java objects and SQL in the statement. Finally, the MySQL framework executes SQL, maps the results into java objects and returns them. In this way, when you need to change SQL, you only need to change the configuration file. (without affecting the interface)
3. Problem description III:
There is hard coding when using preparedStatement to transfer parameters to occupancy symbols, because the where conditions of sql statements are not necessarily, there may be more or less, and the code must be modified when modifying sql, so the system is not easy to maintain.
solve the problem:
Ibid., configuration file.
4. Problem description IV:
There is hard coding (query column name) for result set parsing. The change of sql leads to the change of parsing code, which is difficult to maintain. It is more convenient to parse if the database records can be encapsulated into pojo objects.
solve the problem:
The Mapped Statement defines the output results of sql execution, including HashMap, basic type and pojo. The Executor uses the
The Mapped Statement maps the output result to the java object after executing sql. The mapping process of the output result is equivalent to the parsing process of the result in jdbc programming.
(4) Mybatis execution process
1.mybatis configures SqlMapConfig.xml. As the global configuration file of mybatis, this file configures the running environment and other information of mybatis.
The mapper.xml file is the sql mapping file, in which the sql statements for operating the database are configured. This file needs to be loaded in SqlMapConfig.xml.
2. Construct SqlSessionFactory, i.e. session factory, through configuration information such as mybatis environment
3. sqlSession is created by the session factory, that is, the session. The database operation needs to be carried out through sqlSession.
4. The bottom layer of mybatis customizes the operation database of the Executor interface. The Executor interface has two implementations, one is the basic Executor and the other is the cache Executor.
5. The mapped statement is also an underlying encapsulation object of mybatis, which encapsulates the configuration information and sql mapping information of mybatis.
In the mapper.xml file, an sql corresponds to a mapped statement object, and the id of the sql is the id of the mapped statement.
6. The Mapped Statement defines the input parameters of sql execution, including HashMap, basic type and pojo. The Executor maps the input java objects into sql through the Mapped Statement before executing sql. The input parameter mapping is to set the parameters of preparedStatement in jdbc programming.
7. The Mapped Statement defines the output results of sql execution, including HashMap, basic type and pojo. The Executor maps the output results to java objects after executing sql through the Mapped Statement. The output result mapping process is equivalent to the result parsing process in jdbc programming.
2, Example comparison of JDBC transformation by Mybatis under IDEA environment
(1) JDBC operation database
1.Navicat data sheet
(1) Install jdk and configure environment variables. (not described here)
(2) Download MySQL database driver. Download link: https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.12 After downloading, it is stored in the bin directory of JRE.
3. Using navicat, select database test1 and create a new query
(4) Create a new user table, enter the code, and click Run
create table users( id int primary key auto_increment, name varchar(40), password varchar(40), email varchar(60), birthday date )character set utf8 collate utf8_general_ci;
(5) Insert data
insert into users(name,password,email,birthday) values('zs','123456','zs@sina.com','1999-12-04');
insert into users(name,password,email,birthday) values('lisi','123456','lisi@sina.com','2001-12-04');
insert into users(name,password,email,birthday) values('wangwu','123456','wangwu@sina.com','2003-12-04');
2.IDEA creation project
1. Select Java for file new projest
2. Create a new package class, SRC new Java class, named DatabaseLink
DatebaseLnk code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DatebaseLink {
static final String driverName="org.gjt.mm.mysql.Driver";
static final String dbUrl="jdbc:mysql://localhost:3306/test";
static final String userName="root";
static final String password="xyp20000921";
public static void main(String[] args) {
// TODO Auto-generated method stub
// MySQL 8.0 and below - JDBC driver name and database URL
Connection conn = null;
Statement stmt = null;
try{
// Register JDBC Driver
Class.forName(driverName);
// Open link
System.out.println("Connect to database...");
conn = DriverManager.getConnection(dbUrl,userName,password);
// Execute query
System.out.println(" instantiation Statement object...");
stmt = (Statement) conn.createStatement();
String sql;
sql = "SELECT id, name, password, email FROM users";
ResultSet rs = stmt.executeQuery(sql);
// Expand the result set database
while(rs.next()){
// Retrieve by field
int id = rs.getInt("id");
String name = rs.getString("name");
String password = rs.getString("password");
String email = rs.getString("email");
// output data
System.out.print("ID: " + id);
System.out.print(", full name: " + name);
System.out.print(", password: " +password);
System.out.print(", mailbox: " +email);
System.out.print("\n");
}
// Close when finished
rs.close();
stmt.close();
conn.close();
}catch(SQLException se){
// Handling JDBC errors
se.printStackTrace();
}catch(Exception e){
// Handling Class.forName error
e.printStackTrace();
}finally{
// close resource
try{
if(stmt!=null) stmt.close();
}catch(SQLException se2){
}// Don't do anything?
try{
if(conn!=null) conn.close();
}catch(SQLException se){
se.printStackTrace();
}
}
System.out.println("Goodbye!");
}
}
3. Introduce JDBC jar package
(1)File → Project Structure, Moudles → Dependencies → plus sign on the right
2. Operation results
(2) MyBatis application
1. Create project
(1) Create a new project with IDEA, new peoject
2.Mysql create table (Navicat)
3. Configuration file
(1) The dependency selected in the previous step has been automatically added to our file in pom.xml file
pom.xml file
<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.5</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.example</groupId> <artifactId>databasedemo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>databasedemo</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jdbc</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.2.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project>
(2)application.properties configuration
server.port=8080 spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver spring.datasource.url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC spring.datasource.username=root spring.datasource.password=wqs123 mybatis.mapper-locations=classpath:mapper/*Mapper.xml
4. Code part
(1) The project SRC main Java com creates packages: controller, entity, mapper and service to implement the control layer, entity layer, mapping layer and business layer
(2) Create a mapper package under SRC main resources to store the * Mapper.xml file:
(3) Create Mapper mapping operation StudentMapper class:
package com.example.datebasedemo.mapper;
import com.example.datebasedemo.entity.Student;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface StudentMapper {
public List<Student> findAllStudent();
List<Student> findStudentByno(int no);
}
(4) Create the StudentMapper.xml file corresponding to Mapper mapping:
<?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="com.example.databasedemo.mapper.StudentMapper">
<resultMap id="result" type="com.example.databasedemo.entity.Student">
<result column="no" jdbcType="INTEGER" property="no" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="age" jdbcType="INTEGER" property="age" />
</resultMap>
<select id="findAllStudent" resultType="com.example.databasedemo.entity.Student">
select * from student;
</select>
<select id="findStudentByno" resultType="com.example.databasedemo.entity.Student">
select * from student where no=#{no};
</select>
</mapper(5) Create service business StudentService class:
package com.example.datebasedemo.service;
import com.example.datebasedemo.entity.Student;
import com.example.datebasedemo.mapper.StudentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class StudentService {
@Autowired(required = false)
public StudentMapper StudentMapper;
public List<Student> findAllStudent() {
return StudentMapper.findAllStudent();
}
public List<Student> findStudentByno(int no) {
return StudentMapper.findStudentByno(no);
}
}
(6) Create the controller control layer UserController class:
package com.example.datebasedemo.controller;
import com.example.datebasedemo.entity.Student;
import com.example.datebasedemo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping("/Student")
class UserController {
@Autowired
private StudentService StudentService;
@RequestMapping("/getAllStudent")
public List<Student> findAll(){
return StudentService.findAllStudent();
}
@RequestMapping("/getStudentByno/{no}")
public List<Student> findUserByStudentId(@PathVariable int no){
return StudentService.findStudentByno(no);
}
}
5. Test results
Open browser input http://localhost:8080/Student/getAllStudent/
3, Summary
JDBC is an API for operating database provided by Java; Mybatis is an excellent persistence layer framework that supports common SQL queries, stored procedures and advanced mapping. Mybatis eliminates almost all the manual setting of JDBC code and parameters and the retrieval encapsulation of result sets. Mybatis can use simple XML or annotations for configuration and original mapping, and map the interface and Java POJO(Plain Old Java Objects) into records in the database. Mybatis uses SqlSessionFactoryBuilder to connect the database acquisition and connection that JDBC needs to complete, reducing code duplication. The SQL of mybatis is uniformly placed in the XML file, which is elegant, unified and easy to manage. Mapper's role is to send SQL and then return the required results, or execute SQL to modify the data of the database. Therefore, it should be within a SqlSession transaction method, just like the execution of an SQL statement in JDBC. Its maximum range is the same as that of SqlSession.