The purpose of this page is to collect all the knowledge to let ADF BC work on the MySQL database.

Configure the mysql connection in jdeveloper

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 your projects. 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.

<managed-data-source name="MysqlDS" connection-pool-name="test" jndi-name="jdbc/MysqlDS"/>
<connection-pool name="test">
<connection-factory factory-class="com.mysql.jdbc.Driver"
user="root"
password="welcome"
url="jdbc:mysql://localhost/test">
</connection-factory>
</connection-pool>

To use this datasource in the application module we have to change the application module configuration and put in the datasource name "jdbc/MysqlDS"

if your use unicode characters then you need to add the following parameters to the jdbc url jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF8

Using MySQL on different platforms

If you are MySQL on different platforms like windows and unix make sure you think about the case sensitivity else you have to change everything tables and bc4j objects. For more info read this url

MySQL Datatypes

In mysql you don't have a Number you need to use bigint or an other datatype. For all the datatypes see this url
Because there is no support for sequence I have to use auto_increment option on the mysql column. Here is an example of a oracle and mysql ddl

an oracle table

create table SERVER
(
ID NUMBER(10) not null, PRP_ID NUMBER(10) not null,
NAME VARCHAR2(60) not null,
DESCRIPTION VARCHAR2(255),
HOSTNAME VARCHAR2(60) not null,
PROCESSDATE DATE default sysdate not null,
CRE_USER_CODE VARCHAR2(60) not null,
CRE_DT DATE not null,
LAMU_USER_CODE VARCHAR2(60),
LAMU_DT DATE
);
alter table SERVER add constraint SVR_PK primary key (ID);
alter table SERVER add constraint SVR_PRP_FK foreign key (PRP_ID) references PROPERTY (ID);
alter table SERVER add constraint SRV_UK1 unique (NAME);

an mysql table

create table SERVER
(
ID BIGINT not null AUTO_INCREMENT,
PRP_ID BIGINT not null,
NAME VARCHAR(60) not null,
DESCRIPTION VARCHAR(255),
HOSTNAME VARCHAR(60) not null,
PROCESSDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP 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)
, FOREIGN KEY (PRP_ID) REFERENCES PROPERTY(ID)
, UNIQUE KEY (NAME)
);

How to use a sequence

MySQL does not have a sequence like Oracle has, but there is an autonumber functionality on a table column. Mysql does not have the rowid feature so you can not use retrieve after insert or update on the entity attributes to retrieve this autonumber. 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.

Here is an example of the entityimpl

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 you can update the just inserted record or create child records under this master record. 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.

Need to knows

SQL differences

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

outer join
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

BC4J datatypes

date column
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 timestamp.

blobdomain
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.close();
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();
conn.commit();
stmt.close();
isFile.close();
stmt = conn.prepareStatement("SELECT last_insert_id()",1);
stmt.execute();
ResultSet rs = stmt.getResultSet();
if ( rs != null ){
rs.first();
blobId = rs.getBigDecimal(1);
}
stmt.close();

} catch ( SQLException e) {
e.printStackTrace();
} 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);
stmt.setBigDecimal(1,id);
rs = stmt.executeQuery();
ResultSetMetaData md = rs.getMetaData();
while (rs.next()) {
blob = rs.getBlob("blobdata");
is = blob.getBinaryStream();
}

} catch(SQLException e){
e.printStackTrace();
}
return is;
}

Bind variables

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();
}
}

Unicode (UTF8)

if your use unicode characters then you need to add the following parameters to the jdbc url jdbc:mysql://localhost/test?useUnicode=yes&characterEncoding=UTF8

Know problems ( you can get jbo errors where we need to find a solution)

timeout connections problems. Due to inactivity mysql closes the connection, you can change the timeout parameters in mysql