MySQL---当Java遇上MySQL⑧---数据库元数据、生成Excel文件

玩转数据库元数据主要有两个类：

     *    java.sql.DatabaseMetaData 和 java.sql.ResultSetMetaData
     *    DatabaseMetaData：可以获得驱动信息、所有数据库名，所有表格名(视图、存储过程等)
     *    ResultSetMetaData：可以获得查询结果的表结构，如列(字段)名、字段类型等
     * 获取：
     *    DatabaseMetaData dbmd = con.getMetaData(); //con 是Connection类对象
     *    ResultSetMetaData rsmd = rs.getMetaData(); //rs 是ResultSet类对象

代码

package cn.hncu.dbMate;import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;import org.junit.Test;import cn.hncu.utils.MyConnPool3;/*** 演示：获取并使用数据库元数据* CreateTime: 2018年9月21日 上午7:56:53	* @author 宋进宇  Email:447441478@qq.com*/
public class DatabaseMateDemo {/* 玩转数据库元数据主要有两个类：*    java.sql.DatabaseMetaData 和 java.sql.ResultSetMetaData*    DatabaseMetaData：可以获得驱动信息、所有数据库名，所有表格名(视图、存储过程等)*    ResultSetMetaData：可以获得查询结果的表结构，如列(字段)名、字段类型等* 获取：*    DatabaseMetaData dbmd = con.getMetaData(); //con 是Connection类对象*    ResultSetMetaData rsmd = rs.getMetaData(); //rs 是ResultSet类对象*/@Testpublic void hello() throws Exception {Connection con = MyConnPool3.getConnection();DatabaseMetaData dm = con.getMetaData();System.out.println(dm.getDriverName());System.out.println(dm.getDriverVersion());System.out.println(dm.getDriverMajorVersion());System.out.println(dm.getDriverMinorVersion());System.out.println(dm.getMaxStatements());System.out.println(dm.getJDBCMajorVersion());}@Testpublic void demo1() throws Exception {Connection con = MyConnPool3.getConnection();//技术入口：DatabaseMetaData dbmd = con.getMetaData();//获取所有数据库的信息ResultSet dbRs = dbmd.getCatalogs();while( dbRs.next() ) {//查询结果的表头为：TABLE_CATString dbName = dbRs.getString("TABLE_CAT");System.out.println(dbName);}System.out.println("---------------db----------------");//获取某个数据库中所有表信息ResultSet tbRs = dbmd.getTables("hncu", "hncu", null, new String[]{"TABLE"}); while( tbRs.next() ) {//查询结果的表头: TABLE_CAT TABLE_SCHEM TABLE_NAME 等，具体信息见APIString tbName = tbRs.getString("TABLE_NAME");System.out.println(tbName);}System.out.println("---------------tb----------------");//获取查询结果的表结构信息Statement st = con.createStatement();ResultSet rs = st.executeQuery("select * from hncu.person");//技术入口：ResultSetMetaData rsmd = rs.getMetaData();int cols = rsmd.getColumnCount(); //获取查询结果的列(字段)的个数//输出表头System.out.println("-------------------------------");for (int i = 0; i < cols ; i++) {//注意 getColumnLabel参数 从1开始String columnLabel = rsmd.getColumnLabel(i+1); //别名，在没有使用as的情况下同表结构字段名//String columnName = rsmd.getColumnName(i+1); //表结构字段名System.out.print(columnLabel+"\t");}System.out.println("\n-------------------------------");//输出表数据while(rs.next()) {for (int i = 0; i < cols ; i++) {//注意 getObject参数 从1开始Object obj = rs.getObject( i+1 );System.out.print(obj+"\t");}System.out.println("\n-------------------------------");}}//演示查看 getTables获得的查询结果的所有字段(列)@Testpublic void demo2() throws Exception {Connection con = MyConnPool3.getConnection();//技术入口：DatabaseMetaData dbmd = con.getMetaData();ResultSet tbsRs = dbmd.getTables("hncu", "hncu", null, new String[]{"TABLE"}); ResultSetMetaData tbMd = tbsRs.getMetaData();int cols = tbMd.getColumnCount();System.out.println( cols );for (int i = 0; i < cols; i++) {System.out.print( tbMd.getColumnLabel( i+1 ) +"\t" );}System.out.println("\n-----------------------------");while( tbsRs.next() ) {for (int i = 0; i < cols; i++) {System.out.print( tbsRs.getObject( i+1 ) +"\t" );}System.out.println();}}}

生成Excel文件

     * 技术入口：使用apache公司的poi工具包
     * 该插件可以生成许多office文件，如Excel、Word、PPT等
     * 而office文件又分两个版本：2007以前的是xls、doc、ppt等后缀名
     * 2007以后则是：xlsx、docx、pptx
     * xls使用 org.apache.poi.hssf.usermodel.HSSFWorkbook 类入口 
     * 使用poi-xxx.jar包
     * 
     * xlsx使用 org.apache.poi.xssf.usermodel.XSSFWorkbook 类入口 
     * 使用poi-xxx.jar、poi-ooxml-xxx.jar、poi-ooxml-schemas-xxx.jar 、xmlbeans-xxx.jar等包，不同版本的所需jar包不同。

生成.xls文件

技术入口：org.apache.poi.hssf.usermodel.HSSFWorkbook类，需要poi-xxx.jar包

	@Testpublic void simpleXlsDemo() throws Exception {//1 创建 XSSFWorkbook对象，就是一个Excel文件对象HSSFWorkbook workbook = new HSSFWorkbook();//2 创建表sheetHSSFSheet sheet = workbook.createSheet("测试");//3 创建一行, 0表示为第一行HSSFRow row = sheet.createRow(4);//4 创建单元格, 0表示为第一行HSSFCell cell = row.createCell(3);cell.setCellValue("湖南城市学院");//序列化workbook.write( new FileOutputStream("d:/a/test.xls"));}

生成.xlsx文件

技术入口：org.apache.poi.xssf.usermodel.XSSFWorkbook类，需要poi-xxx.jar、poi-ooxml-xxx.jar、poi-ooxml-schemas-xxx.jar 、xmlbeans-xxx.jar等包???????

	@Testpublic void simpleXlsxDemo() throws Exception {//1 创建 XSSFWorkbook对象，就是一个Excel文件对象XSSFWorkbook workBook = new XSSFWorkbook();//2 创建表sheetXSSFSheet sheet = workBook.createSheet("测试");//3 创建一行, 0表示为第一行XSSFRow row = sheet.createRow(4);//4 创建单元格, 0表示为第一行XSSFCell cell = row.createCell(3);cell.setCellValue("湖南城市学院");//序列化workBook.write( new FileOutputStream("d:/a/t.xlsx"));}

把一个数据库中的所有表数据以 .xlsx 文件导出

	@Testpublic void export() throws Exception {exportDB("hncu");}/*** 把dbName中的所有表数据导出* @param dbName 数据库名* @throws Exception */public void exportDB(String dbName) throws Exception {Connection con = MyConnPool3.getConnection();Statement st = con.createStatement();//获取数据库元数据对象DatabaseMetaData dbmd = con.getMetaData();//通过 dbmd 获取 dbName 中的所有表名ResultSet tables = dbmd.getTables(dbName, dbName, null, new String[]{"TABLE"});/* 下面这种做法逻辑上是没有问题的，* 但是 每个 ResultSet类对象 就相当于 与 mysql数据库进行通讯，* 如果一个 用户 发生了同时发生了两次通讯，即很有可能会发生通讯混乱。* 所以，严禁使用 两个 ResultSet类对象进行嵌套遍历！！！while( tables.next() ) {String tableName = tables.getString("TABLE_NAME");ResultSet resultSet = st.executeQuery( "select * from "+dbName+"."+tableName );while( resultSet.next() ) {//...}}*///正确做法//先把所有表名查出并存起来List<String> tableNames = new ArrayList<String>();while( tables.next() ) {String tableName = tables.getString("TABLE_NAME");tableNames.add(tableName);}//再依次查询每一个表//因为要把数据以Excel格式导出，所有要生成Excel类对象//1 生成Excel类对象XSSFWorkbook workbook = new XSSFWorkbook();//2 遍历tableNamesfor (String tableName : tableNames) {//3  每一张表对应 一个sheetXSSFSheet sheet = workbook.createSheet(tableName);//4 查询数据库表数据ResultSet resultSet = st.executeQuery( "select * from "+dbName+"."+tableName );//5 获取查询结果的表头ResultSetMetaData tbMd = resultSet.getMetaData();//6 获取查询结果总共有几列int cols = tbMd.getColumnCount();//7 把表头数据存储到Excel中int idx = 0;XSSFRow row = sheet.createRow(idx++);for (int i = 0; i < cols; i++) {//从tbMd获取表头信息//注意：java.sql包中大多数getxxx方法的int型参数都是 以 1 为起始String label = tbMd.getColumnLabel( i+1 ); //创建一个单元格XSSFCell cell = row.createCell(i);//给单元格存入数据cell.setCellValue(label);//打印 数据类型System.out.print(tbMd.getColumnTypeName(i+1)+" ");}System.out.println();//8 把表数据存储到Excel中while( resultSet.next() ) {row = sheet.createRow(idx++);for (int i = 0; i < cols; i++) {XSSFCell cell = row.createCell(i);Object data = null;if( tbMd.getColumnTypeName(i+1).contains("LOB") ) {data = "图片："+ tbMd.getColumnTypeName(i+1);}else {data = resultSet.getObject( i+1 );}cell.setCellValue( data.toString() );}}}//9 序列化---把Excel树存储到磁盘中workbook.write( new FileOutputStream("d:/a/"+dbName+".xlsx"));}

源码

源码链接