Monday, 4 July 2016

Connecting MySQL database through Oracle ADF/JDeveloper

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,


  1. Oracle is heavy and comes with lots of features. For educational purposes many will stay back from taking the heavy load on their system.
  2. 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.
  3. MySQL is light and installation requires less space and system resources.
  4. 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.

Prerequisite

  1. MySQL Database or MariaDB (I'm using MariaDB)
  2. HR Schema. You can get it from here.
  3. Oracle JDeveloper
  4. MySQL Java Driver. You can obtain from the MySQL Website itself.

Database Installation

  1. You can get MySQL from MySQL website(https://www.mysql.com/). Navigate to the site and download the required version for your system. Similar goes for MariaDB  (https://mariadb.org/)
  2. Install the database
  3. Get the HR Schema from the website mentioned.
  4. From terminal run the sql script
    mysql -t -u <user name> -p <password> < hr_schema.sql
    
  5. Now login to MySQL Database and verify that all the tables are created and data has been populated.

Connect from JDeveloper

  1. Open JDeveloper

  2. Create on New Application and Select Generic Application and provide name 'EmployeeModelApp'
  3. Provide project name 'EmployeeModel' and select ADF Business Components in the Project Technologies tab
  4. Click Finish
  5. Now Right Click on the EmployeeModel Project and click New
  6. Select Business Components from Table and click OK
  7. Now the wizard will ask you to select a connection.
  8. Click on the plus icon.
  9. In this window, you need to provide the details of your database and the appropriate library to connect to the database.
  10. 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.




  11. 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
  12. Now, you will be back to Library Selection Window, click OK
  13. Again you will be back to Database Connection Creation Window, Click on Test Connection button. If you provided everything properly, you will get Success.
  14. 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.
  15. 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.
  16. From Here On, everything goes exactly same as that of using Oracle Connection.
That's all. Hope you've successfully connected to MySQL Database from JDeveloper and your application is running fine.

6 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. good article, i'm very interesting how connect on MariaDb with Jdeveloper.
    I hope you can halp me, meybe the next blog.
    thanks in advance

    ReplyDelete
    Replies
    1. The same way it connects to MySQL. Im fact, I've used MariaDB while writing this article.

      Delete
  3. When using an application built in Jdeveloper with mySql database in a production environment, are there any major performance issues as compared to an Oracle database? I'm worried about how well JDeveloper works with mySql in regards to a growing connection pool, and simultaneous inserts and edits to the same tables from multiple users.

    ReplyDelete
    Replies
    1. Yes, ADF will have performance issue while working with MySQL Database.It is always better to use Oracle Database with ADF.

      Delete
  4. Hence it becomes the foremost duty of an administrator to protect the database by constantly monitoring the functions and look for any loophole that will benefit the attacker.oracle dashboards

    ReplyDelete