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
Show all versions of sql2java-base Show documentation
sql2java common class package
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 © 2015 - 2025 Weber Informatics LLC | Privacy Policy