自定义orm框架解决玩家数据持久化问题

前面一篇文章 游戏服务器关于玩家数据的解决方案，介绍了当今游戏服务端对玩家数据进行持久化的两种方案。一种是将玩家数据通过json等格式统一打包成字符串或二进制流；另一种是根据模块功能拆分，一个模块一张用户表。

今天的主题就是介绍如何通过orm来简化上面所说的第二种持久化方式。

ORM(关系对象映射),简单来说,就是将oop世界里的对象与关系型数据库里的表记录进行映射。如果玩家数据的持久化是根据功能模块来拆分的话，那么随着游戏功能的增多，用户表的数量也会越来越多。如果对于每一张表，都需要编写对应的CRUD sql语句，那开发效率是非常低下的。但使用了orm框架，对于每一个需要进行持久化的玩家数据对象，都不再需要手动编写sql语句，这无疑是非常爽的。

下面逐步介绍使用的主要类文件。

1. Cacheable抽象类主要是对需要持久化的对象的一种抽象，是对象各种db状态的转换。

package com.kingston.cache;public abstract class Cacheable {protected DbStatus status;public abstract DbStatus getStatus();public abstract boolean isInsert();public abstract boolean isUpdate();public abstract boolean isDelete();public abstract void setInsert();public abstract void setUpdate();public abstract void setDelete();public abstract void save();}

2.AbstractCacheable是对Cacheable的骨架实现，对各种抽象方法提供默认的实现

public abstract class AbstractCacheable extends Cacheable {private static Logger logger = LoggerFactory.getLogger(AbstractCacheable.class); @Overridepublic DbStatus getStatus() {return this.status;}@Overridepublic final boolean isInsert() {return this.status == DbStatus.INSERT;}@Overridepublic final boolean isUpdate() {return this.status == DbStatus.UPDATE;}@Overridepublic final boolean isDelete() {return this.status == DbStatus.DELETE;}public void setInsert() {this.status = DbStatus.INSERT;}public final void setUpdate(){//只有该状态才可以变更为updateif (this.status == DbStatus.NORMAL) {this.status = DbStatus.UPDATE;}}public final void setDelete(){if (this.status == DbStatus.INSERT) {this.status = DbStatus.NORMAL;} else{this.status = DbStatus.DELETE;}}public final void save() {String saveSql = SqlUtils.getSaveSql(this);if (DbUtils.executeSql(saveSql)) {this.status = DbStatus.NORMAL;}if (logger.isDebugEnabled()) {System.err.println(saveSql);}}}

3. DbStatus表示各种db状态

package com.kingston.cache;public enum DbStatus {/** 无需入库 */NORMAL,/** 需要更新 */UPDATE,/** 需要插入 */INSERT,/** 需要删除 */DELETE,;}

4. 采用注解来进行orm映射，注解命名借鉴了hibernate的命名，分别有：

package com.kingston.annotation;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/**  *  *  标识该对象需要持久化 */@Documented@Target(ElementType.TYPE)@Retention(RetentionPolicy.RUNTIME)public @interface Entity {/** 不为空则说明表名字跟entity类名字不一致 */String table() default "";}

package com.kingston.annotation;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/**  * 标识该属性需要映射到表字段 */@Documented@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface Column {/** 不为空则说明表字段名字跟entity属性不一致 */String name() default "";}

package com.kingston.annotation;import java.lang.annotation.Documented;import java.lang.annotation.ElementType;import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;/** *  查询得到实体的唯一索引字段 *  每一个实体至少需要一个Id字段 */@Documented@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public @interface Id {}

5.将对象与表记录进行映射的orm桥梁（OrmBridge.java）

package com.kingston.orm;import java.lang.reflect.Method;import java.util.ArrayList;import java.util.HashMap;import java.util.HashSet;import java.util.List;import java.util.Map;import java.util.Set;public class OrmBridge {/** 对应的数据库表名称 */private String tableName;/** 缓存所有表字段及其对应的getter method */private Map<String, Method> getterMap = new HashMap<>();/** 缓存所有表字段及其对应的setter method */private Map<String, Method> setterMap = new HashMap<>();/** 被覆写的property与表column的映射 */private Map<String, String> propertyToColumnOverride = new HashMap<>();/** 被覆写的表column与property的映射 */private Map<String, String> columnToPropertyOverride = new HashMap<>();/** 实体所有的主键字段 */private Set<String> uniqueProperties = new HashSet<>();/** 需要持久化的字段 */private Set<String> properties = new HashSet<>();public String getTableName() {return tableName;}public void setTableName(String tableName) {this.tableName = tableName;}public Map<String, Method> getGetterMap() {return getterMap;}public void addGetterMethod(String field, Method method) {this.getterMap.put(field, method);}public Map<String, Method> getSetterMap() {return setterMap;}public void addSetterMethod(String field, Method method) {this.setterMap.put(field, method);}/**  * 返回查询实体的id组合 * @return */public List<String> getQueryProperties() {return new ArrayList<>(this.uniqueProperties);}public Method getGetterMethod(String field) {return this.getterMap.get(field);}public void addUniqueKey(String id) {this.uniqueProperties.add(id);}public void addProperty(String property) {this.properties.add(property);}public void addPropertyColumnOverride(String property, String column) {this.propertyToColumnOverride.put(property, column);this.columnToPropertyOverride.put(column, property);}public String getOverrideProperty(String property) {return this.propertyToColumnOverride.get(property);}public Map<String, String> getPropertyToColumnOverride() {return propertyToColumnOverride;}public Map<String, String> getColumnToPropertyOverride() {return columnToPropertyOverride;}public void setColumnToPropertyOverride(Map<String, String> columnToPropertyOverride) {this.columnToPropertyOverride = columnToPropertyOverride;}public void setPropertyToColumnOverride(Map<String, String> propertyToColumnOverride) {this.propertyToColumnOverride = propertyToColumnOverride;}public List<String> listProperties() {return new ArrayList<>(this.properties);}}

6.OrmProcessor类缓存各种entity与表的映射关系

package com.kingston.orm;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.util.HashMap;import java.util.Map;import java.util.Set;import com.kingston.annotation.Column;import com.kingston.annotation.Entity;import com.kingston.annotation.Id;import com.kingston.exception.OrmConfigExcpetion;import com.kingston.utils.ClassFilter;import com.kingston.utils.ClassScanner;import com.kingston.utils.StringUtils;public enum OrmProcessor {INSTANCE;/** entity与对应的ormbridge的映射关系 */private Map<Class<?>, OrmBridge> classOrmMapperr = new HashMap<>();public void initOrmBridges() {Set<Class<?>> entityClazzs = listEntityClazzs();for (Class<?> clazz:entityClazzs) {OrmBridge bridge = createBridge(clazz);this.classOrmMapperr.put(clazz, bridge);}}private OrmBridge createBridge(Class<?> clazz) {OrmBridge bridge = new OrmBridge();Entity entity = (Entity) clazz.getAnnotation(Entity.class);//没有设置tablename,则用entity名首字母小写if (entity.table().length() <= 0) {bridge.setTableName(StringUtils.firstLetterToLowerCase(clazz.getSimpleName()));}else {bridge.setTableName(entity.table());}Field[] fields = clazz.getDeclaredFields();for (Field field:fields) {Column column = field.getAnnotation(Column.class);String fieldName = field.getName();try{if (column != null) {Method m = clazz.getMethod("get" + StringUtils.firstLetterToUpperCase(field.getName()));bridge.addGetterMethod(fieldName, m);Method m2 = clazz.getMethod("set" + StringUtils.firstLetterToUpperCase(field.getName()), field.getType());bridge.addSetterMethod(fieldName, m2);}if (field.getAnnotation(Id.class) != null) {bridge.addUniqueKey(fieldName);}if (!StringUtils.isEmpty(column.name())) {bridge.addPropertyColumnOverride(fieldName, column.name());}bridge.addProperty(fieldName);}catch(Exception e) {throw new OrmConfigExcpetion(e);}if (bridge.getQueryProperties().size() <= 0) {throw new OrmConfigExcpetion(clazz.getSimpleName() + " entity 没有查询索引主键字段");}}return bridge;}private Set<Class<?>> listEntityClazzs() {return ClassScanner.getClasses("com.kingston.entity", new ClassFilter() {@Overridepublic boolean accept(Class<?> clazz) {return clazz.getAnnotation(Entity.class) != null;}});}public OrmBridge getOrmBridge(Class<?> clazz) {return this.classOrmMapperr.get(clazz);}}

7.逼格很高的BeanProcessor类主要是用于将jdbc的ResultSet反射为对应的entity实体，这个类是从apache dbutils框架拿来用的^-^

package com.kingston.orm;import java.beans.BeanInfo;import java.beans.IntrospectionException;import java.beans.Introspector;import java.beans.PropertyDescriptor;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.SQLXML;import java.sql.Time;import java.sql.Timestamp;import java.util.ArrayList;import java.util.Arrays;import java.util.HashMap;import java.util.List;import java.util.Map;public class BeanProcessor{protected static final int PROPERTY_NOT_FOUND = -1;private static final Map<Class<?>, Object> primitiveDefaults = new HashMap();private final Map<String, String> columnToPropertyOverrides;static{primitiveDefaults.put(Integer.TYPE, Integer.valueOf(0));primitiveDefaults.put(Short.TYPE, Short.valueOf((short)0));primitiveDefaults.put(Byte.TYPE, Byte.valueOf((byte)0));primitiveDefaults.put(Float.TYPE, Float.valueOf(0.0F));primitiveDefaults.put(Double.TYPE, Double.valueOf(0.0D));primitiveDefaults.put(Long.TYPE, Long.valueOf(0L));primitiveDefaults.put(Boolean.TYPE, Boolean.FALSE);primitiveDefaults.put(Character.TYPE, Character.valueOf('\000'));}public BeanProcessor(){this(new HashMap());}public BeanProcessor(Map<String, String> columnToPropertyOverrides){if (columnToPropertyOverrides == null) {throw new IllegalArgumentException("columnToPropertyOverrides map cannot be null");}this.columnToPropertyOverrides = columnToPropertyOverrides;}public <T> T toBean(ResultSet rs, Class<T> type)throws SQLException{PropertyDescriptor[] props = propertyDescriptors(type);ResultSetMetaData rsmd = rs.getMetaData();int[] columnToProperty = mapColumnsToProperties(rsmd, props);return createBean(rs, type, props, columnToProperty);}public <T> List<T> toBeanList(ResultSet rs, Class<T> type)throws SQLException{List<T> results = new ArrayList();if (!rs.next()) {return results;}PropertyDescriptor[] props = propertyDescriptors(type);ResultSetMetaData rsmd = rs.getMetaData();int[] columnToProperty = mapColumnsToProperties(rsmd, props);do{results.add(createBean(rs, type, props, columnToProperty));} while (rs.next());return results;}private <T> T createBean(ResultSet rs, Class<T> type, PropertyDescriptor[] props, int[] columnToProperty)throws SQLException{T bean = newInstance(type);for (int i = 1; i < columnToProperty.length; i++) {if (columnToProperty[i] != -1){PropertyDescriptor prop = props[columnToProperty[i]];Class<?> propType = prop.getPropertyType();Object value = null;if (propType != null){value = processColumn(rs, i, propType);if ((value == null) && (propType.isPrimitive())) {value = primitiveDefaults.get(propType);}}callSetter(bean, prop, value);}}return bean;}private void callSetter(Object target, PropertyDescriptor prop, Object value)throws SQLException{Method setter = prop.getWriteMethod();if (setter == null) {return;}Class<?>[] params = setter.getParameterTypes();try{if ((value instanceof java.util.Date)){String targetType = params[0].getName();if ("java.sql.Date".equals(targetType)){value = new java.sql.Date(((java.util.Date)value).getTime());}else if ("java.sql.Time".equals(targetType)){value = new Time(((java.util.Date)value).getTime());}else if ("java.sql.Timestamp".equals(targetType)){Timestamp tsValue = (Timestamp)value;int nanos = tsValue.getNanos();value = new Timestamp(tsValue.getTime());((Timestamp)value).setNanos(nanos);}}else if (((value instanceof String)) && (params[0].isEnum())){value = Enum.valueOf(params[0].asSubclass(Enum.class), (String)value);}if (isCompatibleType(value, params[0])) {setter.invoke(target, new Object[] { value });} else {throw new SQLException("Cannot set " + prop.getName() + ": incompatible types, cannot convert " + value.getClass().getName() + " to " + params[0].getName());}}catch (IllegalArgumentException e){throw new SQLException("Cannot set " + prop.getName() + ": " + e.getMessage());}catch (IllegalAccessException e){throw new SQLException("Cannot set " + prop.getName() + ": " + e.getMessage());}catch (InvocationTargetException e){throw new SQLException("Cannot set " + prop.getName() + ": " + e.getMessage());}}private boolean isCompatibleType(Object value, Class<?> type){if ((value == null) || (type.isInstance(value))) {return true;}if ((type.equals(Integer.TYPE)) && ((value instanceof Integer))) {return true;}if ((type.equals(Long.TYPE)) && ((value instanceof Long))) {return true;}if ((type.equals(Double.TYPE)) && ((value instanceof Double))) {return true;}if ((type.equals(Float.TYPE)) && ((value instanceof Float))) {return true;}if ((type.equals(Short.TYPE)) && ((value instanceof Short))) {return true;}if ((type.equals(Byte.TYPE)) && ((value instanceof Byte))) {return true;}if ((type.equals(Character.TYPE)) && ((value instanceof Character))) {return true;}if ((type.equals(Boolean.TYPE)) && ((value instanceof Boolean))) {return true;}return false;}protected <T> T newInstance(Class<T> c)throws SQLException{try{return c.newInstance();}catch (InstantiationException e){throw new SQLException("Cannot create " + c.getName() + ": " + e.getMessage());}catch (IllegalAccessException e){throw new SQLException("Cannot create " + c.getName() + ": " + e.getMessage());}}private PropertyDescriptor[] propertyDescriptors(Class<?> c)throws SQLException{BeanInfo beanInfo = null;try{beanInfo = Introspector.getBeanInfo(c);}catch (IntrospectionException e){throw new SQLException("Bean introspection failed: " + e.getMessage());}return beanInfo.getPropertyDescriptors();}protected int[] mapColumnsToProperties(ResultSetMetaData rsmd, PropertyDescriptor[] props)throws SQLException{int cols = rsmd.getColumnCount();int[] columnToProperty = new int[cols + 1];Arrays.fill(columnToProperty, -1);for (int col = 1; col <= cols; col++){String columnName = rsmd.getColumnLabel(col);if ((null == columnName) || (0 == columnName.length())) {columnName = rsmd.getColumnName(col);}String propertyName = (String)this.columnToPropertyOverrides.get(columnName);if (propertyName == null) {propertyName = columnName;}for (int i = 0; i < props.length; i++) {if (propertyName.equalsIgnoreCase(props[i].getName())){columnToProperty[col] = i;break;}}}return columnToProperty;}protected Object processColumn(ResultSet rs, int index, Class<?> propType)throws SQLException{if ((!propType.isPrimitive()) && (rs.getObject(index) == null)) {return null;}if (propType.equals(String.class)) {return rs.getString(index);}if ((propType.equals(Integer.TYPE)) || (propType.equals(Integer.class))) {return Integer.valueOf(rs.getInt(index));}if ((propType.equals(Boolean.TYPE)) || (propType.equals(Boolean.class))) {return Boolean.valueOf(rs.getBoolean(index));}if ((propType.equals(Long.TYPE)) || (propType.equals(Long.class))) {return Long.valueOf(rs.getLong(index));}if ((propType.equals(Double.TYPE)) || (propType.equals(Double.class))) {return Double.valueOf(rs.getDouble(index));}if ((propType.equals(Float.TYPE)) || (propType.equals(Float.class))) {return Float.valueOf(rs.getFloat(index));}if ((propType.equals(Short.TYPE)) || (propType.equals(Short.class))) {return Short.valueOf(rs.getShort(index));}if ((propType.equals(Byte.TYPE)) || (propType.equals(Byte.class))) {return Byte.valueOf(rs.getByte(index));}if (propType.equals(Timestamp.class)) {return rs.getTimestamp(index);}if (propType.equals(SQLXML.class)) {return rs.getSQLXML(index);}return rs.getObject(index);}}

8. SqlFactory类主要是通过反射技术，将实体转换为对应的insert,update,delete语句，这个类主要是体力活，没啥难度

package com.kingston.orm;import java.lang.reflect.Method;import java.util.List;import java.util.Map;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.kingston.utils.ReflectUtils;public class SqlFactory {private static Logger logger = LoggerFactory.getLogger(SqlFactory.class); public static String createInsertSql(Object entity, OrmBridge bridge) {StringBuilder sb = new StringBuilder();sb.append(" INSERT INTO ")  .append(bridge.getTableName()).append(" (");List<String> properties = bridge.listProperties();for (int i=0;i<properties.size();i++) {String property = properties.get(i);String column = property;if (bridge.getOverrideProperty(property) != null) {column = bridge.getOverrideProperty(property);}sb.append("`" + column + "`");if (i<properties.size()-1) {sb.append(",");}}sb.append(") VALUES (");for (int i=0;i<properties.size();i++) {String property = properties.get(i);Object value;try{value = ReflectUtils.getMethodValue(entity, property);sb.append("'"+value+"'");if (i < properties.size()-1) {sb.append(",");}}catch(Exception e) {logger.error("createInsertSql failed",e);}}sb.append(")");return sb.toString();}public static String createUpdateSql(Object entity, OrmBridge bridge) {StringBuilder sb = new StringBuilder();sb.append(" UPDATE ").append(bridge.getTableName())  .append(" SET ");sb.append(object2SetterSql(entity, bridge));sb.append(createWhereClauseSql(entity, bridge));return sb.toString();}private static String object2SetterSql(Object entity, OrmBridge bridge) {StringBuilder sb = new StringBuilder();Map<String, Method> getterMap = bridge.getGetterMap();for (Map.Entry<String, Method> entry:getterMap.entrySet()) {String property = entry.getKey();Object value;try{value = ReflectUtils.getMethodValue(entity, property);if (sb.length() > 0) {sb.append(", ");}String column = entry.getKey();if (bridge.getOverrideProperty(property) != null) {column = bridge.getOverrideProperty(property);}sb.append("`"+column+"` = '" +value+"'");}catch(Exception e) {logger.error("object2SetterSql failed",e);}}return sb.toString();}public static String createDeleteSql(Object entity, OrmBridge bridge) {StringBuilder sb = new StringBuilder();sb.append(" DELETE FROM ")  .append(bridge.getTableName())  .append(createWhereClauseSql(entity, bridge));  return sb.toString();}private static String createWhereClauseSql(Object entity, OrmBridge bridge) {StringBuilder sb = new StringBuilder();sb.append(" WHERE 1=1");  //占位申明，避免拼接sql需要考虑andList<String> properties = bridge.getQueryProperties();for (int i=0;i<properties.size();i++) {String property = properties.get(i);Object colValue;try{colValue = ReflectUtils.getMethodValue(entity, property);String column = property;if (bridge.getOverrideProperty(property) != null) {column = bridge.getOverrideProperty(property);}sb.append(" AND `" + column + "` = '" + colValue + "'");}catch(Exception e) {logger.error("createWhereClauseSql failed",e);}}return sb.toString();}}

9. 工具类SqlUtils根据实体不同的db状态拿到对应的sql语句

package com.kingston.utils;import com.kingston.cache.AbstractCacheable;import com.kingston.orm.OrmBridge;import com.kingston.orm.OrmProcessor;import com.kingston.orm.SqlFactory;public class SqlUtils {public static String getInsertSql(AbstractCacheable entity) {OrmBridge bridge = OrmProcessor.INSTANCE.getOrmBridge(entity.getClass());return SqlFactory.createInsertSql(entity, bridge);}public static String getUpdateSql(AbstractCacheable entity) {OrmBridge bridge = OrmProcessor.INSTANCE.getOrmBridge(entity.getClass());return SqlFactory.createUpdateSql(entity, bridge);}public static String getDeleteSql(AbstractCacheable entity) {OrmBridge bridge = OrmProcessor.INSTANCE.getOrmBridge(entity.getClass());return SqlFactory.createDeleteSql(entity, bridge);}public static String getSaveSql(AbstractCacheable entity) {if (entity.isInsert()) {return getInsertSql(entity);}else if (entity.isUpdate()) {return getUpdateSql(entity);}else if (entity.isDelete()) {return getDeleteSql(entity);}return "";}}

10. DbUtils是最重要的工具类了，提供了根据查询语句返回一个实体对象或实体对象列表；提供执行更新、插入、删除操作的接口。更为通用的是，提供通过sql返回一个map对象,map列表的接口。有了这些接口，单表查询、多表查询、执行任意sql都成为可能。

package com.kingston.utils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import org.slf4j.Logger;import org.slf4j.LoggerFactory;import com.kingston.orm.BeanProcessor;import com.kingston.orm.OrmBridge;import com.kingston.orm.OrmProcessor;import com.mchange.v2.c3p0.ComboPooledDataSource;public class DbUtils {private static Logger logger = LoggerFactory.getLogger(DbUtils.class); private static ComboPooledDataSource cpds = new ComboPooledDataSource();static{try {//本地mysql连接配置，改为自己机子的配置cpds.setDriverClass( "com.mysql.cj.jdbc.Driver" ); //loads the jdbc driver            cpds.setJdbcUrl( "jdbc:mysql://localhost/world?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC" );cpds.setUser("root");                                  cpds.setPassword("123456");  }catch(Exception e) {logger.error("DbUtils init failed", e);}}/** * 查询返回一个bean实体 * @param sql * @param entity * @return */@SuppressWarnings("unchecked")public static <T> T queryOne(String sql, Class<?> entity) {OrmBridge bridge = OrmProcessor.INSTANCE.getOrmBridge(entity);if (bridge == null || entity == null || StringUtils.isEmpty(sql)) {return null;}Connection connection = null;Statement statement = null;try{connection = cpds.getConnection();statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);while (resultSet.next()) {return  (T) new BeanProcessor(bridge.getColumnToPropertyOverride()).toBean(resultSet, entity);}}catch(Exception e) {logger.error("DbUtils queryOne failed", e);}finally {if (connection != null) {try{connection.close();}catch(Exception e2) {logger.error("DbUtils queryOne failed", e2);}}}return null;}/** * 查询返回bean实体列表 * @param sql * @param entity * @return */@SuppressWarnings("unchecked")public static <T> List<T> queryMany(String sql, Class<?> entity) {List<T> result = new ArrayList<>();Connection connection = null;Statement statement = null;try{connection = cpds.getConnection();statement = connection.createStatement();ResultSet resultSet = statement.executeQuery(sql);Object bean = entity.newInstance();while (resultSet.next()) {bean = new BeanProcessor().toBean(resultSet, entity);result.add((T) bean);}}catch(Exception e) {logger.error("DbUtils queryMany failed", e);}finally {if (connection != null) {try{connection.close();}catch(Exception e2) {logger.error("DbUtils queryMany failed", e2);}}}return result;}/** * 查询返回一个map * @param sql * @param entity * @return */public static Map<String, Object> queryMap(String sql) {Connection connection = null;Statement statement = null;Map<String, Object> result = new HashMap<>();try{connection = cpds.getConnection();statement = connection.createStatement();ResultSet rs = statement.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();while (rs.next()) {int cols = rsmd.getColumnCount();for (int i = 1; i <= cols; i++){String columnName = rsmd.getColumnLabel(i);if ((null == columnName) || (0 == columnName.length())) {columnName = rsmd.getColumnName(i);}result.put(columnName, rs.getObject(i));}break;}}catch(Exception e) {logger.error("DbUtils queryMap failed", e);}finally {if (connection != null) {try{connection.close();}catch(Exception e2) {logger.error("DbUtils queryMap failed", e2);}}}return result;}/** * 查询返回一个map * @param sql * @param entity * @return */public static List<Map<String, Object>> queryMapList(String sql) {Connection connection = null;Statement statement = null;List<Map<String, Object>> result = new ArrayList<>();try{connection = cpds.getConnection();statement = connection.createStatement();ResultSet rs = statement.executeQuery(sql);ResultSetMetaData rsmd = rs.getMetaData();while (rs.next()) {int cols = rsmd.getColumnCount();Map<String, Object> map = new HashMap<>();for (int i = 1; i <= cols; i++){String columnName = rsmd.getColumnLabel(i);if ((null == columnName) || (0 == columnName.length())) {columnName = rsmd.getColumnName(i);}map.put(columnName, rs.getObject(i));}result.add(map);}}catch(Exception e) {logger.error("DbUtils queryMapList failed", e);}finally {if (connection != null) {try{connection.close();}catch(Exception e2) {logger.error("DbUtils queryMapList failed", e2);}}}return result;}/** * 执行特定的sql语句 * @param sql * @return */public static boolean executeSql(String sql) {if (StringUtils.isEmpty(sql)) {return true;}Connection connection = null;Statement statement = null;try{connection = cpds.getConnection();statement = connection.createStatement();statement.execute(sql);return true;}catch (Exception e) {logger.error("DbUtils executeSql failed", e);}finally {if (connection != null) {try{connection.close();}catch(Exception e2) {logger.error("DbUtils executeSql failed", e2);}}}return false;}}

11.至此，整个orm持久化框架就已经完成了。限于篇幅，部分类代码没有贴上来。整个代码目录结构如下

12. junit测试代码如下

package com.kingston.test;import static org.junit.Assert.*;import org.junit.Before;import org.junit.Test;import com.kingston.entity.Player;import com.kingston.orm.OrmProcessor;import com.kingston.utils.DbUtils;public class TestEntity {@Beforepublic void init() {OrmProcessor.INSTANCE.initOrmBridges();}@Testpublic void testQuery() {Player player = DbUtils.queryOne("select * from player where id=1" , Player.class);assertTrue(player.getName().equals("kingston"));}@Testpublic void testUpdate() {Player player = DbUtils.queryOne("select * from player where id=1" , Player.class);player.setName("Hello");player.setUpdate();player.save();//checkPlayer tmpPlayer = DbUtils.queryOne("select * from player where id=1" , Player.class);assertTrue(tmpPlayer.getName().equals("Hello"));//rollbackplayer.setName("kingston");player.setUpdate();player.save();}@Testpublic void testInsert() {Player player = new Player();player.setNo(666);player.setName("younger");player.setInsert();player.save();//checkPlayer tmpPlayer = DbUtils.queryOne("select * from player where id=" + player.getNo() , Player.class);assertTrue(tmpPlayer.getName().equals("younger"));//rollbackplayer.setDelete();player.save();}@Testpublic void testDelete() {Player player = DbUtils.queryOne("select * from player where id=1" , Player.class);player.setDelete();player.save();//checkPlayer tmpPlayer = DbUtils.queryOne("select * from player where id=" + player.getNo() , Player.class);assertTrue(tmpPlayer == null);//rollbackplayer.setName("kingston");player.setInsert();player.save();}}

13. 实际应用中，当玩家部分数据发生改变，可以把对应的实体对象放在一个生产者队列，消费者线程定时检查队列元素的db状态，然后生成对应的sql语句执行。

个人感觉，这种orm框架比mybatics好多了，起码不用写一堆重复的crud语句。 YYing^-^ 

（完整的maven项目代码请移步git地址）