private void deleteJoinedRows( Mapping particle, TableJoin tableJoin, Map<SQLIdentifier, Object> joinKeyColToValue) throws FeatureStoreException { TableName joinTable = tableJoin.getToTable(); if (particle instanceof CompoundMapping) { CompoundMapping cm = (CompoundMapping) particle; for (Mapping child : cm.getParticles()) { deleteJoinedRows(child, joinKeyColToValue); } } // DELETE join rows PreparedStatement stmt = null; try { StringBuilder sql = new StringBuilder("DELETE FROM " + joinTable + " WHERE"); boolean first = true; for (Entry<SQLIdentifier, Object> joinKey : joinKeyColToValue.entrySet()) { if (!first) { sql.append(" AND"); } sql.append(' '); sql.append(joinKey.getKey()); sql.append("=?"); first = false; } stmt = conn.prepareStatement(sql.toString()); int i = 1; for (Entry<SQLIdentifier, Object> joinKey : joinKeyColToValue.entrySet()) { stmt.setObject(i++, joinKey.getValue()); } LOG.debug("Executing DELETE (joined rows): " + stmt); stmt.executeUpdate(); } catch (SQLException e) { LOG.error(e.getMessage(), e); throw new FeatureStoreException(e.getMessage(), e); } finally { JDBCUtils.close(stmt); } }
private void deleteJoinedRows(Mapping particle, Map<SQLIdentifier, Object> keyColToValue) throws FeatureStoreException { // TODO: After FeatureTypeJoin is introduced, rework this case (may allow joins) if (particle instanceof FeatureMapping) { return; } // determine and delete joined rows if (particle.getJoinedTable() != null && !particle.getJoinedTable().isEmpty()) { TableJoin tableJoin = particle.getJoinedTable().get(0); PreparedStatement stmt = null; try { StringBuilder sql = new StringBuilder("SELECT "); boolean first = true; for (SQLIdentifier selectColumn : tableJoin.getToColumns()) { if (!first) { sql.append(','); } else { first = false; } sql.append("X2."); sql.append(selectColumn); } sql.append(" FROM "); sql.append(tableJoin.getFromTable()); sql.append(" X1,"); sql.append(tableJoin.getToTable()); sql.append(" X2"); sql.append(" WHERE"); first = true; int i = 0; for (SQLIdentifier fromColumn : tableJoin.getFromColumns()) { SQLIdentifier toColumn = tableJoin.getToColumns().get(i++); if (!first) { sql.append(','); } else { first = false; } sql.append(" X1."); sql.append(fromColumn); sql.append("="); sql.append("X2."); sql.append(toColumn); first = false; } for (Entry<SQLIdentifier, Object> joinKey : keyColToValue.entrySet()) { sql.append(" AND X1."); sql.append(joinKey.getKey()); sql.append("=?"); first = false; } stmt = conn.prepareStatement(sql.toString()); i = 1; for (Entry<SQLIdentifier, Object> joinKey : keyColToValue.entrySet()) { stmt.setObject(i++, joinKey.getValue()); } LOG.debug("Executing SELECT (following join): " + stmt); ResultSet rs = stmt.executeQuery(); while (rs.next()) { Map<SQLIdentifier, Object> joinKeyToValue = new HashMap<SQLIdentifier, Object>(); i = 1; for (SQLIdentifier toColumn : tableJoin.getToColumns()) { joinKeyToValue.put(toColumn, rs.getObject(i++)); } deleteJoinedRows(particle, tableJoin, joinKeyToValue); } } catch (SQLException e) { LOG.error(e.getMessage(), e); throw new FeatureStoreException(e.getMessage(), e); } finally { JDBCUtils.close(stmt); } } else { // process compound particle structure if (particle instanceof CompoundMapping) { CompoundMapping cm = (CompoundMapping) particle; for (Mapping child : cm.getParticles()) { deleteJoinedRows(child, keyColToValue); } } } }