Monday, 4 July 2016

Database connection is an important task in any application development. Due to this fact, each and every language has provided useful API to connect to database. In case of pure Java, we have the facility to work with the JDBC API. While JDBC is itself a giant API for Database connection, we get many other tools/API to connect to database from Java application like Hibernate, iBatis, Spring ORM etc.

On the other hand, we have multiple database engines as well like Oracle, MySQL, Microsoft SQL Server, PostgreSQL, IBM DB2 etc.

Connecting with Oracle Database is pretty easy in Oracle JDeveloper and indeed it is very common to use Oracle ADF with Oracle Database. However, many people ask how to connect to MySQL from ADF Applications. There might be different reasons for that. Let's explore why many ask about this feature,

Oracle is heavy and comes with lots of features. For educational purposes many will stay back from taking the heavy load on their system.

Many people use Cloud Hosted MySQL for educational purposes (mostly you can opt for a free account for MySQL hosting). This can save developers from the hassles of installing any database to their system.

MySQL is light and installation requires less space and system resources.

Many people just want to learn ADF and not Oracle Database or they may be expert in MySQL. So, they are reluctant to move to Oracle. So, for them also, this feature may be useful.

With all these considerations, let's see how we can connect to MySQL Database from JDeveloper.

Now login to MySQL Database and verify that all the tables are created and data has been populated.

Connect from JDeveloper

Open JDeveloper

Create on New Application and Select Generic Application and provide name 'EmployeeModelApp'

Provide project name 'EmployeeModel' and select ADF Business Components in the Project Technologies tab

Click Finish

Now Right Click on the EmployeeModel Project and click New

Select Business Components from Table and click OK

Now the wizard will ask you to select a connection.

Click on the plus icon.

In this window, you need to provide the details of your database and the appropriate library to connect to the database.

Provide all the database details and for library selection, click on the Browse button and select User from the left pane and click on New.

Now click on Class Path and click Add Entry button. This will open the explorer to select the MySQL Driver jar. Select the appropriate jar and click OK

Now, you will be back to Library Selection Window, click OK

Again you will be back to Database Connection Creation Window, Click on Test Connection button. If you provided everything properly, you will get Success.

Now Click OK and you will be back to Business Component Initialization Window. Where you can find that SQL Flavor has automatically changes to SQL92. This means that this connection will use Ansi SQL format for all the database operations.

Click OK and you will be directed to Database Object Selection Window. Click on the Query button and you will see the tables and views created by the hr_schema script.

From Here On, everything goes exactly same as that of using Oracle Connection.