mybatis动态sql与分页
动态sql根据id查询模糊查询
分页查询导入pom依赖Mybatis.cfg.xml配置拦截器分页
特殊字符处理代码块测试PageBeanBookMapperBookMapper.xmlBookVoBookServicepom.xml
动态sql
在上篇博客的基础上添加mybatis动态sql，在BookMapper类中新增方法
根据id查询
鼠标选中方法名按Alt+Enter键进行自动生成实现
<select id
="selectBookIn" resultType
="com.solar.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
>
去service实现 BookServiceImpl中通过bookMapper调取 （添加实现方法） 测试
模糊查询
三种方式
#
{...}
$
{...}
Concat
注意：#
{...}自带引号，$
{...}有sql注入的风险
xml文件 再去BookService中添加
并且调用bookMapper实现 测试 首先需要用到一个工具类 StringUtil 这里所使用的到的工具类StringUtils为了模糊查询拼接%% 第三种模糊查询方式不需要通过这个拼接 Concat
package com
.solar
.util
;
public class StringUtils {
public static String
toLinkStr(String str
){
return "%"+str
+"%";
}
}
第一种方法 第二种方法
第三种方法 查询返回结果集的处理 resultMap：适合使用返回值是自定义实体类的情况 resultType：适合使用返回值的数据类型是非自定义的，即jdk的提供的类型
使用resultMap返回自定义类型集合 使用resultType返回List 使用resultType返回单个对象 使用resultType返回List，适用于多表查询返回结果集 使用resultType返回Map<String,Object>，适用于多表查询返回单个结果集
书写一个BookVo实体类
mybatis hibernate都是orm框架，表所存在的列段在实体类model都有映射
实际开发中，会因为某一些需求改变model 破坏model的封装性
此时为了保证Model的封装性，就可以使用vo类来完成指定的需求
package com
.solar
.model
.vo
;
import com
.solar
.model
.Book
;
import java
.util
.List
;
public class BookVo extends Book
{
private Integer min
;
private Integer max
;
private List
<Integer
> bookIds
;
public Integer
getMin() {
return min
;
}
public void setMin(Integer min
) {
this.min
= min
;
}
public Integer
getMax() {
return max
;
}
public void setMax(Integer max
) {
this.max
= max
;
}
public List
<Integer
> getBookIds() {
return bookIds
;
}
public void setBookIds(List
<Integer
> bookIds
) {
this.bookIds
= bookIds
;
}
}
在Mapper中添加方法
配置xml文件
service文件 实现 第一种 第二种 第三种 第四种 第五种
@Test
public void list(){
Map map
=new HashMap();
map
.put("bid",11);
Map book
= this.bookService
.list5(map
);
System
.out
.println(book
);
}
分页查询
Mybatis的分页功能很弱，它是基于内存的分页(查出所有记录再按偏移量offset和边界limit取结果)，在大数据量的情况下这样的分页基本上是没有用的
导入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
>
分页
Mapper xml配置文件
<!--分页
-->
<select id
="listPager" resultType
="java.util.Map" parameterType
="java.util.Map">
select
* from t_mvc_book where bname like
concat(concat('%',#
{bname
}),'%')
</select
>
service
List
<Map
> listPager(Map map
, PageBean pageBean
);
实现
@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
.getPageNum());
System
.out
.println("页大小："+pageInfo
.getPageSize());
System
.out
.println("总记录："+pageInfo
.getTotal());
pageBean
.setTotal(pageInfo
.getTotal()+"");
}
return list
;
}
测试
@Test
public void listPager() {
Map map
=new HashMap();
map
.put("bname","%圣墟%");
PageBean pageBean
=new PageBean();
pageBean
.setPage(2);
List
<Map
> maps
= this.bookService
.listPager(map
, pageBean
);
for (Map map1
: maps
) {
System
.out
.println(map1
);
}
}
结果
特殊字符处理
>(>
;)
<(<
;)
&(&
;)
空格
( 
;)
<![CDATA
[ <= ]]>
mapper xml配置
<!--特殊字符处理
-->
<select id
="list6" resultType
="com.solar.model.Book" parameterType
="com.solar.model.vo.BookVo">
select
* from t_mvc_book where
<![CDATA
[ price
> #
{min
} and price
< #
{max
}]]>
</select
>
service 实现 测试
代码块
测试
package com
.solar
.service
;
import com
.solar
.mapper
.BookMapper
;
import com
.solar
.model
.Book
;
import com
.solar
.model
.vo
.BookVo
;
import com
.solar
.util
.PageBean
;
import com
.solar
.util
.SessionUtil
;
import com
.solar
.util
.StringUtils
;
import org
.apache
.ibatis
.session
.SqlSession
;
import org
.junit
.After
;
import org
.junit
.Before
;
import org
.junit
.Test
;
import java
.util
.ArrayList
;
import java
.util
.HashMap
;
import java
.util
.List
;
import java
.util
.Map
;
public class BookServiceTest {
private BookService bookService
;
private SqlSession sqlSession
;
@Before
public void setUp() throws Exception
{
BookServiceImpl bookService
=new BookServiceImpl();
sqlSession
= SessionUtil
.openSession();
BookMapper mapper
= sqlSession
.getMapper(BookMapper
.class);
bookService
.setBookMapper(mapper
);
this.bookService
=bookService
;
}
@Test
public void deleteByPrimaryKey() {
bookService
.deleteByPrimaryKey(1);
}
@Test
public void insert() {
Book book
=new Book();
book
.setBid(1);
book
.setBname("mybatis输入");
book
.setPrice(100f);
int insert
=bookService
.insert(book
);
System
.out
.println(insert
);
}
@Test
public void insertSelective() {
}
@Test
public void selectByPrimaryKey() {
Book book
=this.bookService
.selectByPrimaryKey(11);
System
.out
.println(book
.toString());
}
@Test
public void updateByPrimaryKeySelective() {
Book book
=bookService
.selectByPrimaryKey(0);
book
.setBname("asad");
book
.setPrice(99f);
bookService
.updateByPrimaryKeySelective(book
);
}
@Test
public void selectBookIn(){
List list
=new ArrayList();
list
.add(11);
list
.add(12);
list
.add(13);
List
<Book
> books
=this.bookService
.selectBookIn(list
);
for (Book book
:books
) {
System
.out
.println(book
);
}
}
@Test
public void selectBookIn1(){
String bname
="飞";
List
<Book
> books
=this.bookService
.selectBookIn1(StringUtils
.toLinkStr(bname
));
for (Book book
:books
) {
System
.out
.println(book
);
}
}
@Test
public void selectBookIn2(){
String bname
="斗";
List
<Book
> books
=this.bookService
.selectBookIn2(StringUtils
.toLinkStr(bname
));
for (Book book
:books
) {
System
.out
.println(book
);
}
}
@Test
public void selectBookIn3(){
String bname
="斗";
List
<Book
> books
=this.bookService
.selectBookIn3(bname
);
for (Book book
:books
) {
System
.out
.println(book
);
}
}
@Test
public void list(){
Map map
=new HashMap();
map
.put("bid",11);
Map book
= this.bookService
.list5(map
);
System
.out
.println(book
);
}
@Test
public void listPager() {
Map map
=new HashMap();
map
.put("bname","%圣墟%");
PageBean pageBean
=new PageBean();
pageBean
.setPage(2);
List
<Map
> maps
= this.bookService
.listPager(map
, pageBean
);
for (Map map1
: maps
) {
System
.out
.println(map1
);
}
}
@Test
public void list6(){
BookVo bookVo
=new BookVo();
bookVo
.setMin(12);
bookVo
.setMax(15);
List
<Book
> list
=this.bookService
.list6(bookVo
);
for (Book book
:list
) {
System
.out
.println(book
);
}
}
@Test
public void updateByPrimaryKey() {
}
@After
public void tearDown() throws Exception
{
sqlSession
.commit();
sqlSession
.close();
}
}
PageBean
package com
.solar
.util
;
import javax
.servlet
.http
.HttpServletRequest
;
import java
.io
.Serializable
;
import java
.util
.Map
;
public class PageBean implements Serializable
{
private static final
long serialVersionUID
= 2422581023658455731L;
private int page
=1;
private int rows
=10;
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
);
}
public int getStartIndex() {
return (this.getPage()-1)*this.rows
;
}
public int getMaxPage() {
int totalpage
=this.total
/this.rows
;
if(this.total
%this.rows
!=0)
totalpage
++;
return totalpage
;
}
public int getNextPage() {
int nextPage
=this.page
+1;
if(this.page
>=this.getMaxPage())
nextPage
=this.getMaxPage();
return nextPage
;
}
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
+ "]";
}
}
BookMapper
package com
.solar
.mapper
;
import com
.solar
.model
.Book
;
import com
.solar
.model
.vo
.BookVo
;
import org
.apache
.ibatis
.annotations
.Param
;
import java
.util
.List
;
import java
.util
.Map
;
public interface BookMapper
{
int deleteByPrimaryKey(Integer bid
);
int insert(Book record
);
int insertSelective(Book record
);
Book
selectByPrimaryKey(Integer bid
);
int updateByPrimaryKeySelective(Book record
);
int updateByPrimaryKey(Book record
);
List
<Book
> selectBookIn(@
Param("bookIds") List bookIds
);
List
<Book
> selectBookIn1(@
Param("bname") String bname
);
List
<Book
> selectBookIn2(@
Param("bname") String bname
);
List
<Book
> selectBookIn3(@
Param("bname") String bname
);
List
<Book
> list1();
List
<Book
> list2();
List
<Book
> list3(BookVo bookVo
);
List
<Map
> list4(Map map
);
Map
list5(Map map
);
List
<Map
> listPager(Map map
);
List
<Book
> list6(BookVo bookVo
);
}
BookMapper.xml
<?xml version
="1.0" encoding
="UTF-8" ?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper
namespace="com.solar.mapper.BookMapper" >
<resultMap id
="BaseResultMap" type
="com.solar.model.Book" >
<constructor
>
<idArg column
="bid" jdbcType
="INTEGER" javaType
="java.lang.Integer" />
<arg column
="bname" jdbcType
="VARCHAR" javaType
="java.lang.String" />
<arg column
="price" jdbcType
="REAL" javaType
="java.lang.Float" />
</constructor
>
</resultMap
>
<sql id
="Base_Column_List" >
bid
, bname
, price
</sql
>
<select id
="selectByPrimaryKey" resultMap
="BaseResultMap" parameterType
="java.lang.Integer" >
select
<include refid
="Base_Column_List" />
from t_mvc_book
where bid
= #
{bid
,jdbcType
=INTEGER
}
</select
>
<delete id
="deleteByPrimaryKey" parameterType
="java.lang.Integer" >
delete from t_mvc_book
where bid
= #
{bid
,jdbcType
=INTEGER
}
</delete>
<insert id
="insert" parameterType
="com.solar.model.Book" >
insert into t_mvc_book
(bid
, bname
, price
)
values
(#
{bid
,jdbcType
=INTEGER
}, #
{bname
,jdbcType
=VARCHAR
}, #
{price
,jdbcType
=REAL
}
)
</insert
>
<insert id
="insertSelective" parameterType
="com.solar.model.Book" >
insert into t_mvc_book
<trim prefix
="(" suffix
=")" suffixOverrides
="," >
<if test
="bid != null" >
bid
,
</if>
<if test
="bname != null" >
bname
,
</if>
<if test
="price != null" >
price
,
</if>
</trim
>
<trim prefix
="values (" suffix
=")" suffixOverrides
="," >
<if test
="bid != null" >
#
{bid
,jdbcType
=INTEGER
},
</if>
<if test
="bname != null" >
#
{bname
,jdbcType
=VARCHAR
},
</if>
<if test
="price != null" >
#
{price
,jdbcType
=REAL
},
</if>
</trim
>
</insert
>
<update id
="updateByPrimaryKeySelective" parameterType
="com.solar.model.Book" >
update t_mvc_book
<set
>
<if test
="bname != null" >
bname
= #
{bname
,jdbcType
=VARCHAR
},
</if>
<if test
="price != null" >
price
= #
{price
,jdbcType
=REAL
},
</if>
</set
>
where bid
= #
{bid
,jdbcType
=INTEGER
}
</update
>
<update id
="updateByPrimaryKey" parameterType
="com.solar.model.Book" >
update t_mvc_book
set bname
= #
{bname
,jdbcType
=VARCHAR
},
price
= #
{price
,jdbcType
=REAL
}
where bid
= #
{bid
,jdbcType
=INTEGER
}
</update
>
<!--
resultType 返回对象
parameterType 返回参数
collection 接受传过来的变量
item 取名
-->
<select id
="selectBookIn" resultType
="com.solar.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
>
<!--三种模糊查询
-->
<select id
="selectBookIn1" resultType
="com.solar.model.Book" parameterType
="java.lang.String">
select
* from t_mvc_book where bname like #
{bname
}
</select
>
<select id
="selectBookIn2" resultType
="com.solar.model.Book" parameterType
="java.lang.String">
select
* from t_mvc_book where bname like
'${bname}'
</select
>
<select id
="selectBookIn3" resultType
="com.solar.model.Book" parameterType
="java.lang.String">
select
* from t_mvc_book where bname like
concat(concat('%',#
{bname
}),'%')
</select
>
<select id
="list1" resultMap
="BaseResultMap" >
select
* from t_mvc_book
</select
>
<select id
="list2" resultType
="com.solar.model.Book" >
select
* from t_mvc_book
</select
>
<select id
="list3" resultType
="com.solar.model.Book" parameterType
="com.solar.model.vo.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
>
<!--分页
-->
<select id
="listPager" resultType
="java.util.Map" parameterType
="java.util.Map">
select
* from t_mvc_book where bname like
concat(concat('%',#
{bname
}),'%')
</select
>
<!--特殊字符处理
-->
<select id
="list6" resultType
="com.solar.model.Book" parameterType
="com.solar.model.vo.BookVo">
select
* from t_mvc_book where
<![CDATA
[ price
> #
{min
} and price
< #
{max
}]]>
</select
>
</mapper
>
BookVo
package com
.solar
.model
.vo
;
import com
.solar
.model
.Book
;
import java
.util
.List
;
public class BookVo extends Book
{
private Integer min
;
private Integer max
;
private List
<Integer
> bookIds
;
public Integer
getMin() {
return min
;
}
public void setMin(Integer min
) {
this.min
= min
;
}
public Integer
getMax() {
return max
;
}
public void setMax(Integer max
) {
this.max
= max
;
}
public List
<Integer
> getBookIds() {
return bookIds
;
}
public void setBookIds(List
<Integer
> bookIds
) {
this.bookIds
= bookIds
;
}
}
BookService
package com
.solar
.service
;
import com
.solar
.model
.Book
;
import com
.solar
.model
.vo
.BookVo
;
import com
.solar
.util
.PageBean
;
import org
.apache
.ibatis
.annotations
.Param
;
import java
.util
.List
;
import java
.util
.Map
;
public interface BookService
{
int deleteByPrimaryKey(Integer bid
);
int insert(Book record
);
int insertSelective(Book record
);
Book
selectByPrimaryKey(Integer bid
);
int updateByPrimaryKeySelective(Book record
);
int updateByPrimaryKey(Book record
);
List
<Book
> selectBookIn(@
Param("bookIds") List bookIds
);
List
<Book
> selectBookIn1(@
Param("bname") String bname
);
List
<Book
> selectBookIn2(@
Param("bname") String bname
);
List
<Book
> selectBookIn3(@
Param("bname") String bname
);
List
<Book
> list1();
List
<Book
> list2();
List
<Book
> list3(BookVo bookVo
);
List
<Map
> list4(Map map
);
Map
list5(Map map
);
List
<Map
> listPager(Map map
, PageBean pageBean
);
List
<Book
> list6(BookVo bookVo
);
}
pom.xml
<?xml version
="1.0" encoding
="UTF-8"?>
<project xmlns
="http://maven.apache.org/POM/4.0.0" xmlns
:xsi
="http://www.w3.org/2001/XMLSchema-instance"
xsi
:schemaLocation
="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion
>4.0.0</modelVersion
>
<groupId
>com
.solar
</groupId
>
<artifactId
>mybatis
</artifactId
>
<version
>1.0-SNAPSHOT
</version
>
<packaging
>war
</packaging
>
<name
>mybatis Maven Webapp
</name
>
<!-- FIXME change it to the project's website
-->
<url
>http
://www
.example
.com
</url
>
<properties
>
<maven
.compiler
.source
>1.8</maven
.compiler
.source
>
<maven
.compiler
.target
>1.8</maven
.compiler
.target
>
</properties
>
<dependencies
>
<!-- ********************** junit单元测试依赖
********************** -->
<dependency
>
<groupId
>junit
</groupId
>
<artifactId
>junit
</artifactId
>
<version
>4.12</version
>
<scope
>test
</scope
>
</dependency
>
<!--**************************slf4j
*****************************-->
<!-- https
://mvnrepository
.com
/artifact
/org
.slf4j
/slf4j
-api
-->
<dependency
>
<groupId
>org
.slf4j
</groupId
>
<artifactId
>slf4j
-api
</artifactId
>
<version
>1.7.22</version
>
</dependency
>
<!-- https
://mvnrepository
.com
/artifact
/org
.slf4j
/slf4j
-log4j12
-->
<dependency
>
<groupId
>org
.slf4j
</groupId
>
<artifactId
>slf4j
-log4j12
</artifactId
>
<version
>1.7.22</version
>
</dependency
>
<!-- https
://mvnrepository
.com
/artifact
/org
.slf4j
/slf4j
-simple
-->
<dependency
>
<groupId
>org
.slf4j
</groupId
>
<artifactId
>slf4j
-simple
</artifactId
>
<version
>1.7.22</version
>
</dependency
>
<!-- ********************** Java Servlet API
********************** -->
<dependency
>
<groupId
>javax
.servlet
</groupId
>
<artifactId
>javax
.servlet
-api
</artifactId
>
<version
>4.0.0</version
>
<scope
>provided
</scope
>
</dependency
>
<!-- ********************** Mybatis依赖
********************** -->
<dependency
>
<groupId
>org
.mybatis
</groupId
>
<artifactId
>mybatis
</artifactId
>
<version
>3.4.5</version
>
</dependency
>
<!-- ********************** Mysql JDBC驱动
********************** -->
<dependency
>
<groupId
>mysql
</groupId
>
<artifactId
>mysql
-connector
-java
</artifactId
>
<version
>5.1.44</version
>
</dependency
>
<!-- ********************** 日志配置
********************** -->
<!--记得修改mybatis
.cfg
.xml添加如下内容
-->
<!--<setting name
="logImpl" value
="LOG4J2"/>-->
<!--核心log4j2jar包
-->
<dependency
>
<groupId
>org
.apache
.logging
.log4j
</groupId
>
<artifactId
>log4j
-core
</artifactId
>
<version
>2.9.1</version
>
</dependency
>
<dependency
>
<groupId
>org
.apache
.logging
.log4j
</groupId
>
<artifactId
>log4j
-api
</artifactId
>
<version
>2.9.1</version
>
</dependency
>
<!--web工程需要包含log4j
-web，非web工程不需要
-->
<dependency
>
<groupId
>org
.apache
.logging
.log4j
</groupId
>
<artifactId
>log4j
-web
</artifactId
>
<version
>2.9.1</version
>
</dependency
>
<!--*****************分页插件
************************-->
<dependency
>
<groupId
>com
.github
.pagehelper
</groupId
>
<artifactId
>pagehelper
</artifactId
>
<version
>5.1.2</version
>
</dependency
>
</dependencies
>
<build
>
<resources
>
<!--解决mybatis
-generator
-maven
-plugin运行时没有将XxxMapper
.xml文件放入target文件夹的问题
-->
<resource
>
<directory
>src
/main
/java
</directory
>
<includes
>
<include
>**
