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" );
}
%>