本篇博客知识点
1. 如何通过元数据拿到数据库的信息?
2. 如何用 Java 生成 Excel 表?
3. 将数据库中的表导出生成 Excel 案例
元数据:描述数据的数据
Java 中使用元数据的两个方法
DatabaseMetaData 的使用学习
- @Test// DatabaseMetaData 通过连接可以拿到的信息:数据库软件,所有数据库名,所有数据库里面的表名
- public void DatabaseMetaData_Demo() throws Exception{
- // 自己写的工具包来获得数据库连接
- Connection con = ConnUtils4.getConn();
- //DatabaseMetaData 通过连接获得
- DatabaseMetaData dbmt = con.getMetaData();
- // 数据库软件名
- System.out.println(" 数据库软件名:"+dbmt.getDatabaseProductName());
- // 拿到所有数据库名字
- ResultSet rs =dbmt.getCatalogs();
- List<String> tablenames = new ArrayList<String>();
- while(rs.next()){
- String tabname=rs.getString("TABLE_CAT");
- tablenames.add(tabname);
- System.out.println("数据库名字:"+tabname);
- }
- System.out.println("--------------");;
- //拿到某个数据库李曼所有的表名---可以指定表的类型
- rs = dbmt.getTables("ake", "ake", null, new String[]{"TABLE","VIEW"});
- while(rs.next()){
- System.out.println("数据库ake里的表名:"+rs.getString("TABL`````
- _NAME"));
- }
- }
ResultSetMetaData 的使用学习
- @Test // ResultSetMetaData 拿到的表结构信息:获得表的列数目 类型和属性
- public void ResultSetMetaData_Demo2() throws Exception {
- // 自己写的工具包来获得数据库连接
- Connection con = ConnUtils4.getConn();
- String sql = "select * from ake.book";
- Statement st = con.createStatement();
- ResultSet rs = st.executeQuery(sql);
- // ResultSetMetaData 通过 查询的返回集获取
- ResultSetMetaData rsmt = rs.getMetaData();
- //获得表的列数
- int n = rsmt.getColumnCount();
- //类型---某一列
- // getColumnTypeName:INT
- //某医疗的名字
- // getColumnName:id
- //某一列的长度
- // getColumnDisplaySize:11
- for (int i = 1; i < n; i++) {
- System.out.println(rsmt.getTableName(i) + "表的第" + i + "列描述信息");
- System.out.println("getColumnDisplaySize:" + rsmt.getColumnDisplaySize(i));
- System.out.println("getColumnLabel:" + rsmt.getColumnLabel(i));
- System.out.println("getColumnName:" + rsmt.getColumnName(i));
- System.out.println("getColumnType:" + rsmt.getColumnType(i));
- System.out.println("getColumnTypeName:" + rsmt.getColumnTypeName(i));
- System.out.println("getPrecision:" + rsmt.getPrecision(i));
- System.out.println("getScale:" + rsmt.getScale(i));
- System.out.println("getSchemaName:" + rsmt.getSchemaName(i));
- System.out.println("------------");
- }
- con.close();
- }
拿出 ake 表里面所有的内容~~~~
- // 拿出ake表里面所有的内容~~~~
- public static void main(String[] args) throws Exception {
- Connection con = ConnUtils4.getConn();
- System.out.println(con);
- DatabaseMetaData dbmt = con.getMetaData();
- //拿到所有的ake所有表名
- ResultSet rs = dbmt.getTables("ake", "ake", null, new String[] {
- "TABLE",
- "VIEW"
- });
- List < String > tablenames = new ArrayList < String > ();
- while (rs.next()) {
- String tablename = rs.getString("TABLE_NAME");
- tablenames.add(tablename);
- }
- for (String tablename: tablenames) {
- System.out.println(tablename + "表:");
- if (tablename.equals("img")) {
- continue;
- }
- String sql = "select * from ake." + tablename;
- Statement st = con.createStatement();
- ResultSet RS = st.executeQuery(sql);
- ResultSetMetaData rsmt = RS.getMetaData();
- // 拿到列数
- int colnums = rsmt.getColumnCount();
- for (int i = 1; i <= colnums; i++) {
- //拿到表头信息
- String colName = rsmt.getColumnName(i);
- System.out.print(colName + "\t");
- }
- System.out.println();
- while (RS.next()) {
- for (int i = 1; i <= colnums; i++) {
- //拿到表信息
- System.out.print(RS.getString(i) + "\t");
- }
- System.out.println();
- }
- }
- con.close();
- }
我把那到的表格信息输出
需要一个插件工具包
- @Test public void Workbook_demo() throws Exception {
- // 建立一个工作表--相当于一个数据库
- Workbook book = new HSSFWorkbook();
- // 数据库中的一个表
- Sheet sheet1 = book.createSheet("表1");
- // 行
- Row row = sheet1.createRow(4);
- // 单元格
- Cell cell = row.createCell(3);
- // 写入数据
- cell.setCellValue("通过java写的Excel");
- // 保存到银盘
- book.write(new FileOutputStream("d:a/a.xls"));
- }
- public static void main(String[] args) throws Exception {
- //把数据库里所有的信息导入到Excel表中~
- Connection con = ConnUtils4.getConn();
- DatabaseMetaData dbmt = con.getMetaData();
- //要通过 DatabaseMetaData 拿到所有数据库的名字
- List < String > Database_Names = new ArrayList < String > ();
- ResultSet rs = dbmt.getCatalogs();
- while (rs.next()) {
- Database_Names.add(rs.getString("TABLE_CAT"));
- }
- //DatabaseMetaData 拿到所有数据表名
- int m = 0;
- for (String Database_Name: Database_Names) {
- if (!Database_Name.equals("ake")) {
- continue;
- }
- // if(m++>=3){
- // break;
- // }
- // 一个数据库对于一个 Excel文档~
- Workbook book = new HSSFWorkbook();
- rs = dbmt.getTables(Database_Name, Database_Name, null, new String[] {
- "TABLE",
- "VIEW"
- });
- //封装所有表名
- List < String > Table_Names = new ArrayList < String > ();
- while (rs.next()) {
- Table_Names.add(rs.getString("TABLE_NAME"));
- }
- for (String Table_Name: Table_Names) {
- if ("img".equals(Table_Name) || "note".equals(Table_Name)) { // img为二进制文件导入会出错
- continue;
- }
- //创建一个表
- Sheet sheet = book.createSheet(Table_Name);
- Statement st = con.createStatement();
- String sql = "select * from " + Database_Name + "." + Table_Name;
- // System.out.println("sql:"+sql);
- rs = st.executeQuery(sql);
- //设置表头信息
- Row row1 = sheet.createRow(0);
- ResultSetMetaData rsmd = rs.getMetaData();
- int colnum = rsmd.getColumnCount();
- for (int i = 1; i <= colnum; i++) {
- String name = rsmd.getColumnName(i);
- Cell cell = row1.createCell(i - 1);
- cell.setCellValue(name);
- // System.out.print(name+"\t");
- }
- // System.out.println();
- //设置表格信息
- int idx = 1;
- while (rs.next()) {
- Row row = sheet.createRow(idx++);
- for (int i = 1; i <= colnum; i++) {
- String str = rs.getString(i);
- // System.out.print(str+"\t");
- Cell cell = row.createCell(i - 1);
- cell.setCellValue(str);
- }
- // System.out.println();
- }
- }
- book.write(new FileOutputStream("d:a/" + Database_Name + ".xls"));
- }
- }
执行结果就成功啦!
来源: http://blog.csdn.net/weixin_37720904/article/details/76542590