catalogue
1. Concept of mybatis (what is mybatis)
Extension: multi table associated query
1. Concept of mybatis (what is mybatis)
MyBatis supports normal SQL queries, stored procedure And advanced mapping Persistent layer Frame. MyBatis eliminates almost all JDBC Manual setting of codes and parameters and Result set Search for. MyBatis uses simple XML or annotations for configuration and original mapping, and maps interfaces and Java POJOs (Plain Ordinary Java Objects) into records in the database.
2. Why use MyBatis
1.mybatis solves the problem of resource waste caused by frequent release of traditional JDBC database links.
Method: configure the data link pool in config.xml and use the connection pool to manage database links.
2. The sql code is written in the code, which makes the code difficult to maintain. sql changes need to change the Java code.
Method: configure the sql statement in mapper.xml to separate it from the code and improve efficiency.
3. It is troublesome to transfer parameters to sql statements. The conditions of sql statements change greatly, and it is difficult to assign placeholders
Method: mybatis automatically maps Java objects to SQL statements
4. It is troublesome to parse the result set, the code changes greatly, and the maintenance and management is particularly troublesome
Method: Mybatis automatically maps the sql execution results to java objects, and defines the output type through resultType
Steps for using mybatis
Using software:
idea2020 Edition
Navicat for MySQL
Table resources used
users:
CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'Zhang San', '23');
INSERT INTO `users` VALUES ('2', 'Li Si', '34');
INSERT INTO `users` VALUES ('3', 'Wang Wu', '33');
INSERT INTO `users` VALUES ('4', 'Sun Liu', null);orders:
CREATE TABLE `orders` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(255) DEFAULT NULL,
`order_price` double DEFAULT NULL,
`num` int(11) DEFAULT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2345', '23', '2');
INSERT INTO `orders` VALUES ('2', '4456', '44', '5');
INSERT INTO `orders` VALUES ('3', '3456', '33', '4');
INSERT INTO `orders` VALUES ('4', null, '5', '34');
class:
CREATE TABLE `class` (
`c_id` int(11) NOT NULL AUTO_INCREMENT,
`c_name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`c_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of class
-- ----------------------------
INSERT INTO `class` VALUES ('1', 'QY145');
INSERT INTO `class` VALUES ('2', 'QY143');
INSERT INTO `class` VALUES ('3', 'QY142');
student:
CREATE TABLE `student` (
`s_id` int(11) NOT NULL AUTO_INCREMENT,
`s_name` varchar(20) DEFAULT NULL,
`class_id` int(11) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', 'xs_A', '1');
INSERT INTO `student` VALUES ('2', 'xs_B', '1');
INSERT INTO `student` VALUES ('3', 'xs_C', '2');
INSERT INTO `student` VALUES ('4', 'xs_D', '2');
INSERT INTO `student` VALUES ('5', 'xs_E', '3');
INSERT INTO `student` VALUES ('6', 'xs_F', '3');
book_info:
CREATE TABLE `book_info` (
`book_id` int(11) NOT NULL AUTO_INCREMENT,
`book_name` varchar(20) NOT NULL,
`book_author` varchar(20) NOT NULL,
`book_price` int(11) NOT NULL,
`book_pub` varchar(20) NOT NULL,
PRIMARY KEY (`book_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1009 DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of book_info
-- ----------------------------
INSERT INTO `book_info` VALUES ('1001 ',' journey to the West ',' Wu Chengen ',' 35 ',' Tsinghua University Press');
INSERT INTO `book_info` VALUES ('1002 ',' Eagle heroes', 'Jin Yong', '23', 'Xinhua Publishing House');
INSERT INTO `book_info` VALUES ('1003 ',' Harry bit ',' J.K. Rowling ',' 41 ',' people's Publishing House ');
INSERT INTO `book_info` VALUES ('1004 ',' Andersen's Fairy Tales', 'Andersen', '28', 'people's Literature Publishing House');
INSERT INTO `book_info` VALUES ('1005 ',' bronze sunflower ',' Cao Wenxuan ',' 32 ',' Tsinghua University Press');
INSERT INTO `book_info` VALUES ('1006 ',' grass house ',' Cao Wenxuan ',' 53 ',' Science Press');
INSERT INTO `book_info` VALUES ('1007 ',' dream of Red Mansions', 'Cao Xueqin', '45', 'people's education press');
INSERT INTO `book_info` VALUES ('1008 ',' ordinary world ',' Luyao ',' 56 ',' people's Art Publishing House ');
1. Create project
Project creation complete
Specific configuration
Insert the specified dependency in the pom.xml file and refresh
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.zhang</groupId>
<artifactId>mybatis10</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<!--Log file dependency-->
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<!--lombok Annotations (can be generated automatically) get，set Method)-->
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<dependency>
<!--mysql Database dependency-->
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<!--mybatis Framework dependency-->
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<!--Unit test method-->
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>After clicking refresh, the corresponding dependency appears
Create the mybatis.xml file
Write the code in the mybatis.xml 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>
<!--Import properties file-->
<properties resource="db.properties"/>
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="param1" value="value1"/>
</plugin>
</plugins>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC" />
<!--Database connection pool:What is database connection pool!
Create a pool and store several connection objects in the pool. When you need to connect to the database, you only need to get it from the pool.
When the connection object is used up, it will be put into the connection pool.
-->
<dataSource type="POOLED">
<!--${Get properties in file key Corresponding value}-->
<property name="driver" value="${jdbc.driverName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
</environments>
<!--Import related mapping files-->
<mappers>
<mapper resource="mapper/StuMapper.xml"/>
</mappers>
</configuration>Create the db.properties file and write the database code
# key=value jdbc.url=jdbc:mysql://localhost:3306/zkj1?serverTimezone=Asia/Shanghai jdbc.driverName=com.mysql.cj.jdbc.Driver jdbc.username=root jdbc.password=
The above code corresponds to the database connection pool in mybatis.xml
Create the log file log4j.properties in the same way as db.properties and write the code
### set up###
log4j.rootLogger = debug,stdout,D,E
### Output information to control module ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n
### output DEBUG Logs above level to=D://logs/log.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
### The default output path is D://logs/log.log ###
log4j.appender.D.File = D://logs/log.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%n
### output ERROR Logs above level to=D://logs/error.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =D://logs/error.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss} [ %t:%r ] - [ %p ] %m%nIn the resources folder, create the XXXMapper.xml mapping file according to the naming convention
Create entity class entity package and dao package under java folder according to naming rules
Create the entity class you need in the entity folder and add properties
Create the required interface file under dao package and write the method to be tested
public interface BooksDao {
/*Query book_info all contents*/
public List<Books> selectAll();
/*According to book_id query book_ Qualified content in info*/
public Books selectById(int id);
/*According to book_id and book_name query book_ Qualified content in info (multi parameter query)*/
public Books selectCondition(@Param("name") String name,@Param("id") int id);
/*According to book_id and book_name query book_ Qualified content in info (multi parameter query)
* It involves <! [CDATA [SQL statement]] > some characters cannot be recognized in the. xml file, such as' < ', and <! [CDATA [SQL statement]] >
* */
public List<Books> selectByIdmax(@Param("min") int min,@Param("max") int max);
/*Insert all fields*/
public int insert(Books books);
/*Modify all fields*/
public int update(Books books);
/*According to book_id deletes a row of data in the table*/
public int delete(int id);
}Write the required sql statements in BooksMapper.xml
Steps:
1.namespace calls the method in BooksDao in dao package
2. Set the mapping of the result set. The type type is set to the Books class in the entity class, where the value of id is consistent with the attribute name in the entity class, and the value of column is consistent with the field name in the database
3. Write the sql statement. The id is consistent with the method name in BooksDao, and the result set is mapped to the id value of resultMap
<?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 call dao In package BooksDao Methods in-->
<mapper namespace="com.zhang.dao.BooksDao">
<!--Sets the mapping of the result set. type The type is set to in the entity class Books class
among id The value of is consistent with the attribute name in the entity class, column The value of is consistent with the field name in the database
-->
<resultMap id="myMap" type="com.zhang.entity.Books">
<id property="id" column="book_id"/>
<result property="name" column="book_name"/>
<result property="author" column="book_author"/>
<result property="price" column="book_price"/>
<result property="pub" column="book_pub"/>
</resultMap>
<!--to write sql sentence, id And BooksDao The method names in are consistent, and the result set is mapped to resultMap of id value-->
<select id="selectAll" resultMap="myMap">
select * from book_info
</select>
<select id="selectById" resultMap="myMap">
select * from book_info where book_id=#{id}
</select>
<select id="selectCondition" resultMap="myMap">
select * from book_info where book_name=#{name} and book_id=#{id}
</select>
<select id="selectByIdmax" resultMap="myMap">
<![CDATA[select * from book_info where book_price>#{min} and book_price<#{max}]]>
</select>
<insert id="insert">
insert into book_info (book_name,book_author,book_price,book_pub) values (#{name},#{author},#{price},#{pub})
</insert>
<update id="update">
update book_info set book_name=#{name},book_author=#{author},book_price=#{price},book_pub=#{pub} where book_id=#{id}
</update>
<delete id="delete">
delete from book_info where book_id=#{id};
</delete>
</mapper>Introduce the relevant mapping file into the mybatis.xml file
Create a java test class under the java file of test and write the test code
import com.zhang.dao.BooksDao;
import com.zhang.entity.Books;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import org.omg.CORBA.PUBLIC_MEMBER;
import java.io.Reader;
import java.util.List;
/**
* @program: mybatis07
* @description:
* @author: Zhang Kaijie
* @create: 2021-12-01 20:21
**/
public class TestBooks {
private SqlSession session;
@Before
public void before() throws Exception{
Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(resourceAsReader);
session=sqlSessionFactory.openSession(); //Set the submission method of connection to automatic submission.
}
@Test
public void TestfindAll(){
BooksDao booksDao=session.getMapper(BooksDao.class);
List<Books> list=booksDao.selectAll();
System.out.println(list);
}
@Test
public void testFindById(){
BooksDao booksDao=session.getMapper(BooksDao.class);
Books books=booksDao.selectById(1009);
System.out.println(books);
}
@Test
public void testfindcon(){
BooksDao booksDao=session.getMapper(BooksDao.class);
Books books=booksDao.selectCondition("12",1099);
System.out.println(books);
}
@Test
public void testfindid(){
BooksDao booksDao=session.getMapper(BooksDao.class);
List<Books> list=booksDao.selectByIdmax(20,40);
System.out.println(list);
}
@Test
public void testinsert(){
BooksDao booksDao=session.getMapper(BooksDao.class);
Books books=new Books();
books.setName("Zhang San");
books.setAuthor("Zhang San");
books.setPrice(33);
books.setPub("People's Publishing House");
int row=booksDao.insert(books);
System.out.println(row);
}
@Test
public void testUpdate(){
BooksDao booksDao=session.getMapper(BooksDao.class);
Books books=new Books();
books.setName("Camel appearance");
books.setAuthor("Lao She");
books.setPrice(24);
books.setPub("People's Publishing House");
books.setId(1002);
int row=booksDao.update(books);
System.out.println(row);
}
@Test
public void testDelete(){
BooksDao booksDao=session.getMapper(BooksDao.class);
int row=booksDao.delete(1004);
System.out.println(row);
}
}
For example, query all selectAll
Extension: multi table associated query
Create the entity class of the table you need in the entity class. Here, take the order table and user table as examples
Many to one:
user instance:
order instance
Explanation: the relationship between user and order is one to many. A user can correspond to multiple orders, but an order can only correspond to one user_ ID lookup, so define a user object in the order entity class.
Create the OrderDao interface and define the method
Define the OrderMapper.xml mapping 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">
<!--call orderdao-->
<mapper namespace="com.zhang.dao.OrderDao">
<!--Return a order Entity object-->
<resultMap id="my01" type="com.zhang.entity.Order">
<id property="id" column="order_id"/>
<result property="no" column="order_no"/>
<result property="price" column="order_price"/>
<result property="num" column="num"/>
<association property="users" javaType="com.zhang.entity.Users" autoMapping="true">
<id property="id" column="id"/>
</association>
</resultMap>
<select id="selectById" resultMap="my01">
select * from orders o join users u on o.order_id=u.id where o.order_id=#{id}
</select>
</mapper>Create a test class and output according to order_ A set of data found by ID
The output result on the console is:
One to many:
Create the entity class of the table you need in the entity class. Here, take the class table and student table as examples
student instance:
class instance:
Define the ClazzMapper.xml mapping 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">
<mapper namespace="com.zhang.dao.ClazzDao">
<resultMap id="my03" type="com.zhang.entity.Clazz">
<id column="c_id" property="cid"/>
<result property="cname" column="c_name"/>
<collection property="students" ofType="com.zhang.entity.Student" autoMapping="true">
<id property="id" column="s_id"/>
<result property="name" column="s_name"/>
<result property="classId" column="class_id"/>
</collection>
</resultMap>
<select id="findByid" resultMap="my03">
select * from class c join student s on c.c_id=s.class_id where c_id=#{id}
</select>
</mapper>Writing test methods
View the output results in the console
nested queries
Create a new UserDao and UserMapper.xml mapping file, write methods, and sql
Write a select statement in the OrderMapper.xml file
<resultMap id="my02" type="com.zhang.entity.Order">
<id property="id" column="order_id"/>
<result property="no" column="order_no"/>
<result property="price" column="order_price"/>
<result property="num" column="num"/>
<association property="users" javaType="com.zhang.entity.Users" autoMapping="true"
column="uid" select="com.zhang.dao.UserDao.selectById">
</association>
</resultMap>
<select id="selectById2" resultMap="my02">
select * from orders where order_id=#{oid}
</select>Write the test method in TestOrder and output it
Dynamic SQL:
Take the book table as an example
Create the instance Books and create the dao method
Create a BooksMapper.xml mapping 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">
<mapper namespace="com.zhang.dao.BookDao">
<resultMap id="map" type="com.zhang.entity.Books">
<id property="id" column="book_id"/>
<result property="name" column="book_name"/>
<result property="author" column="book_author"/>
<result property="price" column="book_price"/>
<result property="pub" column="book_pub"/>
</resultMap>
<update id="update">
update book_info
<set>
<if test="name!=null and name!=''" >
book_name=#{name},
</if>
<if test="author!=null and author!=''">
book_author=#{author}
</if>
<if test="pub!=null and pub!=''">
book_pub=#{pub}
</if>
<if test="price!=null">
book_price=#{price}
</if>
</set>
where book_id=#{id}
</update>
<delete id="batchDelete">
delete from book_info where book_id in
<foreach collection="ids" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</delete>
<select id="findByCondition" resultMap="map">
select * from book_info
<where>
<if test="bookname!=null and bookname!=''">
and book_name=#{bookname}
</if>
<if test="author!=null and author!=''">
and book_author=#{author}
</if>
</where>
</select>
<select id="findByCondotion2" resultMap="map">
select * from book_info
<where>
<choose>
<when test="bookname!=null and bookname!=''">
and book_name=#{bookname}
</when>
<when test="author!=null and author!=''">
and book_author=#{author}
</when>
<otherwise>
and book_price>25
</otherwise>
</choose>
</where>
</select>
</mapper>Write test documents
import com.zhang.dao.BookDao;
import com.zhang.entity.Books;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @program: mybatis08
* @description:
* @author: Zhang Kaijie
* @create: 2021-12-02 18:35
**/
public class TestBook {
private SqlSession session;
@Before
public void before() throws Exception{
Reader resourceAsReader = Resources.getResourceAsReader("mybatis.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(resourceAsReader);
session=sqlSessionFactory.openSession();
}
@Test
public void testSelect01(){
BookDao bookDao=session.getMapper(BookDao.class);
Map<String,Object> map=new HashMap<String, Object>();
map.put("bookname","Douluo continent");
map.put("author","Tang San");
List<Books> list=bookDao.findByCondition(map);
}
@Test
public void testSelect02(){
BookDao bookDao=session.getMapper(BookDao.class);
Map<String,Object> map=new HashMap<String, Object>();
List<Books> list=bookDao.findByCondotion2(map);
}
@Test
public void testupdate(){
BookDao bookDao=session.getMapper(BookDao.class);
Books books=new Books();
books.setAuthor("Jin Yong");
books.setName("Eagle Warrior");
books.setId(1002);
bookDao.update(books);
session.commit();
}
@Test
public void testUpdate02(){
BookDao bookDao=session.getMapper(BookDao.class);
int[] ids={1001,1002,1003};
bookDao.batchDelete(ids);
session.commit();
}
}
Test output results.