java多线程，将oracle中数据导入到sql到文件（包括大字段转换成字符串）

最新推荐文章于 2022-06-26 00:22:28 发布

Jredreamer

最新推荐文章于 2022-06-26 00:22:28 发布

阅读量1.9k

点赞数

分类专栏： Java

本文链接：https://blog.csdn.net/zhang_Red/article/details/17228445

版权

Java 专栏收录该内容

107 篇文章 0 订阅

订阅专栏

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.Reader;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import oracle.sql.CLOB;

public class OracleTest extends Thread{
	
	private int startIndex = 0;
	private String tableName;
	private static int fetchCount = 100000;
	
	public OracleTest(int startIndex, String tableName) {
		this.startIndex = startIndex;
		this.tableName = tableName;
	}
	
	public static void main(String[] args) {
		try {
			// 第一步要Jar包:在oracle安装的目录下,盘符:\oracle\ora92\jdbc\lib\ojdbc14.jar
			// 加载驱动
			Class.forName("oracle.jdbc.driver.OracleDriver");

			// 连接字符串(协议名:jdbc,子协议名: oracle:thin 子名称:@localhost:1521:oracleDB)
			// String url ="jdbc:oracle:thin:@localhost:1521:ora";			
							
			String tableNameString= "table_name";
			
			int count = getCount(tableNameString);	
			 			
			int fetchTimes = count % fetchCount == 0 ? count/fetchCount : (count/fetchCount + 1);
			for (int i=0; i<fetchTimes; i++) {
				OracleTest o = new OracleTest(i, tableNameString);
				o.start();
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void run() {		
		int startRow = startIndex * fetchCount;
		int endRow = (startIndex+1)* fetchCount;
		System.out.println(Thread.currentThread().getName() +", " 
				+ startIndex + "," + startRow + "," + endRow+ ", start");
		createInsertSQL(tableName, startRow, endRow);
		System.out.println(Thread.currentThread().getName() +", " 
				+ startIndex + "," + startRow + "," + endRow+ ", END");
	}

	private void createInsertSQL(String tableName, int startRow, int endRow) {
		String url = "jdbc:oracle:thin:@localhost:1521:oral";
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 建立连接
			conn = DriverManager.getConnection(url, "username", "password");

			// 创建Statement或者是PreparedStatement语句
			st = conn.createStatement();
			String sqlStr = "select * from (select t1.*,rownum as rn from " + tableName + " t1 where rownum<=" + endRow +") t2 where t2.rn>" + startRow;
			// System.out.println(sqlStr);

			// 执行查询
			rs = st.executeQuery(sqlStr);
			ResultSetMetaData data = rs.getMetaData();

			BufferedWriter writer = null;
			FileWriter fileWriter = null;
			try {
				fileWriter = new FileWriter(new File("/Users/cxt/red/tmp/" + tableName +  "_" + (startRow/fetchCount) + ".sql"));
				writer = new BufferedWriter(fileWriter);
				
				String sql = "INSERT INTO " + tableName;
				while (rs.next()) {
					StringBuffer columnBuffer = new StringBuffer("(");
					StringBuffer valueBuffer = new StringBuffer("VALUES(");
					for (int i = 1; i <= data.getColumnCount(); i++) {
						// 获得指定列的列名
						String columnName = data.getColumnName(i);
						
						// 获得指定列的数据类型名
						String columnTypeName = data.getColumnTypeName(i);
						
						// 获得指定列的列值
						String columnValue = null;
						if(columnTypeName.contains("CLOB")) {
							columnValue = clobToString((oracle.sql.CLOB) rs.getClob(i));
						} else {
							columnValue = rs.getString(i);
						} 

						if (columnTypeName.contains("DATE")) {
							if (columnValue.endsWith(".0")) {
								columnValue = columnValue.substring(0,
										columnValue.length() - 2);
							}
						}
						if ("null".equals(columnValue)) {
							columnValue = null;
						}

						columnBuffer.append(columnName).append(",");
						if (!columnTypeName.contains("NUMBER")
								&& null != columnValue) {
							valueBuffer.append("'").append(columnValue)
									.append("'");
						} else {
							valueBuffer.append(columnValue);
						}
						valueBuffer.append(",");
					}
					String column = columnBuffer.toString();
					column = column.substring(0, column.length() - 1) + ")";

					String value = valueBuffer.toString();
					value = value.substring(0, value.length() - 1) + ");";

					String sqlString = sql + column + value;
					// System.out.println(Thread.currentThread().getName()+"; "+sqlString);
					writer.write(sqlString + "\r\n");
				}											
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					
					writer.close();
					fileWriter.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			

			// while (rs.next()) {
			// for (int i = 1; i <= data.getColumnCount(); i++) {
			// // 获得所有列的数目及实际列数
			// int columnCount = data.getColumnCount();
			// // 获得指定列的列名
			// String columnName = data.getColumnName(i);
			// // 获得指定列的列值
			// String columnValue = rs.getString(i);
			// // 获得指定列的数据类型
			// int columnType = data.getColumnType(i);
			// // 获得指定列的数据类型名
			// String columnTypeName = data.getColumnTypeName(i);
			// // 所在的Catalog名字
			// String catalogName = data.getCatalogName(i);
			// // 对应数据类型的类
			// String columnClassName = data.getColumnClassName(i);
			// // 在数据库中类型的最大字符个数
			// int columnDisplaySize = data.getColumnDisplaySize(i);
			// // 默认的列的标题
			// String columnLabel = data.getColumnLabel(i);
			// // 获得列的模式
			// String schemaName = data.getSchemaName(i);
			// // 某列类型的精确度(类型的长度)
			// int precision = data.getPrecision(i);
			// // 小数点后的位数
			// int scale = data.getScale(i);
			// // 获取某列对应的表名
			// String tableName = data.getTableName(i);
			// // 是否自动递增
			// boolean isAutoInctement = data.isAutoIncrement(i);
			// // 在数据库中是否为货币型
			// boolean isCurrency = data.isCurrency(i);
			// // 是否为空
			// int isNullable = data.isNullable(i);
			// // 是否为只读
			// boolean isReadOnly = data.isReadOnly(i);
			// // 能否出现在where中
			// boolean isSearchable = data.isSearchable(i);
			// System.out.println(columnCount);
			// System.out.println("获得列" + i + "的字段名称:" + columnName);
			// System.out.println("获得列" + i + "的字段值:" + columnValue);
			// System.out.println("获得列" + i + "的类型,返回SqlType中的编号:"
			// + columnType);
			// System.out.println("获得列" + i + "的数据类型名:" + columnTypeName);
			// System.out.println("获得列" + i + "所在的Catalog名字:"
			// + catalogName);
			// System.out.println("获得列" + i + "对应数据类型的类:"
			// + columnClassName);
			// System.out.println("获得列" + i + "在数据库中类型的最大字符个数:"
			// + columnDisplaySize);
			// System.out.println("获得列" + i + "的默认的列的标题:" + columnLabel);
			// System.out.println("获得列" + i + "的模式:" + schemaName);
			// System.out
			// .println("获得列" + i + "类型的精确度(类型的长度):" + precision);
			// System.out.println("获得列" + i + "小数点后的位数:" + scale);
			// System.out.println("获得列" + i + "对应的表名:" + tableName);
			// System.out.println("获得列" + i + "是否自动递增:" + isAutoInctement);
			// System.out.println("获得列" + i + "在数据库中是否为货币型:" + isCurrency);
			// System.out.println("获得列" + i + "是否为空:" + isNullable);
			// System.out.println("获得列" + i + "是否为只读:" + isReadOnly);
			// System.out.println("获得列" + i + "能否出现在where中:"
			// + isSearchable);
			// }
			// }
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}				
		}
	}
	
	private static int getCount(String tableName) {
		String url = "jdbc:oracle:thin:@10.27.4.121:1521:ora10g";
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			// 建立连接
			conn = DriverManager.getConnection(url, "newpowersite", "newpowersite_xunjie123");

			// 创建Statement或者是PreparedStatement语句
			st = conn.createStatement();
			String sqlStr = "select count(0) from " + tableName;

			// 执行查询
			rs = st.executeQuery(sqlStr);
			rs.next();
			return rs.getInt(1);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			try {
				rs.close();
				st.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	// 将字CLOB转成STRING类型
    private String clobToString(CLOB clob) throws SQLException, IOException {

        String reString = "";
        if(null == clob) {
        	return null;
        }
        Reader is = clob.getCharacterStream();// 得到流
        BufferedReader br = new BufferedReader(is);
        String s = br.readLine();
        StringBuffer sb = new StringBuffer();
        while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            sb.append(s);
            s = br.readLine();
        }
        reString = sb.toString();
        return reString;
    }
}

上面代码存在的几个问题，以及需要注意的事项：

1、上面代码没有对字符串中的特殊字符进行处理，比如单引号('); 包括对于已经转义的不处理，未转义的要转义

2、上面代码中生成文件的最后是以逗号(,)结尾的，需要替换成分号(;)

3、生成的那件结尾如果包含^M换行符的话，需要处理掉，否则入库会又乱码。