Enlisting Custom DataSource with JTa

[shaby775]

Hello iCodix,
Thanks for your reply. Our problem is as follows, Our application is working with a database that had been made quite a long time ago (We are using Oracle 9i) for another application. As it was made for a two tier app at that time it manages security in database layer by using USER function and views. The user function returns the name of user who executed the database query. So in order to work with that database every user of our application must be the user of Oracle too and he needs to execute all the queries by using his oracle account. It means that we cannot use normal jdbc connection pooling, but fortunately Oracle provides a sloution whic is called Oracle Proxy connection in this way u can make a pool of connection by using only one account of Oracle as the normal case in J2ee and when you called getConnetcion and pass it username and password it gives u a proxy connection that uses the connection from the pool but with the real username and password. To implemet it we have to make OracleConnectionPool, supplying our own pool in normal scenioros pool is provide by app server. Our pool is as follows it also implements the DataSource

/*
 * Copyright 2002-2004 the original author or authors.
 *
 * Licensed 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.
 */

package org.springframework.jdbc.oracle;

import java.sql.*;
import java.util.*;

import org.springframework.jdbc.*;
import org.springframework.jdbc.datasource.*;
import org.springframework.util.*;
import oracle.jdbc.driver.*;
import oracle.jdbc.pool.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Implementation of SmartDataSource that configures an OracleOCIConnectionPool
 * via bean properties, and returns a new connection every time.
 *
 * @author Guido Schmutz
 * @since 12.05.2004
 *
 * @version $Id: OracleOCIConnectionPoolDataSource.java,v 1.6 2004/05/31 11:53:56 sakhtar Exp $
 * @see org.springframework.jndi.JndiObjectFactoryBean
 * @see org.apache.commons.dbcp.BasicDataSource
 */
public class OracleOCIConnectionPoolDataSource
    extends AbstractDataSource
    implements SmartDataSource
{

  private final Log logger = LogFactory.getLog(OracleOCIConnectionPoolDataSource.class);

  private OracleOCIConnectionStrategy connectionStrategy = null;

  private String connPoolMinLimit = "1";
  private String connPoolMaxLimit = "4";
  private String connPoolIncrement = "1";
  private String driverType = "oci8";
  private String tnsName = null;
  private String url = null;
  private String username = null;
  private String password = null;


  // ocipool of this instance
  private OracleOCIConnectionPool ocipool = null;
  private String connPoolTimeOut = "20";

  public void setConnectionStrategy(OracleOCIConnectionStrategy strategy)
  {
    this.connectionStrategy = strategy;
  }

  /**
   * @return Returns the connPoolIncrement.
   */
  public String getConnPoolIncrement()
  {
    return connPoolIncrement;
  }

  /**
   * @param connPoolIncrement The connPoolIncrement to set.
   */
  public void setConnPoolIncrement(String connPoolIncrement)
  {
    this.connPoolIncrement = connPoolIncrement;
  }

  /**
   * @return Returns the connPoolMaxLimit.
   */
  public String getConnPoolMaxLimit()
  {
    return connPoolMaxLimit;
  }

  /**
   * @param connPoolMaxLimit The connPoolMaxLimit to set.
   */
  public void setConnPoolMaxLimit(String connPoolMaxLimit)
  {
    this.connPoolMaxLimit = connPoolMaxLimit;
  }

  /**
   * @return Returns the connPoolMinLimit.
   */
  public String getConnPoolMinLimit()
  {
    return connPoolMinLimit;
  }

  /**
   * @param connPoolMinLimit The connPoolMinLimit to set.
   */
  public void setConnPoolMinLimit(String connPoolMinLimit)
  {
    this.connPoolMinLimit = connPoolMinLimit;
  }

  /**
   * @return Returns the driverType.
   */
  public String getDriverType()
  {
    return driverType;
  }

  /**
   * @param driverType The driverType to set.
   */
  public void setDriverType(String driverType)
  {
    this.driverType = driverType;
  }

  /**
   * @return Returns the password.
   */
  public String getPassword()
  {
    return password;
  }

  /**
   * @param password The password to set.
   */
  public void setPassword(String password)
  {
    this.password = password;
  }

  /**
   * @return Returns the tnsName.
   */
  public String getTnsName()
  {
    return tnsName;
  }

  /**
   * @param tnsName The tnsName to set.
   */
  public void setTnsName(String tnsName)
  {
    this.tnsName = tnsName;
  }

  /**
   * @return Returns the url.
   */
  public String getUrl()
  {
    return url;
  }

  /**
   * @param url The url to set.
   */
  public void setUrl(String url)
  {
    this.url = url;
  }

  /**
   * @return Returns the username.
   */
  public String getUsername()
  {
    return username;
  }

  public String getConnPoolTimeOut() {
    return connPoolTimeOut;
  }

  /**
   * @param username The username to set.
   */
  public void setUsername(String username)
  {
    this.username = username;
  }

  public void setConnPoolTimeOut(String connPoolTimeOut) {
    this.connPoolTimeOut = connPoolTimeOut;
  }

  /**
   * Constructor for bean-style configuration.
   */
  public OracleOCIConnectionPoolDataSource()
  {
  }

  /**
   * Create a new SingleConnectionDataSource with the given standard
   * DriverManager parameters.
   */
  public OracleOCIConnectionPoolDataSource(String url, String username,
                                           String password) throws
      CannotGetJdbcConnectionException
  {
    setUrl(url);
    setUsername(username);
    setPassword(password);
  }

  /**
   * This DataSource returns a new connection every time: Close it when returning one to the "pool".
   */
  public boolean shouldClose(Connection conn)
  {
    return true;
  }

  public Connection getConnection() throws SQLException
  {
    return getConnectionFromPool();
  }

  public Connection getConnection(String username, String password) throws
      SQLException
  {
    if (ObjectUtils.nullSafeEquals(username, getUsername()) &&
        ObjectUtils.nullSafeEquals(password, getPassword()))
    {
      return getConnection();
    }
    else
    {
      throw new SQLException(
          "OCIConnectionPoolDataSource does not support custom username and password");
    }
  }

  protected OracleConnection getConnectionFromPool() throws SQLException
  {
    OracleConnection conn = null;

    if (ocipool == null)
    {
      initPool();
    }

    if ( (connectionStrategy != null) && (connectionStrategy.getProxyUserName() != null))
    {
      if (connectionStrategy.getProxyUserName() != null &&
          connectionStrategy.getProxyUserPassword() != null)
      {

        conn = (OracleConnection) ocipool.getConnection(connectionStrategy.
            getProxyUserName(), connectionStrategy.getProxyUserPassword());
        logger.info("Given connecttion to user: " +
                           connectionStrategy.getProxyUserName() +
                           " password: " +
                           connectionStrategy.getProxyUserPassword());
      }

    }
    else
    {
      conn = (OracleConnection) ocipool.getConnection();
      logger.warn("Given default connection");
    }

    if ( (connectionStrategy != null) &&
        (connectionStrategy.getClientIdentifier() != null))
    {
      conn.setClientIdentifier(connectionStrategy.getClientIdentifier());
    }

    return conn;
  }

  /**
   * Initializes the connection pool
   * @throws SQLException
   */
  private void initPool() throws SQLException
  {
    // Create the pool
    ocipool = new OracleOCIConnectionPool();

    // Configure the Datasource
    configureDataSource();

    // Configure the connection pool
    configPool();
  }

  /**
   * Configures the DataSource with the driverTpye to use, user name, password, tnsEntry name
   * and URL
   * @throws SQLException
   */
  private void configureDataSource() throws SQLException
  {
    // Driver type either 'thin' or 'oci8'
    ocipool.setDriverType(this.driverType);

    // TNS Entry in case of oci8
    ocipool.setTNSEntryName(this.tnsName);

    // URL in case of thin
    ocipool.setURL(this.url);

    // user name
    ocipool.setUser(this.username);

    // password
    ocipool.setPassword(this.password);
  }

  /**
   * Configures the OCI Connection pool by setting the OCI Connectoin pool parameters
   * @throws SQLException
   */
  private void configPool() throws SQLException
  {

    Properties poolProp = new Properties();

    // the minimum number of physical conections maintained by the pool
    poolProp.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT,
                 this.connPoolMinLimit);

    // the maximum number of physical conections maintained by the pool
    poolProp.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT,
                 this.connPoolMaxLimit);

    // Incremental number of physical connections to be opended wehn all the existing ones
    // are busy and a new connection is requested.
    poolProp.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT,
                 this.connPoolIncrement);
    poolProp.put(OracleOCIConnectionPool.CONNPOOL_TIMEOUT, this.connPoolTimeOut);
    ocipool.setPoolConfig(poolProp);
  }
}

/*
 * Created on 12.05.2004
 *
 * TODO To change the template for this generated file go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
package org.springframework.jdbc.oracle;

/**
 * @author gus
 *
 * TODO To change the template for this generated type comment go to
 * Window - Preferences - Java - Code Generation - Code and Comments
 */
public interface OracleOCIConnectionStrategy
{
  public abstract String getProxyUserName();

  public abstract void setProxyUserName(String userName);

  public abstract String getProxyUserPassword();

  public abstract void setProxyUserPassword(String password);

  public abstract String getRoles();

  public abstract void setRoles(String roles);

  public abstract String getClientIdentifier();

  public abstract void setClientIdentifier(String clientId);
}

/*
 * Copyright 2002-2004 the original author or authors.
 *
 * Licensed 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.
 */

package org.springframework.jdbc.oracle;

import java.util.*;

/**
 * The implementation of the OracleOCIConnectoinStrategy interface. It's implemented as a thread
 * local so everything stored in such an instance is kept seperate for each thread.
 *
 *  * @author Guido Schmutz
 */
public class OracleOCIConnectionThreadLocalStrategy
    implements OracleOCIConnectionStrategy
{
  private static class ConnectionThreadLocal
      extends ThreadLocal
  {

    private final static String USERNAME = "username";
    private final static String PWD = "pwd";
    private final static String ROLES = "roles";
    private final static String CLIENT_ID = "clientID";

    protected Object initialValue()
    {
      return new HashMap();
    }

    private HashMap getHashMap()
    {
      return (HashMap)super.get();
    }

    public String getProxyUserName()
    {
      return (String) getHashMap().get(USERNAME);
    }

    public void setProxyUserName(String userName)
    {
      getHashMap().put(USERNAME, userName);
    }

    public String getProxyUserPassword()
    {
      return (String) getHashMap().get(PWD);
    }

    public void setProxyUserPassword(String password)
    {
      getHashMap().put(PWD, password);
    }

    public String getRoles()
    {
      return (String) getHashMap().get(ROLES);
    }

    public void setRoles(String roles)
    {
      getHashMap().put(ROLES, roles);
    }

    public String getClientIdentifier()
    {
      return (String) getHashMap().get(CLIENT_ID);
    }

    public void setClientIdentifier(String clientId)
    {
      getHashMap().put(CLIENT_ID, clientId);
    }
  }

  private static ConnectionThreadLocal connThreadLocal = new
      ConnectionThreadLocal();

  public String getProxyUserName()
  {
    return connThreadLocal.getProxyUserName();
  }

  public void setProxyUserName(String userName)
  {
    connThreadLocal.setProxyUserName(userName);
  }

  public String getProxyUserPassword()
  {
    return connThreadLocal.getProxyUserPassword();
  }

  public void setProxyUserPassword(String password)
  {
    connThreadLocal.setProxyUserPassword(password);
  }

  public String getRoles()
  {
    return connThreadLocal.getRoles();
  }

  public void setRoles(String roles)
  {
    connThreadLocal.setRoles(roles);
  }

  public String getClientIdentifier()
  {
    return connThreadLocal.getClientIdentifier();
  }

  public void setClientIdentifier(String clientId)
  {
    connThreadLocal.setClientIdentifier(clientId);
  }
}

I want to enlist my datsource so that it can participate in CMT transaction (JBoss 3.2.3)

[iCodix]

hi shaby,

What i do not understand, is if you need an XATransaction or a simple UserTransaction?

By the way, i see you are using spring's framework. As i just started with spring i do not know what it offers. Anyway as can i see you are using normal Datasource, I do not know if you re using XADataSource, because what you can enlist in a distributed transaction is a XAResource only, which is given by a XAConnection from a XADataSource. So here everything is XA, that means distributed transaction. So for that i do not know how you are using it ...

Tell me if you knew that or as i think you tried to enlist a simple java.sql.Connection, which is not possible. I used Jboss also but i did everything by MBean, so that means no automatic things like EJB, but i did a test with EJB and it worked fine.

So give me more informations and if you were not using XADataSource take a look on it.

Cheers,
Daniel