hibernate批处理、sql存在则更新不存在插入

来源：互联网发布：酒精刻录软件编辑：程序博客网时间：2024/05/29 08:20

hibernate中可以从query对象获取connection直接操作数据库

public void setStatus(String arrMsgID[]) {        //sql语句        String updateSql = "UPDATE tb_msgboard SET status ='1' WHERE msgID =?";        //通过session获取事务 和spring整合之后这步并不需要        Transaction ts = session.beginTransaction();        session.doWork(             //实现work接口，work接口中有一个execute方法，connection就是这个方法的参数，也就是在这里获得jdbc的connection            new Work(){                 @Override                public void execute(java.sql.Connection connection) throws SQLException {                    try {                        PreparedStatement stmt = connection.prepareStatement(updateSql);                        //这里进行批量操作                        for (int i = 0; i < arrMsgID.length; i++) {                            stmt.setString(1, arrMsgID[i]);                            stmt.addBatch(); // 添加到批处理命令                        }                        stmt.executeBatch();                        ts.commit();                    } catch (Exception e) {                        e.printStackTrace();                        ts.rollback();                        HibernateUtil.closeSession(session);                    }                }             });         HibernateUtil.closeSession(session);    }public void execute(Connection conn) throws SQLException {PreparedStatement ps =    conn.prepareStatement(insertSql.toString());int count = 0;for (PickupDepotResult e : results) {//...设置参数ps.setObject(i++,converDateTimeStamp(e.getCreatedTm()));ps.addBatch();count++;if (count % 1000 == 0) {// 1000条提交一次executeBatch(ps);}}long startTm=System.currentTimeMillis();executeBatch(ps);long endTm=System.currentTimeMillis();logger.debug("执行sql："+insertSql.toString()+"总耗时："+(endTm-startTm)/1000);}private void executeBatch(PreparedStatement ps) {try {ps.executeBatch();ps.clearBatch();} catch (Exception e) {e.printStackTrace();logger.error("ps:", ps);logger.error("executeBatch error," + e.getMessage(), e);}}

sql方式根据 UNIQUE KEY 存在更新不存在插入

insertSql.append("(");insertSql.append("created_tm,batch_code,operate_zone_code,batch_tm,last_indepot_tm,begin_tm,"+ "end_tm,work_day,batch_no,pickphase_begin_tm,pickphase_end_tm,version_dt,status,update_version_dt,update_tm");insertSql.append(") VALUES (");insertSql.append("?,?,?,?,?,");insertSql.append("?,?,?,?,?,");insertSql.append("?,?,?,?,?)");insertSql.append(" ON DUPLICATE KEY UPDATE ");insertSql.append(" created_tm =? ,batch_code =?,operate_zone_code =?,batch_tm= ?,last_indepot_tm=?,begin_tm=?,"+ "end_tm=?,work_day=?,batch_no=?,pickphase_begin_tm=?,pickphase_end_tm=?,version_dt=?,status=?,update_version_dt=?,update_tm=?");建表加上 UNIQUE KEY `pickup_depot_result_unique` (`batch_code`,`batch_tm`)

阅读全文

0 0