从mysql读取数据结果集封装成xml文件，并解析xml文件方便插入其他数据库（oracle，sqlserver，国产数据库）

1. 从mysql读取数据结果集装成xml文件

/**      * 使用SAX同时生成遍历结果集的方法xml文件。      * @param rs      * @param filePath      * @throws Exception      */     public static void resultSet2XML(ResultSet rs, String filePath) throws Exception{          // 创建一个SAX转换工厂         SAXTransformerFactory fac = (SAXTransformerFactory)SAXTransformerFactory.newInstance();         // 创建一个TransformerHandler实例         TransformerHandler handler = fac.newTransformerHandler();         // 创建一个handler转换器         Transformer transformer = handler.getTransformer();         transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION,"no"); // 是否忽略xml声明         transformer.setOutputProperty(OutputKeys.METHOD,"xml");         transformer.setOutputProperty(OutputKeys.ENCODING,"UTF-8"); // 设置输出的编码方法         transformer.setOutputProperty(OutputKeys.INDENT,"yes"); // 是否自动添加额外的空白？ 换行         transformer.setOutputProperty(OutputKeys.STANDALONE,"no");         // 创建一个Result实例连接到XML文件         FileOutputStream fos = new FileOutputStream(filePath);         Result resultxml = new StreamResult(fos);         handler.setResult(resultxml);         ResultSetMetaData rsmd = rs.getMetaData();         String value = "";         byte[] byteValue = null;         // 打开doc对象         handler.startDocument();         // 创建属性实例         AttributesImpl atts = new AttributesImpl();         // 创建元素节点，并构建XML内容 handler.startElement(uri, 命名空间, 元素名, 属性); ///不填null         handler.startElement("","","RECORDS",atts);         if(rs.next()){             rs.previous();             while(rs.isLast() == false){                 rs.next();                 atts.clear();                 handler.startElement("","","RECORD",null);                 for(int i=1;i<=rsmd.getColumnCount();i  ){                     int columnType = rsmd.getColumnType(i);                     System.out.println("columnType:" columnType);                      String columnName = rsmd.getColumnLabel(i);//.toLowerCase();                     System.out.println("columnName:" columnName);                     handler.startElement("","",columnName,null);                      if(columnType == -4){                         byteValue = rs.getBytes(i);                         if(byteValue != null && byteValue.length>0){                             System.out.println("byteValue:" encode(byteValue));                         }                     }else{                         value = rs.getString(i);                         System.out.println("value:" value);                     }                     if(value == null && (byteValue == null || byteValue.length == 0)){                         // 设置标签内容                         handler.characters(null,0,0);                     }else{                         // 设置标签内容                         if(value != null){                             handler.characters(value.toCharArray(),0,value.length());                         }                         if(byteValue!= null ){                             handler.characters(encode(byteValue).toCharArray(),0, byteValue.length);                         }                     }                     // // 结束hanlder.endElement("", "", 元素名)                     handler.endElement("","",columnName);                     value = null;                     byteValue = null;                 }                 handler.endElement("","","RECORD");             }         }         handler.endElement("","","RECORDS");         // 关闭doc对象         handler.endDocument();         fos.close();     }      /**      * byte数组 转换为 Base64字符串      */     public static String encode(byte[] data) {         String body =  new BASE64Encoder().encode(data);         if(body.contains("\r\n")){             body = body.replace("\r\n","");         }         return body;     }

2. 解析xml文件封装成List

/**      * SAX将xml转sql语句      *  备注: SAX因为是事件驱动分析，相当于从上到下分析，所以继承DefaultHandler重写其中的分析方法      * @param filePath      * @throws Exception      */     public static void XML2Sql(String filePath) throws Exception{         File inputFile = new File(filePath);         // 创建一个SAX解析工厂         SAXParserFactory factory = SAXParserFactory.newInstance();         // 创建一个SAX转换工具         SAXParser saxParser = factory.newSAXParser();         // 解析XML         SAXParserHandler handler = new SAXParserHandler();         saxParser.parse(inputFile, handler);         List<Map<String, Object>> list = handler.getList();         for (Map<String, Object> map : list) {             System.out.println("==================开始=====================");             for (Map.Entry<String, Object> m : map.entrySet()) {                 System.out.print(m.getKey()   "    ");                 System.out.println(m.getValue());             }             System.out.println("==================结=====================");
        }
    }

SAXParserHandler.java

import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SAXParserHandler extends DefaultHandler {
    private Map<String,Object> map = new HashMap<>();
    private List<Map<String,Object>> list = new ArrayList<>();
    private String content = null;

    //文档开始
    @Override
    public void startDocument() throws SAXException {
        System.out.println("文档开始");
    }
    //xml元素的开始
    @Override
    public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
       System.out.println("元素名称="+qName);
    }
    //xml元素内容
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        content = new String(ch, start, length);
        System.out.println("start="+start);
        System.out.println("元素内容="+content);
    }
    @Override
    public void endElement(String uri, String localName, String qName) throws SAXException {
        if(content.equals("\n")) content="";
        map.put(qName,content);
        if (qName.equals("RECORD")) {
            list.add(map);
            map = new HashMap<>();
        }
    }
    //文档结束
    @Override
    public void endDocument() throws SAXException {
        System.out.println("文档结束");
    }

    List<Map<String, Object>> getList() {
        return list;
    }

}

3. 测试方法

public static void get() throws Exception {
        String driver = "com.mysql.cj.jdbc.Driver";
        String url = "jdbc:mysql://192.168.0.199:3306/zkxausi?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true&serverTimezone=UTC&useSSL=false" ;
        String user = "root";
        String password = "Zkxa~123";
        Connection conn = null;
        Statement stmt = null;
        Class.forName(driver);
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);

        ResultSet result = stmt.executeQuery("SELECT * FROM t_security_user");
      String filePath = "E://user.xml";
        XmlUtils.resultSet2XML(result,filePath);
        System.out.println("===========================华丽的分割线===============================");
        XmlUtils.XML2Sql(filePath);

    }

4. 测试结果

<?xml version="1.0" encoding="UTF-8" standalone="no"?> <RECORDS> <RECORD> <id>20</id> <username>rthr</username> <password>$2a$10$SH9nQnfjK7bEPG.fnoOdt/VyG</password> <type>1</type> <level>1</level> <online>2</online> <login_ip>152.168.0.111</login_ip> <real_name>管理员</real_name> <hard_certi/> <cert_serial_no/> <telphone>123456</telphone> <mail>123@123.123</mail> <ip_start>1.0.0.0</ip_start> <ip_end>254.254.254.254</ip_end> <login_start>00:00</login_start> <login_end>23:59</login_end> <enable>1</enable> <role_id>0</role_id> <res_keys/> <head_portrait>1</head_portrait> <remark>刚刚</remark> <mark>3</mark> <creator/> <create_time>2021-03-30 14:36:32</create_time> <updator/> <update_time>2021-12-29 09:27:32</update_time> </RECORD> <RECORD> <id>59</id> <username>洱海天域</username> <password>$2a$10$KIcMWVYgR5yOdynajUcr3.Zpm</password> <type>3</type> <level>1</level> <online>2</online> <login_ip>152.168.0.146</login_ip> <real_name>人事</real_name> <hard_certi>NBMsyGCTvpGcosR783Ys79gnfvq4dy3Jg8Dm9sey15lQrp//7dxOo8wENb+nAATgCyo1eg6f2WXO2fLjO3Jg3xR2rXxdmQy1/C0H5kwGkx9/QjdUgxLdih/fsZwxB8M1aKaXXfcvjABQw8Lfza1YdS1D6QiZtxD//JM5CzOzJHUbF2Jb+tCrO7rL6q82GKLnd0VY1leGaPoxT1QQCw8LBNdu8ZQeUPV+agEDA8xz6PUmUZMZXZO8wErDQ6zJylDRT/+MQUvav8NQUU/pUrPzM7RThsCJ1Y2BhXQ0UF2wmHcVkD5Vio4dqDUkgyu9Vo8k2sE4l68twor3Uu09KoyOHvJr45SYOgxqQwRhwakgBbDKH4Vvmf6ib1t34v9ubcp1QrAT9g7pU8IjwWEd6v4LHL1c16egcgleHvP7w7LbhhrlK8hz9wL9t2PLszUzMdcSYkRCer3d3nkN7TfyexQRhnkDgRWpmIl89e0zWGfYo0Svlo1hrLOlVPHhCgZSRebXvzuHWkHOl6yPXuFzokR4OJcdcEOj2PdAX9/sixu0fEj27+nJ+qXgEZXJpF4Cs5NOI2eG5WUcRytKV2fiy3Pb/r4IVeJad/j9wNb6Z+wQcyTSgCuXJMbdQIX7wLsQMo+IpyFaGMiHoZ8ng0DGJ7XWNWKoKU7FEGnVSJ6UUEQs1/UvqhJhtGYhoTMAwbaV3j1QQlSy7kPQulRTPGk0PoRaJNeaNnbJO5sZ8E8qXevwdU2jpce2UijWvkcJ9FZqFYGtUa4jyzL1TPAONksV4hDjpwfct1jgQKE4tugKD6jLazNUGRz6Qjbl044+qvXyVI7ku2xe3SD5FrdDZIIIFIF0wjG1KGh2c+2Gnvr0NrmXHiIrTKoIZQCihN5YXAfFRmupQf6PaYB+B8BNKA3Yf5Sc3IZ8jO0c9Frpkj4vP/q4sid0A3kSWTiGZhxD2gHmAcIhoFx9lxbWy0nKX8HKtOoccGB/Ff9f7pbD2IeLMPqKj1ird2+7yL8VNbrHVxdIX5vm98iDnE4nSbKY79k2y7whZ0ouZqgBoW4rf9Mk8mWt72leicFnjm4fgSUyJ8neelrexfhrnCK5PYimLCtTG+adXK2pQfMSFS3A8smTP0quv9bDj2gFjaNa6DenlvCrW8Tbqm37zq96OR7WFSmMaw0zbBrm9rsPCohrI77WCgKvsvih1JtB3RQSvbwYx2X91AaEO8OzKq3050IQYpie1xbHl39Za+/UoNMrFpn6/WN328H9eEb1Z79X41hdKNVlFeuv540TBBBqUMz3mk/pYEzrGGwN2U67SUhjzGmIKueGq3KZ7d3XJZDAk9N1VbdVBXrkwggVBBgkqhkiG9w0BBwGgggUyBIIFLjCCBSowggUmBgsqhkiG9w0BDAoBAqCCBO4wggTqMBwGCiqGSIb3DQEMAQMwDgQIB6Z+lMYq57wCAggABIIEyEM+DSJ6gz46sG1OqVi7nFmEBPYQupLaq/SxvEwVnqH4LnMoviz9rUrt0A7lUT4GOQ97AGdvU+MlVDSlAWY09sd6zpJdQswKPfv9B/o9Vrjrzgp5vrDE+P6VbmDAeYaOa7/IeL2V85p4K8bZG9C7adrNTzDWYKucrDGtInOAXeE7l3xHOeXwY8PHqr3fB7LTkoOUzsZsGYjKyqs+a6C0KKpShfg4ZTURLqjSQZlVgakOS+bRVpztHscPT+1X+1HQyQcjLrJh2PX8CiTwvsynHTyXpO8TFo3ualjZjulYIhVEvTLEZMYyr4LMOyMgeEIJITlIUgLadAYqhBjlOIDjnU0I00tt7WcKvfdje+cqr2nYUEX8qaKauXq/w+VQaR70dCufuyT32QeWQJJx8UmixzvofnXy4/hTdRTxc1aWTDQEvbIx6+nY+sVgBBZmYo2sMaRALQkWUIcRhd7N6ZjNdiOw8Mbre/gWpLfz/PeENKkMd66yv2bpEACZQSF65hmYK2xwmfjSvvP+6ZG2xtaoRZ7VatYulez4h8qd4rgvOSMCofAIyPL9DKd3ek9gp4ffk0KLXZzCch3R7AOq4kcuVZd8+Eu96LTizVSXielX/OSggFHG/n1nQh449MHYg6dk9ZhTi1bgvsWudrc2s0fkHjLIHRQXLNRPpMf0k9+ic+buCmWrjTvw3pvm/A9pJB+nuCvuuxoz3eLNInAnvVb+h2PEL1viibIhCtxyBJ/NgCNQCD58q1vXjCxTeTk9AQv6ruqFWXGRD+wAiqPQcG4YCeOVm6mKvWuBX1za03213OPDNiuw4ce6QWcfziqEiPYhziiX/j0m20JRXcTK8bKlNd+dMqkTdtURUcUKJEOQyNkJEUoXljHkTE/v74/U5U9y5Yn/3ZtShNcbn</hard_certi> <cert_serial_no>82728A277D598757</cert_serial_no> <telphone/> <mail/> <ip_start>152.168.0.146</ip_start> <ip_end>152.168.0.146</ip_end> <login_start>00:00</login_start> <login_end>23:59</login_end> <enable>1</enable> <role_id>32</role_id> <res_keys>团日活动被淘汰你也见一面人的</res_keys> <head_portrait/> <remark/> <mark/> <creator/> <create_time>2021-12-27 16:15:34</create_time> <updator/> <update_time>2021-12-29 09:27:32</update_time> </RECORD> </RECORDS>