package demo.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver="oracle.jdbc.OracleDriver";
private static String url="jdbc:oracle:thin:@localhost:1521:orcl";
private static String user="scott";
private static String password="tiger";
static{
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnection(){
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
/*
* 执行java程序
* java -Xms100M -Xmx200M HelloWorld
*/
public static void release(Connection conn,Statement stmt,ResultSet rs){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs=null;//垃圾回收：是否可以通过代码干预垃圾回收？
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs=null;//垃圾回收：是否可以通过代码干预垃圾回收？
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally{
rs=null;//垃圾回收：是否可以通过代码干预垃圾回收？
}
}
}
Oracle学习(十二)：存储过程/存储函数(二)
ng(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name);
System.out.println(sal);
System.out.println(job);
}catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
/*
* create or replace
function queryEmpIncome(eno in number)
return number
*/
@Test
public void testFunction(){
//存储函数例子
//{ = call [(,, ...)]}
String sql = "{ =call queryEmpIncome( )}";
Connection conn = null;
CallableStatement call = null;
try{
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.registerOutParameter(1, OracleTypes.NUMBER);
call.setInt(2, 7839);
//执行
call.execute();
//取出年收入
double income = call.getDouble(1);
System.out.println(income);
}catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, null);
}
}
/*
* 问题：
* 1. 光标是否被关？:是，结构集关掉后光标就关掉了。
* 2. 是否能在MYSQL上执行？：不能
*/
@Test
public void testCursor(){
String sql = "{call MYPACKAGE.queryEmpList( , )}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs =null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
call.setInt(1, 10);
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行
call.execute();
//取出集合
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
String name = rs.getString("ename");
String job = rs.getString("job");
System.out.println(name+"的职位是"+job);
}
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtils.release(conn, call, rs);
}
}
}
3.用到的JDBC类