package test;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;
import jxl.Sheet;
import jxl.read.biff.BiffException;
import service.ExcelService;
import util.JdbcUtils;
import bean.HisTrans;
import bean.Point;
public class TestOracle {
/**
* @param args
* @throws SQLException
* @throws IOException
* @throws BiffException
*/
public static void main(String[] args) throws BiffException, IOException {
long timeBegin = System.currentTimeMillis();
long timeEnd;
int colBegin = 0, rowBegin = 0;
int colEnd, rowEnd;
int index = 0;
String filename = "E:/���ж��˵�/2012/3.xls";
ExcelService eService = new ExcelService();
Sheet rs = eService.getSheet(filename, index);
rowEnd = rs.getRows();
// rowEnd = 10000;
colEnd = rs.getColumns();
Map<Point, String> result = eService.readExcel(rs, rowBegin, rowEnd, colBegin, colEnd);
String sql = "insert into data_to_import "
+ "(SETTLE_DATE, PAN, ACC_TYPE, TXN_NAME, TXN_AMT, FEE_AMT, CNAME, TID, SP_MID, SP_TID, TRACE1, RRN, TXN_DATE, TRANS_ID, AERA ) "
+ " values (?,?,?,?,?,?,?,?,?,?,?,?,?,SEQ_DATA_TO_IMPORT.NEXTVAL,?)";
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JdbcUtils.getConnection();
conn.setAutoCommit(true);
ps = conn.prepareStatement(sql);
ps.clearBatch();
for (int i = rowBegin; i < rowEnd; i++) {
HisTrans trans = eService.getRow(result, i);
ps.setString(1, trans.getSettle_date());
ps.setString(2, trans.getPan());
ps.setFloat(3, trans.getAcc_type());
ps.setString(4, trans.getTxn_name());
ps.setFloat(5, trans.getTxn_amt());
ps.setFloat(6, trans.getFee_amt());
ps.setString(7, trans.getCname());
ps.setString(8, trans.getTid());
ps.setString(9, trans.getSp_mid());
ps.setString(10, trans.getSp_tid());
ps.setString(11, trans.getTrace1());
ps.setString(12, trans.getRrn());
ps.setString(13, trans.getTxn_date());
ps.setString(14, trans.getArea());
ps.addBatch();
}
ps.executeBatch();
conn.commit();
} catch (SQLException e) {
e.printStackTrace();
if (null != conn) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
}
} finally {
JdbcUtils.close(conn, ps, null);
}
timeEnd = System.currentTimeMillis();
System.out.println(rowEnd + " rows effect!");
System.out.println("it takes " + (timeEnd - timeBegin));
}
}