# Mit Hibernate alle Tabellen und zugehörigen Metadaten auslesen



## LitWeb (8. Juli 2013)

Hallo, ich bin ganz frisch und neu hier und habe schon eine Frage an euch.

Für ein kleines Übungsprogramm möchte ich es umsetzen das von einer Mysql Tabelle alle Tabellennamen und die zugehörigen Metadaten ausgelesen werden.

Hier meine momentane config die bei mir in MyProject/configs liegt und hibernate.cfg.xml heißt.


```
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
	<session-factory>
        <!-- Database connection settings -->
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://127.0.0.1:3306/employees</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password"></property>
 
        <!-- SQL dialect -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
 
        <!-- JDBC connection pool (built-in) -->
        <property name="connection.pool_size">10</property>
 
        <!-- Disable the second-level cache -->
        <property name="cache.provider_class">org.hibernate.cache.*</property>
 
        <!-- Enable Hibernate's automatic session context management -->
        <property name="current_session_context_class">thread</property>
 
        <!-- Mapping files -->
        <!-- Not needed, because of Annotations -->
    </session-factory>
</hibernate-configuration>
```

Was muss ich tun um alle Tabellen und zugehörigen Meta Daten mit Hibernate aus zu lesen?

Vielen Dank für eure Antworten, Gruß LitWeb


----------



## LitWeb (8. Juli 2013)

Ich habe es nun selber soweit gebracht alle Tabellennamen auszulesen mit Hibernate.

Ebenfalls habe ich nun zu einer Tabelle folgendes.


```
protected void loadTableScheme(String tableName)
		{
			conf.configure("/configs/database/hibernate/hibernate.cfg.xml");
			sessionFactory = conf.buildSessionFactory();
			Session session = sessionFactory.openSession();
			List<?> columns = session.createSQLQuery("SHOW FULL COLUMNS FROM " + tableName).list();
			
			for (Object column : columns) {
				System.out.println(column);
			}
		}
```

In der Console wird folgendes ausgegeben.


```
[Ljava.lang.Object;@1548135
[Ljava.lang.Object;@eed1b8
[Ljava.lang.Object;@10aaf84
[Ljava.lang.Object;@89140e
[Ljava.lang.Object;@183c649
```

Wie komme ich denn nun an die Metadaten der Tabelle mit allen Informationen?


----------



## Thomas Darimont (8. Juli 2013)

Hallo,

das geht auch mit plain JDBC:

```
package de.tutorials.training;

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * User: tom
 * Date: 08.07.13
 * Time: 20:07
 */
public class JDBCMetaDataExample {
    public static void main(String[] args) throws Exception {

        MysqlDataSource ds = new MysqlDataSource();
        ds.setUser("root");
        ds.setPassword("*****");
        ds.setDatabaseName("sakila");


        try (Connection connection = ds.getConnection()) {
            outputTableMetaData(connection);
        }
    }

    private static void outputTableMetaData(Connection connection) throws SQLException {

        DatabaseMetaData dbMetaData = connection.getMetaData();

        try (ResultSet tables = dbMetaData.getTables("", "", "", null)) {
            while (tables.next()) {
                String tableName = tables.getString("TABLE_NAME");
                System.out.printf("Table: %s%n", tableName);

                try (ResultSet columns = dbMetaData.getColumns("", "", tableName, "")) {
                    while (columns.next()) {
                        System.out.printf(" Column: %s (%s)%n", columns.getString("COLUMN_NAME"), columns.getString("TYPE_NAME")); //TABLE_SCHEM, TABLE_NAME, COLUMN_SIZE, NULLABLE
                    }
                }
                System.out.println("****");
            }
        }


    }
}
```

Ausgabe:

```
Table: actor
 Column: actor_id (SMALLINT UNSIGNED)
 Column: first_name (VARCHAR)
 Column: last_name (VARCHAR)
 Column: last_update (TIMESTAMP)
****
Table: address
 Column: address_id (SMALLINT UNSIGNED)
 Column: address (VARCHAR)
 Column: address2 (VARCHAR)
 Column: district (VARCHAR)
 Column: city_id (SMALLINT UNSIGNED)
 Column: postal_code (VARCHAR)
 Column: phone (VARCHAR)
 Column: last_update (TIMESTAMP)
****
Table: category
 Column: category_id (TINYINT UNSIGNED)
 Column: name (VARCHAR)
 Column: last_update (TIMESTAMP)
****
Table: city
 Column: city_id (SMALLINT UNSIGNED)
 Column: city (VARCHAR)
 Column: country_id (SMALLINT UNSIGNED)
 Column: last_update (TIMESTAMP)
****
Table: country
 Column: country_id (SMALLINT UNSIGNED)
 Column: country (VARCHAR)
 Column: last_update (TIMESTAMP)
****
Table: customer
 Column: customer_id (SMALLINT UNSIGNED)
 Column: store_id (TINYINT UNSIGNED)
 Column: first_name (VARCHAR)
 Column: last_name (VARCHAR)
 Column: email (VARCHAR)
 Column: address_id (SMALLINT UNSIGNED)
 Column: active (BIT)
 Column: create_date (DATETIME)
 Column: last_update (TIMESTAMP)
****
Table: film
 Column: film_id (SMALLINT UNSIGNED)
 Column: title (VARCHAR)
 Column: description (TEXT)
 Column: release_year (YEAR)
 Column: language_id (TINYINT UNSIGNED)
 Column: original_language_id (TINYINT UNSIGNED)
 Column: rental_duration (TINYINT UNSIGNED)
 Column: rental_rate (DECIMAL)
 Column: length (SMALLINT UNSIGNED)
 Column: replacement_cost (DECIMAL)
 Column: rating (ENUM)
 Column: special_features (SET)
 Column: last_update (TIMESTAMP)
****
Table: film_actor
 Column: actor_id (SMALLINT UNSIGNED)
 Column: film_id (SMALLINT UNSIGNED)
 Column: last_update (TIMESTAMP)
****
Table: film_category
 Column: film_id (SMALLINT UNSIGNED)
 Column: category_id (TINYINT UNSIGNED)
 Column: last_update (TIMESTAMP)
****
Table: film_text
 Column: film_id (SMALLINT)
 Column: title (VARCHAR)
 Column: description (TEXT)
****
Table: inventory
 Column: inventory_id (MEDIUMINT UNSIGNED)
 Column: film_id (SMALLINT UNSIGNED)
 Column: store_id (TINYINT UNSIGNED)
 Column: last_update (TIMESTAMP)
****
Table: language
 Column: language_id (TINYINT UNSIGNED)
 Column: name (CHAR)
 Column: last_update (TIMESTAMP)
****
Table: payment
 Column: payment_id (SMALLINT UNSIGNED)
 Column: customer_id (SMALLINT UNSIGNED)
 Column: staff_id (TINYINT UNSIGNED)
 Column: rental_id (INT)
 Column: amount (DECIMAL)
 Column: payment_date (DATETIME)
 Column: last_update (TIMESTAMP)
****
Table: rental
 Column: rental_id (INT)
 Column: rental_date (DATETIME)
 Column: inventory_id (MEDIUMINT UNSIGNED)
 Column: customer_id (SMALLINT UNSIGNED)
 Column: return_date (DATETIME)
 Column: staff_id (TINYINT UNSIGNED)
 Column: last_update (TIMESTAMP)
****
Table: staff
 Column: staff_id (TINYINT UNSIGNED)
 Column: first_name (VARCHAR)
 Column: last_name (VARCHAR)
 Column: address_id (SMALLINT UNSIGNED)
 Column: picture (BLOB)
 Column: email (VARCHAR)
 Column: store_id (TINYINT UNSIGNED)
 Column: active (BIT)
 Column: username (VARCHAR)
 Column: password (VARCHAR)
 Column: last_update (TIMESTAMP)
****
Table: store
 Column: store_id (TINYINT UNSIGNED)
 Column: manager_staff_id (TINYINT UNSIGNED)
 Column: address_id (SMALLINT UNSIGNED)
 Column: last_update (TIMESTAMP)
****
Table: actor_info
 Column: actor_id (SMALLINT UNSIGNED)
 Column: first_name (VARCHAR)
 Column: last_name (VARCHAR)
 Column: film_info (TEXT)
****
Table: customer_list
 Column: ID (SMALLINT UNSIGNED)
 Column: name (VARCHAR)
 Column: address (VARCHAR)
 Column: zip code (VARCHAR)
 Column: phone (VARCHAR)
 Column: city (VARCHAR)
 Column: country (VARCHAR)
 Column: notes (VARCHAR)
 Column: SID (TINYINT UNSIGNED)
****
Table: film_list
 Column: FID (SMALLINT UNSIGNED)
 Column: title (VARCHAR)
 Column: description (TEXT)
 Column: category (VARCHAR)
 Column: price (DECIMAL)
 Column: length (SMALLINT UNSIGNED)
 Column: rating (ENUM)
 Column: actors (TEXT)
****
Table: nicer_but_slower_film_list
 Column: FID (SMALLINT UNSIGNED)
 Column: title (VARCHAR)
 Column: description (TEXT)
 Column: category (VARCHAR)
 Column: price (DECIMAL)
 Column: length (SMALLINT UNSIGNED)
 Column: rating (ENUM)
 Column: actors (TEXT)
****
Table: sales_by_film_category
 Column: category (VARCHAR)
 Column: total_sales (DECIMAL)
****
Table: sales_by_store
 Column: store (VARCHAR)
 Column: manager (VARCHAR)
 Column: total_sales (DECIMAL)
****
Table: staff_list
 Column: ID (TINYINT UNSIGNED)
 Column: name (VARCHAR)
 Column: address (VARCHAR)
 Column: zip code (VARCHAR)
 Column: phone (VARCHAR)
 Column: city (VARCHAR)
 Column: country (VARCHAR)
 Column: SID (TINYINT UNSIGNED)
****

Process finished with exit code 0
```

Je nachdem welche Hibernate Version du verwendest, kannst du dir davon die JDBC Connection besorgen und dann mit dem JDBC API wie gezeigt die Metadaten aus der Datenbank auslesen.
http://stackoverflow.com/questions/3526556/session-connection-deprecated-on-hibernate

Gruß Tom


----------



## LitWeb (9. Juli 2013)

Hallo,

wie das mit Plain JDBC geht weis ich, damit habe ich das schon des öfteren gemacht. Nur schreibe ich grad eine Mini Software für meine Bewerbung wo ich eben Hibernate mit einbeziehen will.

Ich werde mir das mal anschauen wie ich die JDBC Connection über Hibernate ansprechen kann. 

Denn ich brauche auch noch Informationen ob eine Spalte ein Primary Key oder Key oder Foreign Key ist. Und so weiter.

Wenn ich Erfolge verbuchen kann dann werde ich das natürlich posten und wenn nicht werde ich leider mit weiteren Fragen löchern müssen.

Gruß LitWeb


----------

