• Mybatis—动态sql和分页

    目录

    简介：

    一、foreach标签

    二、模糊查询

    三、结果集的处理

    四、第三方插件继承Mybatis使用

    五、特殊字符处理

    六、总结

    简介：

    动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

    使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

    MyBatis 的真正强大在于它的语句映射，这是它的魔力所在。由于它的异常强大，映射器的 XML 文件就显得相对简单。如果拿它跟具有相同功能的 JDBC 代码进行对比，你会立即发现省掉了将近 95% 的代码。MyBatis 致力于减少使用成本，让用户能更专注于 SQL 代码。 

     为什么要重写mybatis的分页？
       Mybatis的分页功能很弱，它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果)，在大数据量的情况下这样的分页基本上是没有用的

    一、foreach标签

    1、BookMapper.xml

    2.   <select id="selectByIn" resultMap="BaseResultMap" parameterType="java.util.List" >
    3.     select
    4.     <include refid="Base_Column_List" />
    5.     from t_mvc_book
    6.     where bid in
    7.     <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    8.       #{bid}
    9.     foreach>
    10.   select>

    2、BookMapper

    1. //  通过in关键字进行查询：讲解foreach 标签的使用
    2. //    如果说参数是非实体类(book,Order,....)，那么急的加上注解 @param,bookIds是对应collection属性的
    3.     List selectByIn(@Param("bookIds") List bookIds);

    3、BookBizImpl

    1.  @Override
    2.     public List selectByIn(List bookIds) {
    3.         return bookMapper.selectByIn(bookIds);
    4.     }

     4、BookBiz

    List selectByIn(List bookIds);

    5、BookBizImplTest

    1. @Test
    2.     public void selectByIn() {
    3.         List bookIds = Arrays.asList(new Integer[]{31, 32, 33, 34});
    4.         bookBiz.selectByIn(bookIds).forEach(System.out::println);
    5.     }

    二、模糊查询

    1、test1

    BookBiz

    public List<Book> selectBooksLike1(String bname);

    BookBizImpl

    1. @Override
    2.     public List selectBooksLike1(String bname) {
    3.         return bookMapper.selectBooksLike1(bname);
    4.     }

    BookMapper.xml

    1. <select id="selectBooksLike1" resultType="com.liaoxin.model.Book" parameterType="java.lang.String">
    2.   select * from t_mvc_book where bname like #{bname}
    3. select>

    BookMapper.java

    List selectBooksLike1(@Param("bname") String bname);

    BookBizImplTest

    1. @Test
    2.     public void selectBooksLike1() {
    3.         bookBiz.selectBooksLike1("%圣墟%").forEach(System.out::println);
    4.     }

    2、test2

    BookBiz

    public List selectBooksLike2(String bname);

    BookBizImpl

    1. @Override
    2.     public List selectBooksLike2(String bname) {
    3.         return bookMapper.selectBooksLike2(bname);
    4.     }

    BookMapper.xml

    1. <select id="selectBooksLike2" resultType="com.liaoxin.model.Book" parameterType="java.lang.String">
    2.   select * from t_mvc_book where bname like '${bname}'
    3. select>

    BookMapper.java

    List selectBooksLike2(@Param("bname") String bname);

    BookBizImplTest

    1. @Test
    2.     public void selectBooksLike2() {
    3.         bookBiz.selectBooksLike2("%圣墟%").forEach(System.out::println);
    4.     }

    3、test3

    BookBiz

    public List selectBooksLike3(String bname);

    BookBizImpl

    1. @Override
    2.     public List selectBooksLike3(String bname) {
    3.         return bookMapper.selectBooksLike3(bname);
    4.     }

    BookMapper.xml

    1. <select id="selectBooksLike3" resultType="com.liaoxin.model.Book" parameterType="java.lang.String">
    2.   select * from t_mvc_book where bname like concat('%',#{bname},'%')
    3. select>

    BookMapper.java

    List selectBooksLike3(@Param("bname") String bname);

    BookBizImplTest

    1.  @Test
    2.     public void selectBooksLike3() {
    3.         bookBiz.selectBooksLike3("圣墟").forEach(System.out::println);
    4.     }

    三、结果集的处理

    1、list1

    BookBiz

    List list1();

    BookBizImpl

    1. @Override
    2.     public List list1() {
    3.         return bookMapper.list1();
    4.     }

    BookMapper.xml

    1.  <select id="list1" resultMap="BaseResultMap">
    2.   select * from t_mvc_book
    3. select>

    BookMapper.java

    1. //    list1 list2的结论是，对于单表查询而言，可以用resultmap/resulttype接收，但是多表必须用resultmap接收
    2.     List list1();

    BookVo

    1. package com.liaoxin.model;
    2.  
    3. import java.util.List;
    4.  
    5. public class BookVo extends Book{
    6.     private List bookIds;
    7.  
    8.     public List getBookIds() {
    9.         return bookIds;
    10.     }
    11.  
    12.     public void setBookIds(List bookIds) {
    13.         this.bookIds = bookIds;
    14.     }
    15. }
    16.

    BookBizImplTest

    1.  @Test
    2.     public void list1() {
    3.         bookBiz.list1().forEach(System.out::println);
    4.     }

    2、list2

    BookBiz

    List list2();

    BookBizImpl

    1. @Override
    2.     public List list2() {
    3.         return bookMapper.list2();
    4.     }

    BookMapper.xml

    1. select>
    2.   <select id="list2" resultType="com.liaoxin.model.Book">
    3.   select * from t_mvc_book
    4. select>

    BookMapper.java

     List list2();

    BookVo 

    1. package com.liaoxin.model;
    2.  
    3. import java.util.List;
    4.  
    5. public class BookVo extends Book{
    6.     private List bookIds;
    7.  
    8.     public List getBookIds() {
    9.         return bookIds;
    10.     }
    11.  
    12.     public void setBookIds(List bookIds) {
    13.         this.bookIds = bookIds;
    14.     }
    15. }
    16.

    BookBizImplTest

    1.  @Test
    2.     public void list2() {
    3.         bookBiz.list2().forEach(System.out::println);
    4.     }

    3、list3

    BookBiz

    List list3(BookVo vo);

    BookBizImpl

    1. @Override
    2.     public List list3(BookVo vo) {
    3.         return bookMapper.list3(vo);
    4.     }

    BookMapper.xml

    1.   <select id="list3" resultType="com.liaoxin.model.Book" parameterType="com.liaoxin.model.BookVo">
    2.     select * from t_mvc_book where bid in
    3.     <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    4.       #{bid}
    5.     foreach>
    6.   select>

    BookMapper.java

    1. //    如果要传入多个查询参数，必须以对象的方式进行传递
    2. //    举例：select * from t_mvc_book where bid in (1,2,3,4,5,6) and bname in ("圣墟","不死不休")
    3.     List list3(BookVo vo);

    BookVo

    1. package com.liaoxin.model;
    2.  
    3. import java.util.List;
    4.  
    5. public class BookVo extends Book{
    6.     private List bookIds;
    7.  
    8.     public List getBookIds() {
    9.         return bookIds;
    10.     }
    11.  
    12.     public void setBookIds(List bookIds) {
    13.         this.bookIds = bookIds;
    14.     }
    15. }
    16.

    BookBizImplTest

    1. @Test
    2.     public void list3() {
    3.         BookVo vo=new BookVo();
    4.         vo.setBookIds(Arrays.asList(new Integer[]{31,32,33,34}));
    5.         bookBiz.list3(vo).forEach(System.out::println);
    6.     }

    4、list4

    BookBiz

    List list4();

    BookBizImpl

    1. @Override
    2.     public List list4() {
    3.         return bookMapper.list4();
    4.     }

    BookMapper.xml

    1. <select id="list4" resultType="java.util.Map">
    2.   select * from t_mvc_book
    3. select>

    BookMapper.java

    1. //    说明了不管返回1条数据，还是多条数据，都应该用java.util.Map进行接受
    2. //    如果是1条数据，那么返回值是Map
    3. //    如果是多条数据，那么返回值List
    4.     List list4();

    BookVo

    1. package com.liaoxin.model;
    2.  
    3. import java.util.List;
    4.  
    5. public class BookVo extends Book{
    6.     private List bookIds;
    7.  
    8.     public List getBookIds() {
    9.         return bookIds;
    10.     }
    11.  
    12.     public void setBookIds(List bookIds) {
    13.         this.bookIds = bookIds;
    14.     }
    15. }
    16.

    BookBizImplTest

    1. @Test
    2.     public void list4() {
    3.         bookBiz.list4().forEach(System.out::println);
    4.     }

    5、list5

    BookBiz

    Map list5(Map map);

    BookBizImpl

    1. @Override
    2.     public Map list5(Map map) {
    3.         return bookMapper.list5(map);
    4.     }

    BookMapper.xml

    1. <select id="list5" resultType="java.util.Map" parameterType="java.util.Map">
    2.   select * from t_mvc_book where bid = #{bid}
    3. select>

    BookMapper.java

    Map list5(Map map);

    BookVo

    1. package com.liaoxin.model;
    2.  
    3. import java.util.List;
    4.  
    5. public class BookVo extends Book{
    6.     private List bookIds;
    7.  
    8.     public List getBookIds() {
    9.         return bookIds;
    10.     }
    11.  
    12.     public void setBookIds(List bookIds) {
    13.         this.bookIds = bookIds;
    14.     }
    15. }
    16.

    BookBizImplTest

    1. @Test
    2.     public void list5() {
    3.         Map map=new HashMap();
    4.         map.put("bid",32);
    5.         System.out.println(bookBiz.list5(map));
    6.     }

    四、第三方插件继承Mybatis使用

    1、导入pom依赖

    2.     <groupId>com.github.pagehelpergroupId>
    3.     <artifactId>pagehelperartifactId>
    4.     <version>5.1.2version>
    6.

    2、Mybatis.cfg.xml配置拦截器

    1. <plugins>
    2.     
    3.     <plugin interceptor="com.github.pagehelper.PageInterceptor">
    4.     plugin>
    5. plugins>
    6.

    3、使用PageHelper进行分页

    1. package com.liaoxin.util;
    2.  
    3. import java.io.Serializable;
    4. import java.util.Map;
    5.  
    6. import javax.servlet.http.HttpServletRequest;
    7.  
    8. public class PageBean implements Serializable {
    9.  
    10. 	private static final long serialVersionUID = 2422581023658455731L;
    11.  
    12. 	//页码
    13. 	private int page=1;
    14. 	//每页显示记录数
    15. 	private int rows=10;
    16. 	//总记录数
    17. 	private int total=0;
    18. 	//是否分页
    19. 	private boolean isPagination=true;
    20. 	//上一次的请求路径
    21. 	private String url;
    22. 	//获取所有的请求参数
    23. 	private Map map;
    24. 	
    25. 	public PageBean() {
    26. 		super();
    27. 	}
    28. 	
    29. 	//设置请求参数
    30. 	public void setRequest(HttpServletRequest req) {
    31. 		String page=req.getParameter("page");
    32. 		String rows=req.getParameter("rows");
    33. 		String pagination=req.getParameter("pagination");
    34. 		this.setPage(page);
    35. 		this.setRows(rows);
    36. 		this.setPagination(pagination);
    37. 		this.url=req.getContextPath()+req.getServletPath();
    38. 		this.map=req.getParameterMap();
    39. 	}
    40. 	public String getUrl() {
    41. 		return url;
    42. 	}
    43.  
    44. 	public void setUrl(String url) {
    45. 		this.url = url;
    46. 	}
    47.  
    48. 	public Map getMap() {
    49. 		return map;
    50. 	}
    51.  
    52. 	public void setMap(Map map) {
    53. 		this.map = map;
    54. 	}
    55.  
    56. 	public int getPage() {
    57. 		return page;
    58. 	}
    59.  
    60. 	public void setPage(int page) {
    61. 		this.page = page;
    62. 	}
    63. 	
    64. 	public void setPage(String page) {
    65. 		if(null!=page&&!"".equals(page.trim()))
    66. 			this.page = Integer.parseInt(page);
    67. 	}
    68.  
    69. 	public int getRows() {
    70. 		return rows;
    71. 	}
    72.  
    73. 	public void setRows(int rows) {
    74. 		this.rows = rows;
    75. 	}
    76. 	
    77. 	public void setRows(String rows) {
    78. 		if(null!=rows&&!"".equals(rows.trim()))
    79. 			this.rows = Integer.parseInt(rows);
    80. 	}
    81.  
    82. 	public int getTotal() {
    83. 		return total;
    84. 	}
    85.  
    86. 	public void setTotal(int total) {
    87. 		this.total = total;
    88. 	}
    89. 	
    90. 	public void setTotal(String total) {
    91. 		this.total = Integer.parseInt(total);
    92. 	}
    93.  
    94. 	public boolean isPagination() {
    95. 		return isPagination;
    96. 	}
    97. 	
    98. 	public void setPagination(boolean isPagination) {
    99. 		this.isPagination = isPagination;
    100. 	}
    101. 	
    102. 	public void setPagination(String isPagination) {
    103. 		if(null!=isPagination&&!"".equals(isPagination.trim()))
    104. 			this.isPagination = Boolean.parseBoolean(isPagination);
    105. 	}
    106. 	
    107. 	/**
    108. 	 * 获取分页起始标记位置
    109. 	 * @return
    110. 	 */
    111. 	public int getStartIndex() {
    112. 		//(当前页码-1)*显示记录数
    113. 		return (this.getPage()-1)*this.rows;
    114. 	}
    115. 	
    116. 	/**
    117. 	 * 末页
    118. 	 * @return
    119. 	 */
    120. 	public int getMaxPage() {
    121. 		int totalpage=this.total/this.rows;
    122. 		if(this.total%this.rows!=0)
    123. 			totalpage++;
    124. 		return totalpage;
    125. 	}
    126. 	
    127. 	/**
    128. 	 * 下一页
    129. 	 * @return
    130. 	 */
    131. 	public int getNextPage() {
    132. 		int nextPage=this.page+1;
    133. 		if(this.page>=this.getMaxPage())
    134. 			nextPage=this.getMaxPage();
    135. 		return nextPage;
    136. 	}
    137. 	
    138. 	/**
    139. 	 * 上一页
    140. 	 * @return
    141. 	 */
    142. 	public int getPreivousPage() {
    143. 		int previousPage=this.page-1;
    144. 		if(previousPage<1)
    145. 			previousPage=1;
    146. 		return previousPage;
    147. 	}
    148.  
    149. 	@Override
    150. 	public String toString() {
    151. 		return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", isPagination=" + isPagination
    152. 				+ "]";
    153. 	}
    154. }
    155.

    4、处理分页结果

    BookBiz

    List listPager(Map map, PageBean pageBean);

    BookMapper.java

    1. //    利用第三方插件进行分页
    2.     List listPager(Map map);

    BookMapper.xml

    1. <select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">
    2.   select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
    3. select>

    BookBizImpl

    1. @Override
    2.     public List listPager(Map map, PageBean pageBean) {
    3. //        pageHelper分页插件相关的代码
    4.         if(pageBean!=null&&pageBean.isPagination()){
    5.             PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
    6.         }
    7.         List maps = bookMapper.listPager(map);
    8.         if(pageBean!=null&&pageBean.isPagination()){
    9. //            处理查询结果的前提是需要分页，是需要分页的
    10.             PageInfo info = new PageInfo(maps);
    11.             pageBean.setTotal(info.getTotal()+"");
    12.         }
    13.         return maps;
    14.     }

    BookBizImplTest

    1.  @Test
    2.     public void listPager() {
    3.         Map map=new HashMap();
    4.         map.put("bname","圣墟");
    5. //        bookBiz.listPager(map).forEach(System.out::println);
    6.  
    7. //        查询出第二页的20条数据
    8.         PageBean pageBean = new PageBean();
    9.         pageBean.setPage(2);
    10.         pageBean.setRows(20);
    11.  
    12.         bookBiz.listPager(map,pageBean).forEach(System.out::println);
    13.     }

    五、特殊字符处理

    1、特殊字符

    1. (>)
    2. <(<)
    3. &(&)
    4. 空格( )
    5.

    2、BookBiz

    1.  List list6(BookVo bookVo);
    2.  
    3.     List list7(BookVo bookVo);

    3、BookBizImpl

    1. @Override
    2.     public List list6(BookVo bookVo) {
    3.         return bookMapper.list6(bookVo);
    4.     }
    5.  
    6.     @Override
    7.     public List list7(BookVo bookVo) {
    8.         return bookMapper.list7(bookVo);
    9.     }

    4、BookMapper.java

    1. /**
    2.      * 处理特殊字符
    3.      * @param bookVo
    4.      * @return
    5.      */
    6.     List list6(BookVo bookVo);
    7.  
    8.  
    9.     /**
    10.      * 处理特殊字符
    11.      * @param bookVo
    12.      * @return
    13.      */
    14.     List list7(BookVo bookVo);

    5、BookMapper.xml

    1. <select id="list6" resultType="com.liaoxin.model.Book" parameterType="com.liaoxin.model.BookVo">
    2.     select * from t_mvc_book
    3.     <where>
    4.       <if test="null != min and min != ''">
    5.         
    6.       if>
    7.       <if test="null != max and max != ''">
    8.          price ]]>
    9.       if>
    10.     where>
    11.   select>
    12.  
    13.   <select id="list7" resultType="com.liaoxin.model.Book" parameterType="com.liaoxin.model.BookVo">
    14.     select * from t_mvc_book
    15.     <where>
    16.       <if test="null != min and min != ''">
    17.         and #{min} < price
    18.       if>
    19.       <if test="null != max and max != ''">
    20.         and #{max} > price
    21.       if>
    22.     where>
    23.   select>

    6、BookBizImplTest

    1.  @Test
    2.     public void list6() {
    3.         BookVo vo=new BookVo();
    4.         vo.setMax(45);
    5.         vo.setMin(35);
    6.         bookBiz.list6(vo).forEach(System.out::println);
    7.     }
    8.  
    9.     @Test
    10.     public void list7() {
    11.         BookVo vo=new BookVo();
    12.         vo.setMax(45);
    13.         vo.setMin(35);
    14.         bookBiz.list7(vo).forEach(System.out::println);
    15.     }

    六、总结

    1、MyBatis中#和$的区别

    1、#将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。
    如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by ‘111’,
    如果传入的值是id，则解析成的sql为order by “id”.
    2、$将传入的数据直接显示生成在sql中。
    如：order by u s e r i d user_iduser 
    3、id，如果传入的值是111,那么解析成sql时的值为order by user_id,
    如果传入的值是id，则解析成的sql为order by id.
    #方式能够很大程度防止sql注入。
    4、$方式无法防止Sql注入。
    5、$方式一般用于传入数据库对象，例如传入表名.
    6、一般能用#的就别用$.

    2、BookBiz 

    1. package com.liaoxin.biz;
    2.  
    3. import com.liaoxin.model.Book;
    4. import com.liaoxin.model.BookVo;
    5. import com.liaoxin.util.PageBean;
    6. import org.apache.ibatis.annotations.Param;
    7.  
    8. import java.util.List;
    9. import java.util.Map;
    10.  
    11. public interface BookBiz {
    12.     public int deleteByPrimaryKey(Integer bid);
    13.  
    14.     public Book selectByPrimaryKey(Integer bid);
    15.  
    16.     public List selectByIn(List bookIds);
    17.  
    18.     public List selectBooksLike1(String bname);
    19.     public List selectBooksLike2(String bname);
    20.     public List selectBooksLike3(String bname);
    21.  
    22.     List list1();
    23.     List list2();
    24.     List list3(BookVo vo);
    25.     List list4();
    26.     Map list5(Map map);
    27.  
    28.     List listPager(Map map, PageBean pageBean);
    29.  
    30.     List list6(BookVo bookVo);
    31.  
    32.     List list7(BookVo bookVo);
    33. }
    34.

    3、BookBizImpl

    1. package com.liaoxin.biz.impl;
    2.  
    3. import com.github.pagehelper.PageHelper;
    4. import com.github.pagehelper.PageInfo;
    5. import com.liaoxin.biz.BookBiz;
    6. import com.liaoxin.mapper.BookMapper;
    7. import com.liaoxin.model.Book;
    8. import com.liaoxin.model.BookVo;
    9. import com.liaoxin.util.PageBean;
    10.  
    11. import java.util.List;
    12. import java.util.Map;
    13.  
    14. public class BookBizImpl implements BookBiz {
    15.     private BookMapper bookMapper;
    16.  
    17. //    alt+insert 快速提供set/get/tostring/构造方法
    18. //    alt+enter 快速构建实现类，填充代码的前半部分 ctrl+1
    19.     public BookMapper getBookMapper() {
    20.         return bookMapper;
    21.     }
    22.  
    23.     public void setBookMapper(BookMapper bookMapper) {
    24.         this.bookMapper = bookMapper;
    25.     }
    26.  
    27.     @Override
    28.     public int deleteByPrimaryKey(Integer bid) {
    29.         return bookMapper.deleteByPrimaryKey(bid);
    30.     }
    31.  
    32.     @Override
    33.     public Book selectByPrimaryKey(Integer bid) {
    34.         return bookMapper.selectByPrimaryKey(bid);
    35.     }
    36.  
    37.     @Override
    38.     public List selectByIn(List bookIds) {
    39.         return bookMapper.selectByIn(bookIds);
    40.     }
    41.  
    42.     @Override
    43.     public List selectBooksLike1(String bname) {
    44.         return bookMapper.selectBooksLike1(bname);
    45.     }
    46.  
    47.     @Override
    48.     public List selectBooksLike2(String bname) {
    49.         return bookMapper.selectBooksLike2(bname);
    50.     }
    51.  
    52.     @Override
    53.     public List selectBooksLike3(String bname) {
    54.         return bookMapper.selectBooksLike3(bname);
    55.     }
    56.  
    57.     @Override
    58.     public List list1() {
    59.         return bookMapper.list1();
    60.     }
    61.  
    62.     @Override
    63.     public List list2() {
    64.         return bookMapper.list2();
    65.     }
    66.  
    67.     @Override
    68.     public List list3(BookVo vo) {
    69.         return bookMapper.list3(vo);
    70.     }
    71.  
    72.     @Override
    73.     public List list4() {
    74.         return bookMapper.list4();
    75.     }
    76.  
    77.     @Override
    78.     public Map list5(Map map) {
    79.         return bookMapper.list5(map);
    80.     }
    81.  
    82.     @Override
    83.     public List listPager(Map map, PageBean pageBean) {
    84. //        pageHelper分页插件相关的代码
    85.         if(pageBean!=null&&pageBean.isPagination()){
    86.             PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
    87.         }
    88.         List maps = bookMapper.listPager(map);
    89.         if(pageBean!=null&&pageBean.isPagination()){
    90. //            处理查询结果的前提是需要分页，是需要分页的
    91.             PageInfo info = new PageInfo(maps);
    92.             pageBean.setTotal(info.getTotal()+"");
    93.         }
    94.         return maps;
    95.     }
    96.  
    97.     @Override
    98.     public List list6(BookVo bookVo) {
    99.         return bookMapper.list6(bookVo);
    100.     }
    101.  
    102.     @Override
    103.     public List list7(BookVo bookVo) {
    104.         return bookMapper.list7(bookVo);
    105.     }
    106. }
    107.

    4、BookMaper.xml

    2. mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    3. <mapper namespace="com.liaoxin.mapper.BookMapper" >
    4.   <resultMap id="BaseResultMap" type="com.liaoxin.model.Book" >
    5.     <constructor >
    6.       <idArg column="bid" jdbcType="INTEGER" javaType="java.lang.Integer" />
    7.       <arg column="bname" jdbcType="VARCHAR" javaType="java.lang.String" />
    8.       <arg column="price" jdbcType="REAL" javaType="java.lang.Float" />
    9.     constructor>
    10.   resultMap>
    11.   <sql id="Base_Column_List" >
    12.     bid, bname, price
    13.   sql>
    14.   <select id="selectByPrimaryKey" resultType="com.liaoxin.model.Book" parameterType="java.lang.Integer" >
    15.     select 
    16.     <include refid="Base_Column_List" />
    17.     from t_mvc_book
    18.     where bid = #{bid,jdbcType=INTEGER}
    19.   select>
    20.  
    21.   
    22.   <select id="selectByIn" resultType="com.liaoxin.model.Book" parameterType="java.util.List" >
    23.     select
    24.     <include refid="Base_Column_List" />
    25.     from t_mvc_book
    26.     where bid in
    27.     <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    28.       #{bid}
    29.     foreach>
    30.   select>
    31.  
    32.   <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    33.     delete from t_mvc_book
    34.     where bid = #{bid,jdbcType=INTEGER}
    35.   delete>
    36.   <insert id="insert" parameterType="com.liaoxin.model.Book" >
    37.     insert into t_mvc_book (bid, bname, price
    38.       )
    39.     values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
    40.       )
    41.   insert>
    42.   <insert id="insertSelective" parameterType="com.liaoxin.model.Book" >
    43.     insert into t_mvc_book
    44.     <trim prefix="(" suffix=")" suffixOverrides="," >
    45.       <if test="bid != null" >
    46.         bid,
    47.       if>
    48.       <if test="bname != null" >
    49.         bname,
    50.       if>
    51.       <if test="price != null" >
    52.         price,
    53.       if>
    54.     trim>
    55.     <trim prefix="values (" suffix=")" suffixOverrides="," >
    56.       <if test="bid != null" >
    57.         #{bid,jdbcType=INTEGER},
    58.       if>
    59.       <if test="bname != null" >
    60.         #{bname,jdbcType=VARCHAR},
    61.       if>
    62.       <if test="price != null" >
    63.         #{price,jdbcType=REAL},
    64.       if>
    65.     trim>
    66.   insert>
    67.   <update id="updateByPrimaryKeySelective" parameterType="com.liaoxin.model.Book" >
    68.     update t_mvc_book
    69.     <set >
    70.       <if test="bname != null" >
    71.         bname = #{bname,jdbcType=VARCHAR},
    72.       if>
    73.       <if test="price != null" >
    74.         price = #{price,jdbcType=REAL},
    75.       if>
    76.     set>
    77.     where bid = #{bid,jdbcType=INTEGER}
    78.   update>
    79.   <update id="updateByPrimaryKey" parameterType="com.liaoxin.model.Book" >
    80.     update t_mvc_book
    81.     set bname = #{bname,jdbcType=VARCHAR},
    82.       price = #{price,jdbcType=REAL}
    83.     where bid = #{bid,jdbcType=INTEGER}
    84.   update>
    85.  
    86.   <select id="selectBooksLike1" resultType="com.liaoxin.model.Book" parameterType="java.lang.String">
    87.   select * from t_mvc_book where bname like #{bname}
    88. select>
    89.   <select id="selectBooksLike2" resultType="com.liaoxin.model.Book" parameterType="java.lang.String">
    90.   select * from t_mvc_book where bname like '${bname}'
    91. select>
    92.   <select id="selectBooksLike3" resultType="com.liaoxin.model.Book" parameterType="java.lang.String">
    93.   select * from t_mvc_book where bname like concat('%',#{bname},'%')
    94. select>
    95.  
    96.   <select id="list1" resultMap="BaseResultMap">
    97.   select * from t_mvc_book
    98. select>
    99.   <select id="list2" resultType="com.liaoxin.model.Book">
    100.   select * from t_mvc_book
    101. select>
    102.   <select id="list3" resultType="com.liaoxin.model.Book" parameterType="com.liaoxin.model.BookVo">
    103.     select * from t_mvc_book where bid in
    104.     <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    105.       #{bid}
    106.     foreach>
    107.   select>
    108.   <select id="list4" resultType="java.util.Map">
    109.   select * from t_mvc_book
    110. select>
    111.   <select id="list5" resultType="java.util.Map" parameterType="java.util.Map">
    112.   select * from t_mvc_book where bid = #{bid}
    113. select>
    114.  
    115.   <select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">
    116.   select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
    117. select>
    118.  
    119.   <select id="list6" resultType="com.liaoxin.model.Book" parameterType="com.liaoxin.model.BookVo">
    120.     select * from t_mvc_book
    121.     <where>
    122.       <if test="null != min and min != ''">
    123.         
    124.       if>
    125.       <if test="null != max and max != ''">
    126.          price ]]>
    127.       if>
    128.     where>
    129.   select>
    130.  
    131.   <select id="list7" resultType="com.liaoxin.model.Book" parameterType="com.liaoxin.model.BookVo">
    132.     select * from t_mvc_book
    133.     <where>
    134.       <if test="null != min and min != ''">
    135.         and #{min} < price
    136.       if>
    137.       <if test="null != max and max != ''">
    138.         and #{max} > price
    139.       if>
    140.     where>
    141.   select>
    142.  
    143.  
    144.  
    145. mapper>

    5、BookMapper.java

    1. package com.liaoxin.mapper;
    2.  
    3. import com.liaoxin.model.Book;
    4. import com.liaoxin.model.BookVo;
    5. import org.apache.ibatis.annotations.Param;
    6.  
    7. import java.util.List;
    8. import java.util.Map;
    9.  
    10. public interface BookMapper {
    11.     int deleteByPrimaryKey(Integer bid);
    12.  
    13.     int insert(Book record);
    14.  
    15.     int insertSelective(Book record);
    16.  
    17.     Book selectByPrimaryKey(Integer bid);
    18.  
    19.     int updateByPrimaryKeySelective(Book record);
    20.  
    21.     int updateByPrimaryKey(Book record);
    22.  
    23. //  通过in关键字进行查询：讲解foreach 标签的使用
    24. //    如果说参数是非实体类(book,Order,....)，那么急的加上注解 @param,bookIds是对应collection属性的
    25.     List selectByIn(@Param("bookIds") List bookIds);
    26.  
    27.     List selectBooksLike1(@Param("bname") String bname);
    28.     List selectBooksLike2(@Param("bname") String bname);
    29.     List selectBooksLike3(@Param("bname") String bname);
    30.  
    31. //    list1 list2的结论是，对于单表查询而言，可以用resultmap/resulttype接收，但是多表必须用resultmap接收
    32.     List list1();
    33.     List list2();
    34. //    如果要传入多个查询参数，必须以对象的方式进行传递
    35. //    举例：select * from t_mvc_book where bid in (1,2,3,4,5,6) and bname in ("圣墟","不死不休")
    36.     List list3(BookVo vo);
    37. //    说明了不管返回1条数据，还是多条数据，都应该用java.util.Map进行接受
    38. //    如果是1条数据，那么返回值是Map
    39. //    如果是多条数据，那么返回值List
    40.     List list4();
    41.     Map list5(Map map);
    42.  
    43. //    利用第三方插件进行分页
    44.     List listPager(Map map);
    45.  
    46.     /**
    47.      * 处理特殊字符
    48.      * @param bookVo
    49.      * @return
    50.      */
    51.     List list6(BookVo bookVo);
    52.  
    53.  
    54.     /**
    55.      * 处理特殊字符
    56.      * @param bookVo
    57.      * @return
    58.      */
    59.     List list7(BookVo bookVo);
    60.  
    61.  
    62. }

    6、BookBizImplTest

    1. package com.liaoxin.biz.impl;
    2.  
    3. import com.liaoxin.biz.BookBiz;
    4. import com.liaoxin.mapper.BookMapper;
    5. import com.liaoxin.model.BookVo;
    6. import com.liaoxin.util.PageBean;
    7. import com.liaoxin.util.SessionUtil;
    8. import org.apache.ibatis.session.SqlSession;
    9. import org.junit.After;
    10. import org.junit.Before;
    11. import org.junit.Test;
    12. import org.junit.runner.RunWith;
    13.  
    14. import java.util.Arrays;
    15. import java.util.HashMap;
    16. import java.util.List;
    17. import java.util.Map;
    18.  
    19. import static org.junit.Assert.*;
    20.  
    21. public class BookBizImplTest {
    22.     private BookBizImpl bookBiz;
    23.     private SqlSession sqlSession;
    24.  
    25.     @Before
    26.     public void setUp() throws Exception {
    27.         System.out.println("初始化方法");
    28.         BookBizImpl bookBiz =new BookBizImpl();
    29. //        工具类中获取session对象
    30.         sqlSession = SessionUtil.openSession();
    31. //        从session对象中获取Mapper对象
    32.         BookMapper mapper = sqlSession.getMapper(BookMapper.class);
    33.         bookBiz.setBookMapper(mapper);
    34.         this.bookBiz=bookBiz;
    35.     }
    36.  
    37.     @After
    38.     public void tearDown() throws Exception {
    39.         System.out.println("方法测试结束");
    40.         sqlSession.commit();
    41.         sqlSession.close();
    42.     }
    43.  
    44.     @Test
    45.     public void deleteByPrimaryKey() {
    46.         bookBiz.deleteByPrimaryKey(44);
    47.     }
    48.  
    49.     @Test
    50.     public void selectByPrimaryKey() {
    51.         System.out.println("测试的业务方法");
    52. //        System.out.println(bookBiz.getBookMapper());
    53.         System.out.println(bookBiz.selectByPrimaryKey(44));
    54.     }
    55.  
    56.     @Test
    57.     public void test3() {
    58.         int[] ints={};
    59. //        将数组变成字符串 1,2,3,4,5,6
    60.         StringBuffer sb=new StringBuffer();
    61.         for (int i:ints){
    62.             sb.append(",").append(i);
    63.         }
    64.         String s=sb.toString();
    65.         System.out.println(s.substring(1));
    66.     }
    67.  
    68.     @Test
    69.     public void getBookMapper() {
    70.     }
    71.  
    72.     @Test
    73.     public void setBookMapper() {
    74.     }
    75.  
    76.     @Test
    77.     public void selectByIn() {
    78.         List bookIds = Arrays.asList(new Integer[]{31, 32, 33, 34});
    79.         bookBiz.selectByIn(bookIds).forEach(System.out::println);
    80.     }
    81.  
    82.     @Test
    83.     public void selectBooksLike1() {
    84.         bookBiz.selectBooksLike1("%圣墟%").forEach(System.out::println);
    85.     }
    86.  
    87.     @Test
    88.     public void selectBooksLike2() {
    89.         bookBiz.selectBooksLike2("%圣墟%").forEach(System.out::println);
    90.     }
    91.  
    92.     @Test
    93.     public void selectBooksLike3() {
    94.         bookBiz.selectBooksLike3("圣墟").forEach(System.out::println);
    95.     }
    96.  
    97.     @Test
    98.     public void list1() {
    99.         bookBiz.list1().forEach(System.out::println);
    100.     }
    101.  
    102.     @Test
    103.     public void list2() {
    104.         bookBiz.list2().forEach(System.out::println);
    105.     }
    106.  
    107.     @Test
    108.     public void list3() {
    109.         BookVo vo=new BookVo();
    110.         vo.setBookIds(Arrays.asList(new Integer[]{31,32,33,34}));
    111.         bookBiz.list3(vo).forEach(System.out::println);
    112.     }
    113.  
    114.     @Test
    115.     public void list4() {
    116.         bookBiz.list4().forEach(System.out::println);
    117.     }
    118.  
    119.     @Test
    120.     public void list5() {
    121.         Map map=new HashMap();
    122.         map.put("bid",32);
    123.         System.out.println(bookBiz.list5(map));
    124.     }
    125.  
    126.     @Test
    127.     public void listPager() {
    128.         Map map=new HashMap();
    129.         map.put("bname","圣墟");
    130. //        bookBiz.listPager(map).forEach(System.out::println);
    131.  
    132. //        查询出第二页的20条数据
    133.         PageBean pageBean = new PageBean();
    134.         pageBean.setPage(2);
    135.         pageBean.setRows(20);
    136.  
    137.         bookBiz.listPager(map,pageBean).forEach(System.out::println);
    138.     }
    139.  
    140.     @Test
    141.     public void list6() {
    142.         BookVo vo=new BookVo();
    143.         vo.setMax(45);
    144.         vo.setMin(35);
    145.         bookBiz.list6(vo).forEach(System.out::println);
    146.     }
    147.  
    148.     @Test
    149.     public void list7() {
    150.         BookVo vo=new BookVo();
    151.         vo.setMax(45);
    152.         vo.setMin(35);
    153.         bookBiz.list7(vo).forEach(System.out::println);
    154.     }
    155. }
    156.

  • 相关阅读:
    【JS 的数据类型】
    CSDN21天学习挑战赛——封装（06）
    【机器学习】随机种子Random Seed介绍（在Python、Pytorch、TensorFlow中的设置代码汇总）
    [BAT-表姐御用02tree命令】提取目录文件夹及其子文件后，tree形列出
    C++游戏后端开发(魔兽世界,MMO,TrinityCore源码拆解) 教程
    如何应对老板需求？
    内边距（padding会影响盒子内边距大小）
    病毒感染检测(运用BF算法)
    【每日两题】day 01 组队竞赛 & 删除公共字符
    C++演变历史
  • 原文地址：https://blog.csdn.net/qq_44247968/article/details/126298040