目录
1.MyBatis的概念(mybatis是什么)
MyBatis 是支持普通 SQL查询,存储过程和高级映射的优秀持久层框架。MyBatis 消除了几乎所有的JDBC代码和参数的手工设置以及结果集的检索。MyBatis 使用简单的 XML或注解用于配置和原始映射,将接口和 Java 的POJOs(Plain Ordinary Java Objects,普通的 Java对象)映射成数据库中的记录。
2.为什么要用MyBatis
1.mybatis解决了传统JDBC数据库链接创建,释放频繁造成资源浪费的问题。
方式:在config.xml中配置数据链接池,使用连接池管理数据库链接。
2.sql代码写在代码中造成代码不易维护,sql变动需要改变Java代码。
方式:将sql语句配置在mapper.xml中,与代码分离,提升效率。
3.向sql语句传入参数麻烦,sql语句条件变化较大,占位符赋值难度较大
方式:mybatis自动将Java对象映射至SQL语句
4,对结果集解析麻烦,代码变动大,维护管理特别麻烦
方式:Mybatis自动将sql执行结果映射至java对象,通过resultType定义输出类型
mybatis的使用步骤
使用软件:
idea2020版
Navicat for MySQL
使用的表的资源
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', '张三', '23');
INSERT INTO `users` VALUES ('2', '李四', '34');
INSERT INTO `users` VALUES ('3', '王五', '33');
INSERT INTO `users` VALUES ('4', '孙六', 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', '西游记', '吴承恩', '35', '清华大学出版社');
INSERT INTO `book_info` VALUES ('1002', '神雕侠侣', '金庸', '23', '新华出版社');
INSERT INTO `book_info` VALUES ('1003', '哈利比特', 'J.K.罗琳', '41', '人民出版社');
INSERT INTO `book_info` VALUES ('1004', '安徒生童话', '安徒生', '28', '人民文学出版社');
INSERT INTO `book_info` VALUES ('1005', '青铜葵花', '曹文轩', '32', '清华大学出版社');
INSERT INTO `book_info` VALUES ('1006', '草房子', '曹文轩', '53', '科学出版社');
INSERT INTO `book_info` VALUES ('1007', '红楼梦', '曹雪芹', '45', '人民教育出版社');
INSERT INTO `book_info` VALUES ('1008', '平凡的世界', '路遥', '56', '人民美术出版社');
1.创建项目
项目创建完成
具体配置
在pom.xml文件中插入指定的依赖并且刷新
<?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>
<!--日志文件的依赖-->
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<!--lombok注解(可以自动生成get,set方法)-->
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.22</version>
</dependency>
<dependency>
<!--mysql数据库依赖-->
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<!--mybatis框架依赖-->
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<!--单元测试方法-->
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
<scope>test</scope>
</dependency>
</dependencies>
</project>
单击刷新之后出现相应的依赖
创建mybatis.xml文件
在mybatis.xml文件中写入代码
<?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>
<!--引入属性文件-->
<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" />
<!--数据库连接池:何为数据库连接池!
创建一个池子并且池子中会存放若干个连接对象。当需要连接数据库时,只需要从池子中获取。
当使用完连接对象时 会把该对象放入连接池中。
-->
<dataSource type="POOLED">
<!--${获取属性文件中key对应的值}-->
<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>
<!--引入相关的映射文件-->
<mappers>
<mapper resource="mapper/StuMapper.xml"/>
</mappers>
</configuration>
创建db.properties文件并写入数据库代码
# key=value
jdbc.url=jdbc:mysql://localhost:3306/zkj1?serverTimezone=Asia/Shanghai
jdbc.driverName=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=
上述代码与mybatis.xml中数据库连接池对应
与db.properties创建方式相同创建日志文件log4j.properties文件并写入代码
### 设置###
log4j.rootLogger = debug,stdout,D,E
### 输出信息到控制抬 ###
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
### 输出DEBUG 级别以上的日志到=D://logs/log.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
### 默认输出路径为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
### 输出ERROR 级别以上的日志到=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%n
在resources文件夹下根据命名规范创建XXXMapper.xml映射文件
在java文件夹下根据命名规范创建实体类entity包和dao包
在entity文件夹下创建你所需要的实体类并加入属性
在dao包下创建所需的接口文件,并且写入需要测试的方法
public interface BooksDao {
/*查询book_info全部内容*/
public List<Books> selectAll();
/*根据book_id查询book_info中符合条件的内容*/
public Books selectById(int id);
/*根据book_id和book_name查询book_info中符合条件的内容(多参查询)*/
public Books selectCondition(@Param("name") String name,@Param("id") int id);
/*根据book_id和book_name查询book_info中符合条件的内容(多参查询)
* 涉及到<![CDATA[sql语句]]>转义的使用 有些字符在.xml文件中无法识别,例如'<',这时需要使用<![CDATA[sql语句]]>
* */
public List<Books> selectByIdmax(@Param("min") int min,@Param("max") int max);
/*插入全部字段*/
public int insert(Books books);
/*修改全部字段*/
public int update(Books books);
/*根据book_id删除表中的一行数据*/
public int delete(int id);
}
在BooksMapper.xml中编写所需要的sql语句
步骤:
1.namespace调用dao包中的BooksDao中的方法
2.设置结果集的映射。type类型设置为实体类中的Books类 其中id的值与实体类中的属性名一致,column的值与数据库中的字段名一致
3.编写sql语句,id与BooksDao中的方法名一致,结果集映射为resultMap的id值
<?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调用dao包中的BooksDao中的方法-->
<mapper namespace="com.zhang.dao.BooksDao">
<!--设置结果集的映射。type类型设置为实体类中的Books类
其中id的值与实体类中的属性名一致,column的值与数据库中的字段名一致
-->
<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>
<!--编写sql语句,id与BooksDao中的方法名一致,结果集映射为resultMap的id值-->
<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>
在mybatis.xml文件中引入相关的映射文件
在test的java文件下建立Java测试类写入测试代码
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: 张凯杰
* @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(); //设置connection的提交方式为自动提交。
}
@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("张三");
books.setAuthor("张三");
books.setPrice(33);
books.setPub("人民出版社");
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("骆驼样子");
books.setAuthor("老舍");
books.setPrice(24);
books.setPub("人民出版社");
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);
}
}
以查询全部selectAll举例
扩展:多表联查
在实体类中创建你所需要的表的实体类,这里以order订单表和user用户表举例
多对一:
user实例:
order实例
解释:用户与订单是一对多的关系,一个用户可以对应多个订单,但是一个订单只能对应一个用户,根据order_id查找,所以在order实体类中定义一个user对象。
创建OrderDao接口并定义方法
定义OrderMapper.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">
<!--调用orderdao-->
<mapper namespace="com.zhang.dao.OrderDao">
<!--返回一个order实体对象-->
<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>
建立测试类,输出根据order_id查找到的一组数据
在控制台输出结果为:
一对多:
在实体类中创建你所需要的表的实体类,这里以class班级表和student学生表举例
student实例:
class实例:
定义ClazzMapper.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.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>
编写测试方法
在控制台查看输出结果
嵌套查询
新建UserDao以及UserMapper.xml映射文件,编写方法,以及sql
在OrderMapper.xml文件中编写select语句
<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>
在TestOrder中编写测试方法并输出
动态SQL :
以book表为例
创建实例Books,并创建dao方法
创建BooksMapper.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.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>
编写测试文件
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: 张凯杰
* @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","斗罗大陆");
map.put("author","唐三");
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("金庸");
books.setName("神雕侠侣");
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();
}
}
测试输出结果。