随着信息话的深入，无论是企业还是政府部门都面临一个大数据量的问题。因此对数据的优化就显得尤为重要，这里也重点小结一下数据库的插入和删除的批量操作。对数据的修改一般都是针对一行记录进行的，涉及到大数据量相对比较少。
1、针对删除的批量操作：
基本思路是构造：delete from 表名 where 字段 in (?, ?, ?)
以批量删除物料为例的java实现：
数据访问层代码：这里传入了Connection对象，目的是为了再业务逻辑层处理多个逻辑的时候，保证事务性。因为Oracle默认的是一次提交就执行一次事务。
public void delItem(Connection conn, String[] itemNos) {
//构造：?,?,?
StringBuffer sbString =new StringBuffer();
for (int i=0;i<itemNos.length;i++){
sbString .append("?");
if(i<itemNos.length-1){
sbString.append(",");
}
}
String sql= "delete from t_items where item_no in ( "+ sbString+" ) ";
PreparedStatement pstmt = null;
try{
pstmt = conn.prepareStatement(sql);
//赋值
for(int i =0;i<itemNos.length;i++){
pstmt.setString(i+1, itemNos[i]);
}
pstmt.executeUpdate();
}catch(SQLException e){
e.getStackTrace();
throw new ApplicationException("删除物料失败！");
}finally{
DbUtil.close(pstmt);
}
}
业务逻辑层调用删除的方法：在这里完成事务的管理：开启、提交、回滚和关闭。
public void delItem(String[] itemNos) {
Connection conn = null;
try {
conn = DbUtil.getConnection();
itemDao.delItem(conn, itemNos);
}catch(ApplicationException e) {
throw e;
}finally {
DbUtil.close(conn);
}
}
2、关于事务：
在程序中完成一项功能，往往会需要跨域多个表进行操作，因此正确的保证事务原子性是事务的一个核心之一（另一个重点是事务的隔离级别、事务锁）。
方法一：B层控制事务，将数据库连接传递给D层的方法。
在上面介绍某企业的drp系统的删除物料的功能实现上就已经用到了一种事务：事务的控制在B层，数据库的连接传递给D层的方法，这样就保证了事务的原子性。
方法二：手动控制事务，重载事务的开启、提交、回滚和重置，这样就取消了默认的自动提交。
因为会有很多地方用到事务，所以，完全应该提取出到数据库访问的工具类（DbUtil）中：
/**
* 将事务设置成手动提交
* @param conn
*/
public static void beginTransaction(Connection conn) {
try {
if (conn != null) {
if (conn.getAutoCommit()) {
conn.setAutoCommit(false); //手动提交
}
}
}catch(SQLException e) {}
}
/**
* 提交事务
* @param conn
*/
public static void commitTransaction(Connection conn) {
try {
if (conn != null) {
//如果不是自动处理级别，就说明是手动的提交，所以，就要显示执行提交
if (!conn.getAutoCommit()) {
conn.commit();
}
}
}catch(SQLException e) {}
}
/**
* 回滚事务
* @param conn
*/
public static void rollbackTransaction(Connection conn) {
try {
if (conn != null) {
//如果不是自动提交，就说明是手动的回滚，所以，就要显示执行回滚
if (!conn.getAutoCommit()) {
conn.rollback();
}
}
}catch(SQLException e) {}
}
/**
* 重置事务，如果是自动的，就修改回手动；如果是手动的，改回到原来的自动状态，这里相当于一个开关，把状态恢复到之前的状态。
* @param conn
*/
public static void resetConnection(Connection conn) {
try {
if (conn != null) {
if (conn.getAutoCommit()) {
conn.setAutoCommit(false);
}else {
conn.setAutoCommit(true);
}
}
}catch(SQLException e) {}
}
在添加分销商节点中事务：
业务介绍：分销商和销售终端采用树形结构展示。选中一个分销商，添加子分销商或者为其添加销售终端。如果原来的分销商是叶子节点，那么添加了子分销商或者终端后，就应该修改为非叶子节点。
这里涉及到两个操作，一个是向数据库中添加一条新记录，还要查询原来的节点是否是叶子节点，查询出后，还有可能需要修改是否为叶子字段。所以，这里应该保证这一系列为一个原子操作。
/**
* 添加分销商或区域
* @param clientOrRegion
*/
public void addClientOrRegion(Client clientOrRegion) {
StringBuffer sbSql = new StringBuffer();
sbSql.append("insert into t_client ( ")
.append("id, pid, client_level_id, ")
.append("name, client_id, bank_acct_no, ")
.append("contact_tel, address, zip_code, ")
.append("is_leaf, is_client) ")
.append("values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtil.getConnection();
pstmt = conn.prepareStatement(sbSql.toString());
//手动控制事务提交
DbUtil.beginTransaction(conn);
//取得序号
pstmt.setInt(1, IdGenerator.generate("t_client"));
pstmt.setInt(2, clientOrRegion.getPid());
pstmt.setString(3, clientOrRegion.getClientLevel().getId());
pstmt.setString(4, clientOrRegion.getName());
pstmt.setString(5, clientOrRegion.getClientId());
pstmt.setString(6, clientOrRegion.getBankAcctNo());
pstmt.setString(7, clientOrRegion.getContactTel());
pstmt.setString(8, clientOrRegion.getAddress());
pstmt.setString(9, clientOrRegion.getZipCode());
pstmt.setString(10, clientOrRegion.getIsLeaf());
pstmt.setString(11, clientOrRegion.getIsClient());
//如果事务是自动提交的，不论后面的修改为非叶子的方法，是否能成功执行，这里会提交到数据库，所以，在这个方法中，要将事务修改为手动提交
pstmt.executeUpdate();
Client parentClientOrRegion = findClientOrRegionById(clientOrRegion.getPid());
//如果为叶子
if (Constants.YES.equals(parentClientOrRegion.getIsLeaf())) {
//修改为非叶子
modifyIsLeafField(conn, clientOrRegion.getPid(), Constants.NO);
}
//提交事务
DbUtil.commitTransaction(conn);
}catch(Exception e) {
e.printStackTrace();
//回滚事务
DbUtil.rollbackTransaction(conn);
}finally {
DbUtil.close(pstmt);
DbUtil.resetConnection(conn);
DbUtil.close(conn);
}
}
3、批量添加：
批量添加跟批量删除是有区别的。在批量删除的时候，实质是执行一条sql语句，只不过是将要删除的语句添加到了in后的条件里，还是执行了一次数据库的连接；在批量添加中，是需要执行多条sql语句，在批量删除中使用StringBuffer构造条件语句，使用循环给占位符赋值。但在批量添加中就需要使用到PreparedStatement
的addBatch方法，将一组对象添加到PreparedStatement对象的批处理命令中，对应的要使用到executeBatch方法批量执行。这里也要用的事务的手动提交。
StringBuffer：线程同步，线程安全，但是效率不高；
StringBuilder：没有线程同步，线程不安全，但是效率高。
Connection conn= this.getSession().connection();
//自动提交事物设置为false 不自动提交
conn.setAutoCommit(false);
String sql = "insert into test(username,userpwd) values(?,?)";
//创建PreparedStatement 对象
PreparedStatement pstmt = conn.prepareStatement(sql);
for (int index = 0; index < number; index++) {
pstmt.setString(1,"username");
pstmt.setString(2,"userPwd");
// 将一组对象添加到prepareStatement对象的批处理密令中
pstmt.addBatch();
//每5000条进行事物提交
if (index%5000==0) {
//执行prepareStatement对象中所有的sql语句
pstmt.executeBatch();
//事物提交
conn.commit();
//数据库与缓存同步
this.getSession().flush();
//清空缓存
this.getSession().clear();
//如果连接关闭了 就在创建一个 为什么要这样 原因是 conn.commit()后可能conn被关闭
if (null==conn) {
conn = this.getSession().connection();
}
}
}
//执行与5000去摸不是0的数据
pstmt.executeBatch();
//事物提交
conn.commit();
//释放连接
conn.close();
//释放资源
pstmt.close();
4、数据库的分页查询：
Oracle实现分页查询有三种方法，效率最高的是使用rowid；最慢的是使用分析函数row_number()；最常用的也是最容易理解的效率介于两者之间，对于一般的数据量来说，使用最常用的就足够了。不过这里也就物料查询，列出三种哦方法的实现，并做简单的说明。
1.最常用的：
select * from（select t1.*, rownum rn
from (select * from t_items order by item_no ) t1
where rownum < 10 ）
where rn >= 0;
如果，需要某些字段，需要修改最内层的查询，排序规则也是由最内层决定的。
查询出的rn是Oracle自动分配的，是一个行号，且rownum必须只能使用一次。
上面的语句修改成这样可以执行：
select * from（select t1.*, rownum rn
from (select * from t_items order by item_no ) t1
where rownum < 10 ）
where rownum >= 0;
如果修改成这样就会提示出错：
select * from（select t1.*, rownum rn
from (select * from t_items order by item_no ) t1
where rn < 10 ）
where rownum >= 0;
这样是不会识别rn的。
2.效率最高的：
select *
from t_items
where rowid in (select item_no
from (select rownum rn, item_no
from (select rowid item_no
from t_items
order by item_no desc)
where rownum < 10)
where rn > 0)
order by item_no desc;
如果，我们只需要查询某些字段，那么需要将最外面的*修改即可；中间层的字段，需要在它内层的查询中包含。
3.分析函数：
select *
from (select t.*, row_number() over(order by item_no desc) rk
from t_items t)
where rk < 10
and rk > 0;
查询字段也是由最内层决定的，也就是外层的查询要依赖内层查询的字段。
下面是java中，是最常用的分页方法根据查询条件查询分页查询的sql语句构造：
sbSql.append(" select * ")
.append(" from (")
.append(" select i.*, rownum rn from (")
.append(" select a.item_no, a.item_name, a.spec, a.pattern, a.item_category_id, ")
.append(" b.name as item_category_name, a.item_unit_id, c.name as item_unit_name , a.file_name ")
.append(" from t_items a, t_data_dict b, t_data_dict c ")
.append(" where a.item_category_id=b.id and a.item_unit_id=c.id ");
if (condation != null && !"".equals(condation)) {
sbSql.append(" and (a.item_no like '" + condation + "%' or a.item_name like '" + condation + "%') ");
}
sbSql.append(" order by a.item_no")
.append(") i where rownum<=? ")
.append(") ")
.append("where rn >? ");
System.out.println("sql=" + sbSql.toString());
5、数据库锁：for update语句等同于java的synchronized。两者的差别一个是利用数据库锁一个是使用java的锁。使用java锁更容易控制锁的粒度，可以锁某代码段，也可以对某个类上锁，如果是针对静态变量的锁，其实质也是对静态类的锁。
用一个手动维护的自增id来说明。因为维护这个id需要所有用户的同步，所以，这里一定是一个静态的线程安全的。
使用数据库的for update悲观锁实现：
/**
* 根据表名生成该表的序列
* @param tableName
* @return 返回生成的序列
*/
public static int generate(String tableName) {
//使用数据库的悲观锁for update
String sql = "select value from t_table_id where table_name=? for update";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int value = 0;
try {
conn = DbUtil.getConnection();
//开始事务
DbUtil.beginTransaction(conn);
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, tableName);
rs = pstmt.executeQuery();
if (!rs.next()) {
throw new RuntimeException();
}
value = rs.getInt("value");
//自加
value++;
modifyValueField(conn, tableName, value);
//提交事务
DbUtil.commitTransaction(conn);
}catch(Exception e) {
e.printStackTrace();
//回滚事务
DbUtil.rollbackTransaction(conn);
throw new RuntimeException();
}finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
//重置Connection的状态
DbUtil.resetConnection(conn);
DbUtil.close(conn);
}
return value;
}
使用java的锁实现：
public static synchronized int generate(String tableName) {
String sql = "select value from t_table_id where table_name=? ";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int value = 0;
try {
conn = DbUtil.getConnection();
//开始事务
DbUtil.beginTransaction(conn);
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, tableName);
rs = pstmt.executeQuery();
if (!rs.next()) {
throw new RuntimeException();
}
value = rs.getInt("value");
//自加
value++;
modifyValueField(conn, tableName, value);
//提交事务
DbUtil.commitTransaction(conn);
}catch(Exception e) {
e.printStackTrace();
//回滚事务
DbUtil.rollbackTransaction(conn);
throw new RuntimeException();
}finally {
DbUtil.close(rs);
DbUtil.close(pstmt);
//重置Connection的状态
DbUtil.resetConnection(conn);
DbUtil.close(conn);
}
return value;
}
除了在方法上使用锁，还可以在某些代码段上使用锁，这样可以更好地控制，锁的粒度，提高系统性能。使用synchronized(this) {}将代码段锁住。
静态方法加锁是给类加的，成员方法加的锁是对象的，因为只有实例化后才可以访问方法。