执行update语句后返回update更新的结果集
-
故事背景
某天早上,老大叫我去他那讨论一个需求。老大:小王啊,现在有一个这样的需求,我想要执行一个update语句,执行完以后需要返回一个执行结果集。例如执行 update demo set age=age+1 后,正常情况下会返回一个int 数,也就是受影响的行数,现在我不仅需要它返回受影响的行数,还需要返回更新后的age的值。
我:这简单啊,执行完update以后,再select一下不就ok啦!
老大:这不行,因为update完以后,可能有其它线程还会去执行update语句。也就是说,线程1执行update以后,然后线程2也执行了update,接着线程1又来执行select。这样的情况下就会出现脏读的情况。
我:哦!这样啊,这涉及到多线程确实不好搞了。有没有什么函数或是什么SQL语句可以更新完数据以后不仅返回受影响的条数,还把更新的数据给返回出来呢。
老大:这就需要你自己去查阅资料了。
我:我之前也没有听说过这样的函数或是语句,简直是两眼一抹黑。有没有更简单点的办法呢?要是能让update 和select 两个语句实现原子操作那岂不是就解决了脏读的问题啦。
老大:这也是一种解决办法,你可以考虑一下存储过程。
我:得令。
-
解决思路
回来后,我就想到了一次我执行了一个update 语句忘了commit,结果再执行update语句时就一直在执行中。最后查阅资料以后才发现,原来update未提交把表给锁了。是不是可以从锁表入手解决这个问题呢?这样的话,就有个解决方案了。我写一个存储过程,在这个存储过程中先update,然后 select ,再然后commit,最后把select 的结果返回出来。这样的话就能避免多线程下脏读的情况出现。
-
方案验证
首先先创建一个名叫DEMO的表,表字段如下:
在表里面加入三条测试数据:
然后开始写存储过程:
create or replace procedure testDemo(data out sys_refcursor) is
begin
update demo set age=age+1 where id=1 ;
open data for select t.id,t.age,t.name from demo t where id=1 ;
end testDemo;
由于需要先验证不提交是否能锁住表,防止别的线程在select之前执行update,所以先不comit。
执行这个存储过程开始测试;
存储过程返回结果:
数据确实已更新并返回了更新结果,那么它能阻塞别的update吗?那么来执行一个试试。
发现语句一直在等待,显然是被锁住了,看来方案确实可行。然后提交了存储过程的事务后,这个update语句立马就执行了。
我把这个结果和我老大说了,老大又问,你这样阻塞你考虑过性能问题吗?呃呃,光想着解决并发问题,确实忽略了性能问题。不过后来测试发现,发现update锁的只是它更新的数据,也就是说update id 为1的数据,在未提交事务的情况下并不影响别的语句update id 为2的数据,至于select 和insert 那也不会影响。这样的话性能问题应该不大。就不管了。
- 解决问题
方案验证确实可行,那么接下来就按照这个方案解决问题吧。
首先开始写存储过程:
create or replace procedure update_select(update_sql in varchar2,select_sql in varchar2,exception_message out varchar2,data out sys_refcursor,row_number out integer) is
begin
execute immediate update_sql;
row_number := sql%rowcount;
open data for select_sql;
exception_message := '';
commit;
exception
WHEN dup_val_on_index THEN exception_message := '列已经被限制为唯一索引';
WHEN no_data_found THEN exception_message := '未找到记录';
WHEN value_error THEN exception_message := '字符类型转换出错';
WHEN timeout_on_resource THEN exception_message := 'update阻塞,等待超时';
rollback;
end ;
为了方法的灵活调用性,我特地把update 语句和select 语句以参数的形式动态的传入,这样的话该存储过程就能适用于所有的表。为了满足需求,把语句执行异常信息,查询结果集以及受影响的行数以输出参数输出。于是,该存储过程便诞生了。
测试改存储过程是否好使。
先记录一下表数据以便待会验证
执行存储过程:
执行结果:
可以看到异常信息为空,说明语句执行异常,受影响行数为3.然后再来看一下data里面的数据:
和预想的一样。然后开始程序调用这个存储过程
先说一下程序运行环境,我的程序架构是springmvc+hibernate。
dao层代码:
/**
* update 同时返回结果
* @param param Map<String,String> 包含两个参数 updateSql:需要执行的update语句 ; selectSql :需要执行的select 语句
* {"selectSql":"select...","updateSql":"update.."}
* @return List<Map<String,Object>> {"exceptionMessage":"","rowNumber":1,"resultSet":[{},{}]}
*/
public Map<String,Object> updateSelect(final Map<String,String> param) {
// 定义存放结果的结果list
final Map<String, Object> map=new HashMap<String, Object>();
//参数检查
if(param.get("updateSql")==null || "".equals(param.get("updateSql"))||param.get("selectSql")==null || "".equals(param.get("selectSql")))
throw new NullPointerException("参数中必须包含updateSql和selectSql");
getHibernateTemplate().execute(new HibernateCallback<Object>() {
@Override
public Object doInHibernate(Session arg0)
throws HibernateException, SQLException {
getSession().doWork(new Work() {
@Override
public void execute(Connection conn) throws SQLException {
CallableStatement proc = null;
try {
proc = conn.prepareCall("{call UPDATE_SELECT(?,?,?,?,?)}");
// 注意:这里是注册输出参数
proc.registerOutParameter(3, OracleTypes.VARCHAR);//执行异常信息,执行正常返回null
proc.registerOutParameter(4, OracleTypes.CURSOR);//返回结果集
proc.registerOutParameter(5, OracleTypes.INTEGER);//update 影响条数
//传入参数
proc.setString(1, param.get("updateSql"));
proc.setString(2, param.get("selectSql"));
/*proc.setString(1, "update demo set age=age+1");
proc.setString(2, "select t.id,t.age,t.name from demo t");*/
// 执行存储过程
boolean executeResult = proc.execute();
System.out.println("执行结果=====》"+executeResult);
//异常信息
String exceptionMessage=(String) proc.getObject(3);
map.put("exceptionMessage", exceptionMessage==null?"":exceptionMessage);
//更新条数
int rowNumber=(int) proc.getObject(5);
map.put("rowNumber",rowNumber);
//查询结果集
List<Map<String,Object>> resultList=new ArrayList<Map<String,Object>>();
// 获取执行完的存储过程的返回值
ResultSet set=(ResultSet) proc.getObject(4);
//获取列名
ResultSetMetaData metaData = set.getMetaData();
while(set.next()){
Map<String, Object> result = new HashMap<String, Object>();
for(int i=1;i<=metaData.getColumnCount();i++){
String colName=metaData.getColumnName(i);
System.out.println(colName+"="+ set.getObject(colName));
result.put(colName, set.getObject(colName));
}
resultList.add(result);
}
map.put("resultSet",resultList);
} catch (Exception e) {
e.printStackTrace();
} finally {
if (null != proc) {
proc.close();
}
}
}
});
return null;
}
});
return map;
}
在这里偷个懒,就不经过service层来调用dao层了,我就直接在controller层直接调用该方法:
controller 层代码如下
@RequestMapping("/update/select")
@ResponseBody
public Map<String, Object> updateSelect(HttpServletRequest request, HttpServletResponse response){
Map<String,String> map=new HashMap<String, String>();
map.put("selectSql", "select t.id,t.age,t.name from demo t");
map.put("updateSql", "update demo set age=age+1");
return procedureInvokeDao.updateSelect(map);
}
代码就这些,接下来调试代码。
为了验证程序是否已update程序,我们先留个表记录:
然后运行程序:
再来看一下表数据
和预期一样!
- 结语
在解决问题的过程中遇到很多问题,比如写存储过程的过程中,老是写的不对或是运行结果不符合预期;再比如hibernate中如何调用存储过程,参数又是如何传,返回结果集如何取出来之类的问题。解决这些问题,离不开csdn上面各位大神的博客帮忙。小生在此就谢过各位大神了。