Java Development Oracle Database connection JDBC Thin Driver three ways _java

Oracle's JDBC driver three main categories:

1. JDBC OCI:OCI is an abbreviation for Oracle call interface, which is similar to the traditional ODBC driver. Because it requires Oracle call Interface and NET8, it needs to install the client software on a machine that is running the Java program using this driver, essentially using the OCI and server configuration provided in the Orcale client as a DLL.

2, JDBC Thin:thin is for Thin client meaning, this kind of drive is used in the Java program that runs in the Web browser generally. Rather than using the OCI or NET8, it communicates through the Java sockets and is driven by a pure Java implementation, so there is no need to install Orcale client software on a client machine that uses JDBC thin, so it is very portable and typically used in web development.

3. JDBC KPRB: This drive is used by Java programs that are stored directly in the database, such as Java Stored procedures, triggers, DB JSP ' s. Because it is used inside the server, he uses the default or current session to connect to the visiting database, does not require a username password, and does not require a database URL.

A. JDBC Connection Oracle Description

JDBC Application Connection Oracle encountered a problem with the following error:

Ora-12505,tns:listener does not currently know of SID given in Connect descriptor Theconnection descriptor to the CLI ENT was.

I configured static registration at the DB level, and Global_dbname and sid_name are not the same, the previous configuration is the same, so did not find this problem.

 (Sid_desc =
  (Global_dbname = Dave)
   (Oracle_home =d:\app\administrator\product\11.2.0\dbhome_1)
  (Sid_name = Newccs)
 )

Oracle Listener Dynamic registration and static registration

Http://www.jb51.net/article/75885.htm

Google on the internet, found that the JDBC Thin Driver formats has three forms:

Format one: Oracle JDBC Thin using a ServiceName:

Jdbc:oracle:thin:@//Example:jdbc:oracle:thin:@//192.168.2.1:1521/xe

Notice the format here, @ there's//, this is the main difference from using SIDS.

This format is a recommended format for Oracle because each node's SID is different for a cluster, but service_name can contain all nodes.

Format two: Oracle JDBC Thin using an SID:

Jdbc:oracle:thin:@example:jdbc:oracle:thin:192.168.2.1:1521:x01a

Note:support for SID are being phased out. Oracle recommends that users switch over to Usingservice names.

Format III: Oracle JDBC Thin using a tnsname:

Jdbc:oracle:thin:@<tnsname>
Example:jdbc:oracle:thin: @GL

Note:
Support for TNSNames is added in the driver release 10.2.0.1

Two. Test

2.1 Preparation work:

Oracle is 11GR2

Listener.ora
Sid_list_listener =
 (sid_list = (
 Sid_desc =
  (Global_dbname = Dave)
   (Oracle_home =d : \app\administrator\product\11.2.0\dbhome_1)
  (sid_name = Newccs)
 )
tnsnames.ora
DVD =
 (DESCRIPTION = (
 address_list = (Address
  = (PROTOCOL = TCP) (HOST = 127.0.0.1) (PORT = 1521))
 )
 ( Connect_data =
  (service_name = Dave)
 )
 

2.2 Test 1, using Sid:newccs

. imporjava.sql.*. Publiclasjdb strindbur "Jdbc:oracle:thin:@...::newccs" Strintheuse "Dave" StrintheP "Dave" Conne  
 Ctionull statemenconn resultsernull publijdbc (tr class.forname ("Oracle.jdbc.driver.OracleDriver"). Newinstance () Drivermanager.getconnection (DBURLTHEUSERTHEPW) conc.createstatement () CATC (Exceptioe e.printstacktrace () publib Ooleaexecuteupdate (strinsql tr conn.executeupdate (SQL) returtrue CATC (Sqlexceptioe e.printstacktrace () Returfa 
 LSE Publiresultseexecutequery (Strinsql rnull tr rconn.executequery (SQL) CATC (Sqlexceptioe e.printstacktrace () Returrs publivoiclose (tr conn.close () c.close () CATC (Exceptioe e.printstacktrace () Publistativoimain (Stri Ng[args resultsers Jdbconnejdbc () rconn.executequery ("selecfrodavwherrownum<") tr Whil (Rs.next () System.out 
 . println (Rs.getstring ("username") + "--" +rs.getstring ("user_id")) CATC (Exceptioe e.printstacktrace (). .---output is normal. Mgmt_view--. 
anqing-- . DVD--.  sysman--

2.3 Using Service_name:dave

Change the Dburl of section 2.2 into the following:

String Dburl = "Jdbc:oracle:thin:@//127.0.0.1:1521/dave";

Output results:

mgmt_view--97

anqing--94

dvd--93

sysman--95

If you encounter the following error in 11g:

Test run Java class, error:

Java.sql.SQLException:The Network Adapter could not establish the connection

Can try to replace the corresponding JDBC Connection driver, the official website description is as follows:

JDBC Thin Driver 11g causes "Java.Sql.Sqlexception:Io exception:the network Adapter could notestablish the Connection" Wh Ile connecting to Oracle Database 11g [ID947653.1]

Change the JDBC connection driver class Inyour application Server from:

Oracle.jdbc.driver.OracleDriver
To
Oracle.jdbc.OracleDriver

2.4 Using TNS Name:dvd

String Dburl = "Jdbc:oracle:thin: @dvd";

The error is as follows:

Java.sql.SQLException:Unknown Host specified

The problem is because the JVM has no oracle.net.tns_admin system property.

There are 2 ways to solve this problem:

Method One: When starting the VM, add the following parameters:

-doracle.net.tns_admin=d:\app\administrator\product\11.2.0\dbhome_1\network\admin

Method Two: Add in Java code:

System.setproperty ("Oracle.net.tns_admin", "D:\\app\\administrator\\product\\11.2.0\\dbhome_1\\network\\admin") ;

Once added, Tnsnama can be used normally in JDBC.

. Imporjava.sql. . PUBLICLASJD Strindbur "Jdbc:oracle:thin: @dvd. Strindbur "Jdbc:oracle:thin:@//...:/dave.
Strindbur "Jdbc:oracle:thin:@...::newccs strintheuse" Dave Strinthep "Dave Connectionul Statemencon Publijdbc t System.setproperty ("Oracle.net.tns_admin", "d:\\app\\administrator\\product\\.") \\dbhome_\\NETWORK\\ADMIN "Class.forName (" Oracle.jdbc.driver.OracleDriver "). Newinstance (./class.forname (" Oracle.jdbc.OracleDriver "). Newinstance (drivermanager.getconnection dburltheuserthepw conc.createstatement (CATC ( Exceptio e.printstacktrace (publibooleaexecuteupdate strinsq t conn.executeupdate (SQL Returtru CATC (SQLExceptio e.pr Intstacktrace (returfals publiresultseexecutequery strinsq rnul t rconn.executequery (SQL CATC (SQLExceptio e.printSta
 Cktrace (Returr publivoiclose t Conn.close (c.close CATC (exceptio e.printstacktrace (Publistativoimain (String[arg Resultser Jdbconnejdbc (Rconn.executequery ("selecfrodavwherrownum<" T Whil (Rs.next (System.out. println (Rs.getstring ("username") + "--" +rs.getstring ("user_id") CATC (exceptio e.printstacktrace ( 

  Let's share a piece of code about Java to connect Oracle databases via JDBC (thin)

Package com.jdbc.OracleTest;
 Import Java.sql.DriverManager;
 Import java.sql.Connection;
 Import Java.sql.ResultSet;
 Import java.sql.SQLException;
 Import java.sql.Statement;
 public class Oraclejdbcconnectiontest {/** * by Jarin Month/@param args/public static void Main (string[) args) { try {//First step to jar package: in Oracle installed directory, drive letter: \oracle\ora\jdbc\lib\ojdbc.jar//Load driver Class.forName ("
 Oracle.jdbc.driver.OracleDriver ");
 Connection string (protocol name: JDBC, child protocol name: Oracle:thin child Name: @localhost:: oracledb) string url = "Jdbc:oracle:thin: @localhost:: Ora";
  try {//Establish connection Connection conn = drivermanager.getconnection (URL, "Scott", "");
  Create Statement or PreparedStatement statements Statement st = Conn.createstatement ();
  String sqlstr= "Select ename from emp";
  Execute Query ResultSet rs = st.executequery (SQLSTR);
  Traversal result while (Rs.next ()) {System.out.println (rs.getstring ());
 } catch (SQLException e) {e.printstacktrace ();
 } catch (ClassNotFoundException e) {e.printstacktrace (); }
 }
 }