package frame.persist;
|
|
import java.math.BigDecimal;
|
import java.util.Collection;
|
import java.util.Date;
|
import java.util.Iterator;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
|
import frame.object.dao.Filter;
|
import frame.object.dao.OrderBy;
|
import frame.object.dao.Page;
|
import frame.object.data.Entity;
|
import frame.object.data.EntitySet;
|
import frame.object.meta.EntityMeta;
|
import frame.object.meta.Field;
|
import frame.util.ContentBuilder;
|
import frame.util.Util;
|
import frame.variant.IVariantsConsumer;
|
import frame.variant.IVariantsProvider;
|
import frame.variant.ValueType;
|
import frame.variant.VariantLink;
|
import frame.variant.expression.Expression;
|
import frame.variant.expression.IExpression;
|
import frame.variant.expression.VariantList;
|
import frame.variant.expression.VariantSegment;
|
|
|
public class NamedSQL implements Iterable<VariantSegment>, IExpression, IVariantsConsumer {
|
|
public static final String Code_GetEntity = "getEntity";
|
public static final String Code_GetFirstEntity = "getFirstEntity";
|
public static final String Code_GetCount = "getCount";
|
public static final String Code_InsertEntity = "insertEntity";
|
public static final String Code_UpdateEntityById = "updateById";
|
public static final String Code_DeleteByID = "deleteByID";
|
public static final String Code_DeleteChildrenByID = "deleteChildrenByID";
|
|
public static final String Param_Schema = "schema";
|
public static final String Param_TableName = "tablename";
|
public static final String Param_FieldNames = "fieldNames";
|
public static final String Param_FieldNameValues = "fieldNameValues";
|
public static final String Param_FieldValues = "fieldValues";
|
public static final String Param_FieldMetas = "fieldMetas";
|
public static final String Param_PlaceHolders = "placeHolders";
|
public static final String Param_Filter = "filter";
|
public static final String Param_FieldNamePlaceHolders = "fieldNamePlaceHolders";
|
public static final String Param_KeyFieldName = "keyFieldName";
|
public static final String Param_OrderBy = "orderby";
|
public static final String Param_Limit = "limit";
|
|
private static NamedSQLContainer namedSQLContainer;
|
|
protected String name;
|
protected String sql;
|
protected ValueType returnType;
|
protected Expression expression;
|
|
static {
|
namedSQLContainer = NamedSQLContainer.getInstance();
|
}
|
|
private NamedSQL(String name) {
|
this.name = name;
|
returnType = ValueType.Void;
|
}
|
|
public NamedSQL(String name, String sql) throws Exception {
|
this.name = name;
|
this.sql = sql;
|
|
parseSQL(sql);
|
}
|
|
protected void parseSQL(String sql) throws Exception {
|
String lower = sql.toLowerCase();
|
|
if (lower.indexOf("row_number()") > 0) {
|
sql = "select * from (" + sql + ")table_t where rownum > @{beginNo} and rownum <= @{endNo}";
|
}
|
|
expression = new SQLCreator(sql);
|
}
|
|
public static NamedSQL[] getInstance(String[] names) throws Exception {
|
if (names == null) { return new NamedSQL[0]; };
|
|
NamedSQL[] result = new NamedSQL[names.length];
|
|
for (int i = 0; i < names.length; i++) {
|
result[i] = getInstance(names[i]);
|
}
|
|
return result;
|
}
|
|
public static NamedSQL getInstance(String name) throws Exception {
|
NamedSQL result = namedSQLContainer.get(name);
|
|
if (result == null) {
|
result = namedSQLContainer.get(name);
|
}
|
|
if (result == null) {
|
throw new Exception("can not find named sql: " + name);
|
}
|
|
result = result.newInstance();
|
return result;
|
}
|
|
public NamedSQL newInstance() throws Exception {
|
NamedSQL instance = new NamedSQL(this.name);
|
instance.sql = this.sql;
|
instance.expression = this.expression.newInstance();
|
|
return instance;
|
}
|
|
public String getSQL() throws Exception {
|
String result = expression.getString();
|
return result;
|
}
|
|
public String getOriginalSql(){
|
return sql;
|
}
|
|
public Result exec() throws Exception {
|
Result result = SQLRunner.getResult(this);
|
return result;
|
}
|
|
public NamedSQL setSchema(String schema) {
|
return setParam(Param_Schema, schema);
|
}
|
|
public NamedSQL setTableName(String tableName) {
|
return setParam(Param_TableName, tableName.toLowerCase());
|
}
|
|
public NamedSQL setFieldNames(String names) {
|
return setParam(Param_FieldNames, names);
|
}
|
|
public NamedSQL setFieldNames(EntityMeta tableMeta) {
|
StringBuilder result = new StringBuilder();
|
boolean empty = true;
|
|
for (Field field: tableMeta) {
|
if (!empty) {
|
result.append(", ");
|
}
|
|
result.append(field.getName());
|
|
empty = false;
|
}
|
|
return setParam(Param_FieldNames, result.toString());
|
}
|
|
public NamedSQL setFieldNames(EntityMeta tableMeta, Entity entity) {
|
return setFieldNames(tableMeta, entity, "");
|
}
|
|
public NamedSQL setFieldNames(EntityMeta tableMeta, Entity entity, String excludeColumn) {
|
StringBuilder result = new StringBuilder();
|
boolean empty = true;
|
Field field;
|
|
for (int i = 0; i < tableMeta.getFieldCount(); i++) {
|
if (entity.isEmptyValue(i)) {
|
continue;
|
}
|
|
field = tableMeta.getField(i);
|
|
if (excludeColumn.equals(field.getName())) {
|
continue;
|
}
|
|
if (!empty) {
|
result.append(", ");
|
}
|
|
result.append(field.getName());
|
empty = false;
|
}
|
|
return setParam(Param_FieldNames, result.toString());
|
}
|
|
public NamedSQL setFieldNames(Collection<Field> fields) {
|
ContentBuilder result = new ContentBuilder();
|
|
for (Field field: fields) {
|
result.append(field.getName(), ", ");
|
}
|
|
return setParam(Param_FieldNames, result.toString());
|
}
|
|
public NamedSQL setFieldValues(Entity entity) {
|
ContentBuilder result = new ContentBuilder(", ");
|
|
int max = entity.getFieldCount();
|
|
for (int i = 0; i < max; i++) {
|
if (entity.isEmptyValue(i)) {
|
continue;
|
}
|
|
result.append(entity.getSQLString(i, "null"));
|
}
|
|
return setParam(Param_FieldValues, result.toString());
|
}
|
|
public NamedSQL setTableMeta(EntityMeta entityMeta) {
|
//1. set table name
|
setTableName(entityMeta.getTableName());
|
|
//2. set field meta
|
ContentBuilder result = new ContentBuilder(", ");
|
|
for (Field field: entityMeta) {
|
String line = "`" + field.getName() + "` " + field.getSQLTypeCode() + " " + field.getSQLNullCode();
|
result.append(line);
|
}
|
|
return setParam(Param_FieldMetas, result.toString());
|
}
|
|
public NamedSQL setTableMeta(String tableName, EntitySet entitySet) {
|
//1. set table name
|
setTableName(tableName);
|
|
//2. set field meta
|
ContentBuilder result = new ContentBuilder(", ");
|
|
for (Entity entity: entitySet) {
|
String fieldName = entity.getString("fieldName");
|
String fieldType = entity.getSQLTypeCode("fieldType", "fieldLength");
|
String sqlNullCode = entity.getSQLNullCode("nullable");
|
|
String line = "`" + fieldName + "` " + fieldType + " " + sqlNullCode;
|
result.append(line);
|
}
|
|
return setParam(Param_FieldMetas, result.toString());
|
}
|
|
public NamedSQL setQuotedFieldNames(EntityMeta tableMeta) {
|
ContentBuilder result = new ContentBuilder();
|
|
for (Field field: tableMeta) {
|
result.append(Util.doubleQuotedStr(field.getName()), ", ");
|
}
|
|
return setParam(Param_FieldNames, result.toString());
|
}
|
|
public NamedSQL setPlaceHolders(int count) {
|
ContentBuilder result = new ContentBuilder();
|
|
for (int i = 0; i < count; i++) {
|
result.append("?", ", ");
|
}
|
|
return setParam(Param_PlaceHolders, result.toString());
|
}
|
|
public NamedSQL setPlaceHolders(String placeHolders) {
|
return setParam(Param_PlaceHolders, placeHolders);
|
}
|
|
public NamedSQL setFieldNamePlaceHolders(EntityMeta tableMeta) {
|
ContentBuilder result = new ContentBuilder();
|
|
for (Field field: tableMeta) {
|
result.append(field.getName() + " = ? ", ", ");
|
}
|
|
return setParam(Param_FieldNamePlaceHolders, result.toString());
|
}
|
|
public NamedSQL setFilter(String filter) {
|
if (Util.isEmptyStr(filter)) {
|
return this;
|
}
|
|
return setParam(Param_Filter, filter);
|
}
|
|
public NamedSQL setFilter(Filter filter) {
|
if (filter == null) {
|
return this;
|
}
|
|
if (filter.isRaw()) {
|
return setFilter(filter.getRawFilter());
|
}
|
|
String segment = filter.toString();
|
return setFilter(segment);
|
}
|
|
public NamedSQL setOrderBy(String orderby) {
|
if (Util.isEmptyStr(orderby)) {
|
return this;
|
}
|
|
return setParam(Param_OrderBy, Util.stringJoin(" order by ", orderby));
|
}
|
|
public NamedSQL setOrderBy(OrderBy orderBy) {
|
if (orderBy == null) {
|
return this;
|
}
|
|
return setParam(Param_OrderBy, orderBy.getValue());
|
}
|
|
public NamedSQL setPage(Page page) {
|
if (page == null) {
|
return this;
|
}
|
|
if (expression.containsVariant(Param_Limit)) {
|
setParam(Param_Limit, page.getLimitSQL());
|
return this;
|
}
|
|
|
return this;
|
}
|
|
// public NamedSQL setFieldNameValues(Entity entity) throws Exception {
|
// ContentBuilder result = new ContentBuilder();
|
//
|
// EntityMeta tableMeta = entity.getEntityMeta();
|
// int cnt = tableMeta.getFieldCount();
|
//
|
// for (int i = 0; i < cnt; i++) {
|
// if (entity.isEmptyValue(i)) {
|
// continue;
|
// }
|
//
|
// Field field = tableMeta.getField(i);
|
// result.append(field.getName() + "=" + entity.getSQLString(i), ", ");
|
// }
|
//
|
// return setParam(Param_FieldNameValues, result.toString());
|
// }
|
|
public NamedSQL setFieldNameValues(Entity entity) throws Exception {
|
return setFieldNameValues(entity, null);
|
}
|
|
public NamedSQL setFieldNameValues(Entity entity, Map<String, String> excludeColumn) throws Exception {
|
ContentBuilder result = new ContentBuilder();
|
|
EntityMeta tableMeta = entity.getEntityMeta();
|
int cnt = tableMeta.getFieldCount();
|
|
for (int i = 0; i < cnt; i++) {
|
if (entity.isEmptyValue(i)) {
|
continue;
|
}
|
|
Field field = tableMeta.getField(i);
|
|
if (excludeColumn != null) {
|
if (excludeColumn.containsKey(field.getName())) {
|
continue;
|
}
|
}
|
|
result.append(field.getName() + "=" + entity.getSQLString(i), ", ");
|
}
|
|
return setParam(Param_FieldNameValues, result.toString());
|
}
|
|
public NamedSQL setParam(String name, String value) {
|
VariantSegment variant = expression.getVariant(name);
|
|
if (variant != null) {
|
variant.setValue(value);
|
}
|
|
return this;
|
}
|
|
public NamedSQL setParam(String name, String value, String defaultValue) {
|
if (value == null) {
|
value = defaultValue;
|
}
|
|
VariantSegment sqllVariant = expression.getVariant(name);
|
|
if (sqllVariant != null) {
|
sqllVariant.setValue(value);
|
}
|
|
return this;
|
}
|
|
public NamedSQL setParam(String name, int value) {
|
String StringValue = String.valueOf(value);
|
return setParam(name, StringValue);
|
}
|
|
public NamedSQL setParam(String name, BigDecimal value) {
|
String StringValue = value.toString();
|
return setParam(name, StringValue);
|
}
|
|
public NamedSQL setParam(String name, Date date) {
|
String StringValue = Util.toMySQLDateStr(date);
|
return setParam(name, StringValue);
|
}
|
|
public NamedSQL setParam(String name, boolean value) {
|
String StringValue = Util.booleanToStr(value);
|
return setParam(name, StringValue);
|
}
|
|
public String getParam(String name) {
|
VariantSegment variant = expression.getVariant(name);
|
String valueString = variant.getValueString();
|
if (Util.isNull(valueString)) {
|
return null;
|
}
|
return valueString;
|
}
|
|
public String getName() {
|
return name;
|
}
|
|
public ValueType getReturnType() {
|
return returnType;
|
}
|
|
public void setReturnType(ValueType dataType) {
|
this.returnType = dataType;
|
}
|
|
@Override
|
public Iterator<VariantSegment> iterator() {
|
return expression.iterator();
|
}
|
|
public void clearVariantValues() {
|
expression.clearVariantValues();
|
}
|
|
public VariantList getVariantList() {
|
return expression.getVariantList();
|
}
|
|
public Expression getExpression() {
|
return expression;
|
}
|
|
@Override
|
public void setVariants(IVariantsProvider... providers) throws Exception {
|
VariantLink.moveOnConsumer(this, providers);
|
}
|
|
public static int execute(String name, ISQLContext sqlContext) throws Exception {
|
NamedSQL namedSQL = NamedSQL.getInstance(name);
|
sqlContext.setParametersTo(namedSQL);
|
return SQLRunner.execSQL(namedSQL);
|
}
|
|
public String getSQLString() throws Exception {
|
return expression.getString();
|
}
|
|
@Override
|
public String toString() {
|
return expression.getString();
|
}
|
|
|
public void setSql(String sql) throws Exception {
|
this.sql = sql;
|
expression = new SQLCreator(sql);
|
}
|
|
@Override
|
public List<String> getVariantNameList() {
|
return expression.getVariantNameList();
|
}
|
|
public Set<String> getVariantNameSet() {
|
return expression.getVariantList().getKeySet();
|
}
|
|
@Override
|
public boolean containsVariant(String name) {
|
return expression.containsVariant(name);
|
}
|
|
@Override
|
public void setVariant(String name, Object value) throws Exception {
|
expression.setVariant(name, value);
|
}
|
|
@Override
|
public boolean isVariantNull(String name) {
|
return expression.isVariantNull(name);
|
}
|
|
public Entity getEntity() throws Exception {
|
return SQLRunner.getEntity(this);
|
}
|
|
public EntitySet getEntitySet() throws Exception {
|
return SQLRunner.getEntitySet(this);
|
}
|
|
public int execute() throws Exception {
|
return SQLRunner.execSQL(this);
|
}
|
}
|
