org.sagacity.quickvo.utils.DBUtil Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of quickvo-maven-plugin Show documentation
sqltoy quickvo maven plugin
There is a newer version: 1.0.4
/**
 * @Copyright 2008 版权归陈仁飞，不要肆意侵权抄袭，如引用请注明出处保留作者信息。
 */
package org.sagacity.quickvo.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.logging.Logger;

import org.sagacity.quickvo.utils.callback.PreparedStatementResultHandler;

/**
 * @project sagacity-quickvo
 * @description 数据库连接工具类
 * @author zhongxuchen
 * @version v1.0,Date:2008-11-24
 */
public class DBUtil {
	/**
	 * 定义日志
	 */
	private static Logger logger = LoggerUtil.getLogger();

	/**
	 * 数据库方言定义
	 */
	public static final class Dialect {
		// oracle12c+
		public final static String ORACLE = "oracle";

		// oracle11g
		public final static String ORACLE11 = "oracle11";
		// 10.x
		public final static String DB2 = "db2";

		// sqlserver2012或以上版本
		public final static String SQLSERVER = "sqlserver";

		// mysql的三个变种，5.6版本或以上
		public final static String MYSQL = "mysql";
		public final static String MYSQL57 = "mysql57";
		public final static String INNOSQL = "innosql";
		public final static String MARIADB = "mariadb";

		// 9.5+ 开始
		public final static String POSTGRESQL = "postgresql";
		public final static String POSTGRESQL15 = "postgresql15";
		public final static String GREENPLUM = "greenplum";
		// 神通数据库
		public final static String OSCAR = "oscar";

		// 华为gaussdb(源于postgresql)未验证
		public final static String GAUSSDB = "gaussdb";

		// 3.0以上版本
		public final static String SQLITE = "sqlite";

		// mongodb
		public final static String MONGO = "mongo";

		// elasticsearch
		public final static String ES = "elastic";

		// 19.x版本
		public final static String CLICKHOUSE = "clickhouse";

		// 阿里 oceanbase(未验证)
		public final static String OCEANBASE = "oceanbase";

		// tidb(语法遵循mysql)
		public final static String TIDB = "tidb";

		// 达梦数据库(dm8验证)
		public final static String DM = "dm";

		// 人大金仓数据库
		public final static String KINGBASE = "kingbase";
		public final static String IMPALA = "impala";
		public final static String TDENGINE = "tdengine";

		// h2
		public final static String H2 = "h2";

		// mogdb
		public final static String MOGDB = "mogdb";
		// 海量数据库(opengauss)
		public final static String VASTBASE = "vastbase";
		public final static String OPENGAUSS = "opengauss";
		public final static String STARDB = "stardb";
		public final static String UNDEFINE = "UNDEFINE";
	}

	/*
	 * 数据库类型数字标识
	 */
	public static final class DBType {
		// 未定义未识别
		public final static int UNDEFINE = 0;
		// 12c+
		public final static int ORACLE = 10;
		// 11g
		public final static int ORACLE11 = 11;
		// 10.x版本
		public final static int DB2 = 20;
		// 2017及以上版本
		public final static int SQLSERVER = 30;
		public final static int MYSQL = 40;
		public final static int MYSQL57 = 42;

		// 默认9.5+版本
		public final static int POSTGRESQL = 50;
		public final static int POSTGRESQL15 = 51;

		// clickhouse
		public final static int CLICKHOUSE = 60;

		// gaussdb
		public final static int GAUSSDB = 70;
		// sqlite
		public final static int SQLITE = 80;
		// tidb
		public final static int TIDB = 90;
		// 阿里oceanbase
		public final static int OCEANBASE = 100;
		// 达梦
		public final static int DM = 110;

		// 人大金仓数据库
		public final static int KINGBASE = 120;
		public final static int MONGO = 130;
		public final static int ES = 140;
		public final static int TDENGINE = 150;
		public final static int IMPALA = 160;
		// h2
		public final static int H2 = 170;
		public final static int OSCAR = 180;

		// MOGDB 基于openGauss开发。
		public final static int MOGDB = 190;
		public final static int VASTBASE = 200;
		public final static int OPENGAUSS = 210;
		public final static int STARDB = 220;
	}

	public static HashMap DBNameTypeMap = new HashMap();
	static {
		DBNameTypeMap.put(Dialect.DB2, DBType.DB2);
		DBNameTypeMap.put(Dialect.ORACLE, DBType.ORACLE);
		DBNameTypeMap.put(Dialect.ORACLE11, DBType.ORACLE11);
		DBNameTypeMap.put(Dialect.SQLSERVER, DBType.SQLSERVER);
		DBNameTypeMap.put(Dialect.MYSQL, DBType.MYSQL);
		DBNameTypeMap.put(Dialect.MYSQL57, DBType.MYSQL57);
		// mariaDB的方言以mysql为基准
		DBNameTypeMap.put(Dialect.MARIADB, DBType.MYSQL);
		DBNameTypeMap.put(Dialect.INNOSQL, DBType.MYSQL);

		DBNameTypeMap.put(Dialect.POSTGRESQL, DBType.POSTGRESQL);
		DBNameTypeMap.put(Dialect.POSTGRESQL15, DBType.POSTGRESQL15);
		DBNameTypeMap.put(Dialect.GREENPLUM, DBType.POSTGRESQL);
		DBNameTypeMap.put(Dialect.GAUSSDB, DBType.GAUSSDB);
		// 20240702 增加对mogdb的支持
		DBNameTypeMap.put(Dialect.MOGDB, DBType.MOGDB);
		DBNameTypeMap.put(Dialect.OPENGAUSS, DBType.OPENGAUSS);
		DBNameTypeMap.put(Dialect.STARDB, DBType.STARDB);
		DBNameTypeMap.put(Dialect.MONGO, DBType.MONGO);
		DBNameTypeMap.put(Dialect.ES, DBType.ES);
		DBNameTypeMap.put(Dialect.SQLITE, DBType.SQLITE);
		DBNameTypeMap.put(Dialect.CLICKHOUSE, DBType.CLICKHOUSE);
		DBNameTypeMap.put(Dialect.OCEANBASE, DBType.OCEANBASE);
		// 2020-6-5 增加对达梦数据库的支持
		DBNameTypeMap.put(Dialect.DM, DBType.DM);
		// 2020-8-14 增加对人大金仓数据库支持
		DBNameTypeMap.put(Dialect.KINGBASE, DBType.KINGBASE);
		// 2020-6-7 启动增加对tidb的支持
		DBNameTypeMap.put(Dialect.TIDB, DBType.TIDB);
		DBNameTypeMap.put(Dialect.TDENGINE, DBType.TDENGINE);
		DBNameTypeMap.put(Dialect.IMPALA, DBType.IMPALA);
		DBNameTypeMap.put(Dialect.UNDEFINE, DBType.UNDEFINE);
		// 20220829 增加对h2的支持
		DBNameTypeMap.put(Dialect.H2, DBType.H2);
		DBNameTypeMap.put(Dialect.OSCAR, DBType.OSCAR);
		DBNameTypeMap.put(Dialect.VASTBASE, DBType.VASTBASE);
	}

	/**
	 * @todo 获取数据库类型
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static String getCurrentDBDialect(final Connection conn) throws SQLException {
		String dilectName = Dialect.UNDEFINE;
		// 从hashMap中获取
		if (null != conn) {
			// 剔除空白
			String dbDialect = conn.getMetaData().getDatabaseProductName().replaceAll("\\s+", "");
			// oracle
			if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.ORACLE) != -1) {
				dilectName = Dialect.ORACLE;
			} // mysql以及mysql的分支数据库
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.MYSQL) != -1
					|| StringUtil.indexOfIgnoreCase(dbDialect, Dialect.MARIADB) != -1
					|| StringUtil.indexOfIgnoreCase(dbDialect, Dialect.INNOSQL) != -1) {
				dilectName = Dialect.MYSQL;
			} // postgresql
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.POSTGRESQL) != -1) {
				dilectName = Dialect.POSTGRESQL;
			} // sqlserver,只支持2012或以上版本
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.SQLSERVER) != -1
					|| StringUtil.indexOfIgnoreCase(dbDialect, "mssql") != -1
					|| StringUtil.indexOfIgnoreCase(dbDialect, "microsoftsqlserver") != -1) {
				dilectName = Dialect.SQLSERVER;
			} // db2
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.DB2) != -1) {
				dilectName = Dialect.DB2;
			} // clickhouse
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.CLICKHOUSE) != -1) {
				dilectName = Dialect.CLICKHOUSE;
			} // OCEANBASE
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.OCEANBASE) != -1) {
				dilectName = Dialect.OCEANBASE;
			} // opengauss
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.OPENGAUSS) != -1) {
				dilectName = Dialect.OPENGAUSS;
			}
			// GAUSSDB
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.GAUSSDB) != -1
					|| "zenith".equalsIgnoreCase(dbDialect)) {
				dilectName = Dialect.GAUSSDB;
			} // MOGDB
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.MOGDB) != -1) {
				dilectName = Dialect.MOGDB;
			} else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.STARDB) != -1) {
				dilectName = Dialect.STARDB;
			} else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.SQLITE) != -1) {
				dilectName = Dialect.SQLITE;
			} // dm
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.DM) != -1) {
				dilectName = Dialect.DM;
			} // TIDB
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.TIDB) != -1) {
				dilectName = Dialect.TIDB;
			} // 2022-12-14 验证
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.TDENGINE) != -1) {
				dilectName = Dialect.TDENGINE;
			} else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.KINGBASE) != -1) {
				dilectName = Dialect.KINGBASE;
			} else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.GREENPLUM) != -1) {
				dilectName = Dialect.POSTGRESQL;
			} else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.IMPALA) != -1) {
				dilectName = Dialect.IMPALA;
			} // elasticsearch
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.ES) != -1) {
				dilectName = Dialect.ES;
			} // 20220829 h2
			else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.H2) != -1) {
				dilectName = Dialect.H2;
			} else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.OSCAR) != -1) {
				dilectName = Dialect.OSCAR;
			} else if (StringUtil.indexOfIgnoreCase(dbDialect, Dialect.VASTBASE) != -1) {
				dilectName = Dialect.VASTBASE;
			}
		}
		return dilectName;
	}

	/**
	 * @todo 获取当前数据库的版本
	 * @return
	 */
	public static int getCurrentDBVersion(final Connection conn) {
		// -1表示版本不确定
		int result = -1;
		// 部分数据库驱动还不支持此方法
		try {
			result = conn.getMetaData().getDatabaseMajorVersion();
		} catch (Exception e) {
		}
		return result;
	}

	/**
	 * @todo 获取数据库类型
	 * @author zhongxuchen
	 * @date 2011-8-3 下午06:25:41
	 * @param conn
	 * @return
	 * @throws SQLException
	 */
	public static int getDbType(final Connection conn) throws SQLException {
		// 从hashMap中获取
		String productName = conn.getMetaData().getDatabaseProductName();
		int majorVersion = conn.getMetaData().getDatabaseMajorVersion();
		String dbKey = productName + majorVersion;
		if (!DBNameTypeMap.containsKey(dbKey)) {
			String dbDialect = getCurrentDBDialect(conn);
			int dbType = DBType.UNDEFINE;
			// oracle12+
			if (dbDialect.equals(Dialect.ORACLE)) {
				dbType = DBType.ORACLE;
				if (majorVersion <= 11) {
					dbType = DBType.ORACLE11;
				}
			} else if (dbDialect.equals(Dialect.ORACLE11)) {
				dbType = DBType.ORACLE11;
			} // mysql以及mysql的分支数据库
			else if (dbDialect.equals(Dialect.MYSQL)) {
				dbType = DBType.MYSQL;
				if (majorVersion <= 5) {
					dbType = DBType.MYSQL57;
				}
			} else if (dbDialect.equals(Dialect.MYSQL57)) {
				dbType = DBType.MYSQL57;
			} // 9.5以上为标准支持模式
			else if (dbDialect.equals(Dialect.POSTGRESQL)) {
				dbType = DBType.POSTGRESQL;
				if (majorVersion >= 15) {
					dbType = DBType.POSTGRESQL15;
				}
			} else if (dbDialect.equals(Dialect.POSTGRESQL15)) {
				dbType = DBType.POSTGRESQL15;
			} else if (dbDialect.equals(Dialect.GREENPLUM)) {
				dbType = DBType.POSTGRESQL;
			} // sqlserver,只支持2012或以上版本
			else if (dbDialect.equals(Dialect.SQLSERVER)) {
				dbType = DBType.SQLSERVER;
			} // db2 10+版本
			else if (dbDialect.equals(Dialect.DB2)) {
				dbType = DBType.DB2;
			} else if (dbDialect.equals(Dialect.CLICKHOUSE)) {
				dbType = DBType.CLICKHOUSE;
			} else if (dbDialect.equals(Dialect.OCEANBASE)) {
				dbType = DBType.OCEANBASE;
			} else if (dbDialect.equals(Dialect.OPENGAUSS)) {
				dbType = DBType.OPENGAUSS;
			} else if (dbDialect.equals(Dialect.GAUSSDB)) {
				dbType = DBType.GAUSSDB;
			} else if (dbDialect.equals(Dialect.MOGDB)) {
				dbType = DBType.MOGDB;
			} else if (dbDialect.equals(Dialect.STARDB)) {
				dbType = DBType.STARDB;
			} else if (dbDialect.equals(Dialect.SQLITE)) {
				dbType = DBType.SQLITE;
			} else if (dbDialect.equals(Dialect.DM)) {
				dbType = DBType.DM;
			} else if (dbDialect.equals(Dialect.TIDB)) {
				dbType = DBType.TIDB;
			} else if (dbDialect.equals(Dialect.IMPALA)) {
				dbType = DBType.IMPALA;
			} else if (dbDialect.equals(Dialect.TDENGINE)) {
				dbType = DBType.TDENGINE;
			} else if (dbDialect.equals(Dialect.KINGBASE)) {
				dbType = DBType.KINGBASE;
			} else if (dbDialect.equals(Dialect.ES)) {
				dbType = DBType.ES;
			} else if (dbDialect.equals(Dialect.H2)) {
				dbType = DBType.H2;
			} else if (dbDialect.equals(Dialect.OSCAR)) {
				dbType = DBType.OSCAR;
			} else if (dbDialect.equals(Dialect.VASTBASE)) {
				dbType = DBType.VASTBASE;
			}
			DBNameTypeMap.put(dbKey, dbType);
		}
		return DBNameTypeMap.get(dbKey);
	}

	/**
	 * @todo 提供统一的ResultSet,PreparedStatemenet 关闭功能
	 * @param userData
	 * @param pst
	 * @param rs
	 * @param preparedStatementResultHandler
	 * @return
	 */
	public static Object preparedStatementProcess(Object userData, PreparedStatement pst, ResultSet rs,
			PreparedStatementResultHandler preparedStatementResultHandler) throws Exception {
		try {
			preparedStatementResultHandler.execute(userData, pst, rs);
		} catch (Exception se) {
			se.printStackTrace();
			logger.info(se.getMessage());
			throw se;
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (pst != null) {
					pst.close();
					pst = null;
				}
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
		return preparedStatementResultHandler.getResult();
	}
}