Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (herse.apache.org: local policy)
Date: Thu, 15 Feb 2007 17:48:49 -0800
From: David Van Couvering <david@vancouvering.com>
Subject: Re: Single Transaction Using Multiple Method Calls That Open New
 Database Connections
In-reply-to: <79ADE048-20C3-40AD-A449-9F04E9C34870@SUN.com>
Sender: David.Vancouvering@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>, nbusers@netbeans.org
Message-id: <45D50D81.8040704@vancouvering.com>
MIME-version: 1.0
Content-type: multipart/mixed; boundary="Boundary_(ID_JwzZjbYY0sXAjLtOAsx8wg)"
References: <8971063.post@talk.nabble.com> <45D397D1.2050106@vancouvering.com>
 <8989769.post@talk.nabble.com> <79ADE048-20C3-40AD-A449-9F04E9C34870@SUN.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060911)

This is a multi-part message in MIME format.

--Boundary_(ID_JwzZjbYY0sXAjLtOAsx8wg)
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT

Sheesh, I forgot about this.  I've actually done that before.  This is 
part of my local calendar demo; see how I use thread local storage to 
keep a connection.  This is in the Derby codeline...

David


Craig L Russell wrote:
> Hi Sisilla,
> 
> You might look at using a java.lang.ThreadLocal to store the Connection 
> or some object that stores more state including the Connection.
> 
> Since you don't have to pass the Connection through multiple layers, it 
> can make your code simpler and less "messy".
> 
> Craig
> 
> On Feb 15, 2007, at 9:24 AM, Sisilla wrote:
> 
>>
>> Thank you very much, David. I appreciate your time and consideration. 
>> I chose
>> to use one Connection Object and pass it to all my methods so no new
>> connections were opened. It makes my code messier, but I don't know 
>> anything
>> about JTA. Again, thanks for the suggestion. ~Sisilla
>>
>>
>> David Van Couvering-2 wrote:
>>>
>>> If you're using multiple connections, then you need to use JTA to have
>>> it work in a single transaction, and your database driver needs to
>>> support XA.
>>>
>>> If you're running in a J2EE environment with declarative transaction
>>> management enabled, you're good to go.
>>>
>>> If you're running in a web container, you may be able to get a reference
>>> to a UserTransaction object through JNDI (look up UserTransaction in
>>> Google).  Some web containers provide JTA support, others don't and
>>> you'll need to get it from a third party.
>>>
>>> Others may provide details and/or corrections to what I say here, but
>>> that's the general idea.
>>>
>>> David
>>>
>>> Sisilla wrote:
>>>> Hello All,
>>>>
>>>> I would like to update several tables in one transaction. The updates
>>>> happen
>>>> via several method calls, and each of these methods open new database
>>>> connections. The methods also contain calls to other methods that open
>>>> new
>>>> database connections. Is it at all possible that these updates could be
>>>> handled as a single transaction without eliminating these method calls?
>>>>
>>>> I am using
>>>>                     conn.setAutoCommit(false);
>>>> before the updates and
>>>>                     conn.commit();
>>>> after the updates, but it isn't working as is.
>>>>
>>>> I am using Derby 10.2.1.6 and JDK 1.6.0 on Windows XP Professional. I
>>>> appreciate any help.
>>>>
>>>> Thanks,
>>>> Sisilla
>>>
>>>
>>
>> --View this message in context: 
>> http://www.nabble.com/Single-Transaction-Using-Multiple-Method-Calls-That-Open-New-Database-Connections-tf3228984.html#a8989769 
>>
>> Sent from the Apache Derby Users mailing list archive at Nabble.com.
>>
> 
> Craig Russell
> DB PMC
> clr@apache.org http://db.apache.org/jdo
> 
> 

--Boundary_(ID_JwzZjbYY0sXAjLtOAsx8wg)
Content-type: text/x-java; name=DatabaseManager.java
Content-transfer-encoding: 7BIT
Content-disposition: inline; filename=DatabaseManager.java

/*
 
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at
 
      http://www.apache.org/licenses/LICENSE-2.0
 
   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
 
 */

import javax.sql.*;
import java.sql.*;
import org.apache.derby.jdbc.EmbeddedDataSource;

/**
 * A container for the singleton data source, so we don't have to
 * create a separate one for each class that wants to do JDBC
 */
public class DatabaseManager {
    
    private static EmbeddedDataSource ds;
    
    public static String REQUESTS_TABLE = "APP.REQUESTS";
    public static String EVENTS_TABLE   = "APP.EVENTS";
    
    // We want to keep the same connection for a given thread
    // as long as we're in the same transaction
    private static ThreadLocal<Connection> tranConnection = new ThreadLocal();

    private static void initDataSource(String dbname, String user, 
            String password) {
        ds = new EmbeddedDataSource();
        ds.setDatabaseName(dbname);
        ds.setUser(user);
        ds.setPassword(password);
        ds.setCreateDatabase("create");   
    }
    
    public static void logSql() throws Exception {
        executeUpdate("CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(" +
            "'derby.language.logStatementText', 'true')");
    }
    
    public static synchronized void beginTransaction() throws Exception {
        if ( tranConnection.get() != null ) {
            throw new Exception("This thread is already in a transaction");
        }
        Connection conn = getConnection();
        conn.setAutoCommit(false);
        tranConnection.set(conn);
    }
    
    public static void commitTransaction() throws Exception {
        if ( tranConnection.get() == null ) {
            throw new Exception("Can't commit: this thread isn't currently in a " +
                    "transaction");
        }
        tranConnection.get().commit();
        tranConnection.set(null);
    }
    
    public static void rollbackTransaction() throws Exception {
        if ( tranConnection.get() == null ) {
            throw new Exception("Can't rollback: this thread isn't currently in a " +
                    "transaction");
        }
        tranConnection.get().rollback();
        tranConnection.set(null);
    }
        
    /** get a connection */
    public static Connection getConnection() throws Exception {
        if ( tranConnection.get() != null ) {
            return tranConnection.get();
        } else {
            return ds.getConnection();
        }
    }
    
    public static void releaseConnection(Connection conn) throws Exception {
        // We don't close the connection while we're in a transaction,
        // as it needs to be used by others in the same transaction context
        if ( tranConnection.get() == null ) {
            conn.close();
        }
    }
        
    public static void initDatabase(String dbname, String user, String password,
            boolean dropTables) 
        throws Exception {
        initDataSource(dbname, user, password);

        if ( dropTables ) {
            dropTables();
        }
        
        // Assumption: if the requests table doesn't exist, none of the
        // tables exists.  Avoids multiple queries to the database
        if ( ! tableExists("REQUESTS") ) {
            createTables();
        }
    }
    
    private static boolean tableExists(String tablename) throws Exception {
        Connection conn = getConnection();
        ResultSet rs;
        boolean exists;
        
        try {
            DatabaseMetaData md = conn.getMetaData();
        
            rs = md.getTables(null, "APP", tablename, null);
            exists = rs.next();
        } finally {
            releaseConnection(conn);
        }
        
        return exists;
    } 
    
    private static void createTables() throws Exception {
        System.out.println("Creating tables");
        
        executeUpdate(
            "CREATE TABLE " + REQUESTS_TABLE + "(" +
                "sequence_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, " +
                "request_type INTEGER, " +
                "event_id VARCHAR(300), " +
                "date VARCHAR(20), " +
                "title VARCHAR(300), " +
                "edit_url VARCHAR(300))");
                        
        executeUpdate(
            "CREATE TABLE " + EVENTS_TABLE + "(" +
                "event_id VARCHAR(300) PRIMARY KEY, " +
                "date VARCHAR(20), " +
                "title VARCHAR(300), " +
                "edit_url VARCHAR(300), " +
                "version_id VARCHAR(300))");             
    }
    
    /**
     * Drop the tables.  Used mostly for unit testing, to get back
     * to a clean state
     */
    public static void dropTables() throws Exception {
        try {
            executeUpdate("DROP TABLE " + REQUESTS_TABLE);
        } catch ( SQLException sqle ) {
            if (! tableDoesntExist(sqle.getSQLState())) {
                throw sqle;
            }
        }
        
        try {
            executeUpdate("DROP TABLE " + EVENTS_TABLE);
        } catch ( SQLException sqle ) {
            if (! tableDoesntExist(sqle.getSQLState())) {
                throw sqle;
            }
        }
    }
    
    private static boolean tableDoesntExist(String sqlState) {
        return sqlState.equals("42X05") ||
               sqlState.equals("42Y55");
    }
    
    /**
     * Clean out the tables
     */
    public static void clearTables() throws Exception {
        Connection conn = getConnection();
        
        try {
            executeUpdate("DELETE FROM " + REQUESTS_TABLE);
            executeUpdate("DELETE FROM " + EVENTS_TABLE);
        } finally {
            releaseConnection(conn);
        }
        
    }
    
    /**
     * Helper wrapper around boilerplate JDBC code.  Execute a statement
     * that doesn't return results using a PreparedStatment, and returns 
     * the number of rows affected
     */
    public static int executeUpdate(String statement) 
            throws Exception {
        Connection conn = getConnection();
        try {
           PreparedStatement ps = conn.prepareStatement(statement);
           return ps.executeUpdate();
        } finally {
            releaseConnection(conn);
        }
    }
    
    /**
     * Helper wrapper around boilerplat JDBC code.  Execute a statement
     * that returns results using a PreparedStatement that takes no 
     * parameters (you're on your own if you're binding parameters).
     *
     * @return the results from the query
     */
    public static ResultSet executeQueryNoParams(Connection conn, 
            String statement) throws Exception {
       PreparedStatement ps = conn.prepareStatement(statement);
       return ps.executeQuery();
    }
}

--Boundary_(ID_JwzZjbYY0sXAjLtOAsx8wg)--