Oracle java实现基本操作

 导入依赖jar包   注意maven导入会出问题   需要手动导入  步骤:

 1.找到 安装 Oracle的安装文件夹  oracle\product\11.2.0\dbhome_1\jdbc\lib    下cmd 运行:

 mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.1.0 -Dpackaging=jar -Dfile=ojdbc6.jar

  jar包就会自动 下载到你的指定的本地仓库中  

        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0</version>
        </dependency>

 1.普通查询语句 

    @Test
    public void javaoracle() throws Exception {
        1.加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        2.获得连接
        Connection co = DriverManager.getConnection("jdbc:oracle:thin://127.0.0.1:1521:orcl", "wyc", "123");
        3.获得预编译sql
        PreparedStatement pstm = co.prepareStatement("select * from emp where empno=?");
        4.设置编译sql参数
        pstm.setObject(1,7369);
        5.获得结果集
        ResultSet rs = pstm.executeQuery();
        while (rs.next()){
            System.out.println(rs.getString("ename"));
        }}

  2.执行存放过程

  1. 获得 Callable的预编译sql     CallableStatement  pstm =   connection. prepareCal l("{call p_yearsal(?,?)}");

  { call 过程名( 参数...) } 固定格式

2.设置 输出类型的参数类型    pstm.registerOutParameter(  2,    OracleTypes.NUMBER   );

3. 获得输出类型参数   System.out.println(  pstm.getObject(2)   );

    @Test
    public void test2() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin://127.0.0.1:1521:orcl", "wyc", "123");
        获得 Callable的预编译sql 
        CallableStatement pstm = connection.prepareCall("{call p_yearsal(?,?)}");
        //设置参数
        pstm.setObject(1,7369);

        设置 输出类型的参数类型
        pstm.registerOutParameter(2, OracleTypes.NUMBER);
       pstm.execute();
        获得输出类型参数
        System.out.println(pstm.getObject(2));
    }

3.执行存放函数

   1. 获得 Callable的预编译sql   CallableStatement pstm = connection.prepareCall( "{ ?=call f_yearsal(?)} ");

         { ?=call 函数名( 参数...) }   固定格式   ?代表返回值

  2.    设置 返回值参数类型   pstm.registerOutParameter(1, OracleTypes.NUMBER);

 3.获得 返回值    System.out.println(pstm.getObject(1));

    @Test
    public void test3() throws Exception {
        //加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin://127.0.0.1:1521:orcl", "wyc", "123");
        获得 Callable的预编译sql
        CallableStatement pstm = connection.prepareCall("{?=call f_yearsal(?)}");
        //设置参数
        pstm.setObject(2,7369);
        设置 返回值参数类型
        pstm.registerOutParameter(1, OracleTypes.NUMBER);
        pstm.execute();
        获得 返回值
        System.out.println(pstm.getObject(1));
    }