Transaction using connection from an external Datasource

[ma...@tiscali.it]

 My situation is: iBatis framework using an existing WebSphere DataSource.
We were not succesful in defining the Datasource inside the iBatis SqlMap-config.XML
file cause the datasource itself needs username and password being specified
at run-time by the application using it.  Furthermore we do NOT have an external
transaction, i.e EJB etc..., our DAO is starting a NEW transaction.
 
 Looking around the iBatis javadocs I've found the SqlMapSession interface
which can be istantiated by the SqlMapClient openSession(Connection conn)
SqlMapSession session = sqlMap.openSession().

Well, as I understood I could procede like this:
1) Referencing teh external Datasource (for example using a Service Locator
pattern where to reference the Datasource from the Context).
2) Getting the connection from the DataSource using the getConnection(username,
password) method.
3) Create a SqlMapClient (for example from a Singleton object where I use
the code "sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);" and reader
is the XML iBatis Config file).
4) Create a thread safe SqlMapSession by passing a Connection referenced
starting from an external Datasource (this should start a transaction, shouldn't
it?).   
5) do work inside the transaction (cycle for updates)
6) commit the transaction
7) finally close SqlMapSession and Connection (is this the right order ?)
8) In case of errors and exceptions calls rollback on connection and executes
finally as usual 
   
 I've got a DAO object where I implemented an updateAll() method. Before
going on with development of all DAOs I would like to be sure that the "pattern"
I'm using is OK.
 Is this a correct way of operating a transaction, did I considered all (closing
connection, sqlMapSession, ... ) or I'm missign something?
  
 
 public int updateAll(List list)throws DAOException {
        SqlMapClient sqlMap = null;
        ServiceLocator locator = null;
        DataSource ds = null;
        Connection conn = null;
        SqlMapSession sqlSess = null;
        int result=1;
        Item item;
        
        try{
        	// Getting Datasource and connection (TODO: username and password
to be parametric)
        	locator = ServiceLocator.getInstance();
            ds = (DataSource)locator.getDataSource("jdbc/myOracleDb"); 
        	conn = ds.getConnection("giuseppe", "verdi");
        	
        	// SqlMapClient and SqlMapSession
        	sqlMap = SqlAppConfig.getSqlMapInstance();
        	sqlSess = sqlMap.openSession(conn);
        	
            // do work: cycle the list for update
            for(int k=0;k<list.size();++k){ 
            	item = (Item)list.get(k);
            	result = sqlSess.update("update",item);
            }  
        	
            // commit all updates
        	conn.commit();
            

        }catch (Exception e){
            log.error("Exception: "+ e.getMessage());
            result = 0;
            try{
            	conn.rollback();
            }catch(Exception re){
            }
            DAOException de = new DAOException(e.getMessage());
            throw de;
        }finally{
            // Closing all
        	try{
        		sqlSess.close();
            }catch (Exception ex){
            	log.error("Error in closing SqlMapSession");
            }
            try{
        		conn.close();
            }catch (Exception ex){
            	log.error("Error in closing Connection");
            }
        }
       
        return result;
    }
    
    Thanks in advance for your suggestions
    
    Max

__________________________________________________________________
TISCALI ADSL 1.25 MEGA a soli 19.95 euro/mese
Solo con Tiscali Adsl navighi senza limiti di tempo
a meno di 20 euro al mese. E in piu' telefoni senza
pagare il canone Telecom! Scopri come, clicca qui 
http://abbonati.tiscali.it/adsl/sa/1e25flat_tc/

Re: Transaction using connection from an external Datasource

[Clinton Begin <cl...@gmail.com>]

That should work. I'm glad to see you're using sessions instead of 
setUserConnection() --don't go there.

If anything, you're doing more work than is necessary. Consider the 
following (granted I know nothing of your actual environment):

//no need to regrab these every time...
private SqlMapClient sqlMap = SqlAppConfig.getSqlMapInstance();
private ServiceLocator locator = ServiceLocator.getInstance();

public int updateAll(List list)throws DAOException {

Connection conn = null;
SqlMapSession sqlSess = null;
int result=1;
Item item;

try {
DataSource ds = (DataSource)locator.getDataSource("jdbc/myOracleDb");
 conn = ds.getConnection("giuseppe", "verdi");
sqlSess = sqlMap.openSession(conn);
try {
// do work: cycle the list for update
for(int k=0;k<list.size();++k){
item = (Item) list.get(k);
result = sqlSess.update("update",item);
}

// commit all updates
conn.commit();
} catch (Exception e) {
log.error("Exception: "+ e.getMessage());
result = 0;
try{
// Don't forget to check for null connection
if (conn != null) conn.rollback();
}catch(Exception re){
}
DAOException de = new DAOException(e.getMessage());
throw de;
}finally{
// Closing all
if (sqlSess != null) sqlSess.close(); // this can't really throw any 
exceptions
try {
conn.close();
} catch (Exception ex) {
log.error("Error in closing Connection");
}
}

return result;
}




On 6/20/05, max_lazzari@tiscali.it <ma...@tiscali.it> wrote:
> 
> My situation is: iBatis framework using an existing WebSphere DataSource.
> We were not succesful in defining the Datasource inside the iBatis 
> SqlMap-config.XML
> file cause the datasource itself needs username and password being 
> specified
> at run-time by the application using it. Furthermore we do NOT have an 
> external
> transaction, i.e EJB etc..., our DAO is starting a NEW transaction.
> 
> Looking around the iBatis javadocs I've found the SqlMapSession interface
> which can be istantiated by the SqlMapClient openSession(Connection conn)
> SqlMapSession session = sqlMap.openSession().
> 
> Well, as I understood I could procede like this:
> 1) Referencing teh external Datasource (for example using a Service 
> Locator
> pattern where to reference the Datasource from the Context).
> 2) Getting the connection from the DataSource using the 
> getConnection(username,
> password) method.
> 3) Create a SqlMapClient (for example from a Singleton object where I use
> the code "sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);" and 
> reader
> is the XML iBatis Config file).
> 4) Create a thread safe SqlMapSession by passing a Connection referenced
> starting from an external Datasource (this should start a transaction, 
> shouldn't
> it?).
> 5) do work inside the transaction (cycle for updates)
> 6) commit the transaction
> 7) finally close SqlMapSession and Connection (is this the right order ?)
> 8) In case of errors and exceptions calls rollback on connection and 
> executes
> finally as usual
> 
> I've got a DAO object where I implemented an updateAll() method. Before
> going on with development of all DAOs I would like to be sure that the 
> "pattern"
> I'm using is OK.
> Is this a correct way of operating a transaction, did I considered all 
> (closing
> connection, sqlMapSession, ... ) or I'm missign something?
> 
> 
> public int updateAll(List list)throws DAOException {
> SqlMapClient sqlMap = null;
> ServiceLocator locator = null;
> DataSource ds = null;
> Connection conn = null;
> SqlMapSession sqlSess = null;
> int result=1;
> Item item;
> 
> try{
> // Getting Datasource and connection (TODO: username and password
> to be parametric)
> locator = ServiceLocator.getInstance();
> ds = (DataSource)locator.getDataSource("jdbc/myOracleDb");
> conn = ds.getConnection("giuseppe", "verdi");
> 
> // SqlMapClient and SqlMapSession
> sqlMap = SqlAppConfig.getSqlMapInstance();
> sqlSess = sqlMap.openSession(conn);
> 
> // do work: cycle the list for update
> for(int k=0;k<list.size();++k){
> item = (Item)list.get(k);
> result = sqlSess.update("update",item);
> }
> 
> // commit all updates
> conn.commit();
> 
> 
> }catch (Exception e){
> log.error("Exception: "+ e.getMessage());
> result = 0;
> try{
> conn.rollback();
> }catch(Exception re){
> }
> DAOException de = new DAOException(e.getMessage());
> throw de;
> }finally{
> // Closing all
> try{
> sqlSess.close();
> }catch (Exception ex){
> log.error("Error in closing SqlMapSession");
> }
> try{
> conn.close();
> }catch (Exception ex){
> log.error("Error in closing Connection");
> }
> }
> 
> return result;
> }
> 
> Thanks in advance for your suggestions
> 
> Max
> 
> __________________________________________________________________
> TISCALI ADSL 1.25 MEGA a soli 19.95 euro/mese
> Solo con Tiscali Adsl navighi senza limiti di tempo
> a meno di 20 euro al mese. E in piu' telefoni senza
> pagare il canone Telecom! Scopri come, clicca qui
> http://abbonati.tiscali.it/adsl/sa/1e25flat_tc/
> 
> 
> 
>