Showing posts with label Database Connectivity in JBOSS. Show all posts
Showing posts with label Database Connectivity in JBOSS. Show all posts

Sunday, October 14, 2012

Implement XA-Datasource in JBOSS


We have created a sample app which transfers money between two bank account databases. This task is fulfilled by an ejb method.
Lets called first bank as Akila and the other banks as Sithum. We need to create two different databases for two different banks. then we need two create two different data source as describe earlier. But in this case, we used XA-datasource. So lets start implement our sample application.

First we are going to create two datasources for two banks called akila-ds.xml and sithum-ds.xml. I am going to show only one -ds.xml file. But you have to do is, you can change jndi name only. 


<?xml version="1.0" encoding="UTF-8"?>
<datasources>
     <xa-datasource>
         <jndi-name>jdbc/akilads</jndi-name> 
         <xa-datasource-property name="URL">jdbc:mysql://localhost:3306/mazhar_db</xa-datasource-property>
         <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
         <user-name>root</user-name>
         <password></password>
         <track-connection-by-tx>true</track-connection-by-tx>
         <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
         <valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
         <min-pool-size>1</min-pool-size>
         <max-pool-size>10</max-pool-size>
         <idle-timeout-minutes>10</idle-timeout-minutes>
         <metadata>
             <type-mapping>mySQL</type-mapping>
         </metadata>
     </xa-datasource>


Above file contains the data source for akila_db.  By editing jndi name and the URL we can create a data source file for the other bank. Main difference of this file with the previous datasource file is that this contains ‘xa-datasource’ instead of ‘local-tx-datasource’. We need ‘xa-datasource’ here because we have to handle a transaction between two databases. Don’t forget to begin the jndi name with ‘jdbc/’ .
In order to make a ‘mysql jdbc connection’ with ‘jboss’ we have to copy and paste ‘mysql-connector-java-5.1.13-bin.jar’ in jbossas/server/xxx/lib folder.

Now we need to do some coding in our application. So create a new enterprise application. Then go to ejb and write down following code.

Context initCnt = new InitialContext ();
DataSource ds = (DataSource) initCnt.lookup("java:jdbc/"+from+"bankds");

Then we have to create a database connection and make use of it.

ds.getConnection().createStatement().executeUpdate("Update user_amount set amount=amount-"+amount+ " WHERE user='"+user1+"'");

This ‘getConnection()’ method gets a connection from connection pool and releases it as configured. Connection pool is created by the ‘-ds.xml’ file definition.
In addition to this we have to handle some sql exceptions.
Not only the ejb but also the web.xml file should be modified to make database connections. Add this code inside the ‘<webapp>’ tag

<resource-ref>
<description>Oracle Datasource</description>
<res-ref-name>jdbc/mazhards</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
<resource-ref>
<description>Oracle Datasource</description>
<res-ref-name>jdbc/zaheerds</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

Finally following code should be added to the ‘jboss-web.xml file’

  <resource-ref>
  <res-ref-name>jdbc/akilads</res-ref-name>
<jndi-name>java:/mazhards</jndi-name>
</resource-ref>

<resource-ref>
<res-ref-name>jdbc/sithumds</res-ref-name>
<jndi-name>java:/zaheerds</jndi-name>
</resource-ref>

Now open up your browser and do the transaction.


=================================================

So I know that i can write up to 140 characters in this box.
 http://bit.ly/RgNp89
=================================================












Login module with database connectivity in JBOSS

We need to create a MySQL database to store usernames, passwords and rolls of users. So create a database called akila_db and create two tables called myuser and myuser_roles. Then add following data to database.

myuser table
username      password
akila               akila
sithum            sithum

myuser_roles table
username    role
akila              admin
sithum           guest

Then we have to edit login-config.xml in server/xxx/conf folder.


<application-policy name = "akila_policy">
       <authentication>
          <login-module code = "org.jboss.security.auth.spi.DatabaseServerLoginModule" flag = "required">
             <module-option name = "unauthenticatedIdentity">guest</module-option>
                                                 <module-option name = "dsJndiName">java:/ifsds</module-option>
             <module-option name = "principalsQuery">SELECT password FROM myuser WHERE username=?</module-option>
             <module-option name = "rolesQuery">SELECT role, 'Roles' FROM myuser_roles WHERE username=?</module-option>
          </login-module>
       </authentication>
    </application-policy>


Remaining things are similar to Basic authentication login module as we discussed earlier. So click here  find out about basic authentication login module.

Data source file in JBOSS

So far, we did not discuss about database connectivity. But in this article, we are going to discussed about database connectivity in JBOSS. In jboss we have three kind of data sources.

1.<local-tx-datasource>
     Identifies a data source that uses transactions, even distributed transactions
     within the local application server, but doesn't use distributed
    transactions among multiple application servers.

2.<no-tx-datasource>

   Identifies a data source that doesn't use transactions. This option
   isn't shown in the example, but would appear in place of the
  <local-tx-datasource> tag.

3.<xa-datasource>

  Identifies a data source that uses distributed transaction among multiple
  application servers. This option isn’t shown in the example, but
  would appear in place of the <local-tx-datasource> tag.

Let's see how we implement those data sources in our applications.


With java applications we can make database connections by adding ‘jdbc’ drivers.  There programmers have to spend a lot of time in database connection management. Every time a ‘getConnection ()’ method is called a new database connection is created and it may result in a connection bottleneck. ‘Jboss’ provides an efficient way for database connection management. It is called connection pooling.
A database is referenced from an enterprise application as a data source.  Depending on whether the database is accessed from web app or ejb configuration details differ.
First of all we should create a data source file to be used by the authentication method.  Name it as akila-ds.xml and save it in server/xxx/deploy folder.  Add the below content to it. (-ds.xml is mandatory here because the server identifies a data source file by that extension)


<?xml version="1.0" encoding="UTF-8"?>
<datasources>
       <local-tx-datasource>
     <jndi-name>akilads</jndi-name>
     <connection-url>jdbc: mysql://localhost:3306/ifs_db</connection-url>
     <driver-class>com.mysql.jdbc.Driver</driver-class>
     <user-name>root</user-name>
     <password></password>
<validconnectioncheckerclassname>org.jboss.resource.adapter.jdbc.vendor.MySQLValidConnectionChecker</valid-connection-checker-class-name>
     <min-pool-size>10</min-pool-size>
     <max-pool-size>100</max-pool-size>
     <metadata>
     <type-mapping>mySQL</type-mapping>
     </metadata>
     </local-tx-datasource>
 </datasources>


Jndi name component must contain the file name without ‘-’. (akila-ds.xml -> akilads)  

Here we are going to use MySQL database. Now we created the data source file. In next article, we will see how we use this file to achieve Basic authentication login module with stored data in a database.