• Mybatis之动态sql和分页

    目录

    一、IDEA使用注意事项

     二.Mybatis动态SQL

     三、模糊查询

    四、结果集的处理

     五、第三方分页插件集成Mybatis使用

     六、特殊字符处理

    一、IDEA使用注意事项

    在同一个项目中 尽可能避免 多个模块中取同样的类名

     二.Mybatis动态SQL

    没有用foreach标签：

    1.     @Test
    2.     public void test3() {
    3.         int[] ints = {1,2,3,4,5,6};
    4.         //将数组变成字符串
    5.         StringBuffer sb = new StringBuffer();
    6.         for (int i:ints){
    7.                sb.append(",").append(i);
    8.         }
    9.          String s = sb.toString();
    10.         System.out.println(s.substring(1));
    11.     }

     结果：

     

     这时候报错 这时就要用到foreach标签了

    foreach标签

    BookMapper.xml 写配置文件 selectByIn 写好SQL语句 然后写一个对应的方法在接口类中

    selectByIn 仿照 selectByPrimaryKey 写

    2. mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    3. <mapper namespace="com.cdl.mapper.BookMapper" >
    4.   <resultMap id="BaseResultMap" type="com.cdl.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.  
    15.  
    16.   <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    17.     select
    18.     <include refid="Base_Column_List" />
    19.     from t_mvc_book
    20.     where bid = #{bid,jdbcType=INTEGER}
    21.   select>
    22.  
    24.   <select id="selectByIn" resultMap="BaseResultMap" parameterType="java.util.List" >
    25.     select 
    26.     <include refid="Base_Column_List" />
    27.     from t_mvc_book
    28.     where bid in 
    29.     <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    30.       #{bid}
    31.     foreach>
    32.   select>
    33.  
    34.  
    35.  
    36.  
    37.   <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    38.     delete from t_mvc_book
    39.     where bid = #{bid,jdbcType=INTEGER}
    40.   delete>
    41.   <insert id="insert" parameterType="com.cdl.model.Book" >
    42.     insert into t_mvc_book (bid, bname, price
    43.       )
    44.     values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
    45.       )
    46.   insert>
    47.   <insert id="insertSelective" parameterType="com.cdl.model.Book" >
    48.     insert into t_mvc_book
    49.     <trim prefix="(" suffix=")" suffixOverrides="," >
    50.       <if test="bid != null" >
    51.         bid,
    52.       if>
    53.       <if test="bname != null" >
    54.         bname,
    55.       if>
    56.       <if test="price != null" >
    57.         price,
    58.       if>
    59.     trim>
    60.     <trim prefix="values (" suffix=")" suffixOverrides="," >
    61.       <if test="bid != null" >
    62.         #{bid,jdbcType=INTEGER},
    63.       if>
    64.       <if test="bname != null" >
    65.         #{bname,jdbcType=VARCHAR},
    66.       if>
    67.       <if test="price != null" >
    68.         #{price,jdbcType=REAL},
    69.       if>
    70.     trim>
    71.   insert>
    72.   <update id="updateByPrimaryKeySelective" parameterType="com.cdl.model.Book" >
    73.     update t_mvc_book
    74.     <set >
    75.       <if test="bname != null" >
    76.         bname = #{bname,jdbcType=VARCHAR},
    77.       if>
    78.       <if test="price != null" >
    79.         price = #{price,jdbcType=REAL},
    80.       if>
    81.     set>
    82.     where bid = #{bid,jdbcType=INTEGER}
    83.   update>
    84.   <update id="updateByPrimaryKey" parameterType="com.cdl.model.Book" >
    85.     update t_mvc_book
    86.     set bname = #{bname,jdbcType=VARCHAR},
    87.       price = #{price,jdbcType=REAL}
    88.     where bid = #{bid,jdbcType=INTEGER}
    89.   update>
    90. mapper>
    BookMapper
    1. package com.cdl.mapper;
    2.  
    3. import com.cdl.model.Book;
    4.  
    5. import java.util.List;
    6.  
    7. public interface BookMapper {
    8.     int deleteByPrimaryKey(Integer bid);
    9.  
    10.     int insert(Book record);
    11.  
    12.     int insertSelective(Book record);
    13.  
    14.     Book selectByPrimaryKey(Integer bid);
    15.  
    16.     int updateByPrimaryKeySelective(Book record);
    17.  
    18.     int updateByPrimaryKey(Book record);
    19.  
    20.     //通过in关键字进行查询 熟悉foreach标签的使用
    21.    //如果说参数是非实体类  那么记得加上注解 @param,bookIds是对应collection="bookIds"
    22.     List selectByIn(@Param("bookIds") List bookIds);
    23.    
    24. }
    1. package com.cdl.biz.impl;
    2.  
    3. import com.cdl.biz.BookBiz;
    4. import com.cdl.mapper.BookMapper;
    5. import com.cdl.model.Book;
    6.  
    7. import java.util.List;
    8.  
    9. /**
    10.  * @author cdl
    11.  * @site www.cdl.com
    12.  * @create 2022-08-10 22:49
    13.  */
    14. public class BookBizImpl implements BookBiz {
    15.  
    16.     private BookMapper bookMapper;
    17.  
    18.     //alt + insert 快速提供set/get/tostring/构造函数
    19.     //alt +enter 快速构建实现类 能够自动补全
    20.  
    21.     public BookMapper getBookMapper() {
    22.         return bookMapper;
    23.     }
    24.  
    25.     public void setBookMapper(BookMapper bookMapper) {
    26.         this.bookMapper = bookMapper;
    27.     }
    28.  
    29.     @Override
    30.     public int deleteByPrimaryKey(Integer bid) {
    31.         return bookMapper.deleteByPrimaryKey(bid);
    32.     }
    33.  
    34.     @Override
    35.     public Book selectByPrimaryKey(Integer bid) {
    36.         return bookMapper.selectByPrimaryKey(bid);
    37.     }
    38.  
    39.     @Override
    40.     public List selectByIn(List bookIds) {
    41.         return bookMapper.selectByIn(bookIds);
    42.     }
    43. }

    测试类：
     

    1. package com.cdl.biz.impl;
    2.  
    3. import com.cdl.mapper.BookMapper;
    4. import com.cdl.util.SessionUtil;
    5. import org.apache.ibatis.session.SqlSession;
    6. import org.junit.After;
    7. import org.junit.Before;
    8. import org.junit.Test;
    9.  
    10. import java.util.Arrays;
    11.  
    12. /**
    13.  * @author cdl
    14.  * @site www.cdl.com
    15.  * @create 2022-08-10 22:55
    16.  */
    17. public class BookBizImplTest {
    18.  
    19.     private BookBizImpl bookBiz;
    20.     SqlSession sqlSession;
    21.  
    22.     @Before
    23.     public void setUp() throws Exception {
    24.         System.out.println("初始换方法。。。");
    25.       BookBizImpl  bookBiz = new BookBizImpl();
    26.       //工具类中获取session对象
    27.       sqlSession = SessionUtil.openSession();
    28.      //从session对象中获取mapper对象
    29.       BookMapper mapper = sqlSession.getMapper(BookMapper.class);
    30.         bookBiz.setBookMapper(mapper);
    31.         this.bookBiz = bookBiz;
    32.     }
    33.  
    34.     @After
    35.     public void tearDown() throws Exception {
    36.         System.out.println("方法测试结束。。");
    37.     }
    38.  
    39.     @Test
    40.     public void deleteByPrimaryKey() {
    41.     }
    42.  
    43.     @Test
    44.     public void selectByPrimaryKey() {
    45.  
    46.         System.out.println("测试的业务方法。。。");
    47.         //System.out.println(bookBiz.getBookMapper());
    48.         System.out.println(bookBiz.selectByPrimaryKey(44));
    49.     }
    50.  
    51.     @Test
    52.     public void test3() {
    53.         int[] ints = {};
    54.         //将数组变成字符串
    55.         StringBuffer sb = new StringBuffer();
    56.         for (int i:ints){
    57.                sb.append(",").append(i);
    58.         }
    59.          String s = sb.toString();
    60.         System.out.println(s.substring(1));
    61.     }
    62.  
    63.     @Test
    64.     public void selectByIn(){
    65.         bookBiz.selectByIn(Arrays.asList(new Integer[]{31,32,33,34})).forEach(System.out::println);
    66.  
    67.     }
    68.  
    69.  
    70. }

    结果：

     三、模糊查询

    #{...}

    ${...}

    Concat

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

    BookMapper.xml

    2. mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    3. <mapper namespace="com.cdl.mapper.BookMapper" >
    4.   <resultMap id="BaseResultMap" type="com.cdl.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.  
    15.  
    16.   <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    17.     select
    18.     <include refid="Base_Column_List" />
    19.     from t_mvc_book
    20.     where bid = #{bid,jdbcType=INTEGER}
    21.   select>
    22.  
    24.   <select id="selectByIn" resultMap="BaseResultMap" parameterType="java.util.List" >
    25.     select 
    26.     <include refid="Base_Column_List" />
    27.     from t_mvc_book
    28.     where bid in 
    29.     <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
    30.       #{bid}
    31.     foreach>
    32.   select>
    33.  
    34.  
    35.  
    36.  
    37.   <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    38.     delete from t_mvc_book
    39.     where bid = #{bid,jdbcType=INTEGER}
    40.   delete>
    41.   <insert id="insert" parameterType="com.cdl.model.Book" >
    42.     insert into t_mvc_book (bid, bname, price
    43.       )
    44.     values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL}
    45.       )
    46.   insert>
    47.   <insert id="insertSelective" parameterType="com.cdl.model.Book" >
    48.     insert into t_mvc_book
    49.     <trim prefix="(" suffix=")" suffixOverrides="," >
    50.       <if test="bid != null" >
    51.         bid,
    52.       if>
    53.       <if test="bname != null" >
    54.         bname,
    55.       if>
    56.       <if test="price != null" >
    57.         price,
    58.       if>
    59.     trim>
    60.     <trim prefix="values (" suffix=")" suffixOverrides="," >
    61.       <if test="bid != null" >
    62.         #{bid,jdbcType=INTEGER},
    63.       if>
    64.       <if test="bname != null" >
    65.         #{bname,jdbcType=VARCHAR},
    66.       if>
    67.       <if test="price != null" >
    68.         #{price,jdbcType=REAL},
    69.       if>
    70.     trim>
    71.   insert>
    72.   <update id="updateByPrimaryKeySelective" parameterType="com.cdl.model.Book" >
    73.     update t_mvc_book
    74.     <set >
    75.       <if test="bname != null" >
    76.         bname = #{bname,jdbcType=VARCHAR},
    77.       if>
    78.       <if test="price != null" >
    79.         price = #{price,jdbcType=REAL},
    80.       if>
    81.     set>
    82.     where bid = #{bid,jdbcType=INTEGER}
    83.   update>
    84.   <update id="updateByPrimaryKey" parameterType="com.cdl.model.Book" >
    85.     update t_mvc_book
    86.     set bname = #{bname,jdbcType=VARCHAR},
    87.       price = #{price,jdbcType=REAL}
    88.     where bid = #{bid,jdbcType=INTEGER}
    89.   update>
    90.  
    91.  
    92.   <select id="selectBooksLike1" resultType="com.cdl.model.Book" parameterType="java.lang.String">
    93.   select * from t_mvc_book where bname like #{bname}
    94. select>
    95.   <select id="selectBooksLike2" resultType="com.cdl.model.Book" parameterType="java.lang.String">
    96.   select * from t_mvc_book where bname like '${bname}'
    97. select>
    98.   <select id="selectBooksLike3" resultType="com.cdl.model.Book" parameterType="java.lang.String">
    99.   select * from t_mvc_book where bname like concat('%',#{bname},'%')
    100. select>
    101.  
    102.  
    103.  
    104.  
    105. mapper>
    interface BookMapper 
    1. package com.cdl.mapper;
    2.  
    3. import com.cdl.model.Book;
    4. import org.apache.ibatis.annotations.Param;
    5.  
    6. import java.util.List;
    7.  
    8. public interface BookMapper {
    9.     int deleteByPrimaryKey(Integer bid);
    10.  
    11.     int insert(Book record);
    12.  
    13.     int insertSelective(Book record);
    14.  
    15.     Book selectByPrimaryKey(Integer bid);
    16.  
    17.     int updateByPrimaryKeySelective(Book record);
    18.  
    19.     int updateByPrimaryKey(Book record);
    20.  
    21.     //通过in关键字进行查询 熟悉foreach标签的使用
    22.     //如果说参数是非实体类  那么记得加上注解 @param,bookIds是对应collection="bookIds"
    23.     List selectByIn(@Param("bookIds") List bookIds);
    24.  
    25.  
    26.     List selectBooksLike1(@Param("bname") String bname);
    27.     List selectBooksLike2(@Param("bname") String bname);
    28.     List selectBooksLike3(@Param("bname") String bname);
    29. }
    interface BookBiz
    1. package com.cdl.biz;
    2.  
    3. import com.cdl.model.Book;
    4.  
    5. import java.util.List;
    6.  
    7. /**
    8.  * @author cdl
    9.  * @site www.cdl.com
    10.  * @create 2022-08-10 22:00
    11.  */
    12. public interface BookBiz {
    13.     int deleteByPrimaryKey(Integer bid);
    14.  
    15.  
    16.     Book selectByPrimaryKey(Integer bid);
    17.  
    18.     List selectByIn( List bookIds);
    19.  
    20.     List selectBooksLike1(String bname);
    21.     List selectBooksLike2(String bname);
    22.     List selectBooksLike3(String bname);
    23. }
    BookBizImpl
    1. package com.cdl.biz.impl;
    2.  
    3. import com.cdl.biz.BookBiz;
    4. import com.cdl.mapper.BookMapper;
    5. import com.cdl.model.Book;
    6.  
    7. import java.util.List;
    8.  
    9. /**
    10.  * @author cdl
    11.  * @site www.cdl.com
    12.  * @create 2022-08-10 22:49
    13.  */
    14. public class BookBizImpl implements BookBiz {
    15.  
    16.     private BookMapper bookMapper;
    17.  
    18.     //alt + insert 快速提供set/get/tostring/构造函数
    19.     //alt +enter 快速构建实现类 能够自动补全
    20.  
    21.     public BookMapper getBookMapper() {
    22.         return bookMapper;
    23.     }
    24.  
    25.     public void setBookMapper(BookMapper bookMapper) {
    26.         this.bookMapper = bookMapper;
    27.     }
    28.  
    29.     @Override
    30.     public int deleteByPrimaryKey(Integer bid) {
    31.         return bookMapper.deleteByPrimaryKey(bid);
    32.     }
    33.  
    34.     @Override
    35.     public Book selectByPrimaryKey(Integer bid) {
    36.         return bookMapper.selectByPrimaryKey(bid);
    37.     }
    38.  
    39.     @Override
    40.     public List selectByIn(List bookIds) {
    41.         return bookMapper.selectByIn(bookIds);
    42.     }
    43.  
    44.  
    45.     public List selectBooksLike1(String bname){
    46.         return bookMapper.selectBooksLike1(bname);
    47.     }
    48.     public List selectBooksLike2(String bname){
    49.         return bookMapper.selectBooksLike2(bname);
    50.     }
    51.     public List selectBooksLike3(String bname){
    52.         return bookMapper.selectBooksLike3(bname);
    53.     }
    54.  
    55.  
    56. }

    测试类 测试：

    1. package com.cdl.biz.impl;
    2.  
    3. import com.cdl.mapper.BookMapper;
    4. import com.cdl.util.SessionUtil;
    5. import org.apache.ibatis.session.SqlSession;
    6. import org.junit.After;
    7. import org.junit.Before;
    8. import org.junit.Test;
    9.  
    10. import java.util.Arrays;
    11.  
    12. /**
    13.  * @author cdl
    14.  * @site www.cdl.com
    15.  * @create 2022-08-10 22:55
    16.  */
    17. public class BookBizImplTest {
    18.  
    19.     private BookBizImpl bookBiz;
    20.     SqlSession sqlSession;
    21.  
    22.     @Before
    23.     public void setUp() throws Exception {
    24.         System.out.println("初始换方法。。。");
    25.       BookBizImpl  bookBiz = new BookBizImpl();
    26.       //工具类中获取session对象
    27.       sqlSession = SessionUtil.openSession();
    28.      //从session对象中获取mapper对象
    29.       BookMapper mapper = sqlSession.getMapper(BookMapper.class);
    30.         bookBiz.setBookMapper(mapper);
    31.         this.bookBiz = bookBiz;
    32.     }
    33.  
    34.     @After
    35.     public void tearDown() throws Exception {
    36.         System.out.println("方法测试结束。。");
    37.     }
    38.  
    39.     @Test
    40.     public void deleteByPrimaryKey() {
    41.  
    42.     }
    43.  
    44.     @Test
    45.     public void selectByPrimaryKey() {
    46.  
    47.         System.out.println("测试的业务方法。。。");
    48.         //System.out.println(bookBiz.getBookMapper());
    49.         System.out.println(bookBiz.selectByPrimaryKey(44));
    50.     }
    51.  
    52.     @Test
    53.     public void test3() {
    54.         int[] ints = {1,2,3,4,5};
    55.         //将数组变成字符串
    56.         StringBuffer sb = new StringBuffer();
    57.         for (int i:ints){
    58.                sb.append(",").append(i);
    59.         }
    60.          String s = sb.toString();
    61.         System.out.println(s.substring(1));
    62.     }
    63.  
    64.     @Test
    65.     public void selectByIn(){
    66.         bookBiz.selectByIn(Arrays.asList(new Integer[]{31,32,33,34})).forEach(System.out::println);
    67.     }
    68.  
    69.  
    70.     @Test
    71.     public void selectBooksLike1() {
    72.        //bookBiz.selectBooksLike1(selectBooksLike1:"%圣墟%").forEach(System.out::println);
    73.         bookBiz.selectBooksLike1("%圣墟%").forEach(System.out::println);
    74.     }
    75.  
    76.     @Test
    77.     public void selectBooksLike2() {
    78.         bookBiz.selectBooksLike2("%圣墟%").forEach(System.out::println);
    79.     }
    80.  
    81.     @Test
    82.     public void selectBooksLike3() {
    83.         bookBiz.selectBooksLike3("圣墟").forEach(System.out::println);
    84.     }
    85. }

     结果都一样：

    注意

    MyBatis中#和$的区别
    1. #将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。
       如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by '111', 
           如果传入的值是id，则解析成的sql为order by "id".

    2. $将传入的数据直接显示生成在sql中。
       如：order by $user_id$，如果传入的值是111,那么解析成sql时的值为order by user_id,
           如果传入的值是id，则解析成的sql为order by id.
    3. #方式能够很大程度防止sql注入。
    4. $方式无法防止Sql注入。
    5. $方式一般用于传入数据库对象，例如传入表名. 
    6. 一般能用#的就别用$. 

    四、结果集的处理

    BookMapper.xml

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

     BookVo 

    1. package com.cdl.model;
    2.  
    3. import java.util.List;
    4.  
    5. /**
    6.  * @author cdl
    7.  * @site www.cdl.com
    8.  * @create 2022-08-12 8:48
    9.  */
    10. public class BookVo extends  Book{
    11.  
    12.     private List bookIds;
    13.  
    14.     public List getBookIds() {
    15.         return bookIds;
    16.     }
    17.  
    18.     public void setBookIds(List bookIds) {
    19.         this.bookIds = bookIds;
    20.     }
    21. }

     interface BookMapper 

    1. package com.cdl.mapper;
    2.  
    3. import com.cdl.model.Book;
    4. import com.cdl.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.     //如果说参数是非实体类  那么记得加上注解 @param,bookIds是对应collection="bookIds"
    25.     List selectByIn(@Param("bookIds") List bookIds);
    26.  
    27.  
    28.     List selectBooksLike1(@Param("bname") String bname);
    29.     List selectBooksLike2(@Param("bname") String bname);
    30.     List selectBooksLike3(@Param("bname") String bname);
    31.  
    32.     //list1和list2的结论是：对于单表查询而言，可以用它Resultmap/ReaultType接收 但是多表必须用Resultmap
    33.     List list1();
    34.     List list2();
    35.     //如果要传入多个查询参数，必须以对象的方式进行传递
    36.     List list3(BookVo vo);
    37.  
    38.     //说明了返回一条数据还是多条数据 都应该用java.util.Map" 接收
    39.    // 如果是多条数据，那么返回的是list
    40.     List list4();
    41.     Map list5(Map map);
    42. }

     BookBizImpl

    1. package com.cdl.biz.impl;
    2.  
    3. import com.cdl.biz.BookBiz;
    4. import com.cdl.mapper.BookMapper;
    5. import com.cdl.model.Book;
    6. import com.cdl.model.BookVo;
    7.  
    8. import java.util.List;
    9. import java.util.Map;
    10.  
    11. /**
    12.  * @author cdl
    13.  * @site www.cdl.com
    14.  * @create 2022-08-10 22:49
    15.  */
    16. public class BookBizImpl implements BookBiz {
    17.  
    18.     private BookMapper bookMapper;
    19.  
    20.     //alt + insert 快速提供set/get/tostring/构造函数
    21.     //alt +enter 快速构建实现类 能够自动补全
    22.  
    23.     public BookMapper getBookMapper() {
    24.         return bookMapper;
    25.     }
    26.  
    27.     public void setBookMapper(BookMapper bookMapper) {
    28.         this.bookMapper = bookMapper;
    29.     }
    30.  
    31.     @Override
    32.     public int deleteByPrimaryKey(Integer bid) {
    33.         return bookMapper.deleteByPrimaryKey(bid);
    34.     }
    35.  
    36.     @Override
    37.     public Book selectByPrimaryKey(Integer bid) {
    38.         return bookMapper.selectByPrimaryKey(bid);
    39.     }
    40.  
    41.     @Override
    42.     public List selectByIn(List bookIds) {
    43.         return bookMapper.selectByIn(bookIds);
    44.     }
    45.  
    46.  
    47.     public List selectBooksLike1(String bname){
    48.         return bookMapper.selectBooksLike1(bname);
    49.     }
    50.     public List selectBooksLike2(String bname){
    51.         return bookMapper.selectBooksLike2(bname);
    52.     }
    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.  
    83. }
    //list1和list2的结论是：对于单表查询而言，可以用Resultmap/ReaultType接收 但是多表必须用Resultmap

    BookBizImplTest
    1.   @Test
    2.     public void list1() {
    3.         bookBiz.list1().forEach(System.out::println);
    4.     }
    5.  
    6.     @Test
    7.     public void list2() {
    8.         bookBiz.list2().forEach(System.out::println);
    9.     }

     

     

     

     

     list3

    //如果要传入多个查询参数，必须以对象的方式进行传递

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

     

     

     list4和list5

    //说明了返回一条数据还是多条数据 都应该用java.util.Map" 接收
// 如果是多条数据，那么返回的是list

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

    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使用

    添加pom.xml的文件

    1. <dependency>
    2.     <groupId>com.github.pagehelpergroupId>
    3.     <artifactId>pagehelperartifactId>
    4.     <version>5.1.2version>
    5. dependency>

     Mybatis.cfg.xml配置拦截器

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

    添加进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>

    BookMapper

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

    BookBiz

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

    分析：此时还不能分页 ，因为没有使用MySQL的limit分页 就要使用代码进行分页

    PageBean 

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

    BookBiz

      List listPager(Map map, PageBean pageBean);
    BookBizImpl
    1.   @Override
    2.     public List listPager(Map map, PageBean pageBean) {
    3.        //分页插件相关代码
    4.         if(pageBean != null && pageBean.isPagination()){
    5.             PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
    6.         }
    7.  
    8.        List  maps = bookMapper.listPager(map);
    9.         if(pageBean != null && pageBean.isPagination()){
    10.             //处理结果的前提 是需要分页
    11.             PageInfo info = new PageInfo(maps);
    12.             pageBean.setTotal(info.getTotal()+"");
    13.         }
    14.  
    15.         return maps;
    16.     }
    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.         bookBiz.listPager(map,pageBean).forEach(System.out::println);
    12.     }
    13. }

    45-65

     六、特殊字符处理

    >(>)  

        <(<) 

        &(&)

     空格( )

     

    BookMapper.xml

    1. <select id="list6" resultType="com.cdl.model.Book" parameterType="com.cdl.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.cdl.model.Book" parameterType="com.cdl.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>
    BookMapper
    1.  /**
    2.      * 处理特殊字符
    3.      * @param bookVo
    4.      * @return
    5.      */
    6.     List list6(BookVo bookVo);
    7.     List list7(BookVo bookVo);

     BookVo 

    1. package com.cdl.model;
    2.  
    3. import java.util.List;
    4.  
    5. /**
    6.  * @author cdl
    7.  * @site www.cdl.com
    8.  * @create 2022-08-12 8:48
    9.  */
    10. public class BookVo extends  Book{
    11.  
    12.     private List bookIds;
    13.     private  int min;
    14.     private int max;
    15.  
    16.     public int getMin() {
    17.         return min;
    18.     }
    19.  
    20.     public void setMin(int min) {
    21.         this.min = min;
    22.     }
    23.  
    24.     public int getMax() {
    25.         return max;
    26.     }
    27.  
    28.     public void setMax(int max) {
    29.         this.max = max;
    30.     }
    31.  
    32.     public List getBookIds() {
    33.         return bookIds;
    34.     }
    35.  
    36.     public void setBookIds(List bookIds) {
    37.         this.bookIds = bookIds;
    38.     }
    39. }

    BookBiz

    1.  /**
    2.      * 特殊字符处理
    3.      * @param bookVo
    4.      * @return
    5.      */
    6.     List list6(BookVo bookVo);
    7.     List list7(BookVo bookVo);
    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.     }
    1.  @Test
    2.     public void list6() {
    3.         BookVo bookVo = new BookVo();
    4.         bookVo.setMax(45);
    5.         bookVo.setMax(35);
    6.         bookBiz.list6(bookVo).forEach(System.out::println);
    7.     }
    8.  
    9.     @Test
    10.     public void list7() {
    11.         BookVo bookVo = new BookVo();
    12.         bookVo.setMax(45);
    13.         bookVo.setMax(35);
    14.         bookBiz.list6(bookVo).forEach(System.out::println);
    15.     }
    16. }

     

  • 相关阅读:
    在nodejs中使用typescript
    山东省技能兴鲁网络安全大赛 web方向
    【BP回归预测】改进的鲸鱼算法优化BP神经网络回归预测（多输入单输出）【含Matlab源码 2184期】
    服务器数据恢复- Ext4文件系统分区挂载报错的数据恢复案例
    514. 自由之路
    Java并发编程 | 从进程、线程到并发问题实例解决
    Auracast 广播音频知识点
    期货开户公司的选择和作用
    美国FBA海运详解：美国FBA海运费用价格有哪些
    用ChatGPT编写一个词卡显示网页
  • 原文地址：https://blog.csdn.net/weixin_62735525/article/details/126290653