Get the table structure in the database through JDBC

1, JDBC in the metadata to obtain specific information about the table. You can query which tables are in the database, what fields are in the table, the properties of the fields, and so on. In metadata, this information is stored in the resultset and returned to the user through a series of getxxx functions. About metadata There are also many online, here I just from the perspective of my own learning to record the simple use of JDBC and get data table related information methods.

DatabaseMetaData Dbmd = Con.getmetadata (); rs = Dbmd.getcolumns (Con.getcatalog (), schema, tableName, NULL); Rs.getstring (data_type)//java.sql.Types the size of the SQL type rs.getstring (column_size)//column. For char or date types, the size of the column is the maximum number of characters, and for numeric and decimal types, the column size is precision. Rs.getstring (decimal_digits)//number of digits in the decimal part

2. Below is the code to get the table information under my JDBC. I am using MySQL 5.0 as the test platform. The following four steps can be implemented:

1. The code for the JDBC connection to MySQL is standard. Class.forName ("Com.mysql.jdbc.Driver"). newinstance (); Connection conn = drivermanager.getconnection ("jdbc:mysql://localhost/test?user=root&password=123456");//2. Here is the information for getting the table. M_dbmetadata = M_connection.getmetadata (); ResultSet Tableret = M_dbmetadata.gettables (null, "%", m_tablename,new string[]{"TABLE"}); */* where "%" means the meaning of *, that is, any meaning. Where M_tablename is the name of the data table to get, if you want to get all the names of the table, you can use "%" as a parameter. *///3. Extract the name of the table. while (Tableret.next) System.out.println (tableret.getstring ("table_name"));/* through getString ("table_name"), You can get the name of the table. As can be seen from here, JDBC is returning all of its results, in a table-like memory structure, in front of the Gettables interface, where the field of table_name is the name of each table. *///4. Extracts the names and types of fields within a table String columnName; String ColumnType; ResultSet Colret = M_dbmetadata.getcolumns (null, "%", m_tablename, "%"); 　　while (Colret.next ()) {columnName = colret.getstring ("column_name"); 　　ColumnType = colret.getstring ("type_name"); 　　int datasize = Colret.getint ("Column_size"); 　　int digits = Colret.getint ("Decimal_digits"); int nullable = Colret.GetInt ("NULLABLE"); System.out.println (columnname+ "" +columntype+ "" +datasize+ "" +digits+ "" + Nullable);} /*jdbc inside through the GetColumns interface, to achieve the query of the field. As with Gettables, "%" denotes all arbitrary (fields), and M_tablename is the name of the data table. GetColumns's return also puts all the fields into a similar in-memory table, and column_name is the name of the field, Type_name is the data type, such as "int", "int unsigned" and so on, Column_size returns an integer, Is the length of the field, such as the field of the defined int (8), the return is 8, the last nullable, the return 1 means that it can be null, and 0 means not NULL. */

Each column description has the following columns: table_cat string = Table category (nullable) Table_schem string = Table pattern (nullable) table_name string = table name Column_n AME string = column name Data_type int = The SQL type from java.sql.Types type_name String = The type name that the data source depends on, and for the UDT, the type name is fully qualified C olumn_size int = size of column. Buffer_length is not being used. decimal_digits int = number of digits in the decimal part. For data types that do not apply to Decimal_digits, Null is returned. Num_prec_radix int = = cardinality (usually 10 or 2) NULLABLE int = Whether NULL is allowed. Columnnonulls-null values may not be allowed columnnullable-explicitly allow null values Columnnullableunknown-do not know if you can use null REMARKS String =  Comment on the column (nullable) Column_def string = The default value of the column, which should be interpreted as a string (nullable) when it is within a single quotation mark Sql_data_type int = = Unused Sql_datetime_sub int = = does not use char_octet_length int = = for the CHAR type, the length is the maximum number of bytes in the column ordinal_position int = index of the column in the table (starting at 1) is_nullable Stri ng = ISO rules are used to determine whether a column includes null. Yes---If the parameter can include NULL no---If the parameter cannot include a null empty string---if you do not know if the parameter can include null Scope_catlog the category of the table, it is the scope of the reference property (if Data_ TYPE is not REF, or null) scope_schema String = tableSchema, which is the scope of the reference property (or null if data_type is not ref) scope_table the String = table name, which is the scope of the reference property (or null if data_type is not ref) Source_da Ta_type short = Source type for different types or user-generated ref types, SQL types from java.sql.Types (or null if data_type is not DISTINCT or user-generated ref) Is_autoin Crement String = Indicates whether this column automatically increases YES---if the column automatically increases no---if the column does not automatically increment the empty string---if it is not determined whether the column is automatically incremented by the parameter Column_size column represents the specified column size for the given column. For numeric data, this is the maximum precision. For character data, this is the character length. For datetime data types, this is the string representation of the length of the character (assuming the maximum allowable fractional seconds for the component's precision). For binary data, this is the byte length. For the ROWID data type, this is the byte length. Null is returned for data types that do not apply to the column size. Parameters: Catalog-category name; it must match the category name stored in the database; the parameter is "" to get those descriptions that do not have a category, or null to indicate that the category name should not be used to narrow the search scope Schemapattern-pattern name pattern It must match the schema name stored in the database, which means "" to get those descriptions that do not have a pattern, or null to indicate that the schema name should not be used to narrow the search Tablenamepattern-table name pattern  It must match the table name stored in the database with the Columnnamepattern-column name pattern; it must match the column name stored in the database

3. Get all Tables

String Catalog = Conn.getcatalog (); The catalog is actually the database name  ResultSet tablesresultset = dbmetadata.gettables (catalog,null,null,new string[]{"TABLE"});  while (Tablesresultset.next ()) {      String tableName = tablesresultset.getstring ("table_name");  }  

The following columns are in the Tablesresultset:

Table_cat string = Table category (nullable) Table_schem string = Table pattern (nullable) table_name string = table name Table_type string = The table type. Typical types are "table", "VIEW", "SYSTEM TABLE", "GLOBAL temporary", "LOCAL temporary", "ALIAS", and "synonym". REMARKS Explanatory notes of the String = Table Type_cat string = category (nullable) Type_schem string = Type mode (nullable) Type_name string => ; Type name (nullable) Self_referencing_col_name string = name of the specified "identifier" column with a type table (nullable) Ref_generation string = Specifies the SEL The way in which values are created in F_referencing_col_name. These values are "SYSTEM", "USER", and "DERIVED". (may be null)

4. Primary key of a table

String tableName = ...;  ResultSet Primarykeyresultset = Dbmetadata.getprimarykeys (catalog,null,tablename);  while (Primarykeyresultset.next ()) {      String primarykeycolumnname = primarykeyresultset.getstring ("column_name") ;  }  

Primaykeyresultset has the following columns: table_cat string = Table category (nullable) Table_schem string = Table pattern (nullable) table_name string = Table name column_name String = column name Key_seq the sequence number in the primary key (the value 1 represents the first column in the primary key, and the value 2 represents the second column in the primary key). Pk_name String = name of the primary key (can be null)

5. Foreign keys for a table

ResultSet Foreignkeyresultset = Dbmetadata.getimportedkeys (catalog,null,tablename);  while (Foreignkeyresultset.next ()) {      String fkcolumnname = foreignkeyresultset.getstring ("Fkcolumn_nam");      String pktablenname = foreignkeyresultset.getstring ("Pktable_name");      String pkcolumnname = foreignkeyresultset.getstring ("Pkcolumn_name");  }  

Foreignkeyresultset has the following columns: pktable_cat string = Import primary key table category (nullable) Pktable_schem string = Imported primary key table mode (can be NULL) Pktable_name string = Imported primary key table name Pkcolumn_name string = Imported primary key column name Fktable_cat string = Foreign key table category (Can be NULL) Fktable_schem string = Foreign key table mode (nullable) Fktable_name string = Foreign key table name Fkcolumn_name string = Foreign key column name Key_seq Shor T = serial number in the foreign key (the value 1 indicates the first column in the foreign key, and a value of 2 indicates the second column in the foreign key) update_rule short = changes in foreign keys when the primary key is updated delete_rule short = The foreign key changes when the primary key is deleted Pk_nam E string = Name of the primary key (nullable) Fk_name string = The name of the foreign Key (nullable) deferrability short = Whether the evaluation of foreign key constraints can be deferred to the submission time

6. Application:

Most databases have many primary keys, but the same primary key for two records is not allowed in one table with the same value. You can use Java Database Connectivity (JDBC) to determine the primary key of a data table. JDBC has powerful meta-data processing capabilities. The Java.sql.Connection class and the Java.sql.ResultSet class can be reflected by calling their GetMetaData method. There are two ways to do this:

Both of these methods can obtain the primary foreign key information, only the reference to different Metadata.getexportedkeys ("database name", "Schema", "table name"); metadata.getimportedkeys (catalog, NULL , TableName);

SQL Server private static String URL = "jdbc:sqlserver://192.168.1.220:1433; User=admin; Password=123456;databasename=person ";

Package Cn.test;import Java.io.file;import Java.io.fileoutputstream;import java.sql.connection;import Java.sql.databasemetadata;import Java.sql.drivermanager;import Java.sql.resultset;import Java.text.simpledateformat;import java.util.date;import Java.util.hashmap;import Java.util.Map;public class TestAll    {private static String path = "D:\\tool\\project\\dynamictable\\src\\cn\\test\\entity";    private static String Pkname = "Com.mysql.jdbc.Driver";    private static String URL = "Jdbc:mysql://192.168.1.220:3306/person";    private static string[] Classnames = new string[] {"shipstopping", "Arriveship", "Tblusertype"};    private static map<string, string> fktablenamesandpk = new hashmap<string, string> ();    public static void Main (string[] args) {test ();        public static void Test () {Connection conn = null;        DatabaseMetaData metaData = null;        ResultSet rs = null;        ResultSet CRS = NULL; try {CLASS.FORName ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");            conn = drivermanager.getconnection (URL, "admin", "123"); String Catalog = Conn.getcatalog ();            The catalog is actually the database name metaData = Conn.getmetadata ();            File Dirfile = new file (path);            if (!dirfile.exists ()) {dirfile.mkdirs ();            }//Get Table rs = metadata.gettables (null, "%", "%", new string[] {"Table"});                while (Rs.next ()) {String TableName = rs.getstring ("table_name");                String classname = getclassnamebytablename (tablename);                StringBuffer sb = new StringBuffer ();                StringBuffer sbpackage = new StringBuffer ();                Sbpackage.append ("Package cn.test.entity;\r\n\r\n");                Sbpackage.append ("Import javax.persistence.column;\r\n");                Sbpackage.append ("Import javax.persistence.entity;\r\n"); Sbpackage.append ("Import Javax.persistencE.generatedvalue;\r\n ");                Sbpackage.append ("Import javax.persistence.id;\r\n");                Sbpackage.append ("Import javax.persistence.table;\r\n\r\n");                Sb.append ("\r\[email protected]\r\n");                Sb.append ("@Table (name = \" "+ tablename +" \ ") \ r \ n");                Sb.append ("public class" + classname + "implements java.io.Serializable {\ r \ n");                Gets the column of the current table CRS = Metadata.getcolumns (null, "%", TableName, "%");                Gets the referenced table whose primary key is the foreign Key fktablenamesandpk.clear () of the current table;                ResultSet foreignkeyresultset = metadata.getimportedkeys (catalog, NULL, tablename); while (Foreignkeyresultset.next ()) {String pktablenname = foreignkeyresultset.getstring ("Pktable_name" ); Foreign key table String fkcolumnname = foreignkeyresultset.getstring ("Fkcolumn_name"); Foreign key if (!fktablenamesandpk.containskey (fkcolumnName)) Fktablenamesandpk.put (Fkcolumnname, pktablenname);                }//Foreignkeyresultset.close ();                    while (Crs.next ()) {String ColumnName = crs.getstring ("column_name");                    String ColumnType = crs.getstring ("type_name");                    System.out.println ("--------------------------" + ColumnType); if (Existfkcolumn (ColumnName)) {String fkclassname = Getclassnamebytablename (fktablenamesandpk.get                        (ColumnName));                        Sbpackage.append ("import" + Pkname + "." + fkclassname+ "; \ r \ n");                        Sb.append ("\t/** */\r\n");                    Sb.append ("\tprivate" + fkclassname + "+ columnname+"; \ r \ n ");                        } else {sb.append ("\t/** */\r\n");                    Sb.append ("\tprivate" + getfieldtype (ColumnType, sbpackage) + "" + ColumnName + "; \ r \ n");}} sb.append ("}");                File File = new file (dirfile, classname + ". Java");                if (file.exists ()) {file.delete ();                } getTitle (Sbpackage, classname);                FileOutputStream outputstream = new FileOutputStream (file);                Outputstream.write (Sbpackage.tostring (). GetBytes ());                Outputstream.write (Sb.tostring (). GetBytes ());                Outputstream.close (); System.out.println (classname + "create success ...            ");        }} catch (Exception e) {e.printstacktrace (System.out);                } finally {try {if (null! = rs) {rs.close ();                } if (null! = conn) {conn.close ();     }} catch (Exception e2) {}}}/** * Gets the class name according to the table name * * @param tablename * @return */Private STatic string Getclassnamebytablename (String tablename) {string classname = GetClassName (tablename); for (String name:classnames) {if (Name.tolowercase (). Equals (Tablename.tolowercase ())) {class            name = name;    }} return classname; The private static Boolean Existfkcolumn (String columnname) {if (FKTABLENAMESANDPK! = null) {if (Fkta        Blenamesandpk.containskey (ColumnName)) return true;    } return false; }/** * Suitable for table name is a single word, for example: Table name is Tbluser class name is Tbluser, when the table name is user class name is user, when the surface is usertype (two words) *, the class name is usertype, if you want to usertype, the period     To the Classnames field (which is not case sensitive to the database table name). * * @param tablename * @return */public static string GetClassName (String tablename) {string res        = Tablename.tolowercase ();        if (Tablename.startswith ("TBL")) {return tablename.substring (0, 4) + res.substring (4); } return tablename.substring (0, 1). TouppercaSE () + res.substring (1);            }/** * Set field type MySQL data type * * @param columntype * Column type String * @param sbpackage * Package information * @return */public static string GetFieldType (String columntype, StringBuffer sbpackage) {/             * * Tinyblob tinyblob byte[] tinytext varchar java.lang.string blob blob byte[]             Text varchar java.lang.string mediumblob mediumblob byte[] mediumtext varchar java.lang.string  Longblob Longblob byte[] longtext varchar java.lang.string enum (' value1 ', ' value2 ',...) Char java.lang.string set (' value1 ', ' value2 ',...) char java.lang.string */columntype = column        Type.tolowercase ();                if (columntype.equals ("varchar") | | columntype.equals ("nvarchar") | | columntype.equals ("char")// || Columntype.equals ("Tinytext")//| |     Columntype.equals ("text")//           || Columntype.equals ("Mediumtext")//| |        Columntype.equals ("Longtext")) {return "String"; } else if (Columntype.equals ("Tinyblob") | | Columntype.equals ("blob") | | Columntype.equals ("Mediumblob") | |        Columntype.equals ("Longblob")) {return "byte[]1111"; } else if (Columntype.equals ("datetime") | | Columntype.equals ("date") | | Columntype.equals ("timestamp") | | Columntype.equals ("Time") | |            Columntype.equals ("Year")) {sbpackage.append ("import java.util.date;\r\n");        return "Date"; } else if (Columntype.equals ("bit") | | Columntype.equals ("int") | | Columntype.equals ("tinyint") | | Columntype.equals ("smallint")//| | Columntype.equals ("bool")//| |        Columntype.equals ("Mediumint")//        ||        Columntype.equals ("bigint")) {return ' int ';        } else if (Columntype.equals ("float")) {return "float";        } else if (Columntype.equals ("double")) {return "double";            } else if (Columntype.equals ("decimal")) {//Sbpackage.append ("Import java.math.bigdecimal;\r\n");//        return "BigDecimal";    } return "ErrorType"; /** * Set Class caption Comment * * @param sbpackage * @param className */public static void GetTitle (Stringbu        Ffer sbpackage, String className) {simpledateformat format = new SimpleDateFormat ("yyyy year mm DD day");        Sbpackage.append ("\r\n/**\r\n");        Sbpackage.append ("*\r\n");        Sbpackage.append ("* Title:" + className + "<br/>\r\n");        Sbpackage.append ("* Description: <br/>\r\n");        Sbpackage.append ("*\r\n");        Sbpackage.append ("* Make information: DATE:" + format.format (new Date ()) + "name:author\r\n"); SbpackagE.append ("*\r\n");        Sbpackage.append ("* Modify information <br/>\r\n");        Sbpackage.append ("* Modification date Modified by modification of the modified content <br/>\r\n");        Sbpackage.append ("*\r\n");    Sbpackage.append ("*/\r\n"); }}

Single-table implementations:

Gets the table structure information based on the query statement.

Package Cn.test;import Java.io.file;import Java.io.fileoutputstream;import java.sql.connection;import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;public class Test {private static    String path= "D:\\tool\\project\\dynamictable\\src\\cn\\test\\entity"; private static String URL = "jdbc:sqlserver://192.168.1.220:1433; USER=CJTC;    Password=cjtc;databasename=person ";    public static void Main (string[] args) {test ();        public static void Test () {Connection conn = null;        PreparedStatement PS = null;        ResultSet rs = null;        StringBuffer sb=new StringBuffer ();            try {class.forname ("com.microsoft.sqlserver.jdbc.SQLServerDriver");            conn = drivermanager.getconnection (URL);            String sql = "SELECT * from Tblaccount";            PS = conn.preparestatement (SQL);            rs = Ps.executequery ();            Sb.append ("Package cn.test.entity;\r\n"); Sb.append ("public class TBLAccount {\ r \ n ");            File Dirfile = new file (path);            if (!dirfile.exists ()) {dirfile.mkdirs ();            }//Get the column name and type int colunmcount = Rs.getmetadata (). getColumnCount ();            string[] Colnamearr = new String[colunmcount];            string[] Coltypearr = new String[colunmcount];                for (int i = 0; i < Colunmcount; i++) {Colnamearr[i] = Rs.getmetadata (). getColumnName (i + 1);                Coltypearr[i] = Rs.getmetadata (). Getcolumntypename (i + 1);                System.out.println (Colnamearr[i] + "(" + coltypearr[i] + ")" + "|"; if (coltypearr[i].equals ("varchar") | |                                    Coltypearr[i].equals ("nvarchar")) {sb.append ("\tprivate String" +colnamearr[i]+ "; \ r \ n"); }else if (coltypearr[i].equals ("datetime")) {} else if (coltypearr[i].equals ("int")            ) {}} sb.append ("}"); File File = new File (dirfile, "Tblaccount.java");            if (file.exists ()) {file.delete ();            } fileoutputstream outputstream = new FileOutputStream (file);            Outputstream.write (Sb.tostring (). GetBytes ());            Outputstream.close (); SYSTEM.OUT.PRINTLN ("Success ...        ");        } catch (Exception e) {e.printstacktrace (System.out);                } finally {try {if (null! = rs) {rs.close ();                } if (null! = PS) {ps.close ();                } if (null! = conn) {conn.close (); }} catch (Exception e2) {}}}}

Get the table structure in the database through JDBC