如何解决DbUnit的Maven插件在执行过程中出现的侵犯外键约束错误(MySQLIntegrityConstraintViolationException)

如果是在使用UbUnit进行单元测试时遇到

 

其实这个问题不止在通过DbUnit的Maven插件操作数据库时会遇到，在基于DbUni进行单元测试时也会遇到，一般的处理方法是在每次获取连接时前执行一个"set  @@session.foreign_key_checks = 0"的statement，以保证本次会话不作外键约束检查，以下是我常用的一个基于DbUnit的单元测试基类，其中第59行就是关于禁止外键约束检查的设置。（注：本例使用的数据库是MySql）

package oobbs.domainmodel;

import java.sql.SQLException;

import javax.sql.DataSource;

import org.dbunit.DataSourceDatabaseTester;
import org.dbunit.DefaultOperationListener;
import org.dbunit.database.DatabaseConfig;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.xml.XmlDataSet;
import org.dbunit.ext.mysql.MySqlDataTypeFactory;
import org.junit.After;
import org.junit.Before;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import testutil.ApplicationContextSupport;

/**
 * This base class does not extends any DBTestCase of dbunit,or use any Tester,Their's implement is not good and flexible.
 * Here,we prepare and set connection manully!
 * 
 * @author Laurence Geng
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = {"classpath:/applicationContext-infrastructure.xml",
                                   "classpath:/applicationContext-domainModel.xml",
                                   "classpath:/applicationContext-test.xml"})
public abstract class DbBasedTest{
    
    /** The data source. */
    @Autowired
    protected DataSource dataSource;

    /** The dbunitTestUtil can fill test data from xml into test db before testing. */
    protected DataSourceDatabaseTester dbunitTestUtil;
    
    /**
     * Inits dbunitTestUtil. 
     * The connectionRetrieved method is called back when setUp() executes.
     * At this time,we should set connection-specific setting: set foreign key check disabled
     * so as dbunit can invert test data, and set data type factory be MySqlDataTypeFactory so as
     * dbunit can convert correct type when invert data to mysql.
     *
     * @throws Exception the exception
     */
    protected void initDbunitTestUtil() throws Exception{
        dbunitTestUtil = new DataSourceDatabaseTester(dataSource);
        dbunitTestUtil.setDataSet(new XmlDataSet(new ClassPathResource("dbunit-test-data.xml").getInputStream()));
        dbunitTestUtil.setOperationListener( new DefaultOperationListener(){
            public void connectionRetrieved(IDatabaseConnection connection) {
                try {
                    //Disable foreign key check!
                    connection.getConnection().prepareStatement("set @@session.foreign_key_checks = 0").execute();
                    // When a new connection has been created then invoke the setUp method
                    // so that user defined DatabaseConfig parameters can be set.
                    connection.getConfig().setProperty(DatabaseConfig.PROPERTY_DATATYPE_FACTORY, new MySqlDataTypeFactory());
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }});
    }
    
    /**
     * Before test method.
     * 
     * @throws Exception the exception
     */
    @Before
    public void beforeTestMethod() throws Exception { 
        initDbunitTestUtil();
        dbunitTestUtil.onSetup();
    }
    
    /**
     * After test method.
     * 
     * @throws Exception the exception
     */
    @After
    public void afterTestMethod() throws Exception {
        dbunitTestUtil.onTearDown();
    }
    
}

如果是在使用DbUnit的Maven插件时遇到

而如果是使用Maven的DbUnit插件，以命令行的方式执行数据导入工作的话，就只能从数据库连接的url上下手了，方法也很简单就是在原插件的配置上添加foreign_key_checks = 0这个变量，以下是一个例子，请注意第14行<url>标记的部分：<url>${jdbc.url}&amp;sessionVariables=foreign_key_checks=0</url>，它在标准url后面追加了对变量foreign_key_checks的设置。

            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>dbunit-maven-plugin</artifactId>
                <version>1.0-beta-3</version>
                <dependencies>
                    <dependency>
                        <groupId>${jdbc.groupId}</groupId>
                        <artifactId>${jdbc.artifactId}</artifactId>
                        <version>${jdbc.version}</version>
                    </dependency>
                </dependencies>
                <configuration>
                    <driver>${jdbc.driverClassName}</driver>
                    <url>${jdbc.url}&sessionVariables=foreign_key_checks=0</url>
                    <username>${jdbc.username}</username>
                    <password>${jdbc.password}</password>
                </configuration>
                <executions>
                    <execution>
                        <id>default-cli</id>
                        <goals>
                            <goal>operation</goal>
                        </goals>
                        <configuration>
                            <type>CLEAN_INSERT</type>
                            <src>src/test/resources/dbunit-test-data.xml</src>
                            <dataTypeFactoryName>org.dbunit.ext.mysql.MySqlDataTypeFactory</dataTypeFactoryName>
                            <transaction>true</transaction>
                        </configuration>
                    </execution>
                </executions>
            </plugin>

备注：

mysql中，变量的作用域有两种session和global,改变变量值的方法为：

要想设置一个GLOBAL变量的值，使用下面的语法：

mysql> SET GLOBAL sort_buffer_size=value;
mysql> SET @@global.sort_buffer_size=value;

要想设置一个SESSION变量的值，使用下面的语法：

mysql> SET SESSION sort_buffer_size=value;
mysql> SET @@session.sort_buffer_size=value;
mysql> SET sort_buffer_size=value;