gdhb/core.src/com/hcks/cmfds/commons/util/EntitySQLHelper.java

package com.hcks.cmfds.commons.util;

import java.util.Set;

import com.hcks.cmfds.core.exception.JdbcDaoException;


/**
 * 
 * @Author  刘厦 (liusha.information@gmail.com)
 * @Date  创建时间：2011-5-22,下午06:12:59
 * @Version 0.0.0
 *
 * 类说明: 
 *
 */
public class EntitySQLHelper {
	
	private static String SELECT_BY_PK_SQL_TEMP = "select #FIELDS from #TABLENAME where #PK";

//	private static String SELECT_ALL_SQL_TEMP = "select #FIELDS from #TABLENAME ";

	private static String INSERT_SQL_TEMP = "insert into #TABLENAME (#FIELDS) values (#VALUES)";

//	private static String UPDATE_BY_PK_SQL_TEMP = "update #TABLENAME set #FIELDS where #PK";

	private static String DELETE_BY_PK_SQL_TEMP = "delete from #TABLENAME where #PK";
	
	/**
	 * 自动生成主键查询字符串
	 * @param pkFieldNames
	 * @return
	 */
	private static String generatePrimaryKeyFormatStr(Set<String> pkFieldNames) {
		StringBuilder pks = new StringBuilder();
		for (String pkName : pkFieldNames) {
			if (pks.length() > 0) {
				pks.append(" and ");
			}
			pks.append(pkName).append("=?");
		}
		return pks.toString();
	}
	
	/**
	 * 所有字段
	 * @param fields
	 * @return
	 */
	private static String generateFieldsFormatStr(Set<String> fields) {
		String _returnValue = fields.toString();
		return _returnValue.substring(1, _returnValue.length() - 1);
	}
	
	/**
	 * 获取按主键查询sql语句
	 * @param tableName
	 * @param pkFieldNames
	 * @return
	 */
	public static String generateSelectByPKSql(String tableName, Set<String> pkFieldNames) {
		String sql = SELECT_BY_PK_SQL_TEMP;
		sql = sql.replaceAll("#FIELDS", "*");
		sql = sql.replaceAll("#TABLENAME", tableName);
		sql = sql.replaceAll("#PK", generatePrimaryKeyFormatStr(pkFieldNames));
		return sql;
	}
	
	public static String generateSelectByPKSql(String tableName,Set<String> pkFieldNames,Set<String> fields){
		String sql = SELECT_BY_PK_SQL_TEMP;
		sql = sql.replaceAll("#FIELDS", generateFieldsFormatStr(fields));
		sql = sql.replaceAll("#TABLENAME", tableName);
		sql = sql.replaceAll("#PK", generatePrimaryKeyFormatStr(pkFieldNames));
		return sql;
	}
	
	
	
	private static String replaceX(String sql, String key) {
		int i = sql.indexOf(key);
		int j = sql.lastIndexOf(key);
		boolean lf = false;
		int k = j - 1;
		if (k >= 0) {
			char f = sql.charAt(k);
			if (f == ' ') {
				lf = true;
			}
		}
		if (i == 0) {
			sql = sql.replaceFirst(key + ", ", "");
		} else if (lf) {
			sql = sql.replaceAll(", " + key, "");
		} else if (i > 0) {
			sql = sql.replaceAll(" " + key + ", ", "");
		}
		return sql;
	}
	
	private static String generateFieldsValueFormatStr(Set<String> fields) {
		StringBuffer _returnValue = new StringBuffer();
		for (int i = 0; i < fields.size(); i++) {
			_returnValue.append("?");
			if (i < fields.size() - 1) {
				_returnValue.append(",");
			}
		}
		return _returnValue.toString();
	}
	
	/**
	 * 
	 * @param fields
	 * @param tableName
	 * @param autoKeyFlag
	 * @param pkFieldNames
	 * @return
	 */
	public static String generateInsertSql(Set<String> fields, String tableName, boolean autoKeyFlag, Set<String> pkFieldNames) {
		String sql = INSERT_SQL_TEMP;
		String[] _pks = pkFieldNames.toArray(new String[pkFieldNames.size()]);
		if (autoKeyFlag && pkFieldNames.size() ==1) {
			String _fieldStr = generateFieldsFormatStr(fields);
			if ( _fieldStr.indexOf(_pks[0]) < 0) {
				throw new JdbcDaoException("表不存在此[" + _pks[0] + "]主键字段，请检查字段名称是否正确，区分大小写");
			} else {
				_fieldStr = replaceX(_fieldStr, _pks[0]);
				String _fieldValueStr = generateFieldsFormatStr(fields);
				_fieldValueStr = _fieldValueStr.substring(2);
				sql = sql.replaceAll("#TABLENAME", tableName);
				sql = sql.replaceAll("#FIELDS", _fieldStr);
				sql = sql.replaceAll("#VALUES", _fieldValueStr);
			}
		} else {
			sql = sql.replaceAll("#TABLENAME", tableName);
			sql = sql.replaceAll("#FIELDS", generateFieldsFormatStr(fields));
			sql = sql.replaceAll("#VALUES",generateFieldsValueFormatStr(fields));
		}
		return sql;
	}
	
	/**
	 * 拼装按主键删除sql语句
	 * @param tableName
	 * @param pkFieldNames
	 * @return
	 */
	public static String generateDeleteByPKSql(String tableName, Set<String> pkFieldNames) {
		String sql = DELETE_BY_PK_SQL_TEMP;
		sql = sql.replaceAll("#TABLENAME", tableName);
		StringBuilder _pkStr = new StringBuilder();
		for (String pkName : pkFieldNames) {
			if (_pkStr.length() > 0) {
				_pkStr.append(" and ");
			}
			_pkStr.append(pkName).append("=?");
		}
		sql = sql.replaceAll("#PK", _pkStr.toString());
		return sql;
	}
	
	

}