mysql 查主键字段长度_使用sql查询mysql/oracle/sql server/gp数据库中指定表的字段信息(字段名/字段类型/字段长度/是否是主键/是否为空)...

1,根据数据库类型拼接不同URL

/**

* 根据类型不同拼接连接的URL

* @param dbType 1：mysql、2：oracle、3：sql server、4：gp

* @param ip

* @param port

* @param databaseName

* @return*/

public static String getTestDbUrl(int dbType, String ip, String port, String databaseName){

String url = "";

if (Constant.DATABASE_TYPE_MYSQL == dbType){

//mysql

url = "jdbc:mysql://"+ip+":"+port+"/"+databaseName+"?useUnicode=true&characterEncoding=UTF8";

}else if (Constant.DATABASE_TYPE_ORACLE == dbType){

//oracle

url = "jdbc:oracle:thin:@"+ip+":"+port+":ORCL";

}else if (Constant.DATABASE_TYPE_SQL_SERVER == dbType){

//sql server

url = "jdbc:sqlserver://"+ip+":"+port+";databaseName="+databaseName+";integratedSecurity=true";

}else if (Constant.DATABASE_TYPE_GP == dbType){

//gp

url = "jdbc:postgresql://"+ip+":"+port+"/"+databaseName;

}

return url;

}

2,创建连接并查询

/**

* 通过jsbc获取数据

* @param driver driver

* @param url 数据库url

* @param username 用户名

* @param password 密码

* @param sql sql语句

* @param sqlType 语句类型 ，1：查询语句，2：创建语句

* @param columnConnt 查询语句返回列的个数

* @return List

*/

public static List> getJdbcData(String driver, String url, String username, String password, String sql, int sqlType, int columnConnt){

Connection con = null;

Statement st = null;

ResultSet rs = null;

List> result = new ArrayList<>();

try {

//1.加载oracle数据库驱动

Class.forName(driver);

//2.获取数据库连接

con = DriverManager.getConnection(url, username, password);

//3.获取执行sql语句的平台

st = con.createStatement();

//4.执行sql语句获取结果集

// 查询

if(sqlType == 1){

rs = st.executeQuery(sql);

//5.循环获取结果集数据

int i = 1;

while(rs.next()){

if(i <= columnConnt){

Map resultMap = new HashMap<>(columnConnt);

for (int j = 0; j < columnConnt; j++) {

resultMap.put("column" + (j + 1) + "", rs.getString(j + 1));

}

result.add(resultMap);

}

}

}else{

// 创建

int rss = st.executeUpdate(sql);

Map resultMap = new HashMap<>(columnConnt);

resultMap.put("column" + 1 + "", rss + "");

result.add(resultMap);

}

return result;

} catch (ClassNotFoundException e) {

e.printStackTrace();

Map resultMap = new HashMap<>(columnConnt);

resultMap.put("column1", "-1");

result.add(resultMap);

} catch (SQLException e) {

e.printStackTrace();

Map resultMap = new HashMap<>(columnConnt);

resultMap.put("column1", "-1");

result.add(resultMap);

}finally{

//关闭rs

if(rs != null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

//关闭st

if(st != null){

try {

st.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

//关闭con

if(con != null){

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

return result;

}

// return result;

}

3,设置参数

/**

* 查询指定数据库中指定表的字段信息

* @param databaseType 数据库类型: 1：mysql、2：oracle、3：sql server、4：gp

* @param databaseName 数据库名称

* @param databaseIp 数据库ip

* @param databasePort 数据库端口

* @param databaseUserName 数据库用户名

* @param databaseUserPassword 数据库用户的密码

* @param tableName 表名

* @return*/

public static List> getColumnInfoByTableName(int databaseType, String databaseName, String databaseIp, String databasePort, String databaseUserName,

String databaseUserPassword, String tableName){

String sql = "";

if (Constant.DATABASE_TYPE_MYSQL == databaseType){

//mysql

sql = "SELECT column_name, data_type,(case when data_type = 'int' or data_type = 'float' or data_type = 'double' or data_type = 'decimal' then NUMERIC_PRECISION else CHARACTER_MAXIMUM_LENGTH end ) as data_length,\n" +

"(case when IS_NULLABLE = 'NO' then 0 else 1 end)as data_Null,(case when COLUMN_KEY='PRI' then 1 else 0 end) as data_IsPK\n" +

" FROM information_schema.COLUMNS WHERE table_schema = '"+databaseName+"' and table_name = '"+tableName+"'";

}else if (Constant.DATABASE_TYPE_ORACLE == databaseType){

//oracle

sql = "SELECT column_name, data_type, data_length, NULLABLE,(case when column_name=(select col.column_name \n" +

"from user_constraints con, user_cons_columns col \n" +

"where con.constraint_name = col.constraint_name \n" +

"and con.constraint_type='P' \n" +

"and col.table_name = "+tableName+"\n" +

") then 1 else 0 end) as IsPK\n" +

" FROM all_tab_cols\n" +

" WHERE table_name = '"+tableName+"' ";

}else if (Constant.DATABASE_TYPE_SQL_SERVER == databaseType){

//sql server

sql = "SELECT C.name as column_name, T.name as data_type, COLUMNPROPERTY(C.id,C.name,'PRECISION') as data_length, \n" +

"convert(bit,case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=c.id and name in (\n" +

" SELECT name FROM sysindexes WHERE indid in(\n" +

" SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) then 1 else 0 end) \n" +

" as data_IsPK, convert(bit,C.IsNullable) as data_Null\n" +

"FROM syscolumns C INNER JOIN systypes T ON C.xusertype = T.xusertype \n" +

"inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' \n" +

"where T.name is not null\n" +

"and d.name='"+tableName+"'";

}else if (Constant.DATABASE_TYPE_GP == databaseType){

//gp

}

List> columnNameList = JdbcUtil.getJdbcData(SingletonHoldResource.getInstance().getDictMap(Constant.DB_DRIVER).get(Integer.toString(databaseType)),

getTestDbUrl(databaseType, databaseIp, databasePort, databaseName),

databaseUserName, databaseUserPassword,

sql,

1, 5);

//结果中的对应关系:column1 -- name; column2 -- type; column3 -- length; column4 -- IsNull; column5 -- isPk;

return columnNameList;

}