`

如何使用DBUnit做数据备份恢复

 
阅读更多

用DBUnit 做数据备份恢复工具

DBunit(http://dbunit.sourceforge.net/ )是一个非常好的数据库unit系列工具，一般都是用它在测试的时候管理数据库。我们可以发挥一下，用它做一个备份和恢复的数据库管理工具。

以mysql为例，正好现在还没有找到一个好用的mysql备份和恢复工具。这个是在代码中管理不是用ant的方式。

主要是用到核心组件：IDatabaseConnection、IDataSet、DatabaseOperation 。

先看代码：

   /**
     * 导出数据到指定文件
     * @param file 一个标准的java.io.File
     * @param connection  一个标准的java.sql.Connection
     * @throws org.dbunit.DatabaseUnitException
     */
    public static void exportRoom(File file， Connection connection) throws DatabaseUnitException， IOException {
        IDatabaseConnection databaseConnection = new DatabaseConnection(connection);
        QueryDataSet dataSet = new QueryDataSet(databaseConnection);
        dataSet.addTable("room");
        Writer writer = new FileWriter(file);
        XmlDataSetWriter w = new XmlDataSetWriter(writer);
        w.write(dataSet);
        writer.flush();
        writer.close();
    }

 在这个方法中指定了一个表名"room"，如果有多个表可以通过参数或其他的方式按照这种方式继续增加。这个文件是XML格式的。具体格式说明或其他格式参见

http://dbunit.sourceforge.net/components.html#databaseconnection

/**
     * 导入数据到ROOM表
     * @param file  一个标准的java.io.File
     * @param connection 一个标准的java.sql.Connection
     */
    public static void importRoom(File file， Connection connection) throws DatabaseUnitException， IOException， SQLException {
        IDataSetProducer dataSetProducer = new XmlProducer(FileHelper.createInputSource(file));
        IDataSet dataSet = new CachedDataSet(dataSetProducer);
        IDatabaseConnection databaseConnection = new DatabaseConnection(connection);
        DatabaseOperation operation = DatabaseOperation.CLEAN_INSERT;
        DatabaseOperation.TRANSACTION(operation);
        operation.execute(databaseConnection， dataSet);
        DatabaseOperation.CLOSE_CONNECTION(operation);
    }

 这个方法可以把上面生成的XML文件导入到数据库中，如果是其他格式的文件只需要更换IDataSetProducer的实现类就可以了。具体格式请参见APIDOC

在这个方法里使用了事务控制，保证数据的一致性。

有了这两个方法我们就可以再发挥一下，然后为客户提供一个备份恢复数据的工具了。如果再加入压缩文件的支持就更加完美了。

 

package com.chenjo;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.io.Writer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.DatabaseConnection;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.database.QueryDataSet;
import org.dbunit.dataset.CachedDataSet;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.stream.IDataSetProducer;
import org.dbunit.dataset.xml.FlatXmlDataSet;
import org.dbunit.dataset.xml.FlatXmlProducer;
import org.dbunit.dataset.xml.XmlDataSet;
import org.dbunit.dataset.xml.XmlDataSetWriter;
import org.dbunit.dataset.xml.XmlProducer;
import org.dbunit.operation.DatabaseOperation;
import org.dbunit.util.FileHelper;
import org.xml.sax.InputSource;

public class DbUnitTestA {

	Connection getConnection() {
		// 驱动程序名
		String driver = "com.mysql.jdbc.Driver";

		// URL指向要访问的数据库名test
		String url = "jdbc:mysql://127.0.0.1:3306/tt";

		// MySQL配置时的用户名
		String user = "root";

		// MySQL配置时的密码
		String password = "root";

		Connection conn = null;
		// 加载驱动程序
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url, user, password);

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return conn;

	}

	/**
	 * 导出数据到指定文件
	 * 
	 * @param file
	 *            一个标准的java.io.File
	 * @param connection
	 *            一个标准的java.sql.Connection
	 * @throws org.dbunit.DatabaseUnitException
	 */
	public static void exportStudent(File file, Connection connection,
			boolean flat) throws DatabaseUnitException, IOException {
		IDatabaseConnection databaseConnection = new DatabaseConnection(
				connection);
		QueryDataSet dataSet = new QueryDataSet(databaseConnection);
		dataSet.addTable("student");
		if (flat) {
			FlatXmlDataSet.write(dataSet, new FileOutputStream(file));
		} else {
			XmlDataSet.write(dataSet, new FileOutputStream(file));
		}
	}

	/**
	 * 导入数据到student表
	 * 
	 * @param file
	 *            一个标准的java.io.File
	 * @param connection
	 *            一个标准的java.sql.Connection
	 */
	public static void importStudent(File file, Connection connection,
			boolean flat) throws DatabaseUnitException, IOException,
			SQLException {
		IDataSetProducer dataSetProducer = null;
		if (flat) {
			dataSetProducer = new FlatXmlProducer(
					FileHelper.createInputSource(file));
		} else {
			dataSetProducer = new XmlProducer(
					FileHelper.createInputSource(file));
		}
		IDataSet dataSet = new CachedDataSet(dataSetProducer);
		IDatabaseConnection databaseConnection = new DatabaseConnection(
				connection);
		DatabaseOperation operation = DatabaseOperation.CLEAN_INSERT;
		DatabaseOperation.TRANSACTION(operation);
		operation.execute(databaseConnection, dataSet);
		DatabaseOperation.CLOSE_CONNECTION(operation);
	}

	/**
	 * 导入数据到ROOM表
	 * 
	 * @param file
	 *            一个标准的java.io.File
	 * @param connection
	 *            一个标准的java.sql.Connection
	 */
	public static void importRoomFlat(File file, Connection connection)
			throws DatabaseUnitException, IOException, SQLException {
		IDataSetProducer dataSetProducer = new FlatXmlProducer(
				FileHelper.createInputSource(file));
		IDataSet dataSet = new CachedDataSet(dataSetProducer);
		IDatabaseConnection databaseConnection = new DatabaseConnection(
				connection);
		DatabaseOperation operation = DatabaseOperation.CLEAN_INSERT;
		DatabaseOperation.TRANSACTION(operation);
		operation.execute(databaseConnection, dataSet);
		DatabaseOperation.CLOSE_CONNECTION(operation);
	}

	public static void main(String[] args) {
		DbUnitTestA op = new DbUnitTestA();
		Connection conn = op.getConnection();
		// op.findStudent("0001");
		// /C:\Users\chenjo\workspace_luna\TT\.
		// System.out.println((new File(".")).getAbsolutePath());
		try {
			importStudent(new File("student_nonflat.xml"), conn, false);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

}

 扁平XML:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <student Id="0001" name="翁仔" sex="m" birthday="1979-12-31"/>
  <student Id="0002" name="王翠花" sex="f" birthday="1982-08-09"/>
</dataset>

 非扁平XML:

<?xml version='1.0' encoding='UTF-8'?>
<dataset>
  <table name="student">
    <column>Id</column>
    <column>name</column>
    <column>sex</column>
    <column>birthday</column>
    <row>
      <value>0001</value>
      <value>翁仔</value>
      <value>m</value>
      <value>1979-12-31</value>
    </row>
    <row>
      <value>0002</value>
      <value>王翠花</value>
      <value>f</value>
      <value>1982-08-09</value>
    </row>
  </table>
</dataset>

 库依赖:

//.classpath
<?xml version="1.0" encoding="UTF-8"?>
<classpath>
	<classpathentry kind="src" path="src"/>
	<classpathentry kind="con" path="org.eclipse.jdt.launching.JRE_CONTAINER/org.eclipse.jdt.internal.debug.ui.launcher.StandardVMType/JavaSE-1.8"/>
	<classpathentry kind="lib" path="lib/mysql-connector-java-5.5.0.jar"/>
	<classpathentry kind="con" path="org.eclipse.jdt.junit.JUNIT_CONTAINER/3"/>
	<classpathentry kind="lib" path="H:/Users/chenjo/Downloads/slf4j-1.7.7/slf4j-1.7.7/slf4j-api-1.7.7.jar"/>
	<classpathentry kind="lib" path="H:/Users/chenjo/Downloads/slf4j-1.7.7/slf4j-1.7.7/slf4j-simple-1.7.7.jar"/>
	<classpathentry kind="output" path="bin"/>
</classpath>

 MySQL数据库建立脚本:

# SQL-Front 5.1  (Build 4.16)

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE */;
/*!40101 SET SQL_MODE='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES */;
/*!40103 SET SQL_NOTES='ON' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;


# Host: localhost    Database: tt
# ------------------------------------------------------
# Server version 5.5.40

#
# Source for table student
#

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `Id` char(4) NOT NULL DEFAULT '',
  `name` char(50) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

#
# Dumping data for table student
#

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES ('0001','翁仔','m','1979-12-31');
INSERT INTO `student` VALUES ('0002','王翠花','f','1982-08-09');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

 

  • TT.zip (23.3 KB)
  • 下载次数: 0
分享到：
| win7右键DOS命令菜单
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics