Mybatis入门之常规操作CURD案例Demo(附源码)
表结构
分页使用了pageHelper插件，有疑问移步这篇博客：Mybatis案例包括分页
实体类和VO类（使用了lombok）：
package com.star.model; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor @Builder public class Book { private Integer id; private String bookname; private Float price; private String booktype; }
package com.star.vo; import com.star.model.Book; import lombok.*; import java.util.List; import java.util.Map; @AllArgsConstructor @NoArgsConstructor public class BookVO extends Book { //传递book集合 private List<Book> books; //传递参数map private Map<String,Object> params; //传递id集合 private List<Integer> list; //传递价格区间 private Float min; private Float max; public Map<String, Object> getParams() { return params; } public void setParams(Map<String, Object> params) { this.params = params; } public List<Book> getBooks() { return books; } public void setBooks(List<Book> books) { this.books = books; } public List<Integer> getList() { return list; } public void setList(List<Integer> list) { this.list = list; } public Float getMin() { return min; } public void setMin(Float min) { this.min = min; } public Float getMax() { return max; } public void setMax(Float max) { this.max = max; } }
mapper代码
package com.star.mapper; import com.star.model.Book; import com.star.util.PageBean; import com.star.vo.BookVO; import lombok.Lombok; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface BookMapper { int deleteByPrimaryKey(Integer id); List<Book> queryBookPager(Book bk); int insert(Book record); int insertSelective(Book record); //返回单个resultMap结果book对象 Book selectByPrimaryKey(Integer id); int updateByPrimaryKeySelective(Book record); int updateByPrimaryKey(Book record); //1.动态sql之if Book queryOneByBookId(@Param("bookId")Integer bookId); //2.动态sql传参 List<Book> queryOneByParam(@Param("booktype")String bookType,@Param("bookname")String bookName); //3.使用对象操作参数 List<Book> queryBookObject(Book bk); //4、使用sql的foreach List<Book> queryBookByForeach(BookVO bookVO); //5.使用choose List<Book> dynamicChooseTest(Book bk); //6.使用where List<Book> dynamicWhereTest(Book bk); //7.使用set int dynamicSetTest(Book bk); //8.查询相关 // 1）：查询返回resultMap结果集 List<Book> queryBookResultMap(Book bk); // 2）：查询返回resultType结果集（多表联查） List<Map<String,Object>> queryResultMap(Book bk); // 3）：查询返回resultType结果集之单个map Map<String,Object> querySingleMap(Book bk); //9.分页实现 List<Book> queryBookPager(Book bk, PageBean pageBean); //10.范围查询 List<Book> queryBookByRange(BookVO bookVO); //11.实现批量插入 int insertBooksBatch(BookVO bookVO); //12.实现批量删除 int deleteBookBatch(BookVO bookVO); //13.实现批量更新 int updateBookBatch(BookVO bookVO); //14.遍历参数为map的集合进行查询 List<Map<String,Object>> selectResultByMap(BookVO bookVO); }
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：mapper的映射接口 --> <mapper namespace="com.star.mapper.BookMapper"> <!-- resultMap：适合使用返回值是自定义实体类的情况 resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型 --> <resultMap id="BaseResultMap" type="com.star.model.Book"> <constructor> <idArg column="id" jdbcType="INTEGER" javaType="java.lang.Integer"/> <arg column="bookname" jdbcType="VARCHAR" javaType="java.lang.String"/> <arg column="price" jdbcType="REAL" javaType="java.lang.Float"/> <arg column="booktype" jdbcType="VARCHAR" javaType="java.lang.String"/> </constructor> </resultMap> <!-- 封装查询列表 --> <sql id="Base_Column_List"> id, bookname, price, booktype </sql> <!-- 封装查询语句 --> <sql id="base_query_list"> select <include refid="Base_Column_List"/> from t_book_vue where 1=1 </sql> <!-- 封装倒序排序 --> <sql id="base_order"> order by id desc </sql> <!-- 封装查询参数 --> <sql id="base_query_param"> <if test="null!=bookname and ''!=bookname"> and bookname like concat('%',#{bookname},'%') </if> <if test="null!=booktype and ''!=booktype"> and booktype = #{booktype} </if> </sql> <!-- 封装插入sql --> <sql id="base_insert_param"> insert into t_book_vue <!-- prefix：代表前缀（ suffix：代表后缀） suffixOverrides：去除末尾的后缀， prefixOverrides：去除开头的前缀 --> <trim prefix="(" suffix=")" suffixOverrides=","> <if test="bookname != null"> bookname, </if> <if test="price != null"> price, </if> <if test="booktype != null"> booktype, </if> </trim> </sql> <!-- resultMap：引用上面的结果集 parameterType：参数类型 --> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer"> select <include refid="Base_Column_List"/> from t_book_vue where id = #{id,jdbcType=INTEGER} </select> <!-- resultType：指定返回结果集是Book对象 --> <select id="queryBookPager" resultType="com.star.model.Book"> select <include refid="Base_Column_List"/> from t_book_vue order by id desc </select> <!-- id：唯一 parameterType：参数类型 resultType：返回类型 --> <select id="dynamicChooseTest" parameterType="com.star.model.Book" resultMap="BaseResultMap"> <include refid="base_query_list"/> <!-- 满足表达式拼接标签体sql语句 --> <choose> <when test="bookname != null"> and bookname like concat('%',#{bookname},'%') </when> <when test="booktype != null"> and booktype = #{booktype} </when> <otherwise> and 2 = 2 </otherwise> </choose> <include refid="base_order"/> </select> <!-- id唯一 where标签跟在表名的后面，与if标签结合使用 --> <select id="dynamicWhereTest" parameterType="com.star.model.Book" resultMap="BaseResultMap"> select * from t_book_vue <where> <if test="bookname != null"> bookname = #{bookname} </if> <if test="booktype != null"> and booktype = #{booktype} </if> </where> </select> <!-- set标签与where标签大同小异 --> <update id="dynamicSetTest" parameterType="com.star.model.Book"> update t_book_vue <set> <if test="bookname != null"> bookname = #{bookname}, </if> <if test="booktype != null"> booktype = #{booktype}, </if> </set> where id = #{id} </update> <!-- 使用普通方式传参 --> <select id="queryOneByBookId" resultType="com.star.model.Book"> <include refid="base_query_list"/> <if test="null!=bookId and ''!=bookId"> and id = #{bookId} </if> </select> <!-- 使用注解传参的方式 --> <select id="queryOneByParam" resultType="com.star.model.Book"> <include refid="base_query_list"/> <include refid="base_query_param"/> <include refid="base_order"/> </select> <!-- 使用对象作为参数的方式 --> <select id="queryBookObject" resultType="com.star.model.Book"> <include refid="base_query_list"/> <include refid="base_query_param"/> <include refid="base_order"/> </select> <!-- foreach用法 --> <select id="queryBookByForeach" resultType="com.star.model.Book"> <include refid="base_query_list"/> and id in <!-- conllection：代表要循环的参数 item： 每一项的别名自定义 open：开头前缀 close: 结尾后缀 separator:每项之间插入字符 --> <foreach collection="list" item="id" open="(" close=")" separator=","> #{id} </foreach> <include refid="base_order"/> </select> <select id="queryBookResultMap" resultType="com.star.model.Book"> <include refid="base_query_list"/> <include refid="base_query_param"/> <include refid="base_order"/> </select> <!-- 联表查询 --> <select id="queryResultMap" resultType="java.util.Map"> SELECT bu.*,dt.dt_name FROM t_book_vue bu LEFT JOIN t_dictionary dt ON bu.booktype=dt.dt_value WHERE 1=1 <if test="bookname!=null"> and bu.bookname like concat('%',#{bookname},'%') </if> <include refid="base_order"/> </select> <select id="querySingleMap" resultType="java.util.Map"> <include refid="base_query_list"/> and id = #{id} </select> <select id="queryBookPagers" resultType="com.star.model.Book"> <include refid="base_query_list"/> <include refid="base_query_param"/> <include refid="base_order"/> </select> <!-- 范围查询 --> <select id="queryBookByRange" resultType="com.star.model.Book"> <include refid="base_query_list"/> <if test="null!=min"> and price>=#{min} </if> <if test="null!=max"> and price <=#{max} </if> <include refid="base_order"/> </select> <!-- 遍历map集合利用entrySet方法 --> <select id="selectResultByMap" parameterType="com.star.vo.BookVO" resultType="java.util.Map"> <include refid="base_query_list"/> and <foreach collection="params.entrySet()" index="key" item="value" separator="and"> ${key} = #{value} </foreach> </select> <delete id="deleteByPrimaryKey" parameterType="integer"> delete from t_book_vue where id = #{id,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.star.model.Book"> insert into t_book_vue (bookname, price,booktype) values (#{bookname,jdbcType=VARCHAR}, #{price,jdbcType=REAL},#{booktype,jdbcType=VARCHAR}) </insert> <!-- trim的用法生成动态sql --> <insert id="insertSelective" parameterType="com.star.model.Book"> <include refid="base_insert_param"/> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="id != null"> #{id,jdbcType=INTEGER}, </if> <if test="bookname != null"> #{bookname,jdbcType=VARCHAR}, </if> <if test="price != null"> #{price,jdbcType=REAL}, </if> <if test="booktype != null"> #{booktype,jdbcType=VARCHAR}, </if> </trim> </insert> <!-- 实现批量插入 --> <insert id="insertBooksBatch" parameterType="com.star.vo.BookVO"> insert into t_book_vue(bookname,booktype,price) <trim prefix="values " suffixOverrides=","> <foreach collection="books" separator="," item="book"> (#{book.bookname},#{book.booktype},#{book.price}) </foreach> </trim> </insert> <!-- 实现批量删除 --> <delete id="deleteBookBatch" parameterType="com.star.vo.BookVO"> DELETE FROM t_book_vue WHERE id in <foreach collection="books" open="(" close=")" item="book" separator=","> #{book.id} </foreach> </delete> <!-- 实现批量更新（利用case when） --> <update id="updateBookBatch" parameterType="com.star.vo.BookVO"> update t_book_vue <trim prefix="set" suffixOverrides=","> <!-- <trim prefix="bookname =case" suffix="end,">--> <foreach collection="books" open="bookname = case" close="end," item="i"> <if test="i.id!=null and i.bookname!=null"> when id=#{i.id} then #{i.bookname} </if> </foreach> <!-- </trim>--> <!-- <trim prefix="price =case" suffix="end,">--> <foreach collection="books" open="price =case" close="end," item="i"> <if test="i.id!=null and i.price!=null"> when id=#{i.id} then #{i.price} </if> </foreach> <!-- </trim>--> <foreach collection="books" open="booktype =case" close="end," item="i"> <if test="i.id!=null and i.booktype!=booktype"> when id=#{i.id} then #{i.booktype} </if> </foreach> </trim> where <foreach collection="books" separator="or" item="i" index="index"> id=#{i.id} </foreach> </update> <update id="updateByPrimaryKeySelective" parameterType="com.star.model.Book"> update t_book_vue <set> <if test="bookname != null"> bookname = #{bookname,jdbcType=VARCHAR}, </if> <if test="price != null"> price = #{price,jdbcType=REAL}, </if> <if test="booktype != null"> booktype = #{booktype,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.star.model.Book"> update t_book_vue set bookname = #{bookname,jdbcType=VARCHAR}, price = #{price,jdbcType=REAL}, booktype = #{booktype,jdbcType=VARCHAR} where id = #{id,jdbcType=INTEGER} </update> </mapper>
service代码
package com.star.service; import com.star.model.Book; import com.star.util.PageBean; import com.star.vo.BookVO; import org.apache.ibatis.annotations.Param; import java.util.List; import java.util.Map; public interface IBookService { /** * 删除单个 * @param id 主键 * @return 影响行数 */ int deleteByPrimaryKey(Integer id); /** * 分页查询 * @param bk 对象 * @return 集合 */ List<Book> queryBookPager(Book bk); /** * 增加操作 * @param record 对象 * @return 影响行数 */ int insert(Book record); /** * 查询单个 * @param id 主键 * @return 影响行数 */ Book selectByPrimaryKey(Integer id); /** * 更新操作 * @param record 对象 * @return 影响行数 */ int updateByPrimaryKey(Book record); //1.动态sql之if,注意service层不添加参数注解 Book queryOneByBookId(Integer bookId); //2.动态sql传参 List<Book> queryOneByParam(String bookType,String bookName); //3.使用对象操作参数 List<Book> queryBookObject(Book bk); //4、使用sql的foreach List<Book> queryBookByForeach(BookVO bookVO); //5.使用choose List<Book> dynamicChooseTest(Book bk); //6.使用where List<Book> dynamicWhereTest(Book bk); //7.使用set int dynamicSetTest(Book BK); //8.查询相关 // 1）：查询返回resultMap结果集 List<Book> queryBookResultMap(Book bk); // 2）：查询返回resultType结果集（多表联查） List<Map<String,Object>> queryResultMap(Book bk); // 3）：查询返回resultType结果集之单个map Map<String,Object> querySingleMap(Book bk); //9.分页实现 List<Book> queryBookPager(Book bk, PageBean pageBean); // //10.范围查询 List<Book> queryBookByRange(BookVO bookVO); //11.实现批量插入 int insertBooksBatch(BookVO bookVO); //12.实现批量删除 int deleteBookBatch(BookVO bookVO); //13.实现批量更新 int updateBookBatch(BookVO bookVO); //14.遍历参数为map的集合进行查询 List<Map<String,Object>> selectResultByMap(BookVO bookVO); }
实现类BookServiceImpl：
package com.star.service.serviceImpl; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; import com.star.mapper.BookMapper; import com.star.model.Book; import com.star.service.IBookService; import com.star.util.PageBean; import com.star.vo.BookVO; import lombok.Lombok; import java.util.List; import java.util.Map; public class BookServiceImpl implements IBookService { private BookMapper bookMapper; public BookMapper getBookMapper() { return bookMapper; } public void setBookMapper(BookMapper bookMapper) { this.bookMapper = bookMapper; } @Override public int deleteByPrimaryKey(Integer id) { return bookMapper.deleteByPrimaryKey(id); } @Override public int updateBookBatch(BookVO bookVO) { return bookMapper.updateBookBatch(bookVO); } @Override public List<Book> queryBookPager(Book bk) { return bookMapper.queryBookPager(bk); } @Override public int insert(Book record) { return bookMapper.insert(record); } @Override public List<Map<String, Object>> selectResultByMap(BookVO bookVO) { return bookMapper.selectResultByMap(bookVO); } @Override public Book selectByPrimaryKey(Integer id) { return bookMapper.selectByPrimaryKey(id); } @Override public int updateByPrimaryKey(Book record) { return bookMapper.updateByPrimaryKey(record); } @Override public Book queryOneByBookId(Integer bookId) { return bookMapper.queryOneByBookId(bookId); } @Override public List<Book> queryOneByParam(String bookType, String bookName) { return bookMapper.queryOneByParam(bookType,bookName); } @Override public List<Book> queryBookObject(Book bk) { return bookMapper.queryBookObject(bk); } @Override public List<Book> queryBookByForeach(BookVO bookVO) { return bookMapper.queryBookByForeach(bookVO); } @Override public List<Book> dynamicChooseTest(Book bk) { return bookMapper.dynamicChooseTest(bk); } @Override public List<Book> dynamicWhereTest(Book bk) { return bookMapper.dynamicWhereTest(bk); } @Override public int dynamicSetTest(Book bk) { return bookMapper.dynamicSetTest(bk); } @Override public List<Book> queryBookResultMap(Book bk) { return bookMapper.queryBookResultMap(bk); } @Override public List<Map<String, Object>> queryResultMap(Book bk) { return bookMapper.queryResultMap(bk); } @Override public List<Book> queryBookPager(Book bk, PageBean pageBean) { //给分页插件填充页码和行数 if(null!=pageBean&&pageBean.isPagination()) PageHelper.startPage(pageBean.getPage(),pageBean.getRows()); return bookMapper.queryBookPager(bk,pageBean); } @Override public int insertBooksBatch(BookVO bookVO) { return bookMapper.insertBooksBatch(bookVO); } @Override public Map<String, Object> querySingleMap(Book bk) { return bookMapper.querySingleMap(bk); } @Override public List<Book> queryBookByRange(BookVO bookVO) { return bookMapper.queryBookByRange(bookVO); } @Override public int deleteBookBatch(BookVO bookVO) { return bookMapper.deleteBookBatch(bookVO); } }
单元测试Junit
package com.star.service.serviceImpl; import com.github.pagehelper.PageInfo; import com.star.mapper.BookMapper; import com.star.model.Book; import com.star.service.IBookService; import com.star.util.MybatisSessionFactoryUtils; import com.star.util.PageBean; import com.star.vo.BookVO; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.junit.After; import org.junit.Before; import org.junit.Test; import java.util.*; import static org.junit.Assert.*; public class BookServiceImplTest { private IBookService bookService; private SqlSession sqlSession; public SqlSession getSqlSession() { return sqlSession; } public void setSqlSession(SqlSession sqlSession) { this.sqlSession = sqlSession; } private Book book; public IBookService getBookService() { return bookService; } public void setBookService(IBookService bookService) { this.bookService = bookService; } @Before public void setUp() throws Exception { sqlSession = MybatisSessionFactoryUtils.openSession(); BookMapper mapper = sqlSession.getMapper(BookMapper.class); bookService = new BookServiceImpl(); ((BookServiceImpl) bookService).setBookMapper(mapper); } @After public void tearDown() throws Exception { sqlSession.commit(); MybatisSessionFactoryUtils.closeSession(); } @Test public void deleteByPrimaryKey() { int n = bookService.deleteByPrimaryKey(2); if(n>0) System.out.println("成功"); } @Test public void queryBookPager() { List<Book> list =bookService.queryBookPager(new Book()); list.forEach(System.out::println); } @Test public void insert() { int n = bookService.insert(Book.builder().bookname("你好啊").price(321f).booktype("1-1").build()); if(n>0) System.out.println("成功！"); } @Test public void selectByPrimaryKey() { Book n =bookService.selectByPrimaryKey(6); System.out.println(n); } @Test public void updateByPrimaryKey() { int n = bookService.updateByPrimaryKey(Book.builder().bookname("n颠三倒四").booktype("1-2").price(123f).id(2).build()); if(n>0) System.out.println("成功"); } @Test public void selectQueryTest() { /*Book bk= bookService.queryOneByBookId(1); System.out.println(bk);*/ /*List<Book> list= bookService.queryOneByParam("1-1","西"); list.forEach(System.out::println);*/ /*List<Book> list= bookService.queryBookObject(Book.builder().bookname("西").build()); list.forEach(System.out::println);*/ /*List<Book> list= bookService.queryBookByForeach(new BookVO(Arrays.asList(1,2,3))); list.forEach(System.out::println);*/ /*List<Map<String,Object>> list= bookService.queryBookResultMap(Book.builder().build()); list.forEach(System.out::println);*/ /*List<Map<String,Object>> list= bookService.queryResultMap(Book.builder().bookname("西").build()); list.forEach(System.out::println);*/ /*Map<String, Object> stringObjectMap = bookService.querySingleMap(Book.builder().id(1).build()); System.out.println(stringObjectMap);*/ //分页实现 /*PageBean pageBean = new PageBean(); pageBean.setPage(2); pageBean.setRows(5); //查询实现 List<Book> books = bookService.queryBookPager(new Book(), pageBean); System.out.println(books.getClass()); //判断分页 if(null!=pageBean&&pageBean.isPagination()&&null!=books){ PageInfo<Book> pageInfo = new PageInfo<>(books); System.out.println("总记录数："+pageInfo.getTotal()); System.out.println("当前页码："+pageInfo.getPageNum()); System.out.println("显示条数："+pageInfo.getPageSize()); List<Book> list = pageInfo.getList(); list.forEach(System.out::println); }*/ //范围查询大于小于 /*BookVO bk = new BookVO(); bk.setMin(100f); bk.setMax(200f); List<Book> books = bookService.queryBookByRange(bk); books.forEach(System.out::println);*/ //遍历参数为map的查询 /*Map<String,Object> map = new HashMap<>(); map.put("bookname","万域之王"); map.put("price",56.5); BookVO bookVO = new BookVO(); bookVO.setParams(map); List<Map<String, Object>> maps = bookService.selectResultByMap(bookVO); maps.forEach(System.out::println);*/ } @Test public void insertDataTest() { List<Book> list = new ArrayList<>(); BookVO bookVO = new BookVO(); for (int i = 1; i <= 10; i++) { list.add(Book.builder().bookname("测试"+i).price(Float.parseFloat("100."+i)).booktype("1-"+i).build()); } System.out.println(list); bookVO.setBooks(list); //测试插入 int n = bookService.insertBooksBatch(bookVO); System.out.println(n>0?"插入成功":"插入失败"); } @Test public void deleteDataTest() { List<Book> list = new ArrayList<>(); BookVO bookVO = new BookVO(); for (int i = 57; i >= 48; i--) { list.add(Book.builder().id(i).build()); } System.out.println(list); bookVO.setBooks(list); //测试删除 int n = bookService.deleteBookBatch(bookVO); System.out.println(n>0?"批量删除成功":"批量删除失败"); } @Test public void updateDataTest() { List<Book> list = new ArrayList<>(); BookVO bookVO = new BookVO(); for (int i = 67; i >= 58; i--) { list.add(Book.builder().bookname("更新测试"+i).price(Float.parseFloat("120."+i)).booktype("1-9").id(i).build()); } System.out.println(list); bookVO.setBooks(list); //测试更新 int n = bookService.updateBookBatch(bookVO); System.out.println(n>0?"批量更新成功":"批量更新失败"); } }
