Technical blog

package com.swayam.db.tools;
 
/**
 *@author paawak
 */
public interface ConstraintViolationExceptionInterpretor {
 
    void setExceptionMessage(String message);
 
    String getConstrainedTableName();
 
    String getForeignKeyColumnName();
 
    String getReferencedTableName();
 
    String getReferencedColumnName();
 
}

package com.swayam.db.tools;
 
import java.util.regex.Matcher;
import java.util.regex.Pattern;
 
/**
 *
 * @author paawak
 */
public class MySQLConstraintViolationExceptionInterpretor implements
        ConstraintViolationExceptionInterpretor {
 
    private String message;
 
    public MySQLConstraintViolationExceptionInterpretor() {
 
    }
 
    public String getConstrainedTableName() {
 
        String match = getFirstMatch("/\w+");
 
        if (match != null) {
            match = match.substring(1);
        }
 
        return match;
 
    }
 
    public String getForeignKeyColumnName() {
 
        String match = getFirstMatch("\(`\w+`\)");
 
        if (match != null) {
            match = match.substring(2, match.length() - 2);
        }
 
        return match;
 
    }
 
    public String getReferencedColumnName() {
 
        String match = getFirstMatch("\(`\w+`\)\)");
 
        if (match != null) {
            match = match.substring(2, match.length() - 3);
        }
 
        return match;
 
    }
 
    public String getReferencedTableName() {
 
        String prefix = "REFERENCES `";
 
        String match = getFirstMatch(prefix + "\w+`");
 
        if (match != null) {
            match = match.substring(prefix.length(), match.length() - 1);
        }
 
        return match;
 
    }
 
    public void setExceptionMessage(String message) {
        this.message = message;
    }
 
    private String getFirstMatch(String regex) {
 
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(message);
 
        if (matcher.find()) {
            String match = matcher.group();
            return match;
        }
 
        return null;
 
    }
 
}

package com.swayam.db.tools;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
 
/**
 *@author paawak
 */
public class RecursiveDeleteUtil {
 
    private final Connection con;
    private final List deleteStatements;
    private final ConstraintViolationExceptionInterpretor interpretor;
    private String idColumn;
    private String idValue;
 
    public RecursiveDeleteUtil(Connection con,
            ConstraintViolationExceptionInterpretor interpretor) {
        this.con = con;
        this.interpretor = interpretor;
        deleteStatements = new ArrayList(1);
    }
 
    public void delete(String tableName, String column, String value) {
 
        if (idColumn == null) {
            idColumn = column;
            idValue = value;
        }
 
        String delQuery = "DELETE FROM " + tableName + " WHERE " + column
                + "='" + value + "'";
 
        deleteStatements.add(delQuery);
 
        try {
 
            Statement stat = con.createStatement();
            int rowsAffected = stat.executeUpdate(delQuery);
 
            if (rowsAffected > 0) {
 
                // execute all stats on the stack
                int lastIndex = deleteStatements.size() - 1;
 
                for (int count = lastIndex; count >= 0; count--) {
                    stat.executeUpdate(deleteStatements.get(count));
                }
 
            }
 
            stat.close();
 
        } catch (SQLException e) {
 
            String message = e.getMessage();
            interpretor.setExceptionMessage(message);
            String constrainedTableName = interpretor.getConstrainedTableName();
            String foreignKeyColumnName = interpretor.getForeignKeyColumnName();
            String referencedTableName = interpretor.getReferencedTableName();
            String referencedColumnName = interpretor.getReferencedColumnName();
            System.out.println("################# message = " + message
                    + "nconstrainedTableName = " + constrainedTableName
                    + ", foreignKeyColumnName = " + foreignKeyColumnName
                    + ", referencedTableName = " + referencedTableName
                    + ", referencedColumnName = " + referencedColumnName);
 
            // select the offending value
            String sel = "SELECT " + referencedColumnName + " FROM "
                    + referencedTableName + " WHERE " + idColumn + "='"
                    + idValue + "'";
 
            System.out.println("################ select = " + sel);
 
            try {
                Statement selectStat = con.createStatement();
                ResultSet res = selectStat.executeQuery(sel);
 
                while (res.next()) {
                    String constraintVal = res.getString(1);
                    delete(constrainedTableName, foreignKeyColumnName,
                            constraintVal);
                }
 
                res.close();
                selectStat.close();
 
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
 
        }
 
    }
 
    public static void main(String[] args) throws Exception {
 
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String conStr = "jdbc:mysql://localhost/dummy?user=root&password=dummy";
 
        Connection con = DriverManager.getConnection(conStr);
 
        new RecursiveDeleteUtil(con,
                new MySQLConstraintViolationExceptionInterpretor()).delete(
                "some_table", "id", "118");
 
    }
 
}