JNDI DataSource
DataSource Connection in Java
)Put mysql-connector-java-5.1.6-bin.jar file inside tomcat common folder.
2)Write below code in conf/server.xml
<Context path="/DBTest" reloadable="true"
docBase="C:\Tomcat 5.5\webapps\DBTest"
workDir="C:\Tomcat 5.5\webapps\DBTest\work" debug="1">
<Resource name="jdbc/DBTest"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
auth="Container" maxActive="40"
maxIdle="10" maxWait="15000" removeAbandoned="true"
removeAbandonedTimeout="60" logAbandoned="true" username="root"
password="charan" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test" />
</Context>
3) Web.xml
<web-app>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
4)Write one below simple jsp file
<%@page import="java.io.*,javax.naming.*,java.sql.*,javax.sql.*" %>
<%
try
{
Context initCtx = new InitialContext();
String env_binding = "java:comp/env";
Context envCtx =(Context)initCtx.lookup(env_binding);
DataSource ds = (DataSource)envCtx.lookup("jdbc/DBTest");
Connection conn = ds.getConnection();
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery("select * from emp");
while(rs.next())
{
out.println(rs.getString(2));
}
}finally
{
}
%>
A data source is a Java Naming and Directory Interface (JNDI) object used to obtain a connection from a connection pool to a database. In order to create a DataSource (so that you can use JDBC connectivity) you need to create a file ending with -ds.xml under the "deploy" directory of your server.
The default Datasource file The default data source configured with JBoss 4.0 is the HypersonicDB data source.
Here's the hsqldb-ds.xml that is shipped with jboss :
As you can see from this file, JDBC connectivity uses Connection pools to dispatch Connections. The initial size and the max size of the Connection pool can be configured with <min-pool-size> and <max-pool-size>.
With <idle-timeout-minutes> you can indicate the maximum time a connection may be idle before being closed and returned to the pool. If not specified it's 15 minutes.
<track-statements/> is a debugging feature: it checks that all statements are closed when the connection is returned to the pool: remember to disable it in production environment.
<security-domain> tells to use the security domain defined in conf/login-config.xml : in our case:
<prepared-statement-cache-size> is the number of prepared statements per connection to be kept open and reused in subsequent requests. They are stored in a LRU cache. The default is 0 (zero), meaning no cache.
Enterprise datasources
I) Local Datasource This is a sample Oracle local datasource configuration: a local DataSource is one that does not support two phase commit using a java.sql.Driver.
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.
Remember: In order to use an Oracle datasource you need to put the jdbc driver in jBoss's server's lib directory.
Configure your project for Tomcat JNDI DataSource
1. In the Solution Explorer, open the file web.xml in the WEB-INF folder.
2. Add the following elements to web.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
3.
4. <resource-ref>
5. <res-ref-name>your-jndi-datasource-name</res-ref-name>
6. <res-type>javax.sql.DataSource</res-type>
7. <res-auth>Container</res-auth>
</resource-ref>
8. Add a new folder named META-INF to your project.
9. Add a new file named context.xml to the META-INF folder you created in the previous step.
10. In the Tomcat root directory \ conf \ context.xml file, for example: C: \ Program Files \ apache-tomcat-6.0.24 \ conf
(2). Open the context.xml file is as follows in the <Context> </ Context> add code
11. In the Solution Explorer, open the file context.xml in the META-INF folder.
12. Add the following elements to context.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
13.
14. <?xml version="1.0" encoding="utf-8" ?>
15. <Context docBase="application name" path="application context">
16. <Resource name="your-jndi-datasource-name" auth="Container" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" username="dbuser" password="dbpassword" driverClassName="your-driver-name" url="your-url" />
17. <ResourceLink name="your-jndi-datasource-name" global="your-jndi-datasource-name" />
</Context>
Your Web project is now configured to use JNDI DataSource with Tomcat.
Configuring your project for WebSphere Application Server JNDI DataSource
This feature applies to the Mainsoft for Java EE Enterprise Edition only.
This section describes the additional configuration steps required when using JNDI DataSources with the WebSphere Application Server.
To configure your project for WebSphere Application Server JNDI DataSource
1. In the Solution Explorer, open the file web.xml in the WEB-INF folder.
2. Add the following elements to web.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
3.
4. <resource-ref>
5. <res-ref-name>your-jndi-datasource-name</res-ref-name>
6. <res-type>javax.sql.DataSource</res-type>
7. <res-auth>Container</res-auth>
</resource-ref>
8. In the Solution Explorer, open the file ibm-web-bnd.xml in the WEB-INF folder.
9. Add the following elements to ibm-web-bnd.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
10.
11. <resRefBindings xmi:id="ResourceRefBinding_1" jndiName="your-jndi-datasource-name">
12. <bindingResourceRef href="WEB-INF/web.xml#ResourceRef_1"/>
</resRefBindings>
Your Web project is now configured to use JNDI DataSource with WebSphere Application Server.
Two Phase Commit
XA Datasource This is a sample XA Datasource: XA DataSources support two phase commit using a javax.sql.XADataSource
Notice the <isSameRM-override-value> set to false to fix problems with Oracle. The element <track-connection-by-tx/> can be omitted on JBoss 5 where it's enabled by default.
At last, the <no-tx-separate-pools> means that Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa.
Configuring your datasource to connect to Oracle RAC Oracle RAC allows multiple computers to run the Oracle RDBMS software simultaneously while accessing a single database thus providing a clustered database.
In order to benefit from Oracle RAC features like fault tolerance and load balancing all you have to do is configuring the connection url with the list of Oracle hosts which belongs to the cluster.
In this example we are configuring our datasource to connect to a RAC made up of host1 and host2:
<connection-url>jdbc:oracle:thin:@(description=(address_list=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=host1)(port=1521))(address=(protocol=tcp)(host=host2)(port=1521)))(connect_data=(service_name=xxxxsid)(failover_mode=()(method=basic))))
</connection-url>
)Put mysql-connector-java-5.1.6-bin.jar file inside tomcat common folder.
2)Write below code in conf/server.xml
<Context path="/DBTest" reloadable="true"
docBase="C:\Tomcat 5.5\webapps\DBTest"
workDir="C:\Tomcat 5.5\webapps\DBTest\work" debug="1">
<Resource name="jdbc/DBTest"
factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
auth="Container" maxActive="40"
maxIdle="10" maxWait="15000" removeAbandoned="true"
removeAbandonedTimeout="60" logAbandoned="true" username="root"
password="charan" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test" />
</Context>
3) Web.xml
<web-app>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
4)Write one below simple jsp file
<%@page import="java.io.*,javax.naming.*,java.sql.*,javax.sql.*" %>
<%
try
{
Context initCtx = new InitialContext();
String env_binding = "java:comp/env";
Context envCtx =(Context)initCtx.lookup(env_binding);
DataSource ds = (DataSource)envCtx.lookup("jdbc/DBTest");
Connection conn = ds.getConnection();
Statement st=conn.createStatement();
ResultSet rs=st.executeQuery("select * from emp");
while(rs.next())
{
out.println(rs.getString(2));
}
}finally
{
}
%>
A data source is a Java Naming and Directory Interface (JNDI) object used to obtain a connection from a connection pool to a database. In order to create a DataSource (so that you can use JDBC connectivity) you need to create a file ending with -ds.xml under the "deploy" directory of your server.
The default Datasource file The default data source configured with JBoss 4.0 is the HypersonicDB data source.
Here's the hsqldb-ds.xml that is shipped with jboss :
- <?xml version="1.0" encoding="UTF-8"?>
- <datasources>
- <local-tx-datasource>
- <jndi-name>DefaultDS</jndi-name>
- <connection-url>jdbc:hsqldb:${jboss.server.data.dir}${/}hypersonic${/}localDB</connection-url>
- <driver-class>org.hsqldb.jdbcDriver</driver-class>
- <user-name>sa</user-name>
- <password></password>
- <min-pool-size>5</min-pool-size>
- <max-pool-size>20</max-pool-size>
- <idle-timeout-minutes>0</idle-timeout-minutes>
- <track-statements/>
- <security-domain>HsqlDbRealm</security-domain>
- <prepared-statement-cache-size>32</prepared-statement-cache-size>
- <metadata>
- <type-mapping>Hypersonic SQL</type-mapping>
- </metadata>
- <depends>jboss:service=Hypersonic,database=localDB</depends>
- </local-tx-datasource>
- <mbean code="org.jboss.jdbc.HypersonicDatabase"
- name="jboss:service=Hypersonic,database=localDB">
- <attribute name="Database">localDB</attribute>
- <attribute name="InProcessMode">true</attribute>
- </mbean>
- </datasources>
As you can see from this file, JDBC connectivity uses Connection pools to dispatch Connections. The initial size and the max size of the Connection pool can be configured with <min-pool-size> and <max-pool-size>.
With <idle-timeout-minutes> you can indicate the maximum time a connection may be idle before being closed and returned to the pool. If not specified it's 15 minutes.
<track-statements/> is a debugging feature: it checks that all statements are closed when the connection is returned to the pool: remember to disable it in production environment.
<security-domain> tells to use the security domain defined in conf/login-config.xml : in our case:
- <application-policy name = "HsqlDbRealm">
- <authentication>
- <login-module code = "org.jboss.resource.security.ConfiguredIdentityLoginModule"
- flag = "required">
- <module-option name = "principal">sa</module-option>
- <module-option name = "userName">sa</module-option>
- <module-option name = "password"></module-option>
- <module-option name = "managedConnectionFactoryName">jboss.jca:service=LocalTxCM,name=DefaultDS</module-option>
- </login-module>
- </authentication>
- </application-policy>
<prepared-statement-cache-size> is the number of prepared statements per connection to be kept open and reused in subsequent requests. They are stored in a LRU cache. The default is 0 (zero), meaning no cache.
Enterprise datasources
I) Local Datasource This is a sample Oracle local datasource configuration: a local DataSource is one that does not support two phase commit using a java.sql.Driver.
- <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>
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.
Remember: In order to use an Oracle datasource you need to put the jdbc driver in jBoss's server's lib directory.
Configure your project for Tomcat JNDI DataSource
1. In the Solution Explorer, open the file web.xml in the WEB-INF folder.
2. Add the following elements to web.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
3.
4. <resource-ref>
5. <res-ref-name>your-jndi-datasource-name</res-ref-name>
6. <res-type>javax.sql.DataSource</res-type>
7. <res-auth>Container</res-auth>
</resource-ref>
8. Add a new folder named META-INF to your project.
9. Add a new file named context.xml to the META-INF folder you created in the previous step.
10. In the Tomcat root directory \ conf \ context.xml file, for example: C: \ Program Files \ apache-tomcat-6.0.24 \ conf
(2). Open the context.xml file is as follows in the <Context> </ Context> add code
11. In the Solution Explorer, open the file context.xml in the META-INF folder.
12. Add the following elements to context.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
13.
14. <?xml version="1.0" encoding="utf-8" ?>
15. <Context docBase="application name" path="application context">
16. <Resource name="your-jndi-datasource-name" auth="Container" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" username="dbuser" password="dbpassword" driverClassName="your-driver-name" url="your-url" />
17. <ResourceLink name="your-jndi-datasource-name" global="your-jndi-datasource-name" />
</Context>
Your Web project is now configured to use JNDI DataSource with Tomcat.
Configuring your project for WebSphere Application Server JNDI DataSource
This feature applies to the Mainsoft for Java EE Enterprise Edition only.
This section describes the additional configuration steps required when using JNDI DataSources with the WebSphere Application Server.
To configure your project for WebSphere Application Server JNDI DataSource
1. In the Solution Explorer, open the file web.xml in the WEB-INF folder.
2. Add the following elements to web.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
3.
4. <resource-ref>
5. <res-ref-name>your-jndi-datasource-name</res-ref-name>
6. <res-type>javax.sql.DataSource</res-type>
7. <res-auth>Container</res-auth>
</resource-ref>
8. In the Solution Explorer, open the file ibm-web-bnd.xml in the WEB-INF folder.
9. Add the following elements to ibm-web-bnd.xml, replacing your-jndi-datasource-name with the name of your JNDI DataSource.
10.
11. <resRefBindings xmi:id="ResourceRefBinding_1" jndiName="your-jndi-datasource-name">
12. <bindingResourceRef href="WEB-INF/web.xml#ResourceRef_1"/>
</resRefBindings>
Your Web project is now configured to use JNDI DataSource with WebSphere Application Server.
Two Phase Commit
XA Datasource This is a sample XA Datasource: XA DataSources support two phase commit using a javax.sql.XADataSource
- <datasources>
- <xa-datasource>
- <jndi-name>XAOracleDS</jndi-name>
- <track-connection-by-tx></track-connection-by-tx>
- <isSameRM-override-value>false</isSameRM-override-value>
- <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class>
- <xa-datasource-property name="URL">jdbc:oracle:oci8:@tc</xa-datasource-property>
- <xa-datasource-property name="User">scott</xa-datasource-property>
- <xa-datasource-property name="Password">tiger</xa-datasource-property>
- <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
- <no-tx-separate-pools></no-tx-separate-pools>
- <metadata>
- <type-mapping>Oracle9i</type-mapping>
- </metadata>
- </xa-datasource>
- <mbean code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter"
- name="jboss.jca:service=OracleXAExceptionFormatter">
- <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager</depends>
- </mbean>
- </datasources>
Notice the <isSameRM-override-value> set to false to fix problems with Oracle. The element <track-connection-by-tx/> can be omitted on JBoss 5 where it's enabled by default.
At last, the <no-tx-separate-pools> means that Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa.
Configuring your datasource to connect to Oracle RAC Oracle RAC allows multiple computers to run the Oracle RDBMS software simultaneously while accessing a single database thus providing a clustered database.
In order to benefit from Oracle RAC features like fault tolerance and load balancing all you have to do is configuring the connection url with the list of Oracle hosts which belongs to the cluster.
In this example we are configuring our datasource to connect to a RAC made up of host1 and host2:
<connection-url>jdbc:oracle:thin:@(description=(address_list=(load_balance=on)(failover=on)(address=(protocol=tcp)(host=host1)(port=1521))(address=(protocol=tcp)(host=host2)(port=1521)))(connect_data=(service_name=xxxxsid)(failover_mode=()(method=basic))))
</connection-url>