collection 关联查询 First_PageHelper 缺失异常

[pagehelper]

collection 关联查询 First_PageHelper 缺失异常

[abel533]

换4.2.x的最新版本或者5.0.1就可以解决。

[zengqw]

非常感谢您百忙之中的回复！

我试过了4.2.1和5.0.1版本都没解决这个问题。

思考过后,提一个小小的改动，你看看是否可行:
PageSqlSource.getBoundSql:

public BoundSql getBoundSql(Object parameterObject) {
Boolean count = getCount();
if (count == null) {
return getDefaultBoundSql(parameterObject);
} else if (count) {
return getCountBoundSql(parameterObject);
} else {
return getPageBoundSql(parameterObject);
}
}

改动后:

public BoundSql getBoundSql(Object parameterObject) {
Boolean count = getCount();
if (count == null) {
return getDefaultBoundSql(parameterObject);
} else if (count) {
return getCountBoundSql(parameterObject);
} else {
BoundSql boundSql = getPageBoundSql(parameterObject);
SqlUtil.clearLocalPage();
return boundSql;
}
}

思路是,在获取count SQL之后就会获取分页Sql,而我们希望获取分页sql只是一次性的(下次查询有可能是子查询所以不希望这里再得到分页sql)，获取完之后就失效了(这里请您判断下是否会有其他影响),所以在这里提前清理LocalPage。

[abel533]

在5.0.1和4.1.3都测试了，没有发现这样的问题，4.1.3输出日志：

DEBUG [main] - ==>  Preparing: SELECT count(0) FROM country 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: C1
TRACE [main] - <==        Row: 183
DEBUG [main] - <==      Total: 1
DEBUG [main] - Cache Hit Ratio [com.github.pagehelper.mapper.CountryMapper]: 0.0
DEBUG [main] - ==>  Preparing: select * from country limit ? offset ? 
DEBUG [main] - ==> Parameters: 5(Integer), 0(Integer)
TRACE [main] - <==    Columns: ID, COUNTRYNAME, COUNTRYCODE
TRACE [main] - <==        Row: 1, Angola, AO
DEBUG [main] - Cache Hit Ratio [com.github.pagehelper.mapper.CountryMapper]: 0.0
DEBUG [main] - ====>  Preparing: select * from country order by id 
DEBUG [main] - ====> Parameters: 
TRACE [main] - <====    Columns: ID, COUNTRYNAME, COUNTRYCODE
TRACE [main] - <====        Row: 1, Angola, AO
TRACE [main] - <====        Row: 2, Afghanistan, AF
TRACE [main] - <====        Row: 3, Albania, AL
TRACE [main] - <====        Row: 4, Algeria, DZ
TRACE [main] - <====        Row: 5, Andorra, AD
TRACE [main] - <====        Row: 6, Anguilla, AI
TRACE [main] - <====        Row: 7, Antigua and Barbuda, AG
TRACE [main] - <====        Row: 8, Argentina, AR
TRACE [main] - <====        Row: 9, Armenia, AM
TRACE [main] - <====        Row: 10, Australia, AU
TRACE [main] - <====        Row: 11, Austria, AT
TRACE [main] - <====        Row: 12, Azerbaijan, AZ
TRACE [main] - <====        Row: 13, Bahamas, BS
TRACE [main] - <====        Row: 14, Bahrain, BH
TRACE [main] - <====        Row: 15, Bangladesh, BD
TRACE [main] - <====        Row: 16, Barbados, BB
TRACE [main] - <====        Row: 17, Belarus, BY
TRACE [main] - <====        Row: 18, Belgium, BE
TRACE [main] - <====        Row: 19, Belize, BZ
TRACE [main] - <====        Row: 20, Benin, BJ
TRACE [main] - <====        Row: 21, Bermuda Is., BM
TRACE [main] - <====        Row: 22, Bolivia, BO
TRACE [main] - <====        Row: 23, Botswana, BW
TRACE [main] - <====        Row: 24, Brazil, BR
TRACE [main] - <====        Row: 25, Brunei, BN
TRACE [main] - <====        Row: 26, Bulgaria, BG
TRACE [main] - <====        Row: 27, Burkina-faso, BF

[abel533]

用的测试方法如下：

    <resultMap id="collectionMap" type="com.github.pagehelper.model.Country">
        <id column="id" property="id"/>
        <collection property="countries" column="countryname" ofType="com.github.pagehelper.model.Country" select="selectAll"/>
    </resultMap>
    
    <select id="selectCollectionMap" resultMap="collectionMap">
        select * from country
    </select>

这个方法没太大意义，对测试结果应该没有影响。

[abel533]

select * from country order by id
这个查询由于没有条件，所以这里只查询一次，后面都用的一级缓存。

[zengqw]

hi,感谢您的回复，可能是我没把问题描述清楚，这次我也试图用代码来描述一下。
mapper配置

	<resultMap id="collectionMap" type="com.example.demo.model.CountryVo">
		<id column="id" property="id" />
		<collection property="countries" column="id"
			ofType="com.example.demo.model.Country" select="com.example.demo.mapper.CountryMapper.selectAllCountry" />
	</resultMap>

	<select id="selectCollectionMap" resultMap="collectionMap">
		select * from country
	</select>
	<select id="selectAllCountry" resultType="com.example.demo.model.Country">
		select * from country
	</select>

java代码:
增加了一个实体类:

public class CountryVo extends Country {
	List<Country> countries;
	public List<Country> getCountries() {
		return countries;
	}
	public void setCountries(List<Country> countries) {
		this.countries = countries;
	}
}

主要执行SQL代码:

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
//@Transactional
@SpringBootTest(classes = Application.class)
public class MyBatisTest {
    private Logger logger = LoggerFactory.getLogger(getClass());
    @Autowired
    private CountryMapper countryMapper;
    @Autowired
	protected SqlSessionTemplate session;

    @Test
    public void testSelectCollectionMap() {
    	//这里可以先打断点 执行session.getConfiguration().getMappedStatement("com.example.demo.mapper.CountryMapper.selectAllCountry"); 
       	//目的是查看 此MappedStatement的sqlSource属性(此时是SqlSource)
    	List list1 = PageHelper.startPage(1,3);
    	countryMapper.selectAllCountry();

    	// 同样此处再打断点查看相应的selectAllCountry的MappedStatement的sqlSource属性(此时是PageRawSqlSource)
        //此处的selectCollectionMap 包含了 上面的selectAllCountry子查询
    	PageHelper.startPage(1,3);
    	List list2 = countryMapper.selectCollectionMap();
    	logger.debug("=========end=============");
    }
}

日志为:
DEBUG com.example.demo.MyBatisTest - =========first=============
DEBUG c.e.d.m.C.selectAllCountry_COUNT - ==> Preparing: SELECT count(0) FROM country
DEBUG c.e.d.m.C.selectAllCountry_COUNT - ==> Parameters:
DEBUG c.e.d.m.C.selectAllCountry_COUNT - <== Total: 1
DEBUG c.e.d.m.C.selectAllCountry - ==> Preparing: select * from country limit ?,?
DEBUG c.e.d.m.C.selectAllCountry - ==> Parameters: 0(Integer), 3(Integer)
DEBUG c.e.d.m.C.selectAllCountry - <== Total: 3
DEBUG com.example.demo.MyBatisTest - =========second=============
DEBUG c.e.d.m.C.selectCollectionMap_COUNT - ==> Preparing: SELECT count(0) FROM country
DEBUG c.e.d.m.C.selectCollectionMap_COUNT - ==> Parameters:
DEBUG c.e.d.m.C.selectCollectionMap_COUNT - <== Total: 1
DEBUG c.e.d.m.C.selectCollectionMap - ==> Preparing: select * from country limit ?,?
DEBUG c.e.d.m.C.selectCollectionMap - ==> Parameters: 0(Integer), 3(Integer)
DEBUG c.e.d.m.C.selectAllCountry - ====> Preparing: select * from country limit ?,?
DEBUG c.e.d.m.C.selectAllCountry - ====> Parameters: 1(Integer), 1(Integer)
DEBUG c.e.d.m.C.selectAllCountry - <==== Total: 1
DEBUG c.e.d.m.C.selectAllCountry - ====> Preparing: select * from country limit ?,?
DEBUG c.e.d.m.C.selectAllCountry - ====> Parameters: 2(Integer), 2(Integer)
DEBUG c.e.d.m.C.selectAllCountry - <==== Total: 2
DEBUG c.e.d.m.C.selectAllCountry - ====> Preparing: select * from country limit ?,?
DEBUG c.e.d.m.C.selectAllCountry - ====> Parameters: 3(Integer), 3(Integer)
DEBUG c.e.d.m.C.selectAllCountry - <==== Total: 3
DEBUG c.e.d.m.C.selectCollectionMap - <== Total: 3
DEBUG com.example.demo.MyBatisTest - =========end=============

测试所显示的子查询(selectAllCountry)也会进行分页查询，而selectCollectionMap查询的目的只是希望父查询是分页的。

[zengqw]

另外，这个问题在不同数据库的使用上反应出来的问题也不一样:
1).oracle环境下是抛异常" First_PageHelper"缺失。
2).mySql环境下没有发生抛错，但是运行结果有误。

[zengqw]

hi,感谢您抽空看这个问题，这个我在三楼提议的提前清空线程变量里面的分页数据
SqlUtil.clearLocalPage();
是否可行?
感觉这样改动会比较小(我这边大概测了一下是修复了此问题，但没全面测，不知道是否会有影响到其他功能)

[abel533]

@zengqw 不会影响其他功能。

[zengqw]

好的。非常感谢。

[abel533]

从描述来看，你用的应该是4.x版本，5.0+不存在这个问题，你升级版本试试？

测试又改了改，仍然没问题：
https://github.com/pagehelper/Mybatis-PageHelper/blob/master/src/test/java/com/github/pagehelper/test/basic/CollectionMapTest.java

[zyong2004]

尝试了一下，我也不存在你说的问题@zengqw

[zengqw]

@zyong2004 @abel533 恩恩，5.0.1不存在这个问题。这个问题只在4.X版本有。