读写CSV文件的代码

不同的中间件对应不同的JDK，weblogic8，需要使用JDK1.4，使用高版本出现过类找不到的情况。

 

1、读入数据导入数据库

参考http://zjb10000.iteye.com/blog/619699

package input;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import util.DbPool;

import com.csvreader.CsvReader;

public class BatchProcess {

	public static void transferData(int m, int n,String csvFilePath) throws ClassNotFoundException, SQLException, IOException {
		
		DbPool dbPool = new DbPool();
		dbPool.createPool();
		
		CsvReader reader = new CsvReader(csvFilePath);

		long start = System.currentTimeMillis();

		String insertSql = "INSERT INTO LIJING727_TEMPDATA(DATA0,DATA1,"
				+ "DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9) VALUES "
				+ "(?,?,?,?,?,?,?,?,?,?)";

		
		for (int i = 0; i < m; i++) {

			Connection insertConn = dbPool.getConnection();

			try {

				PreparedStatement insertPstmt = insertConn.prepareStatement(insertSql);
				
				for (int j = 0; j < n; j++) {
					
					reader.readRecord();
					insertPstmt.setInt(1, Integer.parseInt(reader.get(0)));
					insertPstmt.setString(2, reader.get(1));
					insertPstmt.setString(3, reader.get(2));
					insertPstmt.setString(4, reader.get(3));
					insertPstmt.setString(5, reader.get(4));
					insertPstmt.setString(6, reader.get(5));
					insertPstmt.setString(7, reader.get(6));
					insertPstmt.setString(8, reader.get(7));
					insertPstmt.setString(9, reader.get(8));
					insertPstmt.setString(10, reader.get(9));

					insertPstmt.addBatch();
					
				}

				insertPstmt.executeBatch();
				insertPstmt.close();
				dbPool.returnConnection(insertConn);


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

		}
		
		reader.close();

		long end = System.currentTimeMillis();

		System.out
				.println("批量导入" + m + "*" + n + " 用时" + (end - start) / 1000f);

	}
}

 

 

2、从数据库读出，写入数据库

package util;

import java.io.IOException;

import com.csvreader.CsvWriter;

public class CsvFileWriter {

	private CsvWriter cWriter;
	
	public CsvFileWriter(String filePath){
		
		cWriter = new CsvWriter(filePath);
		
	}
	
	public synchronized void writeToFile(String[] contents){
		
		try {
			
			cWriter.writeRecord(contents);
			
			cWriter.flush();
			
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public synchronized void close(){
		
		cWriter.close();
		
	}
}

 

 

package output;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Timer;
import java.util.TimerTask;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

import oracle.jdbc.driver.OraclePreparedStatement;

import util.CsvFileWriter;
import util.DbPool;

public class MultiThreadProcess {

	// 文件最后没有关闭，使用定时器关闭
	public static void batchOutput(int threadNum, int timeNum, int batchNum,
			String filePath) throws ClassNotFoundException, SQLException,
			InterruptedException {

		ExecutorService exec = Executors.newCachedThreadPool();

		DbPool dbPool = new DbPool();
		dbPool.createPool();

		CsvFileWriter cWriter = new CsvFileWriter(filePath);

		long start = System.currentTimeMillis();

		for (int i = 0; i < threadNum; i++) {

			exec.execute(new BatchOutputTask(dbPool, cWriter, i, timeNum,
					batchNum, start));

		}

		exec.shutdown();
		
		Timer timer = new Timer();
		timer.schedule(new CloseStream(cWriter), 120000);

	}

}

class CloseStream extends TimerTask {

	CsvFileWriter cWriter;

	CloseStream(CsvFileWriter cWriter) {
		this.cWriter = cWriter;
	}

	@Override
	public void run() {

		cWriter.close();
		System.out.println("file closed!");
		
	}

}

class BatchOutputTask implements Runnable {

	DbPool dbPool;
	CsvFileWriter cWriter;
	int threadNo = 0;
	int timeNum = 0;
	int batchNum = 0;
	long start = 0;

	BatchOutputTask(DbPool dbPool, CsvFileWriter cWriter, int threadNum,
			int timeNum, int batchnum, long start) {

		this.dbPool = dbPool;
		this.cWriter = cWriter;
		this.threadNo = threadNum;
		this.timeNum = timeNum;
		this.batchNum = batchnum;
		this.start = start;

	}

	@Override
	public void run() {

		String selectSql = "SELECT b.DATA0,b.DATA1,b.DATA2,b.DATA3,b.DATA4,"
				+ "b.DATA5,b.DATA6,b.DATA7,b.DATA8,b.DATA9"
				+ " FROM (SELECT rownum r,"
				+ "a.DATA0,a.DATA1,a.DATA2,a.DATA3,a.DATA4,a.DATA5,a.DATA6,"
				+ "a.DATA7,a.DATA8,a.DATA9"
				+ " FROM LIJING727_ORIGDATA a WHERE rownum <= ?) b"
				+ " WHERE r >= ?";

		try {

			Connection selectConn = dbPool.getConnection();

			OraclePreparedStatement selectPstmt = (OraclePreparedStatement) selectConn
					.prepareStatement(selectSql);

			for (int i = 0; i < timeNum; i++) {

				selectPstmt.setInt(1, threadNo * timeNum * batchNum + (i + 1)
						* batchNum);
				selectPstmt.setInt(2, threadNo * timeNum * batchNum + i
						* batchNum + 1);

				ResultSet rs = selectPstmt.executeQuery();

				while (rs.next()) {

					String[] contents = { String.valueOf(rs.getInt(1)),
							rs.getString(2), rs.getString(3), rs.getString(4),
							rs.getString(5), rs.getString(6), rs.getString(7),
							rs.getString(8), rs.getString(9), rs.getString(10) };

					cWriter.writeToFile(contents);

				}

				rs.close();

			}

			selectPstmt.close();
			dbPool.returnConnection(selectConn);

			long end = System.currentTimeMillis();

			System.out.println("批量导出数据用时" + (end - start) / 1000f);

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

}