Batch test whether SQL is correct in mybatis project


Go to Oracle

Recently, the company wants to develop overseas projects, so it needs to translate all the existing systems into the past. In addition, the database should also be from the originalOracleTurn intoMysql。 The company’s database interaction level usesMybatisAndOracleAndMysqlThere are also grammatical differences. Therefore, the SQL in the project needs to be changed, but there are many SQLs involved in multiple projects. If you only distinguish one by one, the workload is a little large. Therefore, it initiates to directly change the data source into mysql, use reflection to execute the method in mapper in batches, and set it as the default initial value if there are parameters, then record the successful data and the failed data, so that you can modify them according to the failure reason. Can save a lot of time.

Execution effect

Code introduction

There are three steps in the overall thinking

  1. Get all methods of mapper class to execute through reflection
  2. Get the parameters in the method and assign a value
  3. implement
Autotestmapper autotestmapper = new autotestmapper ("store mapper full path name");

After passing in the full path name in the constructor, parse it, resolve the package name and all file names and store them

public AutoTestMapper(String path) throws IOException, ClassNotFoundException {
        String mapperContent = getFileContent(path);
        String pathPattern = "import [a-z,A-Z,/.]+;";
        String[] pathArr = matchMethod(pathPattern, mapperContent).split(";");
        for (int i = 0; i < pathArr.length; i++) {
            pathArr[i] = pathArr[i].replaceAll("import ", "");
            Class cls = Class.forName(pathArr[i]);
            if (!cls.isInterface()) {
        //Get prefix of full path name
        String packPattern = "package [a-z,A-Z,/.]+;";
        String[] packPathArr = matchMethod(packPattern, mapperContent).split(";");
        String packPath = packPathArr[0].replaceAll("package ", "").replaceAll(";", "");
        this.PACK_PATH = packPath;

Then callopenSqlSessionMethod, passing inSqlSessionFactory parameter

List<Map<Class, Object>> list = new ArrayList<>();
        List<String> invokeSuccess = new ArrayList<>();
        List<String> invokeFail = new ArrayList<>();
        for (String fileName : FILE_NAME) {
            Class cls = Class.forName(PACK_PATH + "." + fileName);
            //Add mapper
            if (!sqlSessionFactory.getConfiguration().hasMapper(cls)){
            //Get mapper
            Object mapper = sqlSessionFactory.openSession().getMapper(cls);
            //Reflection of mapper execution method
            Map<String, List<String>> resultMap = autoTestInvoke(cls, mapper);

And then through the method provided by mybatyisgetMapper()Pass in the class name to get the mapper class you want. The core approach isautoTestInvoke()Method

private Map<String, List<String>> autoTestInvoke(Class c, Object o)
        Method[] declaredMethods = c.getDeclaredMethods();
        String fileName = c.getName().substring(c.getName().lastIndexOf("."));
        List<String> invokeSuccess = new ArrayList<>();
        List<String> invokeFail = new ArrayList<>();
        Map<String, List<String>> resultMap = new HashMap<>();
        //Assign initial value to parameter
        for (Method method : declaredMethods) {
            List<Object> list = new ArrayList<>();
            for (Class cls : method.getParameterTypes()) {
                Object par = new Object();
                if (TYPE_ARRAY.contains(cls)) {
                    if (cls.equals(String.class)) {
                        par = "1";
                    } else {
                        try {
                            par = cls.newInstance();
                            assignment(cls, par);
                        } catch (InstantiationException e) {
                            if (cls.isPrimitive()) {
                                cls = primitiveClazz.get(cls.getName());
                            try {
                                par = cls.getDeclaredConstructor(String.class).newInstance("1");

                            }catch (NoSuchMethodException e1){
                }else if ("java.util.Map".equals(cls.getName())){
                    par = getMapData(c.getName()+"."+method.getName());
            try {
                method.invoke(o, list.toArray());
                invokeSuccess.add("Success: " + fileName + "." + method.getName());
            } catch (Exception e) {
                invokeFail.add("Error:" + method.getName() + "   Error Info:" + e);
        resultMap.put(SUCCESS_FLG, invokeSuccess);
        resultMap.put(FAIL_FLG, invokeFail);
        return resultMap;

This completes the logic of assigning initial values and executing parameters.


Automatic test mapper can test all parameters except list and set. All if conditions in XML are spliced to.

  • takeAutoTestMapperCopy to the test module. As shown in the figure

AutoTestMapper Files are stored in GitHub

  • stayresourcesAdd in modulemybatis-config.xmlFile, as shown

mybatis-config.xmlThe contents are as follows

<!DOCTYPE configuration
        PUBLIC "-// Config 3.0//EN"
    <environments default="dev">
        <environment id="dev">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                < property name = "URL" value = "connection address" / >
                < property name = "username" value = "account" / >
                < property name = "password" value = "password" / >
  • Create the Lib folder in the root directory, put the tested version of mybatis in it, and introduce the package in gradle

Compile files (‘.. / lib / mybatis-3.5.0-hupengfeitest. Jar’) fill in the relative path here

If the directory structure is as follows, thencompile files('lib/mybatis-3.5.0-hupengfeiTest.jar')

mybatis-3.5.0-hupengfeiTest.jarIn the Lib directory under GitHub

    -- mybatis-3.5.0-hupengfeiTest.jar

If the directory structure is as follows, thencompile files('../lib/mybatis-3.5.0-hupengfeiTest.jar')

    -- mybatis-3.5.0-hupengfeiTest.jar
    -- build.gradle

  • Write code and test in unit test
@Springboottest (classes = {airapplication. Class}) // here, airapplication.class is the startup class in the project, which can be modified by yourself
public class TestMapper {

    public void testCeshi()
            throws IllegalAccessException, IntrospectionException, InvocationTargetException, NoSuchMethodException,
            InstantiationException, IOException, ClassNotFoundException {
        //Read mybatis configuration
        Reader resourceAsReader = Resources.getResourceAsReader("mybatis-config.xml");
        //Generate sqlsessionfactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsReader);
        Autotestmapper autotestmapper = new autotestmapper (full path name of the Java folder where mapper is stored);
        //Perform test methods

The mapper that failed to execute and its reason will be printed out in the console. As shown in the figure below

GitHub address: