Java中调用数据库的存储过程存储函数和包体

比眉伴天荒 2022-10-01 04:55 156阅读 0赞

1.java连接oracle数据库的jdbc程序

public class JDBCUtils {
        private static String driver = "oracle.jdbc.OracleDriver";
        private static String url = "jdbc:oracle:thin:@192.168.209.130:1521/orcl";
        private static String user = "scott";
        private static String password = "tiger";
    
        static {
            //注册驱动
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(); 
            }
        }
        //连接数据库
        public static Connection getConnection() {
    
            try {
                return DriverManager.getConnection(url, user, password);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return null;
        }
    
        public static void release(Connection conn,Statement stat,ResultSet rs) {
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    conn = null;
                }
            }
            if(stat != null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    stat = null;
                }
            }
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }finally {
                    rs = null;
                }
            }
        }
    }

2.调用存储过程

String sql = "{call queryEmpInformation(?,?,?,?)}";
            Connection conn = null;
            CallableStatement call = null;
    
            try {
                conn = JDBCUtils.getConnection();
                call = conn.prepareCall(sql);
                //对于in参数 赋值
                call.setInt(1, 7839);
                //对于out参数,申明
                call.registerOutParameter(2, OracleTypes.VARCHAR);
                call.registerOutParameter(3, OracleTypes.NUMBER);
                call.registerOutParameter(4, OracleTypes.VARCHAR);
                //执行
                call.execute();
                //取值
                String name = call.getString(2);
                double sal = call.getDouble(3);
                String job = call.getString(4);
                System.out.println(name +'\t' +sal+'\t' +job);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtils.release(conn, call, null);
            }

3.调用存储函数

String sql = "{?= call sumsal(?)}";
    
            Connection conn = null;
            CallableStatement call = null;
    
            try {
                conn = JDBCUtils.getConnection();
                call = conn.prepareCall(sql);
                //对于out参数,申明
                call.registerOutParameter(1, OracleTypes.NUMBER);
                //对于in参数,赋值
                call.setInt(2, 7839);
                //执行
                call.execute();
                //取值
                double sum = call.getDouble(1);
                System.out.println(sum);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtils.release(conn, call, null);
            }

4.调用包体

String sql = "{call mypackage.queryemplist(?,?)}";
            Connection conn = null;
            CallableStatement call = null;
            ResultSet rs = null;
            try {
                conn = JDBCUtils.getConnection();
                call = conn.prepareCall(sql);
                //对于in参数 赋值
                call.setInt(1,30);
                //对于out参数,申明
                call.registerOutParameter(2, OracleTypes.CURSOR);
                //执行
                call.execute();
                //取值
                rs = ((OracleCallableStatement)call).getCursor(2);
                //遍历结果集
                while(rs.next()) {
                    String name = rs.getString("ename");
                    double sal = rs.getDouble("sal");
                    String job = rs.getString("job");
                    System.out.println(name + '\t' +sal +'\t' +job);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                JDBCUtils.release(conn, call, rs);
            }
        }