JDBC连接Oracle

jdbc连接 oracle

依赖

<dependency>
	<groupId>com.oracle.database.jdbc</groupId>
	<artifactId>ojdbc6</artifactId>
	<version>11.2.0.4</version>
</dependency>

Oracle数据库的 uri

格式一: Oracle JDBC Thin using a ServiceName:

jdbc:oracle:thin:@//<host>:<port>/<service_name> 
Example: jdbc:oracle:thin:@//192.168.2.1:1521/XE

• 注意这里的格式，@后面有//, 这是与使用SID的主要区别。
• 这种格式是Oracle 推荐的格式，因为对于集群来说，每个节点的SID 是不一样的，但是SERVICE_NAME 确可以包含所有节点。

格式二: Oracle JDBC Thin using an SID:

jdbc:oracle:thin:@<host>:<port>:<SID> 
Example: jdbc:oracle:thin:192.168.2.1:1521:X01A 

格式三：jdbc:oracle:thin:@<TNSName>

jdbc:oracle:thin:@(DESCRIPTION_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)(DESCRIPTION=(CONNECT_TIMEOUT=3)(RETRY_COUNT=2)(ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)(ADDRESS=(PROTOCOL=TCP)(HOST=10.XXXX)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=10.XXXX)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ompdb_s1))))

JDBCUtils

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @author zhaokuii11@163.com
 * @create 2021-11-22 17:53
 * @Description
 */
public class JDBCUtils {
    private static Connection conn;

   static  {
        try {
            String url = "jdbc:oracle:thin:@//192.168.217.64:1521/orcl";
            String driverClass = "oracle.jdbc.driver.OracleDriver";
            String user = "scott";
            String password = "scott";
            //注册驱动
            Class.forName(driverClass);
            //获取连接
            conn = DriverManager.getConnection(url, user, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() {
        return conn;
    }
}

测试

    public static void main(String[] args) throws SQLException {
        //获取连接对象
        Connection connect = JDBCUtils.getConnection();
        //准备 sql模板
        String sql = "select * from tab_student where s_id=?";
        //获取预编译对象
        PreparedStatement ps = connect.prepareStatement(sql);
        //给占位符赋值
        ps.setInt(1, 1);
        //执行 execute方法 获取结果集
        ResultSet rs = ps.executeQuery();
        //处理结果集
        while (rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            int age = rs.getInt(3);
            Date birthday = rs.getDate(4);
            String address = rs.getString(5);
            System.out.println("id:" + id + " name:" + name
                    + " age:" + age + " birthday:"
                    + birthday + " address:" + address);

        }
        //关闭连接
        rs.close();
        ps.close();
        connect.close();
    }

//控制台
id:1 name:aa age21 birthday:2021-11-15 address:河南

通过 jdbc调用存储过程/函数

• 调用存储过程使用的是 PreparedStatement的子接口 CallableStatement

存储过程

• 格式

  {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}  --调用function
  

函数

• 格式

  {call <procedure-name>[(<arg1>,<arg2>, ...)]}     --调用procedure
  

代码

package com.zhiyou100;

import java.sql.*;

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

/**
 * @author zhaokuii11@163.com
 * @create 2021-11-19 20:11
 * @Description
 */
public class TestJDBC {
    private Connection conn = null;
    private CallableStatement call = null;

    @Before//获取
    public void init() {
        conn = JDBCUtils.getConnection();
    }


    /**
     * 存储过程
     * 	create or replace procedure
     * 	   pro_1(a in int,b out int, c in out int)
     * 	as
     * 	begin
     * 	  b:=a+1;
     * 	  c:=c+a;
     * 	end;
     * @throws Exception
     */
    @Test
    public void procedure() throws Exception {
        //获取 CallableStatement
        //sql模板：{call <procedure-name>[(<arg1>,<arg2>, ...)]}
        call = conn.prepareCall("{call pro_1(?,?,?)}");
        //给占位符赋值
        //给第一个参数 in 模式的参数赋值
        call.setInt(1,5);
        //给第二个参数 out 模式的参数 指定第二个 out参数的类型
        call.registerOutParameter(2, Types.INTEGER);
        //给第三个参数 in out 模式的参数
        call.setInt(3,6);//给第三个参数赋值
        call.registerOutParameter(3, Types.INTEGER);//指定为out模式
        //执行存储过程
        System.out.println(call.execute());//false
        //处理结果集
        System.out.println("b = "+call.getInt(2));//6
        System.out.println("c = "+call.getInt(3));//11
    }

    /**
     * 存储函数
     * 	create or replace function
     * 	   fun_1(a int,b float)
     * 	return float
     * 	as
     * 	begin
     * 	   return a+b;
     * 	end;
     * 	第一种：
     * 	@throws SQLException
     */
    @Test
    public void function() throws SQLException {
        //获取callablestattement
        //sql模板：{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}  调用function
        call=conn.prepareCall("{?= call fun_1(?,?)}");
        //给占位符赋值：function的返回值就是 procedure的out模式的参数，function的参数就是 procedure的in模式的参数
        call.registerOutParameter(1, Types.FLOAT);
        call.setInt(2,5);
        call.setInt(3,6);
        //执行函数
        System.out.println(call.execute());//false
        //处理结果集
        System.out.println("float = "+call.getFloat(1));//11.0
    }

    /**
     * 第二种：
     * 使用 prepareStatement 执行 存储函数
     * @throws SQLException
     */
    @Test
    public void StatementFunction()throws SQLException{
        //获取callablestattement
        //sql模板：{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}  调用function
        PreparedStatement ps=conn.prepareStatement("select fun_1(?,?) fn from dual");
        ps.setInt(1,5);
        ps.setInt(2,6);
        ResultSet set=ps.executeQuery();
        if(set.next()){
            //给 fun_1 函数起了一个别名
            System.out.println("fn = "+set.getFloat("fn"));//11
        }
        ps.close();
    }


    @After//关闭连接
    public void destroy() {
        if (conn!=null)
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        if (call!=null)
            try {
                call.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
    }
}