MySQL---Database from the start to the Great God Series (11)-java Gets the metadata for the database/result set, writes the data table to the Excel table

Meta-Information for the database:

First, let's introduce the meta-information of the database (metadata):

Metadata (Metadata) is data about the data.

Metadata is the data that describes the structure of data in a data warehouse and how to build it.

What types of data are stored, what drives, etc., the data that describes the data is metadata!

Get ready:

 PackageCn.hncu.pool3;ImportJava.lang.reflect.InvocationHandler;ImportJava.lang.reflect.Method;ImportJava.lang.reflect.Proxy;ImportJava.sql.Connection;ImportJava.sql.DriverManager;ImportJava.util.ArrayList;ImportJava.util.List;ImportJava.util.Properties; Public  class connsutil {    Private StaticList<connection> pool =NewArraylist<connection> ();Private Static Final intnum=3;Static{Try{//Read configuration fileProperties p =NewProperties (); P.load (ConnsUtil.class.getClassLoader (). getResourceAsStream ("Jdbc.properties")); String Driver = P.getproperty ("Driver"); String URL = p.getproperty ("url"); String user = P.getproperty ("username"); String Password = p.getproperty ("Password"); Class.forName (driver); for(intI=0; i<num;i++) {FinalConnection conn = drivermanager.getconnection (URL, user, password);//Only need to change here!                 //Using the dynamic Proxy Proxy Conn object to intercept the Close methodObject obj = proxy.newproxyinstance (ConnsUtil.class.getClassLoader (), Conn. GetClass (). Getinterfaces (),NewInvocationhandler () {@Override                             PublicObjectInvoke(Object Proxy, Method method, object[] args)throwsThrowable {if(Method.getname (). Equalsignorecase ("Close") && (args==NULL|| args.length==0) {Pool.add (Connection) proxy);return NULL; }Else{returnMethod.invoke (conn, args);                }                            }                        });            Pool.add ((Connection) obj); }        }Catch(Exception e)        {E.printstacktrace (); }    } Public Static synchronizedConnectionGetconn()throwsexception{if(Pool.size () <=0) {Thread.Sleep ( -);returnGetconn (); }returnPool.remove (0); }}

Using the interface DatabaseMetaData in java.sql can be implemented as follows:

Demo class:

Package CN. Hncu. Meta;Import Java. SQL. Connection;Import Java. SQL. DatabaseMetaData;Import Java. SQL. ResultSet;Import Java. SQL. ResultSetMetaData;Import Java. SQL. Statement;import org. JUnit. Test;Import CN. Hncu. Pool3. Connsutil;public class Metadatademo {//※ meta-information1: Get DatabaseMetaData (database meta information)---Database connection information, database name, table name @Test public void Databasemetadatademo () throws Exception { Get meta information for a database Connection con = connsutil. Getconn();Key DatabaseMetaData dm = con. GetMetaData();Gets the name of this JDBC driver. System. out. println(DM. Getdrivername());Gets the major version number of this JDBC driver. System. out. println(DM. Getdrivermajorversion());Gets the maximum number of active statements that can be open at the same time in this database. --The return result of zero means that no limit or limit is unknown to the System. out. println(DM. Getmaxstatements());Gets the main JDBC version number for this driver. System. out. println(DM. Getjdbcmajorversion());There are many ways to go to the API to check the System. out. println("=========================");Here is the dynamic get database name ResultSet rs = DM. GetCatalogs();Equivalent to execution: show databases;while (RS. Next()) {System. out. println(RS. getString(1));Perform metadata operations get database name}//know the name of the database con. Createstatement(). Execute("Use Hncu");Dynamically get table name//can write parameter null as "%o%" for fuzzy query ResultSet rs2 = DM. Gettables("Hncu","Hncu", NULL, new string[] {"TABLE"});while (rs2. Next()) {System. out. println(rs2. getString("table_name"));Meta data operation, get table name}}//※ meta-information2: ResultSetMetaData (Result set meta information) from RS---header (per field name), Number of table rows, number of columns//the table header and table contents are queried in case of knowledge of the database name and table name. @Test//stands at the height of the result set---that is, table public void Resultsetmetadatademo () throws exception{Connection Con1 = Connsutil. Getconn();StatementSt= Con1. Createstatement();If it is a cross-Library query, sql: "Database name. Table name"----SELECT * from database. Table name String sql ="SELECT * from Stud";//Our connection is HNCU database, Access HNCU database directly write table name canResultSet rs =St. ExecuteQuery(SQL);Meta-information for result set ResultSetMetaData RSMD = rs. GetMetaData();int columns = RSMD. getColumnCount();Get the number of columns in the table//Output entire data table (including header)//header for (int i=0; i<columns;i++) {String ColumnName = Rsmd. getColumnName(i+1);System. out. Print(columnname+"\ T");} System. out. println();System. out. println("------------------------");Table Content while (RS. Next()) {for (int i=0; i<columns;i++) {String content = rs. getString(i+1);System. out. Print(content+"\ T");} System. out. println();}    }}

getTables(StringStringStringString""null则表示该类别名称不应该用于缩小搜索范围 schemaPattern - 模式名称的模式：它必须与存储在数据库中的模式名称匹配；该参数为""null 则表示该模式名称不应该用于缩小搜索范围tableNamePattern -表名称模式：它必须与存储在数据库中的表名称匹配 types - 要包括的表类型所组成的列表，必须取自从 getTableTypes()返回的表类型列表；null 表示返回所有类型

So it's going to go through.

Writing a data table to an Excel table

First, you need to prepare an Apache jar:

Link:
Https://github.com/chenhaoxiang/Java

Start by creating a simple data:

Package CN. Hncu. Meta;Import Java. IO. FileOutputStream;Import Java. IO. IOException;import org. Apache. Poi. HSSF. Usermodel. Hssfcell;import org. Apache. Poi. HSSF. Usermodel. Hssfrow;import org. Apache. Poi. HSSF. Usermodel. Hssfsheet;import org. Apache. Poi. HSSF. Usermodel. Hssfworkbook;import org. JUnit. Test;public class Exportxls {@Test public void Mkxlsdemo () throws ioexception{//requirements: Create a workbook: a. xls, Worksheet: Table1The4Line No.5Write text in cell of column: Hunan City Institute Hssfworkbook book = New Hssfworkbook ();Hssfsheet sheet = Book. Createsheet("Table I");Hssfrow ROW4 = Sheet. CreateRow(3);//number of rows is subscript plus 1The parameter value of the method is from0The starting---the sequence number in the real table is from1Start marking Hssfcell cell5 = row4. Createcell(4);FileOutputStream fout = new FileOutputStream ("A.xls");Book. Write(Fout);}}

Writes all table data for a database to an Excel table

@Test     Public void exporttest()throwsexception{//Here we only traverse the storage Hncu databaseString dbname="Hncu"; String xlsfilename="B.xls";    Exportdb2xls (Dbname,xlsfilename); } Public void Exportdb2xls(String dbname,string Xlsfilename)throwsexception{Hssfworkbook book =NewHssfworkbook ();        Connection con = connsutil.getconn (); DatabaseMetaData dm = Con.getmetadata ();//When writing code, try to avoid the result set socket operation and perform other result set operations within one result set operation        //If there is a transaction, a fallback or commit of one result set may spread to anotherResultSet rs = dm.gettables (DbName, DbName,NULL,Newstring[]{"TABLE"}); list<string> tables =NewArraylist<string> (); while(Rs.next ()) {String TableName = rs.getstring ("table_name");        Tables.add (TableName); } Statement st = Con.createstatement (); for(String tablename:tables) {//create a table with a table named TableNameHssfsheet sheet = book.createsheet (tableName);//This write, can be accessed across databasesString sql ="SELECT * from"+dbname+"."+tablename; rs = st.executequery (SQL);//The table header is traversed and written into the XLS fileHssfrow row = Sheet.createrow (0);//table header rowResultSetMetaData RSMD = Rs.getmetadata ();intColnum = Rsmd.getcolumncount (); for(intI=0; i<colnum;i++) {Hssfcell cell = Row.createcell (i); String colname = Rsmd.getcolumnname (i+1);            Cell.setcellvalue (colname); }//All data rows            intIDX =1; while(Rs.next ()) {row = Sheet.createrow (idx++); for(intI=0; i<colnum;i++) {Hssfcell cell = Row.createcell (i); Cell.setcellvalue (Rs.getstring (i+1) ); }}} FileOutputStream Fout =NewFileOutputStream (Xlsfilename);    Book.write (Fout); }

MySQL---Database from the start to the Great God Series (11)-java Gets the metadata for the database/result set, writes the data table to the Excel table