Java操作Oracle的基本方式总结.docx
《Java操作Oracle的基本方式总结.docx》由会员分享,可在线阅读,更多相关《Java操作Oracle的基本方式总结.docx(12页珍藏版)》请在冰豆网上搜索。
Java操作Oracle的基本方式总结
1、连接数据库
Packagedusuzhong.Java.Oracle.Test;
importjava.sql.*;
PublicclassConnOraDB
{
/**
*@Classname:
ConnOraDB
*@Classrole:
Connecttoaoracledatabase
*@Author:
Dusuzhong
*@Date:
2010-5-4
*/
privatefinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//驱动程序
privatefinalStringDBURL="jdbc:
oracle:
thin:
@192.168.102.16:
1521:
Test";//数据库地址
privatefinalStringDBUSER="dsz779";//数据库登录用户名
privatefinalStringDBPASSWORD="123456";//数据库登录密码
privateConnectionconn=null;
publicConnOraDB()
{
/**
*@initialrole:
完成对数据库的连接
*/
try
{
//加载数据库驱动程序
Class.forName(DBDRIVER);
}
catch(Exceptione)
{
System.out.println("Can'tloaddbdriver!
-->"+e.getMessage());
}
try
{
//连接到指定的数据库
conn=DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);
}
catch(Exceptione)
{
System.out.println("Can'tconnecttothedatabase!
-->"+e.getMessage());
}
}
publicConnectiongetConnection()
{
/**
*用来使其它类调用取得数据库连接
*@return数据库连接对象
*/
returnthis.conn;
}
}
2、单句执行函数
Packagedusuzhong.Java.Oracle.Test;
importjava.sql.*;
publicclassExecutSingle
{
/**
*@Classname:
ExecutSinle
*@Classrole:
ExecutesingleSQL
*@Author:
Dusuzhong
*@Date:
2010-5-4
*/
PrivateConnectionconn=null;
PrivateStatementstmt=null;
PrivateResultSetrs=null;
PrivateResultSetMetaDatarsmd=null;
publicExecutSingle()
{
}
PublicvoidExecuteQuery(Stringsql)
{
/**
*执行单句查询,并显示结果
*/
IntegernumCols;
StringtempValue;
try
{
//取得数据库的连接
conn=newConnOraDB().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(inti=1;i<=numCols;i++)
{
tempValue=rs.getString(i);
if(tempValue==null)tempValue="";
list.add(tempValue);
}
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try{
if(rs!
=null){
rs.close();
}
if(stmt!
=null){
stmt.close();
}
if(conn!
=null){
conn.close();
}
}
catch(SQLExceptione){
e.printStackTrace();
}
}
}
publicvoidExecuteSQL(Stringsql)
{
/**
*执行单句操作
*/
try
{
conn=newConnOraDB().getConnection();
stmt=conn.createStatement();
//rs=stmt.executeQuery(sql);
rs=stmt.executeUpdate(sql);
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!
=null)
{
rs.close();
}
if(stmt!
=null){
stmt.close();
}
if(conn!
=null){
conn.close();
}
}
catch(SQLExceptione)
{
e.printStackTrace();
}
}
}
}
3、存储过程执行
3.1无返回值的存储过程执行
1、建立存储过程
CREATEORREPLACEPROCEDURETESTA(PARA1INVARCHAR2,PARA2INVARCHAR2)
Is
BEGIN
INSERTINTOT_TEST(I_ID,I_NAME)VALUES(PARA1,PARA2);
ENDTESTA;
2、相应的JAVA程序
Packagedusuzhong.Java.Oracle.Test;
importjava.sql.*;
importjava.io.OutputStream;
importjava.io.Writer;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importoracle.jdbc.driver.*;
publicclassTestProcedureOne
{
publicTestProcedureOne()
{
}
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
//Statementstmt=null;
//ResultSetrs=null;
//CallableStatementcstmt=null;
try
{
conn=newConnOraDB.getConneciton();
CallableStatementproc=null;
//proc=conn.prepareCall("{callTest.TESTA(?
?
)}");
proc=conn.prepareCall("{callTESTA(?
?
)}");
proc.setString(1,"100");//参数1设置为“100”
proc.setString(2,"TestOne");//参数2设置为“TestOne”
proc.execute();
}
catch(SQLExceptione)
{
e.printStackTrace();
}
finally
{
try
{
if(rs!
=null)
{
rs.close();
}
if(stmt!
=null)
{
stmt.close();
}
if(conn!
=null)
{
conn.close();
}
}
catch(Eexeptione)
{
Exit(0);
}
}
}
}
注:
调用存储过程时,切勿在call语句的前后使用空格。
3.2有返回值的存储过程执行
1、存储过程为
CREATEORREPLACEPROCEDURETESTB(PARA1INVARCHAR2,PARA2OUTVARCHAR2)
Is
BEGIN
SELECTINTOPARA2FROMTESTTBWHEREI_ID=PARA1;
ENDTESTB;
2、JAVA代码
Packagedusuzhong.Java.Oracle.Test;
importjava.sql.*;
importjava.io.OutputStream;
importjava.io.Writer;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importoracle.jdbc.driver.*;
publicclassTestProcedureTWO
{
publicTestProcedureTWO()
{
}
publicstaticvoidmain(String[]args)
{
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
CallableStatementproc=null;
try
{
conn=newConnOraDB.getConnection();
proc=conn.prepareCall("{callTESTB(?
?
)}");
proc.setString(1,"100");
proc.registerOutParameter(2,Types.VARCHAR);
proc.execute();
StringtestPrint=proc.getString
(2);
System.out.println("testPrint="+testPrint);
}
catch(SQLExceptionex2)
{
ex2.printStackTrace();
}
catch(Exceptione)
{
e.printStackTrace();
}
finally
{
try
{
If(conn!
=null)
{
conn.close();
}
If()
}
catch(Exceptione)
{
e.printStackTrace();
}
}
}
}
注:
这里的proc.getString
(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString
(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
3.3返回游标
1、存储过程为
CREATEORREPLACEPACKAGETESTPACKAGE
IS
TYPETest_CURSORISREFCURSOR;
procedureTESTC(cur_refoutTest_CURSOR);
endTESTPACKAGE;
createorreplacepackagebodyTESTPACKAGE
is
procedureTESTC(cur_refoutTest_CURSOR)
is
begin
OPENcur_refFOR
SELECT*FROMT_TEST;
endTESTC;
ENDTESTPACKAGE;
2、JAVA代码
Packagedusuzhong.Java.Oracle.Test;
importjava.sql.*;
importjava.io.OutputStream;
importjava.io.Writer;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importoracle.jdbc.driver.*;
publicclassTestProcedureTHREE
{
publicTestProcedureTHREE()
{
}
Publicstaticvoidmain(String[]args)
{
Connectionconn=null;
Statementstmt=null;
ResultSetrs=null;
try
{
conn=newConnOraDB.getConnection();
CallableStatementproc=null;
proc=conn.prepareCall("{calltestc(?
)}");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs=(ResultSet)proc.getObject
(1);
while(rs.next())
{
System.out.println("
"+rs.getString (1)+" | "+rs.getString (2)+" |
");
}
}
catch(SQLExceptionex2)
{
ex2.printStrackTrace();
}
catch(Exceptionex2)
{
ex2.printStrackTrace();
}
finally
{
try
{
If(conn!
=null)
{
conn.close();
}
}
catch()
{
}
}
}
}
注:
在执行前一定要先把oracle的驱动包放到class路径里。