?
Solved

JBoss connection pooling for PL/SQL - Struts 2 with manual jdbc crud operations

Posted on 2012-08-27
16
Medium Priority
?
1,264 Views
Last Modified: 2012-09-05
Hi,

Im a pretty new java programmer. Im actually trying to develop a web application with JBoss. The tech stack is Struts 2 with jsp+jquery for UI. I was asked to do manual JDBC connection to my Oracle 11g pl/sql database.

Can some one explain me how to configure jdbc connection and also the connection pool in jboss manually(without using the admin console) and also access the database using the JNDI look up for crud operations?? Also correct me if im wrong but i will also need to implement session management?? how is this helpful &  how can this be done?? what is the best method.

I would really appreciate detailed explanation and sample code.

Thanks in advance!!
0
Comment
Question by:rr87
  • 10
  • 6
16 Comments
 
LVL 19

Accepted Solution

by:
ramazanyich earned 2000 total points
ID: 38339706
This is a sample Oracle local datasource configuration:
<datasources>
<local-tx-datasource>
<jndi-name>OracleDS</jndi-name>
<connection-url>jdbc:oracle:thin:@youroraclehost:1521:yoursid</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>x</user-name>
<password>y</password>
<min-pool-size>5</min-pool-size>
<max-pool-size>100</max-pool-size>
<query-timeout>60</query-timeout>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter
</exception-sorter-class-name>
<metadata>
<type-mapping>Oracle9i</type-mapping>
</metadata>
</local-tx-datasource>
</datasources>

Open in new window

Just name that file as oracle-ds.xml file and put it into deploy directory of your jboss installation. Number of sessions to DB is configured by two parameters: min-pool-size, max-pool-size
Also replace the username/password by your DB user/password and also provide correct DB server IP and SID.
Notice the <query-timeout> tag which configures the maximum of seconds before a query times out ( avaliable since Jboss 4.0.3). The <exception-sorter-class-name> is used to Check the Oracle error codes and messages for fatal errors.
In your java code you can access your oracle datasource by doing JNDI lookup to name java:/OracleDS
Remember: In order to use an Oracle datasource you need to put the jdbc driver in jBoss's server's lib directory.
0
 

Author Comment

by:rr87
ID: 38345964
Thanks for the info. But can you give me a sample of how the JNDI lookup is done? A helper class sample using JNDI will be great!
0
 
LVL 19

Expert Comment

by:ramazanyich
ID: 38346164
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 19

Expert Comment

by:ramazanyich
ID: 38346171
0
 

Author Comment

by:rr87
ID: 38346538
Im confused....  according to https://community.jboss.org/wiki/HowDoICreateAResourceRef i will also need to modify web.xml and jboss-web.xml..... Why is this necessary?? if it is not than when would i need to configure them????
0
 
LVL 19

Expert Comment

by:ramazanyich
ID: 38347775
it is according to J2EE specifications. You should provide links to the resources in the web.xml
0
 

Author Comment

by:rr87
ID: 38347813
So basically i have to once i do the *-ds.xml file and add the driver to my jboss, i also have to create web.xml file??? I use struts 2 framework and my application already has a web.xml file... is this the same file you mentioned here....if so so i just add the following inside that file??

web.xml excerpt: 
<resource-ref>
 <res-ref-name>jdbc/TheDS</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
 <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

Open in new window


What about this file below...Where is this located ?? do i create one or do i just append to an existing file?
 
jboss-web.xml: 
<jboss-web>
 <resource-ref>
 <res-ref-name>jdbc/TheDS</res-ref-name>
 <jndi-name>java:jdbc/SomeDS</jndi-name>
 </resource-ref>
</jboss-web>

Open in new window


The *-ds.xml file has the JNDI name as peopleDS . so how will the above 2 codes change??
0
 
LVL 19

Expert Comment

by:ramazanyich
ID: 38347868
1. you should use existing web.xml to and add:
<resource-ref>
 <res-ref-name>jdbc/TheDS</res-ref-name>
 <res-type>javax.sql.DataSource</res-type>
 <res-auth>Container</res-auth>
 <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
2. in jboss-web.xml which is located in the same directory where web.xml is (WEB-INF) you add:
<jboss-web>
 <resource-ref>
 <res-ref-name>jdbc/TheDS</res-ref-name>
 <jndi-name>java:/peopleDS</jndi-name>
 </resource-ref>
</jboss-web>

after that you can use in your servlets and jsp pages:
InitialContext ctx= new InitialContext();
Datasource ds = (DataSource)ctx.lookup("jdbc/theDS")
0
 

Author Comment

by:rr87
ID: 38351607
I do not have a jboss-web.xml file in my project... My application runs fine in jboss except that im still trying to setup the database part of it.  Do i need to create this file???
0
 

Author Comment

by:rr87
ID: 38351940
so i added the *-ds.xml file and also the resource ref in my web.xml.... I tried to run the application and one of the errors im getting is

org.jboss.deployment.DeploymentException: Error during deploy; - nested throwable: (javax.naming.NamingException:  resource-ref: jdbc/TheDS has no valid JNDI binding. Check the jboss-web/resource-ref

So im guessing the jboss-web.xml file is definitelyt needed but i dont have this file in my application....should i create one ?? where to place it ?? and what all will this file contain??
0
 

Author Comment

by:rr87
ID: 38352046
So i created a jboss-web.xml file under the WEB-INF folder in my application as below
<!DOCTYPE jboss-web PUBLIC
 "-//JBoss//DTD Web Application 2.4//EN"
 "http://www.jboss.org/j2ee/dtd/jboss-web_4_0.dtd">
 
<jboss-web>
 <resource-ref>
 <res-ref-name>jdbc/peopleDS</res-ref-name>
 <jndi-name>java:/peopleDS</jndi-name>
 </resource-ref>
</jboss-web>

Open in new window


And i access it like

 InitialContext icx = new InitialContext();
	      DataSource   ds = (DataSource)icx.lookup("jdbc/peopleDS");
	       Connection  conn = ds.getConnection(); 

Open in new window


When i run the application i get the following error


15:41:38,603 INFO  [ConnectionFactoryBindingService] Bound ConnectionManager 'jboss.jca:service=DataSourceBinding,name=peopleDS' to JNDI name 'java:peopleDS'
15:41:38,653 INFO  [TomcatDeployer] deploy, ctxPath=/jmx-console, warUrl=.../deploy/jmx-console.war/
15:41:38,866 WARN  [NestedThrowable] Duplicate throwable nesting of same base type: class org.jboss.deployment.DeploymentException is assignable from: class org.jboss.deployment.DeploymentException
15:41:38,867 ERROR [MainDeployer] Could not initialise deployment: file:/C:/Program Files/jboss-as/server/default/deploy/people.war
org.jboss.deployment.DeploymentException: Failed to parse WEB-INF/jboss-web.xml; - nested throwable: (org.jboss.deployment.DeploymentException: Invalid XML: file=file:/C:/Program Files/jboss-as/server/default/tmp/deploy/tmp7879413036219406005people-exp.war//WEB-INF/jboss-web.xml@-1:-1; - nested throwable: (org.xml.sax.SAXParseException: Premature end of file.))
      at org.jboss.web.AbstractWebContainer.parseMetaData(AbstractWebContainer.java:768)
      at org.jboss.web.AbstractWebContainer.init(AbstractWebContainer.java:356)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
      at org.jboss.mx.interceptor.DynamicInterceptor.invoke(DynamicInterceptor.java:97)
      at org.jboss.system.InterceptorServiceMBeanSupport.invokeNext(InterceptorServiceMBeanSupport.java:238)
      at org.jboss.deployment.SubDeployerInterceptorSupport.init(SubDeployerInterceptorSupport.java:119)
      at org.jboss.deployment.SubDeployerInterceptorSupport$XMBeanInterceptor.init(SubDeployerInterceptorSupport.java:172)
      at org.jboss.deployment.SubDeployerInterceptor.invoke(SubDeployerInterceptor.java:87)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy45.init(Unknown Source)
      at org.jboss.deployment.MainDeployer.init(MainDeployer.java:872)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:809)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
      at sun.reflect.GeneratedMethodAccessor25.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy9.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:421)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:634)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:263)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDeploymentScanner.java:336)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalStart(ServiceMBeanSupport.java:289)
      at org.jboss.system.ServiceMBeanSupport.jbossInternalLifecycle(ServiceMBeanSupport.java:245)
      at sun.reflect.GeneratedMethodAccessor3.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
      at $Proxy0.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:417)
      at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:86)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy4.start(Unknown Source)
      at org.jboss.deployment.SARDeployer.start(SARDeployer.java:302)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:1025)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:819)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:782)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:766)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.jboss.mx.interceptor.ReflectedDispatcher.invoke(ReflectedDispatcher.java:155)
      at org.jboss.mx.server.Invocation.dispatch(Invocation.java:94)
      at org.jboss.mx.interceptor.AbstractInterceptor.invoke(AbstractInterceptor.java:133)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.interceptor.ModelMBeanOperationInterceptor.invoke(ModelMBeanOperationInterceptor.java:142)
      at org.jboss.mx.server.Invocation.invoke(Invocation.java:88)
      at org.jboss.mx.server.AbstractMBeanInvoker.invoke(AbstractMBeanInvoker.java:264)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:659)
      at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:210)
      at $Proxy5.deploy(Unknown Source)
      at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:482)
      at org.jboss.system.server.ServerImpl.start(ServerImpl.java:362)
      at org.jboss.Main.boot(Main.java:200)
      at org.jboss.Main$1.run(Main.java:508)
      at java.lang.Thread.run(Unknown Source)
Caused by: org.jboss.deployment.DeploymentException: Invalid XML: file=file:/C:/Program Files/jboss-as/server/default/tmp/deploy/tmp7879413036219406005people-exp.war//WEB-INF/jboss-web.xml@-1:-1; - nested throwable: (org.xml.sax.SAXParseException: Premature end of file.)
      at org.jboss.metadata.XmlFileLoader.getDocument(XmlFileLoader.java:342)
      at org.jboss.metadata.XmlFileLoader.getDocument(XmlFileLoader.java:272)
      at org.jboss.web.AbstractWebContainer.parseMetaData(AbstractWebContainer.java:761)
      ... 87 more
Caused by: org.xml.sax.SAXParseException: Premature end of file.
      at org.apache.xerces.parsers.DOMParser.parse(Unknown Source)
      at org.apache.xerces.jaxp.DocumentBuilderImpl.parse(Unknown Source)
      at org.jboss.metadata.XmlFileLoader.getDocument(XmlFileLoader.java:328)
      ... 89 more
15:41:38,868 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

--- Incompletely deployed packages ---
org.jboss.deployment.DeploymentInfo@55a2b9e4 { url=file:/C:/Program Files/jboss-as/server/default/deploy/people.war }
  deployer: MBeanProxyExt[jboss.web:service=WebServer]
  status: null
  state: FAILED
  watch: file:/C:/Program Files/jboss-as/server/default/deploy/people.war
  altDD: null
  lastDeployed: 1346355698867
  lastModified: 1346355698858
  mbeans:
0
 

Assisted Solution

by:rr87
rr87 earned 0 total points
ID: 38352212
So i played around a bit and what i did is.... take out the ref in web.xml and delete the jboss-web.xml file from my project.... than in my class i called using

InitialContext icx = new InitialContext();
DataSource   ds = (DataSource)icx.lookup("java:/peopleDS");
Connection  conn = ds.getConnection();

and this worked....

Its great that this worked but what is happening here?? if my understanding is not wrong there HAS to be a ref in web.xml and there HAS to be jboss-web.xml file..... What would you suggest???
0
 
LVL 19

Expert Comment

by:ramazanyich
ID: 38352397
my suggestion - if it work - do not touch ;)
But normally you should put jboss-web.xml file in the WEB-INF folder and define resource-red element there and map it also in the web.xml. But probably with latest jboss releases JBoss developers simplified the life of application developers, so you can directly access JNDI resources (like db datasource in your case) without that duplication of definitions.
0
 

Author Comment

by:rr87
ID: 38354547
Thank you !
0
 

Author Comment

by:rr87
ID: 38354569
closing the question
0
 

Author Closing Comment

by:rr87
ID: 38367114
The solution was achieved but with some confusion as to the configurations in web.xml and jboss-web.xml files. Which according to an article recommended was required but on further trial and error it was found to be not necessary.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
In this post we will learn different types of Android Layout and some basics of an Android App.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question