Mybatis动态SQL与分页

喜欢ヅ旅行 2022-12-27 08:45 202阅读 0赞

# Mybatis动态SQL与分页 #

## Mybatis动态SQL ##

### 1. if ###

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    	select * from user where 
    		<if test="username != null">
    		   username=#{username}
    		</if>
    		
    		<if test="sex != null">
    		   and sex=#{sex}
    		</if>
    </select>
    
    复制代码

> 如果 sex 等于 null，那么查询语句为 select \* from user where username=\#\{username\},但是如果usename 为空呢？那么查询语句为 select \* from user where and sex=\#\{sex\}，这是错误的 SQL 语句，可以用where解决

### 2. if+where ###

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    	select * from user 
    	<where>
    		<if test="username != null">
    		   username=#{username}
    		</if>
    		
    		<if test="username != null">
    		   and sex=#{sex}
    		</if>
    	</where> 
    </select>
    复制代码

> 这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

### 3. if+set ###

用于处理update语句中的set

<update id="updateUserById" parameterType="com.ys.po.User">
    	update user u
    		<set>
    			<if test="username != null and username != ''">
    				u.username = #{username},
    			</if>
    			<if test="sex != null and sex != ''">
    				u.sex = #{sex}
    			</if>
    		</set>
    	
    	 where id=#{id}
    </update>
    复制代码

### 4. choose(when,otherwise) ###

> 我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句。

<select id="selectUserByChoose" resultType="com.ys.po.User" parameterType="com.ys.po.User">
    	select * from user 
    	<where>
    		<choose>
    			<when test="id !='' and id != null">
    				id=#{id}
    			</when>
    			<when test="username !='' and username != null">
    				and username=#{username}
    			</when>
    			<otherwise>
    				and sex=#{sex}
    			</otherwise>
    		</choose> 
    	</where>
    </select>
    复制代码

如果id有值语句为:select \* from user where id=? 如果id没有username有: select \* from user where username=?; 如果id和username都没有: select \* from user where sex=?

### 5. trim ###

格式化标记trim，可以完成set或者是where操作

1.  改写where

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    	select * from user 
    	
    	<trim prefix="where" prefixOverrides="and | or">
    		<if test="username != null">
    		   and username=#{username}
    		</if>
    		<if test="sex != null">
    		   and sex=#{sex}
    		</if>
    	</trim>
    </select>
    复制代码

prefix：前缀

prefixoverride：去掉第一个and或者是or 2. 改写set

<update id="updateUserById" parameterType="com.ys.po.User">
      update user u
      	
      	<trim prefix="set" suffixOverrides=",">
      		<if test="username != null and username != ''">
      			u.username = #{username},
      		</if>
      		<if test="sex != null and sex != ''">
      			u.sex = #{sex},
      		</if>
      	</trim>
      
       where id=#{id}
    </update>
    复制代码

suffix：后缀

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

### 6. SQL片段 ###

当有一段sql片段需要重复调用时，可以定义这一片段，方便其他位置调用

<sql id="selectUserByUserNameAndSexSQL">
    	<if test="username != null and username != ''">
    		AND username = #{username}
    	</if>
    	<if test="sex != null and sex != ''">
    		AND sex = #{sex}
    	</if>
    </sql>
    复制代码

引用

<select id="selectUserByUsernameAndSex" resultType="user" parameterType="com.ys.po.User">
    	select * from user 
    	<trim prefix="where" prefixOverrides="and | or">
    		
    		<include refid="selectUserByUserNameAndSexSQL"></include>
    		
    	</trim>
    </select>
    复制代码

注意：1. 最好基于 单表来定义 sql 片段，提高片段的可重用性

2. 在 sql 片段中最好不要包括 where

### 7. foreach遍历 ###

封装多个属性值的类，当做遍历对象

package com.ys.vo;
    
    import java.util.List;
    
    public class UserVo {
    	//封装多个用户的id
    	private List<Integer> ids;
    
    	public List<Integer> getIds() {
    		return ids;
    	}
    
    	public void setIds(List<Integer> ids) {
    		this.ids = ids;
    	}
    
    }　
    复制代码

用foreach

<select id="selectUserByListId" parameterType="com.ys.vo.UserVo" resultType="com.ys.po.User">
    	select * from user 
    	<where>
    		
    		<foreach collection="ids" item="id" open="and (" close=")" separator="or">
    			id=#{id}
    		</foreach>
    	</where>
    </select>
    复制代码

## resultMap与resultType的区别 ##

resultType与resultMap都是将查询到的值映射到对应javabean中，resultMap表示将查询结果集中的列一一映射到bean对象的各个属性。映射的查询结果集中的列标签可以根据需要灵活变化，resultType是根据字段名一一对应映射进去

### resultMap ###

<resultMap id="userMap" type="com.hyx.bean.UserBean">
            <id property="id" column="password"/>
            <result property="name" column="name"/>
            <result property="password" column="id"/>
            <result property="username" column="username"/>
        </resultMap>
        
        <select id="selectUserById"
                     resultMap="userMap" parameterType="int">
                    select * from user where id = #{id}
            </select>
    复制代码

### resultType ###

<select id="selectUserById"
                     resultType="com.hyx.bean.UserBean" parameterType="int">
                    
                    select * from user where id = #{id}
            </select>
    复制代码

# 分页 #

## 1. 借助数组进行分页 ##

> 原理：进行数据库查询操作时，获取到数据库中所有满足条件的记录，保存在应用的临时数组中，再通过List的subList方法，获取到满足条件的所有记录。

*  查询所有符合的数据

<resultMap id="userMap" type="java.util.HashMap">
            <id property="id" column="id"/>
            <result property="name" column="name"/>
            <result property="password" column="password"/>
            <result property="username" column="username"/>
        </resultMap>
        <select id="selectUserByArray" resultMap="userMap" >
           select * from user
        </select>
    复制代码

*  对获取的数据进行处理

// 通过接收currPage参数表示显示第几页的数据，pageSize表示每页显示的数据条数。
        public List<Map<String, Object>> selectUserByArray(int currPage, int pageSize,UserMapper userMapper) {
            List<Map<String, Object>> maps = userMapper.selectUserByArray();
    //        从第几条数据开始
            int firstIndex = (currPage - 1) * pageSize;
    //        到第几条数据结束
            int lastIndex = currPage * pageSize;
            return maps.subList(firstIndex, lastIndex);
        }
    复制代码

## 2. 使用sql进行分页 ##

简单来说就是要多少取多少

xml查询文件

<select id="selectUserBySQL" resultMap="userMap" parameterType="map" >
           select * from user limit #{currIndex} , #{pageSize}
        </select>
    复制代码

service文件

// 第一个参数表示从第几页开始，第二个参数表示一页多少条记录
        public List<Map<String, Object>> selectUserBySQL(int currPage, int pageSize, UserMapper userMapper) {
            Map<String, Object> data = new HashMap<String, Object>();
            data.put("currIndex", (currPage - 1) * pageSize);
            data.put("pageSize", pageSize);
            return userMapper.selectUserBySQL(data);
        }
    复制代码

## 3. 使用插件PageHelper进行拦截查询分页 ##

*  加载PageHelper包:

<dependency>
                <groupId>com.github.pagehelper</groupId>
                <artifactId>pagehelper</artifactId>
                <version>5.1.6</version>
            </dependency>
    复制代码

建议使用5.1.6及其以上版本，经过测试发现5.0.1以下版本在Page对象的toString打印中是不实现父类toString的，较难理解，且与新版本所用配置也有所不同

*  配置文件

配置文件内的各个标签必须严格按照其规定顺序，否则会报错

<plugins>
            <plugin interceptor="com.github.pagehelper.PageInterceptor">
                
                <property name="helperDialect" value="mysql"/>
    
            </plugin>
        </plugins>
    复制代码

*  实现

由于PageHelper是直接插入下一条查询对其进行拦截分页，因此，其方法后应紧接查询方法

// 第一个参数表示从第几页开始，第二个参数表示一页多少条记录，第三个参数表示是否返回总的真实记录数
            // 注意：PageHelper.startPage(currentPage,pageSize,true);要紧接着查询，否则可能不分页
            PageHelper.startPage(2, 3);
            List<Map<String, Object>> mapsPage = mapper.selectUserByArray();
            System.out.println(((Page)mapsPage));
            PageInfo<UserBean> pageInfo = new PageInfo(mapsPage);
            System.out.println(pageInfo);
    复制代码

此时mapsPage实际为Page对象，可强转为Page，并且使用Page方法，PageInfo有许多方法与属性可以使用

PageInfo内各属性解析

//当前页
        private int pageNum;
        //每页的数量
        private int pageSize;
        //当前页的数量
        private int size;
     
        //由于startRow和endRow不常用，这里说个具体的用法
        //可以在页面中"显示startRow到endRow 共size条数据"
     
        //当前页面第一个元素在数据库中的行号
        private int startRow;
        //当前页面最后一个元素在数据库中的行号
        private int endRow;
        //总记录数
        private long total;
        //总页数
        private int pages;
        //结果集
        private List<T> list;
     
        //前一页
        private int prePage;
        //下一页
        private int nextPage;
     
        //是否为第一页
        private boolean isFirstPage = false;
        //是否为最后一页
        private boolean isLastPage = false;
        //是否有前一页
        private boolean hasPreviousPage = false;
        //是否有下一页
        private boolean hasNextPage = false;
        //导航页码数
        private int navigatePages;
        //所有导航页号
        private int[] navigatepageNums;
        //导航条上的第一页
        private int navigateFirstPage;
        //导航条上的最后一页
        private int navigateLastPage;
    复制代码

参考文章:

[blog.csdn.net/chenbaige/a…][blog.csdn.net_chenbaige_a]

[blog.csdn.net_chenbaige_a]: https://blog.csdn.net/chenbaige/article/details/70846902