Jboss, Oracle and Entity Beans

I have modified the Simple Entity Bean Example (Nov. 2005 – updated) which used mySQL, to use Oracle instead. I have reused most of the stuff and also used the same directory structure.
I am using jboss-4.0.3SP1, SQL Plus as the Oracle client and Oracle8i, which may happen to be a pretty old version, but still compatible with newer versions of Oracle.
Here are the changes I made to get the Simple Entity Example up and going on Oracle in place of mySQL:
For creating the entity bean, we need to first create a database table. I am using the default Oracle database with username/password: scott/tiger. The driver used is classes12.zip which is still available on the Oracle site (though you may have to register yourself on the site before you proceed).The entity used in this example uses a table called AUTHORS in the default database which has two fields ISBN_CODE and AUTHOR. Here are the scripts to create the table. Run them by logging into SQL Plus with username/ password/hoststring, scott/tiger/mattiz where mattiz is the Oracle sid entered while creating the database.

CREATE TABLE authors (ISBN_CODE varchar2(10) default '0',AUTHOR varchar2(30) default NULL,PRIMARY KEY (ISBN_CODE)) ;

instead of this mySQL query

CREATE TABLE authors (ISBN_CODE varchar(10) NOT NULL default '0',AUTHOR varchar(30) default NULL,PRIMARY KEY (ISBN_CODE)) ;

Note the use of single quote and varchar2 datatype in the Oracle query. Also when a primary key (ISBN_CODE here) is speciifed, you don’t specify NOT NULL, in Oracle.
So other variations of the script would be as follows, though we don’t use them in our example:

CREATE TABLE authors2 (ISBN_CODE varchar2(10) default '0',AUTHOR varchar2(30) not null,PRIMARY KEY (ISBN_CODE)) ;
CREATE TABLE authors3 (ISBN_CODE varchar2(10) default '0',AUTHOR varchar2(30) default 'ANON',PRIMARY KEY (ISBN_CODE)) ;

Also an insert in Oracle for the first query would look like:

insert into authors values('1e','manik');

or

insert into authors (ISBN_CODE, AUTHOR )values('2e', 'garh');

Use of double quotes though permissible in mySQL will not work in SQL client. Anyway using double quotes in SQL scripts is a bad practice.

Name              Null?              Type
----------------------------------------- -------- ----------------------------
ISBN_CODE         NOT NULL           VARCHAR2(10)
AUTHOR                               VARCHAR2(30)

1. Copy the Oracle driver classes12.zip to server/default/lib.
2. Add this entry to login-config.xml in jboss’s conf folder:


<application-policy name="oracleDbRealm">
	<authentication>
		<login-module
			code="org.jboss.resource.security.ConfiguredIdentityLoginModule"
			flag="required">
			<module-option name="principal">admin</module-option>
			<module-option name="userName">scott</module-option>
			<module-option name="password">tiger</module-option>
			<module-option name="managedConnectionFactoryName">
				jboss.jca:service=LocalTxCM,name=oracledbpool
</module-option>
		</login-module>
	</authentication>
</application-policy>


3. Create oracle-ds.xml as follows:


<?xml version="1.0" encoding="UTF-8"?>
<datasources>
	<local-tx-datasource>
		<jndi-name>oracledbpool</jndi-name>
		<connection-url>jdbc:oracle:thin:@localhost:1521:mattiz
		</connection-url>
		<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
		<user-name>scott</user-name>
		<password>tiger</password>
		<security-domain>oracleDbRealm</security-domain>
		<metadata>
			<type-mapping>Oracle</type-mapping>
		</metadata>
	</local-tx-datasource>
</datasources>

Place oracle-ds.xml under server/default/deploy.
1521 is the default oracle port. Also note that “mattiz” used in the connection url is the Oracle sid.
4. Modify jbosscmp-jdbc.xml so that it looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE jbosscmp-jdbc PUBLIC "-//JBoss//DTD JBOSSCMP-JDBC 4.0//EN" "http://www.jboss.org/j2ee/dtd/jbosscmp-jdbc_4_0.dtd">
<jbosscmp-jdbc>
	<defaults>
		<datasource>java:/oracledbpool</datasource>
		<datasource-mapping>Oracle8</datasource-mapping>
	</defaults>
	<enterprise-beans>
		<entity>
			<ejb-name>AuthorsBean</ejb-name>
			<create-table>false</create-table>
			<table-name>Authors</table-name>
			<cmp-field>
				<field-name>isbnCode</field-name>
				<column-name>ISBN_CODE</column-name>
			</cmp-field>
			<cmp-field>
				<field-name>authorName</field-name>
				<column-name>AUTHOR</column-name>
			</cmp-field>
		</entity>
	</enterprise-beans>
</jbosscmp-jdbc>

5. Modify standard-jbosscmp-jdbc.xml under server/default/conf to change the default datasource

<datasource>java:/oracledbpool></datasource>

Now you are ready to go.
Do a build, fire up jboss and start the application just as you did in the simple entity bean example.

About cuppajavamattiz
Matty Jacob - Avid technical blogger with interests in J2EE, Web Application Servers, Web frameworks, Open source libraries, Relational Databases, Web Services, Source control repositories, ETL, IDE Tools and related technologies.

Comments are closed.

%d bloggers like this: