pom包引入
<dependency> <groupId>org.dom4j</groupId> <artifactId>dom4j</artifactId> <version>2.1.3</version> </dependency>
package com.FSXML.service; import com.FSXML.util.JDBCUtil; import org.apache.commons.lang3.StringUtils; import org.dom4j.Document; import org.dom4j.Element; import org.dom4j.Node; import org.dom4j.io.SAXReader; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.beans.factory.annotation.Value; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Service; import javax.annotation.PostConstruct; import java.io.File; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.text.DateFormat; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; /** * 解析辐射XML */ @Service public class AnalysisXMLService { @Autowired private JDBCUtil jdbcUtil; public boolean Dom4JDemo(File file) throws Exception { try { //1.创建Reader对象 SAXReader reader = new SAXReader(); //2.加载xml Document document = reader.read(file); //3.获取根节点 Element rootElement = document.getRootElement(); //操作 String action = rootElement.element("head").element("action").getTextTrim(); //模式 int index = file.getName().indexOf("_"); String fileName = file.getName().substring(0, index); Element data = rootElement.element("data"); upData(file, data, action, fileName); }catch (Exception e){ e.printStackTrace(); return false; } return true; } /** * 解析XML获取字段和值 * @param element 节点 * @param action 操作 */ private void upData(File file,Element element,String action,String fileName){ //获取根节点下面的所有子节点(不包过子节点的子节点) List<Element> tableNameS = element.elements() ; //遍历List的方法 for (Element table:tableNameS){ System.out.println(table.getName()); String tableName = table.getName(); String tableKey = ""; String keyValue= ""; List<String> columnNames = new ArrayList<>(); if(!tableName.substring(tableName.length()-4).equals("List")){ tableKey = gettableKey(tableName); if(tableKey != "" && table.elements().size()>0) { String xpath = "/content/data/"+tableName+"/"+tableKey; if(isExist(file,xpath)) keyValue = table.element(tableKey).getTextTrim(); } columnNames = getColumnNames(tableName); } String insertColumns = ""; String insertValues = ""; String updateColums=""; String createColumns=""; List<Element> contactList = table.elements(); for (Element e : contactList) { if (e.elements().size()>0) { System.out.println(e.getName()); String childColumns = ""; String childValues = ""; String childUpdateValues=""; String childCreateColumns=""; String childTableName = e.getName(); tableKey = gettableKey(childTableName); String xpath = "/content/data/"+tableName+"/"+childTableName+"/"+tableKey; if(childTableName.equals("TT_LR_MONITOR_INSTRUMENT")){ if(!isExist(file,xpath)){ keyValue = e.element("INSTRUMENT_ID").getTextTrim(); } } //tableKey 为空,数据库不存在表 if(tableKey != "" && isExist(file,xpath)) { keyValue = e.element(tableKey).getTextTrim(); } columnNames = getColumnNames(childTableName); //存在子节点 List<Element> childs = e.elements() ; for (Element child:childs){ //主键不存在,表示数据库不存在表 if(tableKey ==""){ continue; //childCreateColumns += child.getName() + " varchar(255) NOT NULL ,"; } //判断节点是否存在 if(!columnNames.contains(child.getName())){ //不存在新增字段 int result = addColum(childTableName,child.getName()); if(result>0){ childColumns += child.getName() + ","; childValues = childValues +"'"+ child.getStringValue() + "',"; childUpdateValues += child.getName() + "= '"+child.getStringValue()+"',"; } }else { childColumns += child.getName() + ","; childValues = childValues + "'" + child.getStringValue() + "',"; childUpdateValues += child.getName() + "= '" + child.getStringValue() + "',"; } } //去除最后一个逗号 if(StringUtils.isNotEmpty(childColumns) && StringUtils.isNotEmpty(childValues) && StringUtils.isNotEmpty(childUpdateValues)) { childColumns = childColumns.substring(0, childColumns.length() - 1); childValues = childValues.substring(0, childValues.length() - 1); childUpdateValues = childUpdateValues.substring(0, childUpdateValues.length() - 1); executeSql(action, childTableName, childColumns, childValues, childUpdateValues, keyValue,childCreateColumns); } }else { //主键不存在,表示数据库不存在表 if(tableKey==""){ continue; //createColumns += e.getName() + " varchar(255) NOT NULL ,"; } //判断节点是否存在 if(!columnNames.contains(e.getName())){ //不存在新增字段 int result = addColum(tableName,e.getName()); if(result>0){ insertColumns +=e.getName()+","; insertValues = insertValues +"'"+ e.getStringValue()+"',"; updateColums += e.getName() + "= '"+e.getStringValue()+"',"; } }else { insertColumns +=e.getName()+","; insertValues = insertValues +"'"+ e.getStringValue()+"',"; updateColums += e.getName() + "= '"+e.getStringValue()+"',"; } } } //去除最后一个逗号 if(StringUtils.isNotEmpty(insertColumns) && StringUtils.isNotEmpty(insertValues) && StringUtils.isNotEmpty(updateColums)) { insertColumns = insertColumns.substring(0, insertColumns.length() - 1); insertValues = insertValues.substring(0, insertValues.length() - 1); updateColums = updateColums.substring(0, updateColums.length() - 1); executeSql(action, tableName, insertColumns, insertValues, updateColums, keyValue, createColumns); } } } /** * 拼接SQL执行 * @param action 操作 * @param tableName 表名 * @param columns 新增列 * @param values 新增值 * @param updateColumns 修改列 * @param keyValue 主键值 * @return */ public void executeSql(String action,String tableName,String columns,String values,String updateColumns,String keyValue,String createColumns){ if(keyValue != "") { String sql = "SELECT * FROM XLS." + tableName + " where " + gettableKey(tableName) + " = '" + keyValue + "'"; List<Map> maps = jdbcUtil.queryList(sql); if (maps != null) { if (maps.size() > 0) action = "update"; else action = "insert"; if (action.equals("insert") && StringUtils.isNotEmpty(columns) && StringUtils.isNotEmpty(values)) { sql = "INSERT INTO XLS." + tableName + " ( " + columns + " ) " + "VALUES ( " + values + " )"; } else if (action.equals("update") && StringUtils.isNotEmpty(columns) && StringUtils.isNotEmpty(keyValue)) { sql = "UPDATE XLS." + tableName + " set " + updateColumns + " where " + gettableKey(tableName) + " = '" + keyValue + "'"; } else if (action.equals("delete") && StringUtils.isNotEmpty(tableName) && StringUtils.isNotEmpty(keyValue)) { sql = "DELETE FROM XLS." + tableName + " where " + gettableKey(tableName) + " = '" + keyValue + "'"; } if (sql != "") { int result = jdbcUtil.update(sql); if (action.equals("update") && result == 0 && StringUtils.isNotEmpty(columns) && StringUtils.isNotEmpty(values)) { sql = "INSERT INTO XLS." + tableName + " ( " + columns + " ) " + "VALUES ( " + values + " )"; result = jdbcUtil.update(sql); } else { if (action.equals("insert")) System.out.println("新增 XLS." + tableName + "成功。"); else if (action.equals("update")) System.out.println("修改 XLS." + tableName + "成功,主键:" + gettableKey(tableName) + " = '" + keyValue + "'。"); else if (action.equals("delete")) System.out.println("删除 XLS." + tableName + "成功,主键:" + gettableKey(tableName) + " = '" + keyValue + "'。"); } } } }else if(createColumns!= ""){ //新建表 String sql = "CREATE TABLE XLS." + tableName +" (" + createColumns.substring(0, createColumns.length() - 1) + " )"; jdbcUtil.update(sql); System.out.println("创建表 XLS." + tableName + "成功。"); } } public int addColum(String tableName,String columnName){ String sql = "ALTER TABLE XLS."+tableName+" ADD "+ columnName +" varchar(255) NULL"; int result = jdbcUtil.update(sql); return result; } /** * 通过表名获取主键Key */ public String gettableKey(String tableName){ String key=""; switch (tableName){ case "TT_LR_COMPANY": key="COMPANY_ID"; break; case "TT_LR_SITE": key="SITE_ID"; break; } return key; } /** * 文件是否存在某个子节点 * @param file * @param xpath * @return */ public boolean isExist(File file,String xpath){ SAXReader saxReader=new SAXReader(); try { Document document=saxReader.read(file); List<Node> list = document.selectNodes(xpath); if(list.size()>0){ return true; }else { return false; } } catch (Exception e) { e.printStackTrace(); return false; } } /** * 获取表中所有字段名称 * @param tableName 表名 * @return */ public List<String> getColumnNames(String tableName) { List<String> columnNames = new ArrayList<>(); //与数据库的连接 Connection conn = JDBCUtil.getConnetions(); PreparedStatement pStemt = null; String tableSql = "SELECT * FROM XLS." + tableName; try { pStemt = conn.prepareStatement(tableSql); //结果集元数据 ResultSetMetaData rsmd = pStemt.getMetaData(); //表列数 int size = rsmd.getColumnCount(); for (int i = 0; i < size; i++) { columnNames.add(rsmd.getColumnName(i + 1)); } } catch (SQLException e) { e.printStackTrace(); } return columnNames; } }