jdbc批量调用oracle存储过程 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 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();

// 提交事务

mit();

return recordsEffect;

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

批量插入测试

public static void main(String[] args) {

List 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 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();

// 提交事务

mit();

return recordsEffect;

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}

批量删除

public int[] updateBatch(List 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();

// 提交事务

mit();

return recordsEffect;

} catch (SQLException e) {

e.printStackTrace();

}

return null;

}