package jdbc;
import javax.sql.*;
import java.sql.*;
public class Bank 
{
  private DataSource dataSource;
  public Bank() {}
  public void setDataSource ( DataSource dataSource )
  {
    this.dataSource = dataSource;
  }

 private DataSource getDataSource()
  {
    return this.dataSource;
  }

  private Connection getConnection()
  throws SQLException
  {
    Connection ret = null;
    if ( getDataSource() != null ) {
        ret = getDataSource().
              getConnection();
    }
    return ret;
  }

  private void closeConnection ( Connection c )
  throws SQLException
  {
    if ( c != null ) c.close();
  }
    
  public void checkTables()
  throws SQLException
  {
        
    Connection conn = null;
    try {
      conn = getConnection();
      Statement s = conn.createStatement();
      try {
        s.executeQuery ( 
        "select * from Accounts" );
      }
      catch ( SQLException ex ) {
        //table not there => create it
        s.executeUpdate ( 
        "create table Accounts ( " +
        "account VARCHAR ( 20 ), " + 
        "owner VARCHAR(300), " + 
        "balance DECIMAL (19,0) )" );
        for ( int i = 0; i < 100 ; i++ ){
          s.executeUpdate ( 
          "insert into Accounts values ( " +
          "'account"+i +"' , 'owner"+i +"', 10000 )"
          );
        }
      }
      s.close();
      }
      finally {
        closeConnection ( conn );

      }

      //That concludes setup
  }

    
  //
  //Business methods are below
  //

  public long getBalance ( int account )
  throws SQLException
  {
        
    long res = -1;
    Connection conn = null;

    try {
      conn = getConnection();
      Statement s = conn.createStatement();
      
      String query = 
      "select balance from Accounts where account='"+
      "account" + account +"'";
      
      ResultSet rs = s.executeQuery ( query );
      if ( rs == null  !rs.next() ) 
        throw new SQLException ( 
        "Account not found: " + account );
      res = rs.getLong ( 1 );
      s.close();
    }
    finally {
        closeConnection ( conn );
    }
    return res;
        
  }

  public void withdraw ( int account , int amount )
  throws Exception
  {
    Connection conn = null;

    try {
      conn = getConnection();
      Statement s = conn.createStatement();

      String sql = 
      "update Accounts set balance = balance - "+
      amount + " where account ='account"+
      account+"'";
      
      s.executeUpdate ( sql );
      s.close();
    
    }
    finally {
        closeConnection ( conn );

    }
  }
}

<?xml version="1.0" encoding="UTF-8"?>


<beans>

<bean id="datasource" 
class="com.atomikos.jdbc.nonxa.NonXADataSourceBean">

    <property name="user">
        <value>sa</value>
    </property>
    <property name="url">
        <value>jdbc:hsqldb:SpringNonXADB
        </value>
    </property>
    <property name="driverClassName">
        <value>org.hsqldb.jdbcDriver</value>
    </property>
    <property name="poolSize">
        <value>1</value>
    </property>
    <property name="connectionTimeout">
        <value>60</value>
    </property>
</bean>

<bean id="bank" class="jdbc.Bank">
        <property name="dataSource">
            <ref bean="datasource"/>
        </property>
</bean>

</beans>

package jdbc;
import com.atomikos.icatch.jta.UserTransactionImp;
import junit.framework.TestCase;
import java.io.FileInputStream;
import java.io.InputStream;
import org.springframework.beans.factory.xml.XmlBeanFactory;

public class BankTest extends TestCase
{

    private UserTransactionImp utx;

    private Bank bank;

    public BankTest ( String name )
    {
        super ( name );
        utx = new UserTransactionImp();
        

    }

    protected void setUp()
        throws Exception
    {
        //start a new transaction
        //so we can rollback the
        //effects of each test
        //in teardown!
        utx.begin();
        
        //open bean XML file
        InputStream is =
            new FileInputStream("config.xml");

        //the factory is Spring's entry point
        //for retrieving the configured
        //objects from the XML file

        XmlBeanFactory factory =
            new XmlBeanFactory(is);

        bank = ( Bank ) factory.getBean ( "bank" );
        bank.checkTables();
    }

    protected void tearDown()
        throws Exception
    {
        //rollback all DBMS effects
        //of testing
        utx.rollback();
    }

    public void testBank()
    throws Exception
    {
        int accNo = 10;
        long initialBalance = bank.getBalance ( accNo );
        bank.withdraw ( accNo , 100 );
        long newBalance = bank.getBalance ( accNo );
        if ( ( initialBalance - newBalance ) != 100 )
            fail ( "Wrong balance after withdraw: " +
                   newBalance );
    }
    
}