Does my oracle pooling works

Discussions

General J2EE: Does my oracle pooling works

  1. Does my oracle pooling works (3 messages)

    I have a doubt whether my oracle pooling works after I finished all the source code for a three tier web application:

    I create a ConnectionBean which is a java bean dedicated to get all the connections with oracle database, this bean pool the connections.
    ConnectionBean.java:
    import oracle.jdbc.pool.OracleDataSource;
    public class ConnectionBean {
        private final static String JDBC_CONNECT_STRING =
        "jdbc:oracle:thin:@xxxxxxx:1521:ORCL";
        private final static String JDBC_USER_NAME = "xxxxxxx";
        private final static String JDBC_PASSWORD = "xxxxxxx";
        private static Stack connStack = new Stack();
        private static boolean driverLoaded = false;
        private OracleConnection conn;

        public OracleConnection getConnection() throws SQLException{
            synchronized( connStack ) {
                //register driver here
               ...........................
                if ( connStack.empty() ){
                                   OracleDataSource ods = new OracleDataSource();
                                        ods.setUser(JDBC_USER_NAME);
                                        ods.setPassword(JDBC_PASSWORD );
                                        ods.setURL(JDBC_CONNECT_STRING);
                                     conn = (OracleConnection)ods.getConnection();
                    try {
                        OrdMediaUtil.imCompatibilityInit( conn );
                    } catch ( Exception e ) {
                        throw new SQLException( e.toString() ); }
                }else{
                    conn = (OracleConnection)connStack.pop();
                } }//synchronized
            conn.setAutoCommit( true );
            return conn; }

        public void freeConnection() {
            if ( conn != null ){
                synchronized( connStack ) {
                    connStack.push( conn );
                }}}
    }//class

    There is also a LoginBean which use the ConnectionBean to get data: I create a ConnectionBean object in this class, but when I finished the process of login or whatever application data process, I need to close the connection by call release() function
    LoginBean.java

    public class LoginBean{
        private static String SELECT_SQL = "select rolegroup from principals_table natural join roles_table where username= ? and password = ? ";
        private OracleConnection conn;
        private OraclePreparedStatement stmt;
        private OracleResultSet rset;
        private ConnectionBean connBean = new ConnectionBean();
        public String login(String username, String password)
        throws ServletException, IOException, SQLException {
                 if ( conn == null ) {
                      conn = connBean.getConnection();
                  }
                  String result = null;
                   stmt = (OraclePreparedStatement)conn.prepareStatement(SELECT_SQL);
                  stmt.setString( 1, username);
                        stmt.setString( 2, password);
                        rset = (OracleResultSet)stmt.executeQuery();
                       if( !rset.next() )
                    //some code here
                     ................
                       release();
                 return result;
         }

        public void release() throws SQLException {
            if ( rset != null ){
                rset.close();
                rset = null;
            }if ( stmt != null ){
                stmt.close();
                rset = null;
            }if ( conn != null ) {
                connBean.freeConnection();------------------------------------?(1)
                //not sure if this is necessary:
                conn = null;----------------------------------?(2)
                connBean = null;----------------------------------------?(3)
            }}}//class

    My question is how I can cut off the connection between the application and database?
    obviously at
     line (1) connBean.freeConnection();
    can return the conction object to the stack for next use. but after that, is it necessary to call line (2)? In addition, when I call line(3), would this destroy the connBean object? if this is true, then the stack object would be destroyed, then, where is the use of pooling?

    Also, In my LoginAction.java which is a structs action,

    public class LoginAction extends Action{
      public ActionForward execute(ActionMapping mapping,
                                   ActionForm form,
                                   HttpServletRequest request,
                                   HttpServletResponse response)
        throws Exception {
        UserBean userBean = (UserBean)form;
        String username = userBean.getUsername();
        String password = userBean.getPassword();
        LoginBean loginBean = new LoginBean();
        String rolegroup = loginBean.login(username, password);
        userBean.setRolegroup(rolegroup);
        loginBean = null;---------------------------------------------------?(4)

    is line(4) necessary too? if it is, when I destroy this object, loginBean will further destroy it's class memeber connBean, if destroy the connBean, it will destroy the class memeber connStack object, then, where is the connection pooling mechanism?

    Any comment would be appreciated.

    Comment from paquicuba:

    For pooling you should add the following import statement:

    import oracle.jdbc.pool.OracleDataSource;

    Add a finalize method to close stements and connections:

    protected void finalize()
       {
          // attempt to close database connection
          try {
             stm.close();
             conn.close();
          }

          // process SQLException on close operation
          catch ( SQLException sqlException ) {
             sqlException.printStackTrace();
          }
       }

    Comment from arthurwang
    Date: 03/26/2005 03:18PM PST
    Your Comment

    Thanks Paqucuba, I do have that import statement, I just did not put here for a concise post, also, i did not have the finalize function, but I did have a release function to close database connection, the is done by following statement:

    connBean.freeConnection();------------------------------------?(1)

    and the freeConnection() will return the connection into a stack implemented in ConnectionBean class.

    Comment from paquicuba
    Date: 03/26/2005 04:20PM PST

    I haven't worked with ConnectionBean (). I have used the EntityBeans.
    i.e.
    DataBase MyDB = DataBase.getInstance();
    Connection c = MyDB.getConnection();
    try {
       ....
       ....
    } catch (SQLException e) {
        e.printStackTrace();
    }
    MyDB.freeConnection(c); // returns the Connection

    In your case I would try something like:

    public void release() throws SQLException {
            if ( rset != null ){
                rset.close();
                rset = null;
            }if ( stmt != null ){
                stmt.close();
                stmt = null;
            }if ( conn != null ) {
                conn.close();
                conn = null;
                connBean = null;

    Comment from arthurwang
    Date: 03/26/2005 08:11PM PST
    Your Comment

    Thanks again, paquicuba ,
    I understand that when you use entity bean, the container will do all the connection pooling for you, I just implement the oracle pooling by myself. In my case,
    ....................
               if ( conn != null ) {
                conn.close();
                conn = null;
                connBean = null;---------------?
    for the last statement, since connBean is an object contains the stack which implements the pooling mechanism, if detroy this object, JVM will also detroy the stack, and put this memory into heap stack, then, where is the stack after this call? where is the pooling after this statement? when the next connection is needed, another connBean has to be created as well as the stack, then this makes no sense to create the stack to implement the pooling. So I guess it's better to do as below:
           if ( rset != null ){
                rset.close();
                rset = null;
            }if ( stmt != null ){
                stmt.close();
                stmt = null;
            }if ( conn != null ) {
                connBean.freeConnection();
                conn.close();
                conn = null;
    }
         in this way, the object connBean can be kept so as the stack object. but when the next time a user create a LoginBean, and the object of the LoginBean will create a ConnectionBean Object, in this case, will this new ConnectionBean object be created by statement like :

    ConnectionBean connBean = new ConnectionBean();

    or simply get the previous one from the container?

    Threaded Messages (3)

  2. Does my oracle pooling works[ Go to top ]

    My recommendation is to use an existing connection pool implementation.

    For example, I don't see anywhere in your code that verifies if a connection is valid or not (if the db kill the connection after a timeout, and so on).

    I've used dbcp with success (it's also used by default in tomcat):
    http://jakarta.apache.org/commons/dbcp/

    good luck,
    sv
    btw, be careful with your names, a bean is a class with particular structural requirements.
  3. Does my oracle pooling works[ Go to top ]

    Thanks for your advice, I just tried out the apache dbcp example: ManualPoolingDriverExample.java, it works very well. I hope I know this a few days ago, then I would not use the oracle dataSource pooling. However,after I modified my class into a singleton class, it also works very well, but I did not pay attention to the connection validation issue. I will keep the dbcp way.

    By the way, I do need to pay attention to my class name, thanks for reminding me this.
  4. Does my oracle pooling works[ Go to top ]

    I searched for the connection validation and ask about it in the following link,

    https://www.theserverside.com/tss?service=direct/0/PostDiscussionReply/postReply&sp=l27681&sp=F&sp=l132231

    could anyone tell me where to config the connection validatio property in dbcp?