適配Mysql和Oracle數(shù)據(jù)源

文章目錄

• • 1. 適配Mysql和Oracle數(shù)據(jù)源
  • 2. 適配于Mysql數(shù)據(jù)源
  • 3. 適配Oeacle數(shù)據(jù)源

1. 適配Mysql和Oracle數(shù)據(jù)源

package cn.stylefeng.guns.generator.core.util; import cn.stylefeng.guns.generator.modular.entity.DatabaseInfo; import lombok.extern.slf4j.Slf4j;import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map;/*** @ClassName Dbutils* @Deacription TODO* @Author gblfy* @Date 2019/6/5 9:30* @Version 1.0**/ @Slf4j public class Dbutils {/*** 查詢(xún)某個(gè)數(shù)據(jù)庫(kù)連接的所有表** @author fengshuonan* @Date 2019-05-04 20:30*/public static List<Map<String, Object>> selectTables(DatabaseInfo dbInfo) {ArrayList<Map<String, Object>> list = new ArrayList<>();PreparedStatement preparedStatement = null;try {Class.forName(dbInfo.getJdbcDriver());Connection conn = DriverManager.getConnection(dbInfo.getJdbcUrl(), dbInfo.getUserName(), dbInfo.getPassword());String jdbcUrl = dbInfo.getJdbcUrl();/*****************************Mysql和Oracle數(shù)據(jù)源不同部分 Start******************************/if (jdbcUrl.contains("oracle")) {String dbName = dbInfo.getUserName();String db = dbName.toUpperCase();preparedStatement = conn.prepareStatement("SELECT S.TABLE_NAME AS tableName,S.COMMENTS AS tableComment FROM SYS.dba_TAB_comments S WHERE S.OWNER = '" + db + "'");} else if (jdbcUrl.contains("mysql")) {int first = jdbcUrl.lastIndexOf("/") + 1;int last = jdbcUrl.indexOf("?");String dbName = jdbcUrl.substring(first, last);preparedStatement = conn.prepareStatement("select TABLE_NAME as tableName,TABLE_COMMENT as tableComment from information_schema.`TABLES` where TABLE_SCHEMA = '" + dbName + "'");}/*****************************Mysql和Oracle數(shù)據(jù)源不同部分 End******************************/ ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {HashMap<String, Object> map = new HashMap<>();String tableName = resultSet.getString("tableName");String tableComment = resultSet.getString("tableComment");map.put("tableName", tableName);map.put("tableComment", tableComment);list.add(map);}return list;} catch (Exception ex) {log.error("執(zhí)行sql出現(xiàn)問(wèn)題！", ex);return null;}}/*** 查詢(xún)某個(gè)表的所有字段** @author fengshuonan* @Date 2019-05-04 20:31*/public static List<Map<String, Object>> getTableFields(DatabaseInfo dbInfo, String tableName) {ArrayList<Map<String, Object>> list = new ArrayList<>();PreparedStatement preparedStatement = null;try {Class.forName(dbInfo.getJdbcDriver());Connection conn = DriverManager.getConnection(dbInfo.getJdbcUrl(), dbInfo.getUserName(), dbInfo.getPassword());String jdbcUrl = dbInfo.getJdbcUrl();/*****************************Mysql和Oracle數(shù)據(jù)源不同部分 Start******************************/ if (jdbcUrl.contains("oracle")) {String dbName = dbInfo.getUserName();String db = dbName.toUpperCase();String tb = tableName.toUpperCase();preparedStatement = conn.prepareStatement("SELECT T.COLUMN_NAME as columnName,S.COMMENTS as columnComment FROM SYS.dba_TAB_columns T,SYS.ALL_COL_COMMENTS S WHERE T.TABLE_NAME = S.TABLE_NAME AND T.COLUMN_NAME = S.COLUMN_NAME AND T.OWNER = '" + db + "' AND T.TABLE_NAME = '" + tb + "'");} else if (jdbcUrl.contains("mysql")) {int first = jdbcUrl.lastIndexOf("/") + 1;int last = jdbcUrl.indexOf("?");String dbName = jdbcUrl.substring(first, last);preparedStatement = conn.prepareStatement("select COLUMN_NAME as columnName,COLUMN_COMMENT as columnComment from information_schema.COLUMNS where table_name = '" + tableName + "' and table_schema = '" + dbName + "'");}/*****************************Mysql和Oracle數(shù)據(jù)源不同部分 End******************************/ ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {HashMap<String, Object> map = new HashMap<>();String columnName = resultSet.getString("columnName");String columnComment = resultSet.getString("columnComment");map.put("columnName", columnName);map.put("columnComment", columnComment);list.add(map);}return list;} catch (Exception ex) {log.error("執(zhí)行sql出現(xiàn)問(wèn)題！", ex);return null;}} }

2. 適配于Mysql數(shù)據(jù)源

package cn.stylefeng.guns.generator.core.util;import cn.stylefeng.guns.generator.modular.entity.DatabaseInfo; import lombok.extern.slf4j.Slf4j;import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map;/*** 數(shù)據(jù)庫(kù)操作工具類(lèi)** @author fengshuonan* @Date 2019/1/13 18:34*/ @Slf4j public class DbUtil {/*** 查詢(xún)某個(gè)數(shù)據(jù)庫(kù)連接的所有表** @author fengshuonan* @Date 2019-05-04 20:30*/public static List<Map<String, Object>> selectTables(DatabaseInfo dbInfo) {ArrayList<Map<String, Object>> list = new ArrayList<>();try {Class.forName(dbInfo.getJdbcDriver());Connection conn = DriverManager.getConnection(dbInfo.getJdbcUrl(), dbInfo.getUserName(), dbInfo.getPassword());String jdbcUrl = dbInfo.getJdbcUrl();/*****************************Mysql數(shù)據(jù)源獨(dú)有部分 Start******************************/int first = jdbcUrl.lastIndexOf("/") + 1;int last = jdbcUrl.indexOf("?");String dbName = jdbcUrl.substring(first, last);PreparedStatement preparedStatement = conn.prepareStatement("select TABLE_NAME as tableName,TABLE_COMMENT as tableComment from information_schema.`TABLES` where TABLE_SCHEMA = '" + dbName + "'");/*****************************Mysql數(shù)據(jù)源獨(dú)有部分 End******************************/ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {HashMap<String, Object> map = new HashMap<>();String tableName = resultSet.getString("tableName");String tableComment = resultSet.getString("tableComment");map.put("tableName", tableName);map.put("tableComment", tableComment);list.add(map);}return list;} catch (Exception ex) {log.error("執(zhí)行sql出現(xiàn)問(wèn)題！", ex);return null;}}/*** 查詢(xún)某個(gè)表的所有字段** @author fengshuonan* @Date 2019-05-04 20:31*/public static List<Map<String, Object>> getTableFields(DatabaseInfo dbInfo, String tableName) {ArrayList<Map<String, Object>> list = new ArrayList<>();try {Class.forName(dbInfo.getJdbcDriver());Connection conn = DriverManager.getConnection(dbInfo.getJdbcUrl(), dbInfo.getUserName(), dbInfo.getPassword());String jdbcUrl = dbInfo.getJdbcUrl();/*****************************Mysql數(shù)據(jù)源獨(dú)有部分 Start******************************/int first = jdbcUrl.lastIndexOf("/") + 1;int last = jdbcUrl.indexOf("?");String dbName = jdbcUrl.substring(first, last);PreparedStatement preparedStatement = conn.prepareStatement("select COLUMN_NAME as columnName,COLUMN_COMMENT as columnComment from information_schema.COLUMNS where table_name = '" + tableName + "' and table_schema = '" + dbName + "'");/*****************************Mysql數(shù)據(jù)源獨(dú)有部分 End******************************/ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {HashMap<String, Object> map = new HashMap<>();String columnName = resultSet.getString("columnName");String columnComment = resultSet.getString("columnComment");map.put("columnName", columnName);map.put("columnComment", columnComment);list.add(map);}return list;} catch (Exception ex) {log.error("執(zhí)行sql出現(xiàn)問(wèn)題！", ex);return null;}} }

3. 適配Oeacle數(shù)據(jù)源

package cn.stylefeng.guns.generator.core.util;import cn.stylefeng.guns.generator.modular.entity.DatabaseInfo; import lombok.extern.slf4j.Slf4j;import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map;/*** 數(shù)據(jù)庫(kù)操作工具類(lèi)** @author fengshuonan* @Date 2019/1/13 18:34*/ @Slf4j public class DbUtil {/*** 查詢(xún)某個(gè)數(shù)據(jù)庫(kù)連接的所有表** @author fengshuonan* @Date 2019-05-04 20:30*/public static List<Map<String, Object>> selectTables(DatabaseInfo dbInfo) {ArrayList<Map<String, Object>> list = new ArrayList<>();try {Class.forName(dbInfo.getJdbcDriver());Connection conn = DriverManager.getConnection(dbInfo.getJdbcUrl(), dbInfo.getUserName(), dbInfo.getPassword());String jdbcUrl = dbInfo.getJdbcUrl();/*****************************Oracle數(shù)據(jù)源獨(dú)有部分 Start******************************/String dbName = dbInfo.getUserName();String db = dbName.toUpperCase();PreparedStatement preparedStatement = conn.prepareStatement("SELECT S.TABLE_NAME AS tableName,S.COMMENTS AS tableComment FROM SYS.dba_TAB_comments S WHERE S.OWNER = '" + db + "'");/*****************************Oracle數(shù)據(jù)源獨(dú)有部分 End******************************/ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {HashMap<String, Object> map = new HashMap<>();String tableName = resultSet.getString("tableName");String tableComment = resultSet.getString("tableComment");map.put("tableName", tableName);map.put("tableComment", tableComment);list.add(map);}return list;} catch (Exception ex) {log.error("執(zhí)行sql出現(xiàn)問(wèn)題！", ex);return null;}}/*** 查詢(xún)某個(gè)表的所有字段** @author fengshuonan* @Date 2019-05-04 20:31*/public static List<Map<String, Object>> getTableFields(DatabaseInfo dbInfo, String tableName) {ArrayList<Map<String, Object>> list = new ArrayList<>();try {Class.forName(dbInfo.getJdbcDriver());Connection conn = DriverManager.getConnection(dbInfo.getJdbcUrl(), dbInfo.getUserName(), dbInfo.getPassword());String jdbcUrl = dbInfo.getJdbcUrl();/*****************************Oracle數(shù)據(jù)源獨(dú)有部分 Start******************************/String dbName = dbInfo.getUserName();String db = dbName.toUpperCase();String tb = tableName.toUpperCase();PreparedStatement preparedStatement = conn.prepareStatement("SELECT T.COLUMN_NAME as columnName,S.COMMENTS as columnComment FROM SYS.dba_TAB_columns T,SYS.ALL_COL_COMMENTS S WHERE T.TABLE_NAME = S.TABLE_NAME AND T.COLUMN_NAME = S.COLUMN_NAME AND T.OWNER = '" + db + "' AND T.TABLE_NAME = '" + tb + "'");/*****************************Oracle數(shù)據(jù)源獨(dú)有部分 End******************************/ResultSet resultSet = preparedStatement.executeQuery();while (resultSet.next()) {HashMap<String, Object> map = new HashMap<>();String columnName = resultSet.getString("columnName");String columnComment = resultSet.getString("columnComment");map.put("columnName", columnName);map.put("columnComment", columnComment);list.add(map);}return list;} catch (Exception ex) {log.error("執(zhí)行sql出現(xiàn)問(wèn)題！", ex);return null;}}}

總結(jié)

以上是生活随笔為你收集整理的Mysql和Oracle 数据库操作工具类的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。