首页 > 代码库 > 使用orm框架定制用户数据持久化方案

使用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的骨架实现,对各种抽象方法提供默认的实现
package com.kingston.cache;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.kingston.utils.DbUtils;
import com.kingston.utils.SqlUtils;

public abstract class AbstractCacheable extends Cacheable {
	
	private static Logger logger = LoggerFactory.getLogger(AbstractCacheable.class); 

	@Override
	public DbStatus getStatus() {
		return this.status;
	}

	@Override
	public final boolean isInsert() {
		return this.status == DbStatus.INSERT;
	}

	@Override
	public final boolean isUpdate() {
		return this.status == DbStatus.UPDATE;
	}

	@Override
	public final boolean isDelete() {
		return this.status == DbStatus.DELETE;
	}

	public void setInsert() {
		this.status = DbStatus.INSERT;
	}

	public final void setUpdate(){
		this.status = DbStatus.UPDATE;
	}

	public final void setDelete(){
		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() {
			@Override
			public 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 = http://www.mamicode.com/null;>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 = http://www.mamicode.com/ReflectUtils.getMethodValue(entity, property);>9. 工具类SqlUtils根据实体不同的db状态拿到对应的sql语句
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 = http://www.mamicode.com/ReflectUtils.getMethodValue(entity, property);>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 {

	@Before
	public void init() {
		OrmProcessor.INSTANCE.initOrmBridges();
	}

	@Test
	public void testQuery() {
		Player player = DbUtils.queryOne("select * from player where id=1" , Player.class);
		assertTrue(player.getName().equals("kingston"));
	}

	@Test
	public void testUpdate() {
		Player player = DbUtils.queryOne("select * from player where id=1" , Player.class);
		player.setName("Hello");
		player.setUpdate();
		player.save();

		//check
		Player tmpPlayer = DbUtils.queryOne("select * from player where id=1" , Player.class);
		assertTrue(tmpPlayer.getName().equals("Hello"));
		
		//rollback
		player.setName("kingston");
		player.setUpdate();
		player.save();
	}

	@Test
	public void testInsert() {
		Player player = new Player();
		player.setNo(666);
		player.setName("younger");
		player.setInsert();
		
		player.save();
		
		//check
		Player tmpPlayer = DbUtils.queryOne("select * from player where id=" + player.getNo() , Player.class);
		assertTrue(tmpPlayer.getName().equals("younger"));
		
		//rollback
		player.setDelete();
		player.save();
	}

	@Test
	public void testDelete() {
		Player player = DbUtils.queryOne("select * from player where id=1" , Player.class);
		player.setDelete();
		player.save();
		
		//check
		Player tmpPlayer = DbUtils.queryOne("select * from player where id=" + player.getNo() , Player.class);
		assertTrue(tmpPlayer == null);
		
		//rollback
		player.setName("kingston");
		player.setInsert();
		player.save();
	}
	
}
13. 实际应用中,当玩家部分数据发生改变,可以把对应的实体对象放在一个生产者队列,消费者线程定时检查队列元素的db状态,然后生成对应的sql语句执行。

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

(完整的maven项目代码即将上传到git)








使用orm框架定制用户数据持久化方案