本文节选自《Spring 5 外围原理》
3 基于 Spring JDBC 实现要害性能
3.1 ClassMappings
ClassMappings 次要定义根底的映射类型，代码如下：
package com.tom.orm.framework;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
public class ClassMappings {private ClassMappings(){}
static final Set<Class<?>> SUPPORTED_SQL_OBJECTS = new HashSet<Class<?>>();
static {
// 只有这里写了，默认反对主动类型转换
Class<?>[] classes = {
boolean.class, Boolean.class,
short.class, Short.class,
int.class, Integer.class,
long.class, Long.class,
float.class, Float.class,
double.class, Double.class,
String.class,
Date.class,
Timestamp.class,
BigDecimal.class
};
SUPPORTED_SQL_OBJECTS.addAll(Arrays.asList(classes));
}
static boolean isSupportedSQLObject(Class<?> clazz) {return clazz.isEnum() || SUPPORTED_SQL_OBJECTS.contains(clazz);
}
public static Map<String, Method> findPublicGetters(Class<?> clazz) {Map<String, Method> map = new HashMap<String, Method>();
Method[] methods = clazz.getMethods();
for (Method method : methods) {if (Modifier.isStatic(method.getModifiers()))
continue;
if (method.getParameterTypes().length != 0)
continue;
if (method.getName().equals("getClass"))
continue;
Class<?> returnType = method.getReturnType();
if (void.class.equals(returnType))
continue;
if(!isSupportedSQLObject(returnType)){continue;}
if ((returnType.equals(boolean.class)
|| returnType.equals(Boolean.class))
&& method.getName().startsWith("is")
&& method.getName().length() > 2) {map.put(getGetterName(method), method);
continue;
}
if (! method.getName().startsWith("get"))
continue;
if (method.getName().length() < 4)
continue;
map.put(getGetterName(method), method);
}
return map;
}
public static Field[] findFields(Class<?> clazz){return clazz.getDeclaredFields();
}
public static Map<String, Method> findPublicSetters(Class<?> clazz) {Map<String, Method> map = new HashMap<String, Method>();
Method[] methods = clazz.getMethods();
for (Method method : methods) {if (Modifier.isStatic(method.getModifiers()))
continue;
if (! void.class.equals(method.getReturnType()))
continue;
if (method.getParameterTypes().length != 1)
continue;
if (! method.getName().startsWith("set"))
continue;
if (method.getName().length() < 4)
continue;
if(!isSupportedSQLObject(method.getParameterTypes()[0])){continue;}
map.put(getSetterName(method), method);
}
return map;
}
public static String getGetterName(Method getter) {String name = getter.getName();
if (name.startsWith("is"))
name = name.substring(2);
else
name = name.substring(3);
return Character.toLowerCase(name.charAt(0)) + name.substring(1);
}
private static String getSetterName(Method setter) {String name = setter.getName().substring(3);
return Character.toLowerCase(name.charAt(0)) + name.substring(1);
}
}
3.2 EntityOperation
EntityOperation 次要实现数据库表构造和对象类构造的映射关系，代码如下：
package com.tom.orm.framework;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
import org.apache.log4j.Logger;
import org.springframework.jdbc.core.RowMapper;
import javax.core.common.utils.StringUtils;
/**
* 实体对象的反射操作
*
* @param <T>
*/
public class EntityOperation<T> {private Logger log = Logger.getLogger(EntityOperation.class);
public Class<T> entityClass = null; // 泛型实体 Class 对象
public final Map<String, PropertyMapping> mappings;
public final RowMapper<T> rowMapper;
public final String tableName;
public String allColumn = "*";
public Field pkField;
public EntityOperation(Class<T> clazz,String pk) throws Exception{if(!clazz.isAnnotationPresent(Entity.class)){throw new Exception("在" + clazz.getName() + "中没有找到 Entity 注解，不能做 ORM 映射");
}
this.entityClass = clazz;
Table table = entityClass.getAnnotation(Table.class);
if (table != null) {this.tableName = table.name();
} else {this.tableName = entityClass.getSimpleName();
}
Map<String, Method> getters = ClassMappings.findPublicGetters(entityClass);
Map<String, Method> setters = ClassMappings.findPublicSetters(entityClass);
Field[] fields = ClassMappings.findFields(entityClass);
fillPkFieldAndAllColumn(pk,fields);
this.mappings = getPropertyMappings(getters, setters, fields);
this.allColumn = this.mappings.keySet().toString().replace("[", "").replace("]",""). replaceAll("","");
this.rowMapper = createRowMapper();}
Map<String, PropertyMapping> getPropertyMappings(Map<String, Method> getters, Map<String, Method> setters, Field[] fields) {Map<String, PropertyMapping> mappings = new HashMap<String, PropertyMapping>();
String name;
for (Field field : fields) {if (field.isAnnotationPresent(Transient.class))
continue;
name = field.getName();
if(name.startsWith("is")){name = name.substring(2);
}
name = Character.toLowerCase(name.charAt(0)) + name.substring(1);
Method setter = setters.get(name);
Method getter = getters.get(name);
if (setter == null || getter == null){continue;}
Column column = field.getAnnotation(Column.class);
if (column == null) {mappings.put(field.getName(), new PropertyMapping(getter, setter, field));
} else {mappings.put(column.name(), new PropertyMapping(getter, setter, field));
}
}
return mappings;
}
RowMapper<T> createRowMapper() {return new RowMapper<T>() {public T mapRow(ResultSet rs, int rowNum) throws SQLException {
try {T t = entityClass.newInstance();
ResultSetMetaData meta = rs.getMetaData();
int columns = meta.getColumnCount();
String columnName;
for (int i = 1; i <= columns; i++) {Object value = rs.getObject(i);
columnName = meta.getColumnName(i);
fillBeanFieldValue(t,columnName,value);
}
return t;
}catch (Exception e) {throw new RuntimeException(e);
}
}
};
}
protected void fillBeanFieldValue(T t, String columnName, Object value) {if (value != null) {PropertyMapping pm = mappings.get(columnName);
if (pm != null) {
try {pm.set(t, value);
} catch (Exception e) {e.printStackTrace();
}
}
}
}
private void fillPkFieldAndAllColumn(String pk, Field[] fields) {
// 设定主键
try {if(!StringUtils.isEmpty(pk)){pkField = entityClass.getDeclaredField(pk);
pkField.setAccessible(true);
}
} catch (Exception e) {log.debug("没找到主键列，主键列名必须与属性名雷同");
}
for (int i = 0 ; i < fields.length ;i ++) {Field f = fields[i];
if(StringUtils.isEmpty(pk)){Id id = f.getAnnotation(Id.class);
if(id != null){
pkField = f;
break;
}
}
}
}
public T parse(ResultSet rs) {
T t = null;
if (null == rs) {return null;}
Object value = null;
try {t = (T) entityClass.newInstance();
for (String columnName : mappings.keySet()) {
try {value = rs.getObject(columnName);
} catch (Exception e) {e.printStackTrace();
}
fillBeanFieldValue(t,columnName,value);
}
} catch (Exception ex) {ex.printStackTrace();
}
return t;
}
public Map<String, Object> parse(T t) {Map<String, Object> _map = new TreeMap<String, Object>();
try {for (String columnName : mappings.keySet()) {Object value = mappings.get(columnName).getter.invoke(t);
if (value == null)
continue;
_map.put(columnName, value);
}
} catch (Exception e) {e.printStackTrace();
}
return _map;
}
public void println(T t) {
try {for (String columnName : mappings.keySet()) {Object value = mappings.get(columnName).getter.invoke(t);
if (value == null)
continue;
System.out.println(columnName + "=" + value);
}
} catch (Exception e) {e.printStackTrace();
}
}
}
class PropertyMapping {
final boolean insertable;
final boolean updatable;
final String columnName;
final boolean id;
final Method getter;
final Method setter;
final Class enumClass;
final String fieldName;
public PropertyMapping(Method getter, Method setter, Field field) {
this.getter = getter;
this.setter = setter;
this.enumClass = getter.getReturnType().isEnum() ? getter.getReturnType() : null;
Column column = field.getAnnotation(Column.class);
this.insertable = column == null || column.insertable();
this.updatable = column == null || column.updatable();
this.columnName = column == null ? ClassMappings.getGetterName(getter) : ("".equals(column.name()) ? ClassMappings.getGetterName(getter) : column.name());
this.id = field.isAnnotationPresent(Id.class);
this.fieldName = field.getName();}
@SuppressWarnings("unchecked")
Object get(Object target) throws Exception {Object r = getter.invoke(target);
return enumClass == null ? r : Enum.valueOf(enumClass, (String) r);
}
@SuppressWarnings("unchecked")
void set(Object target, Object value) throws Exception {if (enumClass != null && value != null) {value = Enum.valueOf(enumClass, (String) value);
}
//BeanUtils.setProperty(target, fieldName, value);
try {if(value != null){setter.invoke(target, setter.getParameterTypes()[0].cast(value));
}
} catch (Exception e) {e.printStackTrace();
/**
* 出错起因如果是 boolean 字段、mysql 字段类型，设置 tinyint(1)
*/
System.err.println(fieldName + "--" + value);
}
}
}
3.3 QueryRuleSqlBuilder
QueryRuleSqlBuilder 依据用户构建好的 QueryRule 来主动生成 SQL 语句，代码如下：
package com.tom.orm.framework;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.apache.commons.lang.ArrayUtils;
import com.tom.orm.framework.QueryRule.Rule;
import javax.core.common.utils.StringUtils;
/**
* 依据 QueryRule 主动构建 SQL 语句
*/
public class QueryRuleSqlBuilder {
private int CURR_INDEX = 0; // 记录参数所在的地位
private List<String> properties; // 保留列名列表
private List<Object> values; // 保留参数值列表
private List<Order> orders; // 保留排序规定列表
private String whereSql = "";
private String orderSql = "";
private Object [] valueArr = new Object[]{};
private Map<Object,Object> valueMap = new HashMap<Object,Object>();
/**
* 取得查问条件
* @return
*/
public String getWhereSql(){return this.whereSql;}
/**
* 取得排序条件
* @return
*/
public String getOrderSql(){return this.orderSql;}
/**
* 取得参数值列表
* @return
*/
public Object [] getValues(){return this.valueArr;}
/**
* 获取参数列表
* @return
*/
public Map<Object,Object> getValueMap(){return this.valueMap;}
/**
* 创立 SQL 结构器
* @param queryRule
*/
public QueryRuleSqlBuilder(QueryRule queryRule) {
CURR_INDEX = 0;
properties = new ArrayList<String>();
values = new ArrayList<Object>();
orders = new ArrayList<Order>();
for (QueryRule.Rule rule : queryRule.getRuleList()) {switch (rule.getType()) {
case QueryRule.BETWEEN:
processBetween(rule);
break;
case QueryRule.EQ:
processEqual(rule);
break;
case QueryRule.LIKE:
processLike(rule);
break;
case QueryRule.NOTEQ:
processNotEqual(rule);
break;
case QueryRule.GT:
processGreaterThen(rule);
break;
case QueryRule.GE:
processGreaterEqual(rule);
break;
case QueryRule.LT:
processLessThen(rule);
break;
case QueryRule.LE:
processLessEqual(rule);
break;
case QueryRule.IN:
processIN(rule);
break;
case QueryRule.NOTIN:
processNotIN(rule);
break;
case QueryRule.ISNULL:
processIsNull(rule);
break;
case QueryRule.ISNOTNULL:
processIsNotNull(rule);
break;
case QueryRule.ISEMPTY:
processIsEmpty(rule);
break;
case QueryRule.ISNOTEMPTY:
processIsNotEmpty(rule);
break;
case QueryRule.ASC_ORDER:
processOrder(rule);
break;
case QueryRule.DESC_ORDER:
processOrder(rule);
break;
default:
throw new IllegalArgumentException("type" + rule.getType() + "not supported.");
}
}
// 拼装 where 语句
appendWhereSql();
// 拼装排序语句
appendOrderSql();
// 拼装参数值
appendValues();}
/**
* 去掉 order
*
* @param sql
* @return
*/
protected String removeOrders(String sql) {Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);
Matcher m = p.matcher(sql);
StringBuffer sb = new StringBuffer();
while (m.find()) {m.appendReplacement(sb, "");
}
m.appendTail(sb);
return sb.toString();}
/**
* 去掉 select
*
* @param sql
* @return
*/
protected String removeSelect(String sql) {if(sql.toLowerCase().matches("from\\s+")){int beginPos = sql.toLowerCase().indexOf("from");
return sql.substring(beginPos);
}else{return sql;}
}
/**
* 解决 like
* @param rule
*/
private void processLike(QueryRule.Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}
Object obj = rule.getValues()[0];
if (obj != null) {String value = obj.toString();
if (!StringUtils.isEmpty(value)) {value = value.replace('*', '%');
obj = value;
}
}
add(rule.getAndOr(),rule.getPropertyName(),"like","%"+rule.getValues()[0]+"%");
}
/**
* 解决 between
* @param rule
*/
private void processBetween(QueryRule.Rule rule) {if ((ArrayUtils.isEmpty(rule.getValues()))
|| (rule.getValues().length < 2)) {return;}
add(rule.getAndOr(),rule.getPropertyName(),"","between",rule.getValues()[0],"and");
add(0,"","","",rule.getValues()[1],"");
}
/**
* 解决 =
* @param rule
*/
private void processEqual(QueryRule.Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}
add(rule.getAndOr(),rule.getPropertyName(),"=",rule.getValues()[0]);
}
/**
* 解决 <>
* @param rule
*/
private void processNotEqual(QueryRule.Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}
add(rule.getAndOr(),rule.getPropertyName(),"<>",rule.getValues()[0]);
}
/**
* 解决 >
* @param rule
*/
private void processGreaterThen(QueryRule.Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}
add(rule.getAndOr(),rule.getPropertyName(),">",rule.getValues()[0]);
}
/**
* 解决 >=
* @param rule
*/
private void processGreaterEqual(QueryRule.Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}
add(rule.getAndOr(),rule.getPropertyName(),">=",rule.getValues()[0]);
}
/**
* 解决 <
* @param rule
*/
private void processLessThen(QueryRule.Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}
add(rule.getAndOr(),rule.getPropertyName(),"<",rule.getValues()[0]);
}
/**
* 解决 <=
* @param rule
*/
private void processLessEqual(QueryRule.Rule rule) {if (ArrayUtils.isEmpty(rule.getValues())) {return;}
add(rule.getAndOr(),rule.getPropertyName(),"<=",rule.getValues()[0]);
}
/**
* 解决 is null
* @param rule
*/
private void processIsNull(QueryRule.Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"is null",null);
}
/**
* 解决 is not null
* @param rule
*/
private void processIsNotNull(QueryRule.Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"is not null",null);
}
/**
* 解决 <>''
* @param rule
*/
private void processIsNotEmpty(QueryRule.Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"<>","''");
}
/**
* 解决 =''
* @param rule
*/
private void processIsEmpty(QueryRule.Rule rule) {add(rule.getAndOr(),rule.getPropertyName(),"=","''");
}
/**
* 解决 in 和 not in
* @param rule
* @param name
*/
private void inAndNotIn(QueryRule.Rule rule,String name){if (ArrayUtils.isEmpty(rule.getValues())) {return;}
if ((rule.getValues().length == 1) && (rule.getValues()[0] != null)
&& (rule.getValues()[0] instanceof List)) {List<Object> list = (List) rule.getValues()[0];
if ((list != null) && (list.size() > 0)){for (int i = 0; i < list.size(); i++) {if(i == 0 && i == list.size() - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name +" (",list.get(i),")");
}else if(i == 0 && i < list.size() - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name +" (",list.get(i),"");
}
if(i > 0 && i < list.size() - 1){add(0,"",",","",list.get(i),"");
}
if(i == list.size() - 1 && i != 0){add(0,"",",","",list.get(i),")");
}
}
}
} else {Object[] list = rule.getValues();
for (int i = 0; i < list.length; i++) {if(i == 0 && i == list.length - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name +" (",list[i],")");
}else if(i == 0 && i < list.length - 1){add(rule.getAndOr(),rule.getPropertyName(),"",name +" (",list[i],"");
}
if(i > 0 && i < list.length - 1){add(0,"",",","",list[i],"");
}
if(i == list.length - 1 && i != 0){add(0,"",",","",list[i],")");
}
}
}
}
/**
* 解决 not in
* @param rule
*/
private void processNotIN(QueryRule.Rule rule){inAndNotIn(rule,"not in");
}
/**
* 解决 in
* @param rule
*/
private void processIN(QueryRule.Rule rule) {inAndNotIn(rule,"in");
}
/**
* 解决 order by
* @param rule 查问规定
*/
private void processOrder(Rule rule) {switch (rule.getType()) {
case QueryRule.ASC_ORDER:
//propertyName 非空
if (!StringUtils.isEmpty(rule.getPropertyName())) {orders.add(Order.asc(rule.getPropertyName()));
}
break;
case QueryRule.DESC_ORDER:
//propertyName 非空
if (!StringUtils.isEmpty(rule.getPropertyName())) {orders.add(Order.desc(rule.getPropertyName()));
}
break;
default:
break;
}
}
/**
* 退出 SQL 查问规定队列
* @param andOr and 或者 or
* @param key 列名
* @param split 列名与值之间的距离
* @param value 值
*/
private void add(int andOr,String key,String split ,Object value){add(andOr,key,split,"",value,"");
}
/**
* 退出 SQL 查问规定队列
* @param andOr and 或则 or
* @param key 列名
* @param split 列名与值之间的距离
* @param prefix 值前缀
* @param value 值
* @param suffix 值后缀
*/
private void add(int andOr,String key,String split,String prefix,Object value,String suffix){String andOrStr = (0 == andOr ? "":(QueryRule.AND == andOr ?" and ":" or "));
properties.add(CURR_INDEX, andOrStr + key + "" + split + prefix + (null != value ?" ? ":" ") + suffix);
if(null != value){values.add(CURR_INDEX,value);
CURR_INDEX ++;
}
}
/**
* 拼装 where 语句
*/
private void appendWhereSql(){StringBuffer whereSql = new StringBuffer();
for (String p : properties) {whereSql.append(p);
}
this.whereSql = removeSelect(removeOrders(whereSql.toString()));
}
/**
* 拼装排序语句
*/
private void appendOrderSql(){StringBuffer orderSql = new StringBuffer();
for (int i = 0 ; i < orders.size(); i ++) {if(i > 0 && i < orders.size()){orderSql.append(",");
}
orderSql.append(orders.get(i).toString());
}
this.orderSql = removeSelect(removeOrders(orderSql.toString()));
}
/**
* 拼装参数值
*/
private void appendValues(){Object [] val = new Object[values.size()];
for (int i = 0; i < values.size(); i ++) {val[i] = values.get(i);
valueMap.put(i, values.get(i));
}
this.valueArr = val;
}
}
3.4 BaseDaoSupport
BaseDaoSupport 次要是对 JdbcTemplate 的包装，上面讲一下其重要代码，请“小伙伴们”关 注 公 众 号『Tom 弹架构』，回复 ” Spring ” 可下载全副源代码。先看全局定义：
package com.tom.orm.framework;
...
/**
* BaseDao 扩大类，次要性能是反对主动拼装 SQL 语句，必须继承方可应用
* @author Tom
*/
public abstract class BaseDaoSupport<T extends Serializable, PK extends Serializable> implements BaseDao<T,PK> {private Logger log = Logger.getLogger(BaseDaoSupport.class);
private String tableName = "";
private JdbcTemplate jdbcTemplateWrite;
private JdbcTemplate jdbcTemplateReadOnly;
private DataSource dataSourceReadOnly;
private DataSource dataSourceWrite;
private EntityOperation<T> op;
@SuppressWarnings("unchecked")
protected BaseDaoSupport(){
try{Class<T> entityClass = GenericsUtils.getSuperClassGenricType(getClass(), 0);
op = new EntityOperation<T>(entityClass,this.getPKColumn());
this.setTableName(op.tableName);
}catch(Exception e){e.printStackTrace();
}
}
protected String getTableName() { return tableName;}
protected DataSource getDataSourceReadOnly() { return dataSourceReadOnly;}
protected DataSource getDataSourceWrite() { return dataSourceWrite;}
/**
* 动静切换表名
*/
protected void setTableName(String tableName) {if(StringUtils.isEmpty(tableName)){this.tableName = op.tableName;}else{this.tableName = tableName;}
}
protected void setDataSourceWrite(DataSource dataSourceWrite) {
this.dataSourceWrite = dataSourceWrite;
jdbcTemplateWrite = new JdbcTemplate(dataSourceWrite);
}
protected void setDataSourceReadOnly(DataSource dataSourceReadOnly) {
this.dataSourceReadOnly = dataSourceReadOnly;
jdbcTemplateReadOnly = new JdbcTemplate(dataSourceReadOnly);
}
private JdbcTemplate jdbcTemplateReadOnly() {return this.jdbcTemplateReadOnly;}
private JdbcTemplate jdbcTemplateWrite() {return this.jdbcTemplateWrite;}
/**
* 还原默认表名
*/
protected void restoreTableName(){ this.setTableName(op.tableName); }
/**
* 获取主键列名称，倡议子类重写
* @return
*/
protected abstract String getPKColumn();
protected abstract void setDataSource(DataSource dataSource);
// 此处有省略
}
为了关照程序员的个别应用习惯，查询方法的前缀命名次要有 select、get、load，兼顾 Hibernate 和 MyBatis 的命名格调。
/**
* 查问函数，应用查问规定
* 例如以下代码查问条件为匹配的数据
*
* @param queryRule 查问规定
* @return 查问的后果 List
*/
public List<T> select(QueryRule queryRule) throws Exception{QueryRuleSqlBuilder bulider = new QueryRuleSqlBuilder(queryRule);
String ws = removeFirstAnd(bulider.getWhereSql());
String whereSql = ("".equals(ws) ? ws : (" where " + ws));
String sql = "select" + op.allColumn + "from" + getTableName() + whereSql;
Object [] values = bulider.getValues();
String orderSql = bulider.getOrderSql();
orderSql = (StringUtils.isEmpty(orderSql) ? "" : (" order by " + orderSql));
sql += orderSql;
log.debug(sql);
return (List<T>) this.jdbcTemplateReadOnly().query(sql, this.op.rowMapper, values);
}
...
/**
* 依据 SQL 语句执行查问，参数为 Object 数组对象
* @param sql 查问语句
* @param args 为 Object 数组
* @return 符合条件的所有对象
*/
public List<Map<String,Object>> selectBySql(String sql,Object... args) throws Exception{return this.jdbcTemplateReadOnly().queryForList(sql,args);
}
...
/**
* 分页查问函数，应用查问规定 <br>
* 例如以下代码查问条件为匹配的数据
*
* @param queryRule 查问规定
* @param pageNo 页号，从 1 开始
* @param pageSize 每页的记录条数
* @return 查问的后果 Page
*/
public Page<T> select(QueryRule queryRule,final int pageNo, final int pageSize) throws Exception{QueryRuleSqlBuilder bulider = new QueryRuleSqlBuilder(queryRule);
Object [] values = bulider.getValues();
String ws = removeFirstAnd(bulider.getWhereSql());
String whereSql = ("".equals(ws) ? ws : (" where " + ws));
String countSql = "select count(1) from" + getTableName() + whereSql;
long count = (Long) this.jdbcTemplateReadOnly().queryForMap(countSql, values).get ("count(1)");
if (count == 0) {return new Page<T>();
}
long start = (pageNo - 1) * pageSize;
// 在有数据的状况下，持续查问
String orderSql = bulider.getOrderSql();
orderSql = (StringUtils.isEmpty(orderSql) ? "" : (" order by " + orderSql));
String sql = "select" + op.allColumn +"from" + getTableName() + whereSql + orderSql + "limit" + start + "," + pageSize;
List<T> list = (List<T>) this.jdbcTemplateReadOnly().query(sql, this.op.rowMapper, values);
log.debug(sql);
return new Page<T>(start, count, pageSize, list);
}
...
/**
* 分页查问非凡 SQL 语句
* @param sql 语句
* @param param 查问条件
* @param pageNo 页码
* @param pageSize 每页内容
* @return
*/
public Page<Map<String,Object>> selectBySqlToPage(String sql, Object [] param, final int pageNo, final int pageSize) throws Exception {String countSql = "select count(1) from (" + sql + ") a";
long count = (Long) this.jdbcTemplateReadOnly().queryForMap(countSql,param).get("count(1)");
if (count == 0) {return new Page<Map<String,Object>>();
}
long start = (pageNo - 1) * pageSize;
sql = sql + "limit" + start + "," + pageSize;
List<Map<String,Object>> list = (List<Map<String,Object>>) this.jdbcTemplateReadOnly(). queryForList(sql, param);
log.debug(sql);
return new Page<Map<String,Object>>(start, count, pageSize, list);
}
/**
* 获取默认的实例对象
* @param <T>
* @param pkValue
* @param rowMapper
* @return
*/
private <T> T doLoad(Object pkValue, RowMapper<T> rowMapper){Object obj = this.doLoad(getTableName(), getPKColumn(), pkValue, rowMapper);
if(obj != null){return (T)obj;
}
return null;
}
插入方法，均以 insert 结尾：
/**
* 插入并返回 ID
* @param entity
* @return
*/
public PK insertAndReturnId(T entity) throws Exception{return (PK)this.doInsertRuturnKey(parse(entity));
}
/**
* 插入一条记录
* @param entity
* @return
*/
public boolean insert(T entity) throws Exception{return this.doInsert(parse(entity));
}
/**
* 批量保留对象.<br>
*
* @param list 待保留的对象 List
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public int insertAll(List<T> list) throws Exception {int count = 0 ,len = list.size(),step = 50000;
Map<String, PropertyMapping> pm = op.mappings;
int maxPage = (len % step == 0) ? (len / step) : (len / step + 1);
for (int i = 1; i <= maxPage; i ++) {Page<T> page = pagination(list, i, step);
String sql = "insert into" + getTableName() + "(" + op.allColumn + ") values";// ("+ valstr.toString() +")";
StringBuffer valstr = new StringBuffer();
Object[] values = new Object[pm.size() * page.getRows().size()];
for (int j = 0; j < page.getRows().size(); j ++) {if(j > 0 && j < page.getRows().size()){valstr.append(","); }
valstr.append("(");
int k = 0;
for (PropertyMapping p : pm.values()) {values[(j * pm.size()) + k] = p.getter.invoke(page.getRows().get(j));
if(k > 0 && k < pm.size()){valstr.append(","); }
valstr.append("?");
k ++;
}
valstr.append(")");
}
int result = jdbcTemplateWrite().update(sql + valstr.toString(), values);
count += result;
}
return count;
}
private Serializable doInsertRuturnKey(Map<String,Object> params){final List<Object> values = new ArrayList<Object>();
final String sql = makeSimpleInsertSql(getTableName(),params,values);
KeyHolder keyHolder = new GeneratedKeyHolder();
final JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSourceWrite());
try {jdbcTemplate.update(new PreparedStatementCreator() {
public PreparedStatement createPreparedStatement(Connection con) throws SQLException {PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
for (int i = 0; i < values.size(); i++) {ps.setObject(i+1, values.get(i)==null?null:values.get(i));
}
return ps;
}
}, keyHolder);
} catch (DataAccessException e) {log.error("error",e);
}
if (keyHolder == null) {return "";}
Map<String, Object> keys = keyHolder.getKeys();
if (keys == null || keys.size() == 0 || keys.values().size() == 0) {return "";}
Object key = keys.values().toArray()[0];
if (key == null || !(key instanceof Serializable)) {return "";}
if (key instanceof Number) {//Long k = (Long) key;
Class clazz = key.getClass();
// return clazz.cast(key);
return (clazz == int.class || clazz == Integer.class) ? ((Number) key).intValue() : ((Number)key).longValue();} else if (key instanceof String) {return (String) key;
} else {return (Serializable) key;
}
}
/**
* 插入
* @param params
* @return
*/
private boolean doInsert(Map<String, Object> params) {String sql = this.makeSimpleInsertSql(this.getTableName(), params);
int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
return ret > 0;
}
删除办法，均以 delete 结尾：
/**
* 删除对象.<br>
*
* @param entity 待删除的实体对象
*/
public boolean delete(T entity) throws Exception {return this.doDelete(op.pkField.get(entity)) > 0;
}
/**
* 删除对象.<br>
*
* @param list 待删除的实体对象列表
* @throws InvocationTargetException
* @throws IllegalArgumentException
* @throws IllegalAccessException
*/
public int deleteAll(List<T> list) throws Exception {String pkName = op.pkField.getName();
int count = 0 ,len = list.size(),step = 1000;
Map<String, PropertyMapping> pm = op.mappings;
int maxPage = (len % step == 0) ? (len / step) : (len / step + 1);
for (int i = 1; i <= maxPage; i ++) {StringBuffer valstr = new StringBuffer();
Page<T> page = pagination(list, i, step);
Object[] values = new Object[page.getRows().size()];
for (int j = 0; j < page.getRows().size(); j ++) {if(j > 0 && j < page.getRows().size()){valstr.append(","); }
values[j] = pm.get(pkName).getter.invoke(page.getRows().get(j));
valstr.append("?");
}
String sql = "delete from" + getTableName() + "where" + pkName + "in (" + valstr.toString() + ")";
int result = jdbcTemplateWrite().update(sql, values);
count += result;
}
return count;
}
/**
* 依据 id 删除对象。如果有记录则删之，没有记录也不报异样 <br>
* 例如：删除主键惟一的记录
*
* @param id 序列化 id
*/
protected void deleteByPK(PK id) throws Exception {this.doDelete(id);
}
/**
* 删除实例对象，返回删除记录数
* @param tableName
* @param pkName
* @param pkValue
* @return
*/
private int doDelete(String tableName, String pkName, Object pkValue) {StringBuffer sb = new StringBuffer();
sb.append("delete from").append(tableName).append("where").append(pkName).append("= ?");
int ret = this.jdbcTemplateWrite().update(sb.toString(), pkValue);
return ret;
}
批改办法，均以 update 结尾：
/**
* 更新对象.<br>
*
* @param entity 待更新对象
* @throws IllegalAccessException
* @throws IllegalArgumentException
*/
public boolean update(T entity) throws Exception {return this.doUpdate(op.pkField.get(entity), parse(entity)) > 0;
}
/**
* 更新实例对象，返回删除记录数
* @param pkValue
* @param params
* @return
*/
private int doUpdate(Object pkValue, Map<String, Object> params){String sql = this.makeDefaultSimpleUpdateSql(pkValue, params);
params.put(this.getPKColumn(), pkValue);
int ret = this.jdbcTemplateWrite().update(sql, params.values().toArray());
return ret;
}
至此一个残缺的 ORM 框架就横空出世。当然，还有很多优化的中央，请小伙伴能够持续欠缺。
关注微信公众号『Tom 弹架构』回复“Spring”可获取残缺源码。
本文为“Tom 弹架构”原创，转载请注明出处。技术在于分享，我分享我高兴！如果您有任何倡议也可留言评论或私信，您的反对是我保持创作的能源。关注微信公众号『Tom 弹架构』可获取更多技术干货！
原创不易，保持很酷，都看到这里了，小伙伴记得点赞、珍藏、在看，一键三连加关注！如果你感觉内容太干，能够分享转发给敌人滋润滋润！