Sunday, March 30, 2008

Flex drag and drop tree with blazeds in JDeveloper 10.1.3

In this blog I will show you how you can make a custom Flex drag and drop tree in any jsf page where you can drag for example an employee to another department. If you drag an employee to an another department then flex component gives the remote object
method the new department name and with the help of blazeds and ADF BC4J I can update the employee with the new department id. To make the flex tree more flashy, I added a search bar, collapse and expand all button and a tree item click function. Other
features are, a special icon if the employee is a manager and more important you can only drag an employee to a department not between the departments. The departments
employees tree look like this but you can easily customize it. In JDeveloper 11G there is support for dnd ( see my previous blog ) but within in tree and with transactions it can be very complex. Here some pictures.

For using blazeds with jdeveloper and Adobe Flex see my previous blog
This is how the xml looks like which I pass as document ( from the remote object to the flex component). The tree component uses this xml to build the tree. With the type attribute I know what type the tree items is and the label is show in the tree.
To make this work I had to xalan library to the project and put it on the top of the needed libraries, so the xmlparserv2 lib is not loaded first.

This is the needed configuration for blazeds, These xml's are located in WEB-INF/flex/



Now we can work on the java side. It has three methods, the first (getDept) is to generate the xml for the tree. The second (setNewDept) is update the employee with the new department and the last (showCurrent) is when the user click on a tree item.

public Document getDept() {

Document xmldoc= new DocumentImpl();

Element e = null;
Element ee = null;

// Root element.
Element root = xmldoc.createElementNS(null, "root");
root.setAttributeNS(null, "type", "root");

DeptViewImpl view = am.getDeptView();
while (view.hasNext()) {
DeptViewRowImpl row = (DeptViewRowImpl);
e = xmldoc.createElementNS(null, "department");
e.setAttributeNS(null, "id" ,row.getDeptno().toString() );
e.setAttributeNS(null, "label",row.getDname());
e.setAttributeNS(null, "type", "dept");

RowIterator empRows = row.getEmpView();
while (empRows.hasNext()) {
EmpViewRowImpl row2 = (EmpViewRowImpl);
ee = xmldoc.createElementNS(null, "employee");
ee.setAttributeNS(null, "id" ,row2.getEmpno().toString() );
ee.setAttributeNS(null, "label",row2.getEname());
ee.setAttributeNS(null, "type" ,"emp");
// check is the employee is a manager
RowIterator empRows2 = row2.getManagerEmpView();
if ( empRows2.hasNext() ) {
ee.setAttributeNS(null, "icon" ,"myManagerIcon");

return xmldoc;

public Document setNewDept(Integer department, Integer employee) {
System.out.println(" new department "+ department.toString()+ " employee "+employee.toString());
EmpViewImpl view = am.getEmpView();
EmpViewRowImpl row = (EmpViewRowImpl);
row.setDeptno(new Number(department.intValue()));
return getDept();

public void showCurrent( Integer id, String type) {
System.out.println("type "+type+" id "+id.toString());

Now we can make a flex project. In this are three important function the first is dragOverHandler, this enabled or disables the drop of the employee. The second is the dragDropHandler, this function handles the drop and calls the remote object to updat e the employee and the last is the dragEnterHandler which stores the dragged employees.

At last we can add the object to jsf page

Here are the sources to build your own dnd tree.

Monday, March 24, 2008

Flex jms client with OC4J Queue ( blazeds)

In this blog I will show what you have to do to send and receive jms text messages in flex. For this I use the embedded container of jdeveloper with the blazeds libraries and the blazeds configuration xml files( this is very easy, how to do this see this blog). I also use the j2ee container of jdeveloper so I can use the jms queues and do the rmi jndi lookup. First we create a queue in the oc4j container. I will do it manually by editing the jms.xml in the j2ee\home\config folder. Here we add a queue and a queue connection factory.
<queue name="MyJMSQueue" location="jms/MyJMSQueue">
<queue-connection-factory location="jms/MyJMSQCF"/>
Now you can start the j2ee server and use this java code to test the queue. You have to add a lot of oc4j libraries (located in j2ee\home\ ) to get rmi jndi lookup standalone working.

package jms2;

import java.sql.Timestamp;
import java.util.Properties;
import javax.jms.JMSException;
import javax.jms.Queue;
import javax.jms.QueueConnection;
import javax.jms.QueueConnectionFactory;
import javax.jms.QueueReceiver;
import javax.jms.QueueSender;
import javax.jms.QueueSession;
import javax.jms.Session;
import javax.jms.TextMessage;

import javax.naming.Context;
import javax.naming.InitialContext;

public class jmsclientoc4j {

private QueueConnection connection = null;
private QueueSession session = null;
private QueueSender sender = null;
private QueueReceiver receiver = null;
private Queue queue = null;
private long waitTime = 0;

public jmsclientoc4j() {


public static void main(String[] args) {
new jmsclientoc4j().execute();

public void tearDown() {
try {

} catch (JMSException je) {
} finally {

public void execute() {

public void get(){

try {
javax.jms.TextMessage textMessage = (javax.jms.TextMessage)receiver.receive();
System.out.println("Receiving message [" + textMessage.getJMSMessageID() + "] enqueued at " + new Timestamp(textMessage.getJMSTimestamp()).toString());
String xmlText = textMessage.getText();
} catch (JMSException jmse) {

public void put(){

String messageId = null;
String xmlData = "1111";
try {
TextMessage message = session.createTextMessage(xmlData);
} catch (JMSException jmse) {

protected void setUp() {

// Haal instellingen voor de verbinding op
String queueName = "jms/MyJMSQueue";
String queueConnectionFactoryName = "jms/MyJMSQCF";
Context ctx;

try {
Properties parm = new Properties();

ctx = new InitialContext(parm);

QueueConnectionFactory connectionFactory =

connection = connectionFactory.createQueueConnection();
session = connection.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
queue = (Queue)ctx.lookup(queueName);
sender = session.createSender(queue);
receiver = session.createReceiver(queue);

} catch (JMSException je) {
throw new RuntimeException("Fout opgetreden bij het starten ",
} catch (Throwable t) {
throw new RuntimeException("Fout opgetreden bij het starten ",


Here are the blazeds configurations files, these files are located in public_html/WEB-INF/flex/. For jms we need services-config.xml for the channel defitions. You can define two jms channels one for streaming, this mean blazeds is continually connected to the queue, the other polls the queue every few seconds.
The second blazeds configuration xml is the messaging-config.xml file. In this file we define the jms adapter.
here is the service-config.xml

Here is messaging-config.xml with the jms configuration

Now we are raedy with the java side now we can go to the flex builder. first we create the flex project. Follow the pictures belows

The jms producer and consumer mxml looks like this. Here we can send text messages to the queue and the consumer automatically receive these messages and displays it on the screen.

Here is the result.

Friday, March 21, 2008

MySQL with BC4J need to knows

If you want to use MySQL with BC4J and you are only familiar with Oracle databases then you should know the following. Read for all MySQL details my previous blog over MySQL too. If you know more tricks how you can use mysql with bc4j let me know.
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) {

MySQL does not know sysdate you have to use Now()
select A.text from A where A.date_from > now()

In Oracle SQL you can outer join a table bij adding (+) at the right place in the where clause. In MySQL you have to use Left join or Right Join. For example
select A.text
, B.text
, C.text
from A left join B on B.ID = A.B_ID
, C
where A.C_ID = C.ID

If you have a date as column type in MySQL table then BC4J generates the entity attribute as string. You have to change this to timestap.

If you have a blob as table data type then BC4J uses a string as java type. You can change this to BLOBdomain but this doesn't work. You have to program a workaround here some which you can use to store a blob and how to retrieve.

public BigDecimal putBlobData (File file) throws FileNotFoundException,
IOException {
BigDecimal blobId = null;
PreparedStatement stmt = applicationModule.getDBTransaction().createPreparedStatement("SELECT last_insert_id()",1);
try {
Connection conn = stmt.getConnection();
stmt = conn.prepareStatement("insert into Blobdata(BLOBDATA,CRE_USER_CODE,CRE_DT) values (?,?,?)");
InputStream isFile = new FileInputStream(file);
stmt.setBinaryStream(1,isFile, (int)(file.length()));
stmt.setString( 2,"mhs");
stmt.setDate( 3,new Date(new java.util.Date().getTime()));
int count = stmt.executeUpdate();
stmt = conn.prepareStatement("SELECT last_insert_id()",1);
ResultSet rs = stmt.getResultSet();
if ( rs != null ){
blobId = rs.getBigDecimal(1);

} catch ( SQLException e) {
} finally {
return blobId;

To retrieve a blob use this

public static InputStream getBlobInputStream(BigDecimal id, MhsServiceImpl service){

String sqlQuery = "SELECT blobdata FROM blobdata WHERE id = ?";
byte[] bytes = null;
String description = "";
ResultSet rs = null;
Blob blob = null;
InputStream is = null;

PreparedStatement stmt = service.getDBTransaction().createPreparedStatement("SELECT last_insert_id()",1);
try {
Connection conn = stmt.getConnection();
stmt = conn.prepareStatement(sqlQuery);
rs = stmt.executeQuery();
ResultSetMetaData md = rs.getMetaData();
while ( {
blob = rs.getBlob("blobdata");
is = blob.getBinaryStream();


} catch(SQLException e){
return is;


Wednesday, March 19, 2008

Dynamic Adobe Flex Menu based on the Taskflow menu xml

In this blog I will show you how you can use Adobe Flex to create a flash menu which you can use in your JDeveloper 11g taskflow application. You only have to add this flash component to the jsf template. The Flash menu read the taskflow menu (root_menu.xml) and the adfc-config.xml file and generates the menu. You can the change the menu xml and the flash menu adapts to it.
To get this working I need to read the adfc-config.xml too to know which url is connected with an action.
How you can make a taskflow menu in JDeveloper 11g see my previous blog

With Flex you are very flexible to design you own menu or tabs. There are a lot of examples how you can archieve this. In this blog I made two menu's . The first is a menubar (horizontal ) and the second is a vertical menu.
Here are some pics but you can make much shinier and glamorous. In my case I used different icons for group and menuitems. Here you see the menubar

This is the vertical menu

This is how it default looks with JDeveloper. With Flex you make tabs yourself with all the special features like an icon in the tab , hoovering , transparancy etc.

This is an example of the root_menu.xml generated with JDeveloper and I changed it a little bit to make it more complex.

<?xml version="1.0" encoding="windows-1252" ?>
<menu xmlns="">
<groupNode id="gn1" idref="itemNode_page1" label="menu1">
<itemNode id="itemNode_page1" label="label_page1" action="adfMenu_page1" focusViewId="/page1"/>
<groupNode id="gn2" idref="itemNode_page2" label="menu2">
<itemNode id="itemNode_page2" label="label_page2" action="adfMenu_page2" focusViewId="/page2"/>
<itemNode id="itemNode_page3" label="label_page3" action="adfMenu_page3" focusViewId="/page3"/>
<groupNode id="gn3" idref="itemNode_page4" label="menu3">
<itemNode id="itemNode_page4" label="label_page4" action="adfMenu_page4" focusViewId="/page4"/>

In the JDeveloper project I had to make two servlets which returns the root_menu.xml and the adfc-config.xml to the flash component because these file are located in the WEB-INF where I can't download them.

Now we can make a new project in Flex Builder 3. The result swf we have to put in the ViewController\public_html folder. Now we can add the flash object to the jsf template

<object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000"
id="jdeveloper_flex_menu" width="400" height="175"
<param name="movie" value="jdeveloper_flex_menu.swf" />
<param name="quality" value="high" />
<param name="bgcolor" value="#869ca7" />
<param name="allowScriptAccess" value="always" />

These are the steps I did in flex. The first step is to read the xml's and generate a new flex menu xml. For every menuitem in the root_menu.xml I had to read the adfc-config to find the real url of this menuitem. The last step is to generate the menu's with the new menu xml. I also added an event to the menuitems so I know when an user a menuitem cliks so I can redirect them to the right place.

The flex mxml looks like this.

<?xml version="1.0" encoding="utf-8"?>
<mx:Application xmlns:mx=""
layout="absolute" creationComplete="init();" width="400" height="175">

import mx.controls.Menu;
import mx.collections.*;
import flash.xml.XMLDocument;
import flash.xml.XMLNode;
import flash.xml.XMLNodeType;

private var myXml:XML;
private var myXml2:XML;
private var rootMenuXml:XML;
private var adfcconfigXml:XML;
private var afdcconfig:XMLDocument = new XMLDocument();
private var weburl:String = "http://xpcnd7010xmp:8989/flex_menu-ViewController-context-root/";

public var menuBarCollection:XMLListCollection;
public var weburlMenu:String = weburl+"menu";
public var weburlAdfc:String = weburl+"adfc";

private function resultHandler(event:ResultEvent):void {
rootMenuXml = event.result as XML;
private function resultHandler2(event:ResultEvent):void {
adfcconfigXml = event.result as XML;

public function init():void{

// main menu icon
public var main:Class;

// menuitem icon
public var item:Class;

// parse menu
private function parseMenuXML(node:XMLNode):XMLDocument {
var mainXML:XMLDocument = new XMLDocument();
var rootXML:XMLNode = mainXML.createElement("root");

var menuItems:Array = node.childNodes;
for each(var item:XMLNode in menuItems) {
parseMenuItemXML(item, mainXML,rootXML);
return mainXML;
// parse menu items
private function parseMenuItemXML(node:XMLNode, mainXML:XMLDocument, element:XMLNode):void {
var elementXML:XMLNode = mainXML.createElement("menuitem");
elementXML.attributes.label = node.attributes.label;

if (node.nodeName=="groupNode" ) {
elementXML.attributes.icon = "main";
} else {

trace(node.attributes.label + " " +node.attributes.action);
elementXML.attributes.icon = "item";

var action:String = findAction(node.attributes.action,afdcconfig)
if (action!="empty" ) {
var url:String = findActionUrl(action,afdcconfig)
if (url!="empty" ) {
elementXML.attributes.url = url;

var menuItems:Array = node.childNodes;
for each(var item:XMLNode in menuItems) {
parseMenuItemXML(item, mainXML, elementXML);

// find the action so we can use this to find the page url
private function findAction(action:String, afdc:XMLDocument):String {
var menuItems:Array = afdc.childNodes;
for each(var item:XMLNode in menuItems) {
if (item.hasChildNodes()) {
var menuItems2:Array = item.childNodes;
for each(var item2:XMLNode in menuItems2) {
if ( item2.nodeName == "control-flow-rule" ){
var menuItems3:Array = item2.childNodes;
for each(var item3:XMLNode in menuItems3) {
if ( item3.nodeName == "control-flow-case" ){
var menuItems4:Array = item3.childNodes;
var item4:XMLNode = menuItems4[0];
var item5:XMLNode = menuItems4[1];
if ( item4.firstChild.nodeValue == action ) {
return item5.firstChild.nodeValue
return "empty";

// find the url of the page
private function findActionUrl(action:String, afdc:XMLDocument):String {
var menuItems:Array = afdc.childNodes;
for each(var item:XMLNode in menuItems) {
if (item.hasChildNodes()) {
var menuItems2:Array = item.childNodes;
for each(var item2:XMLNode in menuItems2) {
if ( item2.nodeName == "view" ){
if ( == action ) {
return item2.firstChild.firstChild.nodeValue
return "empty";
// main function
private function extractMenu():void {

// load the menu xml
var result:XMLDocument = new XMLDocument();
result.ignoreWhite = true;

// load action xml
afdcconfig.ignoreWhite = true;

// parse the menu xml and find the action url's
var result2:XMLDocument = parseMenuXML(result.firstChild);
myXml = new XML(result2);

// prepare the xml for the vertical menu
menuBarCollection = new XMLListCollection(myXml.children());

// show the vertical menu

private function createAndShow():void {
var myMenu:Menu = Menu.createMenu(null, myXml, false);
myMenu.iconField="@icon";, 0);
myMenu.addEventListener("itemClick", menuHandler);

// Event handler for the Menu control's change event.
private function menuHandler(event:MenuEvent):void {
trace("URL to open::"+event.item.@url);
var urlReq:URLRequest = new URLRequest(weburl+"faces"+event.item.@url);
navigateToURL(urlReq, "_self");


<mx:HTTPService id="service" url="{weburlMenu}"
result="resultHandler(event)" resultFormat="e4x" showBusyCursor="true" />

<mx:HTTPService id="service2" url="{weburlAdfc}"
result="resultHandler2(event)" resultFormat="e4x" showBusyCursor="true" />

<mx:MenuBar id="bar" dataProvider="{menuBarCollection}"
iconField="@icon" labelField="@label" x="0" y="0"
useHandCursor="true" buttonMode="true"
itemClick="menuHandler(event)" />

Download the jdeveloper 11g project here and here is the source code of flex
To make it work on your on pc change the weburl in the flex source and change the path of the root_menu.xml and adfc-config in the two servlets and it should work

Saturday, March 15, 2008

Flex random image panels

For one of the website I develop with dreamweaver, I want to have a image bar with 3 cars, but this is not so difficult but I had some demands how this should work. The images of each car has to change every few seconds and it has to be random. When you click on one of the images and it has to redirect you to the right car page. The last thing I want is that the configuration of the images as to be done in a xml.
This is the power of Flex, you can make this in a few hours , an expert within a hour. Here is a picture of the result or go to the website and see it in action

Here is an example of the xml I use. I have a car element with the images as child

Now let's look at the flex application xml. Globally this is what happens, I retrieve the xml and transform it to objects, start the timer which refresh the images. Last step is to add the code for redirecting to the right webpage when you click on an image.
This is code is not so dynamic, please update it and inform me about the changes.

Here you can download the project.

Tuesday, March 11, 2008

Flex app which call a remote java object in JDeveloper ( blazeds )

In this blog I will show you how you can create a simple Adobe Flex application which get the data from java objects which runs in the embedded oc4j container. For this I create a simple webapp where I use the blazeds libraries and configuration files. Then I can just start the webapp in the embedded container, open the flash application and see the results.

We have to create in JDeveloper a new application with no project. Then we create in this workspace a new web project. In this web project we copy the libraries of the blazeds.war to the lib folder ( WEB-INF/lib ). Do the same with the flex configuration files ( flex folder) and copy these files to WEB-INF/flex. I only use remoting-config.xml for defining the java objects and the services-config.xml for configuring the type of service and logging.
the web.xml of the web application must look like this. Where the listener and the messagebroker configuration are needed for flex.

<?xml version = '1.0' encoding = 'windows-1252'?>
<web-app xmlns:xsi="" xsi:schemaLocation="" version="2.4" xmlns="">
<display-name>Flex data</display-name>
<description>Flex data</description>

<!-- Http Flex Session attribute and binding listener support -->

<!-- MessageBroker Servlet -->




Then I created with BC4J a simple method which retrieves the employees of the scott schema and returns it as a List. Too bad I can not use the RowImpl of the employees viewobject. So I had to create a simple object.

public class Employees {
public Employees() {


public List getEmployees() {

System.out.println("called employees");
List list = new ArrayList();
final ScottModuleImpl scott = (ScottModuleImpl)Configuration.createRootApplicationModule("nl.ordina.service.Scottmodule", "ScottModuleLocal");
try {
EmpViewImpl emp = scott.getEmpView();

while (emp.hasNext()) {
EmpViewRowImpl row = (EmpViewRowImpl);
Employee empRecord = new Employee();

} finally {
Configuration.releaseRootApplicationModule(scott, false);
return list;
In the WEB-INF/flex/remoting-config.xml we define the remote object name and the scott employees class.

<?xml version="1.0" encoding="UTF-8"?>
<service id="remoting-service"

<adapter-definition id="java-object" class="" default="true"/>

<channel ref="my-amf"/>

<destination id="Employees">
<channel ref="my-amf"/>

In the WEB-INF/flex/services-config.xml I define the remote objects file, the channel definition and the logging.

<?xml version="1.0" encoding="UTF-8"?>

<service-include file-path="remoting-config.xml" />
<channel ref="my-amf"/>

<channel-definition id="my-amf" class="mx.messaging.channels.AMFChannel">
<endpoint url="http://{}:{server.port}/{context.root}/messagebroker/amf" class="flex.messaging.endpoints.AMFEndpoint"/>

<target class="flex.messaging.log.ConsoleTarget" level="Debug">
<prefix>[Flex] </prefix>

Now we are ready to start with Adobe Flex builder 3. Here we create a new project.

We have to select the application server type. In our case J2EE and check the option Use remote object access service.
Then next step is to define the j2ee server path with the root folder of the webapp. This is in my case public_html folder. In this folder Flex creates a debug and release build folder for the flash binairy. And you have to define the url of the webapp.

The last step is to create the mxml application file. It looks like this

In the mx:RemoteObject we use the destination which we defined in remote-config.xml In mx:DataGrid we define the java method in my case is that getEmployees dataProvider="{srv.getEmployees.lastResult}"
Now we can run the flex application and it looks like this

Friday, March 7, 2008

deploy BlazeDS on OC4J ( Oracle AS)

With BlazeDS you can exchange data from your backoffice systems to the Flex clients, This is an opensource solution. Previously you had to have Adobe LiveCycle to archieve this. Adobe provides a BlazeDS Tomcat turnkey solution. This turnkey contains with BlazeDS, DS-console and Samples webapps. They also included a hypersonic database for the provided samples.
But you can also use the OC4J Container as application server. For this you have to download a OC4J 10.1.3 container or Oracle AS and download the BlazeDS turnkey solution. We need this turnkey for the webapps and the example database.

in the OC4J\j2ee\home\applications folder create 3 folders named blazeds, ds-console and a samples folder and create in each folder a META-INF folder. In these folder we will create an application.xml. Now we can copy blazeds folder from tomcat\webapps to OC4J\j2ee\home\applications\blazeds. You wil get the following folder structure
Do the same for the ds-console and samples folder.
Now we can create 3 application.xml and put this to the right META-INF folder.

<?xml version="1.0"?><!DOCTYPE application PUBLIC "-//Sun Microsystems, Inc.//DTD J2EE Application 1.2//EN" ""><application> <display-name>Oracle generated application blazeds</display-name> <module> <web> <web-uri>blazeds.war</web-uri> <context-root>blazeds</context-root> </web> </module></application>

ds-console application.xml
<?xml version="1.0"?><!DOCTYPE application PUBLIC "-//Sun Microsystems, Inc.//DTD J2EE Application 1.2//EN" ""><application> <display-name>Oracle generated application blazeds</display-name> <module> <web> <web-uri>ds-console.war</web-uri> <context-root>ds-console</context-root> </web> </module></application>

samples application.xml
<?xml version="1.0"?><!DOCTYPE application PUBLIC "-//Sun Microsystems, Inc.//DTD J2EE Application 1.2//EN" ""><application> <display-name>Oracle generated application blazeds</display-name> <module> <web> <web-uri>samples.war</web-uri> <context-root>samples</context-root> </web> </module></application>

Now we can change the server.xml of the OC4J container (config folder) where we add these lines

<application name="blazeds" path="../applications\blazeds.ear" parent="default" start="true" /> <application name="ds-console" path="../applications/ds-console/" parent="default" start="true" /> <application name="samples" path="../applications/samples/" parent="default" start="true" />

Add the following lines to the default-web-site.xml ( Config folder)

<web-app application="blazeds" name="blazeds" load-on-startup="true" root="/blazeds" /> <web-app application="ds-console" name="ds-console" load-on-startup="true" root="/ds-console" /> <web-app application="samples" name="samples" load-on-startup="true" root="/samples" />

Move the Hypersonic database ( blazeds_turnkey_3-0-0-544\sampledb ) to an other destination and you can delete the turnkey solution.
Now we have to start the OC4J Container with the following option "" Otherwise you get a java.lang.SecurityException error Unauthorized access from application to MBean.
You can add this line in the oc4j.cmd or with Oracle AS in the opmn.xml ( folder OracleAS\opmn\conf ).

Thursday, March 6, 2008

Adobe Flex 3 release

Adobe just released Flex builder 3 SDK and Professional. Professional is an eclipse based developer tool where you can debug and design the user interface. Professional includes support for building desktop applications with Adobe AIR. With Adobe air you can deploy your flex applications to dekstops and on different operating system. The Flex SDK is the open source version and has ant and bat scripts to compile and run the flex applications.

You can download Flex 3 Professional and SDK here

Here are some great links of the possibilities of flex
Flex show case
Flex 3 components explorer
Flex 3 style explorer
Java and Flex
The coming weeks I will build some test applications where I want to build Flex applications on sdo web services and to try to integrate Flex with ADF applications.

Saturday, March 1, 2008

Use MySQL with ADF BC ( BC4j)

In this blog I will explain how you can use MySQL as database in an adf bc web application. You don't have to use a Oracle database with BC4J as model. I use the MySQL Cluster database because the high availability of MySQL is better ( master / slave or make a cluster ) then the Oracle XE database. To have the same options in Oracle you need to have the standard or enterprise database.
There are lot of things you should know if you want to use mysql as database. You have to configure the connection in jdeveloper and configure the datasource in the embedded oc4j. You have to know the difference between oracle and mysql ddl. The last point how to deal with the missing sequence and rowid features in the mysql database.
To make a connection from jdeveloper we have to download the mysql jdbc driver . This driver is called Connector/J. Add this library to the jdeveloper libraries so you can add this to the projects and add this to the embedded oc4j libraries.
Now create a new database connection in jdeveloper. Use com.mysql.jdbc.Driver as driver class. The url is jdbc:mysql://localhost/test where test is the database and localhost is the server where mysql database is installed.

Add the mysql jdbc driver to the libraries of the embedded oc4j container. Go to Tools / Embedded OC4J Container preferences menu item and add the connector/j jar to the libraries. The next step is to add the mysql datasource in the embedded oc4j container. You have to use the datasource for the webapp else you get strange errors. Go to the jdevstudio10133\jdev\system\oracle.j2ee.\embedded-oc4j\config folder and change the datasources.xml.

We have to change to configuration of the bc4j application modules to define the right datasource.

The mysql ddl is a bit different then oracle , I have to use bigint data type for a oracle number data type. Because there is no support for sequence I have to use auto_increment. Here is an example of a oracle and mysql ddl
create table SERVER
ID             NUMBER(10) not null,
NAME           VARCHAR2(60) not null,
HOSTNAME       VARCHAR2(60) not null,
CRE_USER_CODE  VARCHAR2(60) not null,
CRE_DT         DATE not null,

create table SERVER
ID             bigint  not null  AUTO_INCREMENT,
NAME           VARCHAR(60) not null,
HOSTNAME       VARCHAR(60) not null,
CRE_USER_CODE  VARCHAR(60) not null,
CRE_DT         DATE not null,
, primary key(id)

In our model project we can create a new entity on the server table. Mysql does not have the rowid feature so you can not use retrieve after insert or update on the entity attributes. If you do you can get errors after inserting or updating. This is the Oracle way to get the primary key ( if you use triggers to fill in the pk). In mysql you have to commit the transaction and after that you can use last_insert_id to get primary key. Because we don't want the user to fill in the primary key attribute we have to uncheck the mandatory option. The last step is to create our own entityimpl and extend every entity in your to this impl.
We use the create method to fill our default attributes like the user and the current time. The second method is doDml. This is the method where we retrieve the primary and update the primary attribute with this value. If you don't do this you can not update the just created record. You have to requery first and find your just created record. In the doDML we have to execute super.doDML first else there is no commit and you can not retrieve the primary key. After that you can use preparedstatement to execute the following sql SELECT last_insert_id(). Retrieve the results and use setAttribute to update the primary key.
public class MhsEntityImpl extends EntityImpl {
protected void create(AttributeList attributeList) {
setAttribute("CreUserCode", "mhs");
setAttribute("CreDt",new Date(new java.util.Date().getTime()));

protected void doDML(int i, TransactionEvent transactionEvent) {
String  currentViewName = getEntityDef().getName();
super.doDML(i, transactionEvent);      
if ( i == DML_INSERT) {
if (   currentViewName.equalsIgnoreCase("Server")
) {
PreparedStatement stmt = this.getDBTransaction()
.createPreparedStatement("SELECT last_insert_id()",1);
try {
ResultSet rs = stmt.getResultSet();
if ( rs != null ){
} catch ( SQLException e) {

Now we have to extend all the entities to this impl. You can do this by editing the entity and go to Java / class extends and update Row with the new entityimpl

If you have some master detail views in combination with an autonumber primary key column then you can better do the following. Else you need to commit every record.

Create a PK table and a function which gives the latest Keu
CREATE TABLE `pk_keys` (
  `TABLE_NAME` varchar(50) NOT NULL DEFAULT '',
  `TABLE_VALUE` bigint(20) unsigned DEFAULT NULL,

  IF pk_found = 1
    INSERT INTO pk_keys VALUES ( P_TABLE, 1 );
  RETURN pk_value;

The matching EntityImpl
public class MhsEntityImpl extends EntityImpl {
    protected void create(AttributeList attributeList) {
        String  currentViewName = getEntityDef().getName();
        if (       currentViewName.equalsIgnoreCase("Relation")
               || currentViewName.equalsIgnoreCase("Relationship")
        ) {
            PreparedStatement stmt = this.getDBTransaction().createPreparedStatement("select getPkValue('"+currentViewName+"')",1);
            try {
                ResultSet rs = stmt.getResultSet();
                if ( rs != null ){
                  System.out.println("id: "+rs.getBigDecimal(1));
            } catch ( SQLException e) {