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

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();}}@Overridepublic 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换行符的话，需要处理掉，否则入库会又乱码。