Mybatis实现数据库的增删改查
接口StudentDao
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Student;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface StudentDao {
@Select("select * from student;")
List<Student> queryAll();
List<Student> queryResultMap();
}
接口TeacherDao
当传的参数超过一个时,要用@param(xxx)给参数命名,和xml中的#{xxx}一致。
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface TeacherDao {
List<Teacher> queryAll();
/**
* 多参数查询
* @param id
* @param age
* @return
*/
List<Teacher> queryByIdAndAge(@Param("tid") int id, @Param("age") int age);
int insertTeacher(Teacher teacher);
int insertTeacherByParam(@Param("tname") String tname, @Param("age") int age);
Teacher queryById(int id);
int updateTeacherById(Teacher teacher);
int deleteTeacherById(int id);
}
外部数据库配置文件jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/lanou?characterEncoding=utf8
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
taotao.jdbc.url=jdbc:mysql://localhost:3306/taotao?characterEncoding=utf8
taotao.jdbc.driver=com.mysql.jdbc.Driver
taotao.jdbc.user=root
taotao.jdbc.password=root
Mybatis的全局配置文件mybatis_conf.xml
主要用于配置Mybatis的运行环境(事务管理器、数据源等)。
<?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文件,只能引入一个 -->
<properties resource="jdbc.properties" />
<settings>
<setting name="mapUnderscoreToCamelCase" value="false"/>
</settings>
<!-- 定义类型别名(全局),在所有的Mapper.xml中都可以用 -->
<typeAliases>
<typeAlias type="com.lanou3g.mybatis.bean.Teacher" alias="Teacher" />
<typeAlias type="com.lanou3g.mybatis.bean.Student" alias="Student" />
</typeAliases>
<!-- 配置多套环境的数据库连接参数(如:开发环境、生产环境) -->
<environments default="lanou">
<environment id="lanou">
<!-- 事务管理器:
MANAGED: 这个配置就是告诉mybatis不要干预事务,具体行为依赖于容器本身的事务处理逻辑。
JDBC: 这个配置就是直接使用了 JDBC 的提交和回滚设置,它依赖于从数据源得到的连接来管理事务作用域。
-->
<transactionManager type="MANAGED"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
<environment id="taotao">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="${taotao.jdbc.driver}"/>
<property name="url" value="${taotao.jdbc.url}"/>
<property name="username" value="${taotao.jdbc.user}"/>
<property name="password" value="${taotao.jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper class="com.lanou3g.mybatis.dao.StudentDao" />
<mapper resource="mapper/TeacherMapper.xml" />
<mapper resource="mapper/StudentMapper.xml" />
</mappers>
</configuration>
SQL配置文件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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.StudentDao">
<resultMap id="stu" type="Student">
<result property="sname" column="sname" />
<result property="nickName" column="nick_name" />
</resultMap>
<select id="queryResultMap" resultMap="stu">
select * from student;
</select>
</mapper>
SQL配置文件TeacherMapper.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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">
<!-- 此处的id是查询语句的名称,对应接口中的方法名 -->
<select id="queryAll" resultType="Teacher">
select * from teacher;
</select>
<!-- 带一个简单类型的参数, 这种情况下parameterType属性可以省略,mybatis可以自动推断出类型 -->
<select id="queryById" parameterType="int" resultType="Teacher">
select * from teacher where id = #{id};
</select>
<!-- 带两个参数,需要在接口中通过@Param注解指定名称(因为编译时参数名不会保留) -->
<select id="queryByIdAndAge" resultType="Teacher">
select * from teacher where id = #{id} and age <= #{age};
</select>
<!-- insert、update、delete的返回值都是int(影响行数) -->
<!-- 自定义类型参数,通过#{属性名}可以直接获取引入类型参数的属性值 -->
<insert id="insertTeacher" parameterType="Teacher">
insert into teacher(tname) values (#{tname});
</insert>
<insert id="insertTeacherByParam">
insert into teacher(tname, age) values (#{tname}, #{age});
</insert>
<update id="updateTeacherById" parameterType="Teacher">
update teacher set tname = #{tname}, age = #{age} where id = #{id}
</update>
<delete id="deleteTeacherById">
delete from teacher where id = #{id};
</delete>
</mapper>
工具类MyBatisTools.java
创建工具类用来获取sqlSession。
/**
* 封装Mybatis初始化操作
* 支持创建多env sqlSessionFactory
* 整个应用生命周期内相同env的sqlSessionFactory对象只有一个
*/
@Slf4j
public class MyBatisTools {
private static ConcurrentHashMap<String, SqlSessionFactory> factoryMap = new MyConcurrentHashMap();
private static MyBatisTools myBatisTools;
private MyBatisTools() {}
public static MyBatisTools getInstance() {
if(myBatisTools == null) {
synchronized (MyBatisTools.class) {
if(myBatisTools == null) {
myBatisTools = new MyBatisTools();
}
}
}
log.debug("当前一共有: " + factoryMap.size() +"个SqlSessionFactory实例");
log.debug("他们分别是: " + factoryMap);
return myBatisTools;
}
public SqlSessionFactory getSessionFactory(String env) {
try {
// 1. 读入配置文件
InputStream in = Resources.getResourceAsStream("mybatis_conf.xml");
// 2. 构建SqlSessionFactory(用于获取sqlSession)
SqlSessionFactory sessionFactory = null;
synchronized (factoryMap) {
if(factoryMap.containsKey(env)) {
return factoryMap.get(env);
} else {
sessionFactory = new SqlSessionFactoryBuilder().build(in, env);
factoryMap.put(env, sessionFactory);
}
}
return sessionFactory;
} catch (Exception e) {
log.error("初始化SqlSessionFactory失败", e);
return null;
}
}
public SqlSession openSession() {
return getSessionFactory(null).openSession();
}
public SqlSession openSession(boolean autoCommit) {
return getSessionFactory(null).openSession(autoCommit);
}
public SqlSession openSession(ExecutorType executorType, boolean autoCommit) {
return getSessionFactory(null).openSession(executorType, autoCommit);
}
}
/**
* 继承原生ConcurrentHashMap,处理null key问题
*/
class MyConcurrentHashMap extends ConcurrentHashMap {
@Override
public Object put(Object key, Object value) {
if(key == null) {
key = "null";
}
return super.put(key, value);
}
@Override
public boolean containsKey(Object key) {
if(key == null) {
key = "null";
}
return super.containsKey(key);
}
@Override
public Object get(Object key) {
if(key == null) {
key = "null";
}
return super.get(key);
}
}
测试类AppTest.java
/**
* Unit test for simple App.
*/
@Slf4j
public class AppTest {
TeacherDao teacherDao = null;
StudentDao studentDao = null;
CategoryDao categoryDao = null;
@Before
public void setUp() {
categoryDao = MyBatisTools.getInstance().getSessionFactory("taotao").openSession().getMapper(CategoryDao.class);
studentDao = MyBatisTools.getInstance().openSession().getMapper(StudentDao.class);
teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
}
/**
* 练习查询多个库(用到了多环境配置)
*/
@Test
public void testMultiDatabase() {
// 查询其他数据库中的数据
System.out.println(categoryDao.queryAll());
}
@Test
public void testQueryAll() {
// 查询学生表
List<Student> studentList = studentDao.queryAll();
// List<Student> studentList = studentDao.queryResultMap();
log.info("studentList: " + studentList);
// 查询教师表
// TeacherDao teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
// List<Teacher> teacherList = teacherDao.queryAll();
// log.info("teacherList: " + teacherList);
}
@Test
public void testInsert() {
// 新增Teacher表
System.out.println("--------------插入前:");
List<Teacher> teacherList = teacherDao.queryAll();
System.out.println(teacherList);
int ret = teacherDao.insertTeacher(new Teacher("铜赛赛"));
log.info("影响的行数: " + ret);
// 比较low的写法(不推荐)
//int ret = teacherDao.insertTeacherByParam("哈哈哥", 99);
//log.info("影响的行数: " + ret);
System.out.println("--------------插入后:");
teacherList = teacherDao.queryAll();
System.out.println(teacherList);
}
@Test
public void testQueryById() {
Teacher teacher = teacherDao.queryById(6);
System.out.println(teacher);
}
/**
* 多个参数查询语句
*/
@Test
public void testQueryByIdAndAge() {
List<Teacher> teacherList = teacherDao.queryByIdAndAge(6, 99);
log.info("查询结果:" + teacherList);
}
@Test
public void testUpdate() {
Teacher teacher = new Teacher();
teacher.setId(6);
teacher.setAge(99);
teacher.setTname("托尼托尼乔巴老师");
int rows = teacherDao.updateTeacherById(teacher);
log.info("更新行数:" + rows);
}
@Test
public void testDelete() {
int rows = teacherDao.deleteTeacherById(30);
log.info("删除行数:" + rows);
}
}
Mybatis多表查询及动态SQL
多表查询
一对一
数据库里有两个表book,booktype 。book表有如下字段:id,bname,btype,author,author_gender,price,description。booktype表有如下字段:id,tname。
创建与数据库表对应的实体类Book.java和BookType.java
一个数据库表对应的类的属性是另一个数据库表对应的类的对象
如:book类中有一个booktype属性
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
@Setter
@Getter
public class Book {
private Integer id;
private String bname;
private BookType bookType;
private String author;
private String authorGender;
private Float price;
private String description;
@Override
public String toString() {
return "Book{" +
"id=" + id +
", bname='" + bname + '\'' +
", bookType={" + bookType +
"}, author='" + author + '\'' +
", authorGender='" + authorGender + '\'' +
", price=" + price +
", description='" + description + '\'' +
"}\n";
}
}
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class BookType {
private Integer id;
private String tname;
@Override
public String toString() {
return "BookType{" +
"id=" + id +
", tname='" + tname + '\'' +
'}';
}
}
创建dao层接口BookMapper.java
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Book;
import java.util.List;
public interface BookMapper {
List<Book> queryBooks();
}
SQL配置文件BookMapper.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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.BookMapper">
<resultMap id="book" type="com.lanou3g.mybatis.bean.Book">
<id column="bid" property="id" />
<result column="bname" property="bname" />
<result column="author" property="author" />
<result column="author_gender" property="authorGender" />
<result column="price" property="price" />
<result column="description" property="description" />
<association property="bookType" javaType="com.lanou3g.mybatis.bean.BookType">
<id column="bt_id" property="id" />
<result column="tname" property="tname" />
</association>
</resultMap>
<select id="queryBooks" resultMap="book">
select b.*,bt.*, b.id bid, bt.id bt_id from book b, booktype bt where b.btype = bt.id;
</select>
</mapper>
一对多
一个数据库表对应的类的属性是另一个数据库表对应的类的对象的集合
如:一个老师对应多个学生
创建实体类Student.java和Teacher.java
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class Student {
private Integer id;
private String sname;
private String nickName;
public Student() {}
public Student(String sname, String nickName) {
this.sname = sname;
this.nickName = nickName;
}
@Override
public String toString() {
return "{" +
"id='" + id + '\'' +
"sname='" + sname + '\'' +
", nickName='" + nickName + '\'' +
"}\n";
}
}
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
import java.util.List;
@Getter
@Setter
public class Teacher {
private Integer id;
private String tname;
private Integer age;
private List<Student> students;
public Teacher (){}
public Teacher(String tname) {
this.tname = tname;
}
@Override
public String toString() {
return "{" +
"id:" + id +
", tname:'" + tname + '\'' +
", age:'" + age + '\'' +
", 所教学生:[" + students + '\'' +
"]}\n";
}
}
SQL配置文件TeacherMapper.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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">
<resultMap id="teacher" type="Teacher">
<id column="t_id" property="id" />
<result column="tname" property="tname" />
<result column="age" property="age" />
<collection property="students" ofType="Student">
<id column="s_id" property="id" />
<result property="sname" column="sname" />
<result property="nickName" column="nick_name" />
</collection>
</resultMap>
<select id="queryTeacherCascade" resultMap="teacher">
SELECT t.*,s.*,t.id t_id,s.id s_id from teacher t, student s WHERE t.id = s.tid;
</select>
</mapper>
动态SQL
什么是动态SQL
MyBatis的动态SQL是基于OGNL的表达式的。它对SQL语句进行灵活的操作,通过表达式判断来实现对SQL的灵活拼接、组装。
MyBatis拼接SQL语句主要通过以下标签:if,where,choose(when,otherwise),trim,set,foreach。
动态SQL的简单示例
<select id="querStudentByCondition" resultMap="stu">
select *from student
<where>
<if test="sno!=null">
sno =#{sno}
</if>
<if test="sname!=null">
and sname =#{sname}
</if>
<if test="ssex!=null">
and ssex =#{ssex}
</if>
<if test="sage!=null">
and sage =#{sage}
</if>
</where>
</select>
<select id="queryStudentByChooseWhen" resultMap="stu">
select * from Student
<where>
<choose>
<when test="sno != null">
and sno = #{sno}
</when>
<when test="sname != null">
and sname = #{sname}
</when>
<when test="ssex != null">
and ssex= #{ssex}
</when>
<when test="sage != null">
and sage = #{sage}
</when>
<otherwise>
and sage = 16
</otherwise>
</choose>
</where>
</select>
<update id="updateBySno" parameterType="Student">
update Student
<set>
<!--<trim prefix="set" suffixOverrides=","> -->
<if test="sname != null">
sname = #{sname}
</if>
<if test="sage != null">
sage = #{sage}
</if>
<!-- </trim>-->
</set>
where sno = #{sno}
</update>
</mapper>
Mybatis批量插入数据到数据库
常规方式(foreach)批量插入
数据库里的Teacher表有如下字段:id,tname,age。
创建与数据库表对应的实体类Teacher.java
package com.lanou3g.mybatis.bean;
import lombok.Getter;
import lombok.Setter;
import java.util.List;
@Getter
@Setter
public class Teacher {
private Integer id;
private String tname;
private Integer age;
public Teacher (){}
public Teacher(String tname) {
this.tname = tname;
}
@Override
public String toString() {
return "{" +
"id:" + id +
", tname:'" + tname + '\'' +
", age:'" + age + '\'' +
"]}\n";
}
}
dao层接口TeacherDao
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Teacher;
import java.util.List;
public interface TeacherDao {
int batchInsertByNormal(List<Teacher> teacherList);
}
数据库相关外部配置文件jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/yanfa5?characterEncoding=utf8
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
Mybatis的全局配置文件mybatis_conf.xml
主要用于配置Mybatis的运行环境(事务管理器、数据源等)。
<?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文件,只能引入一个 -->
<properties resource="jdbc.properties" />
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<!-- 定义类型别名(全局),在所有的Mapper.xml中都可以用 -->
<typeAliases>
<typeAlias type="com.lanou3g.mybatis.bean.Teacher" alias="Teacher" />
</typeAliases>
<!-- 配置多套环境的数据库连接参数(如:开发环境、生产环境) -->
<environments default="yanfa5">
<environment id="yanfa5">
<!-- 事务管理器:
MANAGED: 这个配置就是告诉mybatis不要干预事务,具体行为依赖于容器本身的事务处理逻辑。
JDBC: 这个配置就是直接使用了 JDBC 的提交和回滚设置,它依赖于从数据源得到的连接来管理事务作用域。
-->
<transactionManager type="MANAGED"/>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/TeacherMapper.xml" />
</mappers>
</configuration>
SQL配置文件TeacherMapper.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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">
<resultMap id="teacher" type="Teacher">
<id column="t_id" property="id" />
<result column="tname" property="tname" />
<result column="age" property="age" />
</resultMap>
<!-- 常规方式批量插入 -->
<insert id="batchInsertByNormal" parameterType="Teacher">
insert into teacher (tname, age) values
<!--open属性表示在拼接的SQL语句前加"xx",separator属性表示在拼接的SQL语句之间加"xx",close属性表示在拼接的SQL语句后加"xx"-->
<foreach collection="list" item="teacher" separator="," close=";">
(#{teacher.tname}, #{teacher.age})
</foreach>
</insert>
</mapper>
工具类MyBatisTools.java
封装Mybatis初始化操作
package com.lanou3g.mybatis;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.BatchResult;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
/**
* 封装Mybatis初始化操作
* 支持创建多env sqlSessionFactory
* 整个应用生命周期内相同env的sqlSessionFactory对象只有一个
*/
@Slf4j
public class MyBatisTools {
private static ConcurrentHashMap<String, SqlSessionFactory> factoryMap = new MyConcurrentHashMap();
private static MyBatisTools myBatisTools;
private MyBatisTools() {}
public static MyBatisTools getInstance() {
if(myBatisTools == null) {
synchronized (MyBatisTools.class) {
if(myBatisTools == null) {
myBatisTools = new MyBatisTools();
}
}
}
log.debug("当前一共有: " + factoryMap.size() +"个SqlSessionFactory实例");
log.debug("他们分别是: " + factoryMap);
return myBatisTools;
}
public SqlSessionFactory getSessionFactory() {
return getSessionFactory(null);
}
public SqlSessionFactory getSessionFactory(String env) {
try {
// 1. 读入配置文件
InputStream in = Resources.getResourceAsStream("mybatis_conf.xml");
// 2. 构建SqlSessionFactory(用于获取sqlSession)
SqlSessionFactory sessionFactory = null;
synchronized (factoryMap) {
if(factoryMap.containsKey(env)) {
return factoryMap.get(env);
} else {
sessionFactory = new SqlSessionFactoryBuilder().build(in, env);
factoryMap.put(env, sessionFactory);
}
}
return sessionFactory;
} catch (Exception e) {
log.error("初始化SqlSessionFactory失败", e);
return null;
}
}
public SqlSession openSession() {
return getSessionFactory(null).openSession();
}
public SqlSession openSession(boolean autoCommit) {
return getSessionFactory(null).openSession(autoCommit);
}
public SqlSession openSession(ExecutorType executorType, boolean autoCommit) {
return getSessionFactory(null).openSession(executorType, autoCommit);
}
public static int flushStatement(SqlSession sqlSession) {
int effectRows = 0;
List<BatchResult> batchResults = sqlSession.flushStatements();
if(batchResults == null || batchResults.size() < 1) {
return effectRows;
}
int[] effectArr = batchResults.get(0).getUpdateCounts();
for(int effectRow : effectArr) {
effectRows += effectRow;
}
return effectRows;
}
}
/**
* 继承原生ConcurrentHashMap,处理null key问题
*/
class MyConcurrentHashMap extends ConcurrentHashMap {
@Override
public Object put(Object key, Object value) {
if(key == null) {
key = "null";
}
return super.put(key, value);
}
@Override
public boolean containsKey(Object key) {
if(key == null) {
key = "null";
}
return super.containsKey(key);
}
@Override
public Object get(Object key) {
if(key == null) {
key = "null";
}
return super.get(key);
}
}
入口类AppTest.java
@Slf4j
public class AppTest {
TeacherDao teacherDao = null;
List<Teacher> testData = new ArrayList<>();
String[] tnameArr = new String[]{"四火", "四水", "四金", "四木", "四土"};
<!--@Before注解表示下面的test单元测试在执行之前都会先执行添加@Before注解的方法-->
@Before
public void setUp() {
teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
Random random = new Random();
// 构造测试数据
for(int i = 0; i < 10000; i++) {
Teacher teacher = new Teacher();
int idx = random.nextInt(tnameArr.length);
teacher.setTname(tnameArr[idx] +"_"+ (i + 1));
teacher.setAge(i+1);
testData.add(teacher);
}
}
/**
* 常规批量插入。(通过foreach,生成很长的SQL)
*/
@Test
public void testBatchInsertByNormal() {
long start = System.currentTimeMillis();
int rows = teacherDao.batchInsertByNormal(testData);
log.info("插入数据行数: " + rows+", 耗时: " + (System.currentTimeMillis() - start));
}
}
使用ExecutorType.BATCH方式执行批量操作
创建与数据库表对应的实体类Teacher.java(与foreach方式批量插入的实体类相同)
dao层接口TeacherDao
package com.lanou3g.mybatis.dao;
import com.lanou3g.mybatis.bean.Teacher;
import java.util.List;
public interface TeacherDao {
int insertTeacher(Teacher teacher);
}
数据库相关外部配置文件jdbc.properties(与foreach方式批量插入的配置文件相同)
Mybatis的全局配置文件mybatis_conf.xml(与foreach方式批量插入的配置文件相同)
主要用于配置Mybatis的运行环境(事务管理器、数据源等)。
SQL配置文件TeacherMapper.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">
<!-- namespace对应空Dao接口的全名 -->
<mapper namespace="com.lanou3g.mybatis.dao.TeacherDao">
<resultMap id="teacher" type="Teacher">
<id column="t_id" property="id" />
<result column="tname" property="tname" />
<result column="age" property="age" />
</resultMap>
<insert id="insertTeacher" parameterType="Teacher">
insert into teacher(tname,age) values (#{tname},#{age});
</insert>
</mapper>
工具类MyBatisTools.java(与foreach方式批量插入的工具类相同)
作用:封装Mybatis初始化操作
入口类AppTest.java
@Slf4j
public class AppTest {
TeacherDao teacherDao = null;
List<Teacher> testData = new ArrayList<>();
String[] tnameArr = new String[]{"四火", "四水", "四金", "四木", "四土"};
@Before
public void setUp() {
teacherDao = MyBatisTools.getInstance().openSession().getMapper(TeacherDao.class);
Random random = new Random();
// 构造测试数据
for(int i = 0; i < 10000; i++) {
Teacher teacher = new Teacher();
int idx = random.nextInt(tnameArr.length);
teacher.setTname(tnameArr[idx] +"_"+ (i + 1));
teacher.setAge(i+1);
testData.add(teacher);
}
}
/**
* 使用ExecutorType.BATCH方式执行批量操作
*/
@Test
public void testBatchInsertByExecutorType() {
SqlSessionFactory factory = MyBatisTools.getInstance().getSessionFactory();
SqlSession sqlSession = factory.openSession(ExecutorType.BATCH, false);
TeacherDao teacherDao = sqlSession.getMapper(TeacherDao.class);
long start = System.currentTimeMillis();
int rows = 0;
int batchSize = 100;
int count = 0;
for(Teacher teacher : testData) {
teacherDao.insertTeacher(teacher);
count ++;
if(count % batchSize == 0) {
rows+=MyBatisTools.flushStatement(sqlSession);
}
}
sqlSession.flushStatements();
sqlSession.commit();
sqlSession.close();
log.info("插入数据行数: " + rows+", 耗时: " + (System.currentTimeMillis() - start));
}
}
Mybatis和Spring的整合
什么是 MyBatis-Spring?
MyBatis-Spring 会帮助你将 MyBatis 代码无缝地整合到 Spring 中。它将允许 MyBatis 参与到 Spring 的事务管理之中,创建映射器 mapper 和 SqlSession 并注入到 bean 中,以及将 Mybatis 的异常转换为 Spring 的 DataAccessException。最终,可以做到应用代码不依赖于 MyBatis,Spring 或 MyBatis-Spring。
MyBatis和Spring整合的操作步骤
第一步 添加依赖
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.1</version>
</dependency>
第二步 在Spring中管理SqlSessionFactory
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!-- 注入数据源 -->
<property name="dataSource" ref="dataSource"/>
<!-- 设置mybatis核心配置文件路径(可选) -->
<property name="configLocation" value="classpath:/mybatis-config.xml" />
<!-- 配置mybatis xml映射文件位置(如果Mapper是用注解配置的,这里就不用设置此属性了) -->
<property name="mapperLocations" value="classpath:/mappers/*" />
</bean>
第三步 用Spring管理事务
和单独使用Spring时一样, 配置Spring的声明式事务就可以了,mybatis会自动参与到spring的事务中
Mybatis-Spring的一个小案例
在pom.xml中引入依赖
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.1.5.RELEASE</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.6</version>
<scope>provided</scope>
</dependency>
</dependencies>
数据库的book表有以下字段:id,bname,btype,author,author_gender,price,description
创建与数据库book表对应的实体类Book.java
package com.lanou3g.bean;
import lombok.Getter;
import lombok.Setter;
@Getter
@Setter
public class Book {
private Integer id;
private String bname;
private Integer btype;
private String author;
private Integer authorGender;
private Float price;
private String description;
@Override
public String toString() {
return "Book{" +
"id=" + id +
", bname='" + bname + '\'' +
", btype=" + btype +
", author='" + author + '\'' +
", authorGender=" + authorGender +
", price=" + price +
", description='" + description + '\'' +
"}\n";
}
}
创建接口BookMapper.java
package com.lanou3g.dao;
import com.lanou3g.bean.Book;
import org.springframework.stereotype.Component;
import java.util.List;
@Component
public interface BookMapper {
int deleteByPrimaryKey(Integer id);
int insert(Book record);
Book selectByPrimaryKey(Integer id);
List<Book> selectAll();
int updateByPrimaryKey(Book record);
}
数据库相关外部配置文件jdbc.properties
jdbc.url=jdbc:mysql://localhost:3306/yanfa5?characterEncoding=utf8
jdbc.driver=com.mysql.jdbc.Driver
jdbc.user=root
jdbc.password=root
配置数据源,连接Spring和Mybatis
applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mybatis="http://mybatis.org/schema/mybatis-spring"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://mybatis.org/schema/mybatis-spring
http://mybatis.org/schema/mybatis-spring.xsd">
<!--引入文件-->
<context:property-placeholder location="classpath:jdbc.properties"/>
<!--开启注解扫描-->
<context:component-scan base-package="com.lanou3g"/>
<!--扫描接口,spring自动管理-->
<mybatis:scan base-package="com.lanou3g.dao"/>
<!--配置数据源-->
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="url" value="${jdbc.url}"/>
<property name="driverClassName" value="${jdbc.driver}"/>
<property name="username" value="${jdbc.user}"/>
<property name="password" value="${jdbc.password}"/>
</bean>
<!--连接spring和mybatis-->
<bean class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="mapperLocations" value="classpath:/mapper/*"/>
</bean>
</beans>
SQL配置文件BookMapper.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.lanou3g.dao.BookMapper">
<resultMap id="BaseResultMap" type="com.lanou3g.bean.Book">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="bname" jdbcType="VARCHAR" property="bname" />
<result column="btype" jdbcType="INTEGER" property="btype" />
<result column="author" jdbcType="VARCHAR" property="author" />
<result column="author_gender" jdbcType="INTEGER" property="authorGender" />
<result column="price" jdbcType="REAL" property="price" />
<result column="description" jdbcType="VARCHAR" property="description" />
</resultMap>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from book
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.lanou3g.bean.Book">
<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
SELECT LAST_INSERT_ID()
</selectKey>
insert into book (bname, btype, author,
author_gender, price, description
)
values (#{bname,jdbcType=VARCHAR}, #{btype,jdbcType=INTEGER}, #{author,jdbcType=VARCHAR},
#{authorGender,jdbcType=INTEGER}, #{price,jdbcType=REAL}, #{description,jdbcType=VARCHAR}
)
</insert>
<update id="updateByPrimaryKey" parameterType="com.lanou3g.bean.Book">
update book
set bname = #{bname,jdbcType=VARCHAR},
btype = #{btype,jdbcType=INTEGER},
author = #{author,jdbcType=VARCHAR},
author_gender = #{authorGender,jdbcType=INTEGER},
price = #{price,jdbcType=REAL},
description = #{description,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select id, bname, btype, author, author_gender, price, description
from book
where id = #{id,jdbcType=INTEGER}
</select>
<select id="selectAll" resultMap="BaseResultMap">
select id, bname, btype, author, author_gender, price, description
from book
</select>
</mapper>
入口类App.java
package com.lanou3g;
import com.lanou3g.dao.BookMapper;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
/**
* Hello world!
*
*/
public class App
{
public static void main( String[] args )
{
ApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
System.out.println(ctx.getBean(BookMapper.class).selectAll());
}
}