ojbc

Java 连接 Oracle 数据库

import oracle.jdbc.OracleTypes;
import org.junit.Test;

import java.sql.*;

public class Demo {
    @Test
    public void test(){
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            // 1.注册驱动
            Class.forName("oracle.jdbc.OracleDriver");

            // 2.获取连接对象
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "long123", "123456");

            // 3. 获取预编译sql 语句的statement对象
            statement = conn.prepareStatement("select * from emp where empno=?");
            //  给?赋值
            statement.setObject(1,7788);
            // 4. 执行sql 语句
            resultSet = statement.executeQuery();
            //5. 对结果进行处理
            while (resultSet.next()){
                System.out.println(resultSet.getString("ename"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 7.释放资源
            try {
                if (resultSet != null){
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (statement != null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

Java 操作存储过程

    /**
     * 调用 存储过程
     * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储函数使用
     * {call <procedure-name>[(<arg1>,<arg2>, ...)]}   调用存储过程使用
     */
    @Test
    public void test2(){
        Connection conn = null;
        CallableStatement  statement = null;

        try {
            // 1.注册驱动
            Class.forName("oracle.jdbc.OracleDriver");
            // 2.获取连接对象
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "long123", "123456");
            // 3. 获取预编译sql 语句的statement对象
             statement = conn.prepareCall("{call p_yearsal(?,?)}");
            //  给?赋值
            statement.setObject(1,7788);
            statement.setObject(2, OracleTypes.NUMBER);
            // 4. 执行sql 语句
            statement.execute();
            //5. 对结果进行处理
            // 获取第二个参数
            System.out.println(statement.getObject(2));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 7.释放资源

            try {
                if (statement != null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Java调用存储函数

    @Test
    public void test3(){
        Connection conn = null;
        CallableStatement  statement = null;

        try {
            // 1.注册驱动
            Class.forName("oracle.jdbc.OracleDriver");
            // 2.获取连接对象
            conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "long123", "123456");
            // 3. 获取预编译sql 语句的statement对象
            statement = conn.prepareCall("{?= call f_yearsal(?)}");
            //  给?赋值
            statement.setObject(2,7788);
            statement.setObject(1,OracleTypes.NUMBER);
            // 4. 执行sql 语句
            statement.execute();
            //5. 对结果进行处理
            // 获取第一个参数

            System.out.println(statement.getObject(1));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            // 7.释放资源

            try {
                if (statement != null){
                    statement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (conn != null){
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }