Java将数据库数据转存到文件里 java 数据库迁移

数据库A的字符集是US7ASCII，数据库B的字符集是AL32UTF8，想把数据库A的用户a的所有表复制到数据库B的用户b下，由于两个库的字符集不同，在迁移的过程中会出现导入不成功或导入乱码的问题。现把我的几个尝试和最终的解决办法总结一下，分享给大家。

尝试1：导出导入dmp

尝试结果：乱码、导入不成功，现在还没有查到解决办法

尝试2：导出导入sql

尝试结果：可以成功导入，不会出现乱码

方法：(1)导出前要保证客户端的字符集和数据库A的字符集保持一致

             (2)将数据库A中的用户a下的表导出成sql文件

             (3)导入前要保证客户端的字符集和数据库B的字符集保持一致

             (4)将导出的sql文件导入到数据库B的用户b下

查看或设置字符集的方法：

  查看数据库的字符集：select userenv(‘language’) from dual

  查看或设置客户端的字符集：

    在windows平台下，注册表里面对应OracleHome的NLS_LANG。还可以在dos窗口里面自己设置，比如:

　　set nls_lang=AMERICAN_AMERICA.ZHS16GBK

    这样就只影响这个窗口里面的环境变量。

缺点：要是表中数据比较多的话，导出的sql文件比较大，导入时会出现读取文件失败的错误

尝试3：直接将整个数据库导入到与其字符集相同的中间库，然后直接将中间库的字符集修改成要导入的数据库的字符集（这种方法没还有试）

oracle的字符集有互相的包容关系。如us7ascii就是zhs16gbk的子集,从us7ascii到zhs16gbk不会有数据解释上的问题,不会有数据丢失。在所有的字符集中utf8应该是最大,因为它基于unicode,双字节保存字符(也因此在存储空间上占用更多)。

　　一旦数据库创建后，数据库的字符集理论上讲是不能改变的。因此，在设计和安装之初考虑使用哪一种字符集十分重要。根据Oracle的官方说明，字符集的转换是从子集到超集受支持,反之不行。如果两种字符集之间根本没有子集和超集的关系，那么字符集的转换是不受oracle支持的。对数据库server而言，错误的修改字符集将会导致很多不可测的后果，可能会严重影响数据库的正常运行，所以在修改之前一定要确认两种字符集是否存在子集和超集的关系。一般来说，除非万不得已，我们不建议修改oracle数据库server端的字符集。特别说明，我们最常用的两种字符集ZHS16GBK和ZHS16CGB231280之间不存在子集和超集关系，因此理论上讲这两种字符集之间的相互转换不受支持。

　　1、修改server端字符集(不建议使用)

　　在oracle 8之前，可以用直接修改数据字典表props$来改变数据库的字符集。但oracle8之后，至少有三张系统表记录了数据库字符集的信息，只改props$表并不完全，可能引起严重的后果。正确的修改方法如下:

　　$sqlplus /nolog

　　SQL>conn / as sysdba;

　　若此时数据库服务器已启动，则先执行SHUTDOWN IMMEDIATE命令关闭数据库服务器，然后执行以下命令:

　

SQL>STARTUP MOUNT;
 　　SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;
 　　SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
 　　SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
 　　SQL>ALTER DATABASE OPEN;
 　　SQL>ALTER DATABASE CHARACTER SET ZHS16GBK;
 　　SQL>ALTER DATABASE national CHARACTER SET ZHS16GBK;
 　　SQL>SHUTDOWN IMMEDIATE;
 　　SQL>STARTUP

　　2、修改dmp文件字符集

　　dmp文件的第2第3字节记录了字符集信息，因此直接修改dmp文件的第2第3字节的内容就可以‘骗’过oracle的检查。这样做理论上也仅是从子集到超集可以修改，但很多情况下在没有子集和超集关系的情况下也可以修改，我们常用的一些字符集，如US7ASCII， WE8ISO8859P1，ZHS16CGB231280，ZHS16GBK基本都可以改。因为改的只是dmp文件，所以影响不大。

　　具体的修改方法比较多，最简单的就是直接用UltraEdit修改dmp文件的第2和第3个字节。比如想将dmp文件的字符集改为ZHS16GBK，可以用以下SQL查出该种字符集对应的16进制代码:

SQL> select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual;
 　　0354
SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;
     ZHS16GBK

　　然后将dmp文件的2、3字节修改为0354即可。

　　如果dmp文件很大，用ue无法打开，就需要用程序的方法了。网上有人用java存储过程写了转换的程序(用java存储过程的好处是通用性教好，缺点是比较麻烦)。我在windows下测试通过。但要求oracle数据库一定要安装JVM选项。有兴趣的朋友可以研究一下程序代码

尝试4：通过java程序实现数据源间表的复制

方法：(1)通过PL/SQL将数据库A中用户a下的数据表的表结构导出成sql文件，导出前将Where clause设成1=2然后导出，导出前要保证客户端的字符集和数据库A的                   字符集保持一致

             (2)将导出的sql文件导入到数据库B的用户b下，导入前要保证客户端的字符集和数据库B的字符集保持一致，这步完事后，用户b下会创建好对应的数据表了

             (3)若存在一个汉字对应不同字符个数的情况，比如：一个汉字用GBK存放是2个字符，用AL32UTF8占用3个字符，在第二步创建完表之后可以通过语句批量                     修改字段大小

       

select 'alter table ' || owner || '.' || table_name || ' modify' || '(' ||
                                column_name || ' ' || data_type || '(' || ceil(data_length * 1.5) ||
                               '));'
                     from dba_tab_columns
                  where data_type like '%CHAR%'
                       and owner in ('SJQY')

 (4)通过java程序，循环遍历用户a下的所有表，将表中的数据依次插入到用户b下对应的表中，注意要在程序中实现数据转码

java源代码：

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.json.JSONObject;

public class TransferTest {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		String dbA = "sjgl/sjgl@76.20.19.152:1521/ctais";
		// String dbB = "sjgl/sjgl@76.20.19.151:1523/jssqsj";
		String dbB = "sjqy/sjqy@127.0.0.1:1521/dzda";
		Connection connA = getConn(dbA);
		Connection connB = getConn(dbB);
		List tables = getAllTableNames(connA, "sjgl");
		for (int i = 0; i < tables.size(); i++) {
			connA = getConn(dbA);
			connB = getConn(dbB);
			String tableName = String.valueOf(tables.get(i));
			importData(tableName, connA, connB);
		}
	}

	/**
	 * 获取指定数据库和用户的所有表名
	 * 
	 * @param conn
	 *            连接数据库对象
	 * @param user
	 *            用户
	 * @param database
	 *            数据库名
	 * @return
	 */
	public static List getAllTableNames(Connection conn, String user) {
		List tableNames = new ArrayList();
		if (conn != null) {
			try {
				DatabaseMetaData dbmd = conn.getMetaData();
				// 表名列表
				ResultSet rest = dbmd.getTables(null, null, null,
						new String[] { "TABLE" });
				// 输出 table_name
				while (rest.next()) {
					String tableSchem = rest.getString("TABLE_SCHEM");
					if (user.equalsIgnoreCase(tableSchem)) {
						if (rest.getString("TABLE_NAME").indexOf("=") == -1) {
							tableNames.add(rest.getString("TABLE_NAME"));
						}
					}
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return tableNames;
	}

	private static Connection getConn(String database) {
		int firstIdx = database.indexOf("/");
		String userName = database.substring(0, firstIdx);
		String password = database.substring(firstIdx + 1,
				database.indexOf("@"));
		String dbUrl = database.substring(database.indexOf("@"));

		Connection conn = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:" + dbUrl;
			conn = DriverManager.getConnection(url, userName, password);
		} catch (SQLException e1) {
			e1.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return conn;
	}

	private static ArrayList TableToArrayList(String tableName, Connection connA) {
		Statement stmt = null;
		ArrayList list = new ArrayList();

		try {
			stmt = connA.createStatement();
			String sql = "select * from " + tableName;
			System.out.println(sql);
			ResultSet result = stmt.executeQuery(sql);

			int columnCount = result.getMetaData().getColumnCount();// 列数。
			Integer[] columnType = new Integer[columnCount];// 列的数据类型的数字表示。

			for (int i = 0; i < columnCount; i++) {
				columnType[i] = new Integer(result.getMetaData().getColumnType(
						i + 1));
			}
			list.add(columnType);

			while (result.next()) {
				Object[] columnValue = new Object[columnCount];// 列值。
				for (int i = 0; i < columnCount; i++) {
					columnValue[i] = result.getObject(i + 1);
				}
				list.add(columnValue);
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (connA != null)
					connA.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	private static void ArrayListToTable(ArrayList list, Connection connB,
			String tableName) throws Exception {
		Statement stmt = null;
		PreparedStatement pstmt = null;
		try {
			stmt = connB.createStatement();

			// 把list中的数据插入到表tableName中.
			// 如果tableName表中已经有数据，去掉数据。
			String querySql = "Select * from " + tableName;
			stmt = connB.createStatement();
			ResultSet rs = stmt.executeQuery(querySql);
			if (rs.next()) {
				String deleteSql = "delete from " + tableName;
				stmt.execute(deleteSql);
			}

			Integer[] columnType = (Integer[]) list.get(0);
			Object[] columnValue = null;
			int columnCountNew = columnType.length;

			StringBuffer preSql = new StringBuffer("");
			for (int i = 0; i < columnCountNew - 1; i++) {
				preSql.append("?,");
			}
			preSql.append("?");
			String insertSql = "insert into " + tableName + " values(" + preSql
					+ ")";
			System.out.println("pre insertSql is:" + insertSql);
			pstmt = connB.prepareStatement(insertSql);

			// System.out.println("ParameterCount："
			// + pstmt.getParameterMetaData().getParameterCount());
			// insert the data by row.
			for (int i = 1; i < list.size(); i++) {

				columnValue = (Object[]) list.get(i);
				int type, j;

				for (j = 0; j < columnCountNew; j++) {
					type = columnType[j].intValue();
					int temp = j + 1;
					if (columnValue[j] == null) {
						pstmt.setNull(temp, type);
					} else {
						switch (type) {
						case java.sql.Types.BIGINT:
							pstmt.setLong(temp, (Long) columnValue[j]);
							break;
						case java.sql.Types.INTEGER:
							pstmt.setLong(temp, (Long) columnValue[j]);
							break;
						case java.sql.Types.FLOAT:
							pstmt.setFloat(temp, (Float) columnValue[j]);
							break;
						case java.sql.Types.DOUBLE:
							pstmt.setDouble(temp, (Double) columnValue[j]);
							break;
						case java.sql.Types.DATE:
							pstmt.setDate(temp, (Date) columnValue[j]);
							break;
						case java.sql.Types.TIME:
							pstmt.setTime(temp, (Time) columnValue[j]);
							break;
						case java.sql.Types.TIMESTAMP:
							pstmt.setTimestamp(temp, (Timestamp) columnValue[j]);
							break;
						default:
							String tmp = columnValue[j].toString();
							tmp = new String(tmp.getBytes("ISO-8859-1"), "GBK");
							pstmt.setString(temp, tmp);
							break;
						}
					}

				}

				System.out.println("The row " + i + " :" + insertSql);
				pstmt.executeUpdate();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (connB != null)
					connB.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	private static void importData(String tableName, Connection connA,
			Connection connB) {

		ArrayList list = new ArrayList();

		list = TableToArrayList(tableName, connA);

		try {
			ArrayListToTable(list, connB, tableName);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

补充：2015/5/6 10:31

数据库间数据转存时，字符集转换问题（后续添加中。。。）

1）源数据库字符集：AL32UTF8 目标数据库字符集：US7ASCII

new String(tmp.getBytes("GBK"),"ISO8859-1")

2）源数据库字符集：US7ASCII  目标数据库字符集：AL32UTF8

new String(tmp.getBytes("ISO8859-1"),"GBK")

补充：2015/08/10 16:05

数据库A（AL32UTF8） 数据库B（US7ASCII）

尝试将A中的表test同步到B中的表test

表test的内容如下：

dm   mc

test  测试

第一步：在A中执行

create or replace function encodeStr(str1 in varchar2,strCode in varchar2) return varchar2
is
ss varchar2(500);
  begin
  ss :=   utl_url.escape(str1, true, strCode);
  return ss;
end;

第二步：在A中执行以下语句，创建view

create view v_test as select dm,encodeStr(mc,'GB2312') mc from test

第三步：在B中建立访问A的dblink（dblink_a）

第四步：在B中执行以下语句即可

select dm,utl_url.unescape(mc,'US7ASCII') mc from v_test@dblink_a

补充：20150811

oralce中，varchar2有两个最大长度：一个是在字段类型4000；一个是在PL/SQL中变量类型32767

当带有汉字的字段过长的时候，在调用encodeStr的时候就会出现以下错误：

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
 ORA-06512: at "JNNSFW.ENCODESTR", line 5

解决办法：使用clob代替varchar2

修改之后的函数如下：

create or replace function encodeStr2(str1 in varchar2,strCode in varchar2) return clob
 is
 clob_test clob;
   begin
   dbms_lob.createtemporary(clob_test,true);
   clob_test:=utl_url.escape(str1, true, strCode);
   return clob_test;
 end;

这样在创建的v_test视图中会包含clob类型的字段，此时在第四步通过dblink访问v_test的时候会出现以下错误：

ORA-22992: cannot use LOB locators selected from remote tables

解决办法：

可以先建立临时表再插入本地表。。
方法如下.在pl/sql中执行
第一步建临时表
Create global temporary table temp on commit delete rows as select * from v_test@dblink_a
第二步 插入本地表
insert into temp select * from v_test@dblink_a

第三步 在B中执行

create or replace function decodeStr(myClob in clob,strCode in varchar2) return varchar2
 is
   begin
   return utl_url.unescape(dbms_lob.substr(myClob,dbms_lob.getlength(myClob),1),strCode);
 end;

第四步 在B中执行以下语句即可

select dm,decodestr(mc,'US7ASCII') mc from temp