DatabaseMetaData和ResultSetMeta的使用
元数据,是指由一个Connection对象的情况下,分析数据库的所有信息,例如:数据库版本号、数据库有多少个数据库等等。
DatabaseMetaData: 数据库的信息
ResultSetMetaData: 说明数据结果集的信息
1、DataBaseMetaData
Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql:///contacts"; String username = "root"; String password = "123456"; Connection conn = null; try { conn = DriverManager.getConnection(url, username, password); DatabaseMetaData dbmd = conn.getMetaData(); System.out.println("数据库名和表名的分隔符:" + dbmd.getCatalogSeparator());//例如xxx.persons System.out.println("数据库主版本号:" + dbmd.getDatabaseMajorVersion()); System.out.println("数据库次版本号:" + dbmd.getDatabaseMinorVersion()); System.out.println("数据库是什么数据库:" + dbmd.getDatabaseProductName()); System.out.println("数据库版本:" + dbmd.getDatabaseProductVersion()); System.out.println("默认事务级别:" + dbmd.getDefaultTransactionIsolation()); System.out.println("SQL关键字:" + dbmd.getSQLKeywords()); System.out.println("该数据库有如下几个数据库:"); ResultSet rs = dbmd.getCatalogs(); while(rs.next()){ String name = rs.getString("TABLE_CAT"); System.out.print(name + "\t"); } System.out.println(); /* * DatabaseMetaData提供了很多获取与数据库相关的信息, * 关于更多的信息,可以查阅相关api */ } catch (Exception e) { e.printStackTrace(); }finally{ if(conn != null){ conn.close(); } }
2、ResultSetMetaData
Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql:///contacts"; String username = "root"; String password = "123456"; Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ conn = DriverManager.getConnection(url, username, password); String sql = "select * from contacts"; st = conn.prepareStatement(sql); rs = st.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); //列数 int columnCount = rsmd.getColumnCount(); for(int i = 1; i <= columnCount; i++){ //列名字 String columnName = rsmd.getColumnName(i); //列的类型 String columnTypeName = rsmd.getColumnTypeName(i); //列的长度 int precision = rsmd.getPrecision(i); //列对应的java类型 String columnClassName = rsmd.getColumnClassName(i); /* *还可以获取一些其他的信息..... */ System.out.println(columnName); System.out.println(columnTypeName); System.out.println(precision); System.out.println(columnClassName); } System.out.println("------------------------------"); while(rs.next()){ for(int i = 1; i <= columnCount; i++){ String columnName = rsmd.getColumnName(i); String value = rs.getString(columnName); System.out.println(columnName + "=" + value); } } }catch(Exception e){ e.printStackTrace(); }finally{ if(conn != null){ conn.close(); } }
练习:将某个数据的所有表导到excel
首先需要添加相应的jar包,使用poi操作excel
Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql:///contacts"; String username = "root"; String password = "123456"; Connection conn = null; PreparedStatement st = null; ResultSet rs = null; try{ String dbName = "contacts"; HSSFWorkbook workbook = new HSSFWorkbook(); conn = DriverManager.getConnection(url, username, password); DatabaseMetaData dbmd = conn.getMetaData(); rs = dbmd.getTables(dbName, dbName, null, new String[]{"TABLE"}); List<String> tables = new ArrayList<String>(); while(rs.next()){ String tableName = rs.getString("TABLE_NAME"); tables.add(tableName); } for(String tableName : tables){ HSSFSheet sheet = workbook.createSheet(tableName); String sql = "SELECT * FROM " + dbName + "." + tableName; st = conn.prepareStatement(sql); rs = st.executeQuery(); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); HSSFRow row = sheet.createRow(0); for(int i = 1; i <= columnCount; i++){ String columnName = rsmd.getColumnName(i); HSSFCell cell = row.createCell(i - 1); cell.setCellValue(columnName); } int index = 1; while(rs.next()){ row = sheet.createRow(index++); for(int i = 1; i <= columnCount; i++){ HSSFCell cell = row.createCell(i - 1); String columnName = rsmd.getColumnName(i); Object value = rs.getObject(columnName); cell.setCellValue(value.toString()); } } } workbook.write(new FileOutputStream("c:\\"+ dbName + ".xls") ); }catch(Exception e){ e.printStackTrace(); }finally{ if(conn != null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。