db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From David Van Couvering <da...@vancouvering.com>
Subject Re: Single Transaction Using Multiple Method Calls That Open New Database Connections
Date Wed, 21 Mar 2007 22:21:16 GMT
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