Mybatis 入门实战(2)--简单使用
本文主要介绍 Mybatis 的实际使用,相关的环境及软件信息如下:Mybatis 3.5.11。
1、工程整体结构
这里使用 Maven 来构建样例工程,工程目录结构如下:
2、引入依赖
<dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.11</version> </dependency>
其他相关依赖如分页插件、日志登,根据需要引入:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
<dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.11</version> </dependency> <!--分页插件--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>5.3.2</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.30</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.11</version> <scope>test</scope> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.24</version> <scope>provided</scope> </dependency> </dependencies> <build> <resources> <resource> <directory>src/main/java</directory> <includes> <include>**/*.xml</include> </includes> </resource> <resource> <directory>src/main/resources</directory> </resource> </resources> </build>
3、Mybatis 配置文件(mybatis-config.xml)
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <plugins> <!--分页插件--> <plugin interceptor="com.github.pagehelper.PageInterceptor"> <property name="reasonable" value="true"/> </plugin> </plugins> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <property name="url" value="jdbc:mysql://10.49.196.23:3306/test?characterEncoding=utf-8"/> <property name="username" value="root"/> <property name="password" value="123456"/> </dataSource> </environment> </environments> <mappers> <mapper resource="com/abc/mapper/StudentMapper.xml"/> <mapper class="com.abc.mapper.TeacherMapper"/> </mappers> </configuration>
4、创建实体类
4.1、Student.java
package com.abc.entity; import lombok.Data; import lombok.ToString; import java.time.LocalDateTime; @ToString @Data public class Student { private Long id; private LocalDateTime createTime; private LocalDateTime modifyTime; private String name; private Integer age; private String homeAddress; }
4.2、Teacher.java
package com.abc.entity; import lombok.Data; import lombok.ToString; import java.time.LocalDateTime; @ToString @Data public class Teacher { private Long id; private LocalDateTime createTime; private LocalDateTime modifyTime; private String name; private Integer age; private String homeAddress; }
5、创建 Provider
provider 主要用来提供 SQL。
package com.abc.provider; import org.apache.ibatis.builder.annotation.ProviderMethodResolver; import org.apache.ibatis.jdbc.SQL; public class TeacherProvider implements ProviderMethodResolver { public static String select2(String name, String homeAddress) { return new SQL(){{ SELECT("*"); FROM("a_teacher"); if (name != null && !"".equals(name)) { WHERE("name like #{name}"); } if (name != null && !"".equals(homeAddress)) { WHERE("home_address like #{name}"); } }}.toString(); } }
6、创建 Mapper
6.1、StudentMapper.java
package com.abc.mapper; import com.abc.entity.Student; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface StudentMapper { void insert(Student student); void update(Student student); Student selectById(Long id); List<Student> select(@Param("name") String name, @Param("homeAddress") String homeAddress); List<Map<String, Object>> select2(String name, String homeAddress); void delete(Long[] ids); }
StudentMapper 使用 XML 来编写 SQL,对应 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.abc.mapper.StudentMapper"> <insert id="insert" parameterType="com.abc.entity.Student" useGeneratedKeys="true" keyProperty="id"> insert into a_student(create_time,modify_time,name,age,home_address) values(#{createTime},#{modifyTime},#{name},#{age},#{homeAddress}) </insert> <update id="update" parameterType="com.abc.entity.Student"> update a_student set id=id <if test="name != null and name != ''"> ,name=#{name} </if> <if test="age != null"> ,age=#{age} </if> <if test="homeAddress != null and homeAddress != ''"> ,home_address=#{homeAddress} </if> where id=#{id} </update> <select id="selectById" resultType="com.abc.entity.Student"> select * from a_student where id=#{id} </select> <select id="select" resultType="com.abc.entity.Student"> select * from a_student where 1=1 <if test="name != null and name != ''"> and name like #{name} </if> <if test="homeAddress != null and homeAddress != ''"> and home_address like #{homeAddress} </if> </select> <select id="select2" resultType="map"> select * from a_student where 1=1 <if test="param1 != null and param1 != ''"> and name like #{param1} </if> <if test="param2 != null and param2 != ''"> and home_address like #{param2} </if> </select> <delete id="delete"> delete from a_student where id in <foreach collection="array" item="id" index="index" open="(" close=")" separator=","> #{id} </foreach> </delete> </mapper>
表 a_student 的字段与实体类的属性一一对应(表中字段使用下划线写法,实体类属性使用驼峰写法),字段 id 为自增字段。
6.2、TeacherMapper.java
package com.abc.mapper; import com.abc.entity.Teacher; import com.abc.provider.TeacherProvider; import org.apache.ibatis.annotations.*; import java.util.List; import java.util.Map; public interface TeacherMapper { @Options(useGeneratedKeys = true, keyProperty = "id") @Insert({"insert into a_teacher(create_time,modify_time,name,age,home_address)", " values(#{createTime},#{modifyTime},#{name},#{age},#{homeAddress})"}) void insert(Teacher teacher); @Update({"<script>", "update a_teacher set id=id", "<if test='name != null and name != \"\"'>", " ,name=#{name}", "</if>", "<if test='age != null'>", " ,age=#{age}", "</if>", "<if test='homeAddress != null and homeAddress != \"\"'>", " ,home_address=#{homeAddress}", "</if>", "where id=#{id}", "</script>" }) void update(Teacher teacher); @Select("select * from a_teacher where id=#{id}") Teacher selectById(Long id); @Select({"<script>", "select * from a_teacher where 1=1", "<if test='name != null and name != \"\"'>", " and name like #{name}", "</if>", "<if test='homeAddress != null and homeAddress != \"\"'>", " and home_address like #{homeAddress}", "</if>", "</script>" }) List<Teacher> select(@Param("name") String name, @Param("homeAddress") String homeAddress); @SelectProvider(type = TeacherProvider.class) List<Map<String, Object>> select2(String name, String homeAddress); @Delete({"<script>", "delete from a_teacher where id in", "<foreach collection='array' item='id' index='index' open='(' close=')' separator=','>", " #{id}", "</foreach>", "</script>" }) void delete(Long[] ids); }
TeacherMapper 使用注解来编写 SQL,如果使用了动态 SQL,需添加 script 元素;如果 SQL 比较复杂,不太方便使用注解,可以通过 Provider 使用 Java 代码来构建 SQL。
表 a_teacher 的字段与实体类的属性一一对应(表中字段使用下划线写法,实体类属性使用驼峰写法),字段 id 为自增字段。
7、日志配置
这里使用 Logback 作为日志框架,其配置文件(logback.xml)内容如下:
<?xml version="1.0" encoding="utf-8"?> <configuration debug="false"> <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <pattern>%d %level [%thread] %logger[%L] -> %m%n</pattern> </encoder> </appender> <root level="info"> <appender-ref ref="STDOUT" /> </root> <logger name="com.abc.mapper" level="debug"> </logger> </configuration>
8、分页插件
Mybatis 中分页可以使用 PageHelper 插件,该插件方便好用,具体使用方法及配置说用可参考官网文档:https://pagehelper.github.io。
9、测试用例
9.1、StudentMapperCase.java
package com.abc.mapper; import com.abc.entity.Student; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; 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.After; import org.junit.Before; import org.junit.Test; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.IOException; import java.io.InputStream; import java.time.LocalDateTime; import java.util.List; import java.util.Map; public class StudentMapperCase { private Logger logger = LoggerFactory.getLogger(StudentMapperCase.class); private SqlSession sqlSession; @Before public void before() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); } @After public void after() { sqlSession.close(); } @Test public void insert() { StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); Student student = new Student(); student.setCreateTime(LocalDateTime.now()); student.setName("李白"); student.setAge(30); student.setHomeAddress("长安"); mapper.insert(student); sqlSession.commit(); logger.info("id={}", student.getId()); } @Test public void update() { Student student = new Student(); student.setId(261L); student.setName("李白2"); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); mapper.update(student); sqlSession.commit(); } @Test public void selectById() { StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); Student student = mapper.selectById(261L); logger.info(student.toString()); } @Test public void select() { StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Student> students = mapper.select("%李%", "%长%"); logger.info(students.toString()); } @Test public void selectForPage() { StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); PageHelper.startPage(1, 5); List<Student> students = mapper.select("%李%", ""); PageInfo<Map<String, String>> pageInfo = new PageInfo(students); logger.info(pageInfo.toString()); } @Test public void select2() { StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List<Map<String, Object>> list = mapper.select2("%李%", "%长%"); logger.info(list.toString()); } @Test public void delete() { StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); mapper.delete(new Long[]{260L, 263L}); } }
9.2、TeacherMapperCase.java
package com.abc.mapper; import com.abc.entity.Teacher; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; 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.After; import org.junit.Before; import org.junit.Test; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.IOException; import java.io.InputStream; import java.time.LocalDateTime; import java.util.List; import java.util.Map; public class TeacherMapperCase { private Logger logger = LoggerFactory.getLogger(TeacherMapperCase.class); private SqlSession sqlSession; @Before public void before() throws IOException { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); sqlSession = sqlSessionFactory.openSession(); } @After public void after() { sqlSession.close(); } @Test public void insert() { Teacher teacher = new Teacher(); teacher.setCreateTime(LocalDateTime.now()); teacher.setName("孔子"); teacher.setAge(30); teacher.setHomeAddress("鲁国"); sqlSession.insert("com.abc.mapper.TeacherMapper.insert", teacher); sqlSession.commit(); logger.info("id={}", teacher.getId()); } @Test public void update() { Teacher teacher = new Teacher(); teacher.setId(1865L); teacher.setName("孔子2"); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); mapper.update(teacher); sqlSession.commit(); } @Test public void selectById() { TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.selectById(1865L); logger.info(teacher.toString()); } @Test public void select() { TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); List<Teacher> teachers = mapper.select("%孔%", "%鲁%"); logger.info(teachers.toString()); } @Test public void selectForPage() { TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); PageHelper.startPage(10, 3); List<Teacher> teachers = mapper.select("%孔%", "%鲁%"); PageInfo<Map<String, String>> pageInfo = new PageInfo(teachers); logger.info(pageInfo.toString()); } @Test public void select2() { TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); List<Map<String, Object>> list = mapper.select2("%孔%", "%鲁%"); logger.info(list.toString()); } @Test public void delete() { TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); mapper.delete(new Long[]{1853L, 1854L}); } }