元数据,是指由一个Connection对象的情况下,分析数据库的所有信息,例如:数据库版本号、数据库有多少个数据库等等。
DatabaseMetaData: 数据库的信息
ResultSetMetaData: 说明数据结果集的信息
1、DataBaseMetaDataClass.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.personsSystem.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、ResultSetMetaDataClass.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();}}}
看着它洗涤一缕缕阳光,看着它映衬一片片星辉,