com.github.xiaoyuge5201.util.QuerySqlUtil Maven / Gradle / Ivy
Go to download
Show more of this group Show more artifacts with this name
Show all versions of datasource-spring-boot-starter Show documentation
Show all versions of datasource-spring-boot-starter Show documentation
数据库连接工具，查询mysql、oracle、sqlserver、postgresql的数据表以及字段信息；同时支持导出数据库设计文档
package com.github.xiaoyuge5201.util;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.xiaoyuge5201.config.DatabaseDriverEnum;
import com.github.xiaoyuge5201.entity.ColumnEntity;
import com.google.common.base.CaseFormat;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* 数据库查询sql语句
*/
public class QuerySqlUtil {
/**
* 查询所有的表结构信息
*
* @param driverClassName 驱动
* @param url 地址
* @param username 用户名
* @param password 密码
* @param database 数据库
* @return 表结构列表
*/
public static List findAllTables(String driverClassName, String url, String username, String password, String database) {
Connection connection = ConnectUtil.getConnection(driverClassName, url, username, password, database);
if (connection != null) {
List list = new ArrayList<>(16);
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String query = getTablesInfoBySqlAndType(driverClassName, database);
stmt = connection.prepareStatement(query);
rs = stmt.executeQuery();
while (rs.next()) {
list.add(rs.getString("TABLE_NAME"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
return null;
}
/**
* 查詢數據庫表的字段信息
*
* @param driverClassName 驱动
* @param url 地址
* @param username 用户名
* @param password 密码
* @param database 数据库
* @param table 数据表
* @return 表字段列表
*/
public static List queryTableFieldsToColumnEntity(String driverClassName, String url, String username, String password, String database, String table) {
Connection connection = ConnectUtil.getConnection(driverClassName, url, username, password, database);
if (connection != null) {
List list = new ArrayList<>(16);
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String query = queryColumnSql(driverClassName, database, table);
stmt = connection.prepareStatement(query);
rs = stmt.executeQuery();
while (rs.next()) {
ColumnEntity columnEntity = new ColumnEntity();
String columnName = rs.getString("COLUMN_NAME");
columnEntity.setColumnName(columnName);
columnEntity.setDataType(rs.getString("DATA_TYPE"));
columnEntity.setCharacterMaximunLength(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
columnEntity.setIsNullable(rs.getString("IS_NULLABLE"));
columnEntity.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
columnEntity.setColumnComment(rs.getString("COLUMN_COMMENT"));
columnEntity.setTableName(rs.getString("TABLE_NAME"));
columnEntity.setJavaName(CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnName));
columnEntity.setTableComment(rs.getString("TABLE_COMMENT"));
list.add(columnEntity);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
return null;
}
/**
* 查詢數據庫表的字段信息
*
* @param driverClassName 驱动
* @param url 地址
* @param username 用户名
* @param password 密码
* @param database 数据库
* @param table 数据表
* @return 表字段列表
*/
public static List queryTableFields(String driverClassName, String url, String username, String password, String database, String table) {
Connection connection = ConnectUtil.getConnection(driverClassName, url, username, password, database);
if (connection != null) {
List list = new ArrayList<>(16);
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String query = queryColumnSql(driverClassName, database, table);
stmt = connection.prepareStatement(query);
rs = stmt.executeQuery();
while (rs.next()) {
list.add(rs.getString("COLUMN_NAME"));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
return null;
}
/**
* 查询对应库下所有字段 信息
*
* @param driverClassName 驱动
* @param url 地址
* @param username 用户名
* @param password 密码
* @param database 数据库
* @return 结果
*/
public static List listColumnsByDatasourceParams(String driverClassName, String url, String username, String password, String database) {
Connection connection = ConnectUtil.getConnection(driverClassName, url, username, password, database);
if (connection != null) {
List list = new ArrayList<>(16);
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String query = getSqlByType(driverClassName, database);
stmt = connection.prepareStatement(query);
rs = stmt.executeQuery();
while (rs.next()) {
ColumnEntity columnEntity = new ColumnEntity();
columnEntity.setColumnName(rs.getString("COLUMN_NAME"));
columnEntity.setDataType(rs.getString("DATA_TYPE"));
columnEntity.setCharacterMaximunLength(rs.getString("CHARACTER_MAXIMUM_LENGTH"));
columnEntity.setIsNullable(rs.getString("IS_NULLABLE"));
columnEntity.setColumnDefault(rs.getString("COLUMN_DEFAULT"));
columnEntity.setColumnComment(rs.getString("COLUMN_COMMENT"));
columnEntity.setTableName(rs.getString("TABLE_NAME"));
columnEntity.setTableComment(rs.getString("TABLE_COMMENT"));
list.add(columnEntity);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
}
return null;
}
/**
* 分页查询数据
*
* @param driverClassName 驱动
* @param database 数据库
* @param table 数据表
* @param url 地址
* @param username 用户名
* @param password 密码
* @param columns 字段
* @param pageNo 页码
* @param limit 页容量
* @return 结果
*/
public static JSONArray queryPageData(String driverClassName, String database, String table, String url, String username, String password, List columns, Integer pageNo, Integer limit) throws Exception {
limit = (limit == null) ? 10 : limit;
pageNo = (pageNo == null) ? 1 : pageNo;
if (limit > 1000) {
throw new Exception("最多只能查询1000条数据");
}
Connection connection = ConnectUtil.getConnection(driverClassName, url, username, password, database);
if (connection != null) {
JSONArray array = new JSONArray();
PreparedStatement stmt = null;
ResultSet rs = null;
try {
String column = String.join(",", columns);
stmt = connection.prepareStatement(queryDataSql(driverClassName, table, column, pageNo, limit));
rs = stmt.executeQuery();
while (rs.next()) {
JSONObject jsonObject = new JSONObject();
for (String s : columns) {
jsonObject.fluentPut(s, rs.getObject(s));
}
array.add(jsonObject);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return array;
}
return null;
}
/**
* 根据数据库类型查询所有的表结构信息
*
* @param driverName 数据库类型
* @param database 数据库
* @return 表结构
*/
private static String getTablesInfoBySqlAndType(String driverName, String database) {
if (DatabaseDriverEnum.MYSQL.getDriver().equals(driverName)) {
//mysql
return "SELECT TABLE_NAME AS TABLE_NAME FROM information_schema.tables WHERE TABLE_SCHEMA=\"" + database + "\" AND TABLE_NAME != 'hibernate_sequence'";
} else if (DatabaseDriverEnum.SQL_SERVER.getDriver().equals(driverName)) {
//sql server
return "SELECT name AS TABLE_NAME FROM sysobjects WHERE xtype='U'";
} else if (DatabaseDriverEnum.POSTGRE_SQL.getDriver().equals(driverName)) {
//PostgreSQL
return "SELECT tablename AS TABLE_NAME FROM pg_tables WHERE schemaname='public'";
} else if (DatabaseDriverEnum.ORACLE.getDriver().equals(driverName)) {
//ORACLE
return " SELECT TABLE_NAME AS TABLE_NAME FROM USER_TABLES ";
}
return "";
}
/**
* 根据数据库类型查询表字段信息
*
* @param driverName 驱动类型
* @param database 数据库
* @param table 表
* @return 字段信息列表
*/
private static String queryColumnSql(String driverName, String database, String table) {
if (DatabaseDriverEnum.MYSQL.getDriver().equals(driverName)) {
//mysql
return " SELECT DISTINCT" +
" col.COLUMN_NAME AS COLUMN_NAME," +
" col.DATA_TYPE AS DATA_TYPE," +
" col.CHARACTER_MAXIMUM_LENGTH AS CHARACTER_MAXIMUM_LENGTH," +
" col.IS_NULLABLE AS IS_NULLABLE," +
" col.COLUMN_DEFAULT AS COLUMN_DEFAULT," +
" col.COLUMN_COMMENT AS COLUMN_COMMENT," +
" tab.TABLE_NAME as TABLE_NAME," +
" tab.TABLE_COMMENT as TABLE_COMMENT" +
" FROM" +
" INFORMATION_SCHEMA.COLUMNS col INNER JOIN information_schema.TABLES tab ON col.TABLE_NAME = tab.TABLE_NAME" +
" WHERE col.TABLE_SCHEMA='" + database + "' AND tab.TABLE_NAME='" + table + "' GROUP BY col.COLUMN_NAME HAVING count(*) > 1 ";
} else if (DatabaseDriverEnum.SQL_SERVER.getDriver().equals(driverName)) {
//sql server
return "SELECT CONVERT(varchar(500),obj.name) AS TABLE_NAME,\n" +
"\t\t\t\tCONVERT(varchar(500),ISNULL(epTwo.value, '')) AS TABLE_COMMENT,\n" +
" CONVERT(varchar(500),col.name) AS COLUMN_NAME , \n" +
" CONVERT(varchar(500),ISNULL(ep.[value], '')) AS COLUMN_COMMENT , \n" +
" CONVERT(varchar(500),t.name) AS DATA_TYPE , \n" +
" col.length AS CHARACTER_MAXIMUM_LENGTH , \n" +
" CASE WHEN col.isnullable = 1 THEN 'YES' \n" +
" ELSE 'NO' \n" +
" END AS IS_NULLABLE , \n" +
" CONVERT(varchar(500),ISNULL(comm.text, '')) AS COLUMN_DEFAULT \n" +
"FROM dbo.syscolumns col \n" +
" LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype \n" +
" inner JOIN dbo.sysobjects obj ON col.id = obj.id \n" +
" AND obj.xtype = 'U' \n" +
" AND obj.status >= 0 \n" +
" LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id \n" +
" LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id \n" +
" AND col.colid = ep.minor_id \n" +
" AND ep.name = 'MS_Description' \n" +
" LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id \n" +
" AND epTwo.minor_id = 0 \n" +
" AND epTwo.name = 'MS_Description'\n" +
" WHERE id=Object_Id('" + table + "') " +
"ORDER BY obj.name";
} else if (DatabaseDriverEnum.POSTGRE_SQL.getDriver().equals(driverName)) {
return "SELECT DISTINCT\n" +
"\tC.relname AS TABLE_NAME,\n" +
"\tCAST (\n" +
"\t\tobj_description (relfilenode, 'pg_class') AS VARCHAR\n" +
"\t) AS TABLE_COMMENT,\n" +
"\tA.attname AS COLUMN_NAME,\n" +
"\tformat_type (A .atttypid, A .atttypmod) AS DATA_TYPE,\n" +
"\t(\n" +
"\t\tCASE\n" +
"\t\tWHEN A .attlen > 0 THEN\n" +
"\t\t\tA .attlen\n" +
"\t\tELSE\n" +
"\t\t\tA .atttypmod - 4\n" +
"\t\tEND\n" +
"\t) AS CHARACTER_MAXIMUM_LENGTH,\n" +
"\tcol_description (A .attrelid, A .attnum) AS COLUMN_COMMENT,\n" +
"\tT.IS_NULLABLE AS IS_NULLABLE,\n" +
"\tT.COLUMN_DEFAULT AS COLUMN_DEFAULT\n" +
"FROM\n" +
"\tpg_class AS C\n" +
"INNER JOIN pg_attribute AS A ON A .attrelid = C .oid\n" +
"INNER JOIN (\n" +
"\tSELECT\n" +
"\t\tA1. TABLE_NAME AS TABLE_NAME,\n" +
"\t\tA1. COLUMN_NAME AS COLUMN_NAME,\n" +
"\t\tA1.IS_NULLABLE AS IS_NULLABLE,\n" +
"\t\tA1.COLUMN_DEFAULT AS COLUMN_DEFAULT\n" +
"\tFROM\n" +
"\t\tINFORMATION_SCHEMA. COLUMNS A1\n" +
"\tWHERE\n" +
"\t\tA1.TABLE_SCHEMA = 'public'\n" +
"\tORDER BY\n" +
"\t\tA1. TABLE_NAME\n" +
") T ON C.relname = T.TABLE_NAME AND T.COLUMN_NAME = A.attname\n" +
"WHERE A .attrelid = C .oid\n" +
"AND A .attnum > 0 AND C.relname ='" + table + "'" +
"ORDER BY C .relname DESC";
} else if (DatabaseDriverEnum.ORACLE.getDriver().equals(driverName)) {
//ORACLE
return " SELECT\n" +
" \tUTC.COLUMN_NAME AS COLUMN_NAME,\n" +
" \tUTC.DATA_TYPE AS DATA_TYPE,\n" +
" \tUTC.DATA_LENGTH AS CHARACTER_MAXIMUM_LENGTH,\n" +
" \tUTC.NULLABLE AS IS_NULLABLE,\n" +
" \tUTC.DATA_DEFAULT AS COLUMN_DEFAULT,\n" +
" \tUCC.COMMENTS AS COLUMN_COMMENT,\n" +
" \tUTC.TABLE_NAME AS TABLE_NAME,\n" +
" \tUTS.COMMENTS AS TABLE_COMMENT\n" +
" FROM\n" +
" \tUSER_TAB_COLUMNS UTC\n" +
" INNER JOIN USER_TAB_COMMENTS UTS ON UTC.TABLE_NAME = UTS.TABLE_NAME\n" +
" INNER JOIN USER_COL_COMMENTS UCC ON UTC.TABLE_NAME = UCC.TABLE_NAME AND UTC.COLUMN_NAME = UCC.COLUMN_NAME\n" +
" WHERE UTC.TABLE_NAME ='" + table + "'" +
" ORDER BY TABLE_NAME";
}
return "";
}
/**
* 根据数据库类型查询limit条样例数据
*
* @param driverName 驱动
* @param table 数据表
* @param columns 字段
* @param pageNo 页码
* @param limit 查询条数
* @return 结果
*/
private static String queryDataSql(String driverName, String table, String columns, Integer pageNo, Integer limit) {
int offset = (pageNo - 1) * limit;
if (DatabaseDriverEnum.MYSQL.getDriver().equals(driverName)) {
//mysql
return "SELECT " + columns + " FROM " + table + " LIMIT " + offset + "," + limit;
} else if (DatabaseDriverEnum.SQL_SERVER.getDriver().equals(driverName)) {
//sql server
return "SELECT top " + limit + columns + " FROM " + table;
} else if (DatabaseDriverEnum.POSTGRE_SQL.getDriver().equals(driverName)) {
//PostgreSQL
return "SELECT " + columns + " FROM " + table + " LIMIT " + limit + "," + offset;
} else if (DatabaseDriverEnum.ORACLE.getDriver().equals(driverName)) {
//ORACLE
return " SELECT * FROM ( SELECT " + columns + ",ROWNUM rc FROM " + table + " WHERE ROWNUM <=" + limit + ")a where a.rc>=" + offset;
}
return "";
}
/**
* 根据数据库类型查询表字段信息
*
* @param driverName 驅動類型
* @param database 数据库
* @return 表字段列表
*/
private static String getSqlByType(String driverName, String database) {
if (DatabaseDriverEnum.MYSQL.getDriver().equals(driverName)) {
//mysql
return " SELECT DISTINCT" +
" col.COLUMN_NAME AS COLUMN_NAME," +
" col.DATA_TYPE AS DATA_TYPE," +
" col.CHARACTER_MAXIMUM_LENGTH AS CHARACTER_MAXIMUM_LENGTH," +
" col.IS_NULLABLE AS IS_NULLABLE," +
" col.COLUMN_DEFAULT AS COLUMN_DEFAULT," +
" col.COLUMN_COMMENT AS COLUMN_COMMENT," +
" tab.TABLE_NAME as TABLE_NAME," +
" tab.TABLE_COMMENT as TABLE_COMMENT" +
" FROM" +
" INFORMATION_SCHEMA.COLUMNS col INNER JOIN information_schema.TABLES tab ON col.TABLE_NAME = tab.TABLE_NAME" +
" WHERE col.TABLE_SCHEMA='" + database + "'";
} else if (DatabaseDriverEnum.SQL_SERVER.getDriver().equals(driverName)) {
//sql server
return "SELECT CONVERT(varchar(500),obj.name) AS TABLE_NAME,\n" +
"\t\t\t\tCONVERT(varchar(500),ISNULL(epTwo.value, '')) AS TABLE_COMMENT,\n" +
" CONVERT(varchar(500),col.name) AS COLUMN_NAME , \n" +
" CONVERT(varchar(500),ISNULL(ep.[value], '')) AS COLUMN_COMMENT , \n" +
" CONVERT(varchar(500),t.name) AS DATA_TYPE , \n" +
" col.length AS CHARACTER_MAXIMUM_LENGTH , \n" +
" CASE WHEN col.isnullable = 1 THEN 'YES' \n" +
" ELSE 'NO' \n" +
" END AS IS_NULLABLE , \n" +
" CONVERT(varchar(500),ISNULL(comm.text, '')) AS COLUMN_DEFAULT \n" +
"FROM dbo.syscolumns col \n" +
" LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype \n" +
" inner JOIN dbo.sysobjects obj ON col.id = obj.id \n" +
" AND obj.xtype = 'U' \n" +
" AND obj.status >= 0 \n" +
" LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id \n" +
" LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id \n" +
" AND col.colid = ep.minor_id \n" +
" AND ep.name = 'MS_Description' \n" +
" LEFT JOIN sys.extended_properties epTwo ON obj.id = epTwo.major_id \n" +
" AND epTwo.minor_id = 0 \n" +
" AND epTwo.name = 'MS_Description'\n" +
"ORDER BY obj.name";
} else if (DatabaseDriverEnum.POSTGRE_SQL.getDriver().equals(driverName)) {
//PostgreSQL
return "SELECT DISTINCT\n" +
"\tC.relname AS TABLE_NAME,\n" +
"\tCAST (\n" +
"\t\tobj_description (relfilenode, 'pg_class') AS VARCHAR\n" +
"\t) AS TABLE_COMMENT,\n" +
"\tA.attname AS COLUMN_NAME,\n" +
"\tformat_type (A .atttypid, A .atttypmod) AS DATA_TYPE,\n" +
"\t(\n" +
"\t\tCASE\n" +
"\t\tWHEN A .attlen > 0 THEN\n" +
"\t\t\tA .attlen\n" +
"\t\tELSE\n" +
"\t\t\tA .atttypmod - 4\n" +
"\t\tEND\n" +
"\t) AS CHARACTER_MAXIMUM_LENGTH,\n" +
"\tcol_description (A .attrelid, A .attnum) AS COLUMN_COMMENT,\n" +
"\tT.IS_NULLABLE AS IS_NULLABLE,\n" +
"\tT.COLUMN_DEFAULT AS COLUMN_DEFAULT\n" +
"FROM\n" +
"\tpg_class AS C\n" +
"INNER JOIN pg_attribute AS A ON A .attrelid = C .oid\n" +
"INNER JOIN (\n" +
"\tSELECT\n" +
"\t\tA1. TABLE_NAME AS TABLE_NAME,\n" +
"\t\tA1. COLUMN_NAME AS COLUMN_NAME,\n" +
"\t\tA1.IS_NULLABLE AS IS_NULLABLE,\n" +
"\t\tA1.COLUMN_DEFAULT AS COLUMN_DEFAULT\n" +
"\tFROM\n" +
"\t\tINFORMATION_SCHEMA. COLUMNS A1\n" +
"\tWHERE\n" +
"\t\tA1.TABLE_SCHEMA = 'public'\n" +
"\tORDER BY\n" +
"\t\tA1. TABLE_NAME\n" +
") T ON C.relname = T.TABLE_NAME AND T.COLUMN_NAME = A.attname\n" +
"WHERE A .attrelid = C .oid\n" +
"AND A .attnum > 0\n" +
"ORDER BY C .relname DESC";
} else if (DatabaseDriverEnum.ORACLE.getDriver().equals(driverName)) {
//ORACLE
return " SELECT\n" +
" \tUTC.COLUMN_NAME AS COLUMN_NAME,\n" +
" \tUTC.DATA_TYPE AS DATA_TYPE,\n" +
" \tUTC.DATA_LENGTH AS CHARACTER_MAXIMUM_LENGTH,\n" +
" \tUTC.NULLABLE AS IS_NULLABLE,\n" +
" \tUTC.DATA_DEFAULT AS COLUMN_DEFAULT,\n" +
" \tUCC.COMMENTS AS COLUMN_COMMENT,\n" +
" \tUTC.TABLE_NAME AS TABLE_NAME,\n" +
" \tUTS.COMMENTS AS TABLE_COMMENT\n" +
" FROM\n" +
" \tUSER_TAB_COLUMNS UTC\n" +
" INNER JOIN USER_TAB_COMMENTS UTS ON UTC.TABLE_NAME = UTS.TABLE_NAME\n" +
" INNER JOIN USER_COL_COMMENTS UCC ON UTC.TABLE_NAME = UCC.TABLE_NAME AND UTC.COLUMN_NAME = UCC.COLUMN_NAME\n" +
" ORDER BY TABLE_NAME";
}
return "";
}
}