PreparedStatement批量提交
package com.audaque.datadiscovery.job.service.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Service;
import com.audaque.datadiscovery.job.service.DataTransfer;
import com.audaque.datadiscovery.util.DBUtil;
import com.audaque.lib.db.constants.DatabaseType;
@Service
public class CopyOfDataTransferImpl implements DataTransfer {
private static final Log logger = LogFactory.getLog(CopyOfDataTransferImpl.class);
/**
* 往oracle中间库推送数据
*/
public void transferData() {
Connection conn = null;
PreparedStatement ps = null;
StringBuffer sql = new StringBuffer("insert into TEAMEVENTBDINFO(id, creator) ")
.append("values (?,?)");
try{
//从mysql数据库取得数据
List<Map<String, Object>> list = getData();
if(list != null && list.size() > 0) {
conn = DBUtil.getConnection(DatabaseType.Oracle.getName(), "jdbc:oracle:thin:@localhost:1521:orcl", "admin", "admin");
conn.setAutoCommit(false);
ps = conn.prepareStatement(sql.toString());
for(Map<String, Object> data : list) {
int index = 1;
ps.setObject(index++, data.get("id"), Types.VARCHAR);
ps.setObject(index++, data.get("creator"), Types.INTEGER);
ps.addBatch();
}
ps.executeBatch();
conn.commit();
conn.setAutoCommit(true);
}
}catch(Exception e){
logger.error("load schemas faild", e);
//发生异常时,还原现场
try {
if(conn != null && !conn.isClosed()) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally{
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.error("after load schemas failed", e);
}
}
}
}
/**
* 从mysql数据库取得数据
* @return
*/
public List<Map<String, Object>> getData() {
List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs=null;
StringBuffer sql = new StringBuffer()
.append("SELECT id, creator from t_table ta ");
try{
conn = DBUtil.getConnection(DatabaseType.Oracle.getName(), "jdbc:mysql://localhost:3306/ds?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull", "root", "123456");
ps = conn.prepareStatement(sql.toString());
rs = ps.executeQuery();
while(rs.next()) {
Map<String, Object> map = new HashMap<String, Object>();
map.put("id", rs.getString("id"));
map.put("creator", rs.getString("creator"));
list.add(map);
}
}catch(Exception e){
logger.error("load schemas faild", e);
}finally{
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.error("after load schemas failed", e);
}
}
}
return list;
}
}