java connection to oracle_JAVA_编程开发_程序员俱乐部

class="java" name="code">
Register the JDBC drivers:
there have two ways:
Class.forName("oracle.jdbc.OracleDriver");//this method is only valid for JDK-compliant Java virtual machines. 
										  //It is not valid for Microsoft Java virtual machines
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

Open a connection to a database:
										  
1> DriverManager.getConnection(String DB_URL,String DB_USERNAME,String DB_PASSWORD);
Where the URL is of the form:
jdbc:oracle:<drivertype>:@<database>
The following example connects user scott with password tiger to a database with SID orcl through port 1521 of host myhost, using the Thin driver.

Connection conn = DriverManager.getConnection
  ("jdbc:oracle:thin:@myhost:1521:orcl", "scott", "tiger");

2> DriverManager.getConnection(String DB_URL)
where the URL is of the form:
  jdbc:oracle:<drivertype>:<user>/<password>@<database>
If you want to connect with the Thin driver, you must specify the port number and SID. For example, if you want to connect to the database on host myhost that has a TCP/IP listener up on port 1521,
 and the SID (system identifier) is orcl:
Connection conn = DriverManager.getConnection
   ("jdbc:oracle:thin:scott/tiger@myhost:1521:orcl);

3> getConnection(String URL, Properties info);
Where the URL is of the form:
  jdbc:oracle:<drivertype>:@<database>
In addition to the URL, use an object of the standard Java Properties class as input. For example:

java.util.Properties info = new java.util.Properties();
  info.put ("user", "scott");
  info.put ("password","tiger");
  info.put ("defaultRowPrefetch","15");
  getConnection ("jdbc:oracle:oci8:@",info);

Create an instance of the Java class Statement:

1>Statement stmt=Connection.createStatement();
2>The class PreparedStatement is a subclass of Statement, 
but it pre-compiles the SQL statement before executing it.
PreparedStatement stmt=conn.prepareStatement(
                          " SELECT * from Employee WHERE empno=?");
for (int i=0;i<empNumbers.length; i++){

     // pass the array's value that substitutes the question mark
     stmt.setInt(1,employees[i];)
     stmt.executeQuery(sqlQuery);
   }
3>the callableStatement is used for executing stored procedures from java 
CallableStatement stmt = conn.prepareCall("{call changeEmpTitle(?,?)}");

   stmt.setInt(1,7566);
   stmt.setString (2,"Salesman");
   stmt.executeUpdate();
 If a stored procedure returns some values using output parameters, 
 each of the OUT data types has to be registered before the statement is executed  
 CallableStatement stmt = conn.prepareCall(
                    ("{call getEmpTitle(?,?) }");
   stmt.setInt(1, 7566);
   stmt.registerOutParameter(2,Java.sql.Types.VARCHAR);
   stmt.executeQuery();
   String title=stmt.getString(2);
   
ResultSet:
  1> get the values of each field by specifying the relative position of the column from the reuslt set:
	int empNo = rs.getInt(1);
    String eName = rs.getString(2);
	 
  2> use the field name:
	int empNo = rs.getInt("EMPNO");
    String eName = rs.getString("ENAME");
 ResultSetMetaData:
	with the class ResultSetMetaData you can dynamically find out how many columns three are in the result set
as well as their names and data types 	
 String sqlQuery = "select * from Employee";
   ResultSet rs = stmt.executeQuery(query);

   ResultSetMetaData rsMeta = rs.getMetaData();
   int colCount = rsMeta.getColumnCount();

   for (int i = 1; i <= colCount; i++) {
     System.out.println(
         " Column name: " + rsMeta.getColumnName(i) +
         " Column type: " + rsMeta.getColumnTypeName(i));
   }

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;


public class OracleConnectionDemo {
	private final static String DB_URL = "jdbc:oracle:thin:@17.26.66.209:5731:PDS";
	private final static String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private final static String DB_USERNAME = "scott";
	private final static String DB_PASSWORD = "tiger";
	public static void main(String[] args){
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement stmt1 = null;
		ResultSet rs = null;
		ResultSetMetaData reMeta = null;
		String querysql = "select nda_id from test where rownum < 5";
		try{
			//Load the JDBC driver using the method forName()
			Class.forName(DB_DRIVER);
			
			//Obtain the database connection to database
			conn = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
			//conn = DriverManager.getConnection(DB_URL1);
			//Create an instance of the java class statement:
			stmt = conn.createStatement();
			rs = stmt.executeQuery(querysql);
			while(rs.next()){
				int nda_id = rs.getInt("nda_id");
				System.out.println(nda_id);
			}
			rs.close();
			stmt1 = conn.prepareStatement("select * from test where nda_id = ?");
			stmt1.setInt(1, 16117);
			rs = stmt1.executeQuery();
			reMeta = rs.getMetaData();
			int columCount = reMeta.getColumnCount();
			for(int i = 1; i <= columCount; i++){
				System.out.print("Column name: " + reMeta.getColumnName(i));
				System.out.println(" Column type: " + reMeta.getColumnTypeName(i));
			}
			while(rs.next()){
				System.out.println(rs.getInt(2));
			}
		} catch(SQLException se){
			System.out.println("SQLError: " + se.getMessage() + "code: " + se.getErrorCode());
		} catch(Exception e){
			System.out.println(e.getMessage());
			e.printStackTrace();
		} finally{
				try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}