基于Mybatis封装的增删改查实现通用自动化sql

1.基于map或javaBean的增删改查可实现不写dao接口和实现类以及xml，有效的提高开发速度。
2.支持自定义注解包括主键生成、列重复验证、列名、表名等
3.支持批量插入、批量更新、批量删除

<bean id="dynamicSqlSessionTemplate" class="com.mqy.mybatis.support.DynamicSqlSessionTemplate">
	<property name="dataSource" ref="dataSource" />
	<property name="sqlSessionFactory" ref="sqlSessionFactory" /> 
	<property name="convertNameProcessor"><bean class="com.mqy.mybatis.convert.DefaultConvertName"/></property><!-- 驼峰转下划线 -->
	<!--<property name="splitSign" value="," /> --> <!--批量操作的分隔符 选配--> 
	<!--<property name="annotationProcessor" ref="annotationProcessorTest"/>-->  <!--自定义注解实现策略 选配--> 
</bean>
<!-- 定义MyBatis SqlSessionFactory -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	<property name="dataSource" ref="dataSource" />
	<property name="plugins">
		<array>
			<bean class="cn.com.framework.orm.mybatis.interceptor.PagingInterceptor">
				<property name="dialect">
					<bean class="cn.com.framework.orm.mybatis.dialect.PostgreSQL81Dialect" />
				</property>
			</bean>
		</array>
	</property>
	<property name="mapperLocations">
		<list>
			<value>classpath*:com/mqy/mybatis/EasyMapper.xml</value>
		</list>
	</property>
</bean>

package com.fw.mybatis;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.transaction.TransactionConfiguration;
import org.springframework.transaction.annotation.Transactional;

import com.fw.mybatis.constant.JdbcConstants;
import com.fw.mybatis.support.DynamicSqlSessionTemplate;
import com.fw.web.core.acl.model.User;
import com.fw.web.core.base.util.WebContextUtils;


/**
 * 功能：</b>通用增删改测试类<br>
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:applicationContext-core.xml", "classpath:applicationContext-datasource.xml"})
@Transactional
@TransactionConfiguration(transactionManager = "transactionAdvice", defaultRollback = false)
public class JdbcManageUtilTest{
	
	private final static Logger logger = LoggerFactory.getLogger(JdbcManageUtilTest.class);
	
	@Autowired
	private ApplicationContext ctx;
	
	DynamicSqlSessionTemplate dynamicSqlSessionTemplate;
	
	@Before
	public void init(){
		logger.debug("模块初始化开始...");
//		String[] xmlCfg = new String[] { "classpath:applicationContext-core.xml", "classpath:applicationContext-datasource.xml"};
//		WebContextUtils.setAc(new ClassPathXmlApplicationContext(xmlCfg));
		WebContextUtils.setAc(ctx);
		dynamicSqlSessionTemplate = (DynamicSqlSessionTemplate)WebContextUtils.getAc().getBean("dynamicSqlSessionTemplate");
	}
	
	/**
	 * <b>功能描述：</b>map插入单条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testInsertMap(){
		Map<String, Object> pojoMap = new HashMap<>();
		pojoMap.put(JdbcConstants.TABLE_NAME, "acl_user");
		pojoMap.put("id", Math.round(Math.random() * 1000)+"");
		pojoMap.put(JdbcConstants.KEY, "id");//如果列名称是id可以不加入此行代码
		pojoMap.put("username", "abc");
		dynamicSqlSessionTemplate.insert(pojoMap);
	}
	
	/**
	 * <b>功能描述：</b>bean插入单条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testInsertBean(){
		User User = new User();
		User.setId(Math.round(Math.random() * 1000)+"");
		User.setUsername("222");
		dynamicSqlSessionTemplate.insert(User);
	}
	
	/**
	 * <b>功能描述：</b>批量map或bean插入多条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testInsertBatch(){
		List<Object> pojoList = new ArrayList<>();
		for (int i = 0; i < 3; i++) {
			Map<String, Object> pojoMap = new HashMap<>();
			pojoMap.put(JdbcConstants.TABLE_NAME, "acl_user");
			pojoMap.put("id", Math.round(Math.random() * 1000));
			pojoMap.put(JdbcConstants.KEY, "id");
			pojoMap.put("abc", 333);
			pojoList.add(pojoMap);
		}
		dynamicSqlSessionTemplate.insertBatch(pojoList);
		pojoList = new ArrayList<>();
		for (int i = 0; i < 3; i++) {
			User user = new User();
			user.setId(Math.round(Math.random() * 1000)+"");
			user.setUsername("abc");
			pojoList.add(user);
		}
		dynamicSqlSessionTemplate.insertBatch(pojoList);
	}
	
	/**
	 * <b>功能描述：</b>map修改单条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testUpdateMap(){
		Map<String, Object> params = new HashMap<>();
		params.put("username", "111");
		List<Map<String, Object>> paramList = dynamicSqlSessionTemplate.select("framework.selectMap", params);
		for(Map<String, Object> pojoMap:paramList){
			params.put(JdbcConstants.TABLE_NAME, "acl_user");
			params.put(JdbcConstants.ID, pojoMap.get(JdbcConstants.ID));
			dynamicSqlSessionTemplate.update(params);
		}
	}
	
	/**
	 * <b>功能描述：</b>bean修改单条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testUpdateBean(){
		User User = new User();
		User.setUsername("111");
		List<User> UserList = dynamicSqlSessionTemplate.select("framework.selectBean", User);
		for(User test:UserList){
			test.setUsername("abc");
			dynamicSqlSessionTemplate.update(test);
		}
	}
	
	/**
	 * <b>功能描述：</b>map删除单条或多条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testDeleteMap(){
		Map<String, Object> params = new HashMap<>();
		params.put("username", "333");
		String value = "";
		List<Map<String, Object>> paramList = dynamicSqlSessionTemplate.select("framework.selectMap", params);
		for(Map<String, Object> pojoMap:paramList){
			params.put(JdbcConstants.TABLE_NAME, "acl_user");
			value = value + "," + pojoMap.get(JdbcConstants.ID);
		}
		params.remove("username");
		params.put(JdbcConstants.TABLE_NAME, "acl_user");
		params.put(JdbcConstants.ID, value.replaceFirst(",", ""));
		dynamicSqlSessionTemplate.delete(params);
	}
	
	/**
	 * <b>功能描述：</b>map查询单条或多条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testSelectMap(){
		Map<String, Object> params = new HashMap<>();
		params.put("username", "111");
		List<Map<String, Object>> paramList = dynamicSqlSessionTemplate.select("framework.selectMap", params);
		for(Map<String, Object> pojoMap:paramList){
			System.out.println("testSelectMap:"+pojoMap);
		}
	}
	
	/**
	 * <b>功能描述：</b>bean查询单条记录<br>
	 * 20140826&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;创建方法</li>
	 */
	@Test
	public void testSelectBean(){
		User user = new User();
		user.setUsername("abc");
		List<User> UserList = dynamicSqlSessionTemplate.select("framework.selectBean", user);
		for(User u:UserList){
			System.out.println("testSelectBean:"+u.getUsername());
		}
	}
	
	@Test
	public void testSelectOne(){
		User bbiTest = dynamicSqlSessionTemplate.load(User.class, "174");
		System.out.println(bbiTest);
	}
}

package com.fw.web.core.acl.model;

import java.io.Serializable;
import java.util.Date;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;

import com.mqy.mybatis.annotation.Generate;
import com.mqy.mybatis.annotation.NotRepeat;
import com.fw.mybatis.constant.GenerateType;

/**
 * 基于EasyMybatis的javaBean支持注解
 */
@Table(name="acl_user")
public class User implements Serializable{

	private static final long serialVersionUID = 5044173991416581564L;

	@Id 
	@Generate(type=GenerateType.AUTO,lenght=2) //主键生成策略,括号中内容可不写
	@Column(name="id")//可选
	private String id;

	@NotRepeat //验证重复
	private String username;
	
    private Date createTime;
    
	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public Date getCreateTime() {
		return createTime;
	}

	public void setCreateTime(Date createTime) {
		this.createTime = createTime;
	}
}

package com.fw.mybatis;

import java.util.Map;

import com.mqy.mybatis.annotation.AnnotationProcessor;
import com.mqy.mybatis.annotation.bean.GenerateObject;
import com.mqy.mybatis.annotation.bean.NotRepeatObject;
import com.fw.mybatis.constant.GenerateType;


/**
 * 功能：主键列生成策略、列重复判断实现类
 */
public class AnnotationProcessorTest implements AnnotationProcessor {
	
	@Override
	public Object generate(GenerateObject generate) {
		Object obj = null;
		if(GenerateType.AUTO.equals(generate.getType())) {
			
		} else if (GenerateType.CHECK.equals(generate.getType())) {
			
		} else if (GenerateType.CHECK_PARAMS.equals(generate.getType())) { 
			
		} else if (GenerateType.PREFIX.equals(generate.getType())) {
			
		} 
		return obj;
	}

	/**
     * <b>功能描述：</b>校验名称是否重复<br>
     * @param {String} tableName 表名称
	 * @param {String} ckeckColumeName 校验列
	 * @param {String} ckeckColumeValue 校验列值
	 * @param {String} queryColumeName 筛选列
	 * @param {Object} queryColumeValue 筛选列值
     * @return 响应结果
     */
	@Override
	public RuntimeException notRepeat(NotRepeatObject notRepeat) {
		Map<String, Object> params = new HashMap<>();
		String value = notRepeat.getColumnValue().toString();
		params.put("tableName", notRepeat.getTableName());
		params.put("ckeckColumeName", notRepeat.getColumnName());
		params.put("ckeckColumeValue", value);
		if("".equals(value)) {//为空时不做重复效验
			return null;
		}
		List<Map<String, Object>> resultList = commonMapper.checkRepeatName(params);
		if(resultList!=null&&(!resultList.isEmpty())){
			Map<String, Object> resultMap = resultList.get(0);
			Object delflg = resultMap.get("delflg");
			if(notRepeat.getId()==null) {//新建
				if(delflg!=null) {
					if(Archive.DELFLG_RECYCLE==Integer.parseInt(delflg.toString())){//回收站判断重复
						return new ModelValidateException(String.format("代码与回收站中档案[%s]重复", value));
					} 
				}
				return new ModelValidateException(String.format("代码与现有档案[%s]重复", value));
			} else {//修改
				Object id = resultMap.get(notRepeat.getKey());
				if(id!=null&&(!id.toString().equals(notRepeat.getId().toString()))) {
					if(delflg!=null) {
						if(Archive.DELFLG_RECYCLE==Integer.parseInt(delflg.toString())){//回收站判断重复
							return new ModelValidateException(String.format("代码与回收站中档案[%s]重复", value));
						} 
					}
					return new ModelValidateException(String.format("代码与现有档案[%s]重复", value));
				}
			}
		}
		return null;
	}

}