oracle学习笔记（二十三）——JDBC调用存储过程以及批量操作

jdbc调用存储过程

使用并获得out模式的参数返回值

复制//存储过程为sum_sal(deptno department.deptno%type,sum in out number) CallableStatement cs =conn.prepareCall("{call sum_sal(?,?)}"); cs.setInteger(1,7879); cs.setDouble(2,0.0);//第二个传什么都无所谓，因为第二个参数是in out模式，是作为输出的 cs.registerOutParameter(2,java.sql.Types.Double,2)；//最后那个参数是保留小数点2位 cs.excute();//执行会返回一个boolean结果 //获得结果，获取第二个参数 double result = cs.getDouble(2);

获得oracle返回的结果集

复制//存储过程为list(result_set out sys_refcursor, which in number) CallableStatement cs =conn.prepareCall("{call list(?,?)}"); cs.setInteger(2,1); cs.registerOutParameter(1,racleTypes.CURSOR); cs.execute(); //获得结果集 ResultSet rs = (ResultSet)cs.getObject(1);

批量操作

批量插入

People表中只有两列，id和name ，还有对应的一个实体类People批量操作应该放到事务里进行，因为它会存在某条语句执行失败的情况。

复制public int[] insetBatch(List<People> list) { try (Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement("insert into PEOPLE values (?,?)"); ) { // 关闭事务自动提交，手动提交 connection.setAutoCommit(false); //从list中取出数据 for (People people : list) { ps.setInt(1, people.getId()); ps.setString(2, people.getName()); //加入到指语句组中 ps.addBatch(); } int[] recordsEffect = ps.executeBatch(); // 提交事务 connection.commit(); return recordsEffect; } catch (SQLException e) { e.printStackTrace(); } return null; }

批量插入测试

复制public static void main(String[] args) { List<People> list = new ArrayList<>(); int id = 1; list.add(new People(id++, "james")); list.add(new People(id++, "andy")); list.add(new People(id++, "jack")); list.add(new People(id++, "john")); list.add(new People(id++, "scott")); list.add(new People(id++, "jassica")); list.add(new People(id++, "jerry")); list.add(new People(id++, "marry")); list.add(new People(id++, "alex")); int[] ints = new BatchTest().insetBatch(list); System.out.println(Arrays.toString(ints)); }

批量更新

复制public int[] updateBatch(List<People> list) { try (Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement("undate people set name=? where id=?"); ) { // 关闭事务自动提交，手动提交 connection.setAutoCommit(false); //从list中取出数据 for (People people : list) { ps.setInt(1, people.getId()); ps.setString(2, people.getName()); //加入到指语句组中 ps.addBatch(); } int[] recordsEffect = ps.executeBatch(); // 提交事务 connection.commit(); return recordsEffect; } catch (SQLException e) { e.printStackTrace(); } return null; }

批量删除

复制public int[] updateBatch(List<People> list) { try (Connection connection = JdbcUtil.getConnection(); PreparedStatement ps = connection.prepareStatement("delete people where id=?"); ) { // 关闭事务自动提交，手动提交 connection.setAutoCommit(false); //从list中取出数据 for (People people : list) { ps.setInt(1, people.getId()); //加入到指语句组中 ps.addBatch(); } int[] recordsEffect = ps.executeBatch(); // 提交事务 connection.commit(); return recordsEffect; } catch (SQLException e) { e.printStackTrace(); } return null; }

转载于:https://www.cnblogs.com/chaoyang123/p/11549632.html

相关资源：Oracle存储过程调用bat批处理脚本程序