1 Reply Latest reply on Aug 11, 2003 7:58 AM by jamesstrachan

    developing BMP using JBoss & MySql

    droscaru Newbie

      Hello all,

      I'm new to JBoss and EJB, and I have a problem.

      I'm using JBoss 3.2.1, MySql 4.0.13, JConnector 3.0.8.
      I have a BMP for a table like this:

      USERS
      ------------
      id
      name
      password

      For testing this bean, I'm using a jsp page having a session bean that wraps the BMP (the constructor
      of this class looks up for the BMP once per http session).
      On the top of the jsp I have an HTML fom for inserting an username and a password; after submiting,
      I call in the action file (another jsp) the create method of the BMP and then redirect to the first jsp.
      The first thing I don't understand is: for every create method the BMP container calls setEntityContext
      method and getts a connection to DB from the pool. Is this the right behavior of my BMP? Here the problem is:
      if I add 20-25 records in my table, then I get an error telling me there is no connection available to DB.
      I can't see anywhere called the unsetEntityContext method which closes the DB connection.
      The next issue: I call a finder method for selecting all records and displaying the NAME and PASSWORD fields.
      Here I see that for every record from the DB, again it is called the setEntityContext method.. and when it
      gets 20-25 records, it crashes again showing an error like: ejbStore: null. This method executes an UPDATE
      in the DB. Why is this method called for a finder method???
      Maybe my code for BMP is a mess..

      Any advice will be helpful.

      Razvan Droscaru


      THE BMP
      ---------------

      package winalocatatest;

      import javax.ejb.*;
      import java.sql.*;
      import javax.sql.*;
      import java.util.*;
      import java.math.*;
      import javax.naming.*;
      import util.*;

      public class UsersBean implements EntityBean
      {
      private EntityContext entityContext;
      private Integer id;
      private String name;
      private String password;
      private Connection con;
      private String dbName = "java:WinaLocataDS";

      public Integer ejbCreate( String name, String password ) throws CreateException {
      Integer autoIncKeyFromApi = new Integer( -1 );
      try
      {
      autoIncKeyFromApi = insertRow( name, password );
      } catch (Exception ex)
      {
      throw new EJBException( "ejbCreate: " + ex.getMessage() );
      }
      setName(name);
      setPassword(password);
      setId( autoIncKeyFromApi );
      return autoIncKeyFromApi;
      }

      public void ejbPostCreate( String name, String password ) throws CreateException {
      /**@todo Complete this method*/
      }
      public void ejbRemove() throws RemoveException {
      try
      {
      deleteRow( id );
      } catch (Exception ex)
      {
      throw new EJBException( "ejbRemove: " + ex.getMessage() );
      }
      }
      public void setId( Integer id)
      {
      this.id = id;
      }
      public void setName(String name)
      {
      this.name = name;
      }
      public void setPassword(String password)
      {
      this.password = password;
      }
      public Integer getId()
      {
      return id;
      }
      public String getName()
      {
      return name;
      }
      public String getPassword()
      {
      return password;
      }
      public Integer ejbFindByPrimaryKey( Integer id ) throws FinderException {
      boolean result;
      try
      {
      result = selectByPrimaryKey(id);
      } catch (Exception ex)
      {
      throw new EJBException( "ejbFindByPrimaryKey: " + ex.getMessage() );
      }
      if (result) {
      return id;
      }
      else
      {
      throw new ObjectNotFoundException("Row for id " + id + " not found.");
      }
      }
      public Collection ejbFindByUserName( String userName ) throws FinderException
      {
      try
      {
      return selectByUserName( userName );
      } catch (Exception ex)
      {
      throw new EJBException( "ejbFindByPrimaryKey: " + ex.getMessage() );
      }
      }
      public Collection ejbFindAll( ) throws FinderException
      {
      try
      {
      return selectAll( );
      } catch (Exception ex)
      {
      throw new EJBException( "ejbFindByPrimaryKey: " + ex.getMessage() );
      }
      }
      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( "ejbStore: " + ex.getMessage() );
      }
      }
      public void ejbActivate()
      {
      id = (Integer)entityContext.getPrimaryKey();
      }
      public void ejbPassivate()
      {
      id = null;
      }
      public void unsetEntityContext()
      {
      try
      {
      con.close();
      System.out.println("close connection");
      } catch (SQLException ex)
      {
      throw new EJBException("unsetEntityContext: " + ex.getMessage());
      }
      }
      public void setEntityContext(EntityContext entityContext)
      {
      this.entityContext = entityContext;
      try
      {
      makeConnection();
      } catch (Exception ex)
      {
      throw new EJBException( "Unable to connect to database. " + ex.getMessage() );
      }
      }

      /**
      * ******************************************************************
      */
      private boolean selectByPrimaryKey( Integer id ) throws SQLException
      {
      String selectStatement = "SELECT ID FROM USERS WHERE ID = ? ";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setInt( 1, id.intValue() );

      ResultSet rs = prepStmt.executeQuery();
      boolean result = rs.next();
      prepStmt.close();
      return result;
      }

      private Collection selectByUserName( String userName ) throws SQLException
      {
      String selectStatement = "SELECT ID FROM USERS WHERE NAME = ? ";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);
      prepStmt.setString( 1, userName );

      ResultSet rs = prepStmt.executeQuery();
      ArrayList a = new ArrayList();
      while(rs.next())
      {
      a.add( new Integer( rs.getInt( 1 ) ) );
      }
      prepStmt.close();

      return a;
      }

      private Collection selectAll( ) throws SQLException
      {
      String selectStatement = "SELECT ID FROM USERS";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);

      ResultSet rs = prepStmt.executeQuery();
      ArrayList a = new ArrayList();
      while(rs.next())
      {
      a.add( new Integer( rs.getInt( 1 ) ) );
      }
      prepStmt.close();

      return a;
      }

      private Integer insertRow( String name, String password ) throws SQLException
      {
      Integer autoIncKeyFromApi = new Integer( -1 );
      String insertStatement = "INSERT INTO USERS(NAME, PASSWORD) VALUES( ?, ? )";
      PreparedStatement prepStmt = con.prepareStatement(insertStatement, PreparedStatement.RETURN_GENERATED_KEYS);
      prepStmt.setString( 1, name );
      prepStmt.setString( 2, password );

      prepStmt.executeUpdate();

      ResultSet primKey = prepStmt.getGeneratedKeys();
      if (primKey.next()) {
      autoIncKeyFromApi = new Integer( primKey.getInt( 1 ) );
      } else {
      System.out.println("Failed to retrieve AUTO_INCREMENT using Statement.getGeneratedKeys()");
      }
      prepStmt.close();
      return autoIncKeyFromApi;
      }

      private void makeConnection() throws NamingException, SQLException
      {
      System.out.println( "lookingup connection" );
      InitialContext ic = new InitialContext();
      DataSource ds = (DataSource) ic.lookup(dbName);
      con = ds.getConnection();
      }

      private void loadRow() throws SQLException
      {
      try
      {
      String selectStatement = "SELECT ID, NAME, PASSWORD FROM USERS WHERE ID = ? ";
      PreparedStatement prepStmt = con.prepareStatement(selectStatement);

      if (this.id != null)
      prepStmt.setInt(1, this.id.intValue());
      else
      prepStmt.setInt(1, -1);

      ResultSet rs = prepStmt.executeQuery();

      if (rs.next())
      {
      this.id = new Integer(rs.getInt(1));
      this.name = rs.getString(2);
      this.password = rs.getString(3);
      prepStmt.close();
      }
      else
      {
      prepStmt.close();
      //throw new NoSuchEntityException("Row for id " + id + " not found in database.");
      }
      } catch( Exception e )
      {
      System.out.println( e );
      }
      }

      private void storeRow() throws SQLException
      {
      String updateStatement = "UPDATE USERS SET NAME = ?, PASSWORD = ? WHERE ID = ?";
      PreparedStatement prepStmt = con.prepareStatement(updateStatement);

      prepStmt.setString( 1, name );
      prepStmt.setString( 2, password );
      prepStmt.setInt( 3, this.id.intValue() );
      int rowCount = prepStmt.executeUpdate();
      prepStmt.close();

      if (rowCount == 0)
      {
      throw new EJBException("Storing row for id " + id + " failed.");
      }
      }

      private void deleteRow( Integer id ) throws SQLException
      {
      String deleteStatement = "DELETE FROM USERS WHERE ID = ? ";
      PreparedStatement prepStmt = con.prepareStatement(deleteStatement);

      prepStmt.setInt( 1, id.intValue() );
      prepStmt.executeUpdate();
      prepStmt.close();
      }
      }



      THE CLASS THAT WRAPS THE BMP
      --------------------------------------------------------

      package util;

      import winalocatatest.*;
      import java.util.*;
      import javax.ejb.*;
      import java.math.*;
      import javax.naming.*;
      import javax.rmi.PortableRemoteObject;
      import java.rmi.RemoteException;

      public class WLManage implements java.io.Serializable
      {
      private UsersHome home = null;

      public WLManage()
      {
      System.out.println("WLManage constructor");
      try
      {
      InitialContext ic = new InitialContext();
      Object objRef = ic.lookup("Users");
      home = (UsersHome)PortableRemoteObject.narrow(objRef, UsersHome.class);
      System.out.println("lookingup EJB");
      }
      catch (NamingException ex)
      {
      System.out.println( ex.getMessage() );
      }
      }

      public Users addUser( String name, String password )
      {
      try
      {
      return home.create( name, password );
      } catch( javax.ejb.CreateException e )
      {
      System.out.println( e );
      }
      return null;
      }

      public Collection selectAll()
      {
      try
      {
      return home.findAll();
      } catch( javax.ejb.FinderException e )
      {
      System.out.println( e );
      }
      return null;
      }

      public Collection findByUserName( String name )
      {
      try
      {
      return home.findByUserName( name );
      } catch( javax.ejb.FinderException e )
      {
      System.out.println( e );
      }
      return null;
      }
      }



      THE FIRST JSP PAGE
      ----------------------------------

      <%@ page import="winalocatatest.*, java.util.*" %>
      <jsp:useBean id="winaLocata" scope="session" class="util.WLManage" />



      index







      User Name



      Password
      < input type="text" name="password">
      <i nput type="submit" value="Add">




      <%
      Collection users = winaLocata.selectAll();
      Iterator it = users.iterator();
      while(it.hasNext())
      {
      Users user = (Users)it.next();
      out.print("name: " + user.getName() + "");
      out.print("password: " + user.getPassword() + "");
      }
      %>






      THE SECOND JSP PAGE
      ---------------------------------------

      <%@ page import="winalocatatest.*, java.util.*" %>
      <jsp:useBean id="winaLocata" scope="session" class="util.WLManage" />
      <%
      String name = "" + request.getParameter( "name" );
      String password = "" + request.getParameter( "password" );
      Collection user = winaLocata.findByUserName( name );
      if( !user.isEmpty() )
      {
      response.sendRedirect( "index.jsp" );
      }
      else
      {
      winaLocata.addUser( name, password );
      response.sendRedirect( "index.jsp" );
      }
      %>

        • 1. Re: developing BMP using JBoss & MySql
          jamesstrachan Newbie

          You're getting the connection in the wrong place.

          You are getting a connection for each EJB and never releasing it.

          The DataSource class assumes that connections are pooled - they are obtained from DataSource and closed as soon as possible.

          So a pool of (say) twenty connections can service many more than twenty users - because they are not all active at the same time.

          But, in your implementation, the connections are never closed and MySQL (or its DataSource connection pool) runs out of connections.

          To use Datasource as intended, you should get a Connection at the start of each BMP method, and close it just before the end of the method. As below :-

          ejbCreate( Object thisAndThat ) {

          // Get a database connection.
          context = new InitialContext();
          dataSource = (DataSource) context.lookup( DATASTORE_ADDRESS );
          conn = dataSource.getConnection();

          // BMP Code follows

          conn.close();

          } // Method ends.

          This all needs to be wrapped in try/catch exception handling.

          James Strachan