Automatic Database Schema Generation in Sun Java System Application Server and J2EE 1.4 SDK

Print-friendly Version

This paper describes the Automatic Database Schema Generation facility provided in the Sun Java System Application Server and J2EE 1.4 SDK. The facility allows developers to focus on creating Enterprise Java Beans (EJBs) while letting the application server define tables based on the fields in EJBs and relationships between them. This paper also describes the features available and how to use them, both with the deploytool GUI and with a text editor, and how the choices made by the user impact the resulting tables.

Database applications by their very nature require both an application component and a database component. Developers are generally knowledgeable in one or the other but lack expertise in both. A developer of an application which must access a database will often have a focus on the application aspects of the work, rather than on the database aspects. When setting up the database, they often have to appeal for help from a Database Administrator (DBA), thereby increasing the cost and complexity of the development process.

In the majority of cases, developing database applications with J2EE can be very straightforward and does not require the intervention of a DBA. For example, J2EE's Container Managed Persistence (CMP) insulates developers from many of the database aspects of database applications: developers do not work in terms of SQL or other database constructs, but solely in terms of EJBs. Of course, a database schema is still necessary to represent the CMP state in a database.

The Sun Java System Application Server (SJSAS) and J2EE SDK include a facility by which developers can focus their efforts entirely on the application aspects, with confidence that database access will be handled by the application server. By way of this facility, developers create EJBs, and when they are deployed, appropriate database schemas are created in the database, ready for use. Simply put, the developer does not have to consider database access at all. This greatly increases programmer productivity and ease-of-use with the application server. The resulting schema is usable as-is, or can be given to a DBA for “industrial strength” tuning with respect to performance, security, and so on.

This paper describes how to increase developer productivity and ease-of-use with the automatic database schema generation capabilities in the Sun Java System Application Server and J2EE SDK. It assumes that you are comfortable with EJB concepts, and with deploytool, the graphical user interface tool for assembling and deploying applications that is provided with both the Sun Java System Application Server and J2EE 1.4 SDK distributions. It also assumes that you have handy a .ear file that has a CMP bean that you want to deploy, without having to create the database schema yourself.

Applications consist of modules, and database tables are created on a per-module basis, as required. Here “as required” means that the module has one or more CMP beans for which there is no database information (in particular, for which there is no sun-cmp-mappings.xml file). Within each module, a table is created for each bean. Each CMP field in a bean corresponds to a generated column in the corresponding table. One-to-one and one-to-many relationships between beans are represented by foreign key constraints. Many-to-many relationships are represented by join tables which are created as required.

The database tables generated for each module are independent of the tables of other modules. Just as it is not possible for a bean in one module to have a relationship to a bean in another module, the generated schema will not have foreign keys between tables resulting from different modules.

Database tables are created based on the following defaults, though you can override these:

• Database vendor name – PointBase or the actual database being used, depending on how you deploy your application.
• Table name – Based on the corresponding EJB name
• Column name – Based on the corresponding CMP field name
• Column type and nullability – Depends on the field type; see appendix
• Column type size and precision – Depends on the field type, but for example, java.lang.String defaults to VARCHAR(256), and java.math.BigDecimal defaults to DECIMAL(38).

Ways of Using Automatic Database Schema Generation

The simplest way to develop a CMP application is to write the CMP EJBs and other Java code and deploy it with deploytool or with asadmin --createtables=true. The database schema generation automatically runs and uses system–provided default information to create tables as needed.

If you want finer control on database schema generation, there are two ways of doing that. One is by using the deploytool GUI, the other is by editing a deployment descriptor with your favorite text editor. In either case, the control you have is in terms of overriding the above defaults.

Using deploytool, you get to see the schema that will be generated, and make slight changes. With a text editor, you specify overrides from the defaults, so this is mostly useful if you understand the defaults and want to override them in some way.

Whether via deploytool or text editing, the end result is a module that contains a deployment descriptor with your overrides (if any), so that when the module is deployed, both SQL and database schema representing the beans can be created. This module can be deployed from deploytool or asadmin, with identical results vis–a–vis database schema generation. When you deploy an application or module, SQL corresponding to the CMP EJBs will be created, but you can control whether or not the corresponding database schema is created in the database.

Controlling Schema Generation

You can control the automatic database schema generation in a few ways through options specified in deploytool, in the sun-ejb-jar.xml file, or from the asadmin command line. Below is a description of what the various possibilities.

Creating and Dropping Tables

The automatic schema generation facility distinguishes between generating the SQL that describes the database schema, and creating the schema in the database. This allows finer control over impact on the database, and allows users to examine the SQL before it is used to create schema in the database.

If you choose to create tables at deployment, they will be created in the database. If you choose to drop tables at undeployment, then when the application/module is undeployed, the tables will be dropped in the database.

A simple scenario shows how these can be useful. Let's say you develop an application, and deploy it such that the tables are created. It runs for a while, and creates some data in the database. Then you discover a problem in the application's logic.

If you undeploy without dropping the tables and then redeploy without creating tables, your data will still be there. If you want a fresh start each time, drop the tables at undeploy and create them anew at each deployment.

Choosing the Database Vendor Name

While SQL-92 is a standard, each database vendor's SQL has its own idiosyncrasies. By default, the schema you generate will be targeted to PointBase, which is supplied with the Sun Java Application Server. You can choose from the any of the supported databases:

• PointBase
• Oracle
• IBM DB2
• Sybase
• Microsoft SQL-Server

In addition, you can choose SQL-92. This will cause generation of SQL that is compliant with that standard. If you are not using one of the above databases, and it is SQL-92 compliant, this choice might work.

Using Unique Table and Foreign Key Constraint Names

The names of tables are based on the names of their corresponding beans. For example, a bean with ejb-name of EmployeeEJB results in a table named EMPLOYEEEJB.

You can choose whether the names of generated tables and constraints are unique. By default they are unique within each module, but not within a multi-module application nor across applications deployed in the same application server domain.

However, it is conceivable that two modules within an application could both have EmployeeEJB beans, though with different CMP fields, relationships, etc. In order to allow this, choose to generate unique table names. This will cause digits which are based on module and application names to be appended.

Note that if you deploy with uniqueness turned on, then undeploy, then deploy again with uniqueness turned on, that the table names will be the same. That is, they are not unique for each deployment, though they are unique for each application and module.

Overriding Database Column Type

The schema generator uses some built-in defaults to decide how to represent each bean's fields in the database schema. Each field results in a different column, and each column's type is a reasonable match for the field's Java type. But you can override these defaults on a column-by-column basis.

For example, fields of type java.lang.String are mapped to VARCHAR(256) by default. But a field for the name of a state really needs only two letters, so you could override that field's type to be CHAR(2). This would not affect any other fields.

You can override aspects of types independently. For example, your bean's address field would also start out as VARCHAR(256), but you can change just the maximum length to be 32, resulting in VARCHAR(32).

You can change the scale and precision of numeric types, and choose whether a field's corresponding column should be nullable or not.

Default column types for Java types are given in the appendix.

Start deploytool, which is provided in the bin directory where you installed the application server, and then you are only a few clicks away from generating your CMP bean's database schema:

Use the tree view on the left to navigate to your the module containing your CMP bean.

1. Click the Sun-specific-Settings... button. Notice that the Field Mappings section is blank.
2. Click the Create Database Mappings... button. The default choices will cause creation of tables that have names corresponding to those of their CMP beans.
3. Check Generate Unique Table Names if you want the table and foreign key constraint names to be unique across applications. If you plan on looking at the SQL itself, the non-unique names are easier to reconcile with the ejb-names of your beans.
4. By default, tables are generated for the PointBase database. If you are using a database provided by a different vendor, choose that one from the list. Try Generic SQL92 if your database vendor is not listed but is SQL92-compliant.
5. Click OK.

The Field Mappings table now displays the default field-to-column mappings that have been generated. The mapping is for the bean shown in the Enterprise Bean dropdown; if you have more than one CMP bean in the module you can see the mapping for each, one at a time, by choosing others from this dropdown list.

Modifying Generated Schema

The schema initially shown in the Field Mappings table is based on a set of default, reasonable mappings. You can make changes to the type of non-relationship columns.

Overriding Column Types

You can override the default column JDBC type on a field-by-field basis. To do so, click on the Type cell for a field. Then in the Edit Column Type dialog, choose an appropriate JDBC type from the drop-down list. The choices listed are compatible with the column's database type: You can change the length of character and BLOB fields, as well as precision and scale of DECIMAL fields. Here are the possibilities:

Regenerating Schema

You want to regenerate schema, for example with a different database vendor name. You can do so by clicking the Create Database Mappings... button and going through the schema generation dialogs again. The newly generated schema will replace all modifications you previously made for all beans.

Advanced Concepts

Naming the CMP Resource

You can specify a particular JNDI name of a resource in the CMP Resource text field of the Sun-Specific Settings dialog. This name is of a jdbc-resource entry in the domain.xml file. This name is used at run time to locate persistent resources.

This part is optional; if you do nothing then the automatic schema generator will set it by default so that PointBase is assumed.

Make sure that the name references a database that is of the same type as that chosen in the Create Database Mappings dialog.

For example, consider the following entry in your domain.xml file:

<jdbc-resource jndi-name="jdbc/my_db" pool-name="jdbc/pointbase_pool"/>

This associates the jndi-name with the pool name. To specify that automatic schema generation should use this pool, name your CMP resource jdbc/my_db.

Controlling Table Creation

When you deploy (undeploy) from deploytool, by default, database tables will be created (dropped). But you can change that, for example, so that tables are not dropped when you undeploy, thus preserving data across deployments.

To change the behavior, click on the Table Generation Settings button on the Sun-specific Settings screen. If Create Tables on Deploy is checked, when the application/module is deployed tables will be created in the database for beans that are automatically mapped. If unchecked, tables will not be created. If Delete Tables on Undeploy is checked, database tables that were automatically created when the beans were last deployed will be dropped when the beans are undeployed. If unchecked, the tables will not be dropped.

Save Your Work

When you Save the application or module from within deploytool, it is saved in a EAR or JAR file. For each CMP module, there will be one file containing a representation of the database schema in a .dbschema file. In addition, there will be one file for each CMP module representing the mapping between the database schema and the CMP beans in a sun-cmp-mappings.xml file. Finally, the options for controlling database schema generation (such as whether or not to use unique table names) are saved in sun-ejb-jar.xml. The EAR or JAR can be deployed from deploytool or from the asadmin command line program.

Using a text editor to control database schema generation is not easier than using deploytool, but some people prefer this way of working. The biggest difference is that you don't get to see the schema before it is generated. Another big difference is that because you don't have deploytool verifying your changes, it is easier to cause problems that may be difficult to diagnose later.

All the changes are in one file, sun-ejb-jar.xml, that is within the META-INF directory of each module. If you don't have one yet, the easiest way is to create a module from scratch using deploytool, save the EAR or JAR, then unjar the module. For the remainder of this section, assume an EmployeeEJB with fields int id, String name, and double salary, in a module called EmployeeJar.

Edit the sun-ejb-jar.xml file that is in the module's META-INF directory. Search for the cmp-resource element. All of the control of database schema generation is handled by subelements of cmp-resource. There are four subelements that control database schema generation, as shown in the following table:

One additional element is not strictly related to automatic schema generation: jndi-name. This element will determine the data source used for the database itself. You can specify a particular JNDI name of a resource in the jndi-name subelement of the cmp-resource element in the module's sun-ejb-jar.xml file. This name is of a jdbc-resource entry in the domain.xml file. It is used at run time to locate persistent resources. If you do not set it, the automatic schema generator will do so for you, using jdbc/PointBase

The first three elements in the table were discussed earlier (see “Concepts” above). The create-tables-at-deploy and drop-tables-at-undeploy elements take true or false values, for example:

	<create-tables-at-deploy>true</create-tables-at-deploy>
	<drop-tables-at-undeploy>true</drop-tables-at-undeploy>

The database-vendor-name element takes one of the following names:

• db2
• mssql
• pointbase
• oracle
• sql92
• sybase

For example, to work with an Oracle database, add this in the sun-ejb-jar.xml file:

	<database-vendor-name>oracle</database-vendor-name>

The schema-generator-properties element contains only property subelements. Each property element in turn contains one each name and value subelements. There are two general categories of property elements.

The first category of properties is those which allow you to override the default type or type attributes of a column that gets generated for a field. Note that the type given must be one of those in java.sql.Types. (The appendix provides the default field type – column type mappings.) The overriding follows a pattern in the name portion of the property, to indicate the bean, field, and characteristic of the field that is being overridden. For example:

<schema-generator-properties>
	<property>
		<name>EmployeeEJB.name.jdbc-type</name>
		<value>CHAR</value>
	</property>		

In this case, the name field (which was earlier noted as being a java.lang.String field) will be represented in the database by a CHAR column. By itself, that is probably not a wonderful idea: char columns are fixed width, but the exact width may vary between database vendors. So, let's also specify the length of the char column:

	<property>
		<name>EmployeeEJB.name.jdbc-maximum-length</name>
		<value>30</value>
	</property>		

If you want to allow managers to set aside an id for employees that are not yet hired, make sure the name column is nullable:

	<property>
		<name>EmployeeEJB.name.jdbc-nullable</name>
		<value>true</value>
	</property>		

It might be a good idea to limit their salary:

	<property>
		<name>EmployeeEJB.salary.jdbc-precision</name>
		<value>9</value>
	</property>	
	<property>
		<name>EmployeeEJB.salary.jdbc-scale</name>
		<value>2</value>
	</property>	

Be careful when you use type overrides. Notice that they are, along with use-unique-table-names, all within a single schema-generator-properties element. If you make a mistake, some errors are caught during deployment, for example, mistyping cher instead of char). Others are not caught until runtime, such as using int when you meant double.

The other category of properties allows you to control uniqueness of table names. For example, to override the default so that unique table names are generated:

	<property>
		<name>use-unique-table-names</name>
		<value>true</value>
	</property>	
</schema-generator-properties>

Deployment with asadmin

When you are configuring automatic schema generation with a text editor, it's likely you'll want to use asadmin to deploy your applications and/or modules. You can use EAR/JAR (asadmin deploy) or directory-based deployment (asadmin deploydir); the same options apply in either case. You can further control database schema generation at that time, even overriding options that were selected in the sun-ejb-jar.xml files. The following table describes the schema generation related options to asadmin deploy:

Note that elements in sun-ejb-jar.xml control the what is generated for CMP beans on a module-by-module basis, and therefore can differ across modules. But the options to asadmin deploy affect all modules deployed by a single asadmin deploy command, with the following exceptions:

• If you have manually mapped one or more of the beans in a module and you use any of the asadmin deploy options, the deployment of those beans is not changed in any way, and asadmin will provide a warning that the generation options will be ignored.
  
  
• If you used deploytool to set up automatic schema generation for one or more of the beans, then the --uniquetablenames option has no effect on those modules when you run asadmin deploy. The uniqueness of the table names was established by the value in sun-ejb-jar.xml when deploytool created the mapping, and can no longer be overridden. Other modules remain unaffected. A warning will be printed on the console from which asadmin was run.

Undeployment with asadmin

Similar to deployment, you can control what happens with automatically generated tables at undeployment via asadmin undeploy. The option below overrides the corresponding setting in the sun-ejb-jar.xml file of each module undeployed in a single asadmin undeploy command:

1. How do I redeploy a CMP application so that it regenerates the schema in the database?
   
   If you are using deploytool, in the Table Generation Settings dialog (reached from the Sun-specific Settings dialog), make sure that both Create Tables on Deploy and Delete Tables on Undeploy are checked. To get the same effect using asadmin, use the --dropandcreatetables option. Either way, this will drop the tables (and their data!) created upon the last deployment and create new tables again at redeploy.



2. How do I change the names of tables after deploying the application with automatic database schema generation?
   
   You can't have arbitrarily-named tables. The automatic schema generation provides only two options: regular table names or unique table names.



3. Why would I ever need to specify the use of unique table names?
   
   You should specify the use of unique table names when either or both of the following are true:
   • You have an application with multiple modules, and there is a bean with the same name in more than one of those modules.
   • Your database already has tables with the same names as are generated when not specifying unique table names, and want to keep those existing tables but not use them for the module that you are deploying.


4. How can I have multiple EJBs use the same table?
   
   This will only work if one of the EJBs has a set of fields which is the superset of all the other EJBs. Make sure that all EJBs have the same ejb-name in ejb-jar.xml. Deploy the “superset” EJB with createtables=true and with uniquetablenames=false. Deploy all others with createtables=false.



5. When deploying an application and specifying that tables should be created, is the database schema always created in database?
   
   Not necessarily. There are two cases:
   1. If you have an application or module which contains sun-cmp-mappings.xml and .dbschema files which were not generated via Automatic Database Schema Generation, the options for schema generation are not applied. You will get a warning message, for example:
      
      Command deploy executed successfully with following warning messages: JDO74038: While deploying application 'PayrollApp': module 'salary-grade-ejb' is already mapped, so none of the following deployment options will be applied to this module: --uniquetablenames, --createtables, --dropandcreatetables, --dbvendorname.
      
      
   2. If your application/ear does use Automatic Database Schema Generation, some tables will not be created if errors occur during their creation. In that case, as many tables and foreign key constraints are created as possible, and a warning is printed for each that cannot be created. You can then either
      • Undeploy the application, and redeploy with unique table names set to true
      • Change the database using your database vendor's tools and create the tables and/or constraints that weren't created by examining the SQL created by automatic schema generator. See the answer to question 7 for how to obtain that SQL.


6. What happens if two modules contain the same bean?
   
   If they have exactly the same bean (i.e., same properties, methods, etc.) then the result depends on the whether unique table names are used.
   
   If the application is deployed with unique table names set to true, then the two modules will use different tables in the database.
   
   If the application is deployed with unique table names set to false, tables will be created on behalf of both beans. One of these creation attempts will result in a warning. The two beans will share the created tables. Whether or not that is desired is up to the application developer!



7. How can I examine the SQL that gets created automatically?
   
   Use the asadmin get-client-stubs command to get client jar file. For example:
   
   asadmin get-client-stubs --user user --password password --host host --port port --appname MyApp /tmp/example
   
   

   The client jar file will be located in /tmp/example. Unjar the JAR and you will find two .sql files: the one named *_database_create.sql has the DDL for creation of tables and the one named *_database_drop.sql has the DDL for deletion of tables.




8. I deployed my application with the --createtables=true option, but got warnings that the tables were not created. Was the deployment successful?
   
   Yes, the deployment was successful. You need to remove the existing tables. Then create tables in the database, using tools provided by your database vendor. See the answer to question 7 for how to obtain the SQL. Alternatively, after removing the existing tables, you can undeploy and then redeploy the application.
   
   Command deploy executed successfully with following warning messages:
JDO76614: Deployment encountered SQL Exceptions:
JDO76609: Got SQLException executing statement "CREATE TABLE EMPLOYEEEJB_882885720 (EMPID VARCHAR2(256) NOT NULL, NAME VARCHAR2(120) NULL, CITY CHAR(30) NOT NULL, DEPTBEAN77669104_DEPTID VARCHAR2(256) NULL, CONSTRAINT PK_EMPLOYEEEJB_882885720 PRIMARY KEY (EMPID))": java.sql.SQLException: ORA-00955: name is already used by an existing object



9. I undeployed my application, but got warnings that some tables weren't deleted. Is the undeployment successful? What should I do to delete those tables?
   
   Yes, but you may have to delete some tables and/or constraints by hand, using tools provided by your database vendor. See the answer to question 7 for how to obtain the SQL.



10. What happens if I deploy an application and provide a database vendor name that does not match the database specified by the CMP resource reference in sun-ejb-jar.xml?
    
    The deployment will succeed, but the tables might not be created. The database schema is generated based on the database vendor name you provided. If you specify one database vendor, but then deploy to another, there is a good chance that some differences in the SQL used by the two vendors will cause creation of one or more tables to fail. In that case, you will see SQLExceptions which indicate the nature of the problem. For example:
    
    Command deploy executed successfully with following warning messages:
JDO76614: Deployment encountered SQL Exceptions:
JDO76609: Got SQLException executing statement "CREATE TABLE DEPARTMENTBEAN299706020EMPLOYEEEJB_882885720 (DEPARTMENTBEAN299706020_DEPARTMENTID VARCHAR(256) NULL, EMPLOYEEEJB_882885720_EMPLOYEEID VARCHAR(256) NULL)": java.sql.SQLException: ORA-00972: identifier is too long



11. What happens if I do not specify a database vendor name but set the cmp-resource to a database that is not on your list of supported databases?
    
    The deployment will likely fail. In that case, your best option is to try “sql92” as database vendor name, or one of the others if you believe that your database vendor's SQL is “close” to one of the supported vendors.



12. What happens if I add or remove a CMP field?
    
    We recommend that you always regenerate the schema if you add or remove any CMP fields or relationships. But:
    If you add a field, regenerate the schema, and redeploy, you will have to specify that tables be created for the new field, so your old data will effectively be lost.
    
    If you remove a field, you can redeploy it, specifying that tables not be created, and continue to use the existing database schema. But if your application will be inserting new rows, the column corresponding to the removed field must be nullable. This is because the application will, naturally, not be inserting values into that column, so the resulting value will be null.



13. How are multiple relationships between two beans represented in a database?
    
    For each 1-to-1 or 1-to-N relationship, foreign key constraints model the relationships. For M-to-N relationships, a separate join table models each relationship. The schema generator puts as many foreign key constraints as possible on the table representing the “N” side of relationships.



14. How is a primary key based on the “unknown primary key” feature represented in a database?
    
    The schema generator creates an extra primary key column in the table.



15. What are the defaults for the options that control database schema generation?
    
    The default values in deploytool are:
    • unique table names: false
    • create tables at deploy: true
    • drop tables at undeploy: true
    • database vendor name: PointBase
    
    If you deploy with asadmin, the values used for those same options are taken from each module's sun-ejb-jar.xml file. If no values are specified in that file, the values used are:
    • unique table names: false
    • create tables at deploy: false
    • drop tables at undeploy: false
    • database vendor name: use what is referenced by the cmp-resource in sun-ejb-jar.xml; if that is not given then the schema generation contacts the database to automatically determine the database vendor.



16. Why do some table and/or constraint names end with '9'?
    
    If you you have an EJB or field name that clashes with a reserved word of the database you're using, the automatic schema generator appends '9' to prevent the database from rejecting the name you provided.



17. I have a bean with a field of type java.sql.Timestamp, and it deploys but with errors related to TIMESTAMP on Oracle 8. Why is that?
    
    Oracle 9 introduced the TIMESTAMP column type, which is capable of supporting fractional seconds. The automatic schema generator assumes that uses of java.sql.Timestamp will be for storing dates/times that include fractional seconds, and so generates TIMESTAMP(9) columns for Oracle. If you are using an earlier version of Oracle and need to use java.sql.Timestamp fields, use the type override to generate a DATE column, but be forewarned: in that case, fractional seconds can not be stored!



18. I got a warning when using asadmin deploydir when providing the uniquetablenames option. Why is that?
    
    

    When you deploy an application using deploydir, automatic schema generation (and other elements of the deployment process) write files directly into the directory structure provided. The first time you do a deploydir–based deployment on that directory, database information is generated. Subsequent deploydir–based deployments reuse that same information; it is not generated again. So attempts to override it result in a warning. Similarly, if you have CMP beans in a directory structure which you use with deploydir, the database information included in them cannot be overridden, and warnings are generated to make you aware of that fact.




19. I successfully deployed my application using deploytool, but when I run it there are database-related problems. Why is that?
    
    Unfortunately, deploytool currently does not report warnings that occur during automatic schema generation. If you suspect this could be the cause of the problem, inspect the server.log file, and/or use asadmin to deploy your module/application.



20. Why do some table names end with digits that are not in my EJB names, even though I am not specifying unique table names?
    
    While creating table and constraint names, if a name is used more than once, the automatic schema generator will make one of them unique by appending digits.

This table shows the types of columns that are generated by default for various Java types. You can override these as described in the body of this document. Note that only the JDBC types that are listed here can be used as overrides in sun-ejb-jar.xml.