[JBoss-dev] [ jboss-Bugs-1062429 ] Problem deleting relations
Bugs item #1062429, was opened at 2004-11-08 06:09
Message generated for change (Comment added) made by starksm
You can respond by visiting:
https://sourceforge.net/tracker/?func=detailatid=376685aid=1062429group_id=22866
Category: JBossCMP
Group: v3.2
Status: Open
Resolution: None
Priority: 5
Submitted By: Javier Pedrido (javierpedrido)
Assigned to: Alexey Loubyansky (loubyansky)
Summary: Problem deleting relations
Initial Comment:
I have two CMP 2.x entity beans that have a m:n
relationship (ModelEJB and NodeEJB) and a typical model
has thousands of nodes. The problems arrises when I
want to delete all the nodes related to a model, the
class
org.jboss.ejb.plugins.cmp.jdbc.JDBCDeleteRelationsCommand
creates a sql command so large that neither Oracle nor
MS SqlServer can handle it.
--
Comment By: Scott M Stark (starksm)
Date: 2004-12-29 12:52
Message:
Logged In: YES
user_id=175228
All issues have been moved to http://jira.jboss.com. Existing
issues have been moved. New issues will be closed with this
canned reponse.
--
Comment By: Pekka Vainio (pvainio)
Date: 2004-12-15 04:02
Message:
Logged In: YES
user_id=125350
Here is a quick fix I had to write for this problem for Sybase.
Limit 150 relations per delete statement is hardcoded into
source. I can write more sophiticated version if needed.
I hope this helps.
--- clip ---
/*
* JBoss, the OpenSource J2EE webOS
*
* Distributable under LGPL license.
* See terms of license at gnu.org.
*/
package org.jboss.ejb.plugins.cmp.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import javax.ejb.EJBException;
import javax.sql.DataSource;
import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMPFieldBridge;
import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge;
import org.jboss.logging.Logger;
/**
* Deletes relations from a relation table.
*
* @author a href=mailto:[EMAIL PROTECTED]Dain
Sundstrom/a
* @author a href=mailto:[EMAIL PROTECTED]Alex Loubyansky/a
* @version $Revision: 1.10.2.7 $
*/
public final class JDBCDeleteRelationsCommand
{
private final Logger log;
public JDBCDeleteRelationsCommand(JDBCStoreManager manager)
{
// Create the Log
log = Logger.getLogger(
this.getClass().getName() +
. +
manager.getMetaData().getName());
}
public void execute(RelationData relationData)
{
if(relationData.removedRelations.size() == 0)
{
return;
}
Connection con = null;
PreparedStatement ps = null;
JDBCCMRFieldBridge cmrField =
relationData.getLeftCMRField();
// Get a copy of removed relations
ArrayList removed = new
ArrayList(relationData.removedRelations);
try
{
// get the connection
DataSource dataSource = cmrField.getDataSource();
con = dataSource.getConnection();
while( ! removed.isEmpty()) {
// Sybase allows max 300 parameters
// Remove 150 relations per statement
// Get first 150 relations
List toRemove = null;
if (removed.size() 150) {
toRemove = removed.subList(0,150);
} else {
toRemove = removed;
}
String sql =
createSQL(relationData,toRemove.size());
// create the statement
if(log.isDebugEnabled())
log.debug(Executing SQL: + sql);
ps = con.prepareStatement(sql);
// set the parameters
setParameters(ps, relationData, toRemove);
// execute statement
int rowsAffected = ps.executeUpdate();
JDBCUtil.safeClose(ps);
// remove deleted from list
toRemove.clear();
ps = null;
if(log.isDebugEnabled())
log.debug(Rows affected = + rowsAffected);
}
}
catch(Exception e)
{
throw new EJBException(Could not delete relations
from +
cmrField.getQualifiedTableName(), e);
}
finally
{
JDBCUtil.safeClose(ps);
JDBCUtil.safeClose(con);
}
}
private static String createSQL(RelationData
relationData,int removedRelations)
{
JDBCCMRFieldBridge left = relationData.getLeftCMRField();
JDBCCMRFieldBridge right =
relationData.getRightCMRField();
StringBuffer sql = new StringBuffer(300);
sql.append(SQLUtil.DELETE_FROM)
.append(left.getQualifiedTableName())
.append(SQLUtil.WHERE);
if(removedRelations 0)
{
StringBuffer whereClause = new StringBuffer(20);
whereClause.append('(');
[JBoss-dev] [ jboss-Bugs-1062429 ] Problem deleting relations
Bugs item #1062429, was opened at 2004-11-08 16:09
Message generated for change (Comment added) made by pvainio
You can respond by visiting:
https://sourceforge.net/tracker/?func=detailatid=376685aid=1062429group_id=22866
Category: JBossCMP
Group: v3.2
Status: Open
Resolution: None
Priority: 5
Submitted By: Javier Pedrido (javierpedrido)
Assigned to: Alexey Loubyansky (loubyansky)
Summary: Problem deleting relations
Initial Comment:
I have two CMP 2.x entity beans that have a m:n
relationship (ModelEJB and NodeEJB) and a typical model
has thousands of nodes. The problems arrises when I
want to delete all the nodes related to a model, the
class
org.jboss.ejb.plugins.cmp.jdbc.JDBCDeleteRelationsCommand
creates a sql command so large that neither Oracle nor
MS SqlServer can handle it.
--
Comment By: Pekka Vainio (pvainio)
Date: 2004-12-15 14:02
Message:
Logged In: YES
user_id=125350
Here is a quick fix I had to write for this problem for Sybase.
Limit 150 relations per delete statement is hardcoded into
source. I can write more sophiticated version if needed.
I hope this helps.
--- clip ---
/*
* JBoss, the OpenSource J2EE webOS
*
* Distributable under LGPL license.
* See terms of license at gnu.org.
*/
package org.jboss.ejb.plugins.cmp.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;
import javax.ejb.EJBException;
import javax.sql.DataSource;
import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMPFieldBridge;
import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCCMRFieldBridge;
import org.jboss.logging.Logger;
/**
* Deletes relations from a relation table.
*
* @author a href=mailto:[EMAIL PROTECTED]Dain
Sundstrom/a
* @author a href=mailto:[EMAIL PROTECTED]Alex Loubyansky/a
* @version $Revision: 1.10.2.7 $
*/
public final class JDBCDeleteRelationsCommand
{
private final Logger log;
public JDBCDeleteRelationsCommand(JDBCStoreManager manager)
{
// Create the Log
log = Logger.getLogger(
this.getClass().getName() +
. +
manager.getMetaData().getName());
}
public void execute(RelationData relationData)
{
if(relationData.removedRelations.size() == 0)
{
return;
}
Connection con = null;
PreparedStatement ps = null;
JDBCCMRFieldBridge cmrField =
relationData.getLeftCMRField();
// Get a copy of removed relations
ArrayList removed = new
ArrayList(relationData.removedRelations);
try
{
// get the connection
DataSource dataSource = cmrField.getDataSource();
con = dataSource.getConnection();
while( ! removed.isEmpty()) {
// Sybase allows max 300 parameters
// Remove 150 relations per statement
// Get first 150 relations
List toRemove = null;
if (removed.size() 150) {
toRemove = removed.subList(0,150);
} else {
toRemove = removed;
}
String sql =
createSQL(relationData,toRemove.size());
// create the statement
if(log.isDebugEnabled())
log.debug(Executing SQL: + sql);
ps = con.prepareStatement(sql);
// set the parameters
setParameters(ps, relationData, toRemove);
// execute statement
int rowsAffected = ps.executeUpdate();
JDBCUtil.safeClose(ps);
// remove deleted from list
toRemove.clear();
ps = null;
if(log.isDebugEnabled())
log.debug(Rows affected = + rowsAffected);
}
}
catch(Exception e)
{
throw new EJBException(Could not delete relations
from +
cmrField.getQualifiedTableName(), e);
}
finally
{
JDBCUtil.safeClose(ps);
JDBCUtil.safeClose(con);
}
}
private static String createSQL(RelationData
relationData,int removedRelations)
{
JDBCCMRFieldBridge left = relationData.getLeftCMRField();
JDBCCMRFieldBridge right =
relationData.getRightCMRField();
StringBuffer sql = new StringBuffer(300);
sql.append(SQLUtil.DELETE_FROM)
.append(left.getQualifiedTableName())
.append(SQLUtil.WHERE);
if(removedRelations 0)
{
StringBuffer whereClause = new StringBuffer(20);
whereClause.append('(');
// left keys
SQLUtil.getWhereClause(left.getTableKeyFields(),
whereClause)
.append(SQLUtil.AND);
// right keys
SQLUtil.getWhereClause(right.getTableKeyFields(), whereClause)
.append(')');
String whereClauseStr =
[JBoss-dev] [ jboss-Bugs-1062429 ] Problem deleting relations
Bugs item #1062429, was opened at 2004-11-08 14:09 Message generated for change (Tracker Item Submitted) made by Item Submitter You can respond by visiting: https://sourceforge.net/tracker/?func=detailatid=376685aid=1062429group_id=22866 Category: JBossCMP Group: v3.2 Status: Open Resolution: None Priority: 5 Submitted By: Javier Pedrido (javierpedrido) Assigned to: Nobody/Anonymous (nobody) Summary: Problem deleting relations Initial Comment: I have two CMP 2.x entity beans that have a m:n relationship (ModelEJB and NodeEJB) and a typical model has thousands of nodes. The problems arrises when I want to delete all the nodes related to a model, the class org.jboss.ejb.plugins.cmp.jdbc.JDBCDeleteRelationsCommand creates a sql command so large that neither Oracle nor MS SqlServer can handle it. -- You can respond by visiting: https://sourceforge.net/tracker/?func=detailatid=376685aid=1062429group_id=22866 --- This SF.Net email is sponsored by: Sybase ASE Linux Express Edition - download now for FREE LinuxWorld Reader's Choice Award Winner for best database on Linux. http://ads.osdn.com/?ad_id=5588alloc_id=12065op=click ___ JBoss-Development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
[JBoss-dev] [ jboss-Bugs-1062429 ] Problem deleting relations
Bugs item #1062429, was opened at 2004-11-08 16:09 Message generated for change (Settings changed) made by loubyansky You can respond by visiting: https://sourceforge.net/tracker/?func=detailatid=376685aid=1062429group_id=22866 Category: JBossCMP Group: v3.2 Status: Open Resolution: None Priority: 5 Submitted By: Javier Pedrido (javierpedrido) Assigned to: Alexey Loubyansky (loubyansky) Summary: Problem deleting relations Initial Comment: I have two CMP 2.x entity beans that have a m:n relationship (ModelEJB and NodeEJB) and a typical model has thousands of nodes. The problems arrises when I want to delete all the nodes related to a model, the class org.jboss.ejb.plugins.cmp.jdbc.JDBCDeleteRelationsCommand creates a sql command so large that neither Oracle nor MS SqlServer can handle it. -- You can respond by visiting: https://sourceforge.net/tracker/?func=detailatid=376685aid=1062429group_id=22866 --- This SF.Net email is sponsored by: Sybase ASE Linux Express Edition - download now for FREE LinuxWorld Reader's Choice Award Winner for best database on Linux. http://ads.osdn.com/?ad_id=5588alloc_id=12065op=click ___ JBoss-Development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
