sql文件导入大字段,使用java 代码导入

2019独角兽企业重金招聘Python工程师标准>>>

 sql文件导入大字段,大字段超过4000则报错文本超过长度,

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.OracleDriver;

public class ClobTest {
	 String url = "jdbc:oracle:thin:@192.168.1.102:1521:orcl";
	 String user = "YNZJ";
	 String pwd = "YNZJ";
//	String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
//	String user = "hczz";
//	String pwd = "hczz";

	private void clobImport(List<String> list) throws SQLException {
		DriverManager.registerDriver(new OracleDriver());
		Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象
		PreparedStatement stmt = null;
		for (String sql : list) {
			String content = "";
			try {
				sql = sql.trim();
				int num = sql.lastIndexOf("', '");
				content = sql.substring(num + 4, sql.length() - 3);
				sql = sql.substring(0, num) + "', ?)";
				sql = sql.replace("\"YNZJ\".\"ENET_WORKBOOK\"", "ENET_WORKBOOK_bak");
				stmt = conn.prepareStatement(sql);// 加载SQL语句
				stmt.setCharacterStream(1, new StringReader(content), content.length());
				stmt.executeUpdate();
			} catch (Exception e) {
				System.out.println(e.getMessage());
				System.out.println(content);
				System.out.println(sql);
			}
		}
		stmt.close();
		conn.close();
	}

	private List duqu(String path) throws Exception {
		
		String code = resolveCode(path);
		File file = new File(path);
		InputStream is = new FileInputStream(file);
		InputStreamReader isr = new InputStreamReader(is, code);
		BufferedReader bufferedReader = new BufferedReader(isr);
		List<String> strings = new ArrayList();

		String str = null;
		String sql_d = "";
		int num = 0;
		int ks = 0;
		int dz = 200;
		int jz = ks + dz;
		while ((str = bufferedReader.readLine()) != null) {
			sql_d = sql_d + str;
			if (str.endsWith("');") && sql_d != "") {
				if (num >= ks) {
					strings.add(sql_d);
				}
				sql_d = "";
				num++;
			}
			if (num == jz) {
				break;
			}
		}
		System.out.println(strings.size());
		return strings;

	}

	private String resolveCode(String path) throws Exception {
		InputStream inputStream = new FileInputStream(path);
		byte[] head = new byte[3];
		inputStream.read(head);
		String code = "gb2312"; // 或GBK
		if (head[0] == -1 && head[1] == -2)
			code = "UTF-16";
		else if (head[0] == -2 && head[1] == -1)
			code = "Unicode";
		else if (head[0] == -17 && head[1] == -69 && head[2] == -65)
			code = "UTF-8";

		inputStream.close();
		return code;
	}

	public static void main(String[] args) throws Exception {
		ClobTest a = new ClobTest();
		String path = "D:\\WORD_BOOK超长字段错误数据(1).txt";
		a.clobImport(a.duqu(path));
	}
}

 

转载于:https://my.oschina.net/u/3715963/blog/3000210