环境准备
基本环境
public void javaCallOracle() throws Exception{
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracelDriver");
//2.得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "joyTop", "joyTop");
//得到预编译的Streament对象
PreparedStatement pstm = connection.prepareStatement("select * from emp where empno = ?");
//给参数赋值
pstm.setObject(1,"7788");
//执行数据库查询操作
ResultSet resultSet = pstm.executeQuery();
//输出结果
while(resultSet.next()){
System.out.println(resultSet.getString("enmae"));
}
connection.close();;
pstm.close();
resultSet.close();
}
存储过程
//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} --存储函数
//{call <procedure-name>[(<arg1>,<arg2>, ...)]} --存储过程
//java调用存储过程
public void javaCallProcedure() throws Exception{
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracelDriver");
//2.得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "joyTop", "joyTop");
//得到预编译的Streament对象
CallableStatement pstm = connection.prepareCall("{call p_yearsal(?,?)}");
//给参数赋值
pstm.setObject(1,"7788");
pstm.registerOutParameter(2,OracleType.NUMBER);
//执行数据库查询操作
pstm.execute();
//输出结果,第二个参数
System.out.println(pstm.getObject(2));
//关流
connection.close();;
pstm.close();
}
存储函数
//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} --存储函数
//{call <procedure-name>[(<arg1>,<arg2>, ...)]} --存储过程
//java调用存储函数
public void javaCallFunction() throws Exception{
//1.加载驱动
Class.forName("oracle.jdbc.driver.OracelDriver");
//2.得到Connection连接
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "joyTop", "joyTop");
//得到预编译的Streament对象
CallableStatement pstm = connection.prepareCall("{? = call f_yearsal(?)}");
//给参数赋值
pstm.setObject(2,"7788");
pstm.registerOutParameter(1,OracleType.NUMBER);
//执行数据库查询操作
pstm.execute();
//输出结果,第1个参数
System.out.println(pstm.getObject(1));
//关流
connection.close();;
pstm.close();
}
pom.xml
<dependencies>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc14</artifactId>
<version>10.2.0.4.0</version>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>