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.10.1.3.41.57\embedded-oc4j\config folder and change the datasources.xml.
<br /> <managed-data-source name="MysqlDS" name="test" name="jdbc/MysqlDS"><br /> <br /> <connection-pool name="test"><br /> <br /> <connection-factory class="com.mysql.jdbc.Driver" user="root" password="welcome" url="jdbc:mysql://localhost/test"><br /> <br /> </connection-factory><br /> <br /> </connection-pool><br /> <br />
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

oracle
create table SERVER
(
ID             NUMBER(10) not null,
NAME           VARCHAR2(60) not null,
DESCRIPTION    VARCHAR2(255),
HOSTNAME       VARCHAR2(60) not null,
CRE_USER_CODE  VARCHAR2(60) not null,
CRE_DT         DATE not null,
LAMU_USER_CODE VARCHAR2(60),
LAMU_DT        DATE
)

mysql
create table SERVER
(
ID             bigint  not null  AUTO_INCREMENT,
NAME           VARCHAR(60) not null,
DESCRIPTION    VARCHAR(255),
HOSTNAME       VARCHAR(60) not null,
CRE_USER_CODE  VARCHAR(60) not null,
CRE_DT         DATE not null,
LAMU_USER_CODE VARCHAR(60),
LAMU_DT        DATE
, 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) {
super.create(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")
currentViewName.equalsIgnoreCase("Property")
currentViewName.equalsIgnoreCase("Service")
) {
PreparedStatement stmt = this.getDBTransaction()
.createPreparedStatement("SELECT last_insert_id()",1);
try {
stmt.execute();
ResultSet rs = stmt.getResultSet();
if ( rs != null ){
rs.first();
setAttribute("Id",rs.getBigDecimal(1));
}
} catch ( SQLException e) {
e.printStackTrace();
}
}
}
}
}

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,
  PRIMARY KEY (`TABLE_NAME`)
)

CREATE FUNCTION `get_pk_value`(`P_TABLE` VARCHAR(50))
RETURNS BIGINT
DETERMINISTIC
BEGIN  
  DECLARE pk_value BIGINT DEFAULT 0;
  DECLARE pk_found INT DEFAULT 0;
               
  SELECT 1 INTO pk_found FROM pk_keys WHERE TABLE_NAME = P_TABLE;
               
  IF pk_found = 1
  THEN  
    UPDATE pk_keys SET TABLE_VALUE = (TABLE_VALUE + 1 ) WHERE TABLE_NAME = P_TABLE;
  ELSE
    INSERT INTO pk_keys VALUES ( P_TABLE, 1 );
  END IF;
               
  SELECT TABLE_VALUE INTO pk_value FROM pk_keys WHERE TABLE_NAME = P_TABLE;
               
  RETURN pk_value;
               
END

The matching EntityImpl

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