ItsTikk's Tech Note: Wildfly 10 Create XA Datasource for Distribute Transaction

If you work with multiple databases and you have table distributed across those databases. The example for this scenario is when you want to save, update and delete on 2 or more table on different databases. On this cases, you need to use distribute transaction. Wildfly as Java EE specification provides distribute transaction through JTA. To implement, distributed transaction by using XA datasource, JTA use 2 phases commit. First, JTA will check whether transaction could be commit or not by preparing transaction. If transaction could be committed without any issue. JTA will commit transactions across databases.

Create XA Datasource

XA protocol use transaction manager to implement two phase commit as below figure.

Fig 01-1. Transactio Manager

Fig.01-2. two phase commit

To create XA datasource, you need to go to administrator console in the Wildfly.

Fig 01. Create XA Datasource 01

Click Add to add XA datasource

Fig 02. Create XA Datasource 02

Select Database type that you use.

Fig 03. Select Database type

Input Datasource name and JNDI name

Fig 04 Create Datasource Name and JNDI name

Select Driver type that you use

Fig 05 Select Datasource Type

Input Database information.

Fig 06. Input Database Information

Input Credential

Fig 07 Input Credential Information

Click Finish.

Fig 08. Finish adding XA datasource

Edit Configuration file standalone.xml

Even you finished adding datasource at the console page. You still face test connection failed once you test connection.

Fig 09. Testing Connection failed

To finish this connection testing issue. you need to edit standalone.xml. In the <subsystem xmlns="urn:jboss:domain:datasources:4.0"> you will <xa-datasource> as below.

<xa-datasource jndi-name="java:/PostgresXADS02" pool-name="PostgresXADS02" enabled="true" use-ccm="true">

<xa-datasource-property name="ServerName">
localhost
</xa-datasource-property>
<xa-datasource-property name="PortNumber">
5432
</xa-datasource-property>
<xa-datasource-property name="currentSchema">
usr01
</xa-datasource-property>
<xa-datasource-property name="DatabaseName">
usr01
</xa-datasource-property>
<xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>
<driver>postgresql-42.2.9.jar</driver>
<security>
<user-name>usr01</user-name>
<password>Qf48d8uv12!@</password>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>
<background-validation>true</background-validation>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>
</validation>
</xa-datasource>

you need to add <xa-datasource-class>org.postgresql.xa.PGXADataSource</xa-datasource-class>. Then you need restart your Widlfly server.

#systemctl restart wildfly

Then, you start to test connection again

Fig 12. Test Connection after editing Configure file

Finally, to enable prepared transaction, you need to edit postgresql.conf on your PostgreSQL server.


  #cd /var/lib/pgsql/12/data

  #vi postgresql.conf

  max_prepared_transactions = 64          # zero disables the feature

After edit postgresql.conf, you need to restart PostgreSQL server.


  #systemctl restart postgresql

Utilizing Distributed Transaction with XA Datasource

To test distributed transaction with XA datasource, I have created two XA datasources as below.

Fig 12. Sample Datasource

Create two XA Datasources in the Widlfly as below.

Fig 15. Create two XA Datasources

Next is to create project to test distributed transactions.

Create JPA Project

In this project, I will demonstrate money transferring between two accounts which this two account is stored in different database. In this scenario, it is required to perform distributed transaction between two databases.

In the set up, I have use two databases. Both contain same tables. I have created table as below.

>vi accounttbl.sql

CREATE TABLE IF NOT EXISTS usr01.ACCOUNTTBL (

        ACCNO   VARCHAR(15) NOT NULL,
        BALANCE NUMERIC DEFAULT 500,
        PRIMARY KEY(ACCNO)
)

Next is to connect to the database and create table


  #psql -U usr01

  >create database db00;

  >create database db01;

  >\connect db00

  >\i accounttbl.sql

  >\connect db01

  >\i accounttbl.sql

for db00 and db01 account table, I just create record as below

>\connect db00
>insert into accounttbl (accno, balance) values ('0001',2000);
>\connect db01
>insert into accounttbl (accno, balance) values ('0002',2000);

Create JPA Project and persistence.xml. I have two JPA projects (java-ee-02-xa-jpa-00 and java-ee-02-jpa-01).

Fig 19.JPA projects

for the first project java-ee-02-xa-jpa-00, I have created persistence.xml as following.



 <?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
	<persistence-unit name="java-ee-02-xa-jpa-00" transaction-type="JTA">
		<provider>org.hibernate.ejb.HibernatePersistence</provider>
		<jta-data-source>java:jboss/datasources/sampledbxa00</jta-data-source>
		<class>pro.itstikk.jpa.xa00.Account</class>
		<properties>
			<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
			<property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
		</properties>
	</persistence-unit>
</persistence>

And for the Entity Class


  
  package pro.itstikk.jpa.xa00;

import java.io.Serializable;
import javax.persistence.*;


/**
 * The persistent class for the accounttbl database table.
 * 
 */
@Entity
@Table(name="accounttbl")
@NamedQuery(name="Account.findAll", query="SELECT a FROM Account a")
public class Account implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private String accno;

	private float balance;

	public Account() {
	}

	public String getAccno() {
		return this.accno;
	}

	public void setAccno(String accno) {
		this.accno = accno;
	}

	public float getBalance() {
		return this.balance;
	}

	public void setBalance(float balance) {
		this.balance = balance;
	}

}

And for java-ee-02-jpa-xa-01 project, persistence.xml and Entity class has been created as below.



<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
	<persistence-unit name="java-ee-02-xa-jpa-01" transaction-type="JTA">
		<provider>org.hibernate.ejb.HibernatePersistence</provider>
		<jta-data-source>java:jboss/datasources/sampledbxa01</jta-data-source>
		<class>pro.itstikk.jpa.xa01.Account</class>
		<properties>
			<property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
			<property name="hibernate.connection.driver_class" value="org.postgresql.Driver"/>
		</properties>
	</persistence-unit>
</persistence>

For JPA Entity class .


package pro.itstikk.jpa.xa01;

import java.io.Serializable;
import javax.persistence.*;


/**
 * The persistent class for the accounttbl database table.
 * 
 */
@Entity
@Table(name="accounttbl")
@NamedQuery(name="Account.findAll", query="SELECT a FROM Account a")
public class Account implements Serializable {
	private static final long serialVersionUID = 1L;

	@Id
	@GeneratedValue(strategy=GenerationType.IDENTITY)
	private String accno;

	private float balance;

	public Account() {
	}

	public String getAccno() {
		return this.accno;
	}

	public void setAccno(String accno) {
		this.accno = accno;
	}

	public float getBalance() {
		return this.balance;
	}

	public void setBalance(float balance) {
		this.balance = balance;
	}

}

Create EJB Project

Next is to create EJB for business logic to perform business logic. I have created 2 EJB to retrieve account information and update account in formation as following. First is for source account and second is for destination account.


	  package pro.istikk.ejb.xa00;
      

      import javax.ejb.LocalBean;
      import javax.ejb.Stateless;
      import javax.persistence.EntityManager;
      import javax.persistence.PersistenceContext;
      import javax.persistence.Query;
      import javax.transaction.Transactional;
      

      import pro.itstikk.jpa.xa00.Account;
      

      

      /**
       * Session Bean implementation class Account00Ejb
       */
      @Stateless
      @LocalBean
      public class Account00Ejb {
       
      
         @PersistenceContext(unitName="java-ee-02-xa-jpa-00")
      
      
         private EntityManager em;
      
          /**
           * Default constructor. 
           */
          public Account00Ejb() {
              
          }
          public Account getAccount(Account account) {
      
             String sql =
        "Select a from Account a where a.accno = :accno";
      
      
             Query query =
        em.createQuery(sql);
      
      
           
         query.setParameter("accno",
        account.getAccno());
      
      
             return (Account)
        query.getSingleResult();
      
          }
          @Transactional
          public Account updateAccount(Account account) {
      
             return
        em.merge(account);
      
          }
      

      }

Next is EJB to retrieve data from second account.


    package pro.itstikk.jpa.xa01;

    import javax.ejb.LocalBean;

    import javax.ejb.Stateless;

    import javax.persistence.EntityManager;

    import javax.persistence.PersistenceContext;

    import javax.persistence.Query;

    import javax.transaction.Transactional;

    /**

     * Session Bean implementation class Account01Ejb

     */

    @Stateless

    @LocalBean

    public class Account01Ejb {

    
       @PersistenceContext(unitName="java-ee-02-xa-jpa-01")
    

     private EntityManager em;

        /**

         * Default constructor. 

         */

        public Account01Ejb() {

            

        }

        

        public Account getAccount(Account account) {

    
           String sql = "Select
      a from Account a where a.accno = :accno";
    

           Query query =
      em.createQuery(sql);
    

         
       query.setParameter("accno",
      account.getAccno());
    

           return (Account)
      query.getSingleResult();
    

        }

        @Transactional

        public Account updateAccount(Account account) {

    
           return
      em.merge(account);
    

        }

    }

You need to add JPA Projects in to build path to resolve dependencies. And to perform fund transfer business logic to need to create one more EJB as following. For fund transfer you need to validate transfer amount, account and balance of the account.


package pro.itstikk.ejb.xa;

import javax.ejb.EJB;

import javax.ejb.Stateless;

import javax.transaction.Transactional;




import org.jboss.logging.Logger;




import pro.istikk.ejb.xa00.Account00Ejb;

import pro.itstikk.jpa.xa01.Account01Ejb;




/**

 * Session Bean implementation class AccountEjb

 */

@Stateless(mappedName = "accountEjb")

public class AccountEjb {


   private static Logger logger =
  Logger.getLogger(AccountEjb.class);


 @EJB


   private Account00Ejb account00Ejb;


 @EJB


   private Account01Ejb account01Ejb;


    /**

     * Default constructor. 

     */

    public AccountEjb() {

        

    }

    @Transactional


      public void transfer(pro.itstikk.jpa.xa00.Account source,
  pro.itstikk.jpa.xa01.Account destination, float amount) throws Exception {


     


       // validate source and
  destination



     
   if(source.getAccno().equals(destination.getAccno())) throw new
  Exception("Source and Destination is the same.");


     

     // validate source


     
   pro.itstikk.jpa.xa00.Account
  sourceAccount = account00Ejb.getAccount(source);



       if(sourceAccount==null)
  throw new Exception("No Source Account found !");


     


       // validate destination


     


     
   pro.itstikk.jpa.xa01.Account
  destinationAccount = account01Ejb.getAccount(destination);



     
   if(destinationAccount==null) throw new
  Exception("No destination account found !");


     

     // validate amount


       if(amount<=0) throw
  new Exception("Transfer amount is 0");



       float sourceBal =
  sourceAccount.getBalance() - amount;



       logger.info("source
  account : "+sourceAccount.getAccno());



       logger.info("source
  balance : "+sourceBal); 


     


       if(sourceBal < 0)
  throw new Exception("Insufficient fund");



       float destinationBal =
  destinationAccount.getBalance() + amount;



       logger.info("destination
  account : "+destinationAccount.getAccno());



       logger.info("destination
  balance : "+destinationBal);


     

     // update account


     
   source.setBalance(sourceBal);



     
   if(account00Ejb.updateAccount(source)==null) throw new Exception("update
  source account failed.");


     


       // update destination



     
   destination.setBalance(destinationBal);



     
   if(account01Ejb.updateAccount(destination)==null) throw new Exception("update
  destination account failed.");


     

    }

}

Create Dynamic Web Project

Finally, you need to create an interface to test whether xa transaction management working properly or not. To test whether xa datasource working or not, I just created simple Dynamic Web Project, create Managed Bean and system xhtml page as following. First ist my managed bean, I just need accounts and transfer amount.


  package pro.itstikk.bean;


  import javax.annotation.PostConstruct;

  import javax.ejb.EJB;

  import javax.enterprise.context.RequestScoped;

  import javax.inject.Named;


  import org.jboss.logging.Logger;


  import pro.itstikk.ejb.xa.AccountEjb;


  @Named(value="accountBean")

  @RequestScoped

  public class AccountBean {

  
     private static Logger logger =
    Logger.getLogger(AccountBean.class);
  

   @EJB

  
     private AccountEjb accountEjb;
  

  
     private pro.itstikk.jpa.xa00.Account
    source;
  

  
     private pro.itstikk.jpa.xa01.Account
    destination;
  

   private float amount;

   @PostConstruct

   public void init() {

  
         source = new
    pro.itstikk.jpa.xa00.Account();
  

  
         destination = new
    pro.itstikk.jpa.xa01.Account();
  

   }

  
     public String transfer() throws
    Exception {
  

  
         logger.info("source :
    "+source.getAccno());
  

  
         logger.info("destination :
    "+destination.getAccno());
  

  
         logger.info("amount "+amount);
  

  
         accountEjb.transfer(source,
    destination, amount);
  

       return "response.xhtml";

   }


  
     public pro.itstikk.jpa.xa00.Account
    getSource() {
  

       return source;

   }


  
     public void
    setSource(pro.itstikk.jpa.xa00.Account source) {
  

       this.source = source;

   }


  
     public pro.itstikk.jpa.xa01.Account
    getDestination() {
  

       return destination;

   }


  
     public void
    setDestination(pro.itstikk.jpa.xa01.Account destination) {
  

  
         this.destination = destination;
  

   }


   public float getAmount() {

       return amount;

   }


  
     public void setAmount(float amount)
    {
  

       this.amount = amount;

   }

   

  }

Next is xhtml page for input the information.



  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 

<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:rich="http://richfaces.org/rich"
      xmlns:ui="http://java.sun.com/jsf/facelets"
      xmlns:f="http://java.sun.com/jsf/core"
      xmlns:a4j="http://richfaces.org/a4j"
      xmlns:h="http://java.sun.com/jsf/html"> 


<h:head></h:head> 
<body>
    <rich:panel>
        <f:facet name="header">

          Write your own custom rich components with
  built-in AJAX support

        </f:facet>
     <h:form>
         <p>

               Source Account : <h:inputText
  value="#{accountBean.source.accno}" />

         </p>
         <p>

               Transfer Amount : <h:inputText
  value="#{accountBean.amount}" />

         </p>
         <p>

               Destination Account : <h:inputText
  value="#{accountBean.destination.accno}" />

         </p>

           <h:commandButton
  action="#{accountBean.transfer()}" value="confirm" />

     </h:form>
   </rich:panel>
</body> 
</html>

For the response page, I have create as below.


  
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
  
  <html xmlns="http://www.w3.org/1999/xhtml"
        xmlns:rich="http://richfaces.org/rich"
        xmlns:ui="http://java.sun.com/jsf/facelets"
        xmlns:f="http://java.sun.com/jsf/core"
        xmlns:a4j="http://richfaces.org/a4j"
  
          xmlns:h="http://java.sun.com/jsf/html"> 
  
  

  <h:head></h:head> 
  <body>
      <rich:panel>
          <f:facet name="header">
  
            Write your own custom rich components with
    built-in AJAX support
  
          </f:facet>
  
     <h:outputText value="Index"
    /> 
  
   </rich:panel>
  </body> 
  </html>

After you create all the projects, You need to just pack them in the JAVA Enterprise Project and deploy it as ERR package and run the Wildfly. It should work as below.

Fig 21 Result 01

Fig 22 Response

ItsTikk's Tech Note

Wildfly 10 Create XA Datasource for Distribute Transaction

Create XA Datasource

Edit Configuration file standalone.xml

Utilizing Distributed Transaction with XA Datasource

Create JPA Project

Create EJB Project

Create Dynamic Web Project

No comments:

Post a Comment

Feature Recently

Running Wildfly Application Server in Domain Mode

Most Views