Java+RestltData获取数据库、数据库表、表字段、执行sql等数据源操作
目录
1、数据源公共类封装
该类用于进行数据源的连接操作，作为一个公共类
GetDataSourceService ：
public class GetDataSourceService { public static DataSource getSource() { // 实际开发中，可以从数据库中获取数据源的配置项 DataSource dataSource = new DataSource(); dataSource.setId("123"); dataSource.setName("测试"); dataSource.setDatabaseName("template"); dataSource.setDatabaseType(DataBaseTypeConstant.MYSQL); dataSource.setAddress("127.0.0.1"); dataSource.setPort("3307"); dataSource.setUsername("root"); dataSource.setPassword("lhzlx"); return dataSource; }}JdbcSourcePoolServer：
public class JdbcSourcePoolServer { / * 在SpringBoot项目中，将数据连接池存在线程中，避免没有重新连接 * 可以打断点进行测试，会发现只有执行‘testConn’方法时需要重新进行连接，其他几个方法都是直接在线程的connPol中获取连接 */ private static ConcurrentHashMap<String, HikariDataSource> connPol = new ConcurrentHashMap<>(); / * 配置数据池连接 * * @param sourceId * @return */ public static Connection getConnection(String sourceId) { //创建连接池 Connection conn = null; HikariDataSource ds = null; try { sourceId = sourceId == null ? "" : sourceId; //查询当前数据源是否存在连接 if (connPol.get(sourceId) != null) { conn = connPol.get(sourceId).getConnection(); } else { //基本参数4个 DataSource source = GetDataSourceService.getSource(); //获取DruidDataSource配置 ds = JDBCUtil.getDruidSource(source); //获取连接 conn = ds.getConnection(); //将数据源连接池存入Map中 connPol.put(source.getId(), ds); } if (conn == null) { throw new Exception(); } return conn; } catch (Exception e) { JDBCUtil.closeDataSource(ds); e.printStackTrace(); return null; } } / * SQL查询 * * @param sourceId * @param sql * @param limit * @return */ public static SqlResult searchSql(String sourceId, String sql, int limit) { //默认预览条数 sql = sql + " limit " + limit; //存放字段名称 List<String> cellHeader = null; //存放字段类型 Map<String, String> colType = null; //存放字段值集合 List<Object> cellList = new ArrayList<>(); try { //获取链接 Connection conn = getConnection(sourceId); //执行sql PreparedStatement pstmt = conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); ResultSetMetaData metaData = rs.getMetaData(); //遍历结果集 while (rs.next()) { cellHeader = new ArrayList<>(); colType = new HashMap<>(); Map<Object, Object> valueMap = new HashMap<>(); //rs的下标只能从1开始 for (int i = 1; i <= metaData.getColumnCount(); i++) { //获取字段名称,只获取一次 cellHeader.add(metaData.getColumnName(i)); //获取当前字段的类型 metaData.getColumnTypeName(i); colType.put(metaData.getColumnName(i), metaData.getColumnTypeName(i)); //获取值 Object value = rs.getObject(i) == null ? "" : rs.getObject(i); valueMap.put(i, value); } cellList.add(valueMap); } JDBCUtil.closeConnection(conn); rs.close(); //封装数据返回给前端 SqlResult resultVo = new SqlResult(); resultVo.setCellHeader(cellHeader); resultVo.setCellList(cellList); resultVo.setColType(colType); return resultVo; } catch (Exception e) { e.printStackTrace(); return null; } } / * 关闭对应数据源的连接池 * * @param sourceId */ public static void destroyDataSource(String sourceId) { HikariDataSource dataSource = connPol.get(sourceId); if (dataSource != null) { connPol.remove(sourceId); JDBCUtil.closeDataSource(dataSource); } }}2、连接数据源
private static void testConn() { DataSource source = GetDataSourceService.getSource(); Connection connection = JdbcSourcePoolServer.getConnection(source.getId()); if (connection != null) { System.out.println("----- 测试连接成功 -----"); } }3、获取数据列表
private static void getCatalogs() { DataSource source = GetDataSourceService.getSource(); try { List<String> catList = new ArrayList<>(); Connection conn = JdbcSourcePoolServer.getConnection(sourceId); if (conn == null) { System.out.println("----- 连接失败 -----"); return; } ResultSet rs = conn.getMetaData().getCatalogs(); while (rs.next()) { catList.add(rs.getString("TABLE_CAT")); } for (String cat : catList) { System.out.println("-----数据库名称：" + cat + " -----"); } JDBCUtil.closeConnection(conn); } catch (Exception e) { e.printStackTrace(); } }4、获取指定数据库中的表
private static List<TableInfoVo> getTables() { DataSource source = GetDataSourceService.getSource(); try { Connection conn = JdbcSourcePoolServer.getConnection(sourceId); if (conn == null) { System.out.println("----- 连接失败 -----"); return null; } List<TableInfoVo> tabList = new ArrayList<>(); String[] types = new String[]{"TABLE", "VIEW"}; //也可以指定要查询表的数据库名称 ResultSet rs = conn.getMetaData().getTables(conn.getCatalog(), conn.getSchema(), "%", types); JDBCUtil.closeConnection(conn); System.out.println("----- 表数据： -----"); while (rs.next()) { TableInfoVo infoVo = new TableInfoVo(); String tableName = rs.getString("TABLE_NAME"); String desc = rs.getString("REMARKS"); infoVo.setName(tableName); infoVo.setComment(desc); tabList.add(infoVo); System.out.println(infoVo); } JDBCUtil.closeConnection(conn); return tabList; } catch (Exception e) { e.printStackTrace(); return null; } }
5、获取指定表中字段信息
private static void getColumns() { String tabName = "sys_account"; DataSource source = GetDataSourceService.getSource(); try { Connection conn = JdbcSourcePoolServer.getConnection(sourceId); if (conn == null) { System.out.println("----- 连接失败 -----"); return; } List<TableInfoVo> colList = new ArrayList<>(); ResultSet rs = conn.getMetaData().getColumns(conn.getCatalog(), conn.getSchema(), tabName, "%"); JDBCUtil.closeConnection(conn); System.out.println("----- 字段数据： -----"); while (rs.next()) { TableInfoVo infoVo = new TableInfoVo(); String columnName = rs.getString("COLUMN_NAME"); String typeName = rs.getString("TYPE_NAME"); String desc = rs.getString("REMARKS"); infoVo.setName(columnName); infoVo.setComment(desc); infoVo.setColumnType(typeName); colList.add(infoVo); System.out.println(infoVo); } JDBCUtil.closeConnection(conn); } catch (Exception e) { e.printStackTrace(); } }
6、执行SQL语句
private static void searchSql() {String sql= "select * from sys_account";int limit = 10; DataSource source = GetDataSourceService.getSource(); SqlResult result = JdbcSourcePoolServer.searchSql(sourceId, sql, ); System.out.println("----- SQL预览： -----"); System.out.println(result); }7、源码下载
《Demo传送门》
