Java操作Oracle的基本方式总结.docx

1、Java操作Oracle的基本方式总结1、 连接数据库Package dusuzhong.Java.Oracle.Test;import java.sql.*;Public class ConnOraDB/* Class name: ConnOraDB* Class role: Connect to a oracle database* Author: Du suzhong* Date: 2010-5-4*/ private final String DBDRIVER = oracle.jdbc.driver.OracleDriver ;/驱动程序 private final String D

2、BURL = 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)

3、System.out.println(Cant load dbdriver!- + e.getMessage(); try /连接到指定的数据库 conn = DriverManager.getConnection(DBURL, DBUSER, DBPASSWORD) ; catch(Exception e) System.out.println(Cant connect to the database!- + e.getMessage() ; public Connection getConnection() /* * 用来使其它类调用取得数据库连接 * return 数据库连接对象 */

4、return this.conn; 2、 单句执行函数Package dusuzhong.Java.Oracle.Test;import java.sql.*;public class ExecutSingle /* Class name: ExecutSinle* Class role: Execute single SQL* Author: Du suzhong* Date: 2010-5-4*/Private Connection conn = null; Private Statement stmt = null; Private ResultSet rs = null; Privat

5、e 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.get

6、ColumnCount(); 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.getSt

7、ring(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) /* * 执行单句操作 *

8、/ 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 (SQLExcepti

9、on e) e.printStackTrace(); 3、 存储过程执行3.1 无返回值的存储过程执行1、建立存储过程CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2) IsBEGININSERT INTO T_TEST (I_ID,I_NAME) VALUES (PARA1, PARA2);END TESTA;2、相应的JAVA程序Package dusuzhong.Java.Oracle.Test;import java.sql.*;import java.io.OutputStream;import

10、 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

11、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();f

12、inallytry 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) IsBEGINSELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;END TE

13、STB;2、JAVA代码Package dusuzhong.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 )Conn

14、ection 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);

15、System.out.println( testPrint = + testPrint);catch(SQLException ex2) ex2.printStackTrace();catch(Exception e) e.printStackTrace(); finallytry If (conn != null) conn.close(); If ()catch(Exception e) e.printStackTrace(); 注：这里的proc.getString(2)中的数值2并非任意的，而是和存储过程中的out列对应的，如果out是在第一个位置，那就是proc.getString(

16、1)，如果是第三个位置，就是proc.getString(3)，当然也可以同时有多个返回值，那就是再多加几个out参数了。3.3 返回游标 1、存储过程为CREATE OR REPLACE PACKAGE TESTPACKAGE ISTYPE Test_CURSOR IS REF CURSOR;procedure TESTC(cur_ref out Test_CURSOR);end TESTPACKAGE;create or replace package body TESTPACKAGE isprocedure TESTC(cur_ref out Test_CURSOR) isbeginOP

17、EN cur_ref FOR SELECT * FROM T_TEST;end TESTC;END TESTPACKAGE;2、JAVA代码Package dusuzhong.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

18、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( + rs.getString(1) + +rs.getString(2)+);catch(SQLException ex2) ex2.printStrackTrace(); catch(Exception ex2) ex2.printStrackTrace(); finally try If (conn ! = null) conn.close(); catch()注：在执行前一定要先把oracle的驱动包放到class路径里。