How to set up a data source in JBoss3.0 and accessing it fro

[stefankuhn]

Hi!

Im new to JBoss 3.0 (JB) and had some problems accessing a data source (DS) from within my session bean,
that i called from a JSP page. As you will se my problems also originated in som bad luck too.
I started using JBoss porting my already running application from SUN's reference implementation,
having tried out different databases (Interbase and Oracle 9i).
Now that i have got all upp and running, i would like to share my knowledge to all the other
who are strugeling with this kind of problems.

This sample is based on the PostgreSQL (PG) database. Further on, i'm using SuSe Linux 8.0,
but you wil probably have to perform similar steps using another DB and operating systems.


The following lines are a sort of cook book on "accessing a DS via JNDI on JBoss", and i would
like to see some similar lines in the up comming JB 3.0 documentation.


It's convenient to split the task in 3 sub tasks
1. starting the DB
2. the JB-server settings and
3. the application settings



1. Starting the PG DB
It is important to start PG with the -i option. Otherwise you wont be able to connect to the
database using TCP/IP wich is used by a JDBC connection.
You can accieve this by settings in som PG config files too. Please have a look at the rather
good PG documenation how this is done.

2. JBoss Server Settings
2.1 Put the Postgres JDBC-Driver (pgjdbc2.jar) in the [JB-Inst-Dir]/server/default/lib directory
and (re)start JB!
If you put it in the [JB-Inst-Dir]/lib directory the PG JDBC-driver class wont be found
when you try to establisch a connection! (I have tried that out ;-).
(You will get an "Class Not Found" exception in the later case)

2.2 Making the DS accessible using a JNDI name.
This task has to be done in 2 slightly differen ways wether you want to log on to the DB
always using the same user or not.

2.2.1 Making the DS accessible using a JNDI name - Always logging on to the DB with the same user.
Generally you have to put the postgres-service.xml file in the
[JB-Inst-Dir]/server/default/deploy directory to make the DB accessible.
If you always want to log on to the DB with the same user, thats all you have to do!
I wont add the complete files here as the are all over the JB Forum's.
Here im just pointing out the most important stuff.
You will also get this file (similar) when you download the JBoss source code.
Unfortunately i got a buggy one when downloading the jboss-all.zip file.
Never mint that. Just follow this cook book now.

If you put your username (here "nafets") and the password (here "nafets") in the postgres-service.xml
file this user will always be used for log on to the DB.

UKontakt


<config-property name="ConnectionURL" type="java.lang.String">jdbc:postgresql://192.168.115.7:5432/udb2</config-property>
<config-property name="DriverClass" type="java.lang.String">org.postgresql.Driver</config-property>
<config-property name="UserName" type="java.lang.String">nafets</config-property>
<config-property name="Password" type="java.lang.String">nafets</config-property>



It is IMPORTANT(!) that you comment out or delete the line saying:
UKontaktRealm

I have given my DS the JNDI name "UKontakt".

Unfortunatele JBoss won't tell you the JNDI name of the DS when starting!
Use this small trick to get to know the JNDI name of the PG DS.
After starting JB, open postgres-service.xml with a tex teditor "edit a blank" and save it.
Now JB will redeploy the file, telling you the JNDI name of the DS like this:
18:08:40,295 INFO [UKontakt] Bound connection factory for resource adapter 'JBoss LocalTransaction JDBC Wrapper' to JNDI name 'java:/UKontakt'
JBoss is now telling you that UKontakt is bound to the JNDI name java:/UKontakt
Please keep this JNDI name in mind! We will use it later on in this cook book!

If you don't get this message, there is no use going on. You must get the confirmation that the DS is bound
to a JNDI name! That was my problem using the buggy postgres-service.xml file!
I never got the bind message shown above. Unfortunately the JBoss Agent View (on Port 8082) told
me that is was bound!?!?

Now the DS is ready to use. Your Java code, making the connection, will look somethis like this:

java.sql.Connection DBConnection;
try {
Context jndiContext = new InitialContext();

javax.sql.DataSource DBDataSource = (javax.sql.DataSource) jndiContext.lookup("java:comp/env/jdbc/UKontaktDB");

DBConnection = DBDataSource.getConnection();
...

Never mind the lookup statement now. Pease note, that there are no arguments given to the
InitialContext() constructor! Compare this with the one in the next chapter (2.2.2)

2.2.2 Making the DS accessible using a JNDI name - Logging on to the DB with different users.
I must admitt, that i havent tried this out my selves, but it has to work this way.

In ADDITION to the postgres-service.xml file, you now have to create the login-config.xml file too.
This file has to be placed in the [JB-Inst-Dir]/server/default/conf directory.

First of all you have to uncoment/add the row already mentioned in chapter 1.2.1 above.
You now have to state, that you want to use a separate login module usung the line
UKontaktRealm
placed above the XML block where you state the JNDI name of your DS.

This line tells, that there is a block of "logon data", named UKontaktRealm in the file
login-config.xml.

My log on data in the file login-config.xml looks like this:
<application-policy name = "UKontaktRealm">

<login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule" flag = "required">
<module-option name = "principal">nafets</module-option>
<module-option name = "userName">nafets</module-option>
<module-option name = "password">nafets</module-option>
<module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=UKontaktDS</module-option>
</login-module>

</application-policy>

Your Java code, making the connection, will look somethis like this:
(I have not tried this out as i have a lot of better things to do than writing JBoss doku. ;-)

java.sql.Connection DBConnection;
try {
Properties properties = new Properties();
properties.put(Context.INITIAL_CONTEXT_FACTORY, bla bla bla);
properties.put(Context.PROVIDER_URL, bla bla bla);
properties.put(Context.SECURITY_PRINCIPAL, "nafets");
properties.put(Context.SECURITY_CREDENTIALS, "nafets");

Context jndiContext = new InitialContext(properties);

javax.sql.DataSource DBDataSource = (javax.sql.DataSource) jndiContext.lookup("java:comp/env/jdbc/UKontaktDB");

DBConnection = DBDataSource.getConnection();
...

Pease note, the argument given to the InitialContext() constructor!
Compare this with the one in the previous chapter (2.2.1)


3. Application settings
Now that the DS is ready to use, you have to make the appropriate deployment settings for your application.
We split this task in 2 sub tasks.
Writing the deployment descriptor for your beans (ejb-jar.xml) and the deployment descriptor that maps the
coded names (names used in your code to access DS, beans and so on) to JNDI names (jboss.xml).

Hint:
The file jboss-web.xml does the same jobb as the jboss.xml when packing a WAR using the deployment
descriptors jboss-web.xml and web.xml accordingly.

3.1 The bean deployment descriptor - ejb-jar.xml
For every bean using the DS you have to define a <resource-ref> like this

<resource-ref>
<res-ref-name>jdbc/UKontaktDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

As you remember from chapter 2.2.1 and 2.2.2, i look'ed up the DS using the code line
jndiContext.lookup("java:comp/env/jdbc/UKontaktDB");

The name passed to the lookup() funktion has to be stated in the deployment descriptor
as shown above!

Why you have to use "java:comp/env" in the lookup()-funktion is described
on page 120 in the book "JBoss - Administration and Development".
Please note, that this book is not covering JB3.0!

Now how on earth do i get my DS using the lookup() name "java:comp/env/jdbc/UKontaktDB" ???

Did you remember the JNDI name that JBoss stated when redeploying the file postgres-service.xml?
(See chapter 2.2.1 above)
JBoss stated that the DS UKontakt was bound to the JNDI name java:/UKontakt

This is where the file jboss.xml come in.

3.2 The deployment descriptor maping coded names to JNDI names - jboss.xml
The structure of the file jboss.xml is very easy.
The important part of my file looks like this:


<ejb-name>LogInEJB</ejb-name>
<jndi-name>ejb/Login</jndi-name>
<resource-ref>
<res-ref-name>jdbc/UKontaktDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<jndi-name>java:/UKontakt</jndi-name>
</resource-ref>


As you can see here, the coded name "jdbc/UKontaktDB" - used in the lookop()-funktion,
is maped to the JNDI name "java:/UKontakt" that JBoss spitted out redeploying the
postgres-service.xml file.



You are all welcome to correct me, if something i have writte here is wrong.
You are also welcome to add information to this theme.

Have fun!
Rgds
Stefan


PPS. Thanks to all of you answering my questions!!!

[cimetgiu]

Thank you for this exaustive example, it's right, I have use it for setting up a Oracle DB in my application.

I have a question, if you can help me:
I call from a session EJB another session EJB and it had to connecting at the db.
The exception when I execute the ejb is:
'919 ERROR [STDERR] Caused by: javax.ejb.EJBException: null; CausedByExc
eption is:
Wrong credentials passed to getConnection!; - nested throwable: (javax.r
esource.ResourceException: Wrong credentials passed to getConnection!)'

and much more exception.

The first ejb call the same db and read a table without problems:
"
try {
con = this.getConnection();
//errore se la connessione non riesce...
ps = con.prepareStatement("select AS0034_CODICEFISCALE from AS0069 WHERE AS0069_USERNAME=?");
ps.setString(1,Login);
result = ps.executeQuery();
while(result.next()) {
System.out.println("CodiceFiscaleUtente:"+result.getString(1));
this.CodiceFiscaleUtente=result.getString("AS0034_CODICEFISCALE");
}
} catch (SQLException sql) {
throw new EJBException(sql);
}
finally {
try {
if (result!=null) result.close();
if (ps!=null) ps.close();
if (con!=null) con.close();
} catch (SQLException sql){
sql.printStackTrace();
}
}



private Connection getConnection()throws SQLException {
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:/OracleDS");
return ds.getConnection(Login,Password);
} catch (NamingException ne) {
throw new EJBException(ne);
}
}
"

I have use the same statement in the second ejb too but it didn't work!!
the problem could be that the second ejb is called by another ejb???I had to do something???

Thanks.

[cimetgiu]

Thank you for this exaustive example, it's right, I have use it for setting up a Oracle DB in my application.

I have a question, if you can help me:
I call from a session EJB another session EJB and it had to connecting at the db.
The exception when I execute the ejb is:
'919 ERROR [STDERR] Caused by: javax.ejb.EJBException: null; CausedByExc
eption is:
Wrong credentials passed to getConnection!; - nested throwable: (javax.r
esource.ResourceException: Wrong credentials passed to getConnection!)'

and much more exception.

The first ejb call the same db and read a table without problems:
"
try {
con = this.getConnection();
//errore se la connessione non riesce...
ps = con.prepareStatement("select AS0034_CODICEFISCALE from AS0069 WHERE AS0069_USERNAME=?");
ps.setString(1,Login);
result = ps.executeQuery();
while(result.next()) {
System.out.println("CodiceFiscaleUtente:"+result.getString(1));
this.CodiceFiscaleUtente=result.getString("AS0034_CODICEFISCALE");
}
} catch (SQLException sql) {
throw new EJBException(sql);
}
finally {
try {
if (result!=null) result.close();
if (ps!=null) ps.close();
if (con!=null) con.close();
} catch (SQLException sql){
sql.printStackTrace();
}
}



private Connection getConnection()throws SQLException {
try {
InitialContext ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:/OracleDS");
return ds.getConnection(Login,Password);
} catch (NamingException ne) {
throw new EJBException(ne);
}
}
"

I have use the same statement in the second ejb too but it didn't work!!
the problem could be that the second ejb is called by another ejb???I had to do something???

Thanks.

[cimetgiu]

I have solved a step of my problem:
the first ejb open and close a connection with a login and password and than call another ejb.
this second ejb open a connection but it had to connect with the same login and password of the first ejb, elsewhere there is an exception.

Now: while if I try to connect with other login I can't??the first connection isn't closed?("finally {
try {
if (result!=null) result.close();
if (ps!=null) ps.close();
if (con!=null) con.close();
")

how I have to make a new connection??

Thanks