mybatis（2）之动态sql和分页

1、mybatis动态sql

If、trim、

	<trim(去空格) prefix="(" suffix=")" suffixOverrides="," >
	      <if test="bid != null" > （这里不为空就看的到bid，为空执行sql就不显示，这就是动态sql）
	        bid,
	      </if>

foreach

	List<Book> selectBooksIn(List bookIds);

<select id="selectBooksIn" resultType="com.javaxl.model.Book" parameterType="java.util.List">
        select *from t_mvc_book where bid in
        <foreach collection="bookIds" item="bid" open="(" close="）" separator=",">
            #{bid}
        </foreach>
  </select>

最终成功

也可能遇到报错如下如图：

解决方案：

2、模糊查询

第一步：

在BookMapper中添加三个查询

    List<Book> selectBooksLike1(@Param("bname") String bname);
    List<Book> selectBooksLike2(@Param("bname") String banem);
    List<Book> selectBooksLike3(@Param("banem") String banem);

第二步：在BookMapper.xml中配置三个

		<select id="selectBooksLike1" resultType="com.zking.model.Book" parameterType="java.lang.String">
	        select * from t_mvc_book where bname like #{bname}
	  </select>
	  <select id="selectBooksLike2" resultType="com.zking.model.Book" parameterType="java.lang.String">
	        select * from t_mvc_book where bname like '${bname}'
	  </select>
	  <select id="selectBooksLike3" resultType="com.zking.model.Book" parameterType="java.lang.String">
	            select * from t_mvc_book where bname like concat(concat('%',#{banem}),'%')
	  </select>

第三步

将BookMapper中添加三个查询复制到 service的下的BookService接口类中
在到其同目录impl下的BookServiceImpl中添加方法。

第四步

在util目录下在建一个StringUtils

StringUtils

	package com.zking.util;
	
	/**
	 * @author 邦无敌
	 * @site https://blog.csdn.net/YoonBongChi
	 * @compsny 邦无敌公司
	 * @create 2020-10-17-19:31
	 */
	public class StringUtils {
	    public static String toLikeStr(String str){
	        return  "%"+str+"%";
	    }
	}

第五步

再测试类BookServiceTest中测试

三种测试方式。

@Test
public void selectBooksLike() {
String bname=“录”;
// List books = this.bookService.selectBooksLike1(StringUtils.toLikeStr(bname));
// List books = this.bookService.selectBooksLike2(StringUtils.toLikeStr(bname));
List books = this.bookService.selectBooksLike3(bname);

    for (Book book : books) {
        System.out.println(book);
    }
}

注意：#与$的区别

$会引起sqlde攻击。$的bname的值会多传过来一个引号和他本身抵消，所以
select * from t_mvc_book where bname like ‘${bname}’ (这里加了引号)。

3、查询返回结果集的处理

ov的介绍moyixie

	mybatis,hiternate都是orm框架，表所存在的列段再实体类model都有映射
	实际开发中，会因为某一些需求改变model，破坏model的封装性，此时为了
	保证model的封装性，就可以用vo来完成指定的需求。

第一步,老规矩再BookMapper中添加方法

	List<Book>  List1();
    List<Book>  List2();	
	List<Book> list3(BookVo bookVo);
    List<Map> list4(Map map);
    Map  list5(Map map);

Bookmapper.xml中配置

			<select id="list1" resultMap="BaseResultMap">
		        select * from t_mvc_book
		  </select>
		
		  <select id="list2" resultType="com.zking.model.Book">
		            select * from t_mvc_book
		  </select>
		
		  <select id="list3" resultType="com.zking.model.Book" parameterType="com.zking.model.ov.BookVo">
		    select * from t_mvc_book where bid in
		    <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
		      #{bid}
		    </foreach>
		  </select>
		
		  <select id="list4" resultType="java.util.Map" parameterType="java.util.Map">
		    select * from t_mvc_book where bid in
		    <foreach collection="bookIds" open="(" close=")" separator="," item="bid">
		      #{bid}
		    </foreach>
		  </select>
		
		  <select id="list5" resultType="java.util.Map" parameterType="java.util.Map">
		    select * from t_mvc_book where bid = #{bid}
		  </select>

BookService中配置

添加方法和前差不多

测试

BookServiceTest

 @Test
    public void list() {
     //   List<Book> list = this.bookService.list1();
     //   List<Book> list1 = this.bookService.list2();
     List list=new ArrayList();
        list.add(1);
        list.add(6);

     /*  BookVo  bookVo = new BookVo();
    bookVo.setBookIds(list);
    List<Book> list1 = this.bookService.list3(bookVo);
    for (Book book : list1) {
        System.out.println(book);
    }*/

   /* Map  map=new HashMap();
    map.put("bookIds",list);
    List<Map> maps = this.bookService.list4(map);
    for (Map map1 : maps) {
        System.out.println(map1);
    }*/

   Map map=new HashMap();
   map.put("bid",1);
    Map map1 = this.bookService.list5(map);
    System.out.println(map1);

}

4、分页查询

第一步

Pom依赖

	<dependency>
	    <groupId>com.github.pagehelper</groupId>
	    <artifactId>pagehelper</artifactId>
	    <version>5.1.2</version>
	</dependency>

Mybatis.cfg.xml配置拦截器

	<plugins>
	    <!-- 配置分页插件PageHelper, 4.0.0以后的版本支持自动识别使用的数据库 -->
	    <plugin interceptor="com.github.pagehelper.PageInterceptor">
	    </plugin>
	</plugins>

拦截器必须放在environments之前，如图：

第二步再BookMapper中写一个

分页，再配置。前面做了很多例子我就直写了

BookMapper接口类

List<Map> ListPage(Map map);

BookMapp.xml

 <select id="ListPager" resultType="java.util.Map" parameterType="java.util.Map">
    select * from t_mvc_book where bname like #{bname}
  </select>

第二步

再uitl下写一个PageBean
PageBean类

package com.zking.util;

import java.io.Serializable;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

public class PageBean implements Serializable {

	private static final long serialVersionUID = 2422581023658455731L;

	//页码
	private int page=1;
	//每页显示记录数
	private int rows=2;
	//总记录数
	private int total=0;
	//是否分页
	private boolean isPagination=true;
	//上一次的请求路径
	private String url;
	//获取所有的请求参数
	private Map<String,String[]> map;
	
	public PageBean() {
		super();
	}
	
	//设置请求参数
	public void setRequest(HttpServletRequest req) {
		String page=req.getParameter("page");
		String rows=req.getParameter("rows");
		String pagination=req.getParameter("pagination");
		this.setPage(page);
		this.setRows(rows);
		this.setPagination(pagination);
		this.url=req.getContextPath()+req.getServletPath();
		this.map=req.getParameterMap();
	}
	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public Map<String, String[]> getMap() {
		return map;
	}

	public void setMap(Map<String, String[]> map) {
		this.map = map;
	}

	public int getPage() {
		return page;
	}

	public void setPage(int page) {
		this.page = page;
	}
	
	public void setPage(String page) {
		if(null!=page&&!"".equals(page.trim()))
			this.page = Integer.parseInt(page);
	}

	public int getRows() {
		return rows;
	}

	public void setRows(int rows) {
		this.rows = rows;
	}
	
	public void setRows(String rows) {
		if(null!=rows&&!"".equals(rows.trim()))
			this.rows = Integer.parseInt(rows);
	}

	public int getTotal() {
		return total;
	}

	public void setTotal(int total) {
		this.total = total;
	}
	
	public void setTotal(String total) {
		this.total = Integer.parseInt(total);
	}

	public boolean isPagination() {
		return isPagination;
	}
	
	public void setPagination(boolean isPagination) {
		this.isPagination = isPagination;
	}
	
	public void setPagination(String isPagination) {
		if(null!=isPagination&&!"".equals(isPagination.trim()))
			this.isPagination = Boolean.parseBoolean(isPagination);
	}
	
	/**
	 * 获取分页起始标记位置
	 * @return
	 */
	public int getStartIndex() {
		//(当前页码-1)*显示记录数
		return (this.getPage()-1)*this.rows;
	}
	
	/**
	 * 末页
	 * @return
	 */
	public int getMaxPage() {
		int totalpage=this.total/this.rows;
		if(this.total%this.rows!=0)
			totalpage++;
		return totalpage;
	}
	
	/**
	 * 下一页
	 * @return
	 */
	public int getNextPage() {
		int nextPage=this.page+1;
		if(this.page>=this.getMaxPage())
			nextPage=this.getMaxPage();
		return nextPage;
	}
	
	/**
	 * 上一页
	 * @return
	 */
	public int getPreivousPage() {
		int previousPage=this.page-1;
		if(previousPage<1)
			previousPage=1;
		return previousPage;
	}

	@Override
	public String toString() {
		return "PageBean [page=" + page + ", rows=" + rows + ", total=" + total + ", isPagination=" + isPagination
				+ "]";
	}
}

BookService接口类

 List<Map>  ListPager(Map map, PageBean pageBean);

BookServicelmpl

		 @Override
		    public List<Map> ListPager(Map map, PageBean pageBean) {
		      if(pageBean !=null&&pageBean.isPagination()){
		          PageHelper.startPage(pageBean.getPage(),pageBean.getRows());
		      }
		        List<Map> list = bookMapper.ListPager(map);
		      if(pageBean!=null&&pageBean.isPagination()){
		          PageInfo pageInfo=new PageInfo(list);
		          System.out.println("总记录数："+pageInfo.getTotal());
		          System.out.println("当前页："+pageInfo.getPageNum());
		          System.out.println("页大小："+pageInfo.getPageSize());
		          pageBean.setTotal(pageInfo.getTotal()+"");
		          System.out.println("总页数："+pageBean.getMaxPage());
		      }
		        return list;
		    }

测试

BookServiceTest

  @Test
   public void listPage() {
        Map map=new HashMap();
        map.put("bname","%录%");
        // pageBean.setPagination(false);不想分页，查询全部。
        //pageBean.setPage(2);查询第几页
        PageBean  pageBean=new PageBean();
        List<Map> maps = this.bookService.ListPager(map, pageBean);
        for (Map map1 : maps) {
            System.out.println(map1);
        }
    }

效果图：

5、特殊字符处理

字符报错

	>(>)   
	    <(&lt;)  
	    &(&amp;) 
	 空格(&nbsp;)
	 <![CDATA[ <= ]]> 包在这个字符里无论什么字符都不会报错

相关代码配置

	<select id="list6" resultType="com.javaxl.model.Book" parameterType="com.javaxl.model.BookVo">
	  select * from t_mvc_book
	  <where>
	    <if test="null != min and min != ''">
	      <![CDATA[  and #{min} < price ]]>
	    </if>
	    <if test="null != max and max != ''">
	      <![CDATA[ and #{max} > price ]]>
	    </if>
	  </where>
	</select>

	  <select id="list7" resultType="com.javaxl.model.Book" parameterType="com.javaxl.model.BookVo">
	    select * from t_mvc_book
	    <where>
	      <if test="null != min and min != ''">
	         and #{min} &lt; price
	      </if>
	      <if test="null != max and max != ''">
	         and #{max} &gt; price
	      </if>
	    </where>
	  </select>

	
	/**
	 * 处理特殊字符
	 * @param bookVo
	 * @return
	 */
	List<Book> list6(BookVo bookVo);
	

		/**
		 * 处理特殊字符
		 * @param bookVo
		 * @return
		 */
		List<Book> list7(BookVo bookVo);

总结：积极向上，走向人生巅峰。