CMR, CMP and forgein keys as primary keys: maybe, a workarou
mkelc Sep 18, 2002 11:34 AM
"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 #######################