Parsing the table structure and primary key foreign keys in the database through JDBC

Article turned from: http://ivan4126.blog.163.com/blog/static/20949109220137753214811/

Hibernate is used in a project and everyone knows that Hibernate is the ORM framework, and that he is capable of generating database tables from entities. We used dbUnit in the unit test, DbUnit can help us prepare the test data of the database before testing, then we take advantage of the ready-made database environment test, we need to clean up all the data in the database (in order not to affect the other unit tests), then next Test. Although it has not been updated for more than a year, dbUnit overall use is good, but in the time to clear the data dbUnit seems to be a little overwhelmed. There is a problem when the table has self-correlation (the table has foreign keys referencing itself), because the foreign key constraint, the data is always not clear.
DbUnit not to force, then we have to give strength ah. People who have learned Java should have learned that JDBC,JDBC can not only manipulate the database, but also get meta information in the database, such as which tables are in the database, which columns are in the table, and so on.
The core class we want to use is java.sql.DatabaseMetaData, which can be obtained by Java.sql.connection#getmetadata ().

Java code

1. Connection conn = ...;
2. DatabaseMetaData dbmetadata = Conn.getmetadata ();

Get all Tables

Java code

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

The following columns are in the Tablesresultset:

1. Table_cat String = Table category (can be null)
2. Table_schem String = Table mode (can be null)
3. table_name String = table name
4. Table_type String = table type. Typical types are "table", "VIEW", "SYSTEM TABLE", "GLOBAL temporary", "LOCAL temporary", "ALIAS", and "synonym".
5. REMARKS Explanatory notes for the String = table
6. Type_cat String = type category (nullable)
7. Type_schem String = Type mode (can be null)
8. Type_name String = type name (can be null)
9. Self_referencing_col_name String = Name of the specified "identifier" column with a type table (nullable)
10. Ref_generation String = Specifies how values are created in Self_referencing_col_name. These values are "SYSTEM", "USER", and "DERIVED". (may be null)

Get the primary key for a table

Java code

1. String tableName = ...;
2. ResultSet primarykeyresultset = dbmetadata.getprimarykeys (catalog,null, TableName);
3. while (Primarykeyresultset.next ()) {
4. String primarykeycolumnname = primarykeyresultset.getstring ("column_name");
5. }

Primaykeyresultset has the following columns:

1. Table_cat String = Table category (can be null)
2. Table_schem String = Table mode (can be null)
3. table_name String = table name
4. column_name String = column Name
5. Key_seq short = 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).
6. Pk_name String = name of the primary key (can be null)

Get a foreign key for a table

Java code

1. ResultSet foreignkeyresultset = dbmetadata.getimportedkeys (catalog,null, TableName);
2. while (Foreignkeyresultset.next ()) {
3. String fkcolumnname = foreignkeyresultset.getstring ("Fkcolumn_nam");
4. String pktablenname = foreignkeyresultset.getstring ("Pktable_name");
5. String pkcolumnname = foreignkeyresultset.getstring ("Pkcolumn_name");
6. }

Foreignkeyresultset has the following columns:

1. Pktable_cat String = Imported primary key table category (nullable)
2. Pktable_schem String = Imported PRIMARY key table mode (nullable)
3. Pktable_name String = name of the primary key table being imported
4. Pkcolumn_name String = name of the primary key column being imported
5. Fktable_cat String = foreign key table category (can be null)
6. Fktable_schem String = foreign key table mode (nullable)
7. Fktable_name String = foreign key table name
8. Fkcolumn_name String = foreign key column name
9. Key_seq short = serial number in the foreign key (value 1 indicates the first column in the foreign key, and a value of 2 indicates the second column in the foreign key)
10. Update_rule short = changes in foreign keys when updating primary keys
11. Delete_rule short = changes in foreign keys when you delete a primary key
12. Pk_name String = name of the primary key (can be null)
13. Fk_name String = Name of the foreign key (can be null)
14. Deferrability short = Whether the evaluation of foreign key constraints can be deferred to the time of submission

The key technical issues have been resolved, and the next step is to implement them. Delete the data in the database without so the table, I think of a simple rude method, that is 0. Remove all foreign keys from all tables 1. Delete all data from the table 2. Add the foreign key back.