1. Overview of Mybatis
1.1 Mybatis concepts
MyBatis was originally an open source project for apache, iBatis, which was migrated to google code by apache software foundation in 2010 and renamed MyBatis. Migrated to Github in November 2013. The term iBATIS, derived from a combination of "internet" and "abatis", is a persistent Java-based framework. IBATIS provides a persistence layer framework that includes SQL Maps and Data Access Objects (DAO).
Mybatis is a java-based persistent tier framework that encapsulates JDBC internally, allowing developers to focus only on the SQL statement itself and not on complex processes such as driver loading, connection creation, Statement creation.
Mybatis configures the various statements to be executed through XML or annotations, maps the java object to the dynamic parameters of SQL in the statement, and generates the final executed SQL statement, which is executed by the mybatis framework, and maps the result directly to a java object.
The problem of mapping entity classes to database tables is solved by using ORM. JDBC is encapsulated to block the access details at the bottom of the JDBCAPI and avoid dealing with jdbc's api to accomplish data persistence.
- O-Object java object
- R-Relation is a table in a database
- M-mapping Mapping
1.2 JDBC programming
public class TestJDBC {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
//Load Driver
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT";
//Get Connections
conn= DriverManager.getConnection(url,"root","root");
//SQL statement
String sql="select * from team;";
ps=conn.prepareStatement(sql);
//Execute Query
rs = ps.executeQuery();
//Traversing result set
List<Team> list=new ArrayList<>();
while (rs.next()){
Team team=new Team();
team.setTeamName(rs.getString("teamName"));
team.setTeamId(rs.getInt("teamId"));
team.setCreateTime(rs.getDate("createTime"));
team.setLocation(rs.getString("location"));
list.add(team);
}
list.forEach(team -> System.out.println(team));
} catch (Exception e){
e.printStackTrace();
} finally {
try {
//close resource
if (rs != null){
rs.close();
}
if (ps != null){
ps.close();
}
if (conn != null){
conn.close();
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
1.3 Questions solved by Mybatis
- Frequent operations of creating and releasing database connections result in a waste of resources that can affect system performance.
- As SQL statements are written in code, hard coding makes the code difficult to maintain. It is more likely that the SQL statements will change in practice, and the java classes will need to change once they change.
- Pass-through parameters use placeholders when using preparedStatement, and there is also hard-coding because the source code must be modified because of changes in the SQL statement.
- Hardcodes are also present in resolving result sets.
2. Introduction to Mybatis
2.1 Creating databases and tables
CREATE TABLE `team` ( `teamId` int NOT NULL AUTO_INCREMENT COMMENT 'Team ID', `teamName` varchar(50) DEFAULT NULL COMMENT 'Team name', `location` varchar(50) DEFAULT NULL COMMENT 'Team Position', `createTime` date DEFAULT NULL COMMENT 'Team building time', PRIMARY KEY (`teamId`) ) ENGINE=InnoDB AUTO_INCREMENT=1003 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
2.2 Create a maven project and add a jar dependency for Mybatis
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.23</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.0</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
</configuration>
</plugin>
</plugins>
</build>
2.3 Write a configuration file for Mybatis
In general, the name of the profile can be customized and mybatis.xml is used in the course. Configuration files are placed in java/resources.
Header files go to the official website to copy and paste. Here you can find a website in Chinese. Mybatis web address
<?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>
<!--To configure mybatis Environment-->
<environments default="development">
<!--id: Data Source Name-->
<environment id="development">
<!--Type of transaction: JDBC Use Connection Submit and rollback methods for objects-->
<transactionManager type="JDBC"/>
<!--Create a data source: POOLED How to use connection pooling-->
<dataSource type="POOLED">
<!--Four essential parameters for creating a data source-->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis? useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
</configuration>
2.4 Writing entity classes
Attributes in entity classes must match column names in tables
public class Team {
private Integer teamId;
private String teamName;
private String location;
private Date createTime;
//Relationship field: A team can have more than one player
//One party (team) holds a collection of players
private List<Player> playerList1;
private List<Player> playerList2;
@Override
public String toString() {
return "Team{" +
"teamId=" + teamId +
", teamName='" + teamName + '\'' +
", location='" + location + '\'' +
", createTime=" + createTime +
", playerList1=" + playerList1 +
", playerList2=" + playerList2 +
'}';
}
//Omit set get method
}
2.5 Write ORM mapping file
We do ORM mapping for entity class Team.java and table Team.
In the Mybatis framework, ORM mapping is done for SQL statements, and the Mybatis framework extracts the SQL statements into XML. So we need to write an XML mapping file for each entity class.
2.5.1 The XML mapping file must be under the same package as the entity class
2.5.2 The XML mapping file name must be the same as the entity class name
Header files can be copied on the website.
<?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">
<!--namespace="The name must match the name of the mapped class and be fully qualified"-->
<mapper namespace="com.kkb.pojo.Team">
<!-- id="Custom name, id Cannot repeat; Amount to dao Method name in"
resultType="Requirements for use: Attribute names in entity classes match column names in tables"
-->
<select id="queryAll" resultType="com.kkb.pojo.Team">
select * from team;
</select>
</mapper>
2.6 Register the mapping file with the mybatis configuration file
<?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>
<!--To configure mybatis Environment-->
<environments default="development">
<!--id: Data Source Name-->
<environment id="development">
<!--Type of transaction: JDBC Use Connection Submit and rollback methods for objects-->
<transactionManager type="JDBC"/>
<!--Create a data source: POOLED How to use connection pooling-->
<dataSource type="POOLED">
<!--Four essential parameters for creating a data source-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/kkb/pojo/Team.xml"/>
</mappers>
</configuration>
2.7 Scan location for configuration mapping files
Scan path for pom.xml file configuration map file
<build>
<resources>
<resource>
<directory>src/main/java</directory><!--In the directory-->
<includes><!--Include under directory.properties,.xml All files will be scanned to-->
<include>**/*.properties</include>
<include>**/*.xml</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
<plugins>
//ellipsis
</plugins>
</build>
2.8 Core interface testing using the Mybatis framework
public class TestTeam {
private String resource="mybatis.xml"; //Drawing drawings
@Test
public void test01(){
SqlSession sqlSession=null;
try {
//Read Configuration File
Reader reader = Resources.getResourceAsReader(resource);
//Create a factory
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);//The factory was created from the drawing
//Get Connections
sqlSession= factory.openSession();
//Execute sql
List<Team> list = sqlSession.selectList("com.kkb.pojo.Team.queryAll");
//Traversal result
for (Team team : list) {
System.out.println(team);
}
} catch (IOException e) {
e.printStackTrace();
}finally {
//close resource
sqlSession.close();
}
}
}
2.9 Additions and deletions of introductory cases
2.9.1 Query single object based on ID
Add to the mapping file for Team.xml:
<!--according to ID query
parameterType="Types of parameters"，Only one parameter is currently supported
where teamId=#{id}: #{id} denotes parameter id-customization, which only needs to conform to naming conventions and has no actual meaning
-->
<select id="queryById" parameterType="java.lang.Integer" resultType="com.kkb.pojo.Team">
select * from team where teamId=#{id}
</select>
Add the following to the test class:
@Test
public void testFindById(){
System.out.println("testFindById---------");
Team team = sqlSession.selectOne("com.kkb.pojo.Team.queryById", 1001);
System.out.println(team);
}
@Before //Indicates that the method executes before executing the test method
public void before(){
// System.out.println("before");
//Read Configuration File
Reader reader = null;
try {
reader = Resources.getResourceAsReader(resource);
} catch (IOException e) {
e.printStackTrace();
}
//Create a factory
SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(reader);//The factory was created from the drawing
//Get Connections
sqlSession= factory.openSession();
}
@After//Indicates that the method is executed after the test method is executed
public void after(){
//System.out.println("after");
sqlSession.close();
}
2.9.2 Add or delete
Add to the mapping file for Team.xml:
<delete id="del" >
delete from team where teamId=#{id}
</delete>
<update id="update" parameterType="com.kkb.pojo.Team">
update team set teamName=#{teamName},location=#{location}
where teamId=#{teamId}
</update>
<!--Add a team
parameterType="com.kkb.pojo.Team" Objects as parameters,
#The {Value} value must be the attribute name in the entity class, which is actually a placeholder?
-->
<insert id="add" parameterType="com.kkb.pojo.Team" >
INSERT INTO `team` (`teamName`, `location`, `createTime`)
VALUES (#{teamName}, #{location}, #{createTime})
</insert>
Add the following methods to the test class:
@Test
public void testDel(){
int num = sqlSession.delete("com.kkb.pojo.Team.del", 1054);
sqlSession.commit();
System.out.println(num);
}
@Test
public void testUpdate(){
Team team=sqlSession.selectOne("com.kkb.pojo.Team.queryById",1053);
team.setTeamName("dengzeyang The team");
team.setLocation("Bremen");
int num = sqlSession.update("com.kkb.pojo.Team.update", team);
sqlSession.commit();
System.out.println(num);
}
@Test
public void testAdd(){
Team team=new Team();
team.setTeamName("Shanghai bilibili");
team.setLocation("Shanghai");
team.setCreateTime(new Date());
int num = sqlSession.insert("com.kkb.pojo.Team.add", team);//Additions and deletions must be manually committed
sqlSession.commit();//Commit Transaction Manually
System.out.println(num);
}
3. Mybatis object analysis
3.1 Resources
The Resources class, as its name implies, is a resource for reading resource files. There are many ways to return different types of IO stream objects by loading and parsing resource files.
3.2 SqlSessionFactoryBuilder
To create a SqlSessionFactory, you need to use the build() method of the SqlSessionFactoryBuilder object. In fact, the reason for using SqlSessionFactoryBuilder is that the creation of SqlSessionFactory, a complex object, is left to Builder, which uses the Builder Design pattern.
Builder mode: Also known as generator mode, is a mode of creating objects. The internal representation of a product can be separated from the product generation process, which allows a build process to produce a product with different internal representations (separating the construction of a complex object from its representation so that the same build process can create different representations). This allows the user to specify only the type of product to be built. It is not necessary to know the specific construction process and details.
In Builder mode, the role of Director and Builder: the user contacts the instructor, the instructor directs the builder, and finally the product. Builder mode can enforce a step-by-step construction process.
3.3 SqlSessionFactory
The SqlSessionFactory interface object is a heavyweight (overhead) object and thread-safe, so only one object is needed for an application. Creating a SqlSession requires the openSession() method using the SqlSessionFactory interface.
The default openSession() method has no parameters and creates a SqlSession with the following characteristics:
- It opens a transaction (that is, it does not commit automatically).
- The Connection object will be retrieved from the DataSource instance configured by the current environment. The transaction isolation level will use the default settings for the driver or data source.
- Preprocessing statements are not reused or batch processed for updates.
openSession(true): Create a SqlSession with autocommit capability
openSession(false): Create a SqlSession that is not autocommit and requires manual submission
openSession(): same as openSession(false)
3.4 SqlSession
The SqlSession interface object is used to perform persistence operations. A SqlSession corresponds to a database session, which starts with the creation of the SqlSession object and ends with the closure of the SqlSession object.
The SqlSession interface object is thread insecure, so you need to call its close() method and close it immediately before each database session ends. Need session again, create again. SqlSession is created inside the method and closed after use.
There are more than 20 methods in the SqlSession class, and almost all of them are syntax-related.
These methods are used to execute SECT, INSERT, UPDATE, and DELETE statements defined in the XML file of the SQL mapping. They all interpret themselves, each using the ID property of the statement and the parameter object, which can be a native type (auto-boxed or wrapped class), JavaBean, POJO, or Map.
<T> T selectOne(String statement, Object parameter) <E> List<E> selectList(String statement, Object parameter) <K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey) int insert(String statement, Object parameter) int update(String statement, Object parameter) int delete(String statement, Object parameter) <!--selectOne and selectList The difference is simply selectOne Must return an object or null Value. If more than one value is returned, an exception is thrown. selectMap A little special because it will use one of the properties of the returned object as key Value, objects as value Value, which converts a multiple result set to Map Type value. Because not all statements require parameters, these methods are overloaded in a form that does not require parameters.-->
3.5 Mybatis Architecture
- The Mybatis.xml file is the global configuration file for the mybatis framework and configures information such as the environment in which the mybatis framework runs. Mapper1.xml... is a mapping file for SQL in which all SQL statements that operate on the database are configured and need to be loaded in the global configuration file.
- Building SqlSessionFactroy from configuration information such as the mybatis environment is equivalent to generating connection pools
- A SqlSession is a session (connection) created by a session factory and the operation database needs to be done through the SqlSession.
- The bottom level of Mybatis customizes the Executor executor's interface operation database. There are two implementations of the Executor interface, one is the basic executor and the other is the cached executor.
- Mapped statement is also an underlying encapsulation object of the mybatis framework, which wraps mybatis configuration information and SQL mapping information. An SQL statement in the Mapper.xml file corresponds to a Mapped State object whose id is the Mapped State's id.
- Mapped statement defines input parameters for SQL execution, including HashMap, base type, pojo. Executor maps input java objects to SQL statements before executing SQL statements through Mapped statemen. After executing SQL, output mapping is the definition of preparedStatement execution result in JDBC encoding.
4. Configuration log file
4.1 Add jar dependencies
<dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency>
4.2 Add Log Profile
Add log4j.properties configuration file under resource
# Global logging configuration info warning error log4j.rootLogger=DEBUG,stdout # Console output... log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
4.3 Configuration to add logs to mybatis configuration file
<settings>
<!--Configuration Log-->
<setting name="logImpl" value="LOG4J"/>
</settings>
4.4 Results
5. Develop using original Dao method
5.1 Create Tool Class
5.1.1 ThreadLocal
ThreadLocal is not a native implementation version of a thread; it is not a Thread, but a threadlocalvariable (thread local variable). Perhaps it would be more appropriate to name it ThreadLocalVar. ThreadLocal is a very simple function to provide a copy of the value of a variable for every thread that uses it. It is a special thread binding mechanism in Java where each thread can independently change its own copy without conflicting with other threads'copies.
Example:
class Test{
private ThreadLocal<String> str = new ThreadLocal<String>();
private List<String> list = new ArrayList<String>();
class A extends Thread {
public void run() {
str.set("dengzeyang");
System.out.println("A...." + str.get());
list.add("AAA");
System.out.println("A<<<"+list.get(0));
}
}
class B extends Thread {
public void run() {
System.out.println("B...." + str.get());
list.add("BBB");
System.out.println("B<<<"+list.get(0));
}
}
}
Test Code:
Test2 t=new Test2();
Test2.A a=t.new A();
Test2.B b=t.new B();
a.start();
b.start();
5.1.2 Tool Class
public class MybatisUtil {
private static ThreadLocal<SqlSession> sqlSessionThreadLocal=new ThreadLocal<>();
private static SqlSessionFactory factory;
static {
Reader reader = null;
try {
reader = Resources.getResourceAsReader("mybatis.xml");
} catch (IOException e) {
e.printStackTrace();
}
//Create a factory
factory=new SqlSessionFactoryBuilder().build(reader);//The factory was created from the drawing
}
/**
* Get Connections
* @return
*/
public static SqlSession getSqlSession(){
//Get from ThreadLocal
SqlSession sqlSession = sqlSessionThreadLocal.get();
if(sqlSession==null) {
//Create sqlSession
sqlSession = factory.openSession();
//Bind sqlSession to thread
sqlSessionThreadLocal.set(sqlSession);
}
return sqlSession;
}
/**
* Close Connection
*/
public static void closeSqlSession(){
//Get from ThreadLocal
SqlSession sqlSession = sqlSessionThreadLocal.get();
if(sqlSession!=null){
sqlSession.close();
sqlSessionThreadLocal.remove();
}
}
}
5.2 Create a TeamDao interface and implementation class
public interface TeamDao {
List<Team> queryAll();
Team queryById(Integer teamId);
int add(Team team);
int update(Team team);
int del(Integer teamId);
}
public class TeamDaoImpl implements TeamDao{
/**
* Query all teams
* @return
*/
@Override
public List<Team> queryAll() {
SqlSession sqlSession= MybatisUtil.getSqlSession();
return sqlSession.selectList("com.kkb.pojo.Team.queryAll");
}
/**
* Query individual teams based on id
* @param teamId
* @return
*/
@Override
public Team queryById(Integer teamId) {
SqlSession sqlSession= MybatisUtil.getSqlSession();
return sqlSession.selectOne("com.kkb.pojo.Team.queryById",teamId);
}
/**
* Add a team
* @param team
* @return
*/
@Override
public int add(Team team) {
SqlSession sqlSession= MybatisUtil.getSqlSession();
int num= sqlSession.insert("com.kkb.pojo.Team.add",team);
sqlSession.commit();
return num;
}
/**
* Update team
* @param team
* @return
*/
@Override
public int update(Team team) {
SqlSession sqlSession= MybatisUtil.getSqlSession();
int num= sqlSession.update("com.kkb.pojo.Team.update",team);
sqlSession.commit();
return num;
}
/**
* Delete team by id
* @param teamId
* @return
*/
@Override
public int del(Integer teamId) {
SqlSession sqlSession= MybatisUtil.getSqlSession();
int num= sqlSession.delete("com.kkb.pojo.Team.del",teamId);
sqlSession.commit();
return num;
}
}
Test:
public class TestTeamDao {
private TeamDao teamDao=new TeamDaoImpl();
@Test
public void testDel(){
int num = teamDao.del(1114);
System.out.println(num);
}
@Test
public void testUpdate(){
Team team = teamDao.queryById(1114);
team.setTeamName("lina");
team.setLocation("bj");
int num = teamDao.update(team);
System.out.println(num);
}
@Test
public void testAdd(){
Team team=new Team();
team.setTeamName("lina The team");
team.setLocation("Beijing");
team.setCreateTime(new Date());
int num = teamDao.add( team);
System.out.println(num);
}
@Test
public void test02(){
Team team = teamDao.queryById(1001);
System.out.println(team);
}
@Test
public void test01(){
List<Team> teams = teamDao.queryAll();
teams.forEach(team -> System.out.println(team));
}
}
6. Write Mybatis projects using Mapper's interface
6.1 What is a Mapper interface
In the previous example, we found a problem when customizing the Dao interface implementation class: Dao's implementation class does not actually do any real work, it just locates the corresponding id's SQL statement in the mapper file through the related API of SqlSession. The real work to operate on DB is done by the framework through SQL in mapper.
Therefore, the MyBatis framework bypasses Dao's implementation class and navigates directly to the corresponding SQL statement in the mapper file to manipulate the DB. This implementation of Dao is called the dynamic proxy of the Mapper interface.
Mapper dynamic proxy does not require a programmer to implement the Dao interface. The interface is implemented by a dynamic proxy automatically generated by MyBatis in conjunction with a mapping file.
6.2 Implementation Steps
6.2.1 Writing interface TeamMapper.java
public interface TeamMapper {
List<Team> queryAll();
Team queryById(Integer teamId);
int add(Team team);
int update(Team team);
int del(Integer teamId);
}
Create a TeamMapper.xml file, much like the contents of Team.xml, with only namespace="com.kkb.mapper.TeamMapper" modified to the fully qualified name of the interface
<?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">
<!--namespace="Fully qualified name, fully qualified name of interface"-->
<mapper namespace="com.kkb.mapper.TeamMapper">
<!--Omit content, copy Team.xml Content in-->
</mapper>
Register the mapping file in the mybatis.xml configuration file
<mappers>
<!--Omit Others-->
<mapper resource="com/kkb/mapper/TeamMapper.xml"/>
</mappers>
6.2.2 getMapper method to get proxy objects
Simply call the SqlSession getMapper() method to get the implementation class object for the specified interface.
public class TeamMapperTest {
private SqlSession sqlSession=MybatisUtil.getSqlSession();
@Test
public void test01(){
TeamMapper teamDao= sqlSession.getMapper(TeamMapper.class);
//Add to
Team team=new Team();
team.setTeamName("Warrior");
team.setLocation("The Golden State");
team.setCreateTime(new Date());
int num=teamDao.add(team);
sqlSession.commit();//Submit is required for additions and deletions to take effect
System.out.println(num);
//To update
Team team1 = teamDao.queryById(1001);
team1.setTeamName("lina The team");
num = teamDao.update(team1);
sqlSession.commit();//Submit is required for additions and deletions to take effect
System.out.println(num);
//delete
num=teamDao.del(1001);
sqlSession.commit();//Submit is required for additions and deletions to take effect
System.out.println(num);
//Query all
List<Team> teams = teamDao.queryAll();
teams.forEach(t-> System.out.println(t));
}
}
6.3 Implementation Principles
7. Adding, deleting or altering details in the inspection
7.1 Obtain self-increasing id when inserting data
7.1.1 Case Preparation
7.1.1.1 Add a new table
CREATE TABLE `gamerecord` ( `recordId` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `homeTeamId` int DEFAULT NULL COMMENT 'Home team ID', `gameDate` datetime DEFAULT CURRENT_TIMESTAMP COMMENT 'Date of competition', `score` int DEFAULT NULL COMMENT 'Score', `visitingTeamId` int DEFAULT NULL COMMENT 'Visiting team ID', PRIMARY KEY (`recordId`), KEY `homeTeamId` (`homeTeamId`), KEY `visitingTeamId` (`visitingTeamId`), CONSTRAINT `gamerecord_ibfk_1` FOREIGN KEY (`homeTeamId`) REFERENCES `team` (`teamId`), CONSTRAINT `gamerecord_ibfk_2` FOREIGN KEY (`visitingTeamId`) REFERENCES `team` (`teamId`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
7.1.1.2 Entity Class
/**
* Entity Class for Competition Records
*/
public class GameRecord {
private String recordId;
private Integer homeTeamId;
private Date gameDate;
private Integer score;
private Integer visitingTeamId;
//Omit set get
}
7.1.1.3 mapper interface
public interface GameRecordMapper {
int add(GameRecord record);
}
7.1.2 Modify Profile
Add GameRecordMapper.xml file
<?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">
<!--namespace="The name must match the name of the mapped class and be fully qualified"-->
<mapper namespace="com.kkb.mapper.GameRecordMapper">
<insert id="add" parameterType="com.kkb.pojo.GameRecord">
<!--Get a 36-bit string as before inserting data id Place Attributes recordId in
order="AFTER/BEFORE" stay insert Before or after execution
resultType="Type of return value"
-->
<selectKey keyProperty="recordId" order="BEFORE" resultType="java.lang.String">
select UUID()
</selectKey>
INSERT INTO `mybatis`.`gamerecord` (`recordId`, `homeTeamId`, `score`, `visitingTeamId`)
VALUES (#{recordId}, #{homeTeamId}, #{score}, #{visitingTeamId})
</insert>
</mapper>
Modify insert nodes in the TeamMapper.xml file
<!--Add a team
parameterType="com.kkb.pojo.Team" Objects as parameters,
#The {Value} value must be the attribute name in the entity class, which is actually a placeholder?
-->
<insert id="add" parameterType="com.kkb.pojo.Team" >
<!--Added Successfully ID Assigning values to parameter attributes teamId
keyProperty:Represents a new id Which attribute value is assigned red
order: AFTER/BEFORE Two values representing selectKey In sql Statement in insert Execute before or after statement
resultType: Represents the return value type
-->
<selectKey keyProperty="teamId" order="AFTER" resultType="java.lang.Integer">
select LAST_INSERT_ID()
</selectKey>
INSERT INTO `team` (`teamName`, `location`, `createTime`)
VALUES (#{teamName}, #{location}, #{createTime})
</insert>
7.1.3 Test Class
public class TestGameRecordMapper {
private SqlSession sqlSession=MybatisUtil.getSqlSession();
@Test
public void testAdd(){
GameRecordMapper mapper = MybatisUtil.getSqlSession().getMapper(GameRecordMapper.class);
GameRecord record=new GameRecord();
record.setHomeTeamId(1003);
record.setVisitingTeamId(1001);
record.setScore(90);
int add = mapper.add(record);
MybatisUtil.getSqlSession().commit(); //Submit is required for additions and deletions to take effect
System.out.println(add);
}
}
7.2 Input Mapping
7.2.1 parameterType
parameterType: The type of a method parameter in an interface, which must be a fully qualified name or alias (alias later). This property is not required because the Mybatis framework can determine the parameters of the specific incoming statement on its own and the default value is unset.
Add the following to the TeamMapper.xml configuration file:
<!--Detail 1:
mybatis3.3 Before version: can write directly#{0} #{1}
from mybatis3.4 Start:#{arg0} #{arg1}... Or #{param1} #{param2}...
Detail 2: sql You cannot use the less than sign in a statement and replace it with the transfer symbol;The greater than sign is not restricted or can be replaced with an escape symbol >
-->
<select id="queryByRange1" resultType="com.kkb.pojo.Team">
select * from team
where teamId>=#{param1} and teamId<=#{param2}
</select>
Test class addition method:
public class TestTeamMapperArg {
private TeamMapper teamMapper= MybatisUtil.getSqlSession().getMapper(TeamMapper.class);
@Test
public void test01(){
List<Team> teams = teamMapper.queryByRange1(1004, 1010);
teams.forEach(team -> System.out.println(team));
}
}
7.2.3.2 via @Param annotation
Add @Param("Custom Parameter Name") before the method's parameters and use #{Custom Parameter Name} to pass the parameters in the mapper file.
The TeamMapper interface adds the following:
List<Team> queryByRange2(@Param("min") Integer min, @Param("max") Integer max);
Add the following to the TeamMapper.xml configuration file:
<!--
Mode 2:#The name in {} must match the parameter comment @Param() in the method of the interface-->
<select id="queryByRange2" resultType="com.kkb.pojo.Team">
select * from team
where teamId>=#{min} and teamId<=#{max}
</select>
Test class addition method:
@Test
public void test02(){
List<Team> teams = teamMapper.queryByRange2(1005, 1011);
teams.forEach(team -> System.out.println(team));
}
7.2.3.3 Pass multiple parameters by map
Map collections can store multiple values and use Map to pass in multiple parameters to a mapper file at once. Map collections use String's key and Object-type values to store parameters. The mapper file uses the # {key} reference parameter value TeamMapper interface to add the following:
List<Team> queryByRange3(Map<String,Object> map);
Add the following to the TeamMapper.xml configuration file:
<!--
Mode 3:#The name in {} must be the same as the key in the Map collection-->
<select id="queryByRange3" resultType="com.kkb.pojo.Team">
select * from team
where teamId>=#{min} and teamId<=#{max}
</select>
Test class addition method:
@Test
public void test03(){
Map<String, Object> map=new HashMap<>();
map.put("min",1010);
map.put("max",1015);
List<Team> teams = teamMapper.queryByRange3(map);
teams.forEach(team -> System.out.println(team));
}
7.2.3.4 Pass multiple parameters through the pojo class
Similar to passing multiple parameters by map, the parameter placeholder in the map file must exactly match the properties in the pojo class.
Entity class:
public class QueryVO {
private String name;
private Integer min;
private Integer max;
private String location;
//Omit set get
}
The TeamMapper interface adds the following:
List<Team> queryByCondition(QueryVO vo);
Add the following to the TeamMapper.xml configuration file:
<!--
Mode 4:#The name in {} must have the same properties as the pojo parameter -->
<select id="queryByCondition" resultType="com.kkb.pojo.Team">
select * from team
where teamId>=#{min} and teamId<=#{max}
and teamName like #{name} and location=#{location}
</select>
Test class addition method:
@Test
public void test04(){
QueryVO vo=new QueryVO();
vo.setLocation("Los Angeles");
vo.setName("%Team%");
vo.setMin(1001);
vo.setMax(1111);
List<Team> teams = teamMapper.queryByCondition(vo);
teams.forEach(team -> System.out.println(team));
}
7.3 Differences between #{} and ${} - Examination questions preferred in interviews
7.3.1 #{}
#{}: Represents a placeholder that tells Mybatis to use the actual parameter values instead. The sql statement is executed using the PrepareStatement object, #{...} instead of the'?'of the sql statement. This is the preferred practice in Mybatis and is safe and fast.
<select id="queryById" parameterType="java.lang.Integer" resultType="com.kkb.pojo.Team">
select * from team where teamId=#{id}
</select>
<!--Mybatis Execute when:
String sql="select * from team where teamId=?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1,1001);
where teamId=? Actually it is where teamId=#{id}
ps.setInt(1,1001) 1001 in will replace#{id}
-->
7.3.2 ${}
Indicates that the string is replaced as is, notifying Mybatis to replace its location with the string contained in the dollar sign. Use Statement or PreparedStatement to connect the sql statement to the contents of ${}. They are commonly used to replace table names, column names, sort different columns, and so on.
For example, based on the name of the team, the team location queries the team list
Mode 1:
The TeamMapper interface adds the following:
List<Team> queryByName(String teamName); List<Team> queryByLocation(String location);
Add the following to the TeamMapper.xml configuration file:
<select id="queryByName" resultType="team">
select * from team where teamName=#{teamName}
</select>
<select id="queryByLocation" resultType="com.kkb.pojo.Team">
select * from team where location=#{location}
</select>
Test class addition method:
@Test
public void test05(){
System.out.println("Query by team name:");
List<Team> teams = teamMapper.queryByName("zeyang The team");
teams.forEach(team -> System.out.println(team));
System.out.println("Query based on team position:");
List<Team> teams2 = teamMapper.queryByLocation("Los Angeles");
teams2.forEach(team -> System.out.println(team));
}
Mode 2: Use different columns as query criteria
The TeamMapper interface adds the following:
List<Team> queryByFiled(@Param("column") String column,@Param("columnValue") String columnValue);
Add the following to the TeamMapper.xml configuration file:
<select id="queryByFiled" resultType="com.kkb.pojo.Team">
select * from team where ${column}=#{columnValue}
</select>
Test class addition method:
@Test
public void test06(){
System.out.println("Query by team name:");
List<Team> teams = teamMapper.queryByFiled("teamName","lina The team");
teams.forEach(team -> System.out.println(team));
System.out.println("Query based on team position:");
List<Team> teams2 = teamMapper.queryByFiled("location","Los Angeles");
teams2.forEach(team -> System.out.println(team));
}
7.4 Output Mapping
7.4.1 resultType
resultType: The type that executes sql to get the ResultSet transformation, using the fully qualified name or alias of the type. If a collection is returned, the type of collection element is set, not the collection itself. resultType and resultMap cannot be used together.
7.4.1.1 Output Simple Type
Case: Total number of records returned to the team
The TeamMapper interface adds the following:
int getCount();
Add the following to the TeamMapper.xml configuration file:
<!--The return type can be a basic type: the data required to be returned must be a single line,If it's a single row and multiple columns, you won't get the value of the following column,
If a multiline exception is returned: TooManyResultsException-->
<select id="getCount" resultType="int">
select count(teamId)from team
</select>
Test class addition method:
@Test
public void test07(){
int count = teamMapper.getCount();
System.out.println("Total rows:"+count);
}
7.4.1.2 Output pojo type
Case: Refer to previous queries for all team information
List<Team> queryAll();
<!--The interface method returns a collection type, but the resultType The type in the collection needs to be specified, not the collection itself.-->
<select id="queryAll" resultType="com.kkb.pojo.Team">
select * from team
</select>
7.4.1.3 Output Map Type
When we only need to query a few columns of data in a table, we can use sql's query results as Map key s and value s. Map<Object, Object> is commonly used.
Map returns as an interface, and the query result of the sql statement can have at most one record. More than one record throws a TooManyResultsException exception.
If there are multiple rows, use List<Map<Object, Object>.
Case: Query team name and location based on id.
The TeamMapper interface adds the following:
Map<Object,Object> queryTwoColumn(int teamId); List<Map<Object,Object>> queryTwoColumnList();
Add the following to the TeamMapper.xml configuration file:
<select id="getTwoColumn" resultType="hashmap">
select count(teamId) as 'sum',max(teamId) as 'max' from team
</select>
<select id="getTwoColumnList" resultType="java.util.HashMap">
select count(teamId) as 'sum',max(teamId) as 'max' from team group by location;
</select>
Test class addition method:
@Test
public void test08(){
Map<String, Object> map = teamMapper.getTwoColumn();
System.out.println(map);
}
@Test
public void test09(){
List<Map<String, Object>> list = teamMapper.getTwoColumnList();
for (Map<String, Object> map : list) {
System.out.println(map);
}
}
7.4.2 resultMap
resultMap can customize the mapping relationship between sql results and java object properties. More flexible assignment of column values to specified attributes.
Commonly used when column names and java object property names are different.
How to use:
- Define the resultMap first, specifying the corresponding relationship between the column name and the attributes.
- Add in the TeamMapper interface:
List<Team> queryAll2();
TeamMapper.xml mapping file added:
<!--resultMap and resultType Can't appear at the same time
resultMap: Is the reference's own creation resultMap Of id-->
<select id="queryAll2" resultMap="baseResultMap">
select * from team;
</select>
<!--Establish resultMap: Equivalent to writing your own mapping of columns in a table to attributes in an entity class
id:resultMap Name of, Require Unique
type: Expect to map to java Type of
-->
<resultMap id="baseResultMap" type="com.kkb.pojo.Team">
<!--General Primary Key Columns id,The remaining columns are used result
column: Represents column names in database tables, case-insensitive
property: Represents the corresponding attribute name in the entity class, case sensitive
javaType: The type of the corresponding attribute in the entity class can be omitted. mybatis Will deduce for yourself
jdbcType="Types in the database column Type of" General omission
-->
<id column="teamId" property="teamId" javaType="java.lang.Integer" ></id>
<result column="teamName" property="teamName" javaType="java.lang.String"></result>
<result column="location" property="location" javaType="java.lang.String"></result>
<result column="createTime" property="createTime" javaType="java.util.Date"></result>
</resultMap>
Test:
@Test
public void test10(){
List<Team> teams = teamMapper.queryAll2();
teams.forEach(team-> System.out.println(team));
}
7.4.3 How to handle inconsistencies between column and entity class attributes in database tables
Case preparation: Create tables:
use mybatis; DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `user_id` int NOT NULL AUTO_INCREMENT COMMENT 'user id', `user_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT 'User Name', `user_age` int NULL DEFAULT NULL COMMENT 'User Age', PRIMARY KEY (`user_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; INSERT INTO `users` VALUES (1, 'Jia Baoyu', 14); INSERT INTO `users` VALUES (2, 'Lin Daiyu', 13); INSERT INTO `users` VALUES (3, 'Xue Baochai', 15); SET FOREIGN_KEY_CHECKS = 1;
7.4.3.1 Use column aliases and resultType
Entity Class Users.java
public class Users {
private Integer userId;
private String userName;
private Integer userAge;
@Override
public String toString() {
return "Users{" +
"userId=" + userId +
", userName='" + userName + '\'' +
", userAge=" + userAge +
'}';
}
}
Interface UsersMapper.java
public interface UsersMapper {
Users queryById(int userId);
}
Mapping file UsersMapper.xml
<?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.kkb.mapper.UsersMapper">
<!--Mode 1: resultType Attributes of entity classes in as aliases in query statements to keep aliases and attributes consistent-->
<select id="queryById" resultType="com.kkb.pojo.Users">
select user_id as userId,user_name as userName,user_age as userAge from users where user_id=#{id};
</select>
</mapper>
Test class TestUsersMapper.java
public class TestUsersMapper {
private UsersMapper mapper= MybatisUtil.getSqlSession().getMapper(UsersMapper.class);
@Test
public void test1(){
Users user = mapper.queryById(1);
System.out.println(user);
}
}
7.4.3.2 Using resultMap
Interface UsersMapper.java Add Method
Users queryByID2(int userId);
The mapping file UsersMapper.xml adds the following:
<!--Mode 2: Pass through resultMap Self-Mapping-->
<select id="queryById2" resultMap="baseMap">
select * from users where user_id=#{id};
</select>
<resultMap id="baseMap" type="com.kkb.pojo.Users">
<id column="user_id" property="userId"/>
<result column="user_name" property="userName"/>
<result column="user_age" property="userAge"/>
</resultMap>
Test:
@Test
public void test2(){
Users user = mapper.queryById2(1);
System.out.println(user);
}
8. Mybatis Global Profile
The mybatis.xml used in the case is the global configuration file for Mybatis.
Global profile requires constraints in the header
<?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">
8.1 Configuration Contents
The configuration file for MyBatis contains settings and attribute information that deeply affect the behavior of MyBatis. The top-level structure of the configuration document is as follows:
configuration(Configuration) properties--Properties: Load external configuration files, such as loading connection information for the database Settings--Global configuration parameters: such as log configuration typeAliases--Type Alias typeHandlers----Type Processor objectFactory-----Object Factory Plugins------Plug-ins: such as paging plug-ins Environments----Environment Collection Property Object environment(Environment Variables) transactionManager(Transaction Manager) dataSource(Data Source) Mappers---Mapper: Register mapping files for
8.2 properties
Attributes can be configured externally and replaced dynamically. We can either set the properties in the child elements of the properties element (such as the properties node in the DataSource node) or configure these properties in the Java properties file.
There are four parameter data in the data source to connect to the database. We usually put it in a special property file. The global configuration file of mybatis can read the data directly from the property file.
- Create a jdbc.properties file in the resources directory with a customizable name.
jdbc.driver=com.mysql.cj.jdbc.Driver jdbc.url=jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT jdbc.username=root jdbc.password=123456
- mybatis global profile introduction properties file
<properties resource="jdbc.properties"/>
- Use the values in the properties file
<dataSource type="POOLED">
<!--Four essential parameters for creating a data source-->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
8.3 settings
The most important adjustment settings in MyBatis change the runtime behavior of MyBatis. For example, the logs we configure are one of the applications. The rest refers to the setup documentation.
<settings>
<!--Configuration Log-->
<setting name="logImpl" value="LOG4J"/>
</settings>
8.4 Type Aliases
Type aliases can set an abbreviated name for Java types. It is used only for XML configuration and is intended to reduce redundancy in fully qualified class name writing.
8.4.1 Aliases already supported in Mybatis
Below are some built-in type aliases for common Java types. They are case-insensitive, and note that special naming styles have been adopted to cope with duplicate naming of the original type.
8.4.2 Custom Alias
<!--Set Alias-->
<typeAliases>
<typeAlias type="com.kkb.pojo.Team" alias="Team"></typeAlias>
<package name="com.kkb.pojo"/>
<package name="com.kkb.mapper"/>
</typeAliases>
8.5 Mappers
There are several ways to configure:
8.5.1, using resource references relative to class paths
Grammar:<mapper resource=""/> Use resources relative to class paths,from classpath Path Find File For example:<mapper resource="com/kkb/mapper/TeamMapper.xml" />
8.5.2, Fully qualified class name using mapper interface
grammar:<mapper class=""/> Used mapper Fully qualified name of interface Requirements: Interface and mapping files have the same name as the package for example<mapper class="com.kkb.mapper.GameRecordMapper"/>
8.5.3, Register all mapper interface implementations within the package as mappers - Recommended
grammar:<package name=""/> Specify all under the package Mapper Interface For example:<package name="com.kkb.mapper"/> Note: This method requires Mapper Interface name and mapper Map files have the same name and are in the same directory.
8.6 dataSource tag
Access database in Mybatis supports connection pooling technology and is your own connection pooling technology. Configuration of connection pools in Mybatis is implemented by using mybatis.xml configuration file in Mybatis. When MyBatis initializes, it creates a corresponding type of data source DataSource based on the type property.
Mybatis data sources fall into three categories:
- UNPOOLED: Data source without connection pool
- POOLED: Data source using connection pool
- JNDI: Data source implemented using JNDI
The first two data sources implement the javax.sql.DataSource interface
8.7 Transactions
8.7.1, Transaction needs to be committed manually by default
The Mybatis framework encapsulates JDBC, so the transaction control of the Mybatis framework itself uses the commit (), rollback (), and setAutoCommit() methods of the JDBC Connection object to set the transaction commit mode. Automatic and manual submissions
<transactionManager type="JDBC"/>
This tag is used to specify the transaction manager used by MyBatis. MyBatis supports two types of transaction managers: JDBC and MANAGED.
JDBC: Using JDBC's transaction management mechanism, commit through the commit() method of the Connection object, and rollback() method. By default, mybatis turns off the automatic commit function and changes to manual commit, as you can see from the observation log, so we need to commit or roll back the transaction ourselves in our programs.
MANAGED: A container manages the entire life cycle of a transaction (such as a Spring container).
8.7.2, Autocommit Transaction
The openSession method of SqlSessionFactory is overloaded and can be set up for automatic submission.
If sqlSession = SqlSessionFactory.openSession(true); With the parameter set to true, the session.commit() method is not required to perform additions and deletions again, and the transaction is committed automatically.
9. Relationship Mapping in Mybatis
The table structure is shown in the figure (the SQL statement that created the table is in the package):
9.1 Mapping of one-to-one relationships
9.1.1 Entity Class
public class Player {
private Integer playerId;
private String playerName;
private Integer playerNum;
private Integer teamId;
//Relationship field: Multiple players can belong to the same team
//Objects held by one party (team) by many parties (players)
private Team team1;
private Team team2;
private Team team3;
}
9.1.2 mapper interface
public interface PlayerMapper {
Player queryById(int playerId);
Player queryById1(int playerId);
Player queryById2(int playerId);
Player queryById3(int playerId);
List<Player> queryByTeamId(int teamId);
}
9.1.3 One-to-one mapping 1: the way attributes are invoked by dotting associated objects
Requirements: Join query for two tables
9.1.4 One-to-one mapping 2: Mapper mapping that directly references associated objects
Requirement:
- Join Query for Two Tables
- A referenced resultMap already exists in the associated object
9.1.5 One-to-one mapping 3: A way to directly reference a separate query of associated objects
Requirement:
- Join queries that do not require two tables
- A referenced query method already exists in the associated object
<?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.kkb.mapper.PlayerMapper">
<select id="queryByTeamId" resultMap="baseResultMap">
select * from player where teamId=#{id}
</select>
<select id="queryById" resultMap="baseResultMap">
select * from player where playerId=#{id}
</select>
<resultMap id="baseResultMap" type="Player">
<id column="playerId" property="playerId"/>
<result column="playerName" property="playerName"/>
<result column="playerNum" property="playerNum"/>
<result column="teamId" property="teamId"/>
</resultMap>
<!--Mode 1: Map column names directly from object dotting attributes
Requirements: Queries must be joined
Result mappings are typically customized-->
<select id="queryById1" resultMap="joinTeamResult1">
SELECT * FROM `player` p INNER JOIN team t
on t.teamId=p.teamId
where playerid=#{id}
</select>
<resultMap id="joinTeamResult1" type="Player" extends="baseResultMap">
<result column="teamId" property="team1.teamId"></result>
<result column="teamName" property="team1.teamName"></result>
<result column="location" property="team1.location"></result>
<result column="createTime" property="team1.createTime"></result>
</resultMap>
<!--Mode 2: directly referencing the associated object Mapper mapping
Requirements: Queries must be joined-->
<select id="queryById2" resultMap="joinTeamResult2">
SELECT * FROM `player` p INNER JOIN team t
on t.teamId=p.teamId
where playerid=#{id}
</select>
<resultMap id="joinTeamResult2" type="Player" extends="baseResultMap">
<association property="team2" javaType="Team"
resultMap="com.kkb.mapper.TeamMapper.baseResultMap"/>
</resultMap>
<!--Mode 3: Use a separate query statement for the associated object
Requirement: No join query required
A corresponding query statement is required in the associated object-->
<select id="queryById3" resultMap="joinTeamResult3">
select * from player where playerId=#{id}
</select>
<resultMap id="joinTeamResult3" type="Player" extends="baseResultMap">
<association property="team3" javaType="Team"
select="com.kkb.mapper.TeamMapper.queryById" column="teamId"/>
</resultMap>
</mapper>
9.1.6 Test
public class TestPlayerMapper {
private PlayerMapper playerMapper= MybatisUtil.getSqlSession().getMapper(PlayerMapper.class);
private TeamMapper teamMapper=MybatisUtil.getSqlSession().getMapper(TeamMapper.class);
@Test
public void test1(){
Player player = playerMapper.queryById(1);
System.out.println(player);
}
@Test
public void test2(){
Player player = playerMapper.queryById1(1);
System.out.println(player);
}
@Test
public void test3(){
Player player = playerMapper.queryById2(1);
System.out.println(player);
}
}
9.2 Mapping of one-to-many relationships
Modify the entity class Team.java:
public class Team implements Serializable {
private Integer teamId;
private String teamName;
private String location;
private Date createTime;
//Relationship field: A team can have more than one player
//One party (team) holds a collection of players
private List<Player> playerList1;
private List<Player> playerList2;
}
Add methods to the TeamMapper.java interface:
public interface TeamMapper {
Team queryById1(Integer teamId);
Team queryById2(Integer teamId);
}
Add methods to the PlayerMapper.java interface:
public interface PlayerMapper {
List<Player> queryByTeamId(int teamId);
}
9.2.1 Mode 1: Join Query + Reference Result Mapping of Associated Objects
9.2.2 Mode 2: A method of referencing a separate query of associated objects
TeamMapper.xml Add
<!--1,join query+Result mapping referencing associated objects-->
<select id="queryById1" resultMap="joinPlayMap1">
select * from team t inner join player p
on t.teamId=p.teamId where t.teamId=#{id}
</select>
<!--collection: Many-to-many mapping nodes
property: Collection attributes to be queried in entity classes
javaType: Collection Type
ofType:Type of element in collection type
resultMap-->
<resultMap id="joinPlayMap1" type="Team" extends="baseResultMap">
<collection property="playerList1" javaType="arraylist" ofType="Player"
resultMap="com.kkb.mapper.PlayerMapper.baseResultMap"/>
</resultMap>
<!--2,Separate query statements using associated objects-->
<select id="queryById2" resultMap="joinPlayMap2">
select * from team where teamId=#{id}
</select>
<resultMap id="joinPlayMap2" type="Team" extends="baseResultMap">
<collection property="playerList2" javaType="arraylist" ofType="Player"
select="com.kkb.mapper.PlayerMapper.queryByTeamId" column="teamId"/>
</resultMap>
PlayerMapper.xml adds the following:
<select id="queryByTeamId" resultMap="baseResultMap">
select * from player where teamId=#{id}
</select>
9.2.3 Test:
public class TestPlayerMapper {
private PlayerMapper playerMapper= MybatisUtil.getSqlSession().getMapper(PlayerMapper.class);
private TeamMapper teamMapper=MybatisUtil.getSqlSession().getMapper(TeamMapper.class);
@Test
public void test1(){
Player player = playerMapper.queryById(1);
System.out.println(player);
}
@Test
public void test4(){
Player player = playerMapper.queryById3(1);
System.out.println(player);
}
@Test
public void test5(){
Team team = teamMapper.queryById1(1025);
System.out.println(team);
List<Player> playerList = team.getPlayerList1();
System.out.println("Number of players in this team:"+playerList.size());
playerList.forEach(player -> System.out.println(player));
}
}
10. Dynamic SQL
Dynamic SQL is one of the powerful features of MyBatis. If you've used JDBC or other similar frameworks, you should understand how painful it is to stitch SQL statements according to different conditions, such as making sure you don't forget to add the necessary spaces when stitching, and be careful to remove the comma from the last column name in the list. With dynamic SQL, you can get rid of this pain completely.
Using dynamic SQL is not easy, but MyBatis significantly improves the usability of this feature by using a powerful dynamic SQL language that can be used in any SQL mapping statement.
We've learned JSTL before, so dynamic SQL elements can make you feel familiar. In previous versions of MyBatis, it took time to understand a large number of elements. With powerful OGNL-based expressions, MyBatis 3 replaces most of the previous elements, greatly simplifying the element types, and now learning fewer than half the original.
10.1 where tag use in select
Case: Team's Multi-Conditional Query
/*Original multi-condition analysis: all through string splicing in java
String sql="select * from team where 1 = 1 ";
// Fuzzy query if the user enters a name
and teamName like '%?%'
// If the user enters a date, query by date interval
and createTime> ? and createTime< ?
//If a region is entered, query by Region
and location =?";*/
if(vo.getName()!=null && !"".equals(vo.getName().trim())){
sql+=" and teamName like '%"+vo.getName().trim()+"%'";
}
if(vo.getBeginTime()!=null ){
sql+=" and getEndTime>"+vo.getBeginTime();
}
if(vo.getBeginTime()!=null ){
sql+=" and createTime<="+vo.getEndTime();
}
if(vo.getLocation()!=null && !"".equals(vo.getLocation().trim())){
sql+=" and location ="+vo.getLocation().trim();
}
Self-encapsulated query condition class QueryTeamVO.java:
public class QueryTeamVO {
private String name;
private Date beginTime ;
private Date endTime;
private String location;
}
The TeamMapper.java interface adds:
List<Team> queryByVO(QueryTeamVO vo);
TeamMapper.xml mapping file added:
<!--Multiple Conditional Query:
Fuzzy queries can be written in three ways:
Mode 1: and teamName like #{name}, with% when passing parameters, such as vo.setName("%person%")
Mode 2: and teamName like '%${name}%' Not when passing parameters%，for example vo.setName("people")
Mode 3: and teamName like concat(concat('%',#{name},'%') such as vo.setName("person")
concat(str1,str2)Function is used for string splicing-->
<select id="queryByVO" parameterType="QueryVO" resultMap="baseResultMap" useCache="false">
select * from team
<where>
<!-- Fuzzy query if the user enters a name and teamName like '%?%'-->
<if test="name!=null ">
and teamName like concat(concat('%',#{name}),'%')
</if>
<if test="beginTime!=null ">
and createTime>=#{beginTime}
</if>
<if test="endTime!=null ">
and createTime<=#{endTime}
</if>
<if test="location!=null ">
and location=#{location}
</if>
</where>
</select>
Test:
public class TestSQL {
private TeamMapper teamMapper= MybatisUtil.getSqlSession().getMapper(TeamMapper.class);
@Test
public void test1(){
QueryTeamVO vo=new QueryTeamVO();
vo.setName("people");
vo.setEndTime(new Date());
vo.setLocation("Los Angeles, California");
List<Team> teams = teamMapper.queryByVO(vo);
for (Team team : teams) {
System.out.println(team);
}
}
}
10.2 set tag usage in update
10.2.1 Updated original writing
Methods in the TeamMapper.java interface:
int update(Team team);
The corresponding contents of the TeamMapper.xml mapping file:
<update id="update" parameterType="com.kkb.pojo.Team">
update team set teamName=#{teamName},location=#{location},createTime=#{createTime}
where teamId=#{teamId}
</update>
Add a test method to the test class:
@Test
public void test2(){
Team team=new Team();
team.setTeamId(1055);
team.setTeamName("zeyang");
int update = teamMapper.update1(team);
MybatisUtil.getSqlSession().commit();
System.out.println(update);
}
10.2.2 Use set tags to build dynamic SQL statements
Add methods to the TeamMapper.java interface:
int update1(Team team);
The corresponding contents of the TeamMapper.xml mapping file:
<update id="update1" parameterType="com.kkb.pojo.Team">
update team
<set>
<if test="teamName!=null">
teamName=#{teamName},
</if>
<if test="location!=null">
location=#{location},
</if>
<if test="createTime!=null">
createTime=#{createTime},
</if>
</set>
where teamId=#{teamId}
</update>
Test class:
@Test
public void test2(){
Team team=new Team();
team.setTeamId(1055);
team.setTeamName("zeyang");
int update = teamMapper.update1(team);
MybatisUtil.getSqlSession().commit();
System.out.println(update);
}
10.3 for Each Tags
10.3.1 Bulk Add
Add methods to the TeamMapper.java interface:
void addList(List<Team> list);
The corresponding contents of the TeamMapper.xml mapping file:
<!--Bulk Add-->
<insert id="addList" parameterType="arraylist">
INSERT INTO team (teamName,location) VALUES
<!--collection:The set to traverse; Parameters are set types, written directly list
item:Every data in the traversed set
separator: Divide the result of traversal into-->
<foreach collection="list" item="t" separator=",">
(#{t.teamName},#{t.location})
</foreach>
</insert>
Test class:
@Test
public void test3(){
List<Team> list=new ArrayList<>();
for(int i=1;i<=3;i++){
Team team=new Team();
team.setTeamName("zeyang"+i);
team.setLocation("bj"+i);
list.add(team);
}
teamMapper.addList(list);
MybatisUtil.getSqlSession().commit();
}
10.3.2 Bulk Delete
Add methods to the TeamMapper.java interface:
void delList(List<Integer> list);
The corresponding contents of the TeamMapper.xml mapping file:
<delete id="delList" >
delete from team where teamId in
<!--collection:The set to traverse; Parameters are set types, written directly list
item:Every data in the traversed set
separator: Divide the result of traversal into
open="(" close=")": Indicates that the traversal result will be used open close Wrap up-->
<foreach collection="list" item="teamId" separator="," open="(" close=")">
#{teamId}
</foreach>
</delete>
Test class:
@Test
public void test4() {
List<Integer> list = new ArrayList<>();
list.add(1109);
list.add(1110);
list.add(1111);
teamMapper.delList(list);
MybatisUtil.getSqlSession().commit();
}
11. Paging Plugins
11.1 jar dependency
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
11.2 Add Plugin Configuration to the MBatis Global Configuration File
<!--Configure Paging Plugins-->
<plugins>
<!--5.0 Used before version PageHelper,5.0 Use later PageInterceptor-->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!--<property name="reasonable" value="true"/>-->
</plugin>
</plugins>
11.3 Use Plugins
@Test
public void test5() {
// PageHelper.startPage must be adjacent to the query statement and only be valid for the first query statement
PageHelper.startPage(2,5);
List<Team> teams = teamMapper.queryAll();//Query statement cannot end with a semicolon
teams.forEach(team-> System.out.println(team));
PageInfo<Team> info=new PageInfo<>(teams);
System.out.println("Paging information is as follows:");
System.out.println("Current Page:"+info.getPageNum());
System.out.println("PageCount:"+info.getPages());
System.out.println("next page before:"+info.getPrePage());
System.out.println("Next Page:"+info.getNextPage());
System.out.println("navigatepageNums:"+info.getNavigatepageNums());
for (int num : info.getNavigatepageNums()) {
System.out.println(num);
}
}
Part of PageInfo.java source:
public class PageInfo<T> extends PageSerializable<T> {
//Current Page
private int pageNum;
//Number of pages per page
private int pageSize;
//Number of current pages
private int size;
//Since startRow and endRow are not commonly used, here's a specific usage
//You can "display a total of size data from startRow to endRow" on the page
//Line number of the first element of the current page in the database
private int startRow;
//The row number of the last element of the current page in the database
private int endRow;
//PageCount
private int pages;
//next page before
private int prePage;
//next page
private int nextPage;
//Is it the first page
private boolean isFirstPage;
//Is it the last page
private boolean isLastPage;
//Is there a previous page
private boolean hasPreviousPage;
//Is there a next page
private boolean hasNextPage;
//Number of navigation pages
private int navigatePages;
//All navigation page numbers
private int[] navigatepageNums;
//First page on navigation bar
private int navigateFirstPage;
//Last page on navigation bar
private int navigateLastPage;
}
12. Mybatis Cache
12.1 Cache Role
Caching is a function provided by the general ORM framework to improve query efficiency and reduce database pressure. The data that is frequently queried is stored in the cache (in memory). Users do not need to query from disk (relational database file) when querying this data, but query directly from the cache to improve query efficiency and solve high concurrency problems.
MyBatis also has a first-level cache and a second-level cache, and reserves an interface for integrating third-party caches.
Mybatis cache architecture:
12.2 Level 1 Cache: Auto-open, SqlSession level cache
The sqlSession object needs to be constructed when operating on the database, and there is a (memory area) data structure (HashMap) within the object to store the cached data. Cached data areas (HashMaps) between different sqlSessions do not affect each other.
The scope of the first level cache is the same SqlSession, which executes the same sql statement twice in the same sqlSession. When executed for the first time, the data queried in the database is written to the cache (in memory). When executed for the second time, the data retrieved from the cache is no longer queried from the database, which improves query efficiency.
When an sqlSession ends, the first level cache in that sqlSession will no longer exist.
Mybatis turns on the first level cache by default. In-memory (local cache) cannot be turned off. You can call clearCache() to empty the local cache or change the scope of the cache.
12.2.1 Level 1 Cache Analysis
Working principle:
When the user first queries team=1001, first go to the cache to find if there is an object with team=1001; If not, continue sending query statements to the data, and the results of teamId=1001 will be cached after the query succeeds.
When the user initiates the second query for team=1001, first go to the cache to find if there is an object for team=1001, because the data has been stored in the cache since the first query succeeded, and can be retrieved directly from the cache, which means there is no need to send query statements to the database.
If the SqlSession performs a commit with additions and deletions, the corresponding cache area of the SqlSession is emptied entirely to avoid dirty reads.
Prerequisite: SqlSession is not closed.
Test class:
public class TestCache {
private SqlSession sqlSession= MybatisUtil.getSqlSession();
//Test Level 1 Cache: Auto-open, sqlSession level cache
@Test
public void test1() {
Team t1=sqlSession.selectOne("com.kkb.mapper.TeamMapper.queryById",1001);//The first query, first look at the cache, then there is no cache, continue to send query statements to the database
System.out.println(t1);//Data is automatically stored in the cache area after the query is completed
Team t2=sqlSession.selectOne("com.kkb.mapper.TeamMapper.queryById",1001);//The second query because the data is already in the cache and can be obtained directly without sending a query statement
System.out.println(t2);
MybatisUtil.closeSqlSession();//Close connection, cache empty
sqlSession=MybatisUtil.getSqlSession();//Get the connection again, when the cache is empty
Team t3=sqlSession.selectOne("com.kkb.mapper.TeamMapper.queryById",1001);//First query under new connection, definitely send query statement
System.out.println(t3);//Data is automatically stored in the cache area after the query is completed
int num=sqlSession.delete("com.kkb.mapper.TeamMapper.del",10000);
sqlSession.commit();//Cache emptied entirely after submission
System.out.println("Delete results:"+num);
Team t4=sqlSession.selectOne("com.kkb.mapper.TeamMapper.queryById",1001);//Second query, because the cache has been cleared by the last submission, you still need to send a query statement
System.out.println(t4);
sqlSession.close();
}
}
12.2.2 How to empty the cache
- session.clearCache( ) ;
- Execute update;
- session.close( );
- xml configuration flushCache="true";
- rollback；
- commit.
12.3 Level 2 Cache: Mapper Level Cache
Multiple SqlSessions operate on the same Mapper's sql statement. Multiple SqlSessions operate on the database to get data that has a secondary cache area. Multiple SqlSessions can share a secondary cache, which is across SqlSessions.
The secondary cache is shared by multiple SqlSession s and its domain is the same namespace of mapper.
Different sqlSession s execute the same sql statement parameters under the same namespace twice, that is, ultimately execute the same sql statement. The first execution will write the data queried in the database to the cache (in memory) and the second will get the data from the cache, which will no longer query from the database, thus improving query efficiency.
Mybatis does not turn on secondary caching by default and needs to be configured to turn on secondary caching in the setting s global parameter.
If there is data in the cache, you do not need to get it from the database, which greatly improves system performance.
Second level cache schematic:
12.3.1 Use secondary caching steps
The secondary cache is mapper-wide and is not enabled by default.
- Turn on secondary caching in the global configuration file of the MBatis framework
<!--Whether to turn on secondary caching, default false-Do not open, true: open--> <setting name="cacheEnabled" value="true"/>
- Add a cache flag to a Mapper that requires a secondary cache
- Entity class must implement Serializable interface
- Test Level 2 Cache
If two session s are not retrieved from the same Factory, the secondary cache will not work.
/*
Test Level 2 Cache: Prerequisite 3 points: Global profile opens Level 2 Cache; TeamMapper.xml configures the cache; Team entity class implements serialization interface
*/
@Test
public void test2() {
SqlSession sqlSession1 = MybatisUtil.getSqlSession();
Team t1 = sqlSession1.selectOne("com.kkb.mapper.TeamMapper.queryById", 1001);//Look up the cache first, no, database first, write the query to the secondary cache
System.out.println(t1);
MybatisUtil.closeSqlSession();//Close connection, first level cache empty, second level cache exists
SqlSession sqlSession2 = MybatisUtil.getSqlSession();
Team t2 = sqlSession2.selectOne("com.kkb.mapper.TeamMapper.queryById", 1001);//Look up the cache first, yes, get it directly, do not need to query the database
System.out.println(t2);
MybatisUtil.closeSqlSession();//Close connection, first level cache empty, second level cache exists
SqlSession sqlSession3 = MybatisUtil.getSqlSession();
int num = sqlSession3.delete("com.kkb.mapper.TeamMapper.del", 10000);//Delete succeeded
System.out.println("Result of deletion:" + num);
sqlSession3.commit();//Empty secondary cache after submission
MybatisUtil.closeSqlSession();//Close connection, cache empty
SqlSession sqlSession4 = MybatisUtil.getSqlSession();
Team t3 = sqlSession4.selectOne("com.kkb.mapper.TeamMapper.queryById", 1001);Look up the cache first, once there was, but the last submission emptied the cache, so you can only go to the database to query, and write the query to the secondary cache
System.out.println(t3);
MybatisUtil.closeSqlSession();//Close connection, cache empty
}
12.3.2 Disabling secondary caching
Secondary caching can be disabled for SQL that changes frequently.
Setting useCache=false in the corresponding statement in the XML where the second-level cache was started disables the second-level cache for the current Select statement, which means that the SQL statement only needs to query the database each time and does not query the cache.
The default value for useCache is true. For some very important data, do not place it in a secondary cache at all.
Attribute Configuration for 12.3.3 Cache
<cache>
<property name="eviction" value="LRU"/><!--Recycling policy is LRU-->
<property name="flushInterval" value="60000"/><!--Auto refresh interval is 60 S-->
<property name="size" value="1024"/><!--Cache up to 1024 reference objects-->
<property name="readOnly" value="true"/><!--read-only-->
</cache>
Source code:
If you want to share the same cache configuration and instance in the namespace, you can use the cache-ref element to reference another cache.
<cache-ref namespace="com.kkb.mapper.TeamMapper" /> //Reference a cache in the TeamMapper namespace.
13. Reverse Generation Plug-in
Configuration of 13.1 Plugins
Add the following plug-in configuration to the pom.xml file
<!--Reverse Generation Plugin-->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.5</version>
<configuration>
<!--Path to configuration file-->
<configurationFile>src/main/resources/generatorConfig.xml</configurationFile>
<overwrite>true</overwrite>
</configuration>
<dependencies>
<dependency>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-core</artifactId>
<version>1.3.5</version>
</dependency>
</dependencies>
</plugin>
GeneorConfig.xml Content
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<!-- Configuration Generator:Numbered sections need to be modified to their own content -->
<generatorConfiguration>
<!--1,Database Driver jar:Add your own jar Route -->
<classPathEntry
location="D:\repository\mysql\mysql-connector-java\8.0.23\mysql-connector-java-8.0.23.jar" />
<context id="MyBatis" targetRuntime="MyBatis3">
<!--Remove Comments -->
<commentGenerator>
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!--2,Database Connection -->
<jdbcConnection driverClass="com.mysql.cj.jdbc.Driver"
connectionURL="jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT"
userId="root"
password="123456">
</jdbcConnection>
<!-- default false，hold JDBC DECIMAL and NUMERIC Type resolved to Integer;
by true Time handles JDBC DECIMAL and NUMERIC Type resolved to java.math.BigDecimal -->
<javaTypeResolver>
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!--3,Generate Entity Class specifies the package name and generated address (you can customize the address, but the path does not exist and will not be automatically created)
Use Maven Generated in target Directory, created automatically) -->
<javaModelGenerator targetPackage="org.xzk.pojo"
targetProject="src\main\java">
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!--4,generate SQLmapper.xml Mapping File -->
<sqlMapGenerator targetPackage="org.xzk.mapper"
targetProject="src\main\resources">
</sqlMapGenerator>
<!--5,generate Dao(Mapper)Interface File,-->
<javaClientGenerator type="XMLMAPPER"
targetPackage="org.xzk.mapper"
targetProject="src\main\java">
</javaClientGenerator>
<!--6,Which tables to generate(change tableName and domainObjectName Can) -->
<!-- tableName:Table name to generate
enableCountByExample:Count Add to statement where Conditional query, default is true open
enableUpdateByExample:Update Add to statement where Conditional query, default is true open
enableDeleteByExample:Delete Add to statement where Conditional query, default is true open
enableSelectByExample:Select Add to multiple statements where Conditional query, default is true open
selectByExampleQueryId:Select Add to a single object statement where Conditional query, default is true open
-->
<table tableName="Team">
<property name="useActualColumnNames" value="true"/>
</table>
<table tableName="Player">
<property name="useActualColumnNames" value="true"/>
</table>
<table tableName="gameRecord">
<property name="useActualColumnNames" value="true"/>
</table>
<!--<table tableName="Team"
enableCountByExample="false"
enableUpdateByExample="false"
enableUpdateByPrimaryKey="false"
enableDeleteByExample="false"
enableDeleteByPrimaryKey="false"
enableSelectByExample="false"
selectByExampleQueryId="false">
<property name="useActualColumnNames" value="true"/>
</table>-->
</context>
</generatorConfiguration>
Note that it can only be run once and that BUILD SUCCESS will be successful when it is finished.
13.2 Use the multiconditional query method in reverse generation
public class TestGenerator {
private TeamMapper mapper= MybatisUtil.getSqlSession().getMapper(TeamMapper.class);
@Test
public void test1(){
Team team = mapper.selectByPrimaryKey(1001);
System.out.println(team);
}
@Test
public void test2(){
Team team=new Team();
team.setTeamName("lina-test");
team.setLocation("bj");
int i = mapper.insert(team);
MybatisUtil.getSqlSession().commit();
System.out.println(i);
}
@Test
public void test3(){
//Can be understood as a class of services such as multi-criteria, sorting, etc.
TeamExample example=new TeamExample();
//Understand as containers for holding conditions
TeamExample.Criteria criteria = example.createCriteria();
//Add condition to container
criteria.andTeamNameLike("people");
criteria.andTeamIdBetween(1001,1100);
//sort
example.setOrderByClause("teamName desc");
List<Team> teams = mapper.selectByExample(example);
for (Team team : teams) {
System.out.println(team);
}
}
}