本文介绍如何利用QueryObject模式简化JPQL查询过程，包括参数化查询、分页、排序、连接等常见操作，以及如何通过单元测试验证查询逻辑的正确性。
Query Object
编程语言可以包含sql语句，但许多开发者对此并不太熟悉。而且，你需要了解数据库设计方案以便形成查询。可以通过创建特殊的，隐藏的SQL内部参数化方法来避免这一点。但是这样难以构造更多的查询。而且，如果数据库设计方案改变，就会需要复制到SQL语句。
查询对象是一个解析器（interpreter）【Gang of Four】，也就是说，它是由多个对象组成的结构，该结构可以自身转化为一个sql查询。你可以通过引用类和域而不是表和列来创建这个查询。采用这种方式，那些编写查询的人就能够设计独立数据库方案的查询，并且数据库方案的变化也可以被封装到局部
《企业应用架构模式》 - Martin Fower
看到上面，我第一时间是想到Hibernate中的Criteria查询，他是Query Object很直观和跟经典的实现。但是就我个人使用来说，Criteria查询比较少，而且要记住几个英文很长的类，写出来巨繁琐（静态导入会好一些）。目前做项目都基于JPA的接口来开发。（但大多复杂查询还是使用原生sql查出来，然后自己转换对象！也有干脆使用mybatis）。就平时开发而言，简单快速好用的封装Query Object，然后配合链式编程（该实践中，良好的测试可以避免火车事故），使用起来是非常方便的。
查询对象可以很好的简单的完美的:^)跟 查询通道（QueryChannel）或者直接活动记录（ActiveRecord）+资源库（Repository）这些模式结合，形成简单好用的查询接口。
以下是我参考以前一些前辈，跟自己进行改进的一个Query Object实践，基本JPQL。 最终于我Repository 或DAO的查询接口可能是类似于这样的风格。
<T> T findEntity(Query q);
PageVo findPage(Query q);
List<T> findEntites(Query q);
Query.java
package com.xz.marksix.repository;
import java.util.ArrayList;
import java.util.List;
import com.xz.marksix.infrastructure.AppContants;
/**
* <pre>
*
* JPA 查询对象
*
* @author XzhiF
* Sep 25, 2013
* </pre>
*/
public class Query
{
private static final String SPACE = " ";
private static final String SELECT = "SELECT";
private static final String COUNT = "COUNT";
private static final String FROM = "FROM";
private static final String WHERE = "WHERE";
private static final String AND = "AND";
private static final String OR = "OR";
private static final String ORDER_BY = "ORDER BY";
private static final String ASC = "ASC";
private static final String DESC = "DESC";
private static final String JOIN = "JOIN";
private static final String INNER_JOIN = "INNER JOIN";
private static final String LEFT_JOIN = "LEFT JOIN";
private static final String RIGHT_JOIN = "RIGHT JOIN";
private static final String LEFT_OUTER_JOIN = "LEFT OUTER JOIN";
private static final String RIGHT_OUT_JOIN = "RIGHT OUTER JOIN";
private static final String LEFT_JOIN_FETCH = "LEFT JOIN FETCH";
private static final String RIGHT_JOIN_FETCH = "RIGHT JOIN FETCH";
private static final boolean ADD_PARAM = true;
private static final boolean NOT_ADD_PARAM = false;
private Class<?> entityClass;
private String alias;
private Integer pageNum;
private Integer pageSize;
private List<Object> paramters = new ArrayList<>();
private StringBuilder selectClause = new StringBuilder();
private StringBuilder fromClause = new StringBuilder();
private StringBuilder joinClause = new StringBuilder();
private StringBuilder whereClause = new StringBuilder();
private StringBuilder orderByClause = new StringBuilder();
private boolean fetchAllRecords = true;
public Query( Class<?> entityClass, String alias )
{
this.entityClass = entityClass;
this.alias = alias;
/**
* FROM abc.Def alais
*/
fromClause//
.append( SPACE )//
.append( FROM )//
.append( SPACE )//
.append( entityClass.getName() )//
.append( SPACE )//
.append( this.alias );
}
/**
* evaluate 生成最后的JPQL语句
*/
public String evaluate()
{
StringBuilder result = new StringBuilder();
/**
* 以下构造要保持顺序，若重构时请注意
*/
evlauteAppendSelectClause( result );
evlauteAppendFromClause( result );
evlauteAppendJoinClause( result );
evlauteAppendWhereClause( result );
evlauteAppendOrderByClause( result );
return result.toString().trim();
}
/**
* evaluateCount 生成最后的查询总数的JPQL语句
*/
public String evaluateCount()
{
StringBuilder result = new StringBuilder();
/**
* 以下构造要保持顺序，若重构时请注意
*/
evlauteAppendSelectCountClause( result );
evlauteAppendFromClause( result );
evlauteAppendWhereClause( result );
return result.toString().trim();
}
private void evlauteAppendSelectClause( StringBuilder result )
{
if ( isNotBlank( selectClause ) )
{
result.append( selectClause );
}
}
private void evlauteAppendSelectCountClause( StringBuilder result )
{
result.append( SELECT )//
.append( SPACE )//
.append( COUNT )//
.append( "(" )//
.append( alias )//
.append( ")" );
}
private void evlauteAppendFromClause( StringBuilder result )
{
result.append( fromClause );
}
private void evlauteAppendJoinClause( StringBuilder result )
{
if ( isNotBlank( joinClause ) )
{
result.append( joinClause );
}
}
private void evlauteAppendWhereClause( StringBuilder result )
{
if ( isNotBlank( whereClause ) )
{
result.append( whereClause );
}
}
private void evlauteAppendOrderByClause( StringBuilder result )
{
if ( isNotBlank( orderByClause ) )
{
result.append( orderByClause );
}
}
/**
* <pre>
* 例如返回一个实体
* addSelectClause("e") "e" 跟new Query() 第二个别名参数要一至。
*
* 例如返回一个查询的VO对象
* addSelectClause("new VO(e.id,e.name)") "e" 跟new Query() 第二个别名参数要一至。
*
* 注意些方法只能调用一次，同一实例调用多次会抛出RepositoryException异常
* </pre>
*
* @param expression JPQL片段
* @return Query
*/
public Query addSelectClause( String expression )
{
checkSelectClauseIsBinded();
selectClause.append( SELECT ).append( SPACE );
if ( isBlank( expression ) )
{
selectClause.append( this.alias );
}
else
{
selectClause.append( expression );
}
return this;
}
private void checkSelectClauseIsBinded()
{
if ( isNotBlank( selectClause ) )
{
throw new RepositoryException( "selectClause已经绑定过不能再定绑定." );
}
}
/**
* <pre>
* 结果将添加如 WHERE e.id=? AND e.name=? 的 JPQL片段
* 例子:
* addWhereAndClause( id!=null, "e.id=?", id )
*
* </pre>
*
* @param canAdd 条件，如果成立 ，JPQL片段将被添加
* @param expression JPQL片段
* @param value 设置的值
* @return Query
*/
public Query addWhereAndClause( boolean canAdd, String expression, Object value )
{
if ( canAdd )
{
return addWhereClause( AND, expression, value, ADD_PARAM );
}
return this;
}
public Query addWhereAndClause( boolean canAdd, String expression )
{
if ( canAdd )
{
return addWhereClause( AND, expression, null, NOT_ADD_PARAM );
}
return this;
}
/**
* 直接添加JPQL片段
*
* @see Query#addWhereOrClause(boolean,
* String, Object)
*/
public Query addWhereAndClause( String expression, Object value )
{
return addWhereClause( AND, expression, value, ADD_PARAM );
}
/**
* 直接添加JPQL片段不带有占位参数
*
* @see Query#addWhereOrClause(boolean,
* String, Object)
*/
public Query addWhereAndClause( String expression )
{
return addWhereClause( AND, expression, null, NOT_ADD_PARAM );
}
/**
* <pre>
* 结果将添加如 WHERE e.id=? OR e.name=? 的 JPQL片段
* 例子:
* addWhereAndClause( id!=null, "e.id=?", id )
*
* </pre>
*
* @param canAdd 条件，如果成立 ，JPQL片段将被添加
* @param expression JPQL片段
* @param value 设置的值
* @return Query
*/
public Query addWhereOrClause( boolean canAdd, String expression, Object value )
{
if ( canAdd )
{
return addWhereClause( OR, expression, value, ADD_PARAM );
}
return this;
}
public Query addWhereOrClause( boolean canAdd, String expression )
{
if ( canAdd )
{
return addWhereClause( OR, expression, null, NOT_ADD_PARAM );
}
return this;
}
/**
* 直接添加JPQL片段
*
* @see Query#addWhereOrClause(boolean,
* String, Object)
*/
public Query addWhereOrClause( String expression, Object value )
{
return addWhereClause( OR, expression, value, ADD_PARAM );
}
/**
* 直接添加JPQL片段， 不添加占位符号查询参数
*
* @see Query#addWhereOrClause(boolean,
* String, Object)
*/
public Query addWhereOrClause( String expression )
{
return addWhereClause( OR, expression, null, NOT_ADD_PARAM );
}
/**
* addWhereClause
*/
private Query addWhereClause( String operation, String expression, Object value, boolean addParam )
{
prepareWhereClause( operation );
whereClause.append( SPACE )//
.append( expression.trim() );//
if ( addParam )
{
paramters.add( value );
}
return this;
}
/**
* <pre>
*
* 结果将添加如 ORDER BY e.id ASC, e.name ASC 的 JPQL片段
* 例子:
* addOrderByAscClause( true, "e.id" )
* addOrderByAscClause( true, "e.name" )
* </pre>
*
* @param canAdd 条件，如果成立 ，JPQL片段将被添加
* @param expression JPQL片段
* @return {@link Query}
*/
public Query addOrderByAscClause( boolean canAdd, String expression )
{
if ( canAdd )
{
return addOrderByAscClause( expression );
}
return this;
}
/**
* 直接添加JPQL语句
*
* @see Query#addOrderByAscClause(boolean,
* String)
*/
public Query addOrderByAscClause( String expression )
{
return addOrderByClause( ASC, expression );
}
/**
* <pre>
*
* 结果将添加如 ORDER BY e.id DESC, e.name DESC 的 JPQL片段
* 例子:
* addOrderByAscClause( true, "e.id" )
* addOrderByAscClause( true, "e.name" )
* </pre>
*
* @param canAdd 条件，如果成立 ，JPQL片段将被添加
* @param expression JPQL片段
* @return {@link Query}
*/
public Query addOrderByDescClause( boolean canAdd, String expression )
{
if ( canAdd )
{
return addOrderByClause( DESC, expression );
}
return this;
}
/**
* 直接添加JPQL语句
*
* @see Query#addOrderByDescClause(boolean,
* String)
*/
public Query addOrderByDescClause( String expression )
{
return addOrderByClause( DESC, expression );
}
private Query addOrderByClause( String opration, String expression )
{
prepareOrderByClause();
orderByClause.append( SPACE ).append( expression )//
.append( SPACE )//
.append( opration );
return this;
}
/**
* <pre>
*
* 添加JOIN语句，例如:
* .addJoinClause("d.sub s") s为join的对象别名
*
* </pre>
*/
public Query addJoinClause( String expression )
{
return addCaseedJoinClause( JOIN, expression );
}
/**
* <pre>
*
* 添加INNER JOIN语句，例如:
* .addInnerJoinClause("d.sub s") s为join的对象别名
*
* </pre>
*/
public Query addInnerJoinClause( String expression )
{
return addCaseedJoinClause( INNER_JOIN, expression );
}
/**
* <pre>
*
* 添加LEFT JOIN语句，例如:
* .addLeftJoinClause("d.sub s") s为join对象后别名
*
* </pre>
*/
public Query addLeftJoinClause( String expression )
{
return addCaseedJoinClause( LEFT_JOIN, expression );
}
/**
* <pre>
*
* 添加RIGHT JOIN语句，例如:
* .addRightJoinClause("d.sub s") s为join的对象别名
*
* </pre>
*/
public Query addRightJoinClause( String expression )
{
return addCaseedJoinClause( RIGHT_JOIN, expression );
}
/**
* <pre>
*
* 添加LEFT OUTER JOIN语句，例如:
* .addLeftOuterJoinClause("d.sub s") s为join的对象别名
*
* </pre>
*/
public Query addLeftOuterJoinClause( String expression )
{
return addCaseedJoinClause( LEFT_OUTER_JOIN, expression );
}
/**
* <pre>
*
* 添加RIGHT OUTER JOIN语句，例如:
* .addRightOuterJoinClause("d.sub s") s为join的对象别名
*
* </pre>
*/
public Query addRightOuterJoinClause( String expression )
{
return addCaseedJoinClause( RIGHT_OUT_JOIN, expression );
}
/**
* <pre>
* 注意fetch也不应该与setMaxResults() 或 setFirstResult()共用，
* 这是因为这些操作是基于结果集的，而在预先抓取集合类时可能包含重复的数据，也就是说无法预先知道精确的行数。
*
* 添加LEFT JOIN FETCH 语句，例如:
* .addLeftJoinFetchClause("d.sub s") s为join的对象别名
*
* </pre>
*/
public Query addLeftJoinFetchClause( String expression )
{
return addCaseedJoinClause( LEFT_JOIN_FETCH, expression );
}
/**
* <pre>
* 注意fetch也不应该与setMaxResults() 或 setFirstResult()共用，
* 这是因为这些操作是基于结果集的，而在预先抓取集合类时可能包含重复的数据，也就是说无法预先知道精确的行数。
*
* 添加LEFT JOIN FETCH 语句，例如:
* .addRightJoinFetchClause("d.sub s") s为join的对象别名
*
* </pre>
*/
public Query addRightJoinFetchClause( String expression )
{
return addCaseedJoinClause( RIGHT_JOIN_FETCH, expression );
}
private Query addCaseedJoinClause( String whatJoin, String expression )
{
joinClause.append( SPACE ).append( whatJoin ).append( SPACE ).append( expression );
return this;
}
private void prepareOrderByClause()
{
if ( isBlank( orderByClause ) )
{
orderByClause.append( SPACE ).append( ORDER_BY );
}
prepareOrderByClauseIfHasCondition();
}
private void prepareOrderByClauseIfHasCondition()
{
if ( orderByClause.length() != new String( SPACE + ORDER_BY ).length() )
{
orderByClause.append( "," );
}
}
private void prepareWhereClause( String operation )
{
if ( isBlank( whereClause ) )
{
whereClause.append( SPACE ).append( WHERE );
}
prepareWhereClauseIfHasCondition( operation );
}
private void prepareWhereClauseIfHasCondition( String operation )
{
if ( whereClause.length() != new String( SPACE + WHERE ).length() )
{
whereClause.append( SPACE ).append( operation );
}
}
private boolean isBlank( CharSequence c )
{
if ( c == null || c.length() == 0 )
{
return true;
}
return false;
}
private boolean isNotBlank( CharSequence c )
{
return !isBlank( c );
}
public Class<?> getEntityClass()
{
return entityClass;
}
public List<Object> getParameters()
{
return paramters;
}
/**
* <pre>
*
* 设置该参数抓取数据库所有数据
* 设置的分页数将无效，改方法不能与setPageNum 跟 setPageSize 同时使用
*
* 设置为true为全部抓取
* 默认为false
*
* </pre>
*
* @param fetch 是否抓取全部数据
* @return
*/
public Query setFetchAllRecords( boolean fetch )
{
this.fetchAllRecords = fetch;
return this;
}
public boolean isFetchAllRecords()
{
return fetchAllRecords;
}
/**
* - page
*/
public Query setPageNum( Integer pageNum )
{
this.fetchAllRecords = false;
this.pageNum = pageNum;
return this;
}
public Query setPageSize( int pageSize )
{
this.fetchAllRecords = false;
this.pageSize = pageSize;
return this;
}
public Integer getPageNum()
{
return ( pageNum == null || pageNum < 1 ) ? 1 : pageNum;
}
public Integer getPageSize()
{
return ( pageSize == null || pageSize < 1 ) ? AppContants.DEFAULT_PAGE_SIZE : pageSize;
}
public Integer getFirstResult()
{
return ( getPageNum() - 1 ) * getPageSize();
}
public Integer getMaxResults()
{
return getPageSize();
}
}对应的单元测试代码：TestQuery.java
package com.xz.marksix.repository;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.fail;
import java.util.List;
import org.junit.Test;
/**
* <pre>
*
* 测试Query生成jpql查询语子
*
* @author XzhiF
* Sep 25, 2013
* </pre>
*/
public class TestQuery
{
private static final String ENTITY_NAME = Object.class.getName();
@Test
public void testMultiEvaluate() throws Exception
{
String expected = "FROM " + ENTITY_NAME + " d WHERE d.name=?";
Query query = new Query( Object.class, "d" )//
.addWhereAndClause( "d.name=?", "abc" );
String result = query.evaluate();
assertEquals( expected, result );
/*
* second evaluate
*/
assertEquals( result, query.evaluate() );
}
@Test
public void testAndOrWhereClause() throws Exception
{
String expected = "FROM " + ENTITY_NAME + " d WHERE d.name=? AND d.id=? OR d.id=? AND d.parent IS NULL";
Query query = new Query( Object.class, "d" )//
.addWhereAndClause( "d.name=?", "abc" )//
.addWhereAndClause( "d.id=?", "abc" )//
.addWhereOrClause( "d.id=?", "aa" )//
.addWhereAndClause( "d.parent IS NULL" );
String result = query.evaluate();
assertEquals( expected, result );
assertEquals( result, query.evaluate() );
assertEquals( new Integer(3), new Integer(query.getParameters().size()) );
}
@Test
public void testOrderByClause() throws Exception
{
String expected = "FROM " + ENTITY_NAME + " d WHERE d.name=? ORDER BY d.id DESC, d.name DESC, d.age ASC";
Query query = new Query( Object.class, "d" )//
.addWhereAndClause( "d.name=?", "abc" )//
.addOrderByDescClause( "d.id" )//
.addOrderByDescClause( "d.name" ).addOrderByAscClause( "d.age" );
String result = query.evaluate();
// System.out.println(result);
assertEquals( expected, result );
}
@Test
public void testJoinsClause() throws Exception
{
String expected = "FROM " + ENTITY_NAME + " d " + //
"JOIN d.sub s" + //
" INNER JOIN d.sub s" + //
" LEFT JOIN d.sub s" + //
" RIGHT JOIN d.sub s" + //
" LEFT OUTER JOIN d.sub s" + //
" RIGHT OUTER JOIN d.sub s" + //
" LEFT JOIN FETCH d.sub s" + //
" RIGHT JOIN FETCH d.sub s";
Query query = new Query( Object.class, "d" )//
.addJoinClause( "d.sub s" )//
.addInnerJoinClause( "d.sub s" )//
.addLeftJoinClause( "d.sub s" )//
.addRightJoinClause( "d.sub s" )//
.addLeftOuterJoinClause( "d.sub s" )//
.addRightOuterJoinClause( "d.sub s" )//
.addLeftJoinFetchClause( "d.sub s" )//
.addRightJoinFetchClause( "d.sub s" );
String result = query.evaluate();
// System.out.println(result);
assertEquals( expected, result );
}
@Test
public void testClauseHasCondition() throws Exception
{
String expected = "FROM " + ENTITY_NAME + " d INNER JOIN d.sub s WHERE d.id=? OR d.name=? ORDER BY o.id ASC, o.name DESC";
Query query = new Query( Object.class, "d" )//
.addWhereAndClause( true, "d.id=?", "1" )//
.addWhereAndClause( false, "d.id=?", "1" )//
.addWhereOrClause( true, "d.name=?", "a" )//
.addWhereOrClause( false, "d.name=?", "b" )//
.addOrderByAscClause( true, "o.id" )//
.addOrderByAscClause( false, "o.id" )//
.addOrderByDescClause( true, "o.name" )//
.addOrderByDescClause( false, "o.name" )//
.addInnerJoinClause( "d.sub s" );
String result = query.evaluate();
// System.out.println(result);
assertEquals( expected, result );
}
@Test
public void testSelectClause() throws Exception
{
String expected = "SELECT d FROM " + ENTITY_NAME + " d";
Query query = new Query( Object.class, "d" )//
.addSelectClause( "d" );//
String result = query.evaluate();
// System.out.println(result);
assertEquals( expected, result );
expected = "SELECT new EntityVO(id,name) FROM " + ENTITY_NAME + " d";
query = new Query( Object.class, "d" )//
.addSelectClause( "new EntityVO(id,name)" );//
result = query.evaluate();
assertEquals( expected, result );
}
@Test
public void testMultiAddSelectClause() throws Exception
{
Query query = new Query( Object.class, "d" )//
.addSelectClause( "d" );
try
{
query.addSelectClause( "a" );
fail( "retry addSelectCluase should throw a exception" );
}
catch ( RepositoryException e )
{
}
}
@Test
public void testWithinParams() throws Exception
{
Query query = new Query( Object.class, "d" )//
.addWhereAndClause( "d.id=?", "1" )//
.addWhereAndClause( "d.id=?", "2" )//
.addWhereOrClause( "d.name=?", "a" )//
.addWhereOrClause( "d.name=?", "b" );//
List<Object> paramters = query.getParameters();
assertEquals( "1", paramters.get( 0 ) );
assertEquals( "2", paramters.get( 1 ) );
assertEquals( "a", paramters.get( 2 ) );
assertEquals( "b", paramters.get( 3 ) );
}
@Test
public void testEvaluateCount() throws Exception
{
String expected = "SELECT COUNT(d) FROM " + ENTITY_NAME + " d WHERE d.id=? AND d.name=?";
Query query = new Query( Object.class, "d" )//
.addWhereAndClause( "d.id=?", "1" )//
.addWhereAndClause( "d.name=?", "a" )//
.addOrderByAscClause( "d.id" )//
.addInnerJoinClause( "d.sub c" )//
.addSelectClause( "d" );
String result = query.evaluateCount();
// System.out.println(result);
assertEquals( expected, result );
}
}
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
