Use of DatabaseMetaData and Resultsetmeta

metadata, which is the case of a Connection object, analyzes all the information about the database, such as the database version number, the number of databases, and so on.

DatabaseMetaData: Database Information

ResultSetMetaData: Description of data result set information

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 ("Database name and table name delimiter:" + dbmd.getcatalogseparator ());//For example xxx.persons System.out.println ("database major version number:" + Dbmd.getdatabasemajorversion ()); System.out.println ("Database minor version number:" + dbmd.getdatabaseminorversion ()); SYSTEM.OUT.PRINTLN ("Database is what database:" + dbmd.getdatabaseproductname ()); SYSTEM.OUT.PRINTLN ("database version:" + dbmd.getdatabaseproductversion ()); SYSTEM.OUT.PRINTLN ("Default transaction level:" + dbmd.getdefaulttransactionisolation ()); SYSTEM.OUT.PRINTLN ("SQL keyword:" + dbmd.getsqlkeywords ()); SYSTEM.OUT.PRINTLN ("The database has the following databases:"); ResultSet rs = Dbmd.getcatalogs (); while (Rs.next ()) {String name = rs.getstring ("Table_cat"); System.out.print (name + "\ T");  } System.out.println (); /* * DatabaseMetaData provides a lot of access to database-related information, * For more information, you can check the relevant 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 ();//column int columnCount = Rsmd.getcolumncount (); for (int i = 1; I <= columnCount; i++) {//Column name string columnName = Rsmd.getcolumnname (i);//column type string columntypename = Rsmd.getcolumntypename (i);// The length of the column int precision = rsmd.getprecision (i);//column corresponding Java type string columnclassname = Rsmd.getcolumnclassname (i);/* * You can also get some other information ...... */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 ();}}

Exercise: Guide All the tables of a data into Excel

You first need to add the appropriate jar package, use the POI to manipulate 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);D Atabasemetadata 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 ();}}}

Use of DatabaseMetaData and Resultsetmeta