• Mybatis的分页和动态sql

    目录

    一：forEach

    BookMapper.xml

     BookMapper.java

    BookBiz 

    BookBizImpl 

    BookBizImplTest

     效果图

    二：模糊查询

    BookBizImplTest

     BookMapper.java

    BookBiz

     BookBizImpl 

    BookMapper.xml 

    效果图

    三：查询分页

    BookBizImplTest

     BookMapper.java

    BookBiz

    BookMapper.xml 

     BookBizImpl 

    效果图 

    四：结果集

    BookBizImplTest

     BookMapper.java

    BookMapper.xml 

    BookBiz

    BookBizImpl 

    效果图 

    五：特殊符号

    BookVo

    BookMapper.xml 

    一：forEach

    BookMapper.xml

    1.   <select id="selectByIn" resultType="com.zking.model.Book" parameterType="java.util.List">
    2.     select
    3.     "Base_Column_List" />
    4.     from t_mvc_book
    5.     where bid in
    6.     <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    7.       #{bid}
    8.     foreach>
    9.   select>

     BookMapper.java

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

    BookBiz 

     List<Book> selectByIn(List bookIds);

    BookBizImpl 

    1.   public List<Book> selectByIn(List bookIds){
    2.         return bookMapper.selectByIn(bookIds);
    3.     }

    BookBizImplTest

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

     效果图

    二：模糊查询

    #{...}

    ${...}

    Concat

    注意：#{...}自带引号，${...}有sql注入的风险

    BookBizImplTest

    1.  @Test
    2.     public void selectBooksLike1() {
    3.         bookBiz.selectBooksLike1("%圣墟%").forEach(System.out::println);
    4.     }
    5.  
    6.     @Test
    7.     public void selectBooksLike2() {
    8.         bookBiz.selectBooksLike2("%圣墟%").forEach(System.out::println);
    9.     }
    10.  
    11.     @Test
    12.     public void selectBooksLike3() {
    13.         bookBiz.selectBooksLike3("圣墟").forEach(System.out::println);
    14.     }
    15.  
    16.     @Test
    17.     public void list1() {
    18.         bookBiz.list1().forEach(System.out::println);
    19.     }
    20.  
    21.     @Test
    22.     public void list2() {
    23.         bookBiz.list2().forEach(System.out::println);
    24.     }
    25.     @Test
    26.     public void list3() {
    27.         BookVo vo=new BookVo();
    28.         vo.setBookIds(Arrays.asList(new Integer[]{31,32,33}));
    29.         bookBiz.list3(vo).forEach(System.out::println);
    30.     }
    31.  
    32.     @Test
    33.     public void list4() {
    34.         bookBiz.list4().forEach(System.out::println);
    35.     }
    36.  
    37.     @Test
    38.     public void list5() {
    39.         Map map=new HashMap();
    40.         map.put("bid",32);
    41.         System.out.println(bookBiz.list5(map));
    42.     }

     BookMapper.java

    1.  List<Book> selectBooksLike1(@Param("bname") String bname);
    2.     List<Book> selectBooksLike2(@Param("bname") String bname);
    3.     List<Book> selectBooksLike3(@Param("bname") String bname);
    4.  
    5. //    list1 list2的结论是：对于单表查询而言，可以用它ResultMap/resultType接收，但是多表必须用ResultMap接收
    6.     List<Book> list1();
    7.     List<Book> list2();

    BookBiz

    1.    List list1();
    2.     List list2();
    3.     //    如果要传入多个查询参数，必须以对象的方式传递
    4.     List list3(BookVo vo);
    5.     List list4();
    6.     Map list5(Map map);

     BookBizImpl 

    1.  @Override
    2.     public List<Book> list1() {
    3.         return bookMapper.list1();
    4.     }
    5.  
    6.     @Override
    7.     public List<Book> list2() {
    8.         return bookMapper.list2();
    9.     }
    10.  
    11.     @Override
    12.     public List<Book> list3(BookVo vo) {
    13.         return bookMapper.list3(vo);
    14.     }
    15.  
    16.     @Override
    17.     public List<Map> list4() {
    18.         return bookMapper.list4();
    19.     }
    20.  
    21.     @Override
    22.     public Map list5(Map map) {
    23.         return bookMapper.list5(map);
    24.     }

    BookMapper.xml 

    1.  <select id="selectBooksLike1" resultType="com.zking.model.Book" parameterType="java.lang.String">
    2.   select * from t_mvc_book where bname like #{bname}
    3. select>
    4.   <select id="selectBooksLike2" resultType="com.zking.model.Book" parameterType="java.lang.String">
    5.   select * from t_mvc_book where bname like '${bname}'
    6. select>
    7.   <select id="selectBooksLike3" resultType="com.zking.model.Book" parameterType="java.lang.String">
    8.   select * from t_mvc_book where bname like concat('%',#{bname},'%')
    9. select>
    11.   <select id="list1" resultMap="BaseResultMap">
    12.   select * from t_mvc_book
    13. select>
    14.  
    15.   <select id="list2" resultType="java.zking.model.Book">
    16.   select * from t_mvc_book
    17. select>
    18.  
    19.   <select id="list3" resultType="com.zking.model.Book" parameterType="java.zking.model.BookVo">
    20.   select * from t_mvc_book where bid in
    21.   <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    22.     #{bid}
    23.   foreach>
    24. select>
    25.  
    26.   <select id="list4" resultType="java.util.Map">
    27.   select * from t_mvc_book
    28. select>
    29.  
    30.   <select id="list5" parameterType="java.util.Map" resultType="java.util.Map">
    31.   select * from t_mvc_book where bid=#{bid}
    32. select>

    效果图

    三：查询分页

    BookBizImplTest

    1. public void listPager(){
    2.         Map map=new HashMap();
    3.         map.put("bnam","圣墟");
    4. //        bookBiz.listPager(map).forEach(System.out::println);
    5.  
    6.         PagerBean pagerBean=new PagerBean();
    7.         pagerBean.setPage(2);
    8.         pagerBean.setRows(20);
    9.  
    10.         bookBiz.listPager(map,pagerBean).forEach(System.out::println);
    11.     }

     BookMapper.java

    List<Map> listPager(Map map);

    BookBiz

     List<Map> listPager(Map map, PagerBean pagerBean);

    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<Map> listPager(Map map, PagerBean pagerBean) {
    3. //        pagerHelper分页插件相关代码
    4.         if(pagerBean !=null && pagerBean.isPagination()){
    5.             pageHelper.startPage(pagerBean.getPage(),pagerBean.getRows());
    6.         }
    7.  
    8.         List<Map> maps=bookMapper.listPager(map);
    9.         if(pagerBean !=null && pagerBean.isPagination()){
    10. //            处理查询结果的前提是，需要分页的
    11.             PageInfo info =new PageInfo(maps);
    12.             pagerBean.setTotal(info.getTotal()+"");
    13.         }
    14.  
    15.         return maps;
    16.     }

    效果图 

    四：结果集

    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.     }

     BookMapper.java

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

    BookMapper.xml 

    1. <select id="list6" resultType="com.javaxl.model.Book" parameterType="com.javaxl.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.        price ]]>
    11.     where>
    12.   select>
    13.  
    14.   <select id="list7" resultType="com.zking.model.Book" parameterType="com.zking.model.BookVo">
    15.     select * from t_mvc_book
    16.     <where>
    17.       <if test="null != min and min != ''">
    18.         and #{min} < price
    19.       if>
    20.       <if test="null != max and max != ''">
    21.         and #{max} > price
    22.       if>
    23.     where>
    24.   select>

    BookBiz

    1. List<Book> list6(BookVo bookVo);
    2.     List<Book> list7(BookVo bookVo);

    BookBizImpl 

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

    效果图 

    五：特殊符号

    BookVo

    1. public class BookVo extends Book{
    2.     private List bookIds;
    3.     private int min;
    4.     private int max;
    5.  
    6.     public int getmin(){
    7.         return min;
    8.     }
    9.  
    10.     public void setmin(int min){
    11.         this.min=min;
    12.     }
    13.     public int getmax(){
    14.         return max;
    15.     }
    16.  
    17.     public void setmax(int max){
    18.         this.max=max;
    19.     }

    BookMapper.xml 

    1. <select id="list6" resultType="com.javaxl.model.Book" parameterType="com.javaxl.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.        price ]]>
    11.     where>
    12.   select>
    13.  
    14.   <select id="list7" resultType="com.zking.model.Book" parameterType="com.zking.model.BookVo">
    15.     select * from t_mvc_book
    16.     <where>
    17.       <if test="null != min and min != ''">
    18.         and #{min} < price
    19.       if>
    20.       <if test="null != max and max != ''">
    21.         and #{max} > price
    22.       if>
    23.     where>
    24.   select>

     

  • 相关阅读:
    Python 基础入门指南，干货分享来啦！
    第一本Docker书，带你走进微服务架构的奇妙世界！手撕面试官
    Spring Cache + Caffeine的整合与使用
    list 模拟与用法
    如何搭建node_exporter
    文章聚合怎么进行文章伪原创
    Java-比较器Comparable与Comparator（详解）
    架构安全性设计、部分示例及原理分析
    【STM32】HAL库ADC多通道精准测量（采用VREFINT内部参考电压）
    Harmony import和export
  • 原文地址：https://blog.csdn.net/m0_67864917/article/details/126298799