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();
				}
			}
		}

技术分享


郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。