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

Comments

  1. Hi Jobinesh,

    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?

    Rgds,

    R. Saravanan

    ReplyDelete
  2. 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

    ReplyDelete
  3. I followed the blog, at the end i tried to add mysql jdbc to weblogic classpath.

    How to add a line in the classpath,
    do you have any sample code?

    Thank you for your help.

    David

    ReplyDelete
  4. 1. Open setDomainEnv.cmd file located under \Application Data\JDeveloper\system\DefaultDomain\bin.
    2. Append the following entry in the above file

    WEBLOGIC_CLASSPATH=%WEBLOGIC_CLASSPATH%;path to MySQL JDBC driver jar [ e.g: c:\test\mysql-connector-java-5.1.10-bin.jar]

    ReplyDelete
  5. Hey great work!,

    i have trouble with the sap (maxdb) driver. I added the line you told but...

    <27.12.2009 13:35 Uhr MEZ> (ConnectionEnvFactory.java:131)
    at weblogic.jdbc.common.internal.ConnectionPool.initPooledResourceFactory(ConnectionPool.java:646)
    at weblogic.common.resourcepool.ResourcePoolImpl.start(ResourcePoolImpl.java:218)
    Truncated. see log file for complete stacktrace
    >

    ...

    ReplyDelete
  6. Hi Jobinesh,
    Great Post, Thank you.
    I have the same problem, I follow all the steps and the problem is still there.
    Diego Rodriguez.

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

    ReplyDelete
  8. thanks Subu for your input, sounds like a better solution!

    ReplyDelete
  9. 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,

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. 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

    ReplyDelete
  12. 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

    ReplyDelete
  13. On my previous comment i mentioned path as C:\Documents and Settings\\Application Data\JDeveloper\system11.1.1.2.36.55.36\DefaultDomain.

    but it be actualy -
    C:\Documents and Settings\login user\Application Data\JDeveloper\system11.1.1.2.36.55.36\DefaultDomain.

    I used less than and greater than sign around "login user", Hence i feel it has appeared as blank i.e. //.

    ReplyDelete
  14. Hi Jobinesh!

    Your tip was very helpful. I was able to use JDev + ADF with MS SQL Server following that same tip you gave for mySQL!

    I've copied my driver to: "C:\Documents and Settings\Administrator\AppData\JDeveloper\system11.1.1.2.36.55.36\DefaultDomain\lib" and is works fine...

    ...until now. I,ve tried to create a Graph (from ADF Data Visualization), and received the error below:

    "Unable to retrieve data: java.sql.SQLException: No suitable driver found for jdbc:sqlserver://localhost\:1433;databaseName=mydb

    See the JDeveloper log output for additional information"

    Is there any other place in WebLogic or Jdeveloper when I can place my driver in order to graph to work?

    Regards,
    Luciano S. Lorencini

    ReplyDelete
  15. 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.

    ReplyDelete
  16. Jobinesh,

    I've realized that I just need to stop and restart WLS in order to work. My bad :-)

    I've realized that the Graph Preview does not work inside jdeveloper, but works fine in WLS. I just needed to restart the server.

    Thanks for your help!

    ReplyDelete
  17. 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

    ReplyDelete
  18. Thanks a lot for the article... it was very helpful.

    ReplyDelete
  19. I have mysql server and i could not bind variables in sql query. it gives me error. in the demos people did it with just putting ":var_names" into query. how can i do it with mysql

    ReplyDelete
  20. Please check http://wiki.oracle.com/page/ADF+Business+Components+on+MySQL
    copying 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();
    }
    }

    ReplyDelete
  21. Hi. Thanks again.
    I checked website you suggested
    because 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.name
    FROM database_1.user User1 where User1.userid = ?:1
    ///////////////////////////
    how should I enter to variables. after the question mark or where ?

    thanks again

    ReplyDelete
  22. Hi
    I 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

    Thanks in advance

    ReplyDelete
  23. Hi Jobinesh,

    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?

    Thanks in advance.
    Joe

    ReplyDelete
  24. I'm also an ADF developer but after reading your blog, am i really?

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

    ReplyDelete
  25. Hi,

    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

    ReplyDelete

Post a Comment