Exit the Roo shell and delete the contents of the C: oo-cookbookch03-recipes directory.

Start the Roo shell from the C: oo-cookbookch03-recipes directory.

Execute the ch03_jpa_setup.Roo script which creates the flight-app Roo project, sets up Hibernate as a persistence provider, and configures MySQL as the database for the application. If you are using a different database than MySQL or your connection settings are different than what is specified in the script, then modify the script accordingly.

Execute the myflightappdb.sql SQL script (that accompanies this book) against the MySQL database. The myflightappdb.sql creates a database named myflightappdb consisting of the FLIGHT_TBL, FLIGHT_DESC_TBL, and CUSTOMER_TBL tables and a FLIGHT_VIEW database view, as shown earlier in this recipe. Ensure that the database.properties file of your Roo project contains the settings to connect to this newly created myflightappdb database.

For the purpose of this recipe, we'll be using the database introspect command to view the metadata of a myflightappdb database created in MySQL database. Now, you're all set to view metadata related to the database you've configured in the database.properties file of your Roo project.

To use the database introspect command to view the database metadata, follow the steps given here:

The database introspect command makes use of the connection properties defined in the database.properties file of the Roo project to create metadata information. This implies that you can't simultaneously introspect multiple databases using the database introspect command. The command accepts the following arguments:

The output of the database introspect command is an XML file (assuming a file argument was specified), which contains details of database tables, including fields and their type, primary key, foreign key, and so on. If you specify the enableViews argument, the generated metadata includes database views also.

The following XML fragment shows how the database metadata is presented in the mydb.xml file:

<database name="no-schema-required">
  <table name="customer_tbl">
    <column name="cust_id" primaryKey="true" 
       required="true" scale="0" size="10" type="4,INT"/>
    ...
    <unique name="PRIMARY">
      <unique-column name="cust_id"/>
      <unique-column name="cust_dob"/>
    </unique>
  </table>
  <table name="flight_desc_tbl">
     <column name="flight_desc_id" primaryKey="true" 
       required="true" scale="0" size="19" type="-5,BIGINT"/>
   ...
      <foreign-key foreignTable="flight_tbl" 
        name="FK7E26BB6F365DD59" onDelete="none" 
        onUpdate="none">
        <option key="exported" value="true"/>
        <reference foreign="flight_desc_id" 
           local="flight_desc_id"/>
       </foreign-key>
       <unique name="PRIMARY">
         <unique-column name="flight_desc_id"/>
       </unique>
   </table>
   <table name="flight_tbl">
   ...
   </table>
    
   <table name="flights_view">
    <column name="origin_city" primaryKey="false" 
      required="true" scale="0" size="255" type="12,VARCHAR"/>
   ... 
   </table>
</database>

The following table describes the significance of the elements in the given XML:

The metadata generated by Roo is used as input for generating JPA entities, as we'll see in the Creating entities from database recipe. Errors may be reported when you use the database introspect command if the META-INF/spring/database.properties file is not found or the connection properties defined in the database.properties are incorrect. The following table shows error messages that might be reported by the database introspect and the corresponding resolution: