黑马程序员-Java操作Oracle的基本方式总结

----------- 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路径里。