CMR, CMP and forgein keys as primary keys: maybe, a workarou

[mkelc]

"mkelc" wrote:
Hi folks!

We are developers in a german telecomunications company and are using JBoss
as an application server for an internal project.

One of our major problems with Jboss is a constraint in using foreign-keys
as part of a primary-key together with cmr.
We have observed in the forums that others have the same problem. As Dain
Sundstrom stated, this is not a bug but
merely a design-problem of the CMP-Engine (however).

Since we are using a given database structure it would be really
appreciated if we could use cmr to model the database relations.
Unfortunately this structure makes heavy use of foreign keys that are part
of primary keys.

Instead of asking more silly questions we decided to have a look at the
jboss sources. So we worked out a small patch
that _seems_ to work. What we are doing is simply looking for fields that
are used in the construction of sql statements
more than once, removing them from the lists used for building the sql
statements (look at the patch).

We definitley know that this is a hack and not a real solution, but maybe,
its an idea.

Could someone test it?
Tell us, if we are on the completly wrong way?
Any side effects we are not seeing?


However, we will continue to test it and would be happy for feedback of any
kind.

Thanx in advance,

Andreas Ahrens,
Marian Kelc

The patch is for jboss-3.2beta
####################### cut here, patch follows #######################

diff -r -b -c
jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java

jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java
***
jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java

Sun May 26 01:04:50 2002
---
jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCCreateEntityCommand.java

Wed Sep 18 11:28:01 2002
***************
*** 79,94 ****
private List getInsertFields() {
List fields = entity.getFields();
List insertFields = new ArrayList(fields.size());
!
for(Iterator iter = fields.iterator(); iter.hasNext(); ) {
JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
! if(!field.isReadOnly()) {
insertFields.add(field);
}
}
return insertFields;
}

private String createEntityExistsSQL() {
StringBuffer sql = new StringBuffer();
sql.append("SELECT COUNT(*)");
--- 79,111 ----
private List getInsertFields() {
List fields = entity.getFields();
List insertFields = new ArrayList(fields.size());
! //Hack: try to omit the multiple insertion of columns from fields,
that are foreign-keys _and_ cmp-fields
! //Set to keep track of already inserted columns
! java.util.Set set = new java.util.HashSet();
for(Iterator iter = fields.iterator(); iter.hasNext(); ) {
JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
! //get the columname and avoid an ArrayIndexOutOfBoundsException
! String columnName= getColumnNameIfExists(field);
! if( (!field.isReadOnly()) && (columnName==null ||
(!set.contains(columnName))) ) {
insertFields.add(field);
+ if (columnName!=null) set.add(columnName);
}
}
return insertFields;
}

+ private String getColumnNameIfExists(JDBCFieldBridge field) {
+ if (field==null)
+ return null;
+ JDBCType type= field.getJDBCType();
+ if (type==null)
+ return null;
+ String[] columnNames= type.getColumnNames();
+ if (columnNames==null || columnNames.length==0)
+ return null;
+ return columnNames[0];
+ }
+
private String createEntityExistsSQL() {
StringBuffer sql = new StringBuffer();
sql.append("SELECT COUNT(*)");
diff -r -b -c
jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java

jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java
***
jboss-3.2.0beta-src.orig/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java

Sun May 26 01:04:50 2002
---
jboss-3.2.0beta-src/server/src/main/org/jboss/ejb/plugins/cmp/jdbc/JDBCStoreEntityCommand.java

Wed Sep 18 13:01:43 2002
***************
*** 47,67 ****
manager.getMetaData().getName());
}

public void execute(EntityEnterpriseContext ctx) {
List dirtyFields = entity.getDirtyFields(ctx);

! if(dirtyFields.isEmpty()) {
if(log.isTraceEnabled()) {
log.trace("Store command NOT executed. Entity is not dirty:
pk=" +
ctx.getId());
}
return;
}
-
// generate sql
StringBuffer sql = new StringBuffer();
sql.append("UPDATE ").append(entity.getTableName());
! sql.append(" SET ").append(SQLUtil.getSetClause(dirtyFields));
sql.append(" WHERE ").append(
SQLUtil.getWhereClause(entity.getPrimaryKeyFields()));

--- 47,105 ----
manager.getMetaData().getName());
}

+ private String getColumnNameIfExists(JDBCFieldBridge field) {
+ if (field==null)
+ return null;
+ JDBCType type= field.getJDBCType();
+ if (type==null)
+ return null;
+ String[] columnNames= type.getColumnNames();
+ if (columnNames==null || columnNames.length==0)
+ return null;
+ return columnNames[0];
+ }
+
+ private java.util.Set getColumnNamesSet(List fields) {
+ java.util.Set set= new java.util.HashSet();
+ for (java.util.Iterator itr= fields.iterator(); itr.hasNext(); ) {
+ String columnName= getColumnNameIfExists
((JDBCFieldBridge)itr.next());
+ if (columnName!=null)
+ set.add(columnName);
+ }
+ return set;
+ }
+
public void execute(EntityEnterpriseContext ctx) {
List dirtyFields = entity.getDirtyFields(ctx);

! //if(dirtyFields.isEmpty()) {
! // if(log.isTraceEnabled()) {
! // log.trace("Store command NOT executed. Entity is not dirty:
pk=" +
! // ctx.getId());
! // }
! // return;
! //}
!
! List updatedDirtyFields= new java.util.LinkedList();
! java.util.Set primaryKeyFields =
getColumnNamesSet(entity.getPrimaryKeyFields());
! for (java.util.Iterator itr= dirtyFields.iterator(); itr.hasNext();
) {
! JDBCFieldBridge field= (JDBCFieldBridge) itr.next();
! String columnName= getColumnNameIfExists(field);
! if (columnName==null || !primaryKeyFields.contains(columnName))
{
! updatedDirtyFields.add(field);
! }
! }
! if(updatedDirtyFields.isEmpty()) {
if(log.isTraceEnabled()) {
log.trace("Store command NOT executed. Entity is not dirty:
pk=" +
ctx.getId());
}
return;
}
// generate sql
StringBuffer sql = new StringBuffer();
sql.append("UPDATE ").append(entity.getTableName());
! sql.append(" SET
").append(SQLUtil.getSetClause(/*dirtyFields*/updatedDirtyFields));
sql.append(" WHERE ").append(
SQLUtil.getWhereClause(entity.getPrimaryKeyFields()));

***************
*** 80,86 ****

// set the parameters
int index = 1;
! for(Iterator iter = dirtyFields.iterator(); iter.hasNext(); ) {
JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
index = field.setInstanceParameters(ps, index, ctx);
}
--- 118,124 ----

// set the parameters
int index = 1;
! for(Iterator iter = updatedDirtyFields.iterator(); iter.hasNext
(); ) {
JDBCFieldBridge field = (JDBCFieldBridge)iter.next();
index = field.setInstanceParameters(ps, index, ctx);
}

####################### end of patch #######################

[dsundstrom]

"dsundstrom" wrote:
Post a patch at source forge.

The other half of this problem is removal. The remove protocol (ejb spec) calls for the removal of the entity from all relationships before removal from the database. This means that the foreign key must be set to null.

[mkelc]

"mkelc" wrote:
Ok, Dain, we have uploaded the patch.
It has the ID 621033.

Marian Kelc

[asashour]

"asashour" wrote:
Dear All,

I am stick with this issue for a long long time.

-------------------------------------------------------
I would like to know how to make a CMR also a CMP, as the return type of CMR is a local interface of the other bean, while if we used this as a CMP field, its type must match the one in the PK class.

public abstract OtherEntityLocal getOtherEntity()
public abstract void setOtherEntity( OtherEntityLocal local );

If anyone can post a working sample, it would be appreciated so much.

-------------------------------------------------------

I still don't know how to apply .patch file, or how to make one!!

Thank you very much,

Yours,
Ahmed Ashour

[juha]

What version of JBoss are you using? AFAIK this has been fixed long time ago in 3.2.x series.

[sarsipius]

I'm using 3.2.3 and having the same problems I've seen in other posts. (duplicate fields, etc.)

I also saw posts saying that it was fixed in 3.2, but it looks like it's back. At least in the package download from Nov 30, 2003.

[sarsipius]

Sorry, my bad. I found a typo in the fk-column. Had "ItemiD" instead of "ItemID", which of course causes problems on a case insensitive database. It works better now. :)

[asashour]

Thanks, CMR to a bean with a primitive PK can be CMP too, but consider the following scenario:

BeanA has a String PK.

BeanB has a FK to BeanA and a String ID, both of them are PK.

BeanC has a FK to BeanB and a String ID, both of them are PK.

As you know, the type of Bean implementation and PK should be the same for CMP.

In BeanB you will have

public abstract BeanALocal getBeanA();

and in BeanB_PK class:

public BeanALocal beanA;
public String beanB_ID;

In BeanC you will have

public abstract BeanBLocal getBeanB();

and in BeanC_PK class:

public BeanBLocal beanB;
public String beanC_ID;

Now, how to map BeanC.beanB field in the database.

And the PK class can be used by remote clients which should not see the Local interfaces at all!!!!

Really, I guess this is a major design issue of EJB, which prevents us to use it on legacy applications.

Thank you so much,

Yours,
Ahmed Ashour