SQL文組立クラス(JAVA実装)
自分のある事はSQLを書いて工具類を組み立てます
一般条件、日付条件(OracleおよびMysql)をサポートします.
比較演算、likeクエリー、inクエリー、not inクエリーをサポートします.
グループ化、ソートをサポートします.
コードは次のとおりです.
一般条件、日付条件(OracleおよびMysql)をサポートします.
比較演算、likeクエリー、inクエリー、not inクエリーをサポートします.
グループ化、ソートをサポートします.
コードは次のとおりです.
package cn.create.cbl.core.utils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.IdentityHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.lang3.StringUtils;
import cn.create.cbl.core.exception.AppException;
import cn.create.cbl.core.global.Constants;
import cn.create.cbl.core.global.ResultCode;
public class SQLQueryUtil extends BaseUtil {
private static final long serialVersionUID = -4008847394285279230L;
private static String USE_JDBC_DRIVER = Constants.getConfig("jdbc.driverClassName");
/**
* main()
*
* @param args
*/
public static void main(String[] args) {
SQLQueryUtil util = new SQLQueryUtil();
util.addTable("base_user");
util.addTable("base_group");
util.addColumn("user_name");
util.addColumn("user_age");
util.addAndCondition("id", RelationalOperators.EQ, "xxxx", ValueType.STRING);
util.addAndInCondition("user_age", "'x','x','x','x','x'");
util.addOrLikeCondition("xxx", "yyyy");
util.addGroupBy("user_name");
util.addAscOrderBy("create_time");
util.addDescOrderBy("id");
System.out.println(util.toSQL());
}
/**
* :2013-06-20
*/
public static final String FMT_DATE = "yyyy-MM-dd";
/**
* :13:01:01
*/
public static final String FMT_TIME = "HH:mm:ss";
/**
* :2013-06-20 13:01:01
*/
public static final String FMT_DATETIME = "yyyy-MM-dd HH:mm:ss";
/**
*
*
* @author Vity
*
*/
public enum LogicalOperators {
AND("AND"), OR("OR");
private String value;
private LogicalOperators(String value) {
this.value = value;
}
public String toString() {
return new String(this.value);
}
}
/**
*
*
* <p>
* EQ("="), NE("<>"), GT(">"), GE(">="), LT("<"), LE("<=")
* </p>
*
* @author Vity
*
*/
public enum RelationalOperators {
EQ("="), NE("<>"), GT(">"), GE(">="), LT("<"), LE("<=");
private String value;
private RelationalOperators(String value) {
this.value = value;
}
public String toString() {
return this.value;
}
}
/**
*
*
* @author Vity
*
*/
public enum SortMethod {
ASC("ASC"), DESC("DESC");
private String value;
private SortMethod(String value) {
this.value = value;
}
public String toString() {
return this.value;
}
}
/**
*
*
* @author Vity
*
*/
public enum ValueType {
NUMBER, DATE, STRING, TIME, DATETIME
}
/**
*
*/
private final String TABLE_ALIAS_NAME = "TEMP_TABLE_ALIAS_";
/**
*
*/
private int TABLE_ALIAS_INDEX = 0;
/**
* SQL
*/
private boolean isDebug = true;
/**
*
*/
private boolean isDistinct = false;
/**
* key : table & view name | value : alias name
* <p>
* <b> :</b>
* </p>
*/
private Map<String, String> tableMap = new HashMap<String, String>();
/**
* key : column name value : | alias name
*/
private Map<String, String> columnMap = new HashMap<String, String>();
/**
* key : and & or | value
*/
private Map<String, String> conditionMap = new IdentityHashMap<String, String>();
/**
*
*/
private Map<String, String> orderMap = new HashMap<String, String>();
/**
*
*/
private List<String> groupList = new ArrayList<String>();
/**
* sql
*/
private StringBuilder sqlResult = new StringBuilder();
/**
* ,
*
* @param string
* @return
*/
private SQLQueryUtil append(String string) {
sqlResult.append(string).append(" ");
return this;
}
/**
*
*
* @return
*/
private SQLQueryUtil clearEndChar() {
sqlResult.delete(sqlResult.length() - 2, sqlResult.length());
return this;
}
/**
* SQL
*
* @return
*/
public String toSQL() {
if (tableMap.isEmpty()) {
throw new AppException(ResultCode.Failure, " ");
}
this.append("SELECT");
if (isDistinct) {
this.append("DISTINCT");
}
if (columnMap.isEmpty()) {
this.append("*");
} else {
for (String key : columnMap.keySet()) {
this.append(key);
if (!StringUtils.isBlank(columnMap.get(key))) {
this.append("AS").append(columnMap.get(key));
}
this.append(",");
}
this.clearEndChar();
}
this.append("FROM");
for (String key : tableMap.keySet()) {
this.append(key);
if (!StringUtils.isBlank(tableMap.get(key))) {
this.append("AS").append(tableMap.get(key));
}
this.append(",");
}
this.clearEndChar().append(" ");
if (!groupList.isEmpty()) {
this.append("GROUP BY");
for (String field : groupList) {
this.append(field).append(",");
}
this.clearEndChar();
}
if (!conditionMap.isEmpty()) {
if (!groupList.isEmpty()) {
this.append("HAVING");
} else {
this.append("WHERE");
}
this.append("1 = 1");
for (Entry<String, String> entry : conditionMap.entrySet()) {
this.append(entry.getKey()).append(entry.getValue());
}
}
if (!orderMap.isEmpty()) {
this.append("ORDER BY");
for (String key : orderMap.keySet()) {
this.append(key).append(orderMap.get(key)).append(",");
}
this.clearEndChar();
}
if (isDebug) {
logger.trace(sqlResult.toString().replace(" ,", ",").replace(" ", " ").replace(" 1 = 1 AND", "").replace(" 1 = 1 OR", ""));
}
return sqlResult.toString().replace(" ,", ",").replace(" ", " ").replace(" 1 = 1 AND", "").replace(" 1 = 1 OR", "");
}
/**
* Distinct
*
* @param isDistinct
*/
public void setDistinct(boolean isDistinct) {
this.isDistinct = isDistinct;
}
/**
*
*
* @param tableName
* @return
*/
public SQLQueryUtil addTable(String tableName) {
tableMap.put(tableName, TABLE_ALIAS_NAME + TABLE_ALIAS_INDEX++);
return this;
}
/**
* ,
*
* @param tableName
* @param aliasName
* @return
*/
public SQLQueryUtil addTable(String tableName, String aliasName) {
if (aliasName.contains(TABLE_ALIAS_NAME)) {
throw new AppException(ResultCode.Failure, " :" + TABLE_ALIAS_NAME);
}
tableMap.put(tableName, aliasName);
return this;
}
/**
*
*
* @param columnName
* @return
*/
public SQLQueryUtil addColumn(String columnName) {
return this.addColumn(columnName, "");
}
/**
* ,
*
* @param columnName
* @param aliasName
* @return
*/
public SQLQueryUtil addColumn(String columnName, String aliasName) {
columnMap.put(columnName, aliasName);
return this;
}
/**
*
*
* <p>
* <span> </span> <div>
* , ValueType </div>
* </p>
*
* @param logicalOperators
* @param cKey
* @param relationalOperators
* @param cValue
* @param valueType
* @return
*/
private SQLQueryUtil addCondition(LogicalOperators logicalOperators, String cKey, RelationalOperators relationalOperators, String cValue,
ValueType valueType) {
switch (valueType) {
case STRING:
conditionMap.put(logicalOperators.toString(),
cKey.concat(" ").concat(relationalOperators.toString()).concat(" '").concat(cValue).concat("'"));
break;
case NUMBER:
conditionMap.put(logicalOperators.toString(), cKey.concat(" ").concat(relationalOperators.toString()).concat(" ").concat(cValue));
break;
default:
conditionMap.put(logicalOperators.toString(), this.getDateTimeQuery(cKey, relationalOperators, cValue, valueType));
break;
}
return this;
}
/**
* Oracle, MySQL ;
*/
private String getDateTimeQuery(String cKey, RelationalOperators ro, String cValue, ValueType vt) {
if (USE_JDBC_DRIVER.toLowerCase().contains("oracle")) {
switch (vt) {
case DATE:
return "to_char(".concat(cKey).concat(",'yyyy-mm-dd')").concat(ro.toString()).concat("'").concat(cValue).concat("'");
case TIME:
return "to_char(".concat(cKey).concat(",'hh24:mi:ss')").concat(ro.toString()).concat("'").concat(cValue).concat("'");
case DATETIME:
return "to_char(".concat(cKey).concat(",'yyyy-mm-dd hh24:mi:ss')").concat(ro.toString()).concat("'").concat(cValue).concat("'");
default:
break;
}
} else if (USE_JDBC_DRIVER.toLowerCase().contains("mysql")) {
switch (vt) {
case DATE:
return cKey.concat(ro.toString()).concat("UNIX_TIMESTAMP('").concat(cValue).concat(" 00:00:00')");
case TIME:
throw new AppException(ResultCode.Failure, " MySQL , ValueType.DATETIME ");
case DATETIME:
return cKey.concat(ro.toString()).concat("UNIX_TIMESTAMP('").concat(cValue).concat("')");
default:
break;
}
}
return cKey.concat(" ").concat(ro.toString()).concat(" \"").concat(cValue).concat("\"");
}
private SQLQueryUtil addLikeCondition(LogicalOperators logicalOperators, String cKey, String cValue) {
if (cValue.contains("%")) {
conditionMap.put(logicalOperators.toString(), cKey.concat(" LIKE \"").concat(cValue).concat("\""));
} else {
conditionMap.put(logicalOperators.toString(), cKey.concat(" LIKE \"%").concat(cValue).concat("%\""));
}
return this;
}
private SQLQueryUtil addInCondition(LogicalOperators logicalOperators, String cKey, String cValue) {
conditionMap.put(logicalOperators.toString(), cKey.concat(" IN ( ").concat(cValue).concat(" )"));
return this;
}
private SQLQueryUtil addNotInCondition(LogicalOperators logicalOperators, String cKey, String cValue) {
conditionMap.put(logicalOperators.toString(), cKey.concat(" NOT IN ( ").concat(cValue).concat(" )"));
return this;
}
private SQLQueryUtil addOrderBy(String orderField, SortMethod sortMethod) {
orderMap.put(orderField, sortMethod.toString());
return this;
}
/**
*
*
* @param groupField
* @return
*/
public SQLQueryUtil addGroupBy(String groupField) {
groupList.add(groupField);
return this;
}
/**
* AND
*
* @param cKey
* @param relationalOperators
* @param cValue
* @param valueType
* @return
*/
public SQLQueryUtil addAndCondition(String cKey, RelationalOperators relationalOperators, String cValue, ValueType valueType) {
return this.addCondition(LogicalOperators.AND, cKey, relationalOperators, cValue, valueType);
}
/**
* OR
*
* @param cKey
* @param relationalOperators
* @param cValue
* @param valueType
* @return
*/
public SQLQueryUtil addOrCondition(String cKey, RelationalOperators relationalOperators, String cValue, ValueType valueType) {
return this.addCondition(LogicalOperators.OR, cKey, relationalOperators, cValue, valueType);
}
/**
* AND LIKE
*
* @param cKey
* @param cValue
* @return
*/
public SQLQueryUtil addAndLikeCondition(String cKey, String cValue) {
return this.addLikeCondition(LogicalOperators.AND, cKey, cValue);
}
/**
* OR LIKE
*
* @param cKey
* @param cValue
* @return
*/
public SQLQueryUtil addOrLikeCondition(String cKey, String cValue) {
return this.addLikeCondition(LogicalOperators.OR, cKey, cValue);
}
/**
* AND IN
*
* @param cKey
* @param cValue
* @return
*/
public SQLQueryUtil addAndInCondition(String cKey, String cValue) {
return this.addInCondition(LogicalOperators.AND, cKey, cValue);
}
/**
* OR IN
*
* @param cKey
* @param cValue
* @return
*/
public SQLQueryUtil addOrInCondition(String cKey, String cValue) {
return this.addInCondition(LogicalOperators.OR, cKey, cValue);
}
/**
* AND NOT IN
*
* @param cKey
* @param cValue
* @return
*/
public SQLQueryUtil addAndNotInCondition(String cKey, String cValue) {
return this.addNotInCondition(LogicalOperators.AND, cKey, cValue);
}
/**
* OR NOT IN
*
* @param cKey
* @param cValue
* @return
*/
public SQLQueryUtil addOrNotInCondition(String cKey, String cValue) {
return this.addNotInCondition(LogicalOperators.OR, cKey, cValue);
}
/**
*
*
* @param orderField
* @return
*/
public SQLQueryUtil addAscOrderBy(String orderField) {
return this.addOrderBy(orderField, SortMethod.ASC);
}
/*
*
*/
public SQLQueryUtil addDescOrderBy(String orderField) {
return this.addOrderBy(orderField, SortMethod.DESC);
}
/**
*
*
* @return
*/
public SQLQueryUtil clearTable() {
tableMap.clear();
return this;
}
/**
*
*
* @return
*/
public SQLQueryUtil clearColumn() {
columnMap.clear();
return this;
}
/**
*
*
* @return
*/
public SQLQueryUtil clearCondition() {
conditionMap.clear();
return this;
}
/**
*
*
* @return
*/
public SQLQueryUtil clearOrder() {
orderMap.clear();
return this;
}
/**
*
*
* @return
*/
public SQLQueryUtil clearGroup() {
groupList.clear();
return this;
}
}