用java自定义ORM框架实现数据持久化
1. ORM介绍：
对象关系映射（Object Relational Mapping），简称ORM。这是一种程序技术，把面向对象编程中对象模型与关系型数据库里的表记录进行映射。从效果上说，它其实是创建了一个可在编程语言里使用的–“虚拟对象数据库”。
2. 使用背景：
假如一款应用需要每时每刻都对用户的数据进行频繁的操作，那么每次用户的业务行为都会往数据库生成一条执行语句的话，那么数据库很快就会进入到瓶颈。正常的情况是用户操作只会影响内存里的缓存数据，将需要修改的实体对象（Entity）放到一个生产者队列里，然后通过专门的消费者线程异步执行sql操作。
3. 包介绍
3. 主要类文件介绍
- Cacheable抽象类主要是对需要持久化的对象的一种抽象，是对象各种db状态的转换。 ```java package com.yunlaiwu.cache;
public abstract class Cacheable {
/** 当前实体对象的db状态 */
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的骨架实现，对各种抽象方法提供默认的实现
```java
package com.yunlaiwu.cache;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.yunlaiwu.utils.DbUtils;
import com.yunlaiwu.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);
}
}
}
- DbStatus表示各种db状态 ```java package com.yunlaiwu.cache;
public enum DbStatus {
/** 无需入库 */
NORMAL,
/** 需要更新 */
UPDATE,
/** 需要插入 */
INSERT,
/** 需要删除 */
DELETE,
; }
4. 采用注解来进行orm映射，注解命名借鉴了hibernate的命名，分别有：
```java
package com.yunlaiwu.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) // 只能用来描述类、接口(包括注解类型) 或enum声明
@Retention(RetentionPolicy.RUNTIME) // 程序运行时执行
public @interface Entity {
/** 不为空则说明表名字跟entity类名字不一致 */
String table() default "";
}
package com.yunlaiwu.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) // 只能用来描述类中的Field
@Retention(RetentionPolicy.RUNTIME) // 运行时执行
public @interface Column {
/** 不为空则说明表字段名字跟entity属性不一致 */
String name() default "";
}
package com.yunlaiwu.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.yunlaiwu.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;
/** 详细的将entityBean与表之间进行映射的一个类, 可以理解成这个类包含了表和bean所有的信息,并且一一对应 **/
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.yunlaiwu.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.yunlaiwu.annotation.Column;
import com.yunlaiwu.annotation.Entity;
import com.yunlaiwu.annotation.Id;
import com.yunlaiwu.exception.OrmConfigExcpetion;
import com.yunlaiwu.utils.ClassFilter;
import com.yunlaiwu.utils.ClassScanner;
import com.yunlaiwu.utils.StringUtils;
/** 用枚举项当做一个持久化对象,进行缓存entityBean和table的关系(Map) **/
public enum OrmProcessor {
INSTANCE;
/** entity与对应的ormbridge的映射关系 */
private Map<Class<?>, OrmBridge> classOrmMapperr = new HashMap<>();
/** 拿到所有entity类并创建对应的bridge对象,存到Mapper中 **/
public void initOrmBridges() {
Set<Class<?>> entityClazzs = listEntityClazzs();
for (Class<?> clazz:entityClazzs) {
OrmBridge bridge = createBridge(clazz);
this.classOrmMapperr.put(clazz, bridge);
}
}
/** 使用反射的方式,动态操作OrmBridges对象 **/
private OrmBridge createBridge(Class<?> clazz) {
OrmBridge bridge = new OrmBridge();
//拿到class的Entity注解对象
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());
}
//获取该类所有声明的字段,包括public、private和proteced，但是不包括父类的字段
Field[] fields = clazz.getDeclaredFields();
for (Field field:fields) {
//拿到field的Column注解对象
Column column = field.getAnnotation(Column.class);
//拿到field声明时的名字
String fieldName = field.getName();
try{
//如果声明field有Column注解,那么拿到对应getter,setter方法,并存放到bridge对象的getterMethor(HashMap)中
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);
}
//如果field上有主键注解的话,那么放到bridge对象中的主键集合uniqueProperties(Set)里
if (field.getAnnotation(Id.class) != null) {
bridge.addUniqueKey(fieldName);
}
//如果column注解的name属性非空,则加入到bridge对象的property与表column的映射集合(Map)中.
if (!StringUtils.isEmpty(column.name())) {
bridge.addPropertyColumnOverride(fieldName, column.name());
}
//把entity的声明的字段名添加到bridge的持久化字段的集合(Set)里
bridge.addProperty(fieldName);
}catch(Exception e) {
throw new OrmConfigExcpetion(e);
}
// 如果没有唯一索引,那么抛出异常
if (bridge.getQueryProperties().size() <= 0) {
throw new OrmConfigExcpetion(clazz.getSimpleName() + " entity 没有查询索引主键字段");
}
}
return bridge;
}
/** 读取包下面所有带有Entity注解的class **/
private Set<Class<?>> listEntityClazzs() {
return ClassScanner.getClasses("com.yunlaiwu.entity",
/**使用内部类实现ClassFilter接口,并重写accept方法,**/
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.yunlaiwu.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;
/** BeanProcessor类主要是用于将jdbc的ResultSet反射为对应的entity对象，这个类是从apache dbutils框架拿来用的 **/
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()))
{
Class c = params[0].asSubclass(Enum.class);
value = Enum.valueOf(c, (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);
}
}
- SqlFactory类主要是通过反射技术，将实体转换为对应的insert,update,delete语句
package com.yunlaiwu.orm;
import java.lang.reflect.Method;
import java.util.List;
import java.util.Map;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.yunlaiwu.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需要考虑and
List<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();
}
}
- DbUtils是最重要的工具类了，提供了根据查询语句返回一个实体对象或实体对象列表；提供执行更新、插入、删除操作的接口。更为通用的是，提供通过sql返回一个map对象,map列表的接口。有了这些接口，单表查询、多表查询、执行任意sql都成为可能。
package com.yunlaiwu.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.yunlaiwu.orm.BeanProcessor;
import com.yunlaiwu.orm.OrmBridge;
import com.yunlaiwu.orm.OrmProcessor;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DbUtils {
private static Logger logger = LoggerFactory.getLogger(DbUtils.class);
// 生成c3p0数据源对象,会自动读取配置文件
private static ComboPooledDataSource cpds = new ComboPooledDataSource();
static{
try {
//加载JDBC驱动
cpds.setDriverClass( "com.mysql.cj.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;
}
}
- junit测试代码如下
package com.yunlaiwu.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();
}
}