Exporting Database Tables





Exporting Database Tables

Once you have created your mappings for the EJB project, you must export the logical database model to a relational database in order to do any testing of the EJB module. This is assuming that you did not first import the database model from a relational database when doing a bottom-up or a meet-in-the-middle mapping. There are two ways to export the logical database tables to a relational database: export the database directly or execute the table.ddl file. Both methods are simple to use but, depending on your needs, one method may be better. Typically the production database is tightly controlled and you will not be able to export the logical database to the physical database. However, you may have a private database that serves as a testing ground prior to making changes on the production database. This is where the following export options are useful.

The simplest approach is to export the logical database model directly to the relational database by using a wizard that generates dynamic SQL statements that are executed against the database. This is accomplished from the J2EE Hierarchy view within the J2EE perspective (see Figure). Expand the Databases group so that you can see each of the models that have been defined. Once you find the database that you want to export, select the Export to server context menu action which opens the Data Export wizard shown in Figure. The first page of the wizard displays a tree check box view starting with the root object selected and all of its children expanded. So in this example the wsbook database contains one NULLID schemata which in turn contains several tables. Only the selected objects are exported to the relational database.

21. Data export wizard.

graphics/24fig21.jpg

Note that you can select any database component within the J2EE Hierarchy view and still select the Export to server context menu action. If you select the NULLID schemata from the J2EE Hierarchy view and select the Export to server action, the first page of the data export wizard starts with the NULLID schemata and includes only its child components.

The second page of the wizard, shown in Figure, provides options that you can choose when exporting to the database. The first three options allow you to control when the export transaction is committed. By default, the Commit changes only upon success option is selected.

22. Data export options wizard page.

graphics/24fig22.jpg

The last three check boxes are probably the most interesting because they control the contents of the SQL statements that will be executed on the database:

  • Generate fully qualified names indicates whether you want to include the schemata name for each table (e.g., SCHEMANAME.TABLENAME). You want to ensure that this is not selected since we are not using fully qualified statements in our SQL queries; we want to export the tables without fully qualified names as well.

  • Generate delimited identifiers will add quotation marks around the created objects within the statement (e.g., CREATE TABLE "TEST.TABLEA"). Use this option with column names that contain spaces.

  • Generate associated DROP statements will add DROP TABLE statements for each table being created as well as DROP SCHEMA statements for each schema. This is extremely useful when you already have existing databases and you want to overwrite them with a new definition.

The last wizard page is the Database Connection page which is the same page that was used during the bottom-up and meet-in-the-middle mapping approaches earlier in the chapter (see Figure). You can go back to the previous sections to learn more detail about these sections but they are basically used to provide detailed information for making a connection to the database you wish to use as the target of this export. You can either supply the information to create a connection, or select from an existing connection that has been defined. You should be careful to use the same user ID in this connection page as you plan to use for the datasource that will be defined in the server configuration. This is necessary because the tables will automatically use the user ID for the schema name if one is not supplied during this export. Also, any SQL statements that are not fully qualified will use the user ID of the datasource for the schema qualification of the tables within the statement.

The second method to export the tables to the database is to execute the Table.ddl file which contains SQL statements to create the database, schema, and tables. This file was generated when running the EJB to RDB mapping wizard if you had the Generate DDL option selected (Figure). If you did not have this option selected at the time that you created the mapping, you can always select the EJB module from the J2EE Hierarchy view and then select the Generate > Schema DDL context menu option. This will produce the static Table.ddl file within the current back-end folder. This file is convenient because you can make modifications to suit your needs. The Table.ddl that is generated does not contain foreign key statements since referential integrity sorting (the sorting of SQL statements to ensure database table constraints are not violated) is not maintained by the generated SQL or the WebSphere EJB container.

Remember that these options are typically used to export to a private database for the purpose of testing. You can use the generated DDL file to request changes to your DBA on the production database.

To export tables to the database using the Table.ddl file, select the file from the appropriate back-end folder within the J2EE Navigator view. Next, select the Run on Database Server context menu action. This will open the Run Script database wizard shown in Figure. The first page of this wizard contains a check box list of each statement within the script file. You have the option of deselecting any statement which you do not want to be executed at this time. The second page of the wizard has the same three commit options as displayed in the Data Export Options wizard page shown in Figure. The last page is the same Database Connection page used in the previous data export wizard and the EJB to RDB mapping wizard.

23. Run script wizard.

graphics/24fig23.jpg


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows