1。首先定义一个XML Sql 组装解析类: public class SQLentity { private String select; private String update; private Map updateColNm; private String body; private String orderBy; private String count; private Map clauseColNm; private Map updateSql; private Map createSql; public String getSelect() { return select; } public void setSelect(String select) { this.select = select; } /** * @return the update */ public String getUpdate() { return update; } /** * @param update the update to set */ public void setUpdate(String update) { this.update = update; } /** * @return the updateColNm */ public Map getUpdateColNm() { return updateColNm; } /** * @param updateColNm the updateColNm to set */ public void setUpdateColNm(Map updateColNm) { this.updateColNm = updateColNm; } public String getBody() { return body; } public void setBody(String body) { this.body = body; } /** * @return the orderBy */ public String getOrderBy() { return orderBy; } /** * @param orderBy the orderBy to set */ public void setOrderBy(String orderBy) { this.orderBy = orderBy; } public String getSQL() { String sql = this.select + ' ' + this.body; if (this.orderBy != null) { sql = sql + ' ' + this.orderBy; } return sql; } /** * @return the count */ public String getCount() { return count; } /** * @param count the count to set */ public void setCount(String count) { this.count = count; } public Map getClauseColNm() { return clauseColNm; } public void setClauseColNm(Map clauseColNm) { this.clauseColNm = clauseColNm; } public Map getUpdateSql() { return updateSql; } public void setUpdateSql(Map value) { this.updateSql = value; } public Map getCreateSql() { return createSql; } public void setCreateSql(Map value) { this.createSql = value; } /** * @param clause * @return sql with parameters to set in runtime */ public String getSQL(Map clause) { StringBuffer buf = new StringBuffer(); buf.append(this.select).append(" ").append(this.body); if (clause != null && clause.size() > 0) { String[] keys = (String[]) clauseColNm.keySet().toArray(new String[0]); for (int i = 0; i < keys.length; i++) { String value = (String) clause.get(keys[i]); if (null != value && value.trim().length() != 0) { buf.append(" AND ").append(this.clauseColNm.get(keys[i])).append("=?"); } } } if (this.orderBy != null) { buf.append(" ").append(this.orderBy); } return buf.toString(); } /** * @param clause * @return sql without parameters, the parameters have been set in this * method. */ public String getSQLWithoutParam(Map clause) { StringBuffer buf = new StringBuffer(); buf.append(this.select).append(" ").append(this.body); if (clause != null && clause.size() > 0) { String[] keys = (String[]) clauseColNm.keySet().toArray(new String[0]); for (int i = 0; i < keys.length; i++) { String value = (String) clause.get(keys[i]); if (null != value && value.trim().length() != 0) { buf.append(" AND ").append(this.clauseColNm.get(keys[i])).append("='").append(value).append("'"); } } } if (this.orderBy != null) { buf.append(" ").append(this.orderBy); } return buf.toString(); } /** * TODO:This method only support two type of the value:String and Integer. * * @param clause * @return sql without parameters, the parameters have been set in this * method. */ public String getSQLOfFuzzyWithoutParam(Map clause) { StringBuffer buf = new StringBuffer(); buf.append(this.select).append(" "); buildBodyForFuzzy(clause, buf); if (this.orderBy != null) { buf.append(" ").append(this.orderBy); } return buf.toString(); } /** * @param clause * @return sql which can query data by the conditions */ public String getSQLOfFuzzyForPage(Map clause) { StringBuffer buf = new StringBuffer(); buf.append("select * from ("); Object orderByO = clause.get(NMRMappingConst.PAGE_ORDER_BY); Object orderO = clause.get(NMRMappingConst.PAGE_ORDER); if (MWStringUtils.empty(orderByO) || MWStringUtils.empty(orderO)) { buildBodyForPageOrder(clause, buf, this.orderBy); } else { String orderBy = (String) orderByO; String order = (String) orderO; buildBodyForPageOrder(clause, buf, " order by " + orderBy + " " + order); } int object = ((Integer) clause.get(NMRMappingConst.PAGE_COUNT_START)).intValue(); if (object != 0) { object++; } buf.append(") AS TMPRST1 where rowid between ").append(object).append(" and ").append( clause.get(NMRMappingConst.PAGE_COUNT_END)); return buf.toString(); } /** * @param clause * @param buf */ private void buildBodyForPageOrder(Map clause, StringBuffer buf, String orderBy) { buf.append("select TMPRST.*"); buf.append(", row_number()over("); if (orderBy != null) { buf.append(" ").append(orderBy); } buf.append(") as rowid from ("); buf.append(this.select).append(" "); buildBodyForFuzzy(clause, buf); buf.append(") AS TMPRST"); } /** * @param clause * @return sql which can query the total number of the data */ public String getSQLOfFuzzyForTotalNum(Map clause) { StringBuffer buf = new StringBuffer(); buf.append("select count"); if (this.count == null) { buf.append("(1) "); } else { buf.append("(").append(this.count).append(") "); } buildBodyForFuzzy(clause, buf); return buf.toString(); } /** * @param clause * @param buf */ private void buildBodyForFuzzy(Map clause, StringBuffer buf) { buf.append(this.body); if (clause != null && clause.size() > 0) { String[] keys = clauseColNm == null ? new String[] {} : (String[]) clauseColNm.keySet().toArray( new String[0]); for (int i = 0; i < keys.length; i++) { String columnKey = keys[i]; Object object = clause.get(columnKey); if (!MWStringUtils.empty(object)) { String column = ((String) this.clauseColNm.get(columnKey)).trim(); if (NMRMappingConst.LST_MOD_TS_FROM.equals(columnKey)) { buf.append(" AND ").append(column).append(">='").append( MWStringUtils.toYYYYMMDDString(String.valueOf(object))).append("'"); } else if (NMRMappingConst.LST_MOD_TS_TO.equals(columnKey)) { buf.append(" AND ").append(column).append("<='").append( MWStringUtils.toYYYYMMDDString(String.valueOf(object))).append("'"); } else if (NMRMappingConst.OBJECT_TYPE.equals(columnKey)) { String[] acionIDs = ((String) object).split(","); if (acionIDs.length > 0) { buf.append(" AND ("); for (int j = 0; j < acionIDs.length; j++) { if (j != 0) { buf.append(" or "); } buf.append(column).append("=").append(acionIDs[j]); } buf.append(") "); } } else if (NMRMappingConst.STATUS.equals(columnKey)) { if (NMRMappingConst.STATUS_INACTIVE.equalsIgnoreCase(object.toString())) { buf.append(" AND ").append(column).append("='I'"); } else { buf.append(" AND ").append(column).append("<>'I'"); } } else if(NMRMappingConst.REQUEST_TABLE_SW_STATUS.equals(columnKey)){ if (NMRMappingConst.STATUS_ACTIVE.equalsIgnoreCase(object.toString())) { buf.append(" AND ").append(column).append("='A'"); } else if (NMRMappingConst.STATUS_CLOSED.equalsIgnoreCase(object.toString())) { buf.append(" AND ").append(column).append("='C'"); } } else if (object instanceof String) { // TODO:make fuzzy // query when it is // a String. String value = ((String) object).trim(); if (value.indexOf("'") >= 0) { value = value.replaceAll("'", "''"); } if (value.trim().length() != 0) { if (column.indexOf("UPPER") >= 0) { buf.append(" AND ").append(column).append(" like '%").append(value.toUpperCase()) .append("%'"); } else { buf.append(" AND ").append(column).append(" like '%").append(value).append("%'"); } } } else if (object instanceof Integer) { Integer value = (Integer) object; // history ID can not be zero if (NMRMappingConst.HISTORY_ID.equals(column) && value.intValue() == 0) { continue; } buf.append(" AND ").append(column).append("=").append(value); } } } } } public String getSQL(int in_cnt) { StringBuffer buf = new StringBuffer(); buf.append(this.select).append(" ").append(this.body).append(" ("); for (int i = 0; i < in_cnt; ++i) { if (i != in_cnt - 1) buf.append("?,"); else buf.append("?)"); } if (this.orderBy != null) { buf.append(" ").append(this.orderBy); } return buf.toString(); } public String getUpdateSQL(Map map) { StringBuffer buf = new StringBuffer(); buf.append(this.update); if (!(buf.indexOf("set") >= 0 || buf.indexOf("SET") >= 0)) { buf.append(" SET "); } if (map != null && map.size() > 0) { String[] keys = (String[]) updateColNm.keySet().toArray(new String[0]); for (int i = 0; i < keys.length; i++) { Object object = map.get(keys[i]); if (object != null) { Object column = this.updateColNm.get(keys[i]); if (object instanceof String) { buf.append(", ").append(column).append("='").append(object).append("' "); } else { buf.append(", ").append(column).append("=").append(object).append(" "); } } } if (clauseColNm != null && clauseColNm.size() > 0) { keys = (String[]) clauseColNm.keySet().toArray(new String[0]); buf.append(" WHERE 1=1 "); for (int i = 0; i < keys.length; i++) { Object object = map.get(keys[i]); if (object != null) { Object column = this.clauseColNm.get(keys[i]); buf.append(" AND "); if (object instanceof String) { buf.append(column).append("='").append(object).append("' "); } else { buf.append(column).append("=").append(object).append(" "); } } } } } return buf.toString(); } } 2。Spring 配置文件的bean: <bean id="SearchClientMasterDtls_Sql" class="com.nomura.aeas.nmrmapping.dao.SQLentity"> <property name="select" value="SELECT DISTINCT C.NMR_ENT_CD, C.NMR_CLIENT_ID, C.NMR_CLIENT_NM,X.MACCT_MSTR_ID "></property> <property name="body" value=" FROM ACCOUNT.NMR_CLIENT C, ACCOUNT.NMR_CLIENT_MSTR_XREF X WHERE C.NMR_CLIENT_ID=X.NMR_CLIENT_ID AND C.NMR_ENT_CD=X.NMR_ENT_CD AND X.STATUS!='I' "> </property> <property name="clauseColNm"> <map> <entry key="NMR_ENT_CD"> <value>UPPER(C.NMR_ENT_CD)</value> </entry> <entry key="MACCT_MSTR_ID"> <value>X.MACCT_MSTR_ID</value> </entry> </map> </property> </bean> 3.调用Dao层: public String getTest(String clientID, String entity) { SQLentity sql = (SQLentity) getBean("Search_Sql"); Map requestMap = new HashMap(); requestMap.put(NMRMappingConst.CLIENT_ID, clientID); requestMap.put(NMRMappingConst.ENTITY, entity); List list = dao.query(sql.getSQL(requestMap), new String[]{clientID.trim().toUpperCase(), entity.trim()}); String masterIDOfClient = null; if (list != null && list.size() > 0) { Map res = (Map)list.get(0); masterIDOfClient = MWStringUtils.trimObject(res.get(NMRMappingConst.MASTER_ID)); } return masterIDOfClient; }