Oracle 的jdbc方法

package com.swift.jdbc_oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;


public class Oracle_jdbc {
    
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@192.168.189.101:1521:orcl";
    String username = "scott";
    String password = "tiger";
    Connection conn =null;
    PreparedStatement statement = null;
    ResultSet rs = null;
    CallableStatement call = null;
    OracleCallableStatement oraclecall = null;
    
    @Before
    public void init() throws ClassNotFoundException, SQLException {
        Class.forName(driver);
        conn = DriverManager.getConnection(url,username,password);
    }

    //普通查询
    public void test() throws ClassNotFoundException, SQLException {
        
    
        String sql="select * from emp";
        statement = conn.prepareStatement(sql);
        rs = statement.executeQuery();
        while(rs.next()) {
            System.out.println(rs.getInt("empno")+"~"+rs.getString("ename")+"~"+rs.getString("sal")+"~"
            +rs.getString("comm")+"~"+rs.getString("deptno"));
        }
        System.out.println("Test~~~~~~~~~");
    }
    //访问单返回值存储过程create or replace procedure getYearSal(eno in number,yearsal out number)
    //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
    //sql语法格式     {call <procedure-name>[(<arg1>,<arg2>, ...)]}
    public void procedure() throws SQLException {
        String sql="{call getYearSal(?,?)}";
        call = conn.prepareCall(sql);
        //输入的和之前preparestatement一样
        call.setInt(1, 7788);
        //输出的要注册参数是什么类型
        call.registerOutParameter(2,OracleTypes.NUMBER);
        call.execute();
        //要先查询存储过程再输出结果
        double yearsal = call.getDouble(2);
        System.out.println(yearsal);
        
    }
    //访问游标返回值存储过程create or replace procedure getEmps(dno in number,emps out sys_refcursor)
    
    public void procedure2() throws SQLException {
        String sql="{call getEmps(?,?)}";
        call = conn.prepareCall(sql);
        //输入的和之前preparestatement一样
        call.setInt(1, 20);
        //输出的要注册参数是什么类型
        call.registerOutParameter(2,OracleTypes.CURSOR);
        call.execute();
        //要先查询存储过程再输出结果
        Object o = call.getObject(2);
        rs=(ResultSet)o;
        while(rs.next()) {
            System.out.println(rs.getInt("empno")+"~"+rs.getString("ename")+"~"+rs.getString("sal")+"~"
            +rs.getString("comm")+"~"+rs.getString("deptno"));
        }
    }
    //访问游标返回值存储过程(OracleCallableStatement)create or replace procedure getEmps(dno in number,emps out sys_refcursor)
    
    public void procedure3() throws SQLException {
        String sql="{call getEmps(?,?)}";
        oraclecall = (OracleCallableStatement) conn.prepareCall(sql);
        //输入的和之前preparestatement一样
        oraclecall.setInt(1, 20);
        //输出的要注册参数是什么类型
        oraclecall.registerOutParameter(2,OracleTypes.CURSOR);
        oraclecall.execute();
        //要先查询存储过程再输出结果
        rs = oraclecall.getCursor(2);
        while(rs.next()) {
            System.out.println(rs.getInt("empno")+"~"+rs.getString("ename")+"~"+rs.getString("sal")+"~"
                    +rs.getString("comm")+"~"+rs.getString("deptno"));
        }
    }
    //访问单返回值存储函数create or replace function x(eno in number) return number
    //{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
    
    public void function() throws SQLException {
        String sql="{?= call x(?)}";
        call = conn.prepareCall(sql);
        //输入的和之前preparestatement一样
        call.setInt(2, 7788);
        //输出的要注册参数是什么类型
        call.registerOutParameter(1,OracleTypes.NUMBER);
        call.execute();
        //要先查询存储过程再输出结果
        double yearsal = call.getDouble(1);
        System.out.println(yearsal); 
        
    }
    //访问单返回值存储函数create or replace function x(eno in number) return number
    //不用这个格式{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}用存储函数select方法
    @Test
    public void function2() throws SQLException{
        String sql="select x(7788) yearsal from dual";//不能多写一个; 写;会报无效字符异常
        statement=conn.prepareStatement(sql);
        rs= statement.executeQuery();
        while(rs.next()) {
            System.out.println(rs.getInt("yearsal"));
        }
    }
    
    @After
    public void close() throws SQLException {
        if(rs!=null) {
            rs.close();
        }
        if(statement!=null) {
            statement.close();
        }
        if(conn!=null) {
            conn.close();
        }
        
    }

}