看mybatis的官方文档select元素的属性有一个属性resultOrdered,这个设置仅针对嵌套结果 select 语句:如果为 true,将会假设包含了嵌套结果集或是分组,当返回一个主结果行时,就不会产生对前面结果集的引用。 这就使得在获取嵌套结果集的时候不至于内存不够用。默认值:false。
感觉能用到,百度搜了一下没有实际的使用demo,今天下载了mybatis的源码,想起来在test目录应该有resultOrdered的使用demo,全局搜索resultOrdered
果真让我给找到了,定位到文件位置,在test目录的org.apache.ibatis.submitted.cursor_nested这个包下:改一改相关的配置就能测试使用了。
mybatis-config
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- <properties resource=".\db.properties"/>-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC">
<property name="" value="" />
</transactionManager>
<dataSource type="org.apache.ibatis.submitted.cursor_nested.MyDataSourceFactory">
<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/test?useunicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC"/>
<property name="userName" value="root"/><!--不存在才会使用默认的属性值-->
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="org/apache/ibatis/submitted/cursor_nested/Mapper.xml" />
</mappers>
</configuration>
其中org.apache.ibatis.submitted.cursor_nested.MyDataSourceFactory为我定义的连接池,使用dbcp2
public class MyDataSourceFactory extends PooledDataSourceFactory {
public MyDataSourceFactory() {
this.dataSource = new BasicDataSource();
}
}
这里需要引入依赖:
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.8.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.commons/commons-pool2 -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-pool2</artifactId>
<version>2.8.0</version>
</dependency>
Mapper
Mapper.xml
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.apache.ibatis.submitted.cursor_nested.Mapper">
<select id="getAllUsers" resultMap="results" resultOrdered="true">
select * from users order by id
</select>
<resultMap type="org.apache.ibatis.submitted.cursor_nested.User" id="results">
<id column="id" property="id"/>
<result property="name" column="name"/>
<collection property="groups" ofType="string">
<result column="group_id"/>
</collection>
<collection property="roles" ofType="string">
<result column="rol_id"/>
</collection>
</resultMap>
</mapper>
mapper接口
public interface Mapper {
Cursor<User> getAllUsers();
}
Entity
public class User {
private Integer id;
private String name;
private List<String> groups;
private List<String> roles;
public List<String> getRoles() {
return roles;
}
public void setRoles(List<String> roles) {
this.roles = roles;
}
public List<String> getGroups() {
return groups;
}
public void setGroups(List<String> groups) {
this.groups = groups;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", groups=" + groups +
", roles=" + roles +
'}';
}
}
sql
create table users (
id int,
name varchar(20),
group_id int,
rol_id int
);
insert into users values(1, 'User1', 1, 1);
insert into users values(1, 'User1', 1, 2);
insert into users values(1, 'User1', 2, 1);
insert into users values(1, 'User1', 2, 2);
insert into users values(1, 'User1', 2, 3);
insert into users values(2, 'User2', 1, 1);
insert into users values(2, 'User2', 1, 2);
insert into users values(2, 'User2', 1, 3);
insert into users values(3, 'User3', 1, 1);
insert into users values(3, 'User3', 2, 1);
insert into users values(3, 'User3', 3, 1);
insert into users values(4, 'User4', 1, 1);
insert into users values(4, 'User4', 1, 2);
insert into users values(4, 'User4', 2, 1);
insert into users values(4, 'User4', 2, 2);
Test
/**
* Copyright 2009-2020 the original author or authors.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.ibatis.submitted.cursor_nested;
import java.io.Reader;
import java.util.Iterator;
import java.util.Map;
import org.apache.ibatis.BaseDataTest;
import org.apache.ibatis.cursor.Cursor;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.BeforeAll;
import org.junit.jupiter.api.Test;
class CursorNestedTest {
private static SqlSessionFactory sqlSessionFactory;
@BeforeAll
static void setUp() throws Exception {
// create a SqlSessionFactory
try (Reader reader = Resources.getResourceAsReader("org/apache/ibatis/submitted/cursor_nested/mybatis-config.xml")) {
sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
}
// populate in-memory database
BaseDataTest.runScript(sqlSessionFactory.getConfiguration().getEnvironment().getDataSource(),
"org/apache/ibatis/submitted/cursor_nested/CreateDB.sql");
}
@Test
void testCursorWithRowBound() {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
// Cursor<User> usersCursor = sqlSession.selectCursor("getAllUsers", null/*, new RowBounds(2, 2)*/);
Mapper mapper = sqlSession.getMapper(Mapper.class);
Cursor<User> allUsers = mapper.getAllUsers();
// Iterator<User> iterator = usersCursor.iterator();
Iterator<User> iterator1 = allUsers.iterator();
// while (iterator.hasNext()) {
// User next = iterator.next();
// System.out.println(next);
// }
while (iterator1.hasNext()) {
User next = iterator1.next();
System.out.println(next);
}
/* Assertions.assertTrue(iterator.hasNext());
User user = iterator.next();
Assertions.assertEquals("User3", user.getName());
Assertions.assertEquals(2, usersCursor.getCurrentIndex());
Assertions.assertFalse(iterator.hasNext());
Assertions.assertFalse(usersCursor.isOpen());
Assertions.assertTrue(usersCursor.isConsumed());*/
}
}
}
结果输出
User{id=1, name='User1', groups=[1, 2], roles=[1, 2, 3]}
User{id=2, name='User2', groups=[1], roles=[1, 2, 3]}
User{id=3, name='User3', groups=[1, 2, 3], roles=[1]}
User{id=4, name='User4', groups=[1, 2], roles=[1, 2]}
mybatis添加打印sql日志配置:
<settings>
<setting name="logImpl" value="STDOUT_LOGGING" />
</settings>
打印日志:感觉有点乱----
==> Preparing: select * from users order by id
==> Parameters:
<== Columns: id, name, group_id, rol_id
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 1, User1, 1, 1
<== Row: 1, User1, 1, 2
<== Row: 1, User1, 2, 1
<== Row: 1, User1, 2, 2
<== Row: 1, User1, 2, 3
<== Row: 2, User2, 1, 1
User{id=1, name='User1', groups=[1, 2], roles=[1, 2, 3]}
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 2, User2, 1, 1
<== Row: 2, User2, 1, 2
<== Row: 2, User2, 1, 3
<== Row: 3, User3, 1, 1
User{id=2, name='User2', groups=[1], roles=[1, 2, 3]}
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 3, User3, 1, 1
<== Row: 3, User3, 2, 1
<== Row: 3, User3, 3, 1
<== Row: 4, User4, 1, 1
User{id=3, name='User3', groups=[1, 2, 3], roles=[1]}
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Row: 4, User4, 1, 1
<== Row: 4, User4, 1, 2
<== Row: 4, User4, 2, 1
<== Row: 4, User4, 2, 2
<== Total: 240
User{id=4, name='User4', groups=[1, 2], roles=[1, 2]}
<== Total: 240
Resetting autocommit to true on JDBC Connection [210281271, URL=jdbc:mysql://localhost:3306/test?useunicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC, MySQL Connector/J]
Closing JDBC Connection [210281271, URL=jdbc:mysql://localhost:3306/test?useunicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=UTC, MySQL Connector/J]