What is the correct way to do an update with Entity bean?

[robr]

Currently I am using all the setters to just reset all the values. If I don't do that I will have to call each getter and see if my new value is different and if it is then do the update.

This seems like a very clunky way to do this. Is there some other better way to do this?

I am new to J2EE.

[robr]

I have done some testing.

I have created a table that has 7 columns. I have created getters and setters for each column in an entity bean for the table.

I have a jsp that I use to create a new record or update an existing record. I have turned sql logging on in JBOSS so I can see what is happening under the hood.

If the code in the Struts Action determines an update is being done I currently call all 7 setters to do the update since I don't want to compare each new value against the old value. I think it might be more efficient to just do the updates.

What the log shows me is that if I only update one of the columns only one of the getters causes an sql update to be executed. For any column value that is different than the current value an sql statement is executed. This seems a inefficient. It seems like it would be better to issue one update statement that included only the columns that had been changed.

Anybody know how to make this added efficiency take place in an entity bean?

I did some research in several books I have and on Google and didn't find any info on the subject.

[darranl]

Where is the code that is invoking the set methods on the entity bean?

If the code that is calling the entity bean is within a struts action have you obtained a reference to the UserTransaction object and started a transaction or have you just obtained the entity bean without any transaction?

If you are seeing SQL for every update you make it sounds like a new transaction is being used each time you invoke a method on the entity bean.

If this is the case you have two real options to start with.

1 - Wrap all access to the entity bean with a session bean and have one method in the session bean update the 7 fields in a single operation.

2 - Obtain a reference to the UserTransaction object in the web application and begin a transaction before the updates and commit it at the end.

[robr]

You are correct I have just obtained a reference to the EJB in the Struts action and then use the methods of the Entity bean. So I guess you are saying that this method causes a transaction for every line of code that uses a method from the entity bean.

You mentioned using a session bean to wrap around the entity bean. Do you recommend stateful or stateless. Also are you saying in the session bean to just create my own jdbc code that will do a one line update on all the columns at one go regardless of how many have or haven't been updated?

I have also noticed another weird thing about the sql captured in the log.

I have the following code in one of my actions.

 UsersEntityHome usersEntityHome = (UsersEntityHome) new InitialContext().lookup("java:comp/env/ejb/UsersEntity");
 userEntity = usersEntityHome.findByPrimaryKey(Integer.valueOf(sId));

 request.setAttribute("id", sId); // Set the id so we can create a hidden field with the id in it.
 request.setAttribute("fname", userEntity.getFname());
 request.setAttribute("lname", userEntity.getLname());
 request.setAttribute("email", userEntity.getEmail());
 request.setAttribute("username", userEntity.getUsername());
 request.setAttribute("password", userEntity.getPassword());
 request.setAttribute("role", userEntity.getRole());
 request.setAttribute("partner", userEntity.getPartner());

Every time I use one of the getters the following sql statement is executed according to the sql log.

Executing SQL: SELECT fname, lname, email, username, password, role, partner FROM Users WHERE (id=?)

Why would the code need to execute the select on all 7 columns 7 times to get each column. I thought the member variables(hidden) would be set once and then be used. Maybe you are saying I also need to put this in a transaction so the sql will only be executed once.

Thanks for your advice.

[robr]

The following code contains the solution to my question.

I got a UserTransaction object and then did a begin and end transaction around the updates so that only one update was done instead of individual updates for each setter in the block.

 if (!bAbortInsertUpdate) {
 try {
 // userEntity = usersEntityHome.findUsernameEquals(sId); Leave this in for now example of how to create a custom finder for an entity bean fixrb
 if (sId == null || sId.equals("")) // if sId not set set it to a record that does not exist so a new one will get created.
 sId = "0";
 userEntity = usersEntityHome.findByPrimaryKey(Integer.valueOf(sId));
 Context initContext = new InitialContext();
 UserTransaction tran = (UserTransaction) initContext.lookup("java:comp/UserTransaction");
 tran.begin();
 userEntity.setFname(sFname);
 userEntity.setLname(sLname);
 userEntity.setEmail(sEmail);
 userEntity.setUsername(sUserName);
 if (!sPassword.equals(""))
 userEntity.setPassword(sPassword);
 userEntity.setRole(sRole);
 userEntity.setPartner(sPartner);
 tran.commit();

 } catch (FinderException noFound) {
 try {
 userEntity = usersEntityHome.create(sFname, sLname, sEmail, sUserName, sPassword, sRole, sPartner);
 } catch (CreateException sqlExc) {
 } catch (RemoteException reExc) {
 }
 }