mybatis动态sql和分页
## 咱先直接上代码
BookMapper.java
1 package com.yuan.mapper; 2 3 import com.yuan.model.Book; 4 import com.yuan.model.vo.BookVo; 5 import com.yuan.util.PageBean; 6 import org.apache.ibatis.annotations.Param; 7 8 import java.util.List; 9 import java.util.Map; 10 11 public interface BookMapper { 12 int deleteByPrimaryKey(Integer bid); 13 14 int insert(Book record); 15 16 int insertSelective(Book record); 17 18 Book selectByPrimaryKey(Integer bid); 19 20 int updateByPrimaryKeySelective(Book record); 21 22 int updateByPrimaryKey(Book record); 23 24 25 /** 26 * 如果形参要在mapper.xml中使用就需要加上@param注解 27 * 28 * @param bookIds 29 * @return 30 */ 31 List<Book> selectBooksIn(@Param("bookIds") List bookIds); 32 33 34 /** 35 * mybatis对模糊查询一共有三种方式： 36 * 1、#{} 37 * 2、${} 38 * 3、concat 39 * @param bname 40 * @return 41 */ 42 List<Book> selectBookLike1(@Param("bname") String bname); 43 44 List<Book> selectBookLike2(@Param("bname") String bname); 45 46 List<Book> selectBookLike3(@Param("bname") String bname); 47 48 49 /** 50 * mybatis结果集处理的五种情况 51 * @return 52 */ 53 List<Book> list1(); 54 List<Book> list2(); 55 List<Book> list3(BookVo bookVo); 56 List<Map> list4(Map map); 57 Map list5(Map map); 58 59 /** 60 * 动态分页 61 */ 62 List<Map> listPager(Map map); 63 64 /** 65 * 特殊字符处理 66 */ 67 List<Book> list6(BookVo bookVo); 68 69 70 }
BookMapper.xml
1 <?xml version="1.0" encoding="UTF-8" ?> 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > 3 <mapper namespace="com.yuan.mapper.BookMapper"> 4 <resultMap id="BaseResultMap" type="com.yuan.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" resultMap="BaseResultMap" 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 <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer"> 22 delete from t_mvc_book 23 where bid = #{bid,jdbcType=INTEGER} 24 </delete> 25 <insert id="insert" parameterType="com.yuan.model.Book"> 26 insert into t_mvc_book (bid, bname, price 27 ) 28 values (#{bid,jdbcType=INTEGER}, #{bname,jdbcType=VARCHAR}, #{price,jdbcType=REAL} 29 ) 30 </insert> 31 <insert id="insertSelective" parameterType="com.yuan.model.Book"> 32 insert into t_mvc_book 33 <trim prefix="(" suffix=")" suffixOverrides=","> 34 <if test="bid != null"> 35 bid, 36 </if> 37 <if test="bname != null"> 38 bname, 39 </if> 40 <if test="price != null"> 41 price, 42 </if> 43 </trim> 44 <trim prefix="values (" suffix=")" suffixOverrides=","> 45 <if test="bid != null"> 46 #{bid,jdbcType=INTEGER}, 47 </if> 48 <if test="bname != null"> 49 #{bname,jdbcType=VARCHAR}, 50 </if> 51 <if test="price != null"> 52 #{price,jdbcType=REAL}, 53 </if> 54 </trim> 55 </insert> 56 <update id="updateByPrimaryKeySelective" parameterType="com.yuan.model.Book"> 57 update t_mvc_book 58 <set> 59 <if test="bname != null"> 60 bname = #{bname,jdbcType=VARCHAR}, 61 </if> 62 <if test="price != null"> 63 price = #{price,jdbcType=REAL}, 64 </if> 65 </set> 66 where bid = #{bid,jdbcType=INTEGER} 67 </update> 68 <update id="updateByPrimaryKey" parameterType="com.yuan.model.Book"> 69 update t_mvc_book 70 set bname = #{bname,jdbcType=VARCHAR}, 71 price = #{price,jdbcType=REAL} 72 where bid = #{bid,jdbcType=INTEGER} 73 </update> 74 75 <!--foreach标签的使用--> 76 <select id="selectBooksIn" resultType="com.yuan.model.Book"> 77 select * from t_mvc_book where bid in 78 <foreach collection="bookIds" item="bid" open="(" close=")" separator=","> 79 #{bid} 80 </foreach> 81 </select> 82 <!--模糊查询--> 83 <select id="selectBookLike1" resultType="com.yuan.model.Book" parameterType="java.lang.String"> 84 select * from t_mvc_book where bname like #{bname} 85 </select> 86 <select id="selectBookLike2" resultType="com.yuan.model.Book" parameterType="java.lang.String"> 87 select * from t_mvc_book where bname like '${bname}' 88 </select> 89 <select id="selectBookLike3" resultType="com.yuan.model.Book" parameterType="java.lang.String"> 90 select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%') 91 </select> 92 <!--结果集处理的五种方式--> 93 <select id="list1" resultMap="BaseResultMap"> 94 select * from t_mvc_book 95 </select> 96 <select id="list2" resultType="com.yuan.model.Book"> 97 select * from t_mvc_book 98 </select> 99 <select id="list3" resultType="com.yuan.model.Book" parameterType="com.yuan.model.vo.BookVo"> 100 select * from t_mvc_book where bid in 101 <foreach collection="bookIds" item="bid" open="(" close=")" separator=","> 102 #{bid} 103 </foreach> 104 </select> 105 <select id="list4" resultType="java.util.Map" parameterType="java.util.Map"> 106 select * from t_mvc_book where bid in 107 <foreach collection="bookIds" item="bid" open="(" close=")" separator=","> 108 #{bid} 109 </foreach> 110 </select> 111 <select id="list5" resultType="java.util.Map"> 112 select * from t_mvc_book where bid = #{bid} 113 </select> 114 115 <!--动态分页--> 116 <select id="listPager" resultType="java.util.Map"> 117 select * from t_mvc_book where bname like #{bname} 118 </select> 119 120 <!--特殊字符处理--> 121 <select id="list6" resultType="com.yuan.model.Book" parameterType="com.yuan.model.vo.BookVo"> 122 select * from t_mvc_book where price > #{min} and price < #{max} 123 </select> 124 125 </mapper>
BookService.java
1 package com.yuan.service; 2 3 import com.yuan.model.Book; 4 import com.yuan.model.vo.BookVo; 5 import com.yuan.util.PageBean; 6 import org.apache.ibatis.annotations.Param; 7 8 import java.util.List; 9 import java.util.Map; 10 11 public interface BookService { 12 13 int deleteByPrimaryKey(Integer bid); 14 15 int insert(Book record); 16 17 int insertSelective(Book record); 18 19 Book selectByPrimaryKey(Integer bid); 20 21 int updateByPrimaryKeySelective(Book record); 22 23 int updateByPrimaryKey(Book record); 24 25 /** 26 * foreach使用 27 * @param bookIds 28 * @return 29 */ 30 List<Book> selectBooksIn(List bookIds); 31 32 /** 33 * 模糊查询方式 34 * @param bname 35 * @return 36 */ 37 List<Book> selectBookLike1(@Param("bname") String bname); 38 39 List<Book> selectBookLike2(@Param("bname") String bname); 40 41 List<Book> selectBookLike3(@Param("bname") String bname); 42 43 /** 44 * mybatis结果集处理的五种情况 45 * 46 * @return 47 */ 48 List<Book> list1(); 49 50 List<Book> list2(); 51 52 List<Book> list3(BookVo bookVo); 53 54 List<Map> list4(Map map); 55 56 Map list5(Map map); 57 /** 58 * 动态分页 59 */ 60 List<Map> listPager(Map map, PageBean bean); 61 62 /** 63 * 特殊字符处理 64 */ 65 List<Book> list6(BookVo bookVo); 66 67 68 }
BookService实现类--BookServiceImpl.java
1 package com.yuan.service.impl; 2 3 import com.github.pagehelper.PageHelper; 4 import com.github.pagehelper.PageInfo; 5 import com.yuan.mapper.BookMapper; 6 import com.yuan.model.Book; 7 import com.yuan.model.vo.BookVo; 8 import com.yuan.service.BookService; 9 import com.yuan.util.PageBean; 10 11 import java.util.List; 12 import java.util.Map; 13 14 public class BookServiceImpl implements BookService { 15 private BookMapper bookMapper; 16 17 public BookMapper getBookMapper() { 18 return bookMapper; 19 } 20 21 public void setBookMapper(BookMapper bookMapper) { 22 this.bookMapper = bookMapper; 23 } 24 25 @Override 26 public int deleteByPrimaryKey(Integer bid) { 27 return bookMapper.deleteByPrimaryKey(bid); 28 } 29 30 @Override 31 public int insert(Book record) { 32 return bookMapper.insert(record); 33 } 34 35 @Override 36 public int insertSelective(Book record) { 37 return bookMapper.insertSelective(record); 38 } 39 40 @Override 41 public Book selectByPrimaryKey(Integer bid) { 42 return bookMapper.selectByPrimaryKey(bid); 43 } 44 45 @Override 46 public int updateByPrimaryKeySelective(Book record) { 47 return bookMapper.updateByPrimaryKeySelective(record); 48 } 49 50 @Override 51 public int updateByPrimaryKey(Book record) { 52 return bookMapper.updateByPrimaryKey(record); 53 } 54 55 /** 56 * foreach的使用 57 * @param bookIds 58 * @return 59 */ 60 @Override 61 public List<Book> selectBooksIn(List bookIds) { 62 return bookMapper.selectBooksIn(bookIds); 63 } 64 65 /** 66 * 不同的模糊查询的方式 67 * @param bname 68 * @return 69 */ 70 @Override 71 public List<Book> selectBookLike1(String bname) { 72 return bookMapper.selectBookLike1(bname); 73 } 74 75 @Override 76 public List<Book> selectBookLike2(String bname) { 77 return bookMapper.selectBookLike2(bname); 78 } 79 80 @Override 81 public List<Book> selectBookLike3(String bname) { 82 return bookMapper.selectBookLike3(bname); 83 } 84 85 /** 86 * mybatis结果集处理五种方式 87 * @return 88 */ 89 @Override 90 public List<Book> list1() { 91 return bookMapper.list1(); 92 } 93 94 @Override 95 public List<Book> list2() { 96 return bookMapper.list2(); 97 } 98 99 @Override 100 public List<Book> list3(BookVo bookVo) { 101 return bookMapper.list3(bookVo); 102 } 103 104 @Override 105 public List<Map> list4(Map map) { 106 return bookMapper.list4(map); 107 } 108 109 @Override 110 public Map list5(Map map) { 111 return bookMapper.list5(map); 112 } 113 114 115 @Override 116 /** 117 * 动态分页 118 */ 119 public List<Map> listPager(Map map, PageBean bean) { 120 if(bean!=null && bean.isPagination()){ 121 PageHelper.startPage(bean.getPage(),bean.getRows()); 122 } 123 124 List<Map> list = bookMapper.listPager(map); 125 126 if(bean!=null && bean.isPagination()){ 127 PageInfo pageInfo = new PageInfo(list); 128 System.out.println("总记录数：" + pageInfo.getTotal()); 129 System.out.println("当前页：" + pageInfo.getPageNum()); 130 System.out.println("页大小：" + pageInfo.getPageSize()); 131 bean.setTotal(pageInfo.getTotal()+""); 132 System.out.println("总页数：" + bean.getMaxPage()); 133 } 134 return list; 135 } 136 137 /** 138 * 特殊字符处理 139 * @param bookVo 140 * @return 141 */ 142 @Override 143 public List<Book> list6(BookVo bookVo) { 144 return bookMapper.list6(bookVo); 145 } 146 }
BookVo.java
1 package com.yuan.model.vo; 2 3 import com.yuan.model.Book; 4 5 import java.util.List; 6 7 8 /** 9 * vo介绍 10 * mmybatis、hibernate都是orm框架，表所存在的列段在实体类module中都有映射 11 * 实际开发中，会因为某些需求改变module，破坏module封装性 12 * 此时为了保证module的封装性，就可以使用vo类来完成指定的需求 13 */ 14 public class BookVo extends Book { 15 private float min; 16 private float max; 17 private List<Integer> bookIds; 18 19 public float getMin() { 20 return min; 21 } 22 23 public void setMin(float min) { 24 this.min = min; 25 } 26 27 public float getMax() { 28 return max; 29 } 30 31 public void setMax(float max) { 32 this.max = max; 33 } 34 35 public List<Integer> getBookIds() { 36 return bookIds; 37 } 38 39 public void setBookIds(List<Integer> bookIds) { 40 this.bookIds = bookIds; 41 } 42 }
导入分页时需要用到的pom依赖
pom.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 3 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 4 xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> 5 <modelVersion>4.0.0</modelVersion> 6 7 <groupId>com.yuan</groupId> 8 <artifactId>mybatis01</artifactId> 9 <version>1.0-SNAPSHOT</version> 10 <packaging>war</packaging> 11 12 <name>mybatis01 Maven Webapp</name> 13 <!-- FIXME change it to the project's website --> 14 <url>http://www.example.com</url> 15 16 <properties> 17 <maven.compiler.source>1.8</maven.compiler.source> 18 <maven.compiler.target>1.8</maven.compiler.target> 19 </properties> 20 21 22 <dependencies> 23 <!-- ********************** junit单元测试依赖 ********************** --> 24 <dependency> 25 <groupId>junit</groupId> 26 <artifactId>junit</artifactId> 27 <version>4.12</version> 28 <scope>test</scope> 29 </dependency> 30 31 <!-- ********************** Java Servlet API ********************** --> 32 <dependency> 33 <groupId>javax.servlet</groupId> 34 <artifactId>javax.servlet-api</artifactId> 35 <version>4.0.0</version> 36 <scope>provided</scope> 37 </dependency> 38 39 <!-- ********************** Mybatis依赖 ********************** --> 40 <dependency> 41 <groupId>org.mybatis</groupId> 42 <artifactId>mybatis</artifactId> 43 <version>3.4.5</version> 44 </dependency> 45 46 <!-- ********************** Mysql JDBC驱动 ********************** --> 47 <dependency> 48 <groupId>mysql</groupId> 49 <artifactId>mysql-connector-java</artifactId> 50 <version>5.1.44</version> 51 </dependency> 52 53 <!-- ********************** 日志配置 ********************** --> 54 <!--记得修改mybatis.cfg.xml添加如下内容--> 55 <!--<setting name="logImpl" value="LOG4J2"/>--> 56 <!--核心log4j2jar包--> 57 <dependency> 58 <groupId>org.apache.logging.log4j</groupId> 59 <artifactId>log4j-core</artifactId> 60 <version>2.9.1</version> 61 </dependency> 62 <dependency> 63 <groupId>org.apache.logging.log4j</groupId> 64 <artifactId>log4j-api</artifactId> 65 <version>2.9.1</version> 66 </dependency> 67 <!--web工程需要包含log4j-web，非web工程不需要--> 68 <dependency> 69 <groupId>org.apache.logging.log4j</groupId> 70 <artifactId>log4j-web</artifactId> 71 <version>2.9.1</version> 72 </dependency> 73 <dependency> 74 <groupId>com.github.pagehelper</groupId> 75 <artifactId>pagehelper</artifactId> 76 <version>5.1.2</version> 77 </dependency> 78 </dependencies> 79 80 <build> 81 <finalName>mybatis01</finalName> 82 <resources> 83 <!--解决mybatis-generator-maven-plugin运行时没有将XxxMapper.xml文件放入target文件夹的问题--> 84 <resource> 85 <directory>src/main/java</directory> 86 <includes> 87 <include>**/*.xml</include> 88 </includes> 89 </resource> 90 <!--解决mybatis-generator-maven-plugin运行时没有将jdbc.properites文件放入target文件夹的问题--> 91 <resource> 92 <directory>src/main/resources</directory> 93 <includes> 94 <include>jdbc.properties</include> 95 <include>*.xml</include> 96 </includes> 97 </resource> 98 </resources> 99 100 <plugins> 101 <plugin> 102 <groupId>org.mybatis.generator</groupId> 103 <artifactId>mybatis-generator-maven-plugin</artifactId> 104 <version>1.3.2</version> 105 <dependencies> 106 <!--使用Mybatis-generator插件不能使用太高版本的mysql驱动 --> 107 <dependency> 108 <groupId>mysql</groupId> 109 <artifactId>mysql-connector-java</artifactId> 110 <version>5.1.44</version> 111 </dependency> 112 </dependencies> 113 <configuration> 114 <overwrite>true</overwrite> 115 </configuration> 116 </plugin> 117 </plugins> 118 </build> 119 </project>
mybaytis.cfg.xml
1 <?xml version="1.0" encoding="UTF-8"?> 2 <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> 3 <configuration> 4 <!-- 引入外部配置文件 --> 5 <properties resource="jdbc.properties"/> 6 7 <settings> 8 <setting name="logImpl" value="LOG4J2"/> 9 </settings> 10 11 <!-- 别名 --> 12 <typeAliases> 13 <!--<typeAlias type="com.javaxl.model.Book" alias="Book"/>--> 14 </typeAliases> 15 16 <plugins> 17 <plugin interceptor="com.github.pagehelper.PageInterceptor"></plugin> 18 </plugins> 19 20 <!-- 配置mybatis运行环境 --> 21 <environments default="development"> 22 <environment id="development"> 23 <!-- type="JDBC" 代表使用JDBC的提交和回滚来管理事务 --> 24 <transactionManager type="jdbc"/> 25 26 <!-- mybatis提供了3种数据源类型，分别是：POOLED,UNPOOLED,JNDI --> 27 <!-- POOLED 表示支持JDBC数据源连接池 --> 28 <!-- UNPOOLED 表示不支持数据源连接池 --> 29 <!-- JNDI 表示支持外部数据源连接池 --> 30 <dataSource type="POOLED"> 31 <property name="driver" 32 value="${jdbc.driver}"/> 33 <property name="url" 34 value="${jdbc.url}"/> 35 <property name="username" value="${jdbc.username}"/> 36 <property name="password" value="${jdbc.password}"/> 37 </dataSource> 38 </environment> 39 </environments> 40 41 42 <mappers> 43 <mapper resource="com/yuan/mapper/BookMapper.xml"/> 44 </mappers> 45 </configuration>
工具类PageBean.java
1 package com.yuan.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<String,String[]> 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<String, String[]> getMap() { 49 return map; 50 } 51 52 public void setMap(Map<String, String[]> 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 }
最后到了我们的测试类BookServiceTest.java
1 package com.yuan.service; 2 3 4 import com.yuan.mapper.BookMapper; 5 import com.yuan.model.Book; 6 import com.yuan.model.vo.BookVo; 7 import com.yuan.service.impl.BookServiceImpl; 8 import com.yuan.util.PageBean; 9 import com.yuan.util.SessionUtil; 10 import com.yuan.util.StringUtil; 11 import org.apache.ibatis.session.SqlSession; 12 import org.junit.After; 13 import org.junit.Before; 14 import org.junit.Test; 15 16 import java.util.ArrayList; 17 import java.util.HashMap; 18 import java.util.List; 19 import java.util.Map; 20 21 22 public class BookServiceTest{ 23 24 private BookService bookService; 25 private SqlSession sqlSession; 26 @Before 27 public void setUp(){ 28 BookServiceImpl bookService = new BookServiceImpl(); 29 sqlSession = SessionUtil.openSession(); 30 31 BookMapper mapper = sqlSession.getMapper(BookMapper.class); 32 bookService.setBookMapper(mapper); 33 this.bookService = bookService; 34 } 35 36 @Test 37 public void insert() { 38 Book book = new Book(); 39 book.setBid(1); 40 book.setBname("mybatis插入"); 41 book.setPrice(33f); 42 bookService.insert(book); 43 } 44 45 @Test 46 public void selectByPrimaryKey() { 47 Book book = this.bookService.selectByPrimaryKey(29); 48 // this.bookService.deleteByPrimaryKey(1); 49 System.out.println(book); 50 } 51 @After 52 public void tearDown()throws Exception{ 53 sqlSession.commit(); 54 sqlSession.close(); 55 } 56 57 /** 58 * foreach的使用 59 */ 60 @Test 61 public void selectBooksIn() { 62 List list= new ArrayList<>(); 63 list.add(1); 64 list.add(11); 65 list.add(22); 66 List<Book> books = this.bookService.selectBooksIn(list); 67 for (Book b : books) { 68 System.out.println(b); 69 } 70 71 } 72 73 /** 74 * 模糊查询 75 * #与$符号的区别 76 */ 77 @Test 78 public void selectBookLike() { 79 String bname="圣墟"; 80 // List<Book> books = this.bookService.selectBookLike1(StringUtil.toLikeStr(bname)); 81 List<Book> books = this.bookService.selectBookLike2(StringUtil.toLikeStr(bname)); 82 // List<Book> books = this.bookService.selectBookLike3(bname); 83 for (Book b : books) { 84 System.out.println(b); 85 } 86 87 } 88 89 /** 90 *结果集处理 91 */ 92 @Test 93 public void list(){ 94 // List<Book> books = this.bookService.list1(); 95 // List<Book> books = this.bookService.list2(); 96 List list= new ArrayList<>(); 97 list.add(1); 98 list.add(11); 99 list.add(24); 100 // BookVo bookVo = new BookVo(); 101 // bookVo.setBookIds(list); 102 // List<Book> books = this.bookService.list3(bookVo); 103 // for (Book b : books) { 104 // System.out.println(b); 105 // } 106 107 Map map = new HashMap(); 108 // map.put("bookIds",list); 109 // List<Map> maps = this.bookService.list4(map); 110 // for (Map m : maps) { 111 // System.out.println(m); 112 // } 113 map.put("bid",1); 114 Map map1 = this.bookService.list5(map); 115 System.out.println(map1); 116 117 } 118 119 /** 120 * 动态分页 121 */ 122 @Test 123 public void listPager(){ 124 Map map =new HashMap(); 125 map.put("bname","%圣墟%"); 126 PageBean pageBean = new PageBean(); 127 // pageBean.setPagination(false);//不分页 128 pageBean.setPage(2);//设置页码数 129 List<Map> list = this.bookService.listPager(map, pageBean); 130 for (Map m : list) { 131 System.out.println(m); 132 } 133 } 134 135 /** 136 * 特殊字符处理 137 */ 138 @Test 139 public void list6(){ 140 BookVo bookVo = new BookVo(); 141 bookVo.setMax(25f); 142 bookVo.setMin(15f); 143 List<Book> books = this.bookService.list6(bookVo); 144 for (Book b : books) { 145 System.out.println(b); 146 } 147 } 148 149 150 151 152 153 }
运行结果
mybatis动态sql
模糊查询
#{...}
${...}
Concat
注意：#{...}自带引号，${...}有sql注入的风险
resultMap：适合使用返回值是自定义实体类的情况
resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型
1 使用resultMap返回自定义类型集合
2 使用resultType返回List<T>
3 使用resultType返回单个对象
4 使用resultType返回List<Map>，适用于多表查询返回结果集
5 使用resultType返回Map<String,Object>，适用于多表查询返回单个结果集
分页查询
为什么要重写mybatis的分页？
Mybatis的分页功能很弱，它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果)，在大数据量的情况下这样的分页基本上是没有用的
使用分页插件步奏
1、导入pom依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.2</version>
</dependency>
2、Mybatis.cfg.xml配置拦截器
<plugins>
<!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
<plugin interceptor="com.github.pagehelper.PageInterceptor">
</plugin>
</plugins>
3、使用PageHelper进行分页
<select id="listPager" resultType="java.util.Map" parameterType="java.util.Map">
select * from t_mvc_book where bname like concat(concat('%',#{bname}),'%')
</select>
4、处理分页结果
分页
按页码分页
不分页
特殊字符处理
>(>)
<(<)
&(&)
空格( )
<![CDATA[ <= ]]>
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 <![CDATA[ and #{min} < price ]]> 6 </if> 7 <if test="null != max and max != ''"> 8 <![CDATA[ and #{max} > price ]]> 9 </if> 10 </where> 11 </select> 12 13 <select id="list7" resultType="com.javaxl.model.Book" parameterType="com.javaxl.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>
处理结果
