mybatis动态SQL以及分页模糊查询和返回结果集处理
1. mybatis动态sql
BookMapper
List<Book> selectByIn(@Param("bookIds") List bookIds);
List<Book> selectBylike1(@Param("bname") String bname);
List<Book> selectBylike2(@Param("bname") String bname);
List<Book> selectBylike3(@Param("bname") String bname);
List<Book> list1();
List<Book> list2();
Book list3(BookVo bookVo);
List<Map> list4(Map map);
Map list5(Map map);
List<Map> list6(BookVo bookVo);
List<Map> list7(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.lin.mapper.BookMapper" >
<!--resultMap:适合使用返回值是自定义实体类的情况-->
<!--resultType:适合使用返回值的数据类型是非自定义的,即jdk的提供的类型-->
<!--下面会使用到这个BaseResultMap-->
<resultMap id="BaseResultMap" type="com.lin.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.lin.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.lin.model.Book" >
insert into t_mvc_book
<trim prefix="(" suffix=")" suffixOverrides="," >
<!-- trim:一样的sql语句拼接:prefix前缀,suffi 后缀。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.lin.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.lin.model.Book" >
update t_mvc_book
set bname = #{
bname,jdbcType=VARCHAR},
price = #{
price,jdbcType=REAL}
where bid = #{
bid,jdbcType=INTEGER}
</update>
<!--自己写的-->
<select id="selectByIn" resultType="com.lin.model.Book" parameterType="java.util.List">
select * from t_mvc_book where bid in
<!-- foreach 就是循环的意思,collection代表要被循环参数集合。
open和close代表开始和结束拼接字符串。separator代表item之间的分割符。
item就是当前正在循环的变量定义。就当java中的foreach看就能看懂了-->
<foreach collection="bookIds" open="(" close=")" separator="," item="bid">
#{
bid}
</foreach>
</select>
<!--模糊查的三种方式-->
<!--注意:#{
…}自带引号,${
…}有sql注入的风险-->
<!--推荐使用这种-->
<select id="selectBylike1" resultType="com.lin.model.Book" parameterType