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

Source: Internet
Author: User
Tags set socket

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.