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.

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

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.

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).

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.

Comments

Great post! Considering that my site has been 'coming soon' for over 2 years now, good to see you up and running so quickly! :)

Couple of queries:• How would this work if I am NOT using JDeveloper, and want to build the project using Maven?• It would also help if you can add an outline of the tasks and then start detailing them out?

The effectiveness of IEEE Project Domains depends very much on the situation in which they are applied. In order to further improve IEEE Final Year Project Domains practices we need to explicitly describe and utilise our knowledge about software domains of software engineering Final Year Project Domains for CSE technologies. This paper suggests a modelling formalism for supporting systematic reuse of software engineering technologies during planning of software projects and improvement programmes in Final Year Project Centers in Chennai.

Software management seeks for decision support to identify technologies like JavaScript that meet best the goals and characteristics of a software project or improvement programme. JavaScript Training in Chennai Accessible experiences and repositories that effectively guide that technology selection are still lacking.

Aim of technology domain analysis is to describe the class of context situations (e.g., kinds of JavaScript software projects) in which a software engineering technology JavaScript Training in Chennai can be applied successfully

Thanks RS for the comments!Basically files needs to be touched are bc4jxfg.xml and connections.xml files. Your suggestion sounds useful, will update the blog detalining the steps involved while using maven soon. That said, in ideal scenario I would suggest to use JDeveloper to improve the productivity

Great Post. I would like to see other way instead modifing the setDomainEnv.cmd file. I just drop my .jar file in "\Application Data\JDeveloper\system11.1.1.1.33.54.07\DefaultDomain\lib". And it works. It is with integrated server. But when we deploy to the standalon weblogic I don't need to do any extra step if I push my .jar file under /WEB-INF/lib folder. And in project-properties I need to point the .jar from this location when we set up Libraries and Classpath from project-properties.

Hi Jobinesh,I'm new in JDeveloper and I wish to migrate an application. For this reason, I wanted to use the database in MySQL server. I following all the steps in your post but the problem is still there.The message is java.net.MalformedURLException: unknown protocol: c.I added the line: set WEBLOGIC_CLASSPATH=%WEBLOGIC_CLASSPATH%;C:\Users\Diego\AppData\Roaming\JDeveloper\system11.1.1.2.36.55.36\DefaultDomain\lib\mysql-connector-java-5.1.10-bin.jar in the folder setDomainEnv.cmd.Can you help me please. What do I have to do?Thank you so much,

Hi Diego,Have you tried to connect the MySql DB through a normal java class? Please first try just to check everything is fine. The place you have dropped your .jar you supposed to not to get any error. And can you please check the path mentioned by you generally path used to be as below -C:\Documents and Settings\\Application Data\JDeveloper\system11.1.1.2.36.55.36\DefaultDomain.

The path you have mentioed is it properly being pointed by your jDeveloper? Just I had a try in my dev box, even withought setting classpath it is working.Regards,Subu

Pls try the approach suggested by Subu(sounds better,havn't tried personally though). However, to answer the question pls try modifying the path as C:\\somefolder\\some_jar_ file. Googling for this error taken me to this java bug http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=6506304

Sounds like system is not able to find your MS SQLServer driver libraries.Do you able to run the ViewObjects from the AM tester? Error happens only when you run from WLS?AFIAK, there is no need to set libraries anywhere else other than WLS classpath.

Hello. How is everybody? Very nice blog .. This issue in particular I love ... I am a student of business administration career and I feel great pation by all subjects related to my career ... Thanks for the info

Please check http://wiki.oracle.com/page/ADF+Business+Components+on+MySQLcopying the relevant part below-If you want use bind variables then you should use ? and not named bind variables like :1 or :customerId. For example in viewobject impl public void selectById(BigDecimal id, boolean executeQuery){setWhereClause("ID = ?");setWhereClauseParams(new Object[]{id});if (executeQuery) {executeQuery();}}

Hi. Thanks again.I checked website you suggestedbecause i am newbei to this domain. i could not figured out on my own. In Jdeveloper I create wiev object based on query. and in the query tab I am entering lines in the below////////////////////////

SELECT User1.userid, User1.nameFROM database_1.user User1 where User1.userid = ?:1///////////////////////////how should I enter to variables. after the question mark or where ?

HiI define some variables in the SQL query in adf. And I used variables like :var_name. I want to make this variable optional. If user does not enter value to this parameter. My query should understand it. any idea

I'm running Jdeveloper 11g and MySQL on mac, after I follow your steps to add msql-connector-java-5.1.17-bin.jar to Appliation->default project properties -> Library and copy the file to /Users//.jdeveloper/system11.1.2.0.38.60.17/DefaultDomain/lib I can compile and start "run" for the page without any error (Database test connection is success), but when the page load information from DB, I got error " java.lang.ClassCastException: com.mysql.jdbc.JDBC4Connection cannot be cast to oracle.jdbc.OracleConnection" .... same error occur when I run AM tester ...... do I miss anything?

It's really gr8..pls blog the new stuff even if it is smaller.(Lot of people may know BUT NOT ALL).Atleast if the points are small small plese create a new blog category like 'For Beginners'....a lot learning today Jobinesh... :):):)

I am using MySQL for my ADF application,and trying to run AM, but getting the error as "No suitable driver found for jdbc:mysql://localhost:3306/dbname".Please advice where I am missing.

FYI- In the log file I am getting something as below- even though the username and password is correct![1135] Trying connection/3: url='jdbc:mysql:/*****' user='username'password='*****' ...[1136] DBTransactionImpl.initTransaction: Login failed