接上一篇《hsweb的系列学习--hsweb-easy-orm分析》
TableMetaDataStorage定义
以上三个MetaData元素定义完之后，通过TableMetaDataStorage来对表元数据做存储方面的一些操作:
package org.hsweb.ezorm.core.meta.storage;
import org.hsweb.ezorm.core.meta.TableMetaData;
import java.util.Set;
public interface TableMetaDataStorage {
<T extends TableMetaData> Set<T> getAllTableMetaData();
<T extends TableMetaData> T getTableMetaData(String nameOrAlias);
<T extends TableMetaData> T removeTableMeta(String nameOrAlias);
<T extends TableMetaData> T putTableMetaData(T table);
void clear();
}
因为本就是key-value形式保存的，那么很自然就衍生成map类型的实现类: 都是一些map方面的操作，就不多说了
package org.hsweb.ezorm.core.meta.storage;
import org.hsweb.ezorm.core.meta.TableMetaData;
import java.util.HashMap;
import java.util.LinkedHashSet;
import java.util.Map;
import java.util.Set;
public class MapTableMetaDataStorage implements TableMetaDataStorage {
private final Map<String, TableMetaData> nameTableMetaDataStorage = new HashMap<>();
private final Map<String, TableMetaData> aliasTableMetaDataStorage = new HashMap<>();
@Override
public <T extends TableMetaData> Set<T> getAllTableMetaData() {
Set<T> all = new LinkedHashSet<>();
all.addAll((Set) nameTableMetaDataStorage.values());
return all;
}
@Override
public <T extends TableMetaData> T getTableMetaData(String nameOrAlias) {
T old = (T) nameTableMetaDataStorage.get(nameOrAlias);
if (old == null) old = (T) aliasTableMetaDataStorage.get(nameOrAlias);
return old;
}
@Override
public <T extends TableMetaData> T removeTableMeta(String nameOrAlias) {
T old = (T) nameTableMetaDataStorage.remove(nameOrAlias);
T old2 = (T) aliasTableMetaDataStorage.remove(nameOrAlias);
return old != null ? old : old2;
}
@Override
public <T extends TableMetaData> T putTableMetaData(T table) {
nameTableMetaDataStorage.put(table.getName(), table);
if (!table.getName().equals(table.getAlias())) {
nameTableMetaDataStorage.put(table.getAlias(), table);
}
return table;
}
@Override
public void clear() {
nameTableMetaDataStorage.clear();
aliasTableMetaDataStorage.clear();
}
}
对上面定义的三个元数据类型接口的抽象实现:
对于DatabaseMetaData的初步通用实现:
package org.hsweb.ezorm.core.meta;
import org.hsweb.ezorm.core.ObjectWrapperFactory;
import org.hsweb.ezorm.core.ValidatorFactory;
import org.hsweb.ezorm.core.meta.storage.MapTableMetaDataStorage;
import org.hsweb.ezorm.core.meta.storage.TableMetaDataStorage;
public abstract class AbstractDatabaseMetaData implements DatabaseMetaData {
protected ObjectWrapperFactory objectWrapperFactory;
protected ValidatorFactory validatorFactory;
protected TableMetaDataStorage tableMetaDataStorage=new MapTableMetaDataStorage();
@Override
public <T extends TableMetaData> T getTableMetaData(String name) {
return tableMetaDataStorage.getTableMetaData(name);
}
@Override
public ObjectWrapperFactory getObjectWrapperFactory() {
return objectWrapperFactory;
}
@Override
public ValidatorFactory getValidatorFactory() {
return validatorFactory;
}
public void setObjectWrapperFactory(ObjectWrapperFactory objectWrapperFactory) {
this.objectWrapperFactory = objectWrapperFactory;
}
public void setValidatorFactory(ValidatorFactory validatorFactory) {
this.validatorFactory = validatorFactory;
}
public void setTableMetaDataStorage(TableMetaDataStorage tableMetaDataStorage) {
this.tableMetaDataStorage = tableMetaDataStorage;
}
}
对于TableMetaData的初步通用实现:
properties triggerBase都是用map来进行相应包装
package org.hsweb.ezorm.core.meta;
import org.hsweb.ezorm.core.*;
import java.util.*;
public abstract class AbstractTableMetaData<C extends AbstractColumnMetaData> implements TableMetaData {
//表名称
protected String name = null;
//表别名,如果指定了别名,查询结果将使用别名进行封装
protected String alias = null;
//备注
protected String comment = null;
//表字段
protected Map<String, C> columnMetaDataMap = new LinkedHashMap<>();
protected Map<String, C> aliasColumnMetaDataMap = new LinkedHashMap<>();
protected Validator validator = null;
protected ObjectWrapper objectWrapper = null;
protected Map<String, Object> properties = new HashMap<>();
protected Map<String, Trigger> triggerBase = new HashMap<>();
@Override
public <T> ObjectWrapper<T> getObjectWrapper() {
return objectWrapper;
}
public void setObjectWrapper(ObjectWrapper objectWrapper) {
this.objectWrapper = objectWrapper;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAlias() {
if (alias == null) alias = name;
return alias;
}
public <T extends AbstractTableMetaData<C>> T addColumn(C columnMetaData) {
columnMetaData.setTableMetaData(this);
columnMetaDataMap.put(columnMetaData.getName(), columnMetaData);
if (!columnMetaData.getName().equals(columnMetaData.getAlias()))
aliasColumnMetaDataMap.put(columnMetaData.getAlias(), columnMetaData);
return (T) this;
}
public void setAlias(String alias) {
this.alias = alias;
}
public void on(String triggerName, Trigger trigger) {
triggerBase.put(triggerName, trigger);
}
public void on(String triggerName, Map<String, Object> context) {
if (triggerIsSupport(triggerName)) {
Trigger trigger = triggerBase.get(triggerName);
trigger.execute(context);
}
}
@Override
public Set<C> getColumns() {
return new LinkedHashSet<>(columnMetaDataMap.values());
}
public void removeColumn(String name) {
columnMetaDataMap.remove(name);
aliasColumnMetaDataMap.remove(name);
}
public boolean triggerIsSupport(String name) {
return triggerBase.containsKey(name);
}
public PropertyWrapper getProperty(String name) {
return new SimplePropertyWrapper(properties.get(name));
}
public PropertyWrapper getProperty(String name, Object defaultValue) {
return new SimplePropertyWrapper(properties.getOrDefault(name, defaultValue));
}
public PropertyWrapper removeProperty(String name) {
return new SimplePropertyWrapper(properties.remove(name));
}
public PropertyWrapper setProperty(String property, Object value) {
return new SimplePropertyWrapper(properties.put(property, value));
}
public abstract C findColumn(String name);
public String getComment() {
return comment;
}
@Override
public C getColumn(String name) {
C metaData = columnMetaDataMap.get(name);
if (metaData == null) metaData = aliasColumnMetaDataMap.get(name);
return metaData;
}
public void setComment(String comment) {
this.comment = comment;
}
public Map<String, Object> getProperties() {
return properties;
}
public void setProperties(Map<String, Object> properties) {
this.properties = properties;
}
public Validator getValidator() {
return validator;
}
public void setValidator(Validator validator) {
this.validator = validator;
}
@Override
public String toString() {
return name + " [" + alias + "]" + "(" + comment + ")";
}
}
对于ColumnMetaData的初步通用实现:
package org.hsweb.ezorm.core.meta;
import org.hsweb.ezorm.core.OptionConverter;
import org.hsweb.ezorm.core.PropertyWrapper;
import org.hsweb.ezorm.core.SimplePropertyWrapper;
import org.hsweb.ezorm.core.ValueConverter;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
public abstract class AbstractColumnMetaData implements ColumnMetaData {
protected String name;
protected String alias;
protected String comment;
protected Class javaType;
protected TableMetaData tableMetaData;
protected OptionConverter optionConverter;
protected ValueConverter valueConverter;
protected Set<String> validator;
protected Map<String, Object> properties = new HashMap<>();
@Override
public String getName() {
return name;
}
@Override
public String getAlias() {
if (alias == null) alias = name;
return alias;
}
@Override
public String getComment() {
if (comment == null) comment = "";
return comment;
}
@Override
public Class getJavaType() {
return javaType;
}
@Override
public Set<String> getValidator() {
return validator;
}
@Override
public <T extends TableMetaData> T getTableMetaData() {
return (T) tableMetaData;
}
@Override
public ValueConverter getValueConverter() {
return valueConverter;
}
@Override
public OptionConverter getOptionConverter() {
return optionConverter;
}
@Override
public PropertyWrapper getProperty(String property) {
return new SimplePropertyWrapper(properties.get(property));
}
@Override
public PropertyWrapper getProperty(String property, Object defaultValue) {
Object value = properties.get(property);
return new SimplePropertyWrapper(value == null ? defaultValue : value);
}
@Override
public PropertyWrapper setProperty(String property, Object value) {
return new SimplePropertyWrapper(properties.put(property, value));
}
public void setName(String name) {
this.name = name;
}
public void setAlias(String alias) {
this.alias = alias;
}
public void setComment(String comment) {
this.comment = comment;
}
public void setJavaType(Class javaType) {
this.javaType = javaType;
}
public void setTableMetaData(TableMetaData tableMetaData) {
this.tableMetaData = tableMetaData;
}
public void setOptionConverter(OptionConverter optionConverter) {
this.optionConverter = optionConverter;
}
public void setValueConverter(ValueConverter valueConverter) {
this.valueConverter = valueConverter;
}
public void setValidator(Set<String> validator) {
this.validator = validator;
}
public Map<String, Object> getProperties() {
return properties;
}
public void setProperties(Map<String, Object> properties) {
this.properties = properties;
}
@Override
public abstract AbstractColumnMetaData clone();
}
三，curd的逻辑
从sql语句里可以看出，查，删，改，都是伴随着条件进行的，首先对条件进行设计，然后再具体事情具体办
1，设计一个统一的条件类型的支持接口来统一接收
一个是根据输入条件TermTypeConditionalSupport:
package org.hsweb.ezorm.core;
public interface TermTypeConditionalSupport {
interface Accepter<T> {
T accept(String column, String termType, Object value);
}
interface SimpleAccepter<T> {
T accept(String column, Object value);
}
}
另外一个根据实例化的po bean TermTypeConditionalFromBeanSupport:
package org.hsweb.ezorm.core;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public interface TermTypeConditionalFromBeanSupport {
Logger logger = LoggerFactory.getLogger("queryForBean");
Object getBean();
default Object getValue(String property) {
if (getBean() == null) {
return null;
}
PropertyUtilsBean propertyUtilsBean = BeanUtilsBean.getInstance().getPropertyUtils();
try {
return propertyUtilsBean.getProperty(getBean(), property);
} catch (Exception e) {
logger.warn("get bean property {} error", property, e);
}
return null;
}
}
接下来就搞定条件的设置接口:Conditional
对于Java8 Consumer接口 的知识预备，请查看 Predicate和Consumer接口– Java 8中java.util.function包下的接口
对于嵌套条件的设置，请查看org.hsweb.ezorm.core.NestConditional,
源码最后关于Supplier接口和BiConsumer<T, U>接口,不懂的请看Java 8之Stream的强大工具Collector
先把各种条件关键字用方法给设定出来，如，and(),or(),where()，以及最后条件追加，根据条件判断然后再确定追加与否这些情况进行设定等，同样支持直接拼接sql，代码如下:
* Copyright 2016 http://github.com/hs-web
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.hsweb.ezorm.core;
import org.hsweb.ezorm.core.param.TermType;
import org.hswebframwork.utils.StringUtils;
import java.util.Arrays;
import java.util.Collection;
import java.util.Map;
import java.util.function.BiConsumer;
import java.util.function.BooleanSupplier;
import java.util.function.Consumer;
import java.util.function.Function;
public interface Conditional<T extends Conditional> extends TermTypeConditionalSupport {
NestConditional<T> nest();
NestConditional<T> nest(String column, Object value);
NestConditional<T> orNest();
NestConditional<T> orNest(String column, Object value);
T and();
T or();
default T and(Consumer<Conditional> consumer) {
consumer.accept(this.and());
return (T) this;
}
default T or(Consumer<Conditional> consumer) {
consumer.accept(this.or());
return (T) this;
}
T and(String column, String termType, Object value);
T or(String column, String termType, Object value);
Accepter<T> getAccepter();
default T where(String column, Object value) {
return and(column, TermType.eq, value);
}
default T where() {
return (T) this;
}
default T where(Consumer<Conditional> consumer) {
consumer.accept(this);
return (T) this;
}
default T and(String column, Object value) {
return and(column, TermType.eq, value);
}
default T is(String column, Object value) {
return accept(column, TermType.eq, value);
}
default T or(String column, Object value) {
return or(column, TermType.eq, value);
}
default T like(String column, Object value) {
return accept(column, TermType.like, value);
}
default T like$(String column, Object value) {
if (value == null)
return like(column, null);
return accept(column, TermType.like, StringUtils.concat(value, "%"));
}
default T $like(String column, Object value) {
if (value == null)
return like(column, null);
return accept(column, TermType.like, StringUtils.concat("%", value));
}
default T $like$(String column, Object value) {
if (value == null)
return like(column, null);
return accept(column, TermType.like, StringUtils.concat("%", value, "%"));
}
default T notLike(String column, Object value) {
return accept(column, TermType.nlike, value);
}
default T gt(String column, Object value) {
return accept(column, TermType.gt, value);
}
default T lt(String column, Object value) {
return accept(column, TermType.lt, value);
}
default T gte(String column, Object value) {
return accept(column, TermType.gte, value);
}
default T lte(String column, Object value) {
return accept(column, TermType.lte, value);
}
default T in(String column, Object value) {
return accept(column, TermType.in, value);
}
default T in(String column, Object... values) {
return accept(column, TermType.in, values);
}
default T in(String column, Collection values) {
return accept(column, TermType.in, values);
}
default T notIn(String column, Object value) {
return accept(column, TermType.nin, value);
}
default T isEmpty(String column) {
return accept(column, TermType.empty, 1);
}
default T notEmpty(String column) {
return accept(column, TermType.nempty, 1);
}
default T isNull(String column) {
return accept(column, TermType.isnull, 1);
}
default T notNull(String column) {
return accept(column, TermType.notnull, 1);
}
default T not(String column, Object value) {
return accept(column, TermType.not, value);
}
default T between(String column, Object between, Object and) {
return accept(column, TermType.btw, Arrays.asList(between, and));
}
default T notBetween(String column, Object between, Object and) {
return accept(column, TermType.nbtw, Arrays.asList(between, and));
}
default T accept(String column, String termType, Object value) {
return getAccepter().accept(column, termType, value);
}
/**
* 直接拼接sql,参数支持预编译
* 例如
* <ul>
* <li>query.sql("name=?","admin")</li>
* <li>query.sql("name=#{name}",{name:"admin"})</li>
* <li>query.sql("name=#{[0]}",["admin"])</li>
* </ul>
*
* @param sql sql字符串
* @param params 参数
* @return {@link T}
*/
T sql(String sql, Object... params);
default <O> T each(Collection<O> list, BiConsumer<O, Conditional<T>> consumer) {
if (null != list)
list.forEach(o -> consumer.accept(o, this));
return (T) this;
}
/**
* 遍历一个集合，进行条件追加
* 例如:<br>
* query.or().each("areaId",[1,2,3],(query)->query::$like$)<br>
* 将追加sql<br>
* areaId like '%1%' or areaId like '%2%' or areaId like '%3%'
*
* @param column 要追加到的列名
* @param list 集合
* @param accepterGetter 追加方式函数
* @return {@link T}
* @see Function
* @see Conditional
* @see org.hsweb.ezorm.core.TermTypeConditionalSupport.SimpleAccepter
*/
default T each(String column, Collection list, Function<Conditional<T>, SimpleAccepter<Conditional<T>>> accepterGetter) {
if (null != list)
list.forEach(o -> accepterGetter.apply(this).accept(column, o));
return (T) this;
}
default T each(String column, String termType, Collection list, Function<Conditional<T>, Accepter<Conditional<T>>> accepterGetter) {
if (null != list)
list.forEach(o -> accepterGetter.apply(this).accept(column, termType, o));
return (T) this;
}
/**
* 参照 {@link Conditional#each(String, Collection, Function)}
* 提供了一个valueMapper进行值转换如:
* <br>
* query.or().each("areaId",[1,2,3],(query)->query::$like$,(value)->","+value+",")<br>
* 将追加sql<br>
* areaId like '%,1,%' or areaId like '%,2,%' or areaId like '%,3,%'
*
* @param column 要追加到的列名
* @param list 集合
* @param accepterGetter 追加方式函数
* @param valueMapper 值转换函数 {@link Function}
* @return {@link T}
* @see Function
* @see Conditional
* @see org.hsweb.ezorm.core.TermTypeConditionalSupport.SimpleAccepter
*/
default T each(String column, Collection list, Function<Conditional<T>, SimpleAccepter<Conditional<T>>> accepterGetter, Function<Object, Object> valueMapper) {
if (null != list)
list.forEach(o -> accepterGetter.apply(this).accept(column, valueMapper.apply(o)));
return (T) this;
}
default T each(String column, String termType, Collection list, Function<Conditional<T>, Accepter<Conditional<T>>> accepterGetter, Function<Object, Object> valueMapper) {
if (null != list)
list.forEach(o -> accepterGetter.apply(this).accept(column, termType, valueMapper.apply(o)));
return (T) this;
}
/**
* 遍历一个Map,进行条件追加
*
* @param mapParam map参数
* @param accepter 追加方式函数
* @return {@link T}
* @see Function
* @see Conditional
* @see org.hsweb.ezorm.core.TermTypeConditionalSupport.SimpleAccepter
*/
default T each(Map<String, Object> mapParam, Function<Conditional<T>, SimpleAccepter<Conditional<T>>> accepter) {
if (null != mapParam)
mapParam.forEach((k, v) -> accepter.apply(this).accept(k, v));
return (T) this;
}
default T each(Map<String, Object> mapParam, String termType, Function<Conditional<T>, Accepter<Conditional<T>>> accepter) {
if (null != mapParam)
mapParam.forEach((k, v) -> accepter.apply(this).accept(k, termType, v));
return (T) this;
}
/**
* 指定一个前置条件,当条件满足的时候,调用回调进行自定义参数<br>
* 如: query(age>10,query->query.gt("age",10))
*
* @param condition 前置条件
* @param consumer 回调
* @return {@link T}
*/
default T when(boolean condition, Consumer<Conditional<T>> consumer) {
if (condition) {
consumer.accept(this);
}
return (T) this;
}
/**
* 通过BooleanSupplier获取条件,例如<br>
* query.when(()->age>10,query->query.gt("age",10));
*
* @see Conditional#when(boolean, Consumer)
* @see BooleanSupplier
*/
default T when(BooleanSupplier condition, Consumer<Conditional<T>> consumer) {
return when(condition.getAsBoolean(), consumer);
}
/**
* 指定前置条件,列名,参数值,条件构造函数。当条件满足的时候，执行构造器添加条件.例如<br>
* query.when(age>10,"age",10,query->query::gt);<br>
* 等同于<br>
* if(age>10)query.gt(age,10);<br>
*
* @param condition 前置条件
* @param column 要查询的列名
* @param value 参数值
* @param accepter 条件构造函数
* @return {@link T}
*/
default T when(boolean condition, String column, Object value, Function<Conditional<T>, SimpleAccepter<Conditional<T>>> accepter) {
if (condition) {
accepter.apply(this).accept(column, value);
}
return (T) this;
}
/**
* 指定列名，参数值，条件判断函数，条件构造函数进行条件添加。如<br>
* query.when("age",10,value->value>10,query->query::gt)
*
* @param column 列名
* @param value 值
* @param condition 条件判断函数
* @param accepter 条件构造函数
* @return {@link T}
* @see Conditional#when(boolean, String, Object, Function)
*/
default <V> T when(String column, V value, Function<V, Boolean> condition, Function<Conditional<T>, SimpleAccepter<Conditional<T>>> accepter) {
return when(condition.apply(value), column, value, accepter);
}
/**
* 功能与{@link Conditional#when(boolean, String, Object, Function)} 类似,可自定义termType 如:<br>
* query.when(true,"age","like",10,query->query::or)
*
* @param condition 条件
* @param column 列名
* @param termType 条件类型
* @param value 参数
* @param accepter 条件构造函数
* @return {@link T}
* @see Conditional#when(boolean, String, Object, Function)
*/
default T when(boolean condition, String column, String termType, Object value, Function<Conditional<T>, Accepter<Conditional<T>>> accepter) {
if (condition) {
accepter.apply(this).accept(column, termType, value);
}
return (T) this;
}
/**
* 功能与{@link Conditional#when(String, Object, Function, Function)} 类似,可自定义termType 如:<br>
* query.when("age","like",10,value->value==10,query->query::or)
*
* @param condition 条件
* @param column 列名
* @param termType 条件类型
* @param value 参数
* @param accepter 条件构造函数
* @return {@link T}
* @see Conditional#when(boolean, String, Object, Function)
* @see TermType
*/
default <V> T when(String column, String termType, V value, Function<V, Boolean> condition, Function<Conditional<T>, Accepter<Conditional<T>>> accepter) {
return when(condition.apply(value), column, termType, value, accepter);
}
}
因为之前有设计针对sql的直接封装，那么相应的增加SqlConditionSupport<T>
package org.hsweb.ezorm.core;
import org.hsweb.ezorm.core.param.SqlTerm;
import org.hsweb.ezorm.core.param.Term;
/**
* @author zhouhao
*/
public abstract class SqlConditionSupport<T> {
protected Term.Type nowTermType = Term.Type.and;
protected abstract T addSqlTerm(SqlTerm term);
public Term.Type getNowTermType() {
return nowTermType;
}
protected T setOr() {
nowTermType = Term.Type.or;
return (T) this;
}
protected T setAnd() {
nowTermType = Term.Type.and;
return (T) this;
}
public T sql(String sql, Object... params) {
SqlTerm sqlTerm = new SqlTerm();
sqlTerm.setColumn(sql);
sqlTerm.setValue(params);
sqlTerm.setType(getNowTermType());
return addSqlTerm(sqlTerm);
}
}
2，对删，改，查的设计与实现
对改的接口设计Update:
具体不需要解释了，英文已经很清晰明白了
package org.hsweb.ezorm.core;
import org.hsweb.ezorm.core.param.UpdateParam;
import java.sql.SQLException;
public interface Update<T> extends Conditional<Update<T>>, TriggerSkipSupport<Update<T>> {
Update<T> set(T data);
Update<T> set(String property, Object value);
Update<T> includes(String... fields);
Update<T> excludes(String... fields);
Update<T> setParam(UpdateParam param);
int exec() throws SQLException;
}
实现类:
package org.hsweb.ezorm.rdb.simple;
import org.apache.commons.beanutils.BeanUtils;
import org.hsweb.ezorm.core.*;
import org.hsweb.ezorm.core.param.SqlTerm;
import org.hsweb.ezorm.core.param.UpdateParam;
import org.hsweb.ezorm.rdb.executor.SQL;
import org.hsweb.ezorm.rdb.executor.SqlExecutor;
import org.hsweb.ezorm.rdb.meta.RDBTableMetaData;
import org.hsweb.ezorm.rdb.render.SqlRender;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
/**
* Created by zhouhao on 16-6-5.
*/
class SimpleUpdate<T> extends ValidatorAndTriggerSupport<Update<T>> implements Update<T> {
private static final Logger logger = LoggerFactory.getLogger(Update.class);
private UpdateParam updateParam;
private SimpleTable<T> table;
private SqlExecutor sqlExecutor;
public SimpleUpdate(SimpleTable<T> table, SqlExecutor sqlExecutor) {
this.table = table;
this.sqlExecutor = sqlExecutor;
updateParam = new UpdateParam();
}
@Override
public Update<T> set(T data) {
updateParam.setData(data);
return this;
}
@Override
public Update<T> set(String property, Object value) {
if (updateParam.getData() == null) updateParam.setData(new HashMap<>());
if (updateParam.getData() instanceof Map) {
((Map) updateParam.getData()).put(property, value);
} else {
try {
BeanUtils.setProperty(updateParam.getData(), property, value);
} catch (Exception e) {
logger.warn("property error", e);
}
}
return this;
}
@Override
protected Update<T> addSqlTerm(SqlTerm term) {
updateParam.addTerm(term);
return this;
}
@Override
public Update<T> includes(String... fields) {
updateParam.includes(fields);
return this;
}
@Override
public Update<T> excludes(String... fields) {
updateParam.excludes(fields);
return this;
}
@Override
public Update<T> and(String condition, String termType, Object value) {
updateParam.and(condition, termType, value);
return this;
}
@Override
public Update<T> or(String condition, String termType, Object value) {
updateParam.or(condition, termType, value);
return this;
}
private Accepter accepter=this::and;
@Override
public Update<T> and() {
setAnd();
accepter = this::and;
return this;
}
@Override
public Update<T> or() {
setOr();
accepter = this::or;
return this;
}
@Override
public Accepter getAccepter() {
return accepter;
}
@Override
public NestConditional<Update<T>> nest() {
return new SimpleNestConditional<>(this, updateParam.nest());
}
@Override
public NestConditional<Update<T>> nest(String column, Object value) {
return new SimpleNestConditional<>(this, updateParam.nest(column, value));
}
@Override
public NestConditional<Update<T>> orNest() {
return new SimpleNestConditional<>(this, updateParam.orNest());
}
@Override
public NestConditional<Update<T>> orNest(String column, Object value) {
return new SimpleNestConditional<>(this, updateParam.orNest(column, value));
}
@Override
public Update<T> setParam(UpdateParam param) {
this.updateParam = param;
return this;
}
@Override
public int exec() throws SQLException {
boolean supportBefore = !triggerSkip && table.getMeta().triggerIsSupport(Trigger.update_before);
boolean supportDone = !triggerSkip && table.getMeta().triggerIsSupport(Trigger.update_done);
Map<String, Object> context = table.getDatabase().getTriggerContextRoot();
if (supportBefore || supportDone) {
context = table.getDatabase().getTriggerContextRoot();
context.put("table", table);
context.put("database", table.getDatabase());
context.put("param"
}
if (supportBefore) {
table.getMeta().on(Trigger.update_before, context);
}
SqlRender<UpdateParam> render = table.getMeta().getDatabaseMetaData().getRenderer(SqlRender.TYPE.UPDATE);
SQL sql = render.render(table.getMeta(), updateParam);
tryValidate(updateParam.getData(), Validator.Operation.UPDATE);
int total = sqlExecutor.update(sql);
if (supportDone) {
context.put("total", total);
table.getMeta().on(Trigger.update_done, context);
}
return total;
}
@Override
RDBTableMetaData getTableMeta() {
return table.getMeta();
}
}
最关键的地方也就在于exec(),
boolean supportBefore = !triggerSkip && table.getMeta().triggerIsSupport(Trigger.update_before);
在触发条件不忽略，然后此table的元数据触发条件支持Trigger.update_before,返回true，调用过程如下图:
&amp;amp;amp;lt;img src="https://pic1.zhimg.com/v2-a28cf7fb814e27d950940a380e72a4e0_b.png" data-rawwidth="956" data-rawheight="558" class="origin_image zh-lightbox-thumb" width="956" data-original="https://pic1.zhimg.com/v2-a28cf7fb814e27d950940a380e72a4e0_r.png"&amp;amp;amp;gt;
同理,supportDone就不再解释了，
Map<String, Object> context = table.getDatabase().getTriggerContextRoot();其实就是产生一个map,具体源码如下:
public Map<String, Object> getTriggerContextRoot() {
return new HashMap<>();
}
符合supportBefore || supportDone条件，添加table，database，param信息
当符合supportBefore条件时，table.getMeta().on(Trigger.update_before, context);
&amp;amp;amp;lt;img src="https://pic4.zhimg.com/v2-74cf3fa193ca2f8d57ab1bc1c914deef_b.png" data-rawwidth="1295" data-rawheight="437" class="origin_image zh-lightbox-thumb" width="1295" data-original="https://pic4.zhimg.com/v2-74cf3fa193ca2f8d57ab1bc1c914deef_r.png"&amp;amp;amp;gt;
这里只看到了这一个脚本实现类，就顺带贴下
对后面代码：
SqlRender<UpdateParam> render = table.getMeta().getDatabaseMetaData().getRenderer(SqlRender.TYPE.UPDATE);
SQL sql = render.render(table.getMeta(), updateParam);
tryValidate(updateParam.getData(), Validator.Operation.UPDATE);
int total = sqlExecutor.update(sql);
下图RDBDatabaseMetaData 下面被掩盖的标记代码为public abstract SqlRender getRenderer(SqlRender.TYPE type);
&amp;amp;amp;lt;img src="https://pic1.zhimg.com/v2-fb815df72a5288f26f9e37f3224c7894_b.png" data-rawwidth="1123" data-rawheight="776" class="origin_image zh-lightbox-thumb" width="1123" data-original="https://pic1.zhimg.com/v2-fb815df72a5288f26f9e37f3224c7894_r.png"&amp;amp;amp;gt;
AbstractRDBDatabaseMetaData对RDBDatabaseMetaData进行抽象实现
在getRenderer()内得到相应数据库类型的render，在init()放入相应数据库类型的render
package org.hsweb.ezorm.rdb.render.dialect;
import org.hsweb.ezorm.rdb.meta.RDBDatabaseMetaData;
import org.hsweb.ezorm.rdb.render.SqlRender;
import org.hsweb.ezorm.rdb.render.support.simple.*;
import java.util.HashMap;
import java.util.Map;
public abstract class AbstractRDBDatabaseMetaData extends RDBDatabaseMetaData {
protected Map<SqlRender.TYPE, SqlRender> renderMap = new HashMap<>();
protected Dialect dialect;
public AbstractRDBDatabaseMetaData(Dialect dialect) {
this.dialect = dialect;
}
public void init() {
putRenderer(SqlRender.TYPE.DELETE, new SimpleDeleteSqlRender(getDialect()));
putRenderer(SqlRender.TYPE.INSERT, new SimpleInsertSqlRender());
putRenderer(SqlRender.TYPE.SELECT, new SimpleSelectSqlRender(getDialect()));
putRenderer(SqlRender.TYPE.UPDATE, new SimpleUpdateSqlRender(getDialect()));
putRenderer(SqlRender.TYPE.SELECT_TOTAL, new SimpleSelectTotalSqlRender(getDialect()));
}
@Override
public Dialect getDialect() {
return dialect;
}
public SqlRender getRenderer(SqlRender.TYPE type) {
SqlRender render = renderMap.get(type);
if (render == null) throw new UnsupportedOperationException(type + " is not support");
return render;
}
public void putRenderer(SqlRender.TYPE type, SqlRender sqlRender) {
renderMap.put(type, sqlRender);
}
}
最后再看具体实现类，此处拿MysqlRDBDatabaseMetaData为例
在此MysqlRDBDatabaseMetaData实例进行初始化时，设置MysqL数据库方言并会调用init()方法放入MysqL数据库类型的render
package org.hsweb.ezorm.rdb.render.dialect;
import org.hsweb.ezorm.rdb.render.SqlRender;
import org.hsweb.ezorm.rdb.render.support.mysql.MysqlDeleteSqlRender;
import org.hsweb.ezorm.rdb.render.support.mysql.MysqlMetaAlterRender;
import org.hsweb.ezorm.rdb.render.support.mysql.MysqlMetaCreateRender;
public class MysqlRDBDatabaseMetaData extends AbstractRDBDatabaseMetaData {
private static final String DEFAULT_NAME = "mysql";
private String name;
@Override
public void init() {
super.init();
renderMap.put(SqlRender.TYPE.META_CREATE, new
renderMap.put(SqlRender.TYPE.DELETE, new MysqlDeleteSqlRender(getDialect()));
renderMap.put(SqlRender.TYPE.META_ALTER, new MysqlMetaAlterRender(this));
}
public MysqlRDBDatabaseMetaData() {
super(Dialect.MYSQL);
name = DEFAULT_NAME;
init();
}
@Override
public String getName() {
return name;
}
}
对update sql语句的组合渲染：
首先设计一个其他类型语句共有的语句特性，比如指定了exclude 字段,没有指定include 字段
CommonSqlRender:具体逻辑如下，不仔细解释了
package org.hsweb.ezorm.rdb.render.support.simple;
import org.hsweb.ezorm.rdb.meta.RDBColumnMetaData;
import org.hsweb.ezorm.rdb.meta.Correlation;
import org.hsweb.ezorm.rdb.meta.RDBTableMetaData;
import org.hsweb.ezorm.core.param.Param;
import org.hsweb.ezorm.rdb.render.SqlRender;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;
public abstract class CommonSqlRender<R extends Param> implements SqlRender<R> {
protected Logger logger = LoggerFactory.getLogger(this.getClass());
public class OperationColumn {
private String tableName;
private RDBColumnMetaData RDBColumnMetaData;
public OperationColumn(String tableName, RDBColumnMetaData RDBColumnMetaData) {
this.tableName = tableName;
this.RDBColumnMetaData = RDBColumnMetaData;
if (tableName == null) this.tableName = RDBColumnMetaData.getTableMetaData().getAlias();
}
public String getTableName() {
return tableName;
}
public RDBColumnMetaData getRDBColumnMetaData() {
return RDBColumnMetaData;
}
}
public List<OperationColumn> parseOperationField(RDBTableMetaData metaData, R param) {
Set<String> includes = param.getIncludes(),
excludes = param.getExcludes();
boolean includesIsEmpty = includes.isEmpty(),
excludesIsEmpty = excludes.isEmpty();
List<OperationColumn> tmp = new ArrayList<>();
if ((includesIsEmpty && excludesIsEmpty)) {
metaData.getColumns().forEach(column -> tmp.add(new OperationColumn(null, column)));
return tmp;
}
//指定了exclude,没有指定include
if (includesIsEmpty && !excludesIsEmpty) {
boolean hasSelf = false;
for (String exclude : excludes) {
if (exclude.contains(".")) {
//表.*
includes.add(exclude.split("[.]")[0] + ".*");
} else {
//不包含表. 就设置下面的*就好
hasSelf = true;
}
}
if (hasSelf) includes.add("*");
includesIsEmpty = false;
}
if (!includesIsEmpty) {
includes.forEach(include -> {
if (excludes.contains(include)) return;
if ("*".equals(include)) {
metaData.getColumns().forEach(column -> {
if (excludes.contains(column.getAlias()) || excludes.contains(column.getName()))
return;
tmp.add(new OperationColumn(null, column));
});
return;
}
if (include.contains(".")) {
String[] columnInfo = include.split("[.]");
RDBTableMetaData table = metaData.getDatabaseMetaData().getTableMetaData(columnInfo[0]);
String tname = null;
if (null == table) {
Correlation correlation = metaData.getCorrelation(columnInfo[0]);
if (correlation != null) {
table = metaData.getDatabaseMetaData().getTableMetaData(correlation.getTargetTable());
tname = correlation.getAlias();
}
}
tname = table.getAlias();
}
if (null == table) return;
if (columnInfo[1].equals("*"
String finalName = tname;
table.getColumns().forEach(column -> {
if
|| excludes.contains(finalName + "." + column.getName())
|| excludes.contains(finalName + "." + column.getAlias()))
return;
tmp.add(new OperationColumn(finalName, column));
});
return;
} else {
RDBColumnMetaData column = metaData.findColumn(include);
if (null != column) {
if (excludes.contains(column.getFullAliasName()) || excludes.contains(column.getFullName()))
return;
tmp.add(new OperationColumn(tname, column));
}
}
} else {
RDBColumnMetaData column = metaData.findColumn(include);
if (null != column) {
if (excludes.contains(column.getAlias()) || excludes.contains(column.getName()))
return;
tmp.add(new OperationColumn(column.getTableMetaData().getAlias(), column));
}
}
});
}
if (tmp.isEmpty()) throw new UnsupportedOperationException("未找到任何查询字段!");
return tmp;
}
}
拼接sql准备:
package org.hsweb.ezorm.rdb.render;
import java.util.LinkedList;
/**
* Created by 浩 on 2015-11-07 0007.
*/
public class SqlAppender extends LinkedList<String> {
public SqlAppender() {
}
public SqlAppender(String sql) {
add(sql);
}
public SqlAppender add(Object... str) {
for (Object s : str) {
this.add(String.valueOf(s));
}
return this
}
public SqlAppender addEdSpc(Object... str) {
for (Object s : str) {
this
}
this.add(" ");
return this;
}
/**
* 接入sql语句，并自动加入空格
*
* @param str
* @return
*/
public SqlAppender addSpc(Object... str) {
for (Object s : str) {
this.add(s);
this.add(" ");
}
return this;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
this.forEach(builder::append);
return builder.toString();
}
}
where语句的共同部分进行抽象首先是Dialect的设定:
接口:
package org.hsweb.ezorm.rdb.render.dialect;
import org.hsweb.ezorm.core.param.Term;
import org.hsweb.ezorm.rdb.executor.SqlExecutor;
import org.hsweb.ezorm.rdb.meta.RDBColumnMetaData;
import org.hsweb.ezorm.rdb.meta.parser.TableMetaParser;
import org.hsweb.ezorm.rdb.render.SqlAppender;
import org.hswebframwork.utils.StringUtils;
import java.sql.JDBCType;
import java.util.regex.Matcher;
import static org.hsweb.ezorm.rdb.executor.AbstractJdbcSqlExecutor.APPEND_PATTERN;
import static org.hsweb.ezorm.rdb.executor.AbstractJdbcSqlExecutor.PREPARED_PATTERN;
public interface Dialect {
interface TermTypeMapper {
SqlAppender accept(String wherePrefix, Term term, RDBColumnMetaData column, String tableAlias);
static TermTypeMapper sql(String sql) {
return (wherePrefix, term, column, tableAlias) -> new SqlAppender(sql);
}
static TermTypeMapper sql(String sql, Object param) {
return (wherePrefix, term, column, tableAlias) -> {
Object finalParam = param;
String template = sql;
//?方式预编译
if (template.contains("?")) {
int index = 0;
while (template.contains("?")) {
template = template.replaceFirst("\\?", "#\\{[" + index++ + "]}");
}
} else if (finalParam instanceof Object[]) {
Object[] array = ((Object[]) finalParam);
if (array.length == 1) {
finalParam = array[0];
}
}
Matcher prepared_matcher = PREPARED_PATTERN.matcher(template);
Matcher append_matcher = APPEND_PATTERN.matcher(template);
term.setValue(finalParam);
while (append_matcher.find()) {
String group = append_matcher.group();
String reg = StringUtils.concat("\\$\\{", group.replace("$", "\\$").replace("[", "\\[").replace("]", "\\]"), "}");
String target = StringUtils.concat("\\$\\{", wherePrefix, group.startsWith("[") ? ".value" : ".value.", group, "}");
template = template.replaceFirst(reg, target);
}
while (prepared_matcher.find()) {
String group = prepared_matcher.group();
template = template.replaceFirst(StringUtils.concat("#\\{", group.replace("$", "\\$").replace("[", "\\[").replace("]", "\\]"), "}"),
StringUtils.concat("#\\{", wherePrefix, group.startsWith("[") ? ".value" : ".value.", group, "}"));
}
return new SqlAppender(template);
};
}
}
interface DataTypeMapper {
String getDataType(RDBColumnMetaData columnMetaData);
}
interface ColumnMapper {
String getColumn(RDBColumnMetaData columnMetaData);
}
void setTermTypeMapper(String termType, TermTypeMapper mapper);
void setDataTypeMapper(JDBCType jdbcType, DataTypeMapper mapper);
void setColumnMapper(String columnType, ColumnMapper mapper);
String getQuoteStart();
String getQuoteEnd();
SqlAppender buildCondition(String wherePrefix, Term term, RDBColumnMetaData RDBColumnMetaData, String tableAlias);
String buildDataType(RDBColumnMetaData columnMetaData);
String doPaging(String sql, int pageIndex, int pageSize);
boolean columnToUpperCase();
default String buildColumnName(String tableName, String columnName) {
if (StringUtils.isNullOrEmpty(tableName)) {
return StringUtils.concat(getQuoteStart(), columnToUpperCase() ? columnName.toUpperCase() : columnName, getQuoteEnd());
}
return StringUtils.concat(tableName, ".", getQuoteStart(), columnToUpperCase() ? columnName.toUpperCase() : columnName, getQuoteEnd());
}
TableMetaParser getDefaultParser(SqlExecutor sqlExecutor);
Dialect MYSQL = new MysqlDialect();
Dialect ORACLE = new OracleDialect();
Dialect H2 = new H2Dialect();
Dialect MSSQL = new MSSQLDialect();
}
默认实现DefaultDialect
其中TableMetaParser默认使用OracleTableMetaParser
package org.hsweb.ezorm.rdb.render.dialect;
import org.hsweb.ezorm.core.param.SqlTerm;
import org.hsweb.ezorm.core.param.Term;
import org.hsweb.ezorm.core.param.TermType;
import org.hsweb.ezorm.rdb.executor.SqlExecutor;
import org.hsweb.ezorm.rdb.meta.RDBColumnMetaData;
import org.hsweb.ezorm.rdb.meta.parser.OracleTableMetaParser;
import org.hsweb.ezorm.rdb.meta.parser.TableMetaParser;
import org.hsweb.ezorm.rdb.render.SqlAppender;
import org.hswebframwork.utils.StringUtils;
import java.sql.JDBCType;
import java.util.*;
/**
*
*/
public abstract class DefaultDialect implements Dialect {
protected Map<String, TermTypeMapper> termTypeMappers = new HashMap<>();
protected Map<String, DataTypeMapper> dataTypeMappers = new HashMap<>();
protected DataTypeMapper defaultDataTypeMapper = null;
static final List<JDBCType> numberJdbcType = Arrays.asList(JDBCType.NUMERIC, JDBCType.INTEGER, JDBCType.BIGINT, JDBCType.TINYINT, JDBCType.DOUBLE, JDBCType.FLOAT);
public DefaultDialect() {
//默认查询条件支持
termTypeMappers.put(TermType.eq, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), "=#{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.not, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), "!=#{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.like, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), " LIKE #{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.nlike, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), " NOT LIKE #{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.isnull, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), " IS NULL"));
termTypeMappers.put(TermType.notnull, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), " IS NOT NULL"));
termTypeMappers.put(TermType.gt, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), ">#{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.lt, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), "<#{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.gte, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), ">=#{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.lte, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), "<=#{", wherePrefix, ".value}"));
termTypeMappers.put(TermType.empty, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), "=''"));
termTypeMappers.put(TermType.nempty, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(buildColumnName(tableAlias, column.getName()), "!=''"));
termTypeMappers.put(TermType.func, (wherePrefix, term, column, tableAlias) ->
new SqlAppender().add(term.getValue()));
termTypeMappers.put(TermType.btw, (wherePrefix, term, column, tableAlias) -> {
SqlAppender sqlAppender = new SqlAppender();
List<Object> objects = param2list(term.getValue(), column);
if (objects.size() == 1)
objects.add(objects.get(0));
term.setValue(objects);
sqlAppender.add(buildColumnName(tableAlias, column.getName()), " ").addSpc("BETWEEN")
.add("#{", wherePrefix, ".value[0]}")
.add(" AND ", "#{", wherePrefix, ".value[1]}");
return sqlAppender;
});
termTypeMappers.put(TermType.nbtw, (wherePrefix, term, column, tableAlias) ->
{
SqlAppender sqlAppender = new SqlAppender();
List<Object> objects = param2list(term.getValue(), column);
if (objects.size() == 1
objects.add(objects.get(0));
term.setValue(objects);
sqlAppender.add(buildColumnName(tableAlias, column.getName()), " ").addSpc("NOT BETWEEN")
.add("#{", wherePrefix, ".value[0]}")
.add(" AND ", "#{", wherePrefix, ".value[1]}");
return sqlAppender;
});
termTypeMappers.put(TermType.in, (wherePrefix, term, column, tableAlias) -> {
List<Object> values = param2list(term.getValue(), column);
term.setValue(values);
SqlAppender appender = new SqlAppender();
appender.add(tableAlias, ".").addSpc(column.getName()).add("IN(");
for (int i = 0; i < values.size(); i++) {
appender.add("#{", wherePrefix, ".value[", i, "]}", ",");
}
appender.removeLast();
appender.add(")");
return appender;
});
termTypeMappers.put(TermType.nin, (wherePrefix, term, column, tableAlias) -> {
List<Object> values = param2list(term.getValue(), column);
term.setValue(values);
SqlAppender appender = new SqlAppender();
appender.add(tableAlias, ".").addSpc(column.getName()).add("NOT IN(");
for (int i = 0; i < values.size(); i++) {
appender.add("#{", wherePrefix, ".value[", i, "]}", ",");
}
appender.removeLast();
appender.add(")");
return appender;
});
}
@Override
public SqlAppender buildCondition(String wherePrefix, Term term, RDBColumnMetaData RDBColumnMetaData, String tableAlias) {
if (term instanceof SqlTerm) {
TermTypeMapper mapper = TermTypeMapper.sql(term.getColumn(), term.getValue());
return
}
if (term.getValue() instanceof TermTypeMapper) {
return
}
TermTypeMapper mapper = termTypeMappers.get(term.getTermType());
if (mapper == null) mapper = termTypeMappers.get(TermType.eq);
return mapper.accept(wherePrefix, term, RDBColumnMetaData, tableAlias);
}
protected List<Object> param2list(Object value, RDBColumnMetaData columnMetaData) {
if (value == null) return new ArrayList<>();
if (value instanceof List) return ((List) value);
if (value instanceof Collection) return new ArrayList<>(((Collection) value));
if (!(value instanceof Collection)) {
if (value instanceof String) {
String[] arr = ((String) value).split("[, ;]");
Object[] objArr = new Object[arr.length];
for (int i = 0; i < arr.length; i++) {
String str = arr[i];
Object val = str;
//数字类型
if (numberJdbcType.contains(columnMetaData.getJdbcType())) {
if (StringUtils.isInt(str))
val = StringUtils.toInt(str);
else if (StringUtils.isDouble(str))
val = StringUtils.toDouble(str);
}
objArr[i] = val;
}
return
} else if (value.getClass().isArray()) {
return Arrays.asList(((Object[]) value));
} else {
return new ArrayList<>(Arrays.asList(value));
}
}
return new ArrayList<>();
}
@Override
public void setTermTypeMapper(String termType, TermTypeMapper mapper) {
termType = termType.toLowerCase();
termTypeMappers.put(termType, mapper);
}
@Override
public void setDataTypeMapper(JDBCType jdbcType, DataTypeMapper mapper) {
dataTypeMappers.put(jdbcType.getName(), mapper);
}
@Override
public void setColumnMapper(String columnType, ColumnMapper mapper) {
// TODO: 16-10-28
}
@Override
public String buildDataType(RDBColumnMetaData columnMetaData) {
if (columnMetaData.getJdbcType() == null) return null;
DataTypeMapper mapper = dataTypeMappers.get(columnMetaData.getJdbcType().getName());
if (null == mapper) mapper = defaultDataTypeMapper;
return mapper.getDataType(columnMetaData);
}
@Override
public TableMetaParser getDefaultParser(SqlExecutor sqlExecutor) {
return new OracleTableMetaParser(sqlExecutor);
}
}
具体到相应数据库，这里拿MysqlDialect为例:
其实主要还是设置对应的数据类型
package org.hsweb.ezorm.rdb.render.dialect;
import org.hsweb.ezorm.rdb.executor.SqlExecutor;
import org.hsweb.ezorm.rdb.meta.parser.MysqlTableMetaParser;
import org.hsweb.ezorm.rdb.meta.parser.TableMetaParser;
import org.hswebframwork.utils.StringUtils;
import java.sql.JDBCType;
public class MysqlDialect extends DefaultDialect {
protected MysqlDialect() {
defaultDataTypeMapper = (meta) -> meta.getJdbcType().getName().toLowerCase();
setDataTypeMapper(JDBCType.CHAR, (meta) -> StringUtils.concat("char(", meta.getLength(), ")"));
setDataTypeMapper(JDBCType.VARCHAR, (meta) -> StringUtils.concat("varchar(", meta.getLength(), ")"));
setDataTypeMapper(JDBCType.TIMESTAMP, (meta) -> "datetime");
setDataTypeMapper(JDBCType.TIME, (meta) -> "time");
setDataTypeMapper(JDBCType.DATE, (meta) -> "year");
setDataTypeMapper(JDBCType.CLOB, (meta) -> "text");
setDataTypeMapper(JDBCType.LONGVARBINARY, (meta) -> "longblob");
setDataTypeMapper(JDBCType.LONGVARCHAR, (meta) -> "longtext");
setDataTypeMapper(JDBCType.BLOB, (meta) -> "blob");
setDataTypeMapper(JDBCType.BIGINT, (meta) -> "bigint");
setDataTypeMapper(JDBCType.DOUBLE, (meta) -> "double");
setDataTypeMapper(JDBCType.INTEGER, (meta) -> "int");
setDataTypeMapper(JDBCType.NUMERIC, (meta) -> StringUtils.concat("decimal(", meta.getPrecision(), ",", meta.getScale(), ")"));
setDataTypeMapper(JDBCType.DECIMAL, (meta) -> StringUtils.concat("decimal(", meta.getPrecision(), ",", meta.getScale(), ")"));
setDataTypeMapper(JDBCType.TINYINT, (meta) -> "tinyint");
setDataTypeMapper(JDBCType.BIGINT, (meta) -> "bigint");
setDataTypeMapper(JDBCType.OTHER, (meta) -> "other");
}
@Override
public String getQuoteStart() {
return "`";
}
@Override
public String getQuoteEnd() {
return "`";
}
@Override
public String doPaging(String sql, int pageIndex, int pageSize) {
return new StringBuilder(sql)
.append(" limit ")
.append(pageSize * pageIndex)
.append(",")
.append(pageSize).toString();
}
@Override
public boolean columnToUpperCase() {
return false;
}
@Override
public TableMetaParser getDefaultParser(SqlExecutor sqlExecutor) {
return new MysqlTableMetaParser(sqlExecutor);
}
}
这里漏说了一个表元素的解析:
先设计个接口
package org.hsweb.ezorm.rdb.meta.parser;
import org.hsweb.ezorm.rdb.meta.RDBTableMetaData;
import java.sql.SQLException;
import java.util.List;
/**
* Created by zhouhao on 16-6-5.
*/
public interface TableMetaParser {
RDBTableMetaData parse(String name);
boolean tableExists(String name);
List<RDBTableMetaData> parseAll() throws SQLException;
}
抽出抽象部分:
package org.hsweb.ezorm.rdb.meta.parser;
import org.hsweb.commons.StringUtils;
import org.hsweb.ezorm.core.ObjectWrapper;
import org.hsweb.ezorm.rdb.executor.SqlExecutor;
import org.hsweb.ezorm.rdb.meta.RDBColumnMetaData;
import org.hsweb.ezorm.rdb.meta.RDBTableMetaData;
import org.hsweb.ezorm.rdb.meta.expand.SimpleMapWrapper;
import org.hsweb.ezorm.rdb.render.dialect.Dialect;
import org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL;
import java.sql.JDBCType;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.stream.Collectors;
public abstract class AbstractTableMetaParser implements TableMetaParser {
Map<String, JDBCType> jdbcTypeMap = new HashMap<>();
Map<JDBCType, Class> javaTypeMap = new HashMap<>();
protected SqlExecutor sqlExecutor;
abstract Dialect getDialect();
public AbstractTableMetaParser(SqlExecutor sqlExecutor) {
this.sqlExecutor = sqlExecutor;
}
abstract String getTableMetaSql(String tname);
abstract String getTableCommentSql(String tname);
abstract String getAllTableSql();
abstract String getTableExistsSql();
@Override
public boolean tableExists(String name) {
try {
Map<String, Object> param = new HashMap<>();
param.put("table", name);
Map<String, Object> res = sqlExecutor.single(new SimpleSQL(getTableExistsSql(), param), new LowerCasePropertySimpleMapWrapper());
return res.get("total") != null && StringUtils.toInt(res.get("total")) > 0;
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public RDBTableMetaData parse(String name) {
if (!tableExists(name)) return null;
RDBTableMetaData metaData = new RDBTableMetaData();
metaData.setName(name);
metaData.setAlias(name);
Map<String, Object> param = new HashMap<>();
param.put("table", name);
try {
List<RDBColumnMetaData> metaDatas = sqlExecutor.list(new SimpleSQL(getTableMetaSql(name), param), new RDBColumnMetaDataWrapper());
metaDatas.forEach(metaData::addColumn);
Map<String, Object> comment = sqlExecutor.single(new SimpleSQL(getTableCommentSql(name), param), new LowerCasePropertySimpleMapWrapper());
if (null != comment && comment.get("comment") != null) {
metaData.setComment(String.valueOf(comment.get("comment")));
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
return metaData;
}
@Override
public List<RDBTableMetaData> parseAll() throws SQLException {
List<Map<String, Object>> tables = sqlExecutor.list(new SimpleSQL(getAllTableSql()), new LowerCasePropertySimpleMapWrapper());
return tables.stream()
.map(map -> (String) map.get("name"))
.filter(Objects::nonNull)
.map(this::parse).filter(Objects::nonNull)
.collect(Collectors.toList());
}
class LowerCasePropertySimpleMapWrapper extends SimpleMapWrapper {
@Override
public void wrapper(Map<String, Object> instance, int index, String attr, Object value) {
attr = attr.toLowerCase();
super.wrapper(instance, index, attr, value);
}
}
class RDBColumnMetaDataWrapper implements ObjectWrapper<RDBColumnMetaData> {
@Override
public Class<RDBColumnMetaData> getType() {
return RDBColumnMetaData.class;
}
@Override
public RDBColumnMetaData newInstance() {
return new RDBColumnMetaData();
}
@Override
public void wrapper(RDBColumnMetaData instance, int index, String attr, Object value) {
String stringValue;
if (value instanceof String) {
stringValue = ((String) value).toLowerCase();
} else {
stringValue = value == null ? "" : value.toString();
}
if (attr.equalsIgnoreCase("name")) {
instance.setName(stringValue);
instance.setProperty("old-name", stringValue);
} else if (attr.equalsIgnoreCase("comment")) {
instance.setComment(stringValue);
} else {
if (attr.toLowerCase().equals("not-null")) {
value = "1".equals(stringValue);
instance.setNotNull((boolean) value);
}
instance.setProperty(attr.toLowerCase(), value);
}
}
@Override
public void done(RDBColumnMetaData instance) {
String data_type = instance.getProperty("data_type").toString().toLowerCase();
int len = instance.getProperty("data_length").toInt();
int data_precision = instance.getProperty("data_precision").toInt();
int data_scale = instance.getProperty("data_scale").toInt();
if (data_type == null) {
data_type = "varchar";
}
instance.setLength(len);
instance.setPrecision(data_precision);
instance.setScale(data_scale);
JDBCType jdbcType;
try {
jdbcType = JDBCType.valueOf(data_type.toUpperCase());
} catch (Exception e) {
jdbcType = jdbcTypeMap.get(data_type);
}
Class javaType = javaTypeMap.get(jdbcType);
instance.setJdbcType(jdbcType);
instance.setJavaType(javaType);
instance.setDataType(getDialect().buildDataType(instance));
}
}
}
具体数据库完成具体部分MysqlTableMetaParser为例:
package org.hsweb.ezorm.rdb.meta.parser;
import org.hsweb.ezorm.rdb.executor.SqlExecutor;
import org.hsweb.ezorm.rdb.render.dialect.Dialect;
import org.hsweb.ezorm.rdb.render.dialect.MysqlDialect;
import java.sql.JDBCType;
/**
* Created by zhouhao on 16-6-5.
*/
public class MysqlTableMetaParser extends AbstractTableMetaParser {
static final String TABLE_META_SQL = " select " +
"column_name as `name`, " +
"data_type as `data_type`, " +
"character_maximum_length as `data_length`, " +
"numeric_precision as `data_precision`, " +
"numeric_scale as `data_scale`, " +
"column_comment as `comment`, " +
"case when is_nullable='YES' then 0 else 1 end as 'not-null' " +
"from information_schema.columns where table_schema=database() and table_name=#{table}";
static final String TABLE_COMMENT_SQL = " select " +
"table_comment as `comment` " +
"from information_schema.tables where table_name=#{table}";
static final String ALL_TABLE_SQL = "select table_name as `name` from information_schema.`TABLES` where table_schema=database()";
static final String TABLE_EXISTS_SQL = "select count(1) as 'total' from information_schema.`TABLES` where table_schema=database() and table_name=#{table}";
public MysqlTableMetaParser(SqlExecutor sqlExecutor) {
super(sqlExecutor);
jdbcTypeMap.put("int", JDBCType.INTEGER);
jdbcTypeMap.put("year", JDBCType.TIME);
jdbcTypeMap.put("datetime", JDBCType.TIMESTAMP);
jdbcTypeMap.put("text", JDBCType.CLOB);
}
@Override
Dialect getDialect() {
return Dialect.MYSQL;
}
@Override
String
return TABLE_META_SQL;
}
@Override
String getTableCommentSql(String tname)
return
}
@Override
String getAllTableSql()
return
}
@Override
String getTableExistsSql()
return
}
}
其次是SimpleWhereSqlBuilder：
package org.hsweb.ezorm.rdb.render.support.simple;
import org.hsweb.ezorm.core.param.SqlTerm;
import org.hsweb.ezorm.core.param.Term;
import org.hsweb.ezorm.core.param.TermType;
import org.hsweb.ezorm.rdb.meta.Correlation;
import org.hsweb.ezorm.rdb.meta.RDBColumnMetaData;
import org.hsweb.ezorm.rdb.meta.RDBTableMetaData;
import org.hsweb.ezorm.rdb.render.SqlAppender;
import org.hsweb.ezorm.rdb.render.dialect.Dialect;
import org.hswebframwork.utils.StringUtils;
import java.util.List;
import java.util.Set;
public abstract class SimpleWhereSqlBuilder {
protected String getTableAlias(RDBTableMetaData metaData, String field) {
if (field.contains("."))
field = field.split("[.]")[0];
else return metaData.getAlias();
Correlation correlation = metaData.getCorrelation(field);
if (correlation != null) return correlation.getAlias();
return metaData.getAlias();
}
public void buildWhere(RDBTableMetaData metaData, String prefix,
List<Term> terms, SqlAppender appender,
Set<String> needSelectTable) {
if (terms == null || terms.isEmpty()) return;
int index = -1;
String prefixTmp = StringUtils.concat(prefix, StringUtils.isNullOrEmpty(prefix) ? "" : ".");
for (Term term : terms) {
index++;
boolean nullTerm = StringUtils.isNullOrEmpty(term.getColumn());
RDBColumnMetaData column = metaData.findColumn(term.getColumn());
//不是空条件 也不是可选字段
if (!nullTerm && column == null && term.getTermType() != TermType.func && !(term instanceof SqlTerm)) continue;
//不是空条件，值为空
if (!nullTerm && StringUtils.isNullOrEmpty(term.getValue())) continue;
//是空条件，但是无嵌套
if (nullTerm && term.getTerms().isEmpty()) continue;
String tableAlias = null;
if (column != null) {
tableAlias = getTableAlias(metaData, term.getColumn());
needSelectTable.add(tableAlias);
//转换参数的值
term.setValue(transformationValue(column, term.getValue()));
}
//用于sql预编译的参数名
prefix = StringUtils.concat(prefixTmp, "terms[", index, "]");
//添加类型，and 或者 or
appender.add(StringUtils.concat(" ", term.getType().toString().toUpperCase(), " "));
if (!term.getTerms().isEmpty()) {
//构建嵌套的条件
SqlAppender nest = new SqlAppender();
buildWhere(metaData, prefix, term.getTerms(), nest, needSelectTable);
//如果嵌套结果为空,
if (nest.isEmpty()) {
appender.removeLast();//删除最后一个（and 或者 or）
continue;
}
if (nullTerm) {
//删除 第一个（and 或者 or）
nest.removeFirst();
}
appender.add("(");
if (!nullTerm)
appender.add(getDialect().buildCondition(prefix, term, column, tableAlias));
appender.addAll(nest);
appender.add(")");
} else {
if (!nullTerm)
appender.add(getDialect().buildCondition(prefix, term, column, tableAlias));
}
}
}
protected Object transformationValue(RDBColumnMetaData column, Object value) {
if (value != null && column.getValueConverter() != null) {
value = column.getValueConverter().getData(value);
}
if (value != null && column.getOptionConverter() != null) {
Object tmp = column.getOptionConverter().converterData(value);
if (null != tmp) value = tmp;
}
// JDBCType type = column.getJdbcType();
//
// if (type == null) return value;
// switch (type) {
// case INTEGER:
// case NUMERIC:
// if (StringUtils.isInt(type)) return StringUtils.toInt(value);
// if (StringUtils.isDouble(type)) return StringUtils.toDouble(value);
// break;
// case TIMESTAMP:
// case TIME:
// case DATE:
// if (!(value instanceof Date)) {
// String strValue = String.valueOf(value);
// Date date = DateTimeUtils.formatUnknownString2Date(strValue);
// if (date != null) return date;
// }
// break;
// }
return value;
}
public abstract Dialect getDialect();
}
拼接出的sql封装：
关联查询sql:
package org.hsweb.ezorm.rdb.executor;
/**
* Created by 浩 on 2015-11-06 0006.
*/
public class BindSQL {
private SQL sql;
private String toField;
public SQL getSql() {
return sql;
}
public void setSql(SQL sql) {
this.sql = sql;
}
public String getToField() {
return toField;
}
public void setToField(String toField) {
this.toField = toField;
}
}
定义SQL封装接口:
package org.hsweb.ezorm.rdb.executor;
import java.util.List;
public interface SQL {
/**
* 获取sql语句模板
*
* @return sql语句模板
*/
String getSql();
/**
* 获取预编译参数
*
* @return
*/
Object getParams();
/**
* 获取关联查询的sql
*
* @return
*/
List<BindSQL> getBinds();
int size();
}
具体实现:
package org.hsweb.ezorm.rdb.render.support.simple;
import org.hsweb.ezorm.rdb.executor.BindSQL;
import org.hsweb.ezorm.rdb.executor.SQL;
import java.util.HashMap;
import java.util.List;
/**
* Created by zhouhao on 16-6-4.
*/
public class SimpleSQL implements SQL {
private String sql;
private Object param;
private List<BindSQL> bindSQLs;
public SimpleSQL(String sql, Object param) {
this.sql = sql;
this.param = param;
}
public SimpleSQL(String sql) {
this.sql = sql;
this.param = new HashMap<>();
}
@Override
public String getSql() {
return sql;
}
@Override
public Object getParams() {
return param;
}
@Override
public List<BindSQL> getBinds() {
return bindSQLs;
}
public void setBindSQLs(List<BindSQL> bindSQLs) {
this.bindSQLs = bindSQLs;
}
@Override
public int size() {
return bindSQLs == null ? 1 : bindSQLs.size() + 1;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append(sql).append("\n").append(param);
return builder.toString();
}
}
回到SimpleUpdateSqlRender实现:
package org.hsweb.ezorm.rdb.render.support.simple;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.hsweb.ezorm.core.param.Term;
import org.hsweb.ezorm.core.param.UpdateParam;
import org.hsweb.ezorm.rdb.executor.SQL;
import org.hsweb.ezorm.rdb.meta.RDBColumnMetaData;
import org.hsweb.ezorm.rdb.meta.RDBTableMetaData;
import org.hsweb.ezorm.rdb.render.SqlAppender;
import org.hsweb.ezorm.rdb.render.dialect.Dialect;
import java.util.*;
import java.util.stream.Collectors;
/**
* Created by zhouhao on 16-6-4.
*/
public class SimpleUpdateSqlRender extends CommonSqlRender<UpdateParam> {
class SimpleUpdateSqlRenderProcess extends SimpleWhereSqlBuilder {
private RDBTableMetaData metaData;
private UpdateParam param;
private List<OperationColumn> updateField;
private SqlAppender whereSql = new SqlAppender();
private Set<String> conditionTable = new LinkedHashSet<>();
PropertyUtilsBean propertyUtils = BeanUtilsBean.getInstance().getPropertyUtils();
public SimpleUpdateSqlRenderProcess(RDBTableMetaData metaData, UpdateParam param) {
this.metaData = metaData;
this.param = param.clone();
//先得到条件
List<Term> terms = param.getTerms();
//对包含条件进行过滤转换得到一个list
terms = terms.stream().filter(term -> term.getColumn() == null || !term.getColumn().contains(".")).collect(Collectors.toList());
param.setTerms(terms);
//解析要操作的字段
this.updateField = parseOperationField(metaData, param);
//解析查询条件
buildWhere(metaData, "", param.getTerms(), whereSql, conditionTable);
if (!whereSql.isEmpty()) whereSql.removeFirst();
}
public SQL process() {
SqlAppender appender = new SqlAppender();
appender.add("UPDATE ", metaData.getName(), " ", metaData.getAlias(), " SET ");
byte[] bytes = new byte[1];
Map<String, Object> valueProxy = new HashMap<>();
updateField.forEach(operationColumn -> {
RDBColumnMetaData column = operationColumn.getRDBColumnMetaData();
if (column.getProperty("read-only").isTrue()) return;
try {
String dataProperty = column.getAlias();
Object value = null;
try {
value = propertyUtils.getProperty(param.getData(), dataProperty);
} catch (Exception e) {
}
if (value == null && !column.getAlias().equals(column.getName())) {
dataProperty = column.getName();
try {
value = propertyUtils.getProperty(param.getData(), dataProperty);
} catch (Exception e) {
}
}
if (value == null) {
if (logger.isInfoEnabled())
logger.info("跳过修改列:[{}], 属性[{}]为null!", column.getName(), column.getAlias());
return;
}
if (column.getValueConverter() != null) {
Object new_value = column.getValueConverter().getData(value);
if (column.getOptionConverter() != null) {
new_value = column.getOptionConverter().converterData(new_value);
}
if (value != new_value && !value.equals(new_value)) {
// propertyUtils.setProperty(param.getData(), dataProperty, new_value);
value = new_value;
}
}
valueProxy.put(dataProperty, value);
appender.add(dialect.buildColumnName(null, column.getName()), "=")
.addAll(getParamString("data.".concat(dataProperty), column));
appender.add(",");
bytes[0]++;
} catch (Exception e) {
if (logger.isInfoEnabled())
logger.info("跳过修改列:[{}], 可能属性[{}]不存在!", column.getName(), column.getAlias());
}
});
if (bytes[0] == 0) throw new IndexOutOfBoundsException("没有列被修改!");
appender.removeLast();
if (whereSql.isEmpty()) {
throw new UnsupportedOperationException("禁止执行未设置任何条件的修改操作!");
}
appender.add(" WHERE ", "").addAll(whereSql);
String sql = appender.toString();
param.setData(valueProxy);
SimpleSQL simpleSQL = new SimpleSQL(sql, param);
return simpleSQL;
}
@Override
public Dialect getDialect() {
return dialect;
}
}
protected SqlAppender getParamString(String paramName, RDBColumnMetaData rdbColumnMetaData) {
return new SqlAppender().add("#{", paramName, "}");
}
@Override
public SQL render(RDBTableMetaData metaData, UpdateParam param) {
return new SimpleUpdateSqlRenderProcess(metaData, param).process();
}
public SimpleUpdateSqlRender(Dialect dialect) {
this.dialect = dialect;
}
private Dialect dialect;
public Dialect getDialect() {
return dialect;
}
public void setDialect(Dialect dialect) {
this.dialect = dialect;
}
}
终于得到 了sql，最后，验证之后执行sql语句:设定执行器接口SqlExecutor:
/*
* Copyright 2016 http://github.com/hs-web
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.hsweb.ezorm.rdb.executor;
import org.hsweb.ezorm.core.ObjectWrapper;
import org.hsweb.ezorm.rdb.meta.expand.SimpleMapWrapper;
import org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* SQL执行器,用于执行sql
*
* @author zhouhao
* @since 1.0
*/
public interface SqlExecutor {
SimpleMapWrapper mapWrapper = new SimpleMapWrapper();
/**
* 传入SQL对象和对象包装器执行查询,将查询结果通过对象包装器进行包装后返回
*
* @param sql sql对象 参照{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
* @param wrapper 执行结果对象包装器 参照{@link org.hsweb.ezorm.rdb.meta.expand.SimpleMapWrapper}
* @param <T> 查询结果类型泛型
* @return 查询结果
* @throws SQLException 执行查询异常
*/
<T> List<T> list(SQL sql, ObjectWrapper<T> wrapper) throws SQLException;
/**
* 传入SQL对象和对象包装器执行查询,将查询结果通过对象包装器进行包装后返回
* 只返回单个结果,如果sql结果存在多个值,则返回首个值
*
* @param sql sql对象 参照{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
* @param wrapper 对象包装其
* @param <T> 查询结果类型泛型
* @return 查询结果
* @throws SQLException 执行查询异常
*/
<T> T single(SQL sql, ObjectWrapper<T> wrapper) throws SQLException;
/**
* 执行sql
*
* @param sql sql对象 参照{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
* @throws SQLException 执行异常
*/
void exec(SQL sql) throws SQLException;
/**
* 执行update
*
* @param sql sql对象 参照{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
* @return 执行sql后影响的行数
* @throws SQLException 执行异常
*/
int update(SQL sql) throws SQLException;
/**
* 执行delete
*
* @param sql sql对象 参照{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
* @return 执行sql后影响的行数
* @throws SQLException 执行异常
*/
int delete(SQL sql) throws SQLException;
/**
* 执行insert
*
* @param sql sql对象 参照{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
* @return 执行sql后影响的行数
* @throws SQLException 执行异常
*/
int insert(SQL sql) throws SQLException;
boolean tableExists(String tableName) throws SQLException;
default List<Map<String, Object>> list(SQL sql) throws SQLException {
return list(sql, mapWrapper);
}
default List<Map<String, Object>> list(String sql, Object params) throws SQLException {
return list(new SimpleSQL(sql, params));
}
default List<Map<String, Object>> list(String sql) throws SQLException {
return list(new SimpleSQL(sql));
}
default Map<String, Object> single(SQL sql) throws SQLException {
return single(sql, mapWrapper);
}
default Map<String, Object> single(String sql, Object params) throws SQLException {
return single(new SimpleSQL(sql, params));
}
default Map<String, Object> single(String sql) throws SQLException {
return single(new SimpleSQL(sql));
}
default int insert(String sql, Object params) throws SQLException {
return insert(new SimpleSQL(sql, params));
}
default int update(String sql, Object params) throws SQLException {
return update(new SimpleSQL(sql, params));
}
default int update(String sql) throws SQLException {
return update(new SimpleSQL(sql));
}
default int delete(String sql, Object params) throws SQLException {
return delete(new SimpleSQL(sql, params));
}
default int delete(String sql) throws SQLException {
return delete(new SimpleSQL(sql));
}
default void exec(String sql) throws SQLException {
exec(new SimpleSQL(sql));
}
}
JDBC 通用sql执行器
/*
* Copyright 2016 http://github.com/hs-web
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.hsweb.ezorm.rdb.executor;
import org.apache.commons.beanutils.BeanUtilsBean;
import org.apache.commons.beanutils.PropertyUtilsBean;
import org.hsweb.ezorm.core.ObjectWrapper;
import org.hsweb.ezorm.core.param.Term;
import org.hswebframwork.utils.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.ByteArrayInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* JDBC 通用sql执行器,用于执行sql.支持参数化预编译
*
* @author zhouhao
* @since 1.0
*/
public abstract class AbstractJdbcSqlExecutor implements SqlExecutor {
protected Logger logger = LoggerFactory.getLogger(this.getClass());
/**
* 获取jdbc链接,由子类实现
*
* @return jdbc 链接
*/
public abstract Connection getConnection();
/**
* 直接拼接sql的编译表达式: ${}
*
* @since 1.0
*/
public static final Pattern APPEND_PATTERN = Pattern.compile("(?<=\\$\\{)(.+?)(?=\\})");
/**
* 进行参数预编译的表达式:#{}
*
* @since 1.0
*/
public static final Pattern PREPARED_PATTERN = Pattern.compile("(?<=#\\{)(.+?)(?=\\})");
/**
* 对象属性操作工具
*
* @see PropertyUtilsBean
*/
protected PropertyUtilsBean propertyUtils = BeanUtilsBean.getInstance().getPropertyUtils();
protected Object getSqlParamValue(Object param, String paramName) {
try {
Object obj = propertyUtils.getProperty(param, paramName);
if (obj instanceof Term)
obj = ((Term) obj).getValue();
return obj;
} catch (Exception e) {
logger.warn("获取sql参数失败", e);
}
return null;
}
/**
* 将sql模板编译为sql信息
* 模板语法:${}代表直接拼接sql,#{}使用预编译
* 如: 模板参数为:{name:"张三",age:10},sql为:select * from user where name=#{name} and age=${age}
* 将被编译为:select * from user where name=? and age=10。 参数列表:["张三"]
*
* @param sql sql模板 ,参考{@link org.hsweb.ezorm.rdb.render.support.simple.SimpleSQL}
* @return sql 编译好的信息
*/
public SQLInfo compileSql(SQL sql) {
String sqlTemplate = sql.getSql();
try {
SQLInfo sqlInfo = new SQLInfo();
Object param = sql.getParams();
Matcher prepared_matcher = PREPARED_PATTERN.matcher(sqlTemplate);
Matcher append_matcher = APPEND_PATTERN.matcher(sqlTemplate);
List<Object> params = new LinkedList<>();
//直接拼接sql
while (append_matcher.find()) {
String group = append_matcher.group();
Object obj = getSqlParamValue(param, group);
sqlTemplate = sqlTemplate.replaceFirst(StringUtils.concat("\\$\\{", escapeExprSpecialWord(group), "\\}"), String.valueOf(obj));
}
//参数预编译sql
while (prepared_matcher.find()) {
String group = prepared_matcher.group();
sqlTemplate = sqlTemplate.replaceFirst(StringUtils.concat("#\\{", escapeExprSpecialWord(group), "\\}"), "?");
Object obj = getSqlParamValue(param, group);
params.add(obj);
}
sqlInfo.setSql(sqlTemplate);
sqlInfo.setParam(params.toArray());
return sqlInfo;
} catch (Exception e) {
logger.error("compile sql {} error", sqlTemplate, e);
throw e;
}
}
/**
* 释放连接,在执行完sql后,将释放此链接
*/
public abstract void releaseConnection(Connection connection) throws SQLException;
@Override
public <T> List<T> list(SQL sql, ObjectWrapper<T> wrapper) throws SQLException {
if (sql instanceof EmptySQL) return new ArrayList<>();
//将sql模板编译为可执行的sql
SQLInfo info = compileSql(sql);
printSql(info);//打印sql信息
Connection connection = getConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
List<T> datas = new ArrayList<>();
try {
//预编译SQL
statement = connection.prepareStatement(info.getSql());
this.preparedParam(statement, info);
//执行sql
resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int count = metaData.getColumnCount();
//获取到执行sql后返回的列信息
List<String> headers = new ArrayList<>();
for (int i = 1; i <= count; i++) {
headers.add(metaData.getColumnLabel(i));
}
wrapper.setUp(headers);
int index = 0;
while (resultSet.next()) {
//调用包装器,将查询结果包装为对象
T data = wrapper.newInstance();
for (int i = 0; i < headers.size(); i++) {
Object value = resultSet.getObject(i + 1);
wrapper.wrapper(data, index, headers.get(i), value);
}
index++;
wrapper.done(data);
datas.add(data);
}
if (logger.isDebugEnabled()) {
logger.debug("<== total: {}", index);
}
} finally {
closeResultSet(resultSet);
closeStatement(statement);
//重置JDBC链接
releaseConnection(connection);
}
return datas;
}
protected void closeResultSet(ResultSet resultSet) {
try {
if (null != resultSet)
resultSet.close();
} catch (SQLException e) {
logger.error("close ResultSet error", e);
}
}
protected void closeStatement(Statement statement) {
try {
if (null != statement)
statement.close();
} catch (SQLException e) {
logger.error("close ResultSet error", e);
}
}
@Override
public <T> T single(SQL sql, ObjectWrapper<T> wrapper) throws SQLException {
if (sql instanceof EmptySQL) return null;
SQLInfo info = compileSql(sql);
printSql(info);
Connection connection = getConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
T data = null;
try {
statement = connection.prepareStatement(info.getSql());
//预编译参数
this.preparedParam(statement, info);
resultSet = statement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int count = metaData.getColumnCount();
List<String> headers = new ArrayList<>();
for (int i = 1; i <= count; i++) {
headers.add(metaData.getColumnLabel(i));
}
wrapper.setUp(headers);
int index = 0;
if (resultSet.next()) {
data = wrapper.newInstance();
for (int i = 0; i < headers.size(); i++) {
Object value = resultSet.getObject(i + 1);
wrapper.wrapper(data, index, headers.get(i), value);
}
index++;
wrapper.done(data);
}
if (logger.isDebugEnabled()) {
logger.debug("<== total: {}", index);
}
} finally {
closeResultSet(resultSet);
closeStatement(statement);
releaseConnection(connection);
}
return data;
}
@Override
public void exec(SQL sql) throws SQLException {
if (sql instanceof EmptySQL) return;
SQLInfo info = compileSql(sql);
printSql(info);
Connection connection = getConnection();
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(info.getSql());
//预编译参数
this.preparedParam(statement, info);
statement.execute();
if (sql.getBinds() != null) {
for (BindSQL bindSQL : sql.getBinds()) {
exec(bindSQL.getSql());
}
}
} finally {
closeStatement(statement);
releaseConnection(connection);
}
}
@Override
public int update(SQL sql) throws SQLException {
if (sql instanceof EmptySQL) return 0;
SQLInfo info = compileSql(sql);
printSql(info);
Connection connection = getConnection();
int i = 0;
try {
PreparedStatement statement = connection.prepareStatement(info.getSql());
this.preparedParam(statement, info);
i = statement.executeUpdate();
if (logger.isDebugEnabled())
logger.debug("<== updated: {} rows", i);
closeStatement(statement);
} finally {
releaseConnection(connection);
}
return i;
}
@Override
public int delete(SQL sql) throws SQLException {
if (sql instanceof EmptySQL) return 0;
SQLInfo info = compileSql(sql);
printSql(info);
Connection connection = getConnection();
int i = 0;
try {
PreparedStatement statement = connection.prepareStatement(info.getSql());
this.preparedParam(statement, info);
i = statement.executeUpdate();
if (sql.getBinds() != null) {
for (BindSQL bindSQL : sql.getBinds()) {
i += delete(bindSQL.getSql());
}
return i;
}
if (logger.isDebugEnabled())
logger.debug("<== delete: {} rows", i);
closeStatement(statement);
} finally {
releaseConnection(connection);
}
return i;
}
@Override
public int insert(SQL sql) throws SQLException {
return update(sql);
}
@Override
public boolean tableExists(String tname) throws SQLException {
Connection connection = getConnection();
try {
DatabaseMetaData metaData = connection.getMetaData();
ResultSet resultSet = metaData
.getTables(connection.getCatalog(), null, tname.toUpperCase(), null);
if (resultSet.next()) return true;
else {
resultSet = metaData
.getTables(connection.getCatalog(), null, tname.toLowerCase(), null);
}
if (resultSet.next()) return true;
} finally {
releaseConnection(connection);
}
return false;
}
/**
* 预编译参数
*
* @param statement
* @param info
* @throws Exception
*/
protected void preparedParam(PreparedStatement statement, SQLInfo info) throws SQLException {
int index = 1;
//预编译参数
for (Object object : info.getParam()) {
if (object instanceof Date)
statement.setTimestamp(index++, new java.sql.Timestamp(((Date) object).getTime()));
else if (object instanceof byte[]) {
statement.setBlob(index++, new ByteArrayInputStream((byte[]) object));
} else
statement.setObject(index++, object);
}
}
protected void printSql(SQLInfo info) {
if (logger.isDebugEnabled()) {
logger.debug("==> Preparing: {}", info.getSql());
if (info.getParam() != null && info.getParam().length > 0) {
logger.debug("==> Parameters: {}", info.paramsString());
String sim = info.getSql();
Object[] param = info.getParam();
for (int i = 0; i < param.length; i++) {
Object obj = param[i];
try {
sim = sim.replaceFirst("\\?", obj instanceof Number ? String.valueOf(obj) : "'".concat(escapeExprSpecialWord(String.valueOf(obj))).concat("'"));
} catch (Exception e) {
}
}
logger.debug("==> Simulated: {}", sim);
}
}
}
public static String escapeExprSpecialWord(String keyword) {
if (!StringUtils.isNullOrEmpty(keyword)) {
String[] fbsArr = {"\\", "$", "(", ")", "*", "+", ".", "[", "]", "?", "^", "{", "}", "|"};
for (String key : fbsArr) {
if (keyword.contains(key)) {
keyword = keyword.replace(key, "\\" + key);
}
}
}
return keyword;
}
public static class SQLInfo {
/**
* sql语句
*/
private String sql;
/**
* 参数列表
*/
private Object[] param;
/**
* 参数字符串
*/
private String paramString;
public String getSql() {
return sql;
}
public void setSql(String sql) {
this.sql = sql;
}
public Object[] getParam() {
return param;
}
public void setParam(Object[] param) {
this.param = param;
}
public String paramsString() {
if (getParam() == null
return "";
if (paramString == null
StringBuilder builder = new StringBuilder();
int i = 0;
for (Object param : getParam()) {
if (i++ != 0
builder.append(","
builder.append(String.valueOf(param));
builder.append(
builder.append(param == null ?
builder.append(")");
}
paramString = builder.toString();
}
return paramString;
}
}
}
至此，关于改终于分析完毕
