mybatis 增删改动态SQL

在用mybatis操作时，增删改查总是要在mapper.xml中写一堆sql，在添加了字段总是需要修改sql，不然会报错。

于是对mybatis的增删改查进行了封装，将操作封装成一个BaseService，要用使用的Service只需注入BaseService即可。

接口类BaseService.java如下：

public interface BaseService {
	/**
	 * 根据ID获取实体(实体必须有注解)
	 * */
	public <T, E> T findById(E id, Class<T> clz) throws Exception;

	/**
	 * 根据ID删除实体(实体必须有注解)
	 * */
	public <T, E> void deleteById(E id, Class<T> clz) throws Exception;

	/**
	 * 修改实体信息(实体必须有注解)
	 * */
	public <T> void update(T t) throws Exception;

	/**
	 * 添加实体信息(实体必须有注解)
	 * */
	public <T> void add(T t) throws Exception;

	public <T> T findOne(String statement, Object obj) throws Exception;

	public <T> T findOne(String statement) throws Exception;

	public <T> List<T> findList(String statement, Object obj) throws Exception;

	public <T> List<T> findList(String statement) throws Exception;

	public void update(String statement, Object obj) throws Exception;

	public void insert(String statement, Object obj) throws Exception;

	public void delete(String statement, Object obj) throws Exception;
}

BaseServiceImpl类如下：

@Service("baseService")
@SuppressWarnings("unchecked")
public class BaseServiceImpl extends SqlSessionDaoSupport implements BaseService {
	public <T> List<T> findList(String statement, Object obj) throws Exception {
		return this.getSqlSession().selectList(statement, obj);
	}

	public <T> List<T> findList(String statement) throws Exception {
		return this.getSqlSession().selectList(statement);
	}

	public <T> T findOne(String statement, Object obj) throws Exception {
		return (T) this.getSqlSession().selectOne(statement, obj);
	}

	public <T> T findOne(String statement) throws Exception {
		return (T) this.getSqlSession().selectOne(statement);
	}

	public void update(String statement, Object obj) throws Exception {
		this.getSqlSession().update(statement, obj);
	}

	public void insert(String statement, Object obj) throws Exception {
		this.getSqlSession().insert(statement, obj);
	}

	public void delete(String statement, Object obj) throws Exception {
		this.getSqlSession().delete(statement, obj);
	}

	private static final String dataPrefix = "entity";
	public <T> void add(T t) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>(2, 1);
		String addSql = DynamicSqlUtil.addSql(t, dataPrefix);
		if (addSql == null) throw new Exception("SQL错误, 请检查表名、字段名的注解是否正确");
		map.put("addSql", addSql);
		map.put(dataPrefix, t);
		this.getSqlSession().insert("baseMapper.addInfo", map);
	}

	public <T, E> void deleteById(E id, Class<T> clz) throws Exception {
		String dataName = "idValue";
		String deleteSql = DynamicSqlUtil.deleteByIdSql(clz, dataName);
		if (deleteSql == null) throw new Exception("SQL错误, 请检查表名、字段名的注解是否正确");
		Map<String, Object> map = new HashMap<String, Object>(2, 1);
		map.put(dataName, id);
		map.put("deleteSql", deleteSql);
		this.getSqlSession().delete("baseMapper.deleteInfo", map);
	}

	public <T> void update(T t) throws Exception {
		Map<String, Object> map = new HashMap<String, Object>(2, 1);
		String updateSql = DynamicSqlUtil.updateSql(t, dataPrefix);
		if (updateSql == null) throw new Exception("SQL错误, 请检查表名、字段名的注解是否正确");
		map.put("updateSql", updateSql);
		map.put(dataPrefix, t);
		this.getSqlSession().update("baseMapper.updateInfo", map);
	}

	public <T, E> T findById(E id, Class<T> clazz) throws Exception {
		String dataName = "idValue";
		String selectSql = DynamicSqlUtil.selectByIdSql(clazz, dataName);
		if (selectSql == null) throw new Exception("SQL错误, 请检查表名、字段名的注解是否正确");
		Map<String, Object> map = new HashMap<String, Object>(2, 1);
		map.put(dataName, id);
		map.put("selectSql", selectSql);
		Map<String, Object> valueMap = (Map<String, Object>) this.getSqlSession().selectOne("baseMapper.selectInfoById", map);
		return DynamicSqlUtil.getEntity(valueMap, clazz);
	}
}

/**
 * 动态SQL
 * */
public final class DynamicSqlUtil {
	public static <T> String addSql(T t, String dataPrefix) throws Exception {
		List<Field> fieldList = new ArrayList<Field>(10);
		SqlTable sqlTable = t.getClass().getAnnotation(SqlTable.class);
		if (sqlTable == null) return null;
		getAllField(t.getClass(), fieldList);
		if (fieldList == null || fieldList.size() == 0 || fieldList.get(0) == null) return null;
		return builderAddSql(fieldList, sqlTable.value(), dataPrefix, t);
	}

	private static final int manySum = 2;
	private static final String sqlSpace = ", ";
	private static <T> String builderAddSql(List<Field> fieldList, String tableName, String dataPrefix, T t) throws Exception {
		StringBuilder sql_prefix = new StringBuilder(80);
		StringBuilder sql_suffix = new StringBuilder(80);
		String sqlColumnName = null;
		boolean flag = false;
		String jdbcType = "";
		for (Field field : fieldList) {
			try {
				field.setAccessible(true);
				if (field.get(t) == null) continue;
			} catch (Exception e) {
				e.printStackTrace();
			}
			if (field.isAnnotationPresent(SqlColumn.class)) {
				sqlColumnName = field.getAnnotation(SqlColumn.class).value().toUpperCase();
			} else {
				continue;
			}
			sql_prefix.append(sqlSpace).append(sqlColumnName);
			jdbcType = getJdbcType(field.getType().getSimpleName().toUpperCase());
			sql_suffix.append(", #{").append(dataPrefix).append(".").append(field.getName());
			if (jdbcType != null) {
				sql_suffix.append(sqlSpace).append(jdbcType);
			}
			sql_suffix.append("}");
			if (!flag) flag = true;
		}
		if (!flag) return null;
		String prefix = sql_prefix.substring(manySum), suffix = sql_suffix.substring(manySum);
		StringBuilder sqls = new StringBuilder(prefix.length() + suffix.length() + tableName.length() + 30);
		sqls.append("insert into ").append(tableName).append(" (").append(prefix).append(") values (").append(suffix).append(")");
		return sqls.toString();
	}

	/**
	 * STRING 1
	 * INTEGER 2
	 * FLOAT 3
	 * DOUBLE 4
	 * SHORT 5
	 * LONG 6
	 * DATE 7
	 * */
	private static final String JDBCPREFIX = "JDBCTYPE=";
	private static String getJdbcType(String type) {
		Integer value = clazzMap.get(type);
		if(value == null) return null;
		switch (value) {
			case 1 : return new StringBuffer(JDBCPREFIX).append("VARCHAR").toString();
			case 2 : return new StringBuffer(JDBCPREFIX).append("INTEGER").toString();
			case 3 : return new StringBuffer(JDBCPREFIX).append("REAL").toString();
			case 4 : return new StringBuffer(JDBCPREFIX).append("DOUBLE").toString();
			case 5 : return new StringBuffer(JDBCPREFIX).append("SMALLINT").toString();
			case 6 : return new StringBuffer(JDBCPREFIX).append("BIGINT").toString();
			case 7 : return new StringBuffer(JDBCPREFIX).append("TIMESTAMP").toString();
			default : return null;
		}
	}

	/**
	 * 获取所有属性（含父类属性）
	 * */
	public static <T> void getAllField(Class<T> clazz, List<Field> fieldList) {
		String className = clazz.getSimpleName();
		Boolean baseType = baseTypeMap.get(className);
		if (className.equals(baseClassName) || (baseType != null && baseType)) return;
		Field[] fields = clazz.getDeclaredFields();
		if (fields != null && fields.length > 0 && fields[0] != null) fieldList.addAll(Arrays.asList(fields));
		getAllField(clazz.getSuperclass(), fieldList);
	}

	public final static Map<String, Boolean> baseTypeMap;
	static {
		baseTypeMap = new HashMap<String, Boolean>(9, 1);
		baseTypeMap.put("String", true);
		baseTypeMap.put("Float", true);
		baseTypeMap.put("Double", true);
		baseTypeMap.put("Long", true);
		baseTypeMap.put("Short", true);
		baseTypeMap.put("Character", true);
		baseTypeMap.put("Boolean", true);
		baseTypeMap.put("Byte", true);
		baseTypeMap.put("Date", true);
	}
	public static final String baseClassName = "Object";

	/**
	 * 生成修改SQL
	 * */
	public static <T> String updateSql(T t, String dataPrefix) throws Exception {
		List<Field> fieldList = new ArrayList<Field>(10);
		SqlTable sqlTable = t.getClass().getAnnotation(SqlTable.class);
		if (sqlTable == null) return null;
		getAllField(t.getClass(), fieldList);
		if (fieldList == null || fieldList.size() == 0 || fieldList.get(0) == null) return null;
		return builderUpdateSql(fieldList, sqlTable.value(), dataPrefix, t);
	}

	private static <T> String builderUpdateSql(List<Field> fieldList, String tableName, String dataPrefix, T t) throws Exception {
		StringBuilder setSql = new StringBuilder(200);
		StringBuilder whereSql = new StringBuilder(50);
		SqlColumn column = null;
		for (Field field : fieldList) {
			if (field.isAnnotationPresent(SqlColumn.class)) {
				column = field.getAnnotation(SqlColumn.class);
				if (column.update() == false) continue;
				field.setAccessible(true);
				if (column.pk()) {
					if (field.get(t) == null) return null;
					field.setAccessible(true);
					whereSql.append(" where ").append(column.value().toUpperCase())
								   .append(" = #{").append(dataPrefix).append(".").append(field.getName()).append("}");
				} else {
					setSql.append(sqlSpace);
					if (field.get(t) == null) {
						setSql.append(field.getAnnotation(SqlColumn.class).value()).append(" = null");
					} else {
						setSql.append(field.getAnnotation(SqlColumn.class).value().toUpperCase()).append(" = #{")
								 .append(dataPrefix).append(".").append(field.getName())
								 .append(sqlSpace).append(getJdbcType(field.getType().getSimpleName().toUpperCase()))
								 .append("}");
					}
				}
			}
		}
		if (setSql.length() == 0 || whereSql.length() == 0) return null;
		StringBuilder sqls = new StringBuilder(setSql.length() + whereSql.length() + tableName.length() + 30);
		sqls.append("update ").append(tableName).append(" set ").append(setSql.substring(manySum)).append(whereSql.toString());
		return sqls.toString();
	}

	/**
	 * 生成按ID删除SQL
	 * */
	public static <T> String deleteByIdSql(Class<T> clazz, String dataName) {
		SqlTable sqlTable = clazz.getAnnotation(SqlTable.class);
		if (sqlTable == null) return null;
		String suffixSql = null;
		try {
			suffixSql = getSuffixSql(clazz, sqlTable, dataName);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (suffixSql != null) {
			// SqlTable注解了idFieldName, 无需获取属性列表遍历（效率高，推荐）
			return new StringBuilder("delete").append(suffixSql).toString();
		} else {
			// SqlTable未注解idFieldName, 需获取属性列表遍历（效率低，不推荐）
			List<Field> fieldList = new ArrayList<Field>(10);
			getAllField(clazz, fieldList);
			if (fieldList == null || fieldList.size() == 0 || fieldList.get(0) == null) return null;
			return builderDeleteSql(fieldList, sqlTable.value(), dataName);
		}
	}

	private static String getSuffixSql(Class<?> clazz, SqlTable sqlTable, String dataName) throws Exception {
		Field idField = clazz.getDeclaredField(sqlTable.idFieldName());
		StringBuilder suffixSql = new StringBuilder(100);
		suffixSql.append(" from ").append(sqlTable.value().toUpperCase())
					  .append(" where ").append(idField.getName().toUpperCase()).append(" = #{").append(dataName).append(sqlSpace)
					  .append(getJdbcType(idField.getType().getSimpleName().toUpperCase())).append("}");
		return suffixSql.toString();
	}

	private static String builderDeleteSql(List<Field> fieldList, String tableName, String dataName) {
		StringBuilder sqls = new StringBuilder(100);
		sqls.append("delete from ").append(tableName).append(" where ");
		SqlColumn column = null;
		for (Field field : fieldList) {
			column = field.getAnnotation(SqlColumn.class);
			if (column != null && column.pk()) {
				sqls.append(column.value().toUpperCase()).append(" = #{").append(dataName).append(sqlSpace)
					  .append(getJdbcType(field.getType().getSimpleName().toUpperCase())).append("}");
				return sqls.toString();
			}
		}
		return null;
	}

	public static <T> T getEntity(Map<String, Object> map, Class<T> clz) throws Exception {
		if (map == null) return null;
		Map<String, Object> resultMap = new HashMap<String, Object>(map.size());
		for (Map.Entry<String, Object> m : map.entrySet()) {
			resultMap.put(m.getKey().toUpperCase(), m.getValue());
		}
		T t = clz.newInstance();
		List<Field> fieldList = new ArrayList<Field>(10);
		DynamicSqlUtil.getAllField(clz, fieldList);
		if (fieldList == null || fieldList.size() == 0 || fieldList.get(0) == null) return t;
		Object value = null;
		for (Field f : fieldList) {
			if (f.isAnnotationPresent(SqlColumn.class)) {
				value = resultMap.get(f.getAnnotation(SqlColumn.class).value().toUpperCase());
			} else {
				continue;
			}
			if (value == null) continue;
			setFieldValue(f, t, value);
		}
		return t;
	}

	public static final Map<String, Integer> clazzMap;

	static {
		clazzMap = new HashMap<String, Integer>(7, 1);
		clazzMap.put("STRING", 1);
		clazzMap.put("INTEGER", 2);
		clazzMap.put("FLOAT", 3);
		clazzMap.put("DOUBLE", 4);
		clazzMap.put("SHORT", 5);
		clazzMap.put("LONG", 6);
		clazzMap.put("DATE", 7);
	}

	private static <T> void setFieldValue(Field f, T t, Object value) throws Exception {
		Integer clazzType = clazzMap.get(f.getType().getSimpleName().toUpperCase());
		if (clazzType == null) return;
		f.setAccessible(true);
		switch (clazzType) {
			case 1: f.set(t, value.toString()); return;
			case 2: f.set(t, Integer.valueOf(value.toString())); return;
			case 3: f.set(t, Float.valueOf(value.toString())); return;
			case 4: f.set(t, Double.valueOf(value.toString())); return;
			case 5: f.set(t, Short.valueOf(value.toString())); return;
			case 6: f.set(t, Long.valueOf(value.toString())); return;
			case 7: f.set(t, new java.util.Date(((java.sql.Timestamp) value).getTime())); return;
			default : return;
		}
	}

	/**
	 * 生成按ID查询SQL
	 * */
	public static <T> String selectByIdSql(Class<T> clazz, String dataName) {
		SqlTable sqlTable = clazz.getAnnotation(SqlTable.class);
		if (sqlTable == null) return null;
		String suffixSql = null;
		try {
			suffixSql = getSuffixSql(clazz, sqlTable, dataName);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		if (suffixSql != null) {
			// SqlTable注解了idFieldName, 无需获取属性列表遍历（效率高，推荐）
			return new StringBuilder("select *").append(suffixSql).toString();
		} else {
			// SqlTable未注解idFieldName, 需获取属性列表遍历（效率低，不推荐）
			List<Field> fieldList = new ArrayList<Field>(10);
			getAllField(clazz, fieldList);
			if (fieldList == null || fieldList.size() == 0 || fieldList.get(0) == null) return null;
			return builderSelectSql(fieldList, sqlTable.value(), dataName);
		}
	}

	private static String builderSelectSql(List<Field> fieldList, String tableName, String dataName) {
		StringBuilder sqls = new StringBuilder(100);
		sqls.append("select * from ").append(tableName).append(" where ");
		SqlColumn column = null;
		for (Field field : fieldList) {
			column = field.getAnnotation(SqlColumn.class);
			if (column != null && column.pk()) {
				sqls.append(column.value().toUpperCase()).append(" = #{").append(dataName).append(sqlSpace)
					  .append(getJdbcType(field.getType().getSimpleName().toUpperCase())).append("}");
				return sqls.toString();
			}
		}
		return null;
	}
}

BaseMapper.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="baseMapper">
	<!-- 增加 -->
	<insert id="addInfo" parameterType="Map">
		${addSql}
    </insert>
	<!-- 删除 -->
	<delete id="deleteInfo" parameterType="Map">
		${deleteSql}
    </delete>
	<!-- 更新 -->
	<update id="updateInfo" parameterType="Map">
		${updateSql}
    </update>
    <!-- 查询 -->
    <select id="selectInfoById" parameterType="Map" resultType="Map">
		${selectSql}
    </select>
</mapper>

自定义了两个注解：

SqlTable.java

@Target(ElementType.TYPE)
@Retention(RetentionPolicy.RUNTIME)
public @interface SqlTable {
	/**
	 * 数据库表名
	 * */
	String value();

	/**
	 * 实体中主键属性名, 默认不注解
	 * */
	String idFieldName() default "id";
}

SqlColumn.java

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SqlColumn {
	/**
	 * 数据库字段名
	 * */
	String value();

	/**
	 * 该字段是否是主键, 默认：false
	 * */
	boolean pk() default false;

	/**
	 * update时是否修改：默认true修改
	 * */
	boolean update() default true;
}

entity编写如下：

@SqlTable("T_EM_INVENTORY")
public class Inventory implements Serializable {
	private static final long serialVersionUID = 8990818830033976775L;
	@SqlColumn(value = "ID", pk = true)
	private String id;
	/**
	 * 盘点名称
	 */
	@SqlColumn("INVENTORY_NAME")
	private String inventoryName;

	/**
	 * 创建时间
	 */
	@SqlColumn(value = "CREATE_TIME", update = false)
	private Date createTime;

	/**
	 * 创建人员工号
	 */
	@SqlColumn(value = "CREATE_NO", update = false)
	private String createNo;

	/**
	 * 修改时间
	 */
	@SqlColumn("UPDATE_TIME")
	private Date updateTime;

	/**
	 * 修改人员工号
	 */
	@SqlColumn("UPDATE_NO")
	private String updateNo;

	public String getManagerName() {
		return managerName;
	}

	public void setManagerName(String managerName) {
		this.managerName = managerName;
	}

	public String getDeathDayStr() {
		return deathDayStr;
	}

	public void setDeathDayStr(String deathDayStr) {
		this.deathDayStr = deathDayStr;
	}

	public String getId() {
		return id;
	}

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

	public String getInventoryName() {
		return inventoryName;
	}

	public void setInventoryName(String inventoryName) {
		this.inventoryName = inventoryName;
	}

	public Date getCreateTime() {
		return createTime;
	}

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

	public String getCreateNo() {
		return createNo;
	}

	public void setCreateNo(String createNo) {
		this.createNo = createNo;
	}

	public Date getUpdateTime() {
		return updateTime;
	}

	public void setUpdateTime(Date updateTime) {
		this.updateTime = updateTime;
	}

	public String getUpdateNo() {
		return updateNo;
	}

	public void setUpdateNo(String updateNo) {
		this.updateNo = updateNo;
	}
}

仅需将每个文件copy到项目对应文件夹下即可使用，如有任何问题，请留言，谢谢！