StringBuffer封装sql查询模板

public abstract class FolderSQLGenerater {

	/**
	 * 查询总数时，拼接外层调用
	 * @param sql
	 * @return
	 */
	public static String wrapTotalCountSql(String sql) {
		return "select count(*) from (" + sql + ")";
		}

	/**
	 * 查询分页专用方法
	 * @param sql
	 * @param firstElement
	 * @param lastElement
	 * @return
	 */
	public static String wrapPaginatedSql(String sql, int firstElement, int lastElement) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from (");
		sb.append("select query.*, rownum rnum from (");
		sb.append(sql);
		sb.append(") query where rownum <= " + (lastElement + firstElement));
		sb.append(") where rnum >" + firstElement + " order by rnum");
		return new String(sb);
	}

	/**
	 * 查询总数 isCount= true
	 * @param sortField
	 * @return
	 */
	public static String makeSelectCase(String sortField,boolean isCount) {
		if(!isCount){//查询总数
			return "select distinct pi.instanceId FROM ";
		}
		return "select distinct pi.instanceId," + sortField + " FROM ";
	}

	//拼接表
	public static String makeSelectTable(Set<String> tables){
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("pi,");
		stringBuffer.append("wi");
		for(String table:tables){
			stringBuffer.append(","+table + " ");
		}
		return stringBuffer.toString();
	}

	/**
	 * 查询总数 isCount= true
	 * @param
	 * @param tableRelations
	 * @param selectSqls
	 * @param sort
	 * @return
	 */
	public static String makeSelectTerm(String instanceState,Set<String> tableRelations,List<String> selectSqls,String sort,boolean isCount,String loginID) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append(" where pi.instanceid = wi.instanceid ");
		for(String tableRelation:tableRelations){
			stringBuffer.append(" and "+ tableRelation + " ");
		}

		if (instanceState.equals(ProcessInstanceState.TODO.getValue())) {
			
		} else if(instanceState.equals(ProcessInstanceState.DONE.getValue())){
			

		} else if(instanceState.equals(ProcessInstanceState.FINISH.getValue())){
			
		}else if (instanceState.equals(ProcessInstanceState.DRAFT.getValue())){
			

		}else if (instanceState.equals(ProcessInstanceState.DELETE.getValue())){
			stringBuffer.append( "and pi.deleted  = '1' ");

		}else if(instanceState.equals("findAlltodo")){
			
		}

		for(String selectSql:selectSqls){
			stringBuffer.append(" and "+ selectSql + " ");
		}
		if(!isCount) {//查询总数
			stringBuffer.append(" order by " + sort);
		}

		return stringBuffer.toString();
	}
	
}

然后调用的时候，类似这样：

String valueSql = ""; //拼接查询的值
Set<String> joinTables  = new HashSet<String>(); //拼接表
Set<String> tableRelations  = new HashSet<String>(); //拼接表之间关联条件 类似这种：  and pi.instanceid = wi.instanceid
List<String> conditions = new ArrayList<>(); //对应makeSelectTerm方法里面的selectSqls参数,拼接查询条件  and ... = ...

if (processid.equals(ProcessId.QB_PROCESS)) {
     joinTables.add("d_gw_q q");
     tableRelations.add("q.qbuid = pi.businesskey");
     valueSql += "q.wenhao      ," +
             "q.tiaoma      ," +
             "q.serialnumber," +
             "q.ypld        ," +
             "q.cbdeptname ";
     //拼接
     if(StringUtils.isNotBlank(serialNumber)){
         conditions.add("q.serialnumber like '%"+serialNumber+"%' "); //流水号
     }

}

最后：

String getInstanceIdSql = FolderSQLGenerater.makeSelectCase("pi.created", true)
                + FolderSQLGenerater.makeSelectTable(joinTables)
                + FolderSQLGenerater.makeSelectTerm(instanceState, tableRelations, conditions, "pi.created desc", false,loginID);
List results = this.getObjectsBySql(getInstanceIdSql);