How To Use ADF Business Components with MySQL

An Oracle JDeveloper How To Document
Written by Steve Anderson, Oracle Corporation
Last Updated: July 2005

ADF Business Components and MySQL

ADF Business Components (ADF BC) can help you rapidly create a robust data-driven application. MySQL is a popular and easy to use database. Although ADF BC does take advantage of many of the extended features of the Oracle database, it can work with other databases, including MySQL.

These instructions assume you are using MySQL version 4.1 or higher and are using MySQL Connector/J 3.0.11 or higher.

Contents

• Connecting to MySQL
  • Configure JDeveloper to work with MySQL Connector/J
  • Creating a connection to a MySQL database
• Creating an ADF BC/MySQL Project
  • Creating a ADF BC/MySQL Workspace and Project
  • Creating an Entity Object
  • Creating a View Object
  • Creating an Association, a View Link, or an Application Module
• Testing an ADF BC/MySQL Project
• Modeling a ADF BC/MySQL Project
• Appendix - Javadoc
  • Creating the Javadoc for MySQL Connector/J

Connecting to MySQL

The first step to working with any database in JDeveloper is to create a connection to the database. JDeveloper uses JDBC to connect to databases, so to connect JDeveloper to your MySQL database, you'll need to configure JDeveloper to use MySQL's JDBC library. First you'll have to configure JDeveloper to work with the MySQL Connector/J JDBC driver, then you'll need to create a connection.

Configure JDeveloper to work with MySQL Connector/J

1. Download MySQL Connector/J 3.0.11 and copy the JDBC driver JAR file to a known location, such as <jdevhome>/jdev/lib
2. From the Tools menu, select Default Project Properties.
3. In the tree view, select Libraries.
4. In the right pane, select New. The Create Library dialog will be displayed.
5. Name the library MySQL JDBC.
6. Set the location to Users Libraries.
7. Click Edit for the Class Path. The Edit Class Path dialog is displayed.
8. Click Add Entry. The Select Path Entry dialog is displayed.
9. Navigate to the JAR file for the MySQL Connect/J JDBC driver (it should be called something like mysql-connector-java-<version>-bin.jar).
10. Click on the jar file and click Select.
11. Click OK.
12. If you have uncompressed the source, do the same for the source path. Defining the source code here will allow you to debug the JDBC driver.
13. If you have access to, or have created the Javadoc, do the same for the Javadoc. Including the Javadoc allows you to view the Javadoc for the JDBC driver.
14. Click OK.
15. The library will be defined, and it will be in the Selected Libraries list. You probably don't want that library to be defined in all your projects, so shuttle it over to the Available Libraries list.
16. Click OK.

Now you can use that library in any of your projects.

Creating a connection to a MySQL database

1. In JDeveloper, click on the Connections tab.
2. Double-click on Database. The Create Database Connection wizard will be displayed.
3. If you see the Welcome page, click next.
4. Define your connection name.
5. From the Connection Type dropdown list, select Third Party JDBC Driver.
6. Click Next.
7. Set the username and password and click deploy password. If you want extra security, you may not want to deploy the password. Read JDeveloper's online documentation for more information about this.
8. Click New for the Driver Class. The Register JDBC Driver dialog will be displayed.
9. In the Driver Class field enter com.mysql.jdbc.Driver.
10. In the Library, select the MySQL JDBC library that you defined earlier. If you didn't define it early, you can now by selecting New and following the same procedure.
11. Click OK.
12. Enter the URL to the database. For example, it might be jdbc:mysql://localhost/test?
13. Add to the URL the following: &ultraDevHack=true&capitalizeTypeNames=true&pedantic=true You may also need to add the parameter &sqlmode=oracle. This parameter forces the JDBC driver to mimic the SQL features in the Oracle 7.x database.
14. Click Next.
15. Click Test Connection. If the connection fails, go back and double check your URL.
16. Click Finish.

In the Connections Navigator, you can click on your connection to see the defined tables, views, etc, in the database browser. For more information on the database browser, or database connections, see JDeveloper's online help.

Creating an ADF BC/MySQL Project

Now that you have a connection defined, it's time to work with ADF BC.

Before running you through the steps, there are three issues you need to be aware of

• MySQL does not support RowID. That means that you cannot use RowID as a replacement for a primary key.
• All tables must have a primary key.
• ADF BC does not automatically find the primary keys on your MySQL tables. That has two side effects. You'll see some errors when creating and modifying BC objects, and you'll have to define your BC primary keys manually. However, these errors should not affect your project.

Creating a ADF BC/MySQL Workspace and Project

1. From the File menu, choose New. The New Gallery will be displayed.
2. Click on Application Workspace. The Create Application Workspace dialog will be displayed.
3. Name the workspace anything you want.
4. Choose Web Application Default from the Application Template.
5. Click OK.

Two new projects (Model and ViewController) will be displayed in the Application Navigator. The Model project is the one that we will have our ADF BC objects.

Normally what I'd tell people to do at this point is to run the Business Components from Tables wizard. Due to the fact that ADF BC doesn't understand the way MySQL defines primary keys in tables, though, we're going to define each ADF BC object manually. This isn't hard, but if you want to create a lot of objects, it can take a bit of time. Minimally, a ADF BC project is composed of one or more VOs and an AM. Often you'll also want one or more EOs, Associations, and View Links.

Creating an Entity Object

1. From the File menu, choose New. The New Gallery will be displayed.
2. Select the Business Components category.
3. In Items, select Entity Object.
4. Click OK. The Business Components Project Initialization dialog is displayed. If this is the first ADF BC object for this project, the project needs to be initialized for ADF BC. You only have to do this once for each project.
   1. Select the connection to your MySQL database.
   2. Set the SQL Flavor to SQL92
   3. Set the Type Map to Java.
   4. Click OK.
   The Create Entity Object Wizard is displayed.
5. If you see the Welcome page, click Next.
6. In the Create Entity Object Name page, select the database schema object you want to use. See JDeveloper's online help if you are unsure of which object to choose.
7. Click Next.
8. The columns defined in your table are shown in the Entity Attributes page. You can remove attributes or add new attributes here. If you see a column called RowID that is not defined in your table, that's due to the fact that ADF BC was not able to find the primary key for that table. Do not create an attribute based on RowId. Click Next.
9. On the Attribute Settings page, select the primary key attribute from your table.
10. Click Primary Key.
11. Make any other changes you want on your attributes, and click Next.
12. On the Java page, create any methods or Impl classes you think you'll need and click Next. If you aren't sure what you need here, read the online help for more information.
13. On the generate page, click Next. Note: you cannot generate a default view object. We'll create a VO next.
14. Click Finish.

Creating a View Object

1. Right-click on the model package and select New View Object.
2. If this is the first ADF BC object for this project, the project needs to be initialized for ADF BC. See To create an Entity Object for information on initializing an ADF BC project.
3. If you see the Welcome page, click Next.
4. Specify a name for your View Object, or accept the default. It helps to associate the View Object name with the Entity Object it will be using. For example, if you have a Departments EO, you probably want the VO name to be DepartmentsView.
5. Click Next.
6. Choose the entity objects you want to associate with the VO and shuttle them into the Selected list.
7. Click Next.
8. Shuttle the appropriate attributes into the Selected list.
9. Click Next.
10. Modify the attributes as needed and click Next.
11. On the Query page, you can modify the SQL query if you need to, and test the query to make sure it's valid. Due to an incompatibility between the queries created by ADF BC and MySQL, if you test an expert query, the query will fail. However, if the query is valid (you can test it using the SQL Worksheet in JDeveloper), you can ignore that error.
12. Click Next.
13. On the Java page, create any methods or Impl classes you think you'll need and click Next. If you aren't sure what you need here, read the online help for more information.
14. Click Next.
15. Click Finish.

There is one other issue related to View Objects. When you are in the VO Editor, clicking on Attributes Mapping page throws an error. This is related to the problem with Expert Mode queries. If you need to modify these queries, do it by modifying the query rather than modifying the attribute mapping.

Creating an Association, a View Link, or an Application Module

There are no special instructions for creating an Association, View Link or an Application Module when working with MySQL. Please see the JDeveloper online documentation for information on creating these types of objects. Remember, every ADF BC application needs an AM, so make sure you create one before you test your application.

Testing an ADF BC Project

You'll want to make sure everything worked, so right-click on the AM, and select Test. When the Browser Connect dialog is displayed, click Connect.

Modeling an ADF BC/MySQL Project

You can use the Business Components modeler with MySQL just like you can with any other database. If you are creating new EOs in the modeler, you will need to set the primary key on the attribute. Also, it will not automatically create associations or view links.

Appendix - Javadoc

Creating the Javadoc for MySQL Connector/J

If you want access to the Javadoc for MySQL Connector/J,

1. Uncompress the MySQL Connector/J archive (not the binary JAR file!) into a known directory, such as mywork. The files will them end up in mywork/mysql-connector-java-<version>.
2. Start JDeveloper.
3. From the File menu, choose New. The New Gallery will be displayed. Click on Workspace. The Create Workspace dialog will open.
4. Name the workspace MySQL Connector.
5. Make sure the create empty project box is checked (it is by default).
6. Click OK. The Create Project dialog is displayed.
7. Call the project Connector.
8. Click OK.
9. Click on the Connector project.
10. From the File menu, select Import. The import dialog will be displayed.
11. Select From Existing Sources and click OK. The Importing Existing Sources wizard will be displayed.
12. Click Next on the Welcome panel if it is displayed.
13. On Step 1 of 1: Add Source Files and Directories, click Add. A file selector dialog will be displayed.
14. In the file selector dialog, navigate to the source that you unarchived (mywork/mysql-connector-java<version>), and select org and click open.
15. Repeat that step for com and lib.
16. Click on Copy Files to Project Directory:. This is a good idea when you are using someone else's code to ensure the integrity of the original code.
17. Click Finish. All the files you selected will be displayed in the Connector project in the Application Navigator.
18. Click on the Connector project,
19. From the Run menu, select Javadoc Connector.jpr
20. The Javadoc for the project will be created. If you want to test it, you can click on View Documentation in the Javadoc message log.

The best way to use the Javadoc is to create a jar file for it. You can do so from the command line by using the jar utility, or you can use JDeveloper's deployment tools.

1. Right click on the Connector project, and select New. The New Gallery will be displayed.
2. In the Categories, select Deployment Profiles.
3. In the Item view, select JAR File (not Client JAR File, or EJB JAR File, or Taglib JAR File) and click OK. The Create Deployment Profile - JAR File dialog will be displayed.
4. Name the deployment profile something like mysql-connector-java--javadoc and click OK. The JAR Deployment Profile dialog will be displayed.
5. In the tree view, select Contributors.
6. In the right pane, deselect everything.
7. Click Add. The Add Contributor dialog will be displayed.
8. Click Browse. The Choose Directory dialog will be displayed.
9. Navigate to mywork/MySQL Connector/Connector/ directory. Select the Javadoc directory.
10. Click OK.
11. Click OK.

In the Application Navigator, you should now see this deployment profile. Now we do the actual deployment.

• Right-click on the deployment profile and select Deploy to JAR.

The deployment log will list where the JAR file was created. It should be something like mywork/MySQL Connector/Connector/deploy/mysql-connector-java-<version>-javadoc.jar. That file will contain all the Javadoc for the MySQL Connector/J library you have. Copy that file somewhere (such as into jdev/lib or mywork/lib). Now you can use the Javadoc for the JDBC driver.

It's very easy to do the same thing for the source. Create a deployment profile just like you did for the Javadoc. In the Contributors pane, select Project Source Path, and deploy that as a JAR file. You will then be able to debug into the JDBC driver if you need, or want, to do so.