In this post I am going to discuss the Oracle data connection pool (JNDI Pool) and testing of that connection.
There are 2 ways that you can have oracle driver known to Jboss.
Option 1:
You take ojdbc6.jar from Oracle 11g Home/jdbc/lib and deploy it from JBOSS admin console (http://<host>:9990) This is default port. However if you have changed it then use that port.
After loging in you will see deployment - the navigation path is:
Profile -->Deployments -->Manage Deployments Then on right side pane click "Add" on top right. Now select the ojdbc6.jar and deploy. Please see the screen shot below and check the red circles for navigation.
After the deployment - it need to be enabled. See below:
Now add the data source like:
I put SOADS for name and JNDI name both.
Option2:
This is manual option and involves editing the standalone.xml and making directory etc. Please see below:
1- Cd $JBOSS_HOME/modules/com
2- Mkdir –p oracle/ojdbc6/main/
3- Cd $JBOSS_HOME/modules/com/oracle/ojdbc6/main
4- Cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar . – Please note that the ORACLE_HOME here is the oracle database 11g home and this can be on a separate machine. Please copy this jar file however way you want (Scp , ftp etc etc..)
5- From current directory i.e $JBOSS_HOME/modules/com/oracle/ojdbc6/main and
Vi module.xml
<module xmlns="urn:jboss:module:1.0" name="com.oracle.ojdbc6">
<resources>
<resource-root path="ojdbc6.jar"/>
</resources>
<dependencies>
<module name="javax.api"/>
</dependencies>
</module>
--- Save and exit
6- Cd $JBOSS_HOME/standalone/configuration
Edit the standalone.xml to add following – first in the datasources and then in the drivers section – below the h2 datasource
*********datasources section *****************
<datasource jndi-name="AdmDS" pool-name="OracleDS" enabled="true" use-java-context="true" jta="true">
<connection-url>jdbc:oracle:thin:@kbrsp400.corp.kbr.com:1570:UPKSTG</connection-url>
<driver>oracle</driver>
<pool>
<prefill>true</prefill>
<use-strict-min>false</use-strict-min>
</pool>
<security>
<user-name>ARISBP</user-name>
<password>arisbp11gstg</password>
</security>
<validation>
<validate-on-match>false</validate-on-match>
<background-validation>false</background-validation>
<useFastFail>false</useFastFail>
</validation>
<statement />
</datasource>
********Ends datasources section
*****Drivers section **************
<driver name="oracle" module="com.oracle.ojdbc6">
<xa-datasource-class>oracle.jdbc.OracleDriver</xa-datasource-class>
</driver>
********Ends drivers section*********
7- Test the datasource from command line as:
Cd $JBOSS_HOME/bin
./jboss-admin.sh
[disconnected /] connect amghost3.cup.com
Connected to standalone controller at amghost3.cup.com:9999
[standalone@ amghost3.cup.com:9999 /] /subsystem=datasources/data-source=SOADS:test-connection-in-pool
{
"outcome" => "success",
"result" => [true]
}
[standalone@ amghost3.cup.comm:9999 /]
Test with JSP and for above Data Source:
To test this is what I did. I created a directory "test.war" in $JBOSS_HOME/standalone/deployments and underneath it created WEB-INF directory and lib directory and copied ojdbc6.jar there. This is the listing of the directory:
drwxr-xr-x oracle/dba 0 2011-12-09 10:05:34 test.war/
-rw-r--r-- oracle/dba 1054 2011-12-08 10:28:03 test.war/sql.jsp
drwxr-xr-x oracle/dba 0 2011-11-30 21:04:24 test.war/WEB-INF/
drwxr-xr-x oracle/dba 0 2011-11-30 21:57:01 test.war/WEB-INF/lib/
-rw-r--r-- oracle/dba 2111220 2011-11-30 20:05:59 test.war/WEB-INF/lib/ojdbc6.jar
-rw-r--r-- oracle/dba 783 2011-11-30 18:09:37 test.war/ver.jsp
-rw-r--r-- oracle/dba 1629 2011-12-08 13:22:36 test.war/plsql1.jsp
-rw-r--r-- oracle/dba 837 2011-12-02 19:46:30 test.war/test.jsp
After I am done with my scripting and all the setup as above, I went to $JBOSS_HOME/standalone/deployments and issued following command:
touch test.war.dodeploy
This made my test application deployed in Jboss7 and now I am ready for testing.
Here are screen shots of my testing.
sql.jsp:
test.jsp:
ver.jsp:
plsql1.jsp:
Please note that the packages in Oracle that do not have an out variable cannot be displayed in JSP. For example hellotest package that prints hellotest and dbms_output.put_line that prinsta whatever line or text you want to print. These examplesa do not have any column name associated with them and since JSP prints out them with column index - they cannot be found and will run into "Invalid column Index" issues. However you can still execute them but do not print the output to browser. At least this is my experience and not saying that this is how exactly they behave.
The scripts are:
<%@page contentType="text/html"
import="java.util.*,
javax.naming.*,
javax.sql.DataSource,
java.sql.*"%>
<html>
<body Color bgcolor; bgcolor=Color.Blue>
<p><font size="6">This is the result from:"select 'YES' YES from dual":
<%
DataSource cname = null;
Connection cont = null;
PreparedStatement stmt = null;
InitialContext ctx;
try {
ctx = new InitialContext();
cname = (DataSource)ctx.lookup("SOADS");
cont = cname.getConnection();
stmt = cont.prepareStatement("select BANNER from SYS.V_$VERSION");
ResultSet sr = stmt.executeQuery();
while (sr.next()) {
out.println("<br> " +sr.getString(1));
}
sr.close();
stmt.close();
}catch(Exception excep){
out.println("Exception Encountered " +excep);
}finally{
if(cont != null){
cont.close();
}
} %></font></p>
</body>
</html>
sql.jsp:
<%@page contentType="text/html"
import="java.util.*,
javax.naming.*,
javax.sql.DataSource,
java.sql.*,
java.io.*,
oracle.jdbc.*,
oracle.jdbc.driver.*,
oracle.jdbc.pool.*,
oracle.sql.*"%>
<html>
<body>
<p><font size="3">This is the result from:"PLSQL Execution ...":
<%
DataSource cname = null;
Connection cont = null;
CallableStatement stmt = null;
String oraclProc = (" begin ? :=change.test(?); end;");
InitialContext ctx;
try {
ctx = new InitialContext();
cname = (DataSource)ctx.lookup("SOADS");
cont = cname.getConnection();
stmt = cont.prepareCall(oraclProc);
stmt.registerOutParameter(1,Types.VARCHAR);
stmt.setString(2, "Yummy");
stmt.execute();
out.println(stmt.getString(1));
stmt.close();
}catch(Exception excep){
System.out.println("Exception Encountered " +excep);
out.println("<br>"+"Exception Encountered " +excep);
}
finally{
if(cont != null){
cont.close();
}
if(stmt != null){
stmt.close();
}
} %></font></p>
</body>
</html>
plsql1.jsp:
<%@page contentType="text/html"
import="java.util.*,
javax.naming.*,
javax.sql.DataSource,
java.sql.*,
java.io.*,
oracle.jdbc.*,
oracle.jdbc.driver.*,
oracle.jdbc.pool.*,
oracle.sql.*"%>
<html>
<body >
<pre style="font-family: Verdana, Arial, sans serif;">
<p>This is the result from:"PLSQL Execution ...":
<%
ResultSet ors;
DataSource cname = null;
Connection cont = null;
Statement stmt = null;
String oraquery = ("select owner,name,type from v$db_object_cache where kept='YES' order by
owner");
InitialContext ctx;
try {
ctx = new InitialContext();
cname = (DataSource)ctx.lookup("SOADS");
cont = cname.getConnection();
stmt = cont.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
out.println("\nExecuting query: " + oraquery );
out.println("\n");
out.println("OWNER"+"<HTML>	"+"NAME"+"<HTML>	"+"<HTML>	"+"<HTML>	"+"<HTML>�
9;"+"<HTML>	"+"<HTML>	"+"<HTML>	"+"TYPE");
out.println("--------------------------------------------------------------------------");
ors = stmt.executeQuery(oraquery);
while (ors.next()) {
out.println(ors.getString(1) +"<HTML>	"+"<HTML>	"+ ors.getString(2) +"<
HTML>	" +"<HTML>	"+"<HTML>	"+ ors.getString(3));
}
stmt.close();
}catch(Exception excep){
System.out.println("Exception Encountered " +excep);
out.println("<br>"+"Exception Encountered " +excep);
}
finally{
if(cont != null){
cont.close();
}
if(stmt != null){
stmt.close();
}
} %></font></p>
</pre>
</body>
</html>
ver.jsp:
<%@page contentType="text/html"
import="java.util.*,
javax.naming.*,
javax.sql.DataSource,
java.sql.*,
oracle.jdbc.*,
oracle.sql.*"%>
<%
DataSource cname = null;
Connection cont = null;
InitialContext ctx;
try {
ctx = new InitialContext();
cname = (DataSource)ctx.lookup("SOADS");
cont = cname.getConnection();
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = cont.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
out.println("<br> "+"JDBC driver version is " + meta.getDriverVersion());
}
catch(Exception excep){
out.println("Exception Encountered " +excep);
}
finally{
if(cont != null){
cont.close();
}
}
%>
Hope this Helps.
Comments