【小笔记】Mybatis多数据源环境下的sql-id校验

Bertha 。 2023-03-05 15:56 2阅读 0赞

## 场景 ##

系统同时支持mysql、oracle、mycat、gaussdb等多套数据库(环境)，由于个个数据库语法的差异，为了更好的隔离开代码，采用分文件的形式来写sql，然而在开发过程中难免忘记少写一个数据库方言的sql，只有切换语言环境时才会发现问题。所以需要提前校验下各个环境下的sql语句是否有遗漏。

## 实现 ##

![在这里插入图片描述][20200804194449625.png]

import org.apache.commons.io.FileUtils;
    import org.apache.commons.io.filefilter.IOFileFilter;
    import org.dom4j.Document;
    import org.dom4j.DocumentException;
    import org.dom4j.Element;
    import org.dom4j.io.SAXReader;
    import org.xml.sax.InputSource;
    
    import java.io.ByteArrayInputStream;
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.nio.file.FileSystems;
    import java.nio.file.PathMatcher;
    import java.nio.file.Paths;
    import java.util.*;
    import java.util.concurrent.atomic.AtomicBoolean;
    
    public class Main { 
        public static void main(String[] args) { 
    
            Scanner sc = new Scanner(System.in);
    
            System.out.println("扫描的根路径(src路径):");
            String rootPath = sc.next();
            System.out.println("文件匹配规则1(例如**/model/mycaat/*.xml):");
            String path1 = sc.next();
            System.out.println("文件匹配规则2(例如**/model/gaussdb/*.xml):");
            String path2 = sc.next();
            System.out.println();
            System.out.println("对比中...");
            System.out.println();
            System.out.println();
            System.out.println("对比结果：");
            System.out.println();
    
    
            Collection<File> matchFileForMycat = getMatchFile(rootPath, path1);
            Collection<File> matchFileForGauss = getMatchFile(rootPath, path2);
    
            Map<String, List<String>> mycatIdMappings = new HashMap<>();
            Map<String, List<String>> guassdbIdMappings = new HashMap<>();
            SAXReader reader = new SAXReader(false);
            reader.setEntityResolver((publicId, systemId) -> new InputSource(new ByteArrayInputStream("<?xml version='1.0' encoding='UTF-8'?>".getBytes())));
            matchFileForMycat.forEach(file -> { 
                String fileName = file.getName();
                Document document = null;
                mycatIdMappings.put(fileName, new ArrayList<>());
                try { 
                    FileInputStream fileInputStream = new FileInputStream(file);
                    document = reader.read(fileInputStream, "");
                    Element rootElement = document.getRootElement();
                    List<Element> elements = rootElement.elements();
                    elements.forEach(element -> { 
                        String id = element.attribute("id").getValue();
                        mycatIdMappings.get(fileName).add(id);
                    });
                } catch (DocumentException | FileNotFoundException e) { 
                    e.printStackTrace();
                }
            });
            matchFileForGauss.forEach(file -> { 
                String fileName = file.getName();
                Document document = null;
                guassdbIdMappings.put(fileName, new ArrayList<>());
                try { 
                    document = reader.read(file);
                    Element rootElement = document.getRootElement();
                    List<Element> elements = rootElement.elements();
                    elements.forEach(element -> { 
                        String id = element.attribute("id").getValue();
                        guassdbIdMappings.get(fileName).add(id);
                    });
                } catch (DocumentException e) { 
                    e.printStackTrace();
                }
            });
            AtomicBoolean isDiff = new AtomicBoolean(false);
            guassdbIdMappings.forEach((key, val) -> { 
                if (!mycatIdMappings.containsKey(key)) { 
                    System.out.println("规则1【" + path1 + "】缺失文件" + key);
                    isDiff.set(true);
                } else { 
                    val.forEach(v -> { 
                        if (!mycatIdMappings.get(key).contains(v)) { 
                            System.out.println("规则1【" + path1 + "】sql缺失, sql id:" + v);
                            isDiff.set(true);
                        }
                    });
                }
            });
    
            mycatIdMappings.forEach((key, val) -> { 
                if (!guassdbIdMappings.containsKey(key)) { 
                    System.out.println("规则【" + path2 + "】" + key);
                    isDiff.set(true);
                } else { 
                    val.forEach(v -> { 
                        if (!guassdbIdMappings.get(key).contains(v)) { 
                            System.out.println("规则2【" + path2 + "】sql缺失, sql id:" + v);
                            isDiff.set(true);
                        }
    
                    });
                }
            });
    
            if (!isDiff.get()) { 
                System.out.println("完全一致!");
            }
        }
    
        private static Collection<File> getMatchFile(String rootPath, String matcher) { 
            PathMatcher pathMatcher = FileSystems.getDefault().getPathMatcher("glob:" + matcher);
            Collection<File> files = FileUtils.listFiles(new File(rootPath), new IOFileFilter() { 
                @Override
                public boolean accept(File file) { 
                    return pathMatcher.matches(Paths.get(file.getPath()));
                }
    
                @Override
                public boolean accept(File file, String s) { 
                    return true;
                }
            }, new IOFileFilter() { 
                @Override
                public boolean accept(File file) { 
                    return true;
                }
    
                @Override
                public boolean accept(File file, String s) { 
                    return true;
                }
            });
            return files;
        }
    }

[20200804194449625.png]: /images/20230208/ff771e03dd1a4c86bf2748b1519a3264.png