MySQL基于JdbcTemplate的单表操作工具类

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.UUID;

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

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 * 基于JdbcTemplate的单表操作工具类(MySql)
 * <p>知识点一：</p>
 * <pre>
 * newInstance(): 弱类型。低效率。只能调用无参构造。
 * 使用newInstance有两个前提：
 * 1.这个类已经加载；
 * 2.这个类已经连接了。
 * 同时，newInstance()是实现IOC、反射、依赖倒置 等技术方法的必然选择，new只能实现具体类的实例化，
 * 不适合于接口编程。类里面就是通过这个类的默认构造函数构建了一个对象，如果没有默认构造函数就抛
 * 出InstantiationException, 如果没有访问默认构造函数的权限就抛出IllegalAccessException
 * new: 强类型。相对高效。能调用任何public构造。
 * </pre>
 * <p>知识点二：</p>
 * <pre>
 * ArrayList是Java中比较常用的一个类，它是基于数组实现，非线程安全，可快速随机访问List中的元素。
 * ArrayList具有动态扩容的机制，每次在添加元素时，都会判断容量是否够用，如果不够用，则需要扩容。
 * JDK1.8中，ArrayList的初始容量为0，第一次添加元素时，会将容量设置为10，如果容量不够，则每次会扩大50%
 * </pre>
 * 
 * @author zql
 * @createTime 2020-12-12 12:31:10
 * @version 1.1
 * @modifyLog 1.1 优化代码
 *
 * @param <T>
 */
public class MySQLEntityUtil<T> {

	public static class Operator {
		
		final public static String __thePreFixForLike = UUID.randomUUID().toString().substring(0, 2)
				+ UUID.randomUUID().toString().substring(10, 12);
		
		/**
		 * @param keyWord
		 * @return
		 */
		public static String like (String keyWord) {
			return __thePreFixForLike + keyWord;
		}
	}
	/**
	 * 实体成员数据类型(key)与数据库数据类型(value)键值对
	 */
	private static Map<String, String> t = null;
	static {
		t=new HashMap<String,String>();
		t.put("java.lang.Long", "BIGINT");
		t.put("java.lang.String", "VARCHAR");
		t.put("java.lang.Integer", "INTEGER");
		t.put("java.util.Date", "DATE");
		t.put("java.lang.Double", "DOUBLE");
		t.put("java.lang.Boolean", "BIT");
		t.put("java.lang.Byte", "TINYINT");
		t.put("java.lang.Short", "SMALLINT");
		t.put("java.lang.Float", "FLOAT");
		// DECIMAL(P，D)表示列可以存储D位小数的P位数。十进制列的实际范围取决于精度和刻度。
		t.put("java.math.BigDecimal", "DECIMAL(18,8) DEFAULT(0)");
	}
	/**
	 * Logger for this class
	 */
	private static final Logger logger = LoggerFactory.getLogger(MySQLEntityUtil.class);
	/**
	 * 声明一个泛型类
	 */
	private Class<T> entityClass = null;
	/**
	 * 表名
	 */
	private String tableName = null;
	/**
	 * 主键对应的实体的成员变量名称
	 */
	private String primaryKeyFieldName = null;
	/**
	 * 数据库主键列名
	 */
	private String primaryKeyColumnName = null;
	/**
	 * 实体成员变量名称(key)与数据库列名(value) 键值对
	 */
	private Map<String, String> fieldColumnMap = null;
	/**
	 * 实体成员变量名称数组
	 */
	private String[] fieldNames = null;
	/**
	 * 数据库列名数组
	 */
	private String[] columnNames = null;
	/**
	 * 经过处理的数据库列名，可直接用于拼接sql语句(例如 MySql 关键字desc -> `desc` )
	 */
	private String[] columnNamesForSQL = null;
	/**
	 * insert的sql语句
	 */
	private String sqlForAdd = null;
	/**
	 * 实体成员变量名称(key)与数据类型(value)键值对
	 */
	private Map<String, Class<?>> fieldType = new HashMap<String, Class<?>>();

	/**
	 * @param entityClass 添加了JPA注解的类
	 */
	public MySQLEntityUtil(Class<T> entityClass) {
		this.entityClass = entityClass;
		this.tableName = this.entityClass.getAnnotation(Table.class).name();
		this.parseFieldAndColumn();
	}
	
	/**
	 * 	解析字段和列名，并存入数组，同时拼接数据库插入语句
	 */
	private void parseFieldAndColumn() {
		this.fieldColumnMap = new LinkedHashMap<String, String>();

		this.getColumnFromSelfAndParent(this.entityClass, fieldColumnMap);

		List<String> fieldList = new ArrayList<String>();
		List<String> columnList = new ArrayList<String>();

		StringBuilder sqlForAdd = new StringBuilder("INSERT INTO `");

		sqlForAdd.append(this.tableName).append("`(");
		StringBuilder chars = new StringBuilder();

		int c = this.fieldColumnMap.size();
		this.fieldNames = new String[c];
		this.columnNames = new String[c];
		this.columnNamesForSQL = new String[c];
		// 索引计数器
		int counter = 0;
		boolean isFirst = true;
		for (Map.Entry<String, String> e : this.fieldColumnMap.entrySet()) {
			this.fieldNames[counter] = e.getKey();
			this.columnNames[counter] = e.getValue();
			this.columnNamesForSQL[counter] = "`" + e.getValue() + "`";
			fieldList.add(this.fieldNames[counter]);
			columnList.add(this.columnNames[counter]);
			if (!this.columnNames[counter].equals(this.primaryKeyColumnName)) {
				if (isFirst) {
					isFirst = false;
					sqlForAdd.append("`" + this.columnNames[counter] + "`");
					chars.append("?");
				} else {
					sqlForAdd.append(",").append("`" + this.columnNames[counter] + "`");
					chars.append(",?");
				}
			}
			++counter;
		}
		sqlForAdd.append(")VALUES(").append(chars).append(") ");
		this.sqlForAdd = sqlForAdd.toString();
	}

	/**
	 * 获取列
	 * 
	 * @param clz
	 * @param fieldColumnMap
	 */
	private void getColumn(Class<?> clz, Map<String, String> fieldColumnMap) {
		Field[] fields = clz.getDeclaredFields();
		for (int i = 0; i < fields.length; i++) {
			Field field = fields[i];
			Column ann = field.getAnnotation(Column.class);
			if (Objects.nonNull(ann)) {
				String fieldName = field.getName();
				String columnName = ann.name();
				fieldColumnMap.put(fieldName, columnName);
				this.fieldType.put(fieldName, field.getType());
				Id idAnn = field.getAnnotation(Id.class);
				if (Objects.nonNull(idAnn)) {
					this.primaryKeyFieldName = field.getName();
					this.primaryKeyColumnName = ann.name();
				}
			}
		}
	}

	/**
	 * 获取继承的父类
	 * 
	 * @param clz
	 * @param fieldColumnMap
	 */
	private void getColumnFromSelfAndParent(Class<?> clz, Map<String, String> fieldColumnMap) {
		this.getColumn(clz, fieldColumnMap);
		if (clz.getSuperclass() != Object.class) {
			this.getColumnFromSelfAndParent(clz.getSuperclass(), fieldColumnMap);
		}
	}

	/**
	 * 执行Entity getXxx方法
	 * 
	 * @param fieldName 成员变量名称
	 * @param obj 方法执行目标对象
	 * @return
	 * @throws Exception
	 */
	public Object invokeGet(String fieldName, Object obj) throws Exception {
		String methodName = "get".concat(MySQLEntityUtil.upperFirstChar(fieldName));
		Method method = this.entityClass.getMethod(methodName);
		return method.invoke(obj);
	}

	/**
	 * 执行Entity setXxxx方法
	 * 
	 * @param fieldName 成员变量名称
	 * @param obj 方法执行目标对象
	 * @param value set设置值
	 * @throws Exception
	 */
	public void invokeSet(String fieldName, Object obj, Object value) throws Exception {
		String methodName = "set".concat(MySQLEntityUtil.upperFirstChar(fieldName));
		Method method = this.entityClass.getMethod(methodName, this.fieldType.get(fieldName));
		method.invoke(obj, new Object[] { value });
	}

	/**
	 * 首字母小写转大写
	 * 
	 * @param str
	 * @return
	 */
	public static String upperFirstChar(String str) {
		byte[] items = str.getBytes();
		if (items[0] >= 'a' && items[0] <= 'z') {
			items[0] = (byte) ((char) items[0] - 'a' + 'A');
		}
		return new String(items);
	}

	/**
	 * 向数据库插入一条数据
	 * 
	 * @param entity 添加了JPA注解的实体
	 * @param jdbcTemplate JdbcTemplate实例
	 * @return 返回主键
	 */
	public long insert(T entity, JdbcTemplate jdbcTemplate) {
		List<Object> params = new ArrayList<Object>();
		try {
			for (int i = 0; i < this.columnNames.length; i++) {
				if (this.columnNames[i].equals(this.primaryKeyColumnName)) {
					continue;
				}
				Object value = this.invokeGet(this.fieldNames[i], entity);
				params.add(value);
			}
		} catch (Exception e) {
			logger.error("插入数据失败", e);
			return -1L;
		}
		if (logger.isDebugEnabled()) {
			String sepa = System.getProperty("line.separator");
			StringBuilder paramsValue = new StringBuilder();
			Object[] p = params.toArray();
			for (int i = 0; i < p.length; i++) {
				paramsValue.append(i).append(":").append(p[i]).append(sepa);
			}
			logger.debug("{}MySQLEntityUtil.class long_add({},JdbcTemplate){}-SQL:"
					+ "{}\\n-params:{} {} >------------", sepa, this.getClass(), sepa, this.sqlForAdd, sepa, paramsValue);
		}
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
				PreparedStatement ps = con.prepareStatement(sqlForAdd, PreparedStatement.RETURN_GENERATED_KEYS);
				for (int i = 1; i <= params.size(); i++) {
					ps.setObject(i, params.get(i-1));
				}
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().longValue();
	}

	/**
	 * 根据主键id查询数据
	 * 
	 * @param id 主键
	 * @param jdbcTemplate JdbcTemplate实例
	 * @return
	 */
	public T getById(long id, JdbcTemplate jdbcTemplate) {
		// 声明实体类
		T entity = null;
		String sql = "SELECT * FROM `" + this.tableName + "` WHERE `" + this.primaryKeyColumnName + "`=" + id;
		entity = jdbcTemplate.query(sql, new ResultSetExtractor<T>() {
			@Override
			public T extractData(ResultSet rs) throws SQLException, DataAccessException {
				T entity = null;
				try {
					// 实例化泛型类
					entity = entityClass.newInstance();
					for (int i = 0; i < fieldNames.length; i++) {
						Object value = rs.getObject(columnNames[i]);
						// 设置实体类的变量值
						invokeSet(fieldNames[i], entity, value);
					}
				} catch (InstantiationException e) {
					// 没有默认构造函数
					logger.error("The error message:There is no default constructor !", e);
				} catch (IllegalAccessException e) {
					// 不能访问默认构造函数
					logger.error("The error message:No access to the default constructor !", e);
				} catch (Exception e) {
					logger.error("根据主键【{}】查询数据失败", id, e);
				}
				return entity;
			}
		});
		return entity;
	}

	/**
	 * 根据实体类设置的条件查询数据，所有条件以AND连接
	 * 
	 * @param conditions 条件（null字段自动被忽略）
	 * @param jdbcTemplate JdbcTemplate实例
	 * @param pageSize 要查询的数据条数
	 * @param pageNum 要查询的数据起始页
	 * @param orderBy orderBy可选条件（默认按主键降序排列）
	 * @return
	 */
	public List<T> listObjects(T conditions, JdbcTemplate jdbcTemplate, final int pageSize, int pageNum, String orderBy) {
		// where 1=1 是为了避免where 关键字后面的第一个词直接就是 “and”而导致语法错误。
		StringBuilder sql = new StringBuilder("SELECT * FROM `").append(this.tableName).append("` WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		try {
			// 遍历列表
			for (int i = 0; i < this.columnNames.length; i++) {
				// 获取实体类的变量值
				Object value = this.invokeGet(this.fieldNames[i], conditions);
				if (Objects.nonNull(value)) {
					String op = "=";
					// 如果是字符串，并且有like关键字标识，则将操作符设置为like
					if (value instanceof String && ((String) value).startsWith(Operator.__thePreFixForLike)) {
						op = " like ";
						value = ((String) value).substring(Operator.__thePreFixForLike.length());
					}
					// 拼接条件
					sql.append(" AND `").append(this.columnNames[i]).append("`").append(op).append("?");
					params.add(value);
				}
			}
		} catch (Exception e) {
			logger.error("组装sql语句失败", e);
			return null;
		}

		if (Objects.nonNull(orderBy)) {
			// 自定义order by
			sql.append(" ORDER BY ").append(orderBy);
		} else if (Objects.nonNull(this.primaryKeyColumnName)) {
			// 默认按主键降序排列
			sql.append(" ORDER BY ").append(this.primaryKeyColumnName).append(" DESC ");
		}

		sql.append(" LIMIT ? OFFSET ?");
		params.add(pageSize);
		params.add(pageSize * (pageNum - 1));

		List<T> list = jdbcTemplate.query(sql.toString(), params.toArray(new Object[params.size()]),
				new ResultSetExtractor<List<T>>() {
					public List<T> extractData(ResultSet rs) throws SQLException, DataAccessException {
						int initNum = 0;
						if (pageSize > 100) {
							initNum = 100;
						} else if(pageSize > 1000){
							initNum = 1000;
						}
						/*
						 * ArrayList的初始容量为0，第一次添加元素时，会将容量设置为10，如果容量不够，则每次会扩大50%，
						 * 而ArrayList每次扩容都需要做一次数组拷贝，如果是反复扩容，肯定会对程序的运行效率产生影响。
						 * _所以在初始化ArrayList的时候，尽量设置初始化容量，避免其过多扩容影响效率。
						 */
						List<T> list = new ArrayList<T>(initNum);
						try {
							while (rs.next()) {
								T entity = entityClass.newInstance();
								for (int i = 0; i < fieldNames.length; i++) {
									String s = columnNames[i];
									Object value = rs.getObject(s);
									String f = fieldNames[i];
									// 设置实体类的变量值
									invokeSet(f, entity, value);
								}
								list.add(entity);
							}
						} catch (Exception e) {
							String cond = Objects.nonNull(conditions) ? conditions.toString() : null;
							logger.error("根据实体类设置的条件查询数据失败，条件实体类信息：{}", cond, e);
						}
						return list;
					}
				});
		if (Objects.isNull(list)) {
			list = new ArrayList<T>(0);
		}
		return list;
	}

	/**
	 * 根据实体类设置的条件查询数据总数，所有条件以AND连接
	 * 
	 * @param conditions 条件（null字段自动被忽略）
	 * @param jdbcTemplate JdbcTemplate实例
	 * @return
	 */
	public int count(T conditions, JdbcTemplate jdbcTemplate) {
		StringBuilder sql = new StringBuilder("SELECT COUNT(1) FROM `").append(this.tableName).append("` WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		try {
			for (int i = 0; i < this.columnNames.length; i++) {
				// 获取实体类的变量值
				Object value = this.invokeGet(this.fieldNames[i], conditions);
				if (Objects.nonNull(value)) {
					String op = "=";
					// 如果是字符串，并且有like关键字标识，则将操作符设置为like
					if (value instanceof String && ((String) value).startsWith(Operator.__thePreFixForLike)) {
						op = " like ";
						value = ((String) value).substring(Operator.__thePreFixForLike.length());
					}
					sql.append(" AND `").append(this.columnNames[i]).append("`").append(op).append("?");
					params.add(value);
				}
			}
		} catch (Exception e) {
			String cond = Objects.nonNull(conditions) ? conditions.toString() : null;
			logger.error("根据实体类设置的条件查询数据总数失败，条件实体类信息：{}", cond, e);
			return -1;
		}
		return jdbcTemplate.queryForObject(sql.toString(), params.toArray(), Integer.class);
	}

	/**
	 * 根据更新值和条件进行更新数据
	 * 
	 * @param values 更新的值（实体调用setXXX方法的字段）
	 * @param conditions 条件（实体调用setXXX方法的字段）
	 * @param jdbcTemplate JdbcTemplate实例
	 * @return
	 */
	public int update(T values, T conditions, JdbcTemplate jdbcTemplate) {
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE `").append(this.tableName).append("` SET `").append(this.primaryKeyColumnName).append("`=`")
			.append(this.primaryKeyColumnName).append("`");
		// 不建议在update的where条件后加上1=1。原因当1=1后无条件时，会更新set的所有数据
		StringBuilder condition = new StringBuilder(" WHERE ");
		List<Object> paramsValue = new ArrayList<Object>(this.columnNames.length * 2);
		List<Object> paramsCondition = new ArrayList<Object>(this.columnNames.length);

		try {
			// 遍历实体成员变量名称(key)与数据库列名(value) 键值对
			for (Map.Entry<String, String> e : this.fieldColumnMap.entrySet()) {
				String fieldName = e.getKey();
				String columnName = e.getValue();
				Object value = this.invokeGet(fieldName, values);
				Object con = this.invokeGet(fieldName, conditions);
				if (Objects.nonNull(value)) {
					paramsValue.add(value);
					sql.append(",`").append(columnName).append("`=?");
				}
				if (Objects.nonNull(con)) {
					String op = "=";
					// 如果是字符串，并且有like关键字标识，则将操作符设置为like
					if (con instanceof String && ((String) con).startsWith(Operator.__thePreFixForLike)) {
						op = " like ";
						con = ((String) con).substring(Operator.__thePreFixForLike.length());
					}
					condition.append(" AND `").append(columnName).append("`").append(op).append("?");
					paramsCondition.add(con);
				}
			}
		} catch (Exception e) {
			String val = Objects.nonNull(values) ? values.toString() : null;
			String cond = Objects.nonNull(conditions) ? conditions.toString() : null;
			logger.error("根据更新值和条件进行更新数据失败，更新值实体类信息：{}，条件实体类信息：{}", val, cond, e);
		}
		paramsValue.addAll(paramsCondition);
		return jdbcTemplate.update(sql.append(condition).toString(), paramsValue.toArray());
	}

	/**
	 * 根据条件删除数据
	 * 
	 * @param conditions 条件（实体调用setXXX方法的字段）
	 * @param jdbcTemplate JdbcTemplate实例
	 * @return
	 */
	public int delete(T conditions, JdbcTemplate jdbcTemplate) {
		StringBuilder sql = new StringBuilder();
		// 不建议在delete的where条件后加上1=1。原因当1=1后无条件时，会删除所有数据
		sql.append("DELETE FROM `").append(this.tableName).append("` WHERE ");
		List<Object> params = new ArrayList<Object>(this.columnNames.length);
		try {
			for (Map.Entry<String, String> e : this.fieldColumnMap.entrySet()) {
				String fieldName = e.getKey();
				String columnName = e.getValue();
				Object con = this.invokeGet(fieldName, conditions);
				if (Objects.nonNull(con)) {
					String op = "=";
					// 如果是字符串，并且有like关键字标识，则将操作符设置为like
					if (con instanceof String && ((String) con).startsWith(Operator.__thePreFixForLike)) {
						op = " like ";
						con = ((String) con).substring(Operator.__thePreFixForLike.length());
					}
					sql.append(" AND `").append(columnName).append("`").append(op).append("?");
					params.add(con);
				}
			}
		} catch (Exception e) {
			String cond = Objects.nonNull(conditions) ? conditions.toString() : null;
			logger.error("根据条件删除数据失败，条件实体类信息：{}", cond, e);
		}
		return jdbcTemplate.update(sql.toString(), params.toArray());
	}

	public Class<?> getEntityClass() {
		return entityClass;
	}

	public Map<String, String> getFieldColumnMap() {
		return fieldColumnMap;
	}

	public String getPrimaryKeyColumnName() {
		return primaryKeyColumnName;
	}

	public String getPrimaryKeyFieldName() {
		return primaryKeyFieldName;
	}

	public String getTableName() {
		return tableName;
	}

	public String[] getColumnNames() {
		return columnNames;
	}

	public String[] getFieldNames() {
		return fieldNames;
	}
	
}