什么是Mybatis?
- MyBatis 是一款优秀的持久层框架,它支持自定义 SQL、存储过程以及高级映射。
- MyBatis 免除了几乎所有的 JDBC 代码以及设置参数和获取结果集的工作。
- MyBatis 可以通过简单的 XML 或注解来配置和映射原始类型、接口和 Java POJO(Plain Old Java Objects,普通老式 Java 对象)为数据库中的记录。
简单的增删改查
环境搭建
-
创建数据库
CREATE DATABASE mybatis; USE mybatis; CREATE TABLE `user`( id int, `name` VARCHAR(20), pwd VARCHAR(20) ); INSERT INTO `user`(id,`name`,pwd) VALUES (1,'张三','123456'), (2,'李四','123456'), (3,'王五','123456');
-
创建module
-
导入maven依赖
<!-- 数据库--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.17</version> </dependency> <!-- 单元测试--> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <!-- 连接数据库--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.2</version> </dependency> <!-- 显示日志信息--> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>1.2.17</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-simple</artifactId> <version>1.7.25</version> </dependency>
-
导入文件过滤
这一步是为了能够在项目打包时,mapper包下的xml配置文件不被过滤掉
<resources> <resource> <!--指定配置文件所在的目录--> <directory>src/main/java</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <!--启用上面的过滤文件--> <filtering>true</filtering> </resource> <resource> <!--指定配置文件所在的目录--> <directory>src/main/resources</directory> <includes> <include>**/*.properties</include> <include>**/*.xml</include> </includes> <!--启用上面的过滤文件--> <filtering>true</filtering> </resource> </resources>
-
编写mybatis-config.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"/> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="${driver}"/> <property name="url" value="${url}"/> <property name="username" value="${username}"/> <property name="password" value="${password}"/> </dataSource> </environment> </environments> </configuration>
-
编写db.properties文件
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&failOverReadOnly=false&serverTimezone=UTC username=用户名 password=密码
-
编写实体类
package com.tian.pojo; public class User { private int id; private String name; private String password; public User() { } public User(int id, String name, String password) { this.id = id; this.name = name; this.password = password; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", password='" + password + '\'' + '}'; } }
-
创建UserMapper接口
package com.tian.dao; public interface UserMapper { }
-
创建UserMapper.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.tian.dao.UserMapper"> </mapper>
-
在mybatis-config.xml中设置输出日志信息或设置开启驼峰命名
<!-- 设置配置--> <settings> <!-- 标准的日志工厂--> <setting name="logImpl" value="STDOUT_LOGGING"/> <!-- log4j日志工厂--> <!-- <setting name="logImpl" value="LOG4J"/>--> <!-- 开启驼峰命名自动映射--> <!-- <setting name="mapUnderscoreToCamelCase" value="true"/>--> </settings>
-
在mybatis-config.xml中给实体类起别名
<typeAliases> <typeAlias type="com.tian.pojo.User" alias="User"/> </typeAliases>
-
在mybatis-config.xml中添加Mapper映射
<mappers> <mapper class="com.tian.dao.UserMapper"/> </mappers>
查询
-
在接口UserMapper中添加抽象方法
//查询用户列表 List<User> getUserList();
-
在配置文件UserMapper.xml中添加相应配置
<!-- 查询用户列表--> <select id="getUserList" resultMap="userList"> select * from user; </select> <resultMap id="userList" type="User"> <result property="password" column="pwd"/> </resultMap>
-
编写测试类
import com.tian.dao.UserMapper; import com.tian.pojo.User; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class MybatisTest { @Test public void test01(){ SqlSessionFactory sqlSessionFactory = null; InputStream inputStream; String resource; try { resource = "mybatis-config.xml"; inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); List<User> userList = mapper.getUserList(); for (User user : userList) { System.out.println(user); } sqlSession.close(); } }
-
运行结果
Opening JDBC Connection
Created connection 2056031695.
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7a8c8dcf]
> Preparing: select * from user;
> Parameters:
< Columns: id, name, pwd
< Row: 1, 张三, 123456
<== Row: 2, 李四, 123456
<== Row: 3, 王五, 123456
<== Total: 3
User{id=1, name=‘张三’, password=‘123456’}
User{id=2, name=‘李四’, password=‘123456’}
User{id=3, name=‘王五’, password=‘123456’}
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7a8c8dcf]
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7a8c8dcf]
Returned connection 2056031695 to pool.
添加
-
在接口UserMapper中添加抽象方法
//添加用户 int addUser(User user);
-
在配置文件UserMapper.xml中添加相应配置
<!-- 添加用户--> <insert id="addUser" parameterType="User"> insert into user(id,name,pwd) values(#{id},#{name},#{password}); </insert>
-
编写测试类
@Test public void test02(){ SqlSessionFactory sqlSessionFactory = null; InputStream inputStream; String resource; try { resource = "mybatis-config.xml"; inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int user = mapper.addUser(new User(4, "赵六", "123456")); System.out.println(user); sqlSession.commit(); sqlSession.close(); }
修改
-
在接口UserMapper中添加抽象方法
//修改用户信息 int modifyUser(User user);
-
在配置文件UserMapper.xml中添加相应配置
<!-- 修改用户信息--> <update id="modifyUser" parameterType="User"> update user <set> <if test="name != null">name = #{name},</if> <if test="password != null">pwd = #{password}</if> </set> where id = #{id} </update>
-
编写测试类
@Test public void test03(){ SqlSessionFactory sqlSessionFactory = null; InputStream inputStream; String resource; try { resource = "mybatis-config.xml"; inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int user = mapper.modifyUser(new User(4, "赵六", "111111")); System.out.println(user); sqlSession.commit(); sqlSession.close(); }
删除
-
在接口UserMapper中添加抽象方法
//删除用户信息 int delUser(int id);
-
在配置文件UserMapper.xml中添加相应配置
<!-- 删除用户--> <delete id="delUser"> delete from user where id=#{id} </delete>
-
编写测试类
@Test public void test04(){ SqlSessionFactory sqlSessionFactory = null; InputStream inputStream; String resource; try { resource = "mybatis-config.xml"; inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } SqlSession sqlSession = sqlSessionFactory.openSession(); UserMapper mapper = sqlSession.getMapper(UserMapper.class); int user = mapper.delUser(4); System.out.println(user); sqlSession.commit(); sqlSession.close(); }
复杂查询
多对一
-
创建数据库
create table student ( id int null, name varchar(30) null, tid int null ); insert into student(id,name,tid) values (1,'张三',1), (2,'李四',1), (3,'王五',1), (4,'赵六',2); create table teacher ( id int null, name varchar(30) null ); insert into teacher(id,name) values (1,'老师'), (2,'teacher'),
-
创建module
-
导入maven依赖
-
导入文件过滤
这一步是为了能够在项目打包时,mapper包下的xml配置文件不被过滤掉
-
编写mybatis-config.xml核心配置文件
-
编写db.properties文件
-
编写实体类
package com.tian.pojo; public class Teacher { private int id; private String name; public Teacher() { } public Teacher(int id, String name) { this.id = id; this.name = name; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public String toString() { return "Teacher{" + "id=" + id + ", name='" + name + '\'' + '}'; } }
package com.tian.pojo; public class Student { private int id; private String name; private Teacher teacher; public Student() { } public Student(int id, String name, Teacher teacher) { this.id = id; this.name = name; this.teacher = teacher; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Teacher getTeacher() { return teacher; } public void setTeacher(Teacher teacher) { this.teacher = teacher; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", teacher=" + teacher + '}'; } }
-
创建TeacherMapper和StudentMapper接口
package com.tian.dao; public interface StudentMapper { }
package com.tian.dao; public interface TeacherMapper { }
-
创建TeacherMapper.xml和StudentMapper.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.tian.dao.TeacherMapper"> </mapper>
<?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.tian.dao.StudentMapper"> </mapper>
-
在mybatis-config.xml中设置输出日志信息或设置开启驼峰命名
-
在mybatis-config.xml中给实体类起别名
<!-- 给实体类起别名--> <typeAliases> <typeAlias type="com.tian.pojo.Teacher" alias="Teacher"/> <typeAlias type="com.tian.pojo.Student" alias="Student"/> </typeAliases>
-
在mybatis-config.xml中添加Mapper映射
<mappers> <mapper class="com.tian.dao.TeacherMapper"/> <mapper class="com.tian.dao.StudentMapper"/> </mappers>
-
在StudentMapper接口中添加抽象方法
//查询学生信息以及对应的老师信息 List<Student> getList();
-
在StudentMapper.xml配置文件中添加配置
<select id="getList" resultMap="resMap"> select s.id sid,s.`name` sname,t.id tid,t.`name` tname from student s,teacher t where s.tid = t.id; </select> <resultMap id="resMap" type="Student"> <result column="sid" property="id"/> <result column="sname" property="name"/> <association property="teacher" javaType="Teacher"> <result column="tid" property="id"/> <result column="tname" property="name"/> </association> </resultMap>
-
测试
import com.tian.dao.StudentMapper; import com.tian.pojo.Student; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class mybatisTest { @Test public void test(){ SqlSessionFactory sqlSessionFactory = null; InputStream inputStream; String resource; try { resource = "mybatis-config.xml"; inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } SqlSession sqlSession = sqlSessionFactory.openSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> list = mapper.getList(); for (Student student : list) { System.out.println(student); } sqlSession.close(); } }
一对多
-
创建数据库
-
创建module
-
导入maven依赖
-
导入文件过滤
这一步是为了能够在项目打包时,mapper包下的xml配置文件不被过滤掉
-
编写mybatis-config.xml核心配置文件
-
编写db.properties文件
-
编写实体类
package com.tian.pojo; import java.util.List; public class Teacher { private int id; private String name; private List<Student> students; public Teacher() { } public Teacher(int id, String name, List<Student> students) { this.id = id; this.name = name; this.students = students; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public List<Student> getStudents() { return students; } public void setStudents(List<Student> students) { this.students = students; } @Override public String toString() { return "Teacher{" + "id=" + id + ", name='" + name + '\'' + ", students=" + students + '}'; } }
package com.tian.pojo; public class Student { private int id; private String name; private int tid; public Student() { } public Student(int id, String name, int tid) { this.id = id; this.name = name; this.tid = tid; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getTid() { return tid; } public void setTid(int tid) { this.tid = tid; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", tid=" + tid + '}'; } }
-
创建TeacherMapper和StudentMapper接口
-
创建TeacherMapper.xml和StudentMapper.xml配置文件
-
在mybatis-config.xml中设置输出日志信息或设置开启驼峰命名
-
在mybatis-config.xml中给实体类起别名
-
在mybatis-config.xml中添加Mapper映射
-
在StudentMapper接口中添加抽象方法
//根据id查找老师及其对应的学生信息 Teacher getList(int id);
-
在StudentMapper.xml配置文件中添加配置
<select id="getList" resultMap="resMap"> select t.id tid,t.`name` tname,s.id sid,s.`name` sname from teacher t,student s where s.tid = t.id and t.id = #{id}; </select> <resultMap id="resMap" type="Teacher"> <result column="tid" property="id"/> <result column="tname" property="name"/> <collection property="students" javaType="java.util.List" ofType="Student"> <result column="sid" property="id"/> <result column="sname" property="name"/> </collection> </resultMap>
-
测试
import com.tian.dao.StudentMapper; import com.tian.dao.TeacherMapper; import com.tian.pojo.Student; import com.tian.pojo.Teacher; 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.Test; import java.io.IOException; import java.io.InputStream; import java.util.List; public class mybatisTest { @Test public void test(){ SqlSessionFactory sqlSessionFactory = null; InputStream inputStream; String resource; try { resource = "mybatis-config.xml"; inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } SqlSession sqlSession = sqlSessionFactory.openSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher list = mapper.getList(1); System.out.println(list); sqlSession.close(); } }
生命周期和作用域
生命周期,和作用域是至关重要的,因为错误的使用会导致非常严重的并发问题。
SqlSessionFactoryBuilder:一旦创建了SqlSessionFactory,就不再需要它了
局部变量
mybatis运行流程SqlSessionFactory:
可以想象为:数据库连接池
SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在,没有任何理由丢弃它或重新创建另一个实例。
因此SqlSessionFactory的最佳作用域是应用作用域。
最简单的就是使用 单例模式 或者静态单例模式
SqlSession:
连接到连接池的一个请求!
SqlSession 的实例不是线程安全的,因此是不能被共享的,所以它的最佳的作用域是请求或方法作用域。
用完之后需要赶紧关闭,否则会占用资源
SqlSessionFactory
这里的每一个Mapper,就代表一个具体的业务!