元数据,是指由一个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();
}
}
}