工具类
配置类 (主要是加载一些数据库信息)
@Configuration
public class MySqlConfig {
@Value("${spring.datasource.driver-class-name}")
public String driver;
@Value("${spring.datasource.url}")
public String url;
@Value("${spring.datasource.username}")
public String username;
@Value("${spring.datasource.password}")
public String password;
@Bean("connectionTwo")
public Connection getConnection() throws SQLException, ClassNotFoundException {
Properties props = new Properties();
props.put("remarksReporting","true");//获取数据库的备注信息
props.put("user",username);
props.put("password",password);
//1.获取连接
Class.forName(driver);//注册驱动
return DriverManager.getConnection(url,props);
}
}
工具类
import org.springframework.stereotype.Component;
import javax.annotation.Resource;
import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
/**
* @Author gsz
* @Date 2023/9/11 下午 4:28
* explain: 获取数据库元数据信息(查询所有的库,查询所有的表,查询指定的库中指定的表的所有字段信息, 包括注释)
*/
@Component
public class JdbcUtils {
@Resource(name = "connectionTwo")
private Connection connection;
/**
* 获取数据库基本信息
* @throws Exception
*/
public void getMetaData() {
try {
//2.获取元数据
DatabaseMetaData metaData = connection.getMetaData();
//3.获取数据库基本信息
System.out.println(metaData.getUserName());
System.out.println(metaData.supportsTransactions());//是否支持事务
System.out.println(metaData.getDatabaseProductName());
} catch (SQLException e) {
e.printStackTrace();
}
}
/**
* 返回数据库中所有的表名
* @throws SQLException
*/
public List<String> getDatasourceAllTableNames() throws SQLException {
//1.获取元数据
DatabaseMetaData metaData = connection.getMetaData();
//2.获取所有数据库列表
ResultSet rs = metaData.getCatalogs();
LinkedList<String> tableNames = new LinkedList<>();
while (rs.next()) {
// System.out.println(rs.getString(1));
tableNames.add(rs.getString(1));
}
rs.close();
connection.close();
return tableNames;
}
/**
* 获取指定表中的字段信息
* @throws Exception
*/
public List<String> getTableColumns(String tableName) throws Exception {
DatabaseMetaData metaData = connection.getMetaData();
/**
* 获取表中的字段信息 不包含注释信息
* catalog
* schema
* tableName
* columnName
*/
ResultSet rs = metaData.getColumns(null, null, tableName, null);
LinkedList<String> fieldNames = new LinkedList<>();
while (rs.next()) {
fieldNames.add(rs.getString("COLUMN_NAME"));
System.out.println("字段类型: " + rs.getString("TYPE_NAME"));
}
rs.close();
connection.close();
return fieldNames;
}
/**
* 查询指定的库中指定的表的表信息结构 包含注释
* @throws SQLException
*/
public LinkedList<Map<String, Object>> getTableColumns(String database,String tableName) throws SQLException {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT COLUMN_NAME, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" + "excel_demo" + "' AND TABLE_NAME = '" + "supermarket" + "'");
LinkedList<Map<String, Object>> fieldNames = new LinkedList<>();
while (rs.next()) {
String columnName = rs.getString("COLUMN_NAME"); // 获取字段信息;
String columnComment = rs.getString("COLUMN_COMMENT"); // 获取注释信息;
HashMap<String, Object> map = new HashMap<>();
map.put("column", columnName);
map.put("columnComment", columnComment);
fieldNames.add(map);
}
rs.close();
connection.close();
return fieldNames;
}
}
网上也是参考了很多文章,当初想的是想要读取指定库下所有的表名,后来没有找到合适的方案,大家如果有更好的建议,请留言.