package com.huaxia.idc.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
public class BatchOperateDB {
private String create_room_sql_by_resroom = "insert into res_idc_room(roomid,roomname,roomalias,createtime) values (seq_netdevice_room.nextval,?,?,?)";
private String batchModifyResIdcPortNoSQL = "update res_idc_port t set t.port_no=? where t.port_id=?";
private static final Logger logger = Logger.getLogger(BatchOperateDB.class);
/**
* jdbc批量创建设备机房列表.
*
* @param roomLst
*/
public void batchCreateRoomByResRoom(List<Object> roomLst) {
Connection conn = null;
PreparedStatement psmt = null;
try {
conn = getCon();
conn.setAutoCommit(false);
psmt = conn.prepareStatement(create_room_sql_by_resroom);
int i = 0;
for (Object room : roomLst) {
psmt.setString(1, "");
psmt.setString(2, "");
psmt.setTimestamp(3, new Timestamp(new Date().getTime()));
psmt.addBatch();
if (++i % 30 == 0) {// 一次提交30个
psmt.executeBatch();
conn.commit();
}
}
if (roomLst.size() % 30 != 0) {
psmt.executeBatch();
conn.commit();
}
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new RuntimeException(e);
} finally {
closeCon(null, psmt, conn);
}
}
/**
* jdbc批量更新资源端口表 -- PORT_NO名称.
*
* @param resPortList
*/
public void batchModifyResIdcPortNo(List<Object> resPortList) {
Connection conn = null;
PreparedStatement psmt = null;
try {
conn = getCon();
conn.setAutoCommit(false);
psmt = conn.prepareStatement(batchModifyResIdcPortNoSQL);
int i = 0;
for (Object port : resPortList) {
psmt.setString(1, "");
psmt.setLong(2, 3l);
psmt.addBatch();
if (++i % 30 == 0) {
psmt.executeBatch();
conn.commit();
}
}
if (resPortList.size() % 30 != 0) {
psmt.executeBatch();
conn.commit();
}
} catch (SQLException e) {
e.printStackTrace();
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
throw new RuntimeException(e);
} finally {
closeCon(null, psmt, conn);
}
}
/**
* hibernate批量更新
*
* @param datas
* @param sessionFactory
*/
public void updateVmwareBasic(List<Object> datas,
SessionFactory sessionFactory) {
String sql = "update RES_IDC_DATA_CENTER c set c.BASIC_UPDATE_TIME=:basicUpdateTime where c.DC_NAME=:dcName";
Session session = null;
Transaction transaction = null;
try {
int i = 0;
session = sessionFactory.openSession();
transaction = session.beginTransaction();
for (Object data : datas) {
Query query = session.createSQLQuery(sql);
query.setParameter("basicUpdateTime",
new Timestamp(System.currentTimeMillis()));
query.setParameter("dcName", "");
int count = query.executeUpdate();
i++;
if (i % 30 == 0 || i == datas.size()) {
session.flush();
}
}
transaction.commit();
} catch (Exception e) {
transaction.rollback();
logger.error(e.getMessage());
} finally {
session.close();
}
}
/**
* hibernate 批量插入
*
* @param datas
* @param sessionFactory
*/
public void insertVmwareBasic(List<Object> datas,
SessionFactory sessionFactory) {
Session session = null;
Transaction transaction = null;
try {
int i = 0;
session = sessionFactory.openSession();
transaction = session.beginTransaction();
for (Object data : datas) {
session.save(data);
i++;
if (i % 30 == 0 || i == datas.size()) {
session.flush();
session.clear();
}
}
transaction.commit();
} catch (Exception e) {
transaction.rollback(); // 回滚事物
logger.error(e.getMessage());
} finally {
session.close(); // 关闭session
}
}
/**
* hibernate批量删除
*
* @param datas
* @param sessionFactory
*/
public void deletetVmwareBasic(SessionFactory sessionFactory) {
Session session = null;
Transaction transaction = null;
try {
session = sessionFactory.openSession();
transaction = session.beginTransaction();
String hqlDelete = "delete Customer c where c.name = :oldName";
int deletedResult = session.createQuery(hqlDelete)
.setString("oldName", "Tom").executeUpdate();
transaction.commit();
logger.info("delete result is:" + deletedResult);
} catch (Exception e) {
transaction.rollback(); // 回滚事物
logger.error(e.getMessage());
} finally {
session.close(); // 关闭session
}
}
/**
* hibernate单条插入
*
* @param session
* @param info
* @return
*/
@SuppressWarnings("finally")
public Integer addMsg(Session session, String info) {
Transaction transaction = session.beginTransaction();
Integer id = 0;
try {
Query query = session
.createSQLQuery("insert into res_idc_room (id,info,add_time) values(:id,:info,sysdate)");
query.setParameter("info", info);
query.setParameter("id", id);
query.executeUpdate();
transaction.commit();
} catch (Exception e) {
transaction.rollback();
logger.error(e.toString());
} finally {
session.close();
return id;
}
}
/**
* 获取连接
*
* @return
* @throws SQLException
*/
private Connection getCon() throws SQLException {
Connection connection = null;
String driverClass = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@127.0.0.1:1521:idc";
String username = "idc";
String password = "idc";
try {
Class.forName(driverClass);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
connection = DriverManager.getConnection(url, username, password);
return connection;
}
/**
* 关闭连接
*
* @param rs
* @param pStmt1
* @param con
*/
public static void closeCon(ResultSet rs, PreparedStatement pStmt,
Connection con) {
try {
if (rs != null)
rs.close();
} catch (Exception e) {
logger.error("close rs,error:", e);
e.printStackTrace();
}
try {
if (pStmt != null)
pStmt.close();
} catch (Exception e) {
logger.error("close pStmt,error:", e);
e.printStackTrace();
}
try {
if (con != null)
con.close();
} catch (Exception e) {
logger.error("close con,error:", e);
e.printStackTrace();
}
}
}
springboot hibernate 对数据库基本操作
最新推荐文章于 2024-09-07 15:07:53 发布