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 23:52:12 GMT
Thanks for clarifying David.
Bill

----- Original Message ----- 
From: "David Van Couvering" <david@vancouvering.com>
To: "Derby Discussion" <derby-user@db.apache.org>
Sent: Wednesday, March 21, 2007 3:21 PM
Subject: Re: Single Transaction Using Multiple Method Calls That Open New 
Database Connections


> Hi, Bill
>
> The two tables REQUEST and EVENTS are specific to the application where I 
> wrote the DatabaseManager.  They are used to store local calendar events 
> and to store pending requests to Google Calendar.
>
> In terms of the DatabaseManager, if you want this to be a generic utility 
> class, you should probably get rid of references to these tables (I think 
> they're used only in clearTables if I remember right).
>
> David
>
> Bill Slack wrote:
>> 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