I'm pretty new to EJBs and was wondering about connection pooling mechanism used. In the J2EE reference implementation the examples of Bean managed persistance (see below) obtains a reference to a datasource and then it obtains a connection from the datasource when it is instantiated. The bean then uses the connection for all database interactions, only calling the close method when the bean is removed from the container. Does this mean that for the duration of the existance of the Bean it maintains an open connection to database ? If this is the case then that would mean if I had 10000 beans I would have 10000 open DB connections ... ? Is there a reason why they do not cache the Datasource reference from the lookup and do a ds.getConnection() in the store and load methods and then call the con.close() after each is complete ? Does orion/app servers in general use some kind of wrapper on the Connection object to perform the pooling at a lower level through the Connection object ? Is there a better way to handle the connections ... ? Regards, Manuel > Manuel De Jesus > Software Engineer - IT > Vodacom World Online (Pty) Ltd > 80 Strand Street, Cape Town > [EMAIL PROTECTED] > <http://www.worldonline.co.za> > > /* * * Copyright 2000 Sun Microsystems, Inc. All Rights Reserved. * * This software is the proprietary information of Sun Microsystems, Inc. * Use is subject to license terms. * */ import java.sql.*; import javax.sql.*; import java.util.*; import javax.ejb.*; import javax.naming.*; public class AccountEJB implements EntityBean { private String id; private String firstName; private String lastName; private double balance; private EntityContext context; private Connection con; private String dbName = "java:comp/env/jdbc/AccountDB"; public void debit(double amount) throws InsufficientBalanceException { if (balance - amount < 0) { throw new InsufficientBalanceException(); } balance -= amount; } public void credit(double amount) { balance += amount; } public String getFirstName() { return firstName; } public String getLastName() { return lastName; } public double getBalance() { return balance; } public String ejbCreate(String id, String firstName, String lastName, double balance) throws CreateException { if (balance < 0.00) { throw new CreateException ("A negative initial balance is not allowed."); } try { insertRow(id, firstName, lastName, balance); } catch (Exception ex) { throw new EJBException("ejbCreate: " + ex.getMessage()); } this.id = id; this.firstName = firstName; this.lastName = lastName; this.balance = balance; return id; } public String ejbFindByPrimaryKey(String primaryKey) throws FinderException { boolean result; try { result = selectByPrimaryKey(primaryKey); } catch (Exception ex) { throw new EJBException("ejbFindByPrimaryKey: " + ex.getMessage()); } if (result) { return primaryKey; } else { throw new ObjectNotFoundException ("Row for id " + primaryKey + " not found."); } } public Collection ejbFindByLastName(String lastName) throws FinderException { Collection result; try { result = selectByLastName(lastName); } catch (Exception ex) { throw new EJBException("ejbFindByLastName " + ex.getMessage()); } if (result.isEmpty()) { throw new ObjectNotFoundException("No rows found."); } else { return result; } } public Collection ejbFindInRange(double low, double high) throws FinderException { Collection result; try { result = selectInRange(low, high); } catch (Exception ex) { throw new EJBException("ejbFindInRange: " + ex.getMessage()); } if (result.isEmpty()) { throw new ObjectNotFoundException("No rows found."); } else { return result; } } public void ejbRemove() { try { deleteRow(id); } catch (Exception ex) { throw new EJBException("ejbRemove: " + ex.getMessage()); } } public void setEntityContext(EntityContext context) { this.context = context; try { makeConnection(); } catch (Exception ex) { throw new EJBException("Unable to connect to database. " + ex.getMessage()); } } public void unsetEntityContext() { try { con.close(); } catch (SQLException ex) { throw new EJBException("unsetEntityContext: " + ex.getMessage()); } } public void ejbActivate() { id = (String)context.getPrimaryKey(); } public void ejbPassivate() { id = null; } public void ejbLoad() { try { loadRow(); } catch (Exception ex) { throw new EJBException("ejbLoad: " + ex.getMessage()); } } public void ejbStore() { try { storeRow(); } catch (Exception ex) { throw new EJBException("ejbLoad: " + ex.getMessage()); } } public void ejbPostCreate(String id, String firstName, String lastName, double balance) { } /*********************** Database Routines *************************/ private void makeConnection() throws NamingException, SQLException { InitialContext ic = new InitialContext(); DataSource ds = (DataSource) ic.lookup(dbName); con = ds.getConnection(); } private void insertRow (String id, String firstName, String lastName, double balance) throws SQLException { String insertStatement = "insert into account values ( ? , ? , ? , ? )"; PreparedStatement prepStmt = con.prepareStatement(insertStatement); prepStmt.setString(1, id); prepStmt.setString(2, firstName); prepStmt.setString(3, lastName); prepStmt.setDouble(4, balance); prepStmt.executeUpdate(); prepStmt.close(); } private void deleteRow(String id) throws SQLException { String deleteStatement = "delete from account where id = ? "; PreparedStatement prepStmt = con.prepareStatement(deleteStatement); prepStmt.setString(1, id); prepStmt.executeUpdate(); prepStmt.close(); } private boolean selectByPrimaryKey(String primaryKey) throws SQLException { String selectStatement = "select id " + "from account where id = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, primaryKey); ResultSet rs = prepStmt.executeQuery(); boolean result = rs.next(); prepStmt.close(); return result; } private Collection selectByLastName(String lastName) throws SQLException { String selectStatement = "select id " + "from account where lastname = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, lastName); ResultSet rs = prepStmt.executeQuery(); ArrayList a = new ArrayList(); while (rs.next()) { String id = rs.getString(1); a.add(id); } prepStmt.close(); return a; } private Collection selectInRange(double low, double high) throws SQLException { String selectStatement = "select id from account " + "where balance between ? and ?"; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setDouble(1, low); prepStmt.setDouble(2, high); ResultSet rs = prepStmt.executeQuery(); ArrayList a = new ArrayList(); while (rs.next()) { String id = rs.getString(1); a.add(id); } prepStmt.close(); return a; } private void loadRow() throws SQLException { String selectStatement = "select firstname, lastname, balance " + "from account where id = ? "; PreparedStatement prepStmt = con.prepareStatement(selectStatement); prepStmt.setString(1, this.id); ResultSet rs = prepStmt.executeQuery(); if (rs.next()) { this.firstName = rs.getString(1); this.lastName = rs.getString(2); this.balance = rs.getDouble(3); prepStmt.close(); } else { prepStmt.close(); throw new NoSuchEntityException("Row for id " + id + " not found in database."); } } private void storeRow() throws SQLException { String updateStatement = "update account set firstname = ? ," + "lastname = ? , balance = ? " + "where id = ?"; PreparedStatement prepStmt = con.prepareStatement(updateStatement); prepStmt.setString(1, firstName); prepStmt.setString(2, lastName); prepStmt.setDouble(3, balance); prepStmt.setString(4, id); int rowCount = prepStmt.executeUpdate(); prepStmt.close(); if (rowCount == 0) { throw new EJBException("Storing row for id " + id + " failed."); } } } // AccountEJB
