db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bill Slack <wsl...@shaw.ca>
Subject Re: Single Transaction Using Multiple Method Calls That Open New Database Connections
Date Wed, 21 Mar 2007 21:57:31 GMT
Hi David,

I kept a copy of your message because I was intested in the 
"DatabaseManager" program that you attached.

I am just studying the code and I am curious to know if the two tables 
"REQUEST" and "EVENTS" are for the purpose of testing the program, or if 
they have another purpose that I am missing?

Excuse my ignorance, but I'm just learning this topic.

Thanks for your help,
Bill

----- Original Message ----- 
From: "David Van Couvering" <david@vancouvering.com>
To: "Derby Discussion" <derby-user@db.apache.org>; <nbusers@netbeans.org>
Sent: Thursday, February 15, 2007 6:48 PM
Subject: Re: Single Transaction Using Multiple Method Calls That Open New 
Database Connections


> 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
>>
>>
>


--------------------------------------------------------------------------------


> /*
>
>   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();
>    }
> }
> 


Mime
View raw message