二、使用XML迁移数据库表中数据

数据库连接工厂

package com.test.dom4j;

import java.sql.Connection;

public interface IDbFactory {
	/**
	 * 使用JDBC创建数据库连接
	 * 使用Mysql数据库，自行下载Mysql数据库驱动连接数据库
	 * JDBC Mysql连接配置
	 *		url=jdbc:mysql://localhost:3306/w1?useUnicode=true&characterEncoding=utf8
	 *		username=root
	 *		password=root
	 * @param url 数据库连接URL
	 * @param user 数据库用户
	 * @param pwd 数据库密码
	 * @return
	 */
	public Connection getCon(String url,String user,String pwd);
}

工厂实现类

package com.test.dom4j;

import java.sql.Connection;
import java.sql.DriverManager;

public class DbFactory implements IDbFactory{

	public Connection getCon(String url, String user, String pwd) {
		try
		{
			Class.forName("com.mysql.jdbc.Driver");//java.lang.Class
			Connection con = DriverManager.getConnection(url, user, pwd);
			return con;
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return null;
	}

}

数据迁移接口

package com.test.dom4j;

import java.sql.Connection;

public interface ITableMagrate {
	/**
	 * 此方法实现读取数据库表中数据并保存到文件系统，以XML形式存储
	 * XML存储格式为,根据节点为table,属性name为表名,每行生成row节点,row节点中每列生成column节点,属性name为列名
	 * <table name="">
	 * 	<row>
	 * 		<column name=""></column>
	 * 		<column name=""></column>
	 *      ...
	 * 	<row>
	 * 	<row>
	 * 		<column name=""></column>
	 * 		<column name=""></column>
	 *      ...
	 * 	<row>
	 * </table>
	 * @param con 数据库连接
	 * @param tableName 表名
	 * @param file 文件绝对目录
	 */
	public void db2xml(Connection con,String tableName,String file);
	
	/**
	 * 从XML数据文件中解析数据并保存到数据库表tableName中,数据库表可以预先定义
	 * XML存储格式为,根据节点为table,属性name为表名,每行生成row节点,row节点中每列生成column节点,属性name为列名
	 * <table name="">
	 * 	<row>
	 * 		<column name=""></column>
	 * 		<column name=""></column>
	 *      ...
	 * 	<row>
	 * 	<row>
	 * 		<column name=""></column>
	 * 		<column name=""></column>
	 *      ...
	 * 	<row>
	 * </table>
	 * @param con 数据库连接
	 * @param tableName 表名
	 * @param file 文件绝对目录
	 */
	public void xml2db(Connection con,String tableName,String file);
}

迁移实现类

根据XML解析动态拼接SQL，针对类不同类型还需要细化业务逻辑，本例只针对字符类型字段处理。

package com.test.dom4j;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.Iterator;
import java.util.List;

import org.dom4j.Attribute;
import org.dom4j.CDATA;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.SAXReader;
import org.dom4j.io.XMLWriter;

public class TableMagrate implements ITableMagrate{

	@Override
	public void db2xml(Connection con, String tableName, String file) {
		try
		{
			Document doc = DocumentHelper.createDocument();
			Element root = DocumentHelper.createElement("table");
			Attribute attTab = DocumentHelper.createAttribute(root, "name", tableName);
			root.add(attTab);
			doc.setRootElement(root);
			String sql = "select * from "+tableName;
			Statement stat = con.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			ResultSetMetaData rsmd = rs.getMetaData();
			int colCnt = rsmd.getColumnCount();
			while(rs.next())
			{
				Element rowEle = DocumentHelper.createElement("row");
				root.add(rowEle);
				for(int i=1;i<=colCnt;i++)
				{
					String colName = rsmd.getColumnName(i);
					String colVal = rs.getString(colName);
					if(colVal == null)
						colVal = "";
					Element columnEle = DocumentHelper.createElement("column");
					rowEle.add(columnEle);
					Attribute att = DocumentHelper.createAttribute(columnEle, "name", colName);
					columnEle.add(att);
					CDATA cdata = DocumentHelper.createCDATA(colVal);
					columnEle.add(cdata);
				}
			}
			OutputFormat of = new OutputFormat("\t",true);
			FileOutputStream fos = new FileOutputStream(file);
			XMLWriter xmlw = new XMLWriter(fos,of);
			xmlw.write(doc);
			fos.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

	@Override
	public void xml2db(Connection con, String tableName, String file) {
		try
		{
			FileInputStream fis = new FileInputStream(file);
			SAXReader saxr = new SAXReader();
			Document doc = saxr.read(fis);
			Element root = doc.getRootElement();
			Attribute attTab = root.attribute("name");
			String tabName = attTab.getValue();
			List rows = root.elements("row");
			
			for(Iterator it=rows.iterator();it.hasNext();)
			{
				//insert into table (id,name) values (1,2)
				StringBuffer sb = new StringBuffer();
				sb.append("insert into ");
				sb.append(tabName);
				sb.append(" (");
				StringBuffer sb2 = new StringBuffer();
				sb2.append(" values (");
				Element rowEle = (Element)it.next();
				List cols = rowEle.elements("column");
				for(Iterator it2=cols.iterator();it2.hasNext();)
				{
					Element colEle = (Element)it2.next();
					Attribute colAtt = colEle.attribute("name");
					String colName = colAtt.getValue();
					if("id".equals(colName))
						continue;
					sb.append(colName);
					sb.append(",");
					String colVal = colEle.getText();
					if("".equals(colVal))
					{
						sb2.append("null");
						sb2.append(",");
					}
					else
					{
						sb2.append("'");
						sb2.append(colVal);
						sb2.append("',");
					}
				}
				String sql1 = sb.toString();
				if(sql1.endsWith(","))
					sql1 = sql1.substring(0,sql1.length()-1);
				sql1 = sql1 + ")";
				
				String sql2 = sb2.toString();
				if(sql2.endsWith(","))
					sql2 = sql2.substring(0,sql2.length()-1);
				sql2 = sql2 + ")";
				
				String sql = sql1 + sql2;
				Statement stat = con.createStatement();
				stat.executeUpdate(sql);
				stat.close();
			}
			fis.close();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
	}

}

测试类

package com.test.dom4j;

import java.sql.Connection;

public class TestDb {

	public static void main(String[] args) {
		IDbFactory dbf = new DbFactory();
		ITableMagrate itm = new TableMagrate();
		String url = "jdbc:mysql://localhost:3306/w1?useUnicode=true&characterEncoding=utf8";
		String user = "root";
		String pwd = "root";
		Connection con = dbf.getCon(url, user, pwd);
		//itm.db2xml(con, "t_student", "d:\\my.xml");
		itm.xml2db(con, "t_student", "d:\\my.xml");
	}

}