MysqlUtil 5.3.1.3

package com.jfai.util;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.sql.DataSource;
/**
apache.commons.lang 工具包,可以在maven中添加如下依赖获取
<dependency>
<groupId>commons-lang</groupId>
<artifactId>commons-lang</artifactId>
<version>2.6</version>
</dependency>

 google的Gson工具包,可以在maven中添加如下依赖获取
 <!-- https://mvnrepository.com/artifact/com.google.code.gson/gson -->
 <dependency>
 <groupId>com.google.code.gson</groupId>
 <artifactId>gson</artifactId>
 <version>2.8.5</version>
 </dependency>
  druid连接池包,可以在maven中添加如下依赖获取
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.10</version>
</dependency>
 */
import org.apache.commons.lang.ArrayUtils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.sql.SQLUtils;
import com.google.gson.JsonObject;
import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;

import lombok.extern.slf4j.Slf4j;

/**
 * @author wanglf
 * @version 5.3.1.3
 * @Package com.jfai.kg.dao.impl
 * @Description: MysqlUtil
 * @date 2018年7月9日 上午10:52:04
 * @Attention
		 * 为避免某些神奇的使用各种null做表名和参数的使用情况, 所以为空校验全部调为Blank级别
		 * 关于参数非法的时候返回空对象还是null的问题,现在的策略是如果返回的对象是集合,则返回空的集合,其他则返回null(注:这样主要是为了调用的时候拿到集合返回对象,直接就可以遍历,不用判断null)
		 * 如果没有规定明确要传schemaName参数,tableName参数可以是 库名.表名 的格式,从而对其他的库进行操作
		 * mysql的bigint类型本身就包含了8个字节的长度，指定的长度只是指定显示宽度，不影响存储。所以无论是bigint(10),还是bigint(20)均占用8个字节
 * @remark
		 * 修复表名``画蛇添爪爪 问题 2018年7月19日21:09:44
		 * 增强并调整log显示功能  2018年7月22日22:14:12
		 * 新增private方法,解除对StringUtil类方法的依赖 2018年7月26日16:00:40
		 * 增强并调整log显示功能 2018年7月31日11:33:12
		 * 新增getBuildSQL方法 2018年7月31日12:12:21
		 * 新增execute方法 2018年7月31日12:28:02
		 * 新增getSchemas 获取数据库名的集合 功能 2018年7月31日14:05:11
		 * 新增List 插入 内包含批处理  2018年7月31日16:30:39
		 * 新增id相关的delete方法并更新注释 2018年7月31日17:51:52
		 * 新增批量添加列功能列名修改功能和原新增列功能增强 2018年7月31日18:59:23
		 * 插入jsonOnbject的时候,新增忽略id功能(主要用来插入数据时目标表,有自己的主键生成策略的情况) 2018年8月3日19:32:39
		 * 新增依据id和idList查询数据功能 2018年8月5日13:40:43
		 * 新增入口方法欢迎 2018年8月5日14:37:47
		 * 调整getTableInfo方法,新增列信息获取方法  2018年8月7日09:55:25
		 * 新增isPrimaryKey和调整并增强getColumnInfo方法 2018年8月8日19:50:00
		 * 集成JDBCUtil的功能,新增关闭连接和资源的方法 2018年8月8日20:15:52
		 * 集成DataSourceSingleton,新增获取数据源和获取连接功能 2018年8月8日21:12:18
		 * 新增setParam方法,可以将有占位符的SQL还原为可执行的SQL 2018年8月11日19:23:25
 */
@Slf4j
public class MysqlUtil {
	private static DruidDataSource dataSource = new DruidDataSource();

	/**
	 * 私有化构造方法,工具类禁止new对象
	 */
	private MysqlUtil() {
	}

	/**
	 * 调用该方法可以返回一个mysql的数据源
	 * 目前使用的是阿里的Druid连接池
	 *
	 * @return
	 * @version 1.0.0
	 * @remark 也可以作为单例模式的另一种实现, 因为只New了一个DataSource
	 */
	public static DataSource getDataSource() {
		return dataSource;
	}

	/**
	 * 获取数据库连接
	 */
	public static Connection getConnection() {
		try {
			return dataSource.getConnection();
		} catch (SQLException e) {
			log.error("Exception :", e);
		}
		return null;
	}

	/**
	 * 初始化mysql数据库,优化mysql数据库设置
	 *
	 * @version 1.0.0
	 * @Description 设置mysql的最大并发连接数为5000
	 * 设置mysql的缓冲中间表的size=256M
	 * 设置mysql缓冲区大小=32M
	 * 设置mysql允许接收的的最大数据包尺寸为16M
	 * @remark 可能涉及了一点mysql的数据库调优
	 */
	public static void init() {
		log.info("{}.{} Welcome to your arrival!", Thread.currentThread().getStackTrace()[1].getClassName(), Thread.currentThread().getStackTrace()[1].getMethodName());
		long t1 = System.currentTimeMillis();
		Connection conn = null;
		Statement stm = null;
		ResultSet rs = null;

		// 设置mysql的最大并发连接数为5000,并发连接数设小了,坑.. 虽然一般用不到5000
		String sql = "set GLOBAL max_connections =5000";

		// 设置mysql的缓冲中间表的size,不然涉及列操作的时候,可能会出现二次读写,特别是百万数据大表,那速度 ...
		String sql_1 = "set GLOBAL tmp_table_size =256*1024*1024";// -- 256M
		// 设置mysql缓冲区大小
		String sql_2 = "set GLOBAL read_buffer_size =32*1024*1024";// -- 32M
		// 设置mysql允许的最大的一次可允许接收数据量,不然大数据量批处理操作,你懂得 ..
		String sql_3 = "set GLOBAL max_allowed_packet = 16*1024*1024";// -- 16M

		try {
			conn = dataSource.getConnection();
			conn.setAutoCommit(false);

			stm = conn.createStatement();
			stm.addBatch(sql);
			stm.addBatch(sql_1);
			stm.addBatch(sql_2);
			stm.addBatch(sql_3);

			stm.executeBatch();
			conn.commit();// 执行完后,手动提交事务,不然会回滚
			conn.setAutoCommit(true);// 再把自动提交打开
			long t2 = System.currentTimeMillis();
			log.info("MysqlUtil.init>>执行SQL完毕!, 耗时: {}ms", sql.toUpperCase(), t2 - t1);
		} catch (Exception e) {
			try {
				conn.rollback();
				log.error("MysqlUtil.init log error!", e);
			} catch (SQLException e1) {
				log.error("Exception :", e1);
			}
		} finally {
			close(rs, stm, conn);
		}
	}

	/**
	 * 关闭相关资源,并返回连接
	 *
	 * @param rs         java.sql.ResultSet 数据库结果集
	 * @param s          java.sql.Statement SQL执行体
	 * @param connection java.sql.Connection  数据库连接
	 * @version 1.0.0
	 */
	public static void close(ResultSet rs, Statement s, Connection connection) {
		clostResultSet(rs);
		clostStatement(s);
		clostConnection(connection);
	}

	/**
	 * 关闭ResultSet
	 *
	 * @param rs java.sql.ResultSet
	 * @version 1.0.0
	 * @Attention
	 */
	public static void clostResultSet(ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (Exception e) {
				log.error("Exception :", e);
			}
		}
	}

	/**
	 * 关闭Statement 注:PreparedStatement也可以用该方法关闭
	 *
	 * @param s java.sql.Statement
	 * @version 1.0.0
	 * @remark PreparedStatement是Statement的子类, 所以也可以使用该方法关闭
	 */
	public static void clostStatement(Statement s) {
		if (s != null) {
			try {
				s.close();
			} catch (Exception e) {
				log.error("Exception :", e);
			}
		}
	}

	/**
	 * 返回连接到连接池,如果未使用连接池,则会直接关闭连接
	 *
	 * @param conn
	 * @version 1.0.0
	 * @Attention 如果未使用连接池, 则会直接关闭连接
	 * 如果使用连接池,会将连接返回连接池
	 */
	public static void clostConnection(Connection conn) {
		if (conn != null) {
			try {
				conn.close();
			} catch (Exception e) {
				log.error("Exception :", e);
			}
		}
	}

	/**
	 * 向指定的表插入数据,数据类型为map 的key-value形式
	 *
	 * @param dataMap   要插入的数据集
	 * @param tableName 表名
	 * @return true or false
	 */
	public static boolean add(Map<String, Object> dataMap, String tableName) {
		return insert(dataMap, tableName);
	}

	/**
	 * 向指定的表插入数据,数据类型为map 的key-value形式
	 *
	 * @param dataMap   要插入的数据集
	 * @param tableName 表名
	 * @return true or false
	 */
	public static boolean insert(Map<String, Object> dataMap, String tableName) {
		log.info("{}.{} Welcome to your arrival!", Thread.currentThread().getStackTrace()[1].getClassName(), Thread.currentThread().getStackTrace()[1].getMethodName());
		if (tableName == null || tableName.length() == 0) {
			log.error("MysqlUtil.insert Parameter[tableName] is empty ! Return!");
			return false;
		}
		if (dataMap == null || dataMap.size() == 0) {
			log.error("MysqlUtil.insert Parameter[dataMap] is empty ! Return!");
			return false;
		}

		long t1 = System.currentTimeMillis();

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		StringBuilder sql = new StringBuilder();
		StringBuilder p = new StringBuilder();
//		Object[] args = new Object[dataMap.size()];
		sql.append("insert into  " + tableName + "( ");
//		int i = 0;
//		for (String aName : dataMap.keySet()) {
//			if (i != dataMap.size() - 1) {
//				sql.append(aName + ",");
//				p.append("?,");
//			} else {
//				sql.append(aName);
//				p.append("?");
//			}
//			args[i] = dataMap.get(aName);
//			i++;
//		}
//		sql.append(") values(").append(p).append(")");
		
		List<Object> params =new LinkedList<Object>();
		for (String aName : dataMap.keySet()) {
			sql.append(aName + ",");
			p.append("?,");
			params.add(dataMap.get(aName));
		}
		sql.deleteCharAt(sql.length()-1).append(") values(").append(p.deleteCharAt(p.length()-1)).append(")");
		
		int re = 0;
		try {
			conn = dataSource.getConnection();
			
			sql=new StringBuilder((setParam(params, sql.toString())));
			
			pstm = conn.prepareStatement(sql.toString());
//			for (int k = 0; k < args.length; k++) {
//				pstm.setObject(k + 1, args[k]);
//			}

			re = pstm.executeUpdate();
			long t2 = System.currentTimeMillis();
			log.info("MysqlUtil.insert>>执行SQL: {}, 耗时: {}ms", sql.toString(), t2 - t1);
			//log.info("MysqlUtil.insert>>传入的参数值arg=>{}", Arrays.toString(args));
		} catch (Exception e) {
			log.error("MysqlUtil.insert>>执行SQL: {}", sql);
			//log.error("MysqlUtil.insert>>传入的参数值arg=>{}", Arrays.toString(args));
			log.error("MysqlUtil.insert log error!", e);
		} finally {
			close(rs, pstm, conn);
		}
		return re == 0 ? false : true;
	}

	/**
	 * @param dataMap   要更改的值map
	 * @param condition where条件值  key-value形式,用map包装
	 * @param tableName 表名  可以为库名.表名形式
	 * @return true or false
	 */
	public static boolean update(Map<String, Object> dataMap, Map<String, Object> condition, String tableName) {
		log.info("{}.{} Welcome to your arrival!", Thread.currentThread().getStackTrace()[1].getClassName(), Thread.currentThread().getStackTrace()[1].getMethodName());
		if (tableName == null || tableName.length() == 0) {
			log.error("MysqlUtil.update Parameter[tableName] is empty ! Return!");
			return false;
		}
		if (dataMap == null || dataMap.size() == 0) {
			log.error("MysqlUtil.update Parameter[dataMap] is empty ! Return!");
			return false;
		}

		if (condition == null || condition.size() == 0) {
			log.error("MysqlUtil.update Parameter[condition] is empty ! Return!");
			return false;
		}

		long t1 = System.currentTimeMillis();

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		StringBuilder sql = new StringBuilder();
		Object[] arg1 = new Object[dataMap.size()];
		Object[] arg2 = new Object[condition.size()];

		sql.append("update " + tableName + " set ");
		int i = 0;
		for (String aName : dataMap.keySet()) {
			if (i != dataMap.size() - 1) {
				sql.append("`" + aName + "` = ? ,");
			} else {
				sql.append("`" + aName + "` = ?  where ");
			}
			arg1[i] = dataMap.get(aName);
			i++;
		}

		int j = 0;
		for (String cName : condition.keySet()) {
			if (j != condition.size() - 1) {
				sql.append("`" + cName + "` = ? ,");
			} else {
				sql.append("`" + cName + "` = ?");
			}
			arg2[j] = condition.get(cName);
			j++;
		}

		Object[] args = ArrayUtils.addAll(arg1, arg2);

		int re = 0;
		try {
			conn = dataSource.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			for (int k = 0; k < args.length; k++) {
				pstm.setObject(k + 1, args[k]);
			}
			re = pstm.executeUpdate();
			long t2 = System.currentTimeMillis();
			log.info("MysqlUtil.update>>执行SQL: {}, 耗时: {}ms", sql.toString(), t2 - t1);
			log.info("MysqlUtil.update>>传入的参数值arg=>{}", Arrays.toString(args));
			if (re != 0) {
				log.info("MysqlUtil.update successfully !");
				return true;
			}
		} catch (Exception e) {
			log.error("MysqlUtil.update>>执行SQL: {}", sql);
			log.error("MysqlUtil.update>>传入的参数值arg=>{}", Arrays.toString(args));
			log.error("MysqlUtil.update log error!", e);
		} finally {
			close(rs, pstm, conn);
		}
		return re == 0 ? false : true;
	}

	/**
	 * 依据condition删除数据
	 *
	 * @param condition Map<String, Object>形式,可以包含多条key value
	 * @param tableName
	 * @return true or false
	 */
	public static boolean delete(Map<String, Object> condition, String tableName) {
		log.info("{}.{} Welcome to your arrival!", Thread.currentThread().getStackTrace()[1].getClassName(), Thread.currentThread().getStackTrace()[1].getMethodName());
		if (tableName == null || tableName.length() == 0) {
			log.error("MysqlUtil.delete Parameter[tableName] is empty ! Return!");
			return false;
		}
		if (condition == null || condition.size() == 0) {
			log.error("MysqlUtil.delete Parameter[condition] is empty !  Return !");
			return false;
		}
		long t1 = System.currentTimeMillis();

		Connection conn = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		StringBuilder sql = new StringBuilder();
		Object[] args = new Object[condition.size()];
		sql.append("delete from " + tableName + " where ");
		int i = 0;
		for (String cName : condition.keySet()) {
			if (i != condition.size() - 1) {
				sql.append("`" + cName + "` = ? ,");
			} else {
				sql.append("`" + cName + "` = ?");
			}
			args[i] = condition.get(cName);
			i++;
		}

		int re = 0;
		try {
			conn = dataSource.getConnection();
			pstm = conn.prepareStatement(sql.toString());
			for (int k = 0; k < args.length; k++) {
				pstm.setObject(k + 1, args[k]);
			}
			re = p