jdbc批量更新数据库中的数据 [调用ojdb14.jar]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.ws.smpweb.common.util.AesUtilPublic;

public class UpdateDataOfUserRecomRela {

//测试链接 上生产之前需要改一下
public static final String url = “jdbc:oracle:thin:@199.209.59.89:1521:testdb”;
public static final String user = “user”;
public static final String password = “password”;

private static final Logger logger = LoggerFactory.getLogger(UpdateDataOfUserRecomRela.class);

public static void main(String[] args){

//入参一 每批次更新数据的条数
int batchSize = Integer.parseInt(args[0]);
//入参二 总共更新数据的条数
int totalSize = Integer.parseInt(args[1]);
// 测试数据
// int batchSize = 14;
// int totalSize = 200;

int countSize = 0;
int begin = 1;
int end = batchSize;

Connection connection = null;

try{

// 建立连接
connection = connectOracle();

while (countSize < totalSize) {

// 1.去数据库里面查询当前批次的客户名称、证件号码、手机号码的信息,获取一批数据待加密
List<Map<String, Object>> resultList = selectData(begin, end, connection);
logger.info("查到本批需要更新的数据有：" + resultList.size()+"条");

if (CollectionUtils.isEmpty(resultList)) {

 logger.info("查到本批需要更新的数据为空，更新结束！");
 return;

}

// 2.拿着这batchSize 条数据去加密
List<Map<String, Object>> encryptList=encryptData(resultList);
logger.info("查到本批需要更新的加密数据有：" + encryptList.size()+"条");

// 3.更新数据库
int[] rows= updateData(encryptList,connection);

int length=rows.length;
int errorCount=0;
for(int i=0;i<length;i++){

 if(rows[i]==0){
 
  logger.error("更新失败,id={},sno={},userName={},idNum={},phoneNum={}",
    encryptList.get(i).get("id"),
    encryptList.get(i).get("sno"),
    encryptList.get(i).get("apply_name"),
    encryptList.get(i).get("apply_id_num"),
    encryptList.get(i).get("apply_phone_num")
    );
 
  errorCount++;
 
 }

}

logger.error("本批次数据更新完成，共更新数据"+length+"条,成功更新"+(length-errorCount)+"条,失败"+errorCount+"条。");

countSize+=batchSize;

}

}catch(Exception e) {

logger.error(“发生异常，异常如下:{}”, e);

}finally{

try {

if (connection != null) {

 connection.close();

}

logger.info("数据库已关闭");

} catch (Exception e) {

logger.error("关闭数据库，释放资源时发生异常，异常如下{}", e);

}

}

logger.info(“更新结束！”);
return;

}

/**

• 加密数据
• @param resultList
• @return
  */
  @SuppressWarnings(“rawtypes”)
  public static List<Map<String, Object>> encryptData(List<Map<String, Object>> resultList){

//生产密钥
String secretykey=“htryherturetyuyuyturyurytuyru”;

List<Map<String, Object>> encryptList = new ArrayList<Map<String, Object>>();

for (Map map : resultList) {

Map<String, Object> mapNew = new HashMap<String, Object>();

String parent_recom_name = AesUtilPublic.Encrypt((String) map.get(“PARENT_RECOM_NAME”),secretykey);
String recom_name = AesUtilPublic.Encrypt((String) map.get(“RECOM_NAME”),secretykey);
String apply_name = AesUtilPublic.Encrypt((String) map.get(“APPLY_NAME”),secretykey);
String apply_id_num = AesUtilPublic.Encrypt((String) map.get(“APPLY_ID_NUM”),secretykey);
String apply_phone_num = AesUtilPublic.Encrypt((String) map.get(“APPLY_PHONE_NUM”),secretykey);

//如果加密失敗 就直接跳过这条数据
if(StringUtils.isEmpty(parent_recom_name)||StringUtils.isEmpty(recom_name)||StringUtils.isEmpty(apply_name)||StringUtils.isEmpty(apply_id_num)||
StringUtils.isEmpty(apply_phone_num)){

continue;

}

mapNew.put(“parent_recom_name”, parent_recom_name);
mapNew.put(“recom_name”, recom_name);
mapNew.put(“apply_name”, apply_name);
mapNew.put(“apply_id_num”, apply_id_num);
mapNew.put(“apply_phone_num”, apply_phone_num);
mapNew.put(“id”, map.get(“ID”));
mapNew.put(“sno”, map.get(“SNO”));
mapNew.put(“encrypted”, 1);

encryptList.add(mapNew);

}

return encryptList;

}

/**

• 批量更新数据
• @param resultList
• @return
  */
  public static int[] updateData(List<Map<String, Object>> encryptList,Connection connection){

Statement statement = null;

int size = encryptList.size();
int[] rows = new int[size];

try {

connection.setAutoCommit(false);

// 实例化预编译语句
statement = (Statement) connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

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

 String updateSql=" update user_recomm_relationship  set parent_recom_name='"+encryptList.get(i).get("parent_recom_name")+"',"+
       "recom_name='"+encryptList.get(i).get("recom_name")+"',"+
       "apply_name='"+encryptList.get(i).get("apply_name")+"',"+
       "apply_id_num='"+encryptList.get(i).get("apply_id_num")+"',"+
       "apply_phone_num='"+encryptList.get(i).get("apply_phone_num")+"',"+
       "encrypted='"+encryptList.get(i).get("encrypted")+"',"+
       "modify_date=sysdate where id="+ encryptList.get(i).get("id");

 //添加到批量执行的sql  
 statement.addBatch(updateSql);        

}

// 批量执行sql
rows = statement.executeBatch();
connection.commit();

} catch (Exception e) {

logger.error(“数据批量更新时发生异常，异常如下：{}”,e);

}finally{

try {

if (statement != null) {

 statement.close();

}

} catch (SQLException e) {

logger.error("关闭statement时发生异常，异常如下{}", e);

}

}

return rows;

}

/**

• 将本批数据从数据库中查出来
• @param begin
• @param end
• @return
  */
  public static List<Map<String, Object>> selectData(int begin, int end,Connection connection) {

Statement statement = null;
ResultSet resultSet = null;
List<Map<String, Object>> resultList =new ArrayList<Map<String, Object>>(end-begin+1);
try {

statement = (Statement) connection.createStatement();

// 查询sql,预编译sql语句
String selectSql = “select id,sno,parent_recom_name, recom_name, apply_name, apply_id_num, apply_phone_num from( select t.* ,rownum as rn from user_recomm_relationship t "
+ " where encrypted=0 and "
+ " parent_recom_name is not null and "
+” recom_name is not null and "
+" apply_name is not null and "
+" apply_id_num is not null and "
+" apply_phone_num is not null "
+" order by id asc )where (rn between "
+ begin + " and " + end + “)”;

//执行查询
resultSet = statement.executeQuery(selectSql);

resultList = resultSetToList(resultSet);

} catch (SQLException e) {

logger.error(“查询数据时发生异常，异常如下:{}”, e);

}finally{

try {
if (resultSet != null) {

 resultSet.close();

}
if (statement != null) {

 statement.close();

}

} catch (SQLException e) {

logger.error("关闭resultSet和statement时发生异常，异常如下{}", e);

}

}

return resultList;

}

/**

• 关闭数据库 释放资源
• @param resultSet
• @param statement
• @param connection
  */
  public static void free(ResultSet resultSet, Statement statement, Connection connection) {

try {

if (resultSet != null) {

resultSet.close();

}
if (statement != null) {

statement.close();

}
if (connection != null) {

connection.close();

}

logger.info(“数据库已关闭”);

} catch (Exception e) {

logger.error(“关闭数据库，释放资源时发生异常，异常如下{}”, e);

}

}

/**

• 连接数据库
• @return
• @throws SQLException
  */
  public static Connection connectOracle() throws SQLException {

Connection connection = null;

try {

Class.forName(“oracle.jdbc.OracleDriver”);
logger.info(“开始连接数据库”);

connection = DriverManager.getConnection(url, user, password);
logger.info(“连接成功”);

} catch (ClassNotFoundException e) {

logger.error(“连接数据库失败，发生异常，异常如下：{}”, e);

}

return connection;

}

/**

• ResultSet转list
• @param resultSet
• @return
• @throws SQLException
  */
  @SuppressWarnings(“unchecked”)
  public static List<Map<String, Object>> resultSetToList(ResultSet resultSet) throws SQLException {

List<Map<String, Object>> results = new ArrayList<Map<String, Object>>();
java.sql.ResultSetMetaData rsmd = resultSet.getMetaData();

int colCount = rsmd.getColumnCount();
List colNameList = new ArrayList();
for (int i = 0; i < colCount; i++) {

colNameList.add(rsmd.getColumnName(i + 1));

}
while (resultSet.next()) {

@SuppressWarnings(“rawtypes”)
Map map = new HashMap<String, Object>();

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

String key = colNameList.get(i);
Object value = resultSet.getString(colNameList.get(i));
map.put(key, value);

}
results.add(map);
}
return results;

}

}