ADF Business Components With MySQL

Most of the application built on Oracle ADF uses Oracle RDBMS as Database. The driving factor here is to leverage the feature richness of Oracle DB.

Does it mean ADF is tightly coupled with Oracle DB?

Answer is NO, ADF can be well integrated with other databases as well. Small to medium size applications can live even with some light weight databases and they may not require the sophisticated features provided by Oracle DB. MySQL appears to be more popular among the community now a days.

There are quite a lot articles on using ADF with MySQL. But it seems that most of them are outdated or rather ADF improves at lightening speed.

This article can be considered as an extension of How To Use ADF Business Components with MySQL
OK, had enough theory ,now into action.

Setup the basic infrastructure

JDeveloper discussed in this article is based on version 11.1.1.1.0 and MySQL is 5.x or higher.

Apparently, JDeveloper is not packaged with JDBC driver library for MySQL. So let us go and down load it from the MySQL download centre for absolutely free.
Download Connector/J 5.1

The latest version currently available is mysql-connector-java-5.1.7-bin.jar.
Copy this library to a known location such as <jdevhome>/jdev/lib which needs to be supplied at later stage when we setup the application environment.
So we have procured necessary binaries, now let us proceed to setup a typical application environment which uses MySQL Database

Setup the application environment

Create a new application using JDeveloper
1. Go to Appliation Navigator-> Select New,this opens up a Create New Application wizard.
2. Select Fusion Web Application(ADF)template
3. Click Finish.

Now let us try adding the JDBC driver jar for MySQL to the classpath of this application.

1. Form the main menu toolbar, select the Application menu.
2. Select 'Default Project Properties'.

3. In the tree view select "Libraries and Classpath".
4. Click On the "Add Library..." button.
5. From the 'Add Library' dialog, click on New...
6. "Create Library" dialog is displayed. Name the library as MySQL JDBC. Select location as user from the drop down.
7. Click on Add Entry.
8. From 'Select Path Entry' dialog, navigate to the JAR file for the MySQL Connect/J JDBC driver (it should be called something like mysql-connector-java-<version>-bin.jar).
9. Click on the jar file and click Select. We have copied this jar file to /jdev/lib during the infrastructure setup.

10. Click OK on Create Library Dialog.
11. Click OK on Add Library dialog.
12. One more time OK on Default Project Properties dialog.

Create the Connection and verify the resource

1. Go to Application Resources pane. Right click New Connections->Database. Create Database Connection wizard will be displayed.
2. Define your connection name as you like.
3. From the Connection Type dropdown list, select MySQL.
4. Set the username and password and click deploy password.
5. Enter MySQL settings. Enter HostName,DataBaseName and port name as per your MYSQL server configuration.
6. Click on 'Browse' button next to 'Library' field. In the 'Select Library' dialog choose the MySQL JDBC library that you defined earlier ( while setting up the application environment).

7. Click OK on 'Select Library' dialog
8. Click to Test Connection.
9. Click Finish.

Creating Entity Objects

Before staring this step please keep an eye on following two points.

1. MySQL does not support RowID. Implies that you cannot use RowID as a replacement for a primary key.
2. All tables must have a primary key.

Now let us create Entity Objects and View Objects. This is similar to the normal entity creation irrespective of the database
Please refer Fusion Developers Guide for more details.
Anyway, summarizing the steps below for beginners
1. Right Click the model Project , select "New".
2. From the Gallery select "Business components From Table".
3. From the 'Create Business components From the Table' select right schema and click on Query.
4. If all the configurations are correct then we can see the the tables defined in the schema/DB is displayed. Proceed to create Entities and Views and finally press finish.

Congratulations! You have done all the basic setup for staring the application development using MySQL

Setup the Deployment Environment

The above steps are sufficient to run the application from AM Tester. But when you try deploying it, application server may throw following error.

Cannot load the driver : com.mysql.jdbc.Driver weblogic

Solution is add the MySQL JDBC driver jar to Weblogic classpath. Edit setDomainEnv.cmd file under <userhome>\Application Data\JDeveloper\system<version>\DefaultDomain\bin to add mysql-connector-java-<version>-bin.jar