XML备份mysql数据库

向右看齐 2021-12-13 14:04 363阅读 0赞

**DemoJ4 XML备份数据库**

说明：这篇博客是为测试XML备份数据库，你要想学习生成XML文件基础知识请参照：[Dom4J生成xml文件][Dom4J_xml]

首先提供这几个必要类以供测试(如有需要请自行修改部分代码)：  
**1.JDBCUtile连接数据库**

package po;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    
    
    public class JDBCUtil {
    
    	/**
    	 * @param args
    	 */
    	
    	private static final String mysqlDriver="com.mysql.jdbc.Driver";
    	private static final String mysqlURL="jdbc:mysql://localhost:3307/orm";
    	private static final String mysqlUser="root";
    	private static final String mysqlPwd="123456";
    	
    	public static Connection getConnection(){
    		try {
    			Class.forName(mysqlDriver);
    			return DriverManager.getConnection(mysqlURL,mysqlUser,mysqlPwd);
    		} catch (ClassNotFoundException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		return null;
    	}
    	
    	public static void close(ResultSet rs,PreparedStatement ps,Connection conn){
    		if(rs!=null){
    			try {
    				rs.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if(ps!=null){
    			try {
    				ps.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		if(conn!=null){
    			try {
    				conn.close();
    			} catch (SQLException e) {
    				// TODO Auto-generated catch block
    				e.printStackTrace();
    			}
    		}
    		
    	}
    
    }

**2.数据库对应的java对象**

package po;
    import java.util.*;
    import java.io.Serializable;
    import java.sql.*;
    public class Emp implements Serializable {
    	private String empname;
    	private Integer id;
    	private Integer age;
    	public String getEmpname() {
    		return empname;
    	}
    	public void setEmpname(String empname) {
    		this.empname = empname;
    	}
    	public Integer getId() {
    		return id;
    	}
    	public void setId(Integer id) {
    		this.id = id;
    	}
    	public Integer getAge() {
    		return age;
    	}
    	public void setAge(Integer age) {
    		this.age = age;
    	}
    	public Emp(String empname, Integer id, Integer age) {
    		super();
    		this.empname = empname;
    		this.id = id;
    		this.age = age;
    	}
    	public Emp(){
    		
    	}
    
    
    	
    }

**测试代码：**

package po;
    
    import java.io.FileWriter;
    import java.io.IOException;
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.List;
    
    import org.dom4j.Document;
    import org.dom4j.DocumentHelper;
    import org.dom4j.Element;
    import org.dom4j.io.OutputFormat;
    import org.dom4j.io.XMLWriter;
    
    public class TestEmp {
    	public static void main(String[] args) throws SQLException, IOException{
    		//查询所有数据为1个list集合，泛型为Emp
    		List<Emp> list=selAll();
    		//将list中的数据输出到一个xml文件中
    		writeEmpToXml(list);	
    	}
    	private static List<Emp> selAll() throws SQLException{
    		List<Emp> list=new ArrayList<Emp>();
    		String sql="select * from Emp";
    		Connection conn=JDBCUtil.getConnection();
    		PreparedStatement ps=conn.prepareStatement(sql);
    		ResultSet rs=ps.executeQuery();
    		while(rs.next()){
    			Emp emp=new Emp();
    			emp.setEmpname(rs.getString("empname"));
    			emp.setId(rs.getInt("id"));
    			emp.setAge(rs.getInt("age"));
    			list.add(emp);
    		}
    		return list;
    	}
    	
    	/**
    	 * 将list中的数据写入xml中 
    	 * @throws IOException 
    	 */
    	private static void writeEmpToXml(List<Emp> list) throws IOException{
    		Document doc =DocumentHelper.createDocument();
    		Element emps=doc.addElement("emps");
    		for(Emp emp:list){
    			emps.addElement("emp")
    			.addAttribute("id",emp.getId().toString())
    			.addAttribute("name",emp.getEmpname())
    			.addText(emp.getAge().toString());
    		}
    		//输出到文件
    		OutputFormat format=OutputFormat.createPrettyPrint();
    		XMLWriter writer=new XMLWriter(new FileWriter("conf/emps.xml"),format);
    		writer.write(doc);
    		writer.close();
    	}
    }

结果：

emps.xml文件

<?xml version="1.0" encoding="UTF-8"?>
    
    <emps>
      <emp id="1" name="gaoqi">10</emp>
      <emp id="2" name="wang1">10</emp>
      <emp id="3" name="mashibing">19</emp>
      <emp id="10" name="Test_update">18</emp>
      <emp id="11" name="Test_update">18</emp>
      <emp id="12" name="Test_add">18</emp>
      <emp id="13" name="Test_add">18</emp>
      <emp id="14" name="Test_add">18</emp>
    </emps>

[Dom4J_xml]: https://blog.csdn.net/qq_41877184/article/details/95038336