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=detail&atid=376685&aid=1062429&group_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('('); // left keys SQLUtil.getWhereClause(left.getTableKeyFields(), whereClause) .append(SQLUtil.AND); // right keys SQLUtil.getWhereClause(right.getTableKeyFields(), whereClause) .append(')'); String whereClauseStr = whereClause.toString(); sql.append(whereClauseStr); for(int i = 1; i < removedRelations; ++i) { sql.append(SQLUtil.OR).append(whereClauseStr); } } return sql.toString(); } private static void setParameters(PreparedStatement ps, RelationData relationData, Collection toDelete) throws Exception { int index = 1; Iterator pairs = toDelete.iterator(); JDBCCMPFieldBridge[] leftFields = (JDBCCMPFieldBridge[])relationData.getLeftCMRField().getTableKeyFields(); JDBCCMPFieldBridge[] rightFields = (JDBCCMPFieldBridge[])relationData.getRightCMRField().getTableKeyFields(); while(pairs.hasNext()) { RelationPair pair = (RelationPair)pairs.next(); // left keys Object leftId = pair.getLeftId(); for(int i = 0; i < leftFields.length; ++i) index = leftFields[i].setPrimaryKeyParameters(ps, index, leftId); // right keys Object rightId = pair.getRightId(); for(int i = 0; i < rightFields.length; ++i) index = rightFields[i].setPrimaryKeyParameters(ps, index, rightId); } } } ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=376685&aid=1062429&group_id=22866 ------------------------------------------------------- SF email is sponsored by - The IT Product Guide Read honest & candid reviews on hundreds of IT Products from real users. Discover which products truly live up to the hype. Start reading now. http://productguide.itmanagersjournal.com/ _______________________________________________ JBoss-Development mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/jboss-development
