----------- android培训、java培训、java学习型技术博客、期待与您交流! ------------
1、连接数据库
Package cs.Java.Oracle.Test;
import java.sql.*;
/**
* @ Class name: ExecutSinle
* @ Class role: Execute single SQL
* @ Author: zhanggeng
* @ Date: 2013-1-29
*/
Public class ConnOraDB
{
private final String DBDRIVER = "oracle.jdbc.driver.OracleDriver" ;// 驱动程序private final String DBURL = "jdbc:oracle:thin:@192.168.102.16:1521:Test" ;//数据库地址
private final String DBUSER = "dsz779" ; //数据库登录用户名
private final String DBPASSWORD = "123456" ; //数据库登录密码
private Connection conn = null;
public ConnOraDB()
{
/**
* @ initial role: 完成对数据库的连接
*/
try
{
//加载数据库驱动程序
Class.forName(DBDRIVER);
}
catch(Exception e)
{
System.out.println("Can't load dbdriver!-->" + e.getMessage());
}
try
{
//连接到指定的数据库
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD) ;
}
catch(Exception e)
{
System.out.println("Can't connect to the database!-->" + e.getMessage()) ;
}
}
public Connection getConnection()
{
/**
* 用来使其它类调用取得数据库连接
* @return 数据库连接对象
*/
return this.conn;
}
}
2、单句执行函数
Package cs.Java.Oracle.Test;
import java.sql.*;
public class ExecutSingle
{
/**
* @ Class name: ExecutSinle
* @ Class role: Execute single SQL
* @ Author: zhanggeng
* @ Date: 2013-1-29
*/
Private Connection conn = null;
Private Statement stmt = null;
Private ResultSet rs = null;
Private ResultSetMetaData rsmd = null;
public ExecutSingle ()
{
}
Public void ExecuteQuery(String sql)
{
/**
* 执行单句查询,并显示结果
*/
Integer numCols;
String tempValue;
try
{
//取得数据库的连接
conn = new ConnOraDB().getConnection() ;
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
rtmt= rs.getMetaData();
numCols=rtmt.getColumnCount();
while(rs.next())
{
//System.out.print("编号:" + rs.getInt("customer_id")) ;
//System.out.print("/姓名:" + rs.getString("customer_name")) ;
//System.out.print("/性别:" + rs.getString("customer_sex")) ;
//System.out.println("/电话:" + rs.getString("customer_phone")) ;
for (int i = 1; i <= numCols; i++)
{
tempValue = rs.getString(i);
if(tempValue == null) tempValue = "";
list.add(tempValue);
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
catch (SQLException e) {
e.printStackTrace();
}
}
}
public void ExecuteSQL(String sql)
{
/**
* 执行单句操作
*/
try
{
conn = new ConnOraDB().getConnection() ;
stmt = conn.createStatement();
//rs = stmt.executeQuery(sql);
rs = stmt.executeUpdate(sql);
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
try
{
if (rs != null)
{
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
}
3、存储过程执行
3.1 无返回值的存储过程执行
1、建立存储过程
CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2)
Is
BEGIN
INSERT INTO T_TEST (I_ID,I_NAME) VALUES (PARA1, PARA2);
END TESTA;
2、相应的JAVA程序
Package cs.Java.Oracle.Test;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureOne
{
public TestProcedureOne()
{
}
public static void main(String[] args )
{
Connection conn = null;
//Statement stmt = null;
//ResultSet rs = null;
//CallableStatement cstmt = null;
try
{
conn = new ConnOraDB.getConneciton();
CallableStatement proc = null;
//proc = conn.prepareCall("{ call Test.TESTA(?,?) }");
proc = conn.prepareCall("{ call TESTA(?,?) }");
proc.setString(1, "100"); //参数1设置为“100”
proc.setString(2, "TestOne"); // 参数2设置为“TestOne”
proc.execute();
}
catch (SQLException e)
{
e.printStackTrace();
}
finally
{
try
{
if(rs != null)
{
rs.close();
}
if(stmt!=null)
{
stmt.close();
}
if(conn!=null)
{
conn.close();
}
}
catch(Eexeption e)
{
Exit(0);
}
}
}
}
注:调用存储过程时,切勿在call语句的前后使用空格。
3.2 有返回值的存储过程执行
1、存储过程为
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2)
Is
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;
2、JAVA代码
Package cs.Java.Oracle.Test;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureTWO
{
public TestProcedureTWO()
{
}
public static void main(String[] args )
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
CallableStatement proc = null;
try
{
conn = new ConnOraDB.getConnection();
proc = conn.prepareCall("{ call TESTB(?,?) }");
proc.setString(1, "100");
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String testPrint = proc.getString(2);
System.out.println(" testPrint = " + testPrint);
}
catch(SQLException ex2)
{
ex2.printStackTrace();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try
{
If (conn != null)
{
conn.close();
}
If ()
}
catch(Exception e)
{
e.printStackTrace();
}
}
}
}
注:这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
3.3 返回游标
1、存储过程为
CREATE OR REPLACE PACKAGE TESTPACKAGE
IS
TYPE Test_CURSOR IS REF CURSOR;
procedure TESTC(cur_ref out Test_CURSOR);
end TESTPACKAGE;
create or replace package body TESTPACKAGE
is
procedure TESTC(cur_ref out Test_CURSOR)
is
begin
OPEN cur_ref FOR
SELECT * FROM T_TEST;
end TESTC;
END TESTPACKAGE;
2、JAVA代码
Package cs.Java.Oracle.Test;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureTHREE
{
public TestProcedureTHREE()
{
}
Public static void main(String [] args)
{
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try
{
conn = new ConnOraDB.getConnection();
CallableStatement proc = null;
proc = conn.prepareCall("{call testc(?)}");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
}
}
catch(SQLException ex2)
{
ex2.printStrackTrace();
}
catch(Exception ex2)
{
ex2.printStrackTrace();
}
finally
{
try
{
If (conn ! = null)
{
conn.close();
}
}
catch()
{
}
}
}
}
注:在执行前一定要先把oracle的驱动包放到class路径里。