gu.sql2java.utils.JDBCUtility Maven / Gradle / Ivy

Go to download
Show more of this group Show more artifacts with this name
Show all versions of sql2java-base Show documentation
sql2java common class package
The newest version!
package gu.sql2java.utils;

import static com.google.common.base.Preconditions.checkArgument;
import static com.google.common.base.Preconditions.checkNotNull;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.google.common.base.MoreObjects;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.google.common.primitives.Ints;
import gu.simplemq.SimpleLog;

/**
 * JDBC工具类
 * @author guyadong
 * @since 3.18.0
 *
 */
public class JDBCUtility {
	private final static ImmutableMap> sqlTypes;
	static{
		sqlTypes=new ImmutableMap.Builder>().put(Types.ARRAY,java.sql.Array.class)
				.put(Types.BIGINT,Long.class)
				.put(Types.BINARY,byte[].class)
				.put(Types.BIT,Boolean.class)
				.put(Types.BLOB,byte[].class)
				.put(Types.BOOLEAN,Boolean.class)
				.put(Types.CHAR,String.class)
				.put(Types.CLOB,String.class)
				.put(Types.DATALINK,java.net.URL.class)
				.put(Types.DATE,java.util.Date.class)
				.put(Types.DECIMAL,java.math.BigDecimal.class)
				.put(Types.DISTINCT,Object.class)
				.put(Types.DOUBLE,Double.class)
				.put(Types.FLOAT,Float.class)
				.put(Types.INTEGER,Integer.class)
				.put(Types.JAVA_OBJECT,Object.class)
				.put(Types.LONGVARBINARY,byte[].class)
				.put(Types.LONGVARCHAR,String.class)
				.put(Types.NUMERIC,java.math.BigDecimal.class)
				.put(Types.OTHER,Object.class)
				.put(Types.REAL,Float.class)
				.put(Types.REF,java.sql.Ref.class)
				.put(Types.SMALLINT,Integer.class)
				.put(Types.STRUCT, java.sql.Struct.class)
				.put(Types.TIME, java.util.Date.class)
				.put(Types.TIMESTAMP, java.sql.Timestamp.class)
				.put(Types.TINYINT, Integer.class)
				.put(Types.VARBINARY,byte[].class)
				.put(Types.VARCHAR, String.class)	.build();
	}
	private static Properties defaultConnProperties = new Properties();
	static{
		defaultConnProperties.put("useSSL", "false");
	}
	
	/**
	 * 设置默认的JDBC连接参数,
	 * 为{@code null}忽略,用于为{@link #createConnection(String, Properties)}和
	 * {@link #createConnection(String, String, String, Properties)}指定默认参数
	 * 当前默认值为"useSSL=false"
	 * @param defaultConnProperties
	 */
	public static void setDefaultConnProperties(Properties defaultConnProperties) {
		if(null != defaultConnProperties){
			JDBCUtility.defaultConnProperties = defaultConnProperties;
		}
	}

	/**
	 * @return 返回当前默认的JDBC连接参数
	 */
	public static Properties getDefaultConnProperties() {
		return defaultConnProperties;
	}

	/**
	 * 
	 * @param sqlType SQL Type,see also {@link Types}
	 * @return 返回 SQL Type对应的Java类
	 */
	public static Class getJavaClass(int sqlType) {
		Class clazz = sqlTypes.get(sqlType);
		if(null != clazz){
			return clazz;
		}
		throw new UnsupportedOperationException("Not supported SQL Type yet: " + sqlType);
	}
	
	/**
	 * 查询数据中给定匹配模式的所有表名
	 * @param meta
	 * @param catalog 可为{@code null}
	 * @param schemaPattern schema 支持'%'匹配
	 * @param tableNamePattern 表名,支持'%'匹配
	 * @return catalog为{@code null}则返回 $schema.$tablename 格式的表名列表,否则返回 $catalog.$schema.$tablename 格式的表名列表, 
	 * 	               没有找到返回空表
	 * @throws SQLException
	 */
	public final static List getTablenames(DatabaseMetaData meta,String catalog,String schemaPattern,String tableNamePattern) throws SQLException{
		ImmutableList.Builder builder = new ImmutableList.Builder<>();
		if(null != meta){
			try (ResultSet resultSet = meta.getTables(catalog, schemaPattern, tableNamePattern, new String[]{"TABLE"})){
				while (resultSet.next()) {
					String c = resultSet.getString("TABLE_CAT");
					String s = resultSet.getString("TABLE_SCHEM");
					String t = resultSet.getString("TABLE_NAME");
//					SimpleLog.log("TABLE_CAT={}",s);
//					SimpleLog.log("TABLE_SCHEM={}",s);
//					SimpleLog.log("TABLE_NAME={}",t);
					String qualityName = t;
					if(null != s){
						qualityName = s + "." + qualityName;
					}
					if(null != c){
						qualityName = c + "." + qualityName;
					}
					SimpleLog.log("    table " +qualityName + " found");
					builder.add(qualityName);
				}
			} 
		}
		return builder.build();
	}
	/**
	 * 通过JDBC 接口返回指定表的字段属性
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @param columnLabel 属性名
	 * @param type
	 */
	public final static List getColumnLabel(DatabaseMetaData meta,String catalog,String schematable,String columnLabel,Class type) {
		if (null != meta) {
			String[] nodes = parseSchematable(schematable);
			String schema = nodes[nodes.length-2];
			String tablename = nodes[nodes.length-1];
			try {
				ImmutableList.Builder builder = ImmutableList.builder();
				ResultSet resultSet = checkNotNull(meta, "meta is uninitialized").getColumns(catalog, schema, tablename,
						"%");
				while (resultSet.next()) {
					T column = resultSet.getObject(columnLabel,type);
					builder.add(column);
				}
				resultSet.close();
				return builder.build();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		return null;
	}
	/**
	 * 通过JDBC 接口返回指定表的字段名列表
	 * 
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @return 字段名列表
	 */
	public final static List getColumnNames(DatabaseMetaData meta,String catalog,String schematable) {
		return getColumnLabel(meta,catalog,schematable,"COLUMN_NAME",String.class);
	}
	/**
	 * 通过JDBC 接口返回指定表的字段类型列表
	 * 
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @return 字段名列表
	 */
	public final static List getColumnTypeNames(DatabaseMetaData meta,String catalog,String schematable) {
		return getColumnLabel(meta,catalog,schematable,"TYPE_NAME",String.class);
	}
	/**
	 * 通过JDBC 接口返回指定表的字段的SQL 类型数组,参见{@link java.sql.Types}
	 * 
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @return SQL 类型数组
	 */
	public final static int[] getSqlTypes(DatabaseMetaData meta,String catalog,String schematable) {
		List a = getColumnLabel(meta,catalog,schematable,"DATA_TYPE",Integer.class);
		return Ints.toArray(a);
	}
	/**
	 * 通过{@link DatabaseMetaData}返回表所有字段的Java类型
	 * @param meta
	 * @param catalog
	 * @param schematable 表名(schema+tablename)
	 * @param targetTypes
	 * @return 保存所有字段的Java类型的数组
	 */
	public final static Class[] getColumnTypes(DatabaseMetaData meta,String catalog,String schematable,
			Map> targetTypes){
		List typenames = getColumnNames(meta,catalog,schematable);
		int[] sqltypes = getSqlTypes(meta,catalog,schematable);
		targetTypes = MoreObjects.firstNonNull(targetTypes, Collections.>emptyMap());
		Class[] types = new Class[sqltypes.length];
		for(int i=0; i getColumnNames(ResultSetMetaData metaData){
		try {
			String[] names = new String[metaData.getColumnCount()];
			for(int i=0; i getColumnTypeNames(ResultSetMetaData metaData){
		try {
			String[] names = new String[metaData.getColumnCount()];
			for(int i=0; i[] getColumnTypes(ResultSetMetaData metaData, 
			Map> targetTypes){
		try {
			targetTypes = MoreObjects.firstNonNull(targetTypes, Collections.>emptyMap());
			Class[] types = new Class[metaData.getColumnCount()];
			for(int i=0; i