insertnotices.java

连接oracle的java库

package com.zp.callProcFunc;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleCallableStatement;
import oracle.sql.ArrayDescriptor;
import java.math.BigDecimal;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import oracle.sql.ARRAY;
import oracle.jdbc.OracleTypes;
public class InsertNotices
{
  public InsertNotices()
  {
  }
public static void InsertNoticesFromArray()
{
  Connection connection=null;
  String url="jdbc:oracle:thin:@901-32:1521:xianmap";
  try
  {
    Class.forName("oracle.jdbc.OracleDriver");
    connection=DriverManager.getConnection(url,"userstoremap2","Icando41") ;
  }catch(java.lang.ClassNotFoundException  noclasse)
  {
    System.out.println("get a "+noclasse.getClass() +"\nwith message "+noclasse.getMessage() ) ;
    System.exit(-1) ;
  }catch(java.sql.SQLException  sqle)
  {
    System.out.println("get a "+sqle.getClass() +"\nwith message "+sqle.getMessage() ) ;
    System.exit(-1) ;
  }
  try
  {
    OracleCallableStatement callFunc = (OracleCallableStatement) connection.
        prepareCall("{?=call test_arrays.insert_notices(?)}");
    //create the arraydescriptor to identify the oracle type
    ArrayDescriptor inputArrayDescriptor=ArrayDescriptor.createDescriptor("ARRAY_OF_EMP_ID",connection) ;
    //create an oracle.sql.Array from the empIDS and the descriptor
    BigDecimal[] empIds=getAllEmpIds(connection);
    ARRAY empArray=new ARRAY(inputArrayDescriptor,connection,empIds);
    //register an out parameter as a result set
    callFunc.registerOutParameter(1,OracleTypes.CURSOR ) ;
    //set the input array
    callFunc.setArray(2,empArray) ;
    //execute the function and return the result
    callFunc.execute() ;
    ResultSet results=callFunc.getCursor(1) ;
    //output each notice
    while(results.next() )
    {
      System.out.println("notice id="+results.getInt("notice_id") ) ;
      System.out.println("description="+results.getString("description") ) ;
    }
    results.close() ;
    callFunc.close() ;
  }catch(java.sql.SQLException  sqle)
  {
    System.out.println("get a "+sqle.getClass() +"\nwith message "+sqle.getMessage() ) ;
    System.exit(-1) ;
  }finally
  {
   try
   {
     connection.close();
   }catch(java.sql.SQLException  sqle)
   {
         System.out.println("get a "+sqle.getClass() +"\nwith message "+sqle.getMessage() ) ;
   }
  }
}
private static BigDecimal[] getAllEmpIds(Connection connection)throws java.sql.SQLException
{/*this method looks up all the emp_ids from employees and returns them as a
 * BigDecimal array
 * @param connection the valid connection
 *@return the array of empIds
 *@exception SQLException any unexpected sql error
 */
Statement stmt=connection.createStatement() ;
ResultSet results=stmt.executeQuery("select emp_id from employees") ;
//create an arraylist to add the emp_ids to
 ArrayList empIdList=new ArrayList();
 while(results.next() )
 {
  BigDecimal temp=results.getBigDecimal(1) ;
   empIdList.add(results.getBigDecimal(1) ) ;
 }
 //create an array to pass the values to Oracle
 BigDecimal[] empIds=new BigDecimal[empIdList.size() ];
 empIdList.toArray(empIds) ;
 results.close() ;
 stmt.close() ;
 return empIds;//return the array
}
}