1.Oracle连接
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 final String URL="jdbc:oracle:thin:@192.168.31.31:1521/orcl";
private static final String DRIVER="oracle.jdbc.OracleDriver";
private static final String USERNAME="root";
private static final String PASSWORD="123";
static{
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
Connection conn;
try {
conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void release(Connection conn, Statement st, ResultSet rs){
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
st = null;
}
}
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
} finally {
rs = null;
}
}
}
}
2.Oracle测试用例
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
public class OracleTest {
/*
create or replace procedure queryEmpInformation(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
*/
@Test
public void testProcedure(){
//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
String sql = "{call queryEmpInformation(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try {
conn = JDBCUtils.getConnection();
call = conn.prepareCall(sql);
//对于in参数,赋值
call.setInt(1, 7839);
//对于out参数,声明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//执行
call.execute();
//输出
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println("name:"+name+"\tsal:"+sal+"\tjob:"+job);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
/*
create or replace function queryEmpIncome(eno in number)
return number
*/
@Test
public void testFunction(){
//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
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 (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(conn, call, null);
}
}
@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);
//对于in参数,赋值
call.setInt(1,20);
//对于out参数,申明
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行
call.execute();
//取出结果
rs = ((OracleCallableStatement)call).getCursor(2);
while(rs.next()){
//取出一个员工
String name = rs.getString("ename");
double sal = rs.getDouble("sal");
System.out.println(name+"\t"+sal);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCUtils.release(conn, call, rs);
}
}
}