为mybatis 增加通用的crud 方法

仅供学习参考之用，不要用于生产环境！！！

 

在项目开发中使用mybatis 经常要对着一个 db 对应的对象写 crud 方法,这是很恼人的

比如下面的表

 

对应的 bean 如下

 

import java.util.Date;

public class DBEntityTest {

	private Integer id;
	
	private String uid;
	
	private String test;
	
	private Date buyDate;
	
	private Date createDate;
	
	private Date updateDate;
        
        //geters and setters...

}

通常要对这个表进行增删改差 需要这样写

首先添加对应的 mybatis mapper

 

public interface OriginDao {

	public int insertDBEntityTest(DBEntityTest dbEntityTest);
	
	public int updateDBEntityTest(DBEntityTest dbEntityTest);
}

然后编写对应的xml

 

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.kingdee.finance.p2p.dao.testentity.OriginDao">
	
	<insert id="insertDBEntityTest" parameterType="com.kingdee.finance.p2p.entity.DBEntityTest" useGeneratedKeys="true" keyProperty="id">
		insert into test(id,uid,test,create_date,update_date)	
		values(default,#{uid},#{test},now(),now())
	</insert>
	<update id="updateDBEntityTest" parameterType="com.kingdee.finance.p2p.entity.DBEntityTest">
		update test 
		<set>
			<if test="uid!=null">
				uid=#{uid},
			</if>
			<if test="test!=null">
				test=#{test},
			</if>
			update_date= now()
		</set>
		where id=#{id}
	</update>
</mapper>

以上示例仅有 insert update , select 和 delete 类似

 

mybatis 相对hibernate 的优点是 sql 的易于维护性, 但缺点也非常明显,就是任何操作都要我们编写对应的mapper

两者各有所长,但能不能在mybatis 的基础上优化一下,让普通的单表的crud 不再需要我们手动编写,而复杂的操作再交给mybatis 进行呢,下面我就按照这个思路来改进

首先 or-mapping , 我这里使用的是mysql 数据库,希望使用注解来标记bean 做到与数据库表结构的映射

 

import java.util.Date;

import com.kingdee.finance.p2p.annotation.db.DBColumn;
import com.kingdee.finance.p2p.annotation.db.DBEntity;
import com.kingdee.finance.p2p.annotation.db.KeyColumn;

@DBEntity("test")
public class DBEntityTest {

	@KeyColumn(useGeneratedKeys=true)
	@DBColumn(value = "id",insertIfNull="default")	
	private Integer id;
	
	@DBColumn("uid") 
	private String uid;
	
	@DBColumn(value="test",insertIfNull="'456789'")	
	private String test;
	
	@DBColumn(value="buy_date")	
	private Date buyDate;
	
	@DBColumn(value="create_date",insertIfNull="now()")	
	private Date createDate;
	
	@DBColumn(value="update_date",insertIfNull="now()",updateIfNull="now()")	
	private Date updateDate;
        
        getters and setters ...省略

}

 

下面看一下我实现的调用方法

 

	
              //插入
		DBEntityTest dbEntityTest = new DBEntityTest();
		dbEntityTest.setTest("123");
		dbEntityTest.setBuyDate(new Date());
		int rows= testEntityServiceImpl.insertDBEntity(dbEntityTest);
		
		//修改
		//将 id=1007 的行 test 改为  '123' buy_date 改为 当前时间
		DBEntityTest dbEntityTest2 = new DBEntityTest();
		dbEntityTest2.setId(1007);
		dbEntityTest2.setTest("123");
		dbEntityTest2.setBuyDate(new Date());
		int row2 = testEntityServiceImpl.updateDBEntityByKey(dbEntityTest2);
		
		//删除
		//将 id=1007 的行删除
		DBEntityTest dbEntityTest3 = new DBEntityTest();
		dbEntityTest3.setId(1007);
		testEntityServiceImpl.deleteDBEntityByKey(dbEntityTest3);
		
		//查询
		//查询出 id=1007的行
		DBEntityTest dbEntityTest4 = new DBEntityTest();
		dbEntityTest4.setId(1007);
		dbEntityTest4=testEntityServiceImpl.queryDBEntitySingle(dbEntityTest4);
		//查出 test=123 且 buy_date = 当前的行
		DBEntityTest dbEntityTest5 = new DBEntityTest();
		dbEntityTest5.setTest("123");
		dbEntityTest5.setBuyDate(new Date());
		dbEntityTest5=testEntityServiceImpl.queryDBEntitySingle(dbEntityTest5);
		
		//查出 test = 123 的list
		DBEntityTest dbEntityTest6 = new DBEntityTest();
		dbEntityTest6.setTest("123");
		List<DBEntityTest> list = testEntityServiceImpl.queryDBEntityList(dbEntityTest6);
		
		//查出 test = 123 的list 按createDate asc, buyDate desc
		List<DBEntityTest> list2 = testEntityServiceImpl.queryDBEntityList(dbEntityTest6, "createDate asc","buyDate desc");
		
		//查出 test = 123 的list 带分页信息的List
		Page<DBEntityTest> list3=testEntityServiceImpl.queryDBEntityList(dbEntityTest6, 1, 10);
		
		
		//复杂查询
		//查出 id > 5 且 buy_date <= 当前 的 list
		List<DBEntityTest> list4 = testEntityServiceImpl.queryDBEntityListComplex(DBEntityTest.class, new ComplexCond().col("id").gt(5).and().col("buyDate").lte(new Date()));
		
                
                // 查出  id>5 或(test = 123 且 uid = null) 
                //or 和 and 都有 or()  or(Object)/ and() and(Object) 两种可以嵌套使用
		List<DBEntityTest> list5 = testEntityServiceImpl.queryDBEntityListComplex(DBEntityTest.class, new ComplexCond().col("id").gt(5).or(
				new ComplexCond().col("test").eq("123").and().col("uid").isNull()
				));

 

 

 

 

 

 

 

没有用jpa 是觉得jpa 的注解的功能太多,自己感觉用不到

下面介绍上面类中使用到的自定义注解

1,@DBEntity 标记该类对应的数据库 的 库名 和 表名

 

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface DBEntity {

	/**
	 * 数据库库名
	 * @return
	 */
	String database() default "";
	/**
	 * 数据库表名
	 * @return
	 */
	String value();
		
}

2,@DBColumn 标记该类的字段和数据库表字段的对应关系

 

 

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface DBColumn {

	String value() default ""; //数据库中的字段名
	String insertIfNull() default "";  // 示例   100,'abc' --字符串注意带引号, defalut ,now() 
	String updateIfNull() default "";
	
}

3,@KeyColumn 标记该字段是主键

 

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface KeyColumn {

	/**
	 * 是否使用生成的主键,默认false
	 * 
	 */
	boolean useGeneratedKeys() default false;
}

 

注解标记好了类,下面的思路就是

1,当一个bean 调用增删该查某个方法时, 将注解的值和字段的值都读出来

我们使用一个叫EntityInfo 的类来存放这个 信息

下面贴出这个类

 

package com.kingdee.finance.p2p.dao.generic;

import java.io.Serializable;
import java.util.LinkedList;
import java.util.List;

public class EntityInfo implements Serializable,Cloneable{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 8986658628198395689L;
	private String className;
	private String database;
	private String tableName;
	private List<EntityInfoCol> cols = new LinkedList<EntityInfoCol>();
	private EntityInfoCol keyCol;
	private Object generatedKey; //用于接收 生成的自增主键
	
	public String getClassName() {
		return className;
	}
	public void setClassName(String className) {
		this.className = className;
	}
	public String getDatabase() {
		return database;
	}
	public void setDatabase(String database) {
		this.database = database;
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public List<EntityInfoCol> getCols() {
		return cols;
	}
	public void setCols(List<EntityInfoCol> cols) {
		this.cols = cols;
	}
	
	public Object getGeneratedKey() {
		return generatedKey;
	}
	public void setGeneratedKey(Object generatedKey) {
		this.generatedKey = generatedKey;
	}
	public EntityInfoCol getColByEntityColName(String name){
		for(EntityInfoCol eachCol:cols){
			if(eachCol.getEntityColName().equals(name)){
				return eachCol;	
			}
		}
		return null;
	}
	
	public EntityInfoCol getKeyCol(){
		keyCol=null;
		for(EntityInfoCol eachCol:cols){
			if(eachCol.getIsKeyColumn()){
				keyCol = eachCol;
			}
		}
		return keyCol;
	}
	
	public boolean hasKeyCol(){
		return getKeyCol()!=null;
	}
	@Override
	protected EntityInfo clone() throws CloneNotSupportedException {
		EntityInfo a=(EntityInfo) super.clone();
		List<EntityInfoCol> cols = new LinkedList<EntityInfoCol>();
		for(EntityInfoCol orgCol:this.cols){
			cols.add(orgCol.clone());
		}
		a.setCols(cols);
		return a;
	}
	@Override
	public String toString() {
		return "EntityInfo [className=" + className + ", database=" + database
				+ ", tableName=" + tableName + ", cols=" + cols
				+ ", generatedKey=" + generatedKey + "]";
	}
	
	
	
}

 

package com.kingdee.finance.p2p.dao.generic;

public class EntityInfoCol implements Cloneable{

	
	private String entityColName;
	private String dbColName;
	private String insertIfNull;
	private String updateIfNull;
	private Boolean isKeyColumn;
	private Boolean useGeneratedKeys;
	private Object value;
	public String getEntityColName() {
		return entityColName;
	}
	public void setEntityColName(String entityColName) {
		this.entityColName = entityColName;
	}
	public String getDbColName() {
		return dbColName;
	}
	public void setDbColName(String dbColName) {
		this.dbColName = dbColName;
	}
	public String getInsertIfNull() {
		return insertIfNull;
	}
	public void setInsertIfNull(String insertIfNull) {
		this.insertIfNull = insertIfNull;
	}
	public String getUpdateIfNull() {
		return updateIfNull;
	}
	public void setUpdateIfNull(String updateIfNull) {
		this.updateIfNull = updateIfNull;
	}
	public Boolean getIsKeyColumn() {
		return isKeyColumn;
	}
	public void setIsKeyColumn(Boolean isKeyColumn) {
		this.isKeyColumn = isKeyColumn;
	}
	public Boolean getUseGeneratedKeys() {
		return useGeneratedKeys;
	}
	public void setUseGeneratedKeys(Boolean useGeneratedKeys) {
		this.useGeneratedKeys = useGeneratedKeys;
	}
	public Object getValue() {
		return value;
	}
	public void setValue(Object value) {
		this.value = value;
	}
	@Override
	public String toString() {
		return "EntityInfoCol [entityColName=" + entityColName + ", dbColName="
				+ dbColName + ", insertIfNull=" + insertIfNull
				+ ", updateIfNull=" + updateIfNull + ", isKeyColumn="
				+ isKeyColumn + ", useGeneratedKeys=" + useGeneratedKeys
				+ ", value=" + value + "]";
	}
	@Override
	protected EntityInfoCol clone() throws CloneNotSupportedException {
		// TODO Auto-generated method stub
		return (EntityInfoCol) super.clone();
	}
	
	
	
	
	
}

 

 

下面贴出 实现类

 

 

 

 

package com.kingdee.finance.p2p.dao.generic;

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.github.orderbyhelper.OrderByHelper;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.kingdee.finance.p2p.annotation.db.DBColumn;
import com.kingdee.finance.p2p.annotation.db.DBEntity;
import com.kingdee.finance.p2p.annotation.db.KeyColumn;


@Repository
public class GenericDaoImpl implements GenericDao {

	private final Logger logger = LoggerFactory.getLogger(GenericDaoImpl.class);
	
	
	@Autowired
	private GenericMybatisDao genericDao;
	
	private Map<String, EntityInfo> cache=new HashMap<String, EntityInfo>();
	
	
	private EntityInfo getInfoNoValue(Class clasz){
		
		String className=clasz.getName();
		//使用类名从缓存读取
		if(cache.get(className) != null){
			try {
				return cache.get(className).clone();
			} catch (CloneNotSupportedException e) {
				e.printStackTrace();
			}
		}
		//如果没有则检查注解
		checkDBEntity(clasz);
		
		EntityInfo info=new EntityInfo();
		info.setClassName(className);
		
		//获取表信息
		DBEntity dbEntity=(DBEntity) clasz.getAnnotation(DBEntity.class);
		if(StringUtils.isNotBlank(dbEntity.database())){
			info.setDatabase(dbEntity.database());
		}
		info.setTableName(dbEntity.value());
		//获取字段信息
		Field[] fields=clasz.getDeclaredFields();
		for(Field eachField:fields){
			EntityInfoCol curCol=new EntityInfoCol();
			//读取注解
			DBColumn dbColumn = eachField.getAnnotation(DBColumn.class);
			if(dbColumn==null) continue;
			String entityColName = eachField.getName();//class 字段名
			String dbColName =  dbColumn.value(); //db字段名
			String insertIfNull=dbColumn.insertIfNull().trim();
			String updateIfNull= dbColumn.updateIfNull().trim();
			//表字段默认与class字段相同
			if(StringUtils.isBlank(dbColName)){
				dbColName=entityColName;
			}
			
			curCol.setEntityColName(entityColName);
			curCol.setDbColName(dbColName);
			if(StringUtils.isNotBlank(insertIfNull)){
				curCol.setInsertIfNull(insertIfNull);
			}
			if(StringUtils.isNotBlank(updateIfNull)){
				curCol.setUpdateIfNull(updateIfNull);
			}
			
			KeyColumn keyColumn = eachField.getAnnotation(KeyColumn.class);
			if(keyColumn!=null){
				curCol.setIsKeyColumn(true);
				curCol.setUseGeneratedKeys(keyColumn.useGeneratedKeys());
			}else {
				curCol.setIsKeyColumn(false);
				curCol.setUseGeneratedKeys(false);
			}
			
			info.getCols().add(curCol);
		}
		cache.put(className, info);
		return info;
	}
	
	private EntityInfo getInfoWithValue(Object entity){
		EntityInfo config= getInfoNoValue(entity.getClass());
		for(EntityInfoCol colConfg:config.getCols()){
			try {
				colConfg.setValue(PropertyUtils.getProperty(entity, colConfg.getEntityColName()));
			} catch (Exception e) {
				throw new IllegalArgumentException("read property from entity error");
			} 
		}
		return config;
	}
	
	
	
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private void checkDBEntity(Class clazz){
		DBEntity dbEntity=(DBEntity) clazz.getAnnotation(DBEntity.class);
		if(dbEntity==null)
			throw new IllegalArgumentException("not dbEntity ,plase check your beans with [DBEntity] annotation");
		
	}
	
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#saveDBEntity(java.lang.Object)
	 */
	@Override
	public int saveDBEntity(Object entity){
		EntityInfo info=getInfoWithValue(entity);
		
		if(info.getKeyCol().getValue() == null){
			return insertDBEntity(entity);
		}else {
			return updateDBEntityByKey(entity);
		}
	}
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#insertDBEntity(java.lang.Object)
	 */
	@Override
	public int insertDBEntity(Object entity){
		EntityInfo info=getInfoWithValue(entity);
		int rows=genericDao.insertDBEntity(info);
		//使用生成的自增长主键
		Object generatedKey=info.getGeneratedKey();
		EntityInfoCol col = info.getKeyCol();
		try {
			//原主键为空才填入  否则不填
			if(col.getValue()==null){
				BeanUtils.setProperty(entity, col.getEntityColName(), generatedKey);
			}
		} catch (Exception e) {
			throw new RuntimeException("set generatedKey error",e.fillInStackTrace());
		}
		return rows;
	}
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#insertDBEntityBatch(java.util.List)
	 */
	@Override
	public <T> int insertDBEntityBatch(List<T> entitys){
		List<EntityInfo> infos = new LinkedList<EntityInfo>();
		for(Object each:entitys){
			infos.add(getInfoWithValue(each));
		}
		int rows=genericDao.insertDBEntityBatch(infos);
		return rows;
	}
	
	
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#updateDBEntityByKey(java.lang.Object)
	 */
	@Override
	public int updateDBEntityByKey(Object entity){
		EntityInfo info=getInfoWithValue(entity);
		if(!info.hasKeyCol()||info.getKeyCol().getValue()==null){
			throw new IllegalArgumentException("update cannot done when key property is null");
		}
		int rows = genericDao.updateDBEntityByKey(info);
		return rows;
	}


	

	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#deleteDBEntityByKey(java.lang.Object)
	 */
	@Override
	public int deleteDBEntityByKey(Object entity){
		EntityInfo info = getInfoWithValue(entity);
		if(!info.hasKeyCol()){
			throw new IllegalArgumentException("update cannot done when key property is null");
		}
		int rows = genericDao.deleteDBEntityByKey(info);
		return rows;
	}
	
	private <T> T getFirst(List<T> list){
		if (list.isEmpty()) {
			return null;
		}
		return list.get(0);
	}
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#queryDBEntity(T)
	 */
	@Override
	public <T> T queryDBEntitySingle(T entity){
		return getFirst(queryDBEntityList(entity));
	}
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#queryDBEntityList(T)
	 */
	@Override
	public <T> List<T> queryDBEntityList(T entity){
		return queryDBEntityList(entity, null);
	}
	
	
	
//	/* (non-Javadoc)
//	 * @see com.kingdee.finance.p2p.service.generic.GenericService#queryDBEntity(T, java.lang.String)
//	 */
//	@Override
//	public <T> T queryDBEntity(T entity,String... orderBys){
//		return getFirst(queryDBEntityList(entity, orderBys));
//	}
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#queryDBEntityList(T, java.lang.String)
	 */
	@Override
	public <T> List<T> queryDBEntityList(T entity,String... orderBys){
		return queryDBEntityList(entity, 1, 0, orderBys);
	}
	
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#queryDBEntityList(java.lang.Object, int, java.lang.String[])
	 */
	@Override
	public <T> List<T> queryDBEntityListTop(T entity, int top, String... orderBys) {
		// TODO Auto-generated method stub
		return queryDBEntityList(entity, 1, top, orderBys);
	}
	
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#queryDBEntityList(T, int, int)
	 */
	@Override
	public <T> Page<T> queryDBEntityList(T entity,int pageNum,int pageSize){
		return queryDBEntityList(entity, pageNum, pageSize, null);
	}
	
	
	
	
	
	/* (non-Javadoc)
	 * @see com.kingdee.finance.p2p.service.generic.GenericService#queryDBEntityList(T, int, int, java.lang.String)
	 */
	@Override
	@SuppressWarnings("unchecked")
	public <T> Page<T> queryDBEntityList(T entity,int pageNum,int pageSize,String... orderBys){
		EntityInfo info=getInfoWithValue(entity);
		String orderStr=this.orderParse(entity.getClass(), orderBys);
		if(StringUtils.isNotBlank(orderStr)){
			OrderByHelper.orderBy(orderStr);
		}
		//分页
		PageHelper.startPage(pageNum, pageSize, true, false, true);
		Page<Map<String, Object>> list = genericDao.queryDBEntity(info);
		Page<T> result= (Page<T>) list.clone();
		result.clear();
		for(Map<String, Object> eachBeanMap:list){
			T eachObject = null;
			try {
				eachObject = (T)(entity.getClass().newInstance());
			} catch (Exception e1) {
				logger.error("instantiate new bean error.",e1.fillInStackTrace());
			} 
			
			try {
				BeanUtils.copyProperties(eachObject, eachBeanMap);
			} catch (Exception e) {
				logger.error("copyProperties from Map to bean error.",e.fillInStackTrace());
			} 
			result.add(eachObject);
		}
		return result;
	}
	@Override
	public <T> T queryDBEntitySingleComplex(Class<T> clazz,	ComplexCond condition){
		return getFirst(queryDBEntityListComplex(clazz, condition));
	}
	@Override
	public <T> List<T> queryDBEntityListComplex(Class<T> clazz,	ComplexCond condition) {
		return queryDBEntityListComplex(clazz, condition, 1, 0);
	}
	@Override
	public <T> Page<T> queryDBEntityListComplexTop(Class<T> clazz,	ComplexCond condition, int top,String orderBys) {
		return queryDBEntityListComplex(clazz, condition, 1, top,orderBys);
	}
	@Override
	public <T> Page<T> queryDBEntityListComplex(Class<T> clazz,	ComplexCond condition, int pageNum, int pageSize) {
		return queryDBEntityListComplex(clazz, condition, pageNum, pageSize,null);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public <T> Page<T> queryDBEntityListComplex(Class<T> clazz,	ComplexCond condition, int pageNum, int pageSize,String... orderBys) {
		EntityInfo info=getInfoNoValue(clazz);
		String orderStr=this.orderParse(clazz, orderBys);
		if(StringUtils.isNotBlank(orderStr)){
			OrderByHelper.orderBy(orderStr);
		}
		
		//分页
		PageHelper.startPage(pageNum, pageSize, true, false, true);
		//将condition 中 col 替换为数据库对应字段
		for (ComplexConditionNode eachNode:condition.getNodes()) {
			if("col".equals(eachNode.getLink())){
				EntityInfoCol entityInfoCol= info.getColByEntityColName((String) eachNode.getObjects()[0]);
				eachNode.setObjects(entityInfoCol.getDbColName());
			}
		}
		
		Page<Map<String, Object>> list = genericDao.queryDBEntityComplex(info,condition);
		Page<T> result= (Page<T>) list.clone();
		result.clear();
		for(Map<String, Object> eachBeanMap:list){
			T eachObject = null;
			try {
				eachObject = (T)(clazz.newInstance());
			} catch (Exception e1) {
				logger.error("instantiate new bean error.",e1.fillInStackTrace());
			} 
			
			try {
				BeanUtils.copyProperties(eachObject, eachBeanMap);
			} catch (Exception e) {
				logger.error("copyProperties from Map to bean error.",e.fillInStackTrace());
			} 
			result.add(eachObject);
		}
		return result;
	}
	
	/**
	 * order解析 将bean property 解析为数据库 column
	 * @param entity
	 * @param orderBys
	 * @return
	 */
	@SuppressWarnings({ "rawtypes" })
	private String orderParse(Class clazz,String... orderBys){
		
		if(orderBys==null){
			return null;
		}
		EntityInfo info = getInfoNoValue(clazz);
		//排序解析处理--将bean property 解析为数据库 column
		StringBuffer orderStr=new StringBuffer();
		for(int i= 0;i<orderBys.length;i++){
			String eachOrderBy=orderBys[i];
			if(StringUtils.isBlank(eachOrderBy)){
				continue;
			}
			String[] orderArray = eachOrderBy.split("\\s+");
			if(orderArray.length<1){
				throw new IllegalArgumentException("order parse error.");
			}
			String order = orderArray[0];
			String sort  = null;
			if(orderArray.length==1){
				sort="ASC";
			}else {
				sort=orderArray[1];
				if(!StringUtils.equalsIgnoreCase(sort, "asc")&&!StringUtils.equalsIgnoreCase(sort, "desc")){
					throw new IllegalArgumentException("order sort parse error.");
				}
			}
			EntityInfoCol infoCol= info.getColByEntityColName(order);
			orderStr.append(infoCol.getDbColName()).append("\t").append(sort);
			if(i<orderBys.length-1){
				orderStr.append(",");
			}
		}
		return orderStr.toString();
	}

	

	
	
}

 

实现类 以来的mybatis 的mapper

 

package com.kingdee.finance.p2p.dao.generic;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;

import com.github.pagehelper.Page;

public interface GenericMybatisDao {

	public Page<Map<String, Object>> queryDBEntity(EntityInfo entityInfo);
	
	public Page<Map<String, Object>> queryDBEntityComplex(@Param("entityInfo") EntityInfo entityInfo,@Param("complexCond")ComplexCond complexCond);
	
	public int insertDBEntity(EntityInfo entityInfo);
	
	public int insertDBEntityBatch(List<EntityInfo> entityInfo);
	
	public int updateDBEntityByKey(EntityInfo entityInfo);
	
	public int deleteDBEntityByKey(EntityInfo entityInfo);
	
}

还有mapper 对应的 xml

 

 

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.kingdee.finance.p2p.dao.generic.GenericMybatisDao">
	
	<select id="queryDBEntity" parameterType="com.kingdee.finance.p2p.dao.generic.EntityInfo" resultType="java.util.HashMap">
		select 
		<foreach collection="cols" item="eachCol" separator=",">
			`${eachCol.dbColName}` as ${eachCol.entityColName}
		</foreach>
		from <if test="database!=null">${database}.</if>${tableName}
		<where>
			<foreach collection="cols" item="eachCol" separator="and">
				<if test="eachCol.value!=null">
						${eachCol.dbColName} = #{eachCol.value}
				</if>
			</foreach>	
		</where>
	</select>
	
	<select id="queryDBEntityComplex" resultType="java.util.HashMap">
		select 
		<foreach collection="entityInfo.cols" item="eachCol" separator=",">
			`${eachCol.dbColName}` as ${eachCol.entityColName}
		</foreach>
		from <if test="entityInfo.database!=null">${entityInfo.database}.</if>${entityInfo.tableName}
		<where>
			<foreach collection="complexCond.nodes" item="node" separator=" ">
				 <choose>
					<when test='node.link=="col"'>${node.object}</when>
					<when test='node.link=="and"'>and</when>
					<when test='node.link=="or"'>or</when>
					<when test='node.link=="("'>(</when>
					<when test='node.link==")"'>)</when>
					<when test='node.link=="eq"'>= #{node.object}</when>
					<when test='node.link=="gt"'>> #{node.object}</when>
					<when test='node.link=="lt"'>< #{node.object}</when>
					<when test='node.link=="gte"'>>= #{node.object}</when>
					<when test='node.link=="lte"'><= #{node.object}</when>
					<when test='node.link=="bw"'>like '${node.object}%'</when>
					<when test='node.link=="bn"'>not like '${node.object}%'</when>
					<when test='node.link=="ew"'>like '%${node.object}' </when>
					<when test='node.link=="en"'>not like '%${node.object}'</when>
					<when test='node.link=="cn"'>like '%${node.object}%'</when>
					<when test='node.link=="nc"'>not like '%${node.object}%'</when>
					<when test='node.link=="nu"'>is null</when>
					<when test='node.link=="nn"'>is not null</when>
					<when test='node.link=="in"'>
						in 
						<foreach collection="node.objects" item="in" open="(" close=")" separator=",">
						#{in}
						</foreach>
					</when>
					<when test='node.link=="ni"'>
						not in 
						<foreach collection="node.objects" item="in" open="(" close=")" separator=",">
						#{in}
						</foreach>
					</when>
				</choose>
			</foreach>	
		</where>
	</select>
	
	<insert id="insertDBEntity" parameterType="com.kingdee.finance.p2p.dao.generic.EntityInfo" useGeneratedKeys="true" keyProperty="generatedKey">
		insert into <if test="database!=null">${database}.</if>${tableName} 
		<foreach collection="cols" item="eachCol" separator="," open="(" close=")">
			`${eachCol.dbColName}`
		</foreach>
		values
		<foreach collection="cols" item="eachCol" separator="," open="(" close=")" >
			<choose>
				<when test="eachCol.value==null and  eachCol.insertIfNull!=null">
					${eachCol.insertIfNull}
				</when>
				<otherwise>
					#{eachCol.value}
				</otherwise>
			</choose>
		</foreach>
	</insert>
	
	<insert id="insertDBEntityBatch" parameterType="java.util.List">
		<foreach collection="list" item="eachInfo" index="index" separator=",">
			<if test=" index == 0 ">
					insert into <if test="eachInfo.database!=null">${eachInfo.database}.</if>${eachInfo.tableName} 
					<foreach collection="eachInfo.cols" item="eachCol" separator="," open="(" close=")">
						`${eachCol.dbColName}`
					</foreach>
					values	
			</if>
			<foreach collection="eachInfo.cols" item="eachColumn" separator="," open="(" close=")" >
				<choose>
					<when test="eachColumn.value==null and  eachColumn.insertIfNull!=null">
						${eachColumn.insertIfNull}
					</when>
					<otherwise>
						#{eachColumn.value}
					</otherwise>
				</choose>
			</foreach>
		</foreach>
	</insert>
	
	<update id="updateDBEntityByKey" parameterType="com.kingdee.finance.p2p.dao.generic.EntityInfo">
			update <if test="database!=null">${database}.</if>${tableName} 
			<set>
				<foreach collection="cols" item="eachCol">
					<if test="eachCol.value!=null and eachCol.isKeyColumn == false">
						${eachCol.dbColName} = #{eachCol.value},				
					</if>
					<if test="eachCol.value==null and eachCol.updateIfNull!=null and eachCol.isKeyColumn == false">
						${eachCol.dbColName} = ${eachCol.updateIfNull},
					</if>
				</foreach>
			</set>
				
			<where>
				<if test="keyCol.value==null"> 
				1=0 and 
				</if>
				${keyCol.dbColName} = #{keyCol.value}
			</where>
	</update>
	<delete id="deleteDBEntityByKey" parameterType="com.kingdee.finance.p2p.dao.generic.EntityInfo">
		delete from <if test="database!=null">${database}.</if>${tableName}
		<where>
			<if test="keyCol.value==null"> 
				1=0 and 
			</if>
			${keyCol.dbColName} = #{keyCol.value}
		</where>
	</delete>
	
</mapper>

 

 

 

 

还有 复杂查询中用到的 条件类

 

 

 

 

 

package com.kingdee.finance.p2p.dao.generic;

import java.util.LinkedList;
import java.util.List;

/**
 * 复杂查询条件
 * @author lxn
 * 
 */
public class ComplexCond {

	
	List<ComplexConditionNode> nodes=new LinkedList<ComplexConditionNode>();
	
	
	public List<ComplexConditionNode> getNodes() {
		return nodes;
	}

	public ComplexCond col(String col){
		nodes.add(new ComplexConditionNode("col", col));
		return this;
	}
	
	public ComplexCond and(){
		nodes.add(new ComplexConditionNode("and"));
		return this;
	}
	public ComplexCond or(){
		nodes.add(new ComplexConditionNode("or"));
		return this;
	}
	
	public ComplexCond and(ComplexCond innerCondition){
		nodes.add(new ComplexConditionNode("and"));
		nodes.add(new ComplexConditionNode("("));
		nodes.addAll(innerCondition.getNodes());
		nodes.add(new ComplexConditionNode(")"));
		return this;
	}
	
	public ComplexCond or(ComplexCond innerCondition){
		nodes.add(new ComplexConditionNode("or"));
		nodes.add(new ComplexConditionNode("("));
		nodes.addAll(innerCondition.getNodes());
		nodes.add(new ComplexConditionNode(")"));
		return this;
	}
	
	public ComplexCond eq(Object value){
		nodes.add(new ComplexConditionNode("eq", value));
		return this;
	}
	
	public ComplexCond gt(Object value){
		nodes.add(new ComplexConditionNode("gt", value));
		return this;
	}
	
	public ComplexCond lt(Object value){
		nodes.add(new ComplexConditionNode("lt", value));
		return this;
	}
	
	public ComplexCond gte(Object value){
		nodes.add(new ComplexConditionNode("gte", value));
		return this;
	}
	
	public ComplexCond lte(Object value){
		nodes.add(new ComplexConditionNode("lte", value));
		return this;
	}
	
	public ComplexCond beginWith(Object value){
		nodes.add(new ComplexConditionNode("bw", value));
		return this;
	}
	
	public ComplexCond beginNotWith(Object value){
		nodes.add(new ComplexConditionNode("bn", value));
		return this;
	}
	
	public ComplexCond endWith(Object value){
		nodes.add(new ComplexConditionNode("ew", value));
		return this;
	}
	
	public ComplexCond endNotWith(Object value){
		nodes.add(new ComplexConditionNode("en", value));
		return this;
	}
	
	public ComplexCond contains(Object value){
		nodes.add(new ComplexConditionNode("cn", value));
		return this;
	}
	
	public ComplexCond notContains(Object value){
		nodes.add(new ComplexConditionNode("nc", value));
		return this;
	}	
	
	public ComplexCond isNull(){
		nodes.add(new ComplexConditionNode("nu"));
		return this;
	}
	
	
	public ComplexCond notNull(){
		nodes.add(new ComplexConditionNode("nn"));
		return this;
	}
	
	public ComplexCond in(Object... objects){
		nodes.add(new ComplexConditionNode("in",objects));
		return this;
	}
	
	public ComplexCond notIn(Object... objects){
		nodes.add(new ComplexConditionNode("ni",objects));
		return this;
	}

	@Override
	public String toString() {
		String result="";
		for (ComplexConditionNode eachNode:nodes) {
			result+= eachNode.getLink();
			result+=" ";
			if(eachNode.getObjects()==null){
				continue;
			}
			for(Object eachObject:eachNode.getObjects()){
				result+=eachObject.toString()+" ";
			}
		}
		return result;
	}
	
	
	
}

package com.kingdee.finance.p2p.dao.generic;

public class ComplexConditionNode {

	private String link;
	private Object[] objects;
	private Object object;

	public ComplexConditionNode(String link) {
		super();
		this.link = link;
	}

	public ComplexConditionNode(String link, Object... objects) {
		super();
		this.link = link;
		this.objects = objects;
	}

	public String getLink() {
		return link;
	}

	public void setLink(String link) {
		this.link = link;
	}

	public Object[] getObjects() {
		return objects;
	}

	public void setObjects(Object... objects) {
		this.objects = objects;
	}

	public Object getObject() {
		object = objects[0];
		return object;
	}

}

 

 

 

 

 

 

 

 

 

可以看到最终的实现方式还是调用了mybatis ,只不过将对象经过转换,调用了同一个mapper

查询方法 由于特殊性,增加了 复杂查询,也就是带complex 的查询方法,

该方法的条件 也就是 complexCond使用了链式操作的思想,将复杂的sql 条件予以简化

大大简化了单表复杂查询,

这一套代码 实现了 java code 层面只需要配置注解映射到数据库后就可以直接操作对象实现单表增删改查,

复杂的联合查询等操作还是交给半自动的mybatis来执行.长枪短炮并用,提高工作效率