添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
失恋的牙膏  ·  数据库：查询生成器 | Laravel中文文档·  1 周前    · 
酒量大的煎鸡蛋  ·  SQL重复记录查询的几种方法(转) - ...·  3 天前    · 
憨厚的硬盘  ·  Optimizer Hints | ...·  3 天前    · 
伤情的遥控器  ·  mybatis-plus连接SQL ...·  3 天前    · 
道上混的紫菜汤  ·  查询服务中的SQL语法 | Adobe ...·  3 天前    · 
调皮的草稿纸  ·  智能家居控制系统-智能家居设备-智能家居解决 ...·  5 月前    · 
斯文的烤面包  ·  【预告】“商谈新文化”之正午阳光董事长侯鸿亮 ...·  5 月前    · 
豁达的肉夹馍  ·  Armor ...·  5 月前    · 
痴情的雪糕  ·  暗部蓝奏云·  6 月前    · 
气宇轩昂的钥匙  ·  乐安县人民政府 区域概况 乐安概况·  6 月前    · 
link管理  ›  mybatis-dynamic-sql/src/test/java/examples/groupby/GroupByTest.java at master · mybatis/mybatis-dyna
try last test select
https://github.com/mybatis/mybatis-dynamic-sql/blob/master/src/test/java/examples/groupby/GroupByTest.java
拉风的小熊猫
10 月前
import static examples . groupby . AddressDynamicSqlSupport .*;
import static examples . groupby . Person2DynamicSqlSupport . person2 ;
import static examples . groupby . PersonDynamicSqlSupport .*;
import static org . assertj . core . api . Assertions . assertThat ;
import static org . mybatis . dynamic . sql . SqlBuilder .*;
import java . io . InputStream ;
import java . io . InputStreamReader ;
import java . sql . Connection ;
import java . sql . DriverManager ;
import java . util . List ;
import java . util . Map ;
import org . apache . ibatis . datasource . unpooled . UnpooledDataSource ;
import org . apache . ibatis . jdbc . ScriptRunner ;
import org . apache . ibatis . mapping . Environment ;
import org . apache . ibatis . session . Configuration ;
import org . apache . ibatis . session . SqlSession ;
import org . apache . ibatis . session . SqlSessionFactory ;
import org . apache . ibatis . session . SqlSessionFactoryBuilder ;
import org . apache . ibatis . transaction . jdbc . JdbcTransactionFactory ;
import org . junit . jupiter . api . BeforeEach ;
import org . junit . jupiter . api . Test ;
import org . mybatis . dynamic . sql . render . RenderingStrategies ;
import org . mybatis . dynamic . sql . select . HavingApplier ;
import org . mybatis . dynamic . sql . select . render . SelectStatementProvider ;
import org . mybatis . dynamic . sql . util . mybatis3 . CommonSelectMapper ;
class GroupByTest {
private static final String JDBC_URL = "jdbc:hsqldb:mem:aname" ;
private static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver" ;
private SqlSessionFactory sqlSessionFactory ;
@ BeforeEach
void setup () throws Exception {
Class . forName ( JDBC_DRIVER );
InputStream is = getClass (). getResourceAsStream ( "/examples/groupby/CreateGroupByDB.sql" );
assert is != null ;
try ( Connection connection = DriverManager . getConnection ( JDBC_URL , "sa" , "" )) {
ScriptRunner sr = new ScriptRunner ( connection );
sr . setLogWriter ( null );
sr . runScript ( new InputStreamReader ( is ));
}
UnpooledDataSource ds = new UnpooledDataSource ( JDBC_DRIVER , JDBC_URL , "sa" , "" );
Environment environment = new Environment ( "test" , new JdbcTransactionFactory (), ds );
Configuration config = new Configuration ( environment );
config . addMapper ( CommonSelectMapper . class );
sqlSessionFactory = new SqlSessionFactoryBuilder (). build ( config );
}
@ Test
void testBasicGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( gender , count ())
. from ( person )
. groupBy ( gender )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select gender, count(*) from Person group by gender" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 2 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "GENDER" , "Male" );
assertThat ( row ). containsEntry ( "C2" , 4L );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "GENDER" , "Female" );
assertThat ( row ). containsEntry ( "C2" , 3L );
}
}
@ Test
void testBasicGroupByWithAggregateAlias () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( gender , count (). as ( "count" ))
. from ( person )
. groupBy ( gender )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select gender, count(*) as count from Person group by gender" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 2 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "GENDER" , "Male" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "GENDER" , "Female" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
}
}
@ Test
void testGroupByAfterJoin () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , streetAddress , count (). as ( "count" ))
. from ( person , "p" ). join ( address , "a" ). on ( person . addressId , equalTo ( address . id ))
. groupBy ( lastName , streetAddress )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select p.last_name, a.street_address, count(*) as count" +
" from Person p join Address a on p.address_id = a.address_id" +
" group by p.last_name, a.street_address" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 2 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
assertThat ( row ). containsEntry ( "STREET_ADDRESS" , "123 Main Street" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
assertThat ( row ). containsEntry ( "STREET_ADDRESS" , "456 Main Street" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
}
}
@ Test
void testUnionAfterJoin () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , firstName , streetAddress )
. from ( person , "p" ). join ( address , "a" ). on ( person . addressId , equalTo ( address . id ))
. union ()
. select ( person2 . lastName , person2 . firstName , streetAddress )
. from ( person2 , "p" ). join ( address , "a" ). on ( person2 . addressId , equalTo ( address . id ))
. orderBy ( lastName , firstName )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select p.last_name, p.first_name, a.street_address" +
" from Person p join Address a on p.address_id = a.address_id" +
" union" +
" select p.last_name, p.first_name, a.street_address" +
" from Person2 p join Address a on p.address_id = a.address_id" +
" order by last_name, first_name" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 10 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
assertThat ( row ). containsEntry ( "FIRST_NAME" , "Dino" );
assertThat ( row ). containsEntry ( "STREET_ADDRESS" , "123 Main Street" );
row = rows . get ( 9 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Smith" );
assertThat ( row ). containsEntry ( "FIRST_NAME" , "Suzy" );
assertThat ( row ). containsEntry ( "STREET_ADDRESS" , "123 Main Street" );
}
}
@ Test
void testUnionAllAfterJoin () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , firstName , streetAddress )
. from ( person , "p" ). join ( address , "a" ). on ( person . addressId , equalTo ( address . id ))
. unionAll ()
. select ( person2 . lastName , person2 . firstName , streetAddress )
. from ( person2 , "p" ). join ( address , "a" ). on ( person2 . addressId , equalTo ( address . id ))
. orderBy ( lastName , firstName )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select p.last_name, p.first_name, a.street_address" +
" from Person p join Address a on p.address_id = a.address_id" +
" union all" +
" select p.last_name, p.first_name, a.street_address" +
" from Person2 p join Address a on p.address_id = a.address_id" +
" order by last_name, first_name" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 10 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
assertThat ( row ). containsEntry ( "FIRST_NAME" , "Dino" );
assertThat ( row ). containsEntry ( "STREET_ADDRESS" , "123 Main Street" );
row = rows . get ( 9 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Smith" );
assertThat ( row ). containsEntry ( "FIRST_NAME" , "Suzy" );
assertThat ( row ). containsEntry ( "STREET_ADDRESS" , "123 Main Street" );
}
}
@ Test
void testUnionAfterGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( stringConstant ( "Gender" ), gender . as ( "value" ), count (). as ( "count" ))
. from ( person )
. groupBy ( gender )
. union ()
. select ( stringConstant ( "Last Name" ), lastName . as ( "value" ), count (). as ( "count" ))
. from ( person )
. groupBy ( lastName )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select 'Gender', gender as value, count(*) as count from Person group by gender" +
" union" +
" select 'Last Name', last_name as value, count(*) as count from Person group by last_name" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 4 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "C1" , "Gender " );
assertThat ( row ). containsEntry ( "VALUE" , "Female" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "C1" , "Gender " );
assertThat ( row ). containsEntry ( "VALUE" , "Male" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
row = rows . get ( 2 );
assertThat ( row ). containsEntry ( "C1" , "Last Name" );
assertThat ( row ). containsEntry ( "VALUE" , "Flintstone" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
row = rows . get ( 3 );
assertThat ( row ). containsEntry ( "C1" , "Last Name" );
assertThat ( row ). containsEntry ( "VALUE" , "Rubble" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
}
}
@ Test
void testUnionAllAfterGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( stringConstant ( "Gender" ), gender . as ( "value" ), count (). as ( "count" ))
. from ( person )
. groupBy ( gender )
. unionAll ()
. select ( stringConstant ( "Last Name" ), lastName . as ( "value" ), count (). as ( "count" ))
. from ( person )
. groupBy ( lastName )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select 'Gender', gender as value, count(*) as count from Person group by gender" +
" union all" +
" select 'Last Name', last_name as value, count(*) as count from Person group by last_name" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 4 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "C1" , "Gender " );
assertThat ( row ). containsEntry ( "VALUE" , "Male" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "C1" , "Gender " );
assertThat ( row ). containsEntry ( "VALUE" , "Female" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
row = rows . get ( 2 );
assertThat ( row ). containsEntry ( "C1" , "Last Name" );
assertThat ( row ). containsEntry ( "VALUE" , "Flintstone" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
row = rows . get ( 3 );
assertThat ( row ). containsEntry ( "C1" , "Last Name" );
assertThat ( row ). containsEntry ( "VALUE" , "Rubble" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
}
}
@ Test
void testBasicGroupByOrderByWithAggregateAlias () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( gender , count (). as ( "count" ))
. from ( person )
. groupBy ( gender )
. orderBy ( gender )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select gender, count(*) as count from Person group by gender order by gender" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 2 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "GENDER" , "Female" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "GENDER" , "Male" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
}
}
@ Test
void testBasicGroupByOrderByWithCalculatedColumnAndTableAlias () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( substring ( gender , 1 , 1 ). as ( "ShortGender" ), avg ( age ). as ( "AverageAge" ))
. from ( person , "a" )
. groupBy ( substring ( gender , 1 , 1 ))
. orderBy ( sortColumn ( "ShortGender" ). descending ())
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select substring(a.gender, 1, 1) as ShortGender, avg(a.age) as AverageAge from Person a group by substring(a.gender, 1, 1) order by ShortGender DESC" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 2 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "SHORTGENDER" , "M" );
assertThat ( row ). containsEntry ( "AVERAGEAGE" , 25 );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "SHORTGENDER" , "F" );
assertThat ( row ). containsEntry ( "AVERAGEAGE" , 27 );
}
}
@ Test
void testGroupByAfterWhere () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count (). as ( "count" ))
. from ( person , "a" )
. where ( gender , isEqualTo ( "Male" ))
. groupBy ( lastName )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select a.last_name, count(*) as count from Person a where a.gender = #{parameters.p1,jdbcType=VARCHAR} group by a.last_name" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 2 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
assertThat ( row ). containsEntry ( "COUNT" , 2L );
row = rows . get ( 1 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
assertThat ( row ). containsEntry ( "COUNT" , 2L );
}
}
@ Test
void testLimitAndOffsetAfterGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count (). as ( "count" ))
. from ( person )
. groupBy ( lastName )
. limit ( 1 )
. offset ( 1 )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) as count from Person group by last_name limit #{parameters.p1} offset #{parameters.p2}" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
}
}
@ Test
void testLimitOnlyAfterGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count (). as ( "count" ))
. from ( person )
. groupBy ( lastName )
. limit ( 1 )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) as count from Person group by last_name limit #{parameters.p1}" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
}
}
@ Test
void testOffsetOnlyAfterGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count (). as ( "count" ))
. from ( person )
. groupBy ( lastName )
. offset ( 1 )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) as count from Person group by last_name offset #{parameters.p1} rows" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
}
}
@ Test
void testOffsetAndFetchFirstAfterGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count (). as ( "count" ))
. from ( person )
. groupBy ( lastName )
. offset ( 1 )
. fetchFirst ( 1 ). rowsOnly ()
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) as count from Person group by last_name offset #{parameters.p1} rows fetch first #{parameters.p2} rows only" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
assertThat ( row ). containsEntry ( "COUNT" , 3L );
}
}
@ Test
void testFetchFirstOnlyAfterGroupBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count (). as ( "count" ))
. from ( person )
. groupBy ( lastName )
. fetchFirst ( 1 ). rowsOnly ()
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) as count from Person group by last_name fetch first #{parameters.p1} rows only" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
assertThat ( row ). containsEntry ( "COUNT" , 4L );
}
}
@ Test
void testCountDistinct () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( countDistinct ( lastName ). as ( "count" ))
. from ( person )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select count(distinct last_name) as count from Person" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "COUNT" , 2L );
}
}
@ Test
void testHaving () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. having ( count (), isEqualTo ( 3L ))
. and ( lastName , isEqualTo ( "Rubble" ))
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) from Person group by last_name " +
"having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR}" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
}
}
@ Test
void testHavingAndOrderBy () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. having ( count (), isEqualTo ( 3L ))
. orderBy ( lastName )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) from Person group by last_name having count(*) = #{parameters.p1} order by last_name" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
}
}
@ Test
void testHavingWithGroup () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. having ( group ( count (), isEqualTo ( 3L ), and ( lastName , isEqualTo ( "Rubble" ))))
. limit ( 1 )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) from Person group by last_name " +
"having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR} " +
"limit #{parameters.p3}" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
}
}
@ Test
void testHavingWithUnion () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. having ( group ( count (), isEqualTo ( 3L ), and ( lastName , isEqualTo ( "Rubble" ))))
. union ()
. select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. having ( group ( count (), isGreaterThan ( 1L ), and ( lastName , isEqualTo ( "Flintstone" ))))
. fetchFirst ( 5 ). rowsOnly ()
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) from Person group by last_name " +
"having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR} " +
"union select last_name, count(*) from Person group by last_name " +
"having count(*) > #{parameters.p3} and last_name = #{parameters.p4,jdbcType=VARCHAR} " +
"fetch first #{parameters.p5} rows only" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 2 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
}
}
@ Test
void testHavingWithUnionAll () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. having ( group ( count (), isEqualTo ( 3L ), and ( lastName , isEqualTo ( "Rubble" ))))
. unionAll ()
. select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. having ( group ( count (), isGreaterThan ( 1L ), and ( lastName , isEqualTo ( "Flintstone" ))))
. offset ( 1 )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) from Person group by last_name " +
"having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR} " +
"union all select last_name, count(*) from Person group by last_name " +
"having count(*) > #{parameters.p3} and last_name = #{parameters.p4,jdbcType=VARCHAR} " +
"offset #{parameters.p5} rows" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Flintstone" );
}
}
@ Test
void testStandaloneHaving () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
SelectStatementProvider selectStatement = select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. applyHaving ( commonHaving )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) from Person group by last_name " +
"having count(*) = #{parameters.p1}" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
}
}
@ Test
void testComposedHaving () {
try ( SqlSession session = sqlSessionFactory . openSession ()) {
CommonSelectMapper mapper = session . getMapper ( CommonSelectMapper . class );
HavingApplier composedHaving = commonHaving . andThen ( d -> d . and ( lastName , isEqualTo ( "Rubble" )));
SelectStatementProvider selectStatement = select ( lastName , count ())
. from ( person )
. groupBy ( lastName )
. applyHaving ( composedHaving )
. build ()
. render ( RenderingStrategies . MYBATIS3 );
String expected = "select last_name, count(*) from Person group by last_name " +
"having count(*) = #{parameters.p1} and last_name = #{parameters.p2,jdbcType=VARCHAR}" ;
assertThat ( selectStatement . getSelectStatement ()). isEqualTo ( expected );
List < Map < String , Object >> rows = mapper . selectManyMappedRows ( selectStatement );
assertThat ( rows ). hasSize ( 1 );
Map < String , Object > row = rows . get ( 0 );
assertThat ( row ). containsEntry ( "LAST_NAME" , "Rubble" );
}
}
private final HavingApplier commonHaving = having ( count (), isEqualTo ( 3L )). toHavingApplier ();
}
 
推荐文章
失恋的牙膏  ·  数据库：查询生成器 | Laravel中文文档
1 周前
酒量大的煎鸡蛋  ·  SQL重复记录查询的几种方法(转) - HappyEDay
3 天前
憨厚的硬盘  ·  Optimizer Hints | TiDB 文档中心
3 天前
伤情的遥控器  ·  mybatis-plus连接SQL Server2012分页查询异常 - 清清飞扬
3 天前
道上混的紫菜汤  ·  查询服务中的SQL语法 | Adobe Experience Platform
3 天前
调皮的草稿纸  ·  智能家居控制系统-智能家居设备-智能家居解决方案-智能家居控制系统品牌-罗格朗官网
5 月前
斯文的烤面包  ·  【预告】“商谈新文化”之正午阳光董事长侯鸿亮_视频频道_财新网
5 月前
豁达的肉夹馍  ·  Armor Games：提供大量線上小遊戲的網站 – G. T. Wang
5 月前
痴情的雪糕  ·  暗部蓝奏云
6 月前
气宇轩昂的钥匙  ·  乐安县人民政府 区域概况 乐安概况
6 月前
Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
link管理 - 链接快照平台