mybatis联表分页查询附带总记录数

1. 问题

今天在完成一个项目的联表查询时，发现mybatis-plus无法完成联表，所以就想着自己写sql进行查询吧，但是又有个问题是我这个接口是分页接口，需要获取总记录数，哟嚯，没得法子了，笨办法到是有，查询的时候不加分页条件，查询出来以后进行代码分页，但是这种如果对于数据量大的就会特别消耗内存和时间，所以不太可行。

2. 解决方法

经过查询度娘，找到一个可以使用多sql语句查询，两个函数 SQL_CALC_FOUND_ROWS 和 FOUND_ROWS()，那么具体的我们看下边的代码。

3. 实践

3.1 编写自己的resultMap

<resultMap id="trainResultMap" type="net.maku.buss.vo.BussComebackReportVo">
        <id property="id" column="id"/>
        <result property="backType" column="back_type"/>
        <result property="carNumber" column="car_number"/>
        <result property="comeAddress" column="come_address"/>
        <result property="comeCommunityCode" column="come_community_code"/>
        <result property="personName" column="person_name"/>
        <result property="personIdCard" column="person_id_card"/>
        <result property="personEmail" column="email"/>
        <result property="personPhone" column="phone"/>
</resultMap>

3.2 编写总记录数resultMap

<resultMap id="count" type="java.lang.Integer">
        <result column="total"/>
</resultMap>

3.3 编写sql语句

SELECT SQL_CALC_FOUND_ROWS
    c.*,
    p.email,
    p.person_id_card,
    p.person_name,
    p.phone_number AS phone 
FROM
    buss_comeback_report c
    LEFT JOIN buss_person p ON c.person_id = p.id 
WHERE
    p.email LIKE concat( '%','176', '%' ) 
    OR p.person_id_card LIKE concat( '%','176', '%' ) 
    OR p.person_name LIKE concat( '%','176', '%' ) 
    OR p.phone_number LIKE concat( '%','176', '%' ) 
    LIMIT 0,1;
SELECT
    FOUND_ROWS() AS total;

执行看看，有两个结果集

3.4 编写mybatis select语句

这里是mybatis的xml，在select标签中的resultMap中写两个属性，接收两组数据，其中包含判断空值，主要在SQL_CALC_FOUND_ROWS和FOUND_ROWS()

<select id="getList" resultMap="trainResultMap,count">
        select SQL_CALC_FOUND_ROWS c.*,p.email,p.person_id_card,p.person_name,p.phone_number as phone
        from buss_comeback_report c
        left join buss_person p on c.person_id = p.id
        <where>
            <if test="blurry != null and blurry != ''">
                and
                p.email like concat('%',#{blurry},'%') or
                p.person_id_card like concat('%',#{blurry},'%') or
                p.person_name like concat('%',#{blurry},'%') or
                p.phone_number like concat('%',#{blurry},'%')
            </if>
            <if test="comeCommunityCode != null and comeCommunityCode != ''">
                and
                c.come_community_code like concat(#{comeCommunityCode},'%')
            </if>
            <if test="hasRiskArea14 != null and hasRiskArea14 != ''">
                and
                c.has_risk_area_14 = #{hasRiskArea14}
            </if>
            <if test="status != null and status != ''">
                and
                c.status = #{status}
            </if>
            limit #{offset},#{limit};

            SELECT FOUND_ROWS() AS total;
        </where>
</select>

3.5 对返回的数据进行处理

我们一般接收的是一组数据，直接用list接收，那么这个两组数据怎么办呢？我们使用object接收

List<Object> list = baseMapper.getList(params);

断点打到这里可以看到返回的list第一个元素是数据集合，第二个元素就是总记录数
我们通过获取下标获得数据

List<实体> dtos = ((List<实体>)list).get(0);
Integer total = ((List<Integer>)list).get(1);

4. 解决错误

启动应用调用接口查看

查看后台发现是sql错误

### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS() AS total' at line 15
### The error may exist in file [D:\IDEAProjects\yqmanage-boot\maku-boot-new\target\classes\mapper\BussComebackReportDao.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select SQL_CALC_FOUND_ROWS c.*,p.email,p.person_id_card,p.person_name,p.phone_number as phone  from buss_comeback_report c  left join buss_person p on c.person_id = p.id WHERE  p.email like concat('%',?,'%') or p.person_id_card like concat('%',?,'%') or  p.person_name like concat('%',?,'%') or p.phone_number like concat('%',?,'%') limit ?,?;  SELECT FOUND_ROWS() AS total;
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS() AS total' at line 15
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS() AS total' at line 15

org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS() AS total' at line 15
### The error may exist in file [D:\IDEAProjects\yqmanage-boot\maku-boot-new\target\classes\mapper\BussComebackReportDao.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select SQL_CALC_FOUND_ROWS c.*,p.email,p.person_id_card,p.person_name,p.phone_number as phone  from buss_comeback_report c  left join buss_person p on c.person_id = p.id   WHERE   p.email like concat('%',?,'%') or   p.person_id_card like concat('%',?,'%') or   p.person_name like concat('%',?,'%') or  p.phone_number like concat('%',?,'%') limit ?,?; SELECT FOUND_ROWS() AS total;
### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS() AS total' at line 15
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT FOUND_ROWS() AS total' at line 15
    at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:239)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)

但是我们明明在navicat测试没问题啊！！！
最后查询得到jdbc 驱动默认不支持多sql语句，我们这属于两个sql语句，所以会报错，我们只需要在配置文件的url中添加allowMultiQueries=true即可

5. 测试

成功查询出列表和总记录数

6. 结尾

这篇文章就到此结束了，又解决一个难题，之前因为分页好头疼