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;
}