Java develops three methods for Oracle database connection JDBC Thin Driver

  • 2020-04-01 04:29:24
  • OfStack

There are three main categories of Oracle JDBC drivers:

1. JDBC OCI: OCI is short for oracle call interface. This driver is similar to the traditional ODBC driver. Because it requires the Oracle Call Interface and Net8, it needs to install the client software on the machine running the JAVA program that USES this driver, essentially using the oci and server configuration provided as a DLL in the orcale client.

2. JDBC Thin: Thin means for Thin client. This driver is commonly used in JAVA programs running in WEB browsers. Instead of communicating over OCI or Net8, it communicates over Java sockets and is driven by a pure Java implementation, so you don't need to install orcale client software on a client machine using JDBC Thin, so it has good portability and is typically used in web development.

3. JDBC KPRB: this driver is used by JAVA programs that are Stored directly in the Database, such as JAVA Stored Procedures, triggers, and Database JSP's. Because it is used inside the server, it USES the default or current session connection to the visiting database, does not require a username, password, etc., nor does it require a database url.

One. JDBC connection Oracle instructions

JDBC application connection to Oracle has encountered a problem. The error is as follows:

Ora-12505,TNS:listener does not currently know of SID given in connect descriptor TheConnection descriptor used by the client was.

I configured the static registration at the DB level, and since the GLOBAL_DBNAME and SID_NAME are not the same, the previous configuration is the same, so I didn't find this problem.


 (SID_DESC =
  (GLOBAL_DBNAME = dave)
   (ORACLE_HOME =D:appAdministratorproduct11.2.0dbhome_1)
  (SID_NAME = NEWCCS)
 )

Dynamic and static registration of Oracle Listener

(link: #)

A search of the web revealed three formats for JDBC Thin Driver formats:

Format:   Oracle JDBC Thin using a ServiceName: 

JDBC: oracle: thin: @ / / < Host> : < Port> / < Service_name >
Example: JDBC: oracle: thin: @ / / 192.168.2.1:1521 / XE

Note the format here, the @ followed by //, which is the main difference from using SID.

This format is recommended by Oracle because the SID for each node is different for the cluster, but the SERVICE_NAME does contain all nodes.

Oracle JDBC Thin using an SID: 

JDBC: oracle: thin: @ < Host> : < Port> : < SID>
Example: JDBC: oracle: thin: 192.168.2.1:1521: X01A

Note: the Support for the SID is being phased out. Oracle recommends that users switch over to usingservice names.

Oracle JDBC Thin using a TNSName: 

JDBC: oracle: thin: @ < TNSName>
Example: JDBC: oracle: thin: @ GL

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

Test 2.

2.1 preparation:

Oracle is 11 gr2


Listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
  (GLOBAL_DBNAME = dave)
   (ORACLE_HOME =D:appAdministratorproduct11.2.0dbhome_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" 
 Connectionull 
 Statemenconn 
 ResultSernull 
 publijdbc( 
 tr 
 Class.forName("oracle.jdbc.driver.OracleDriver").newInstance() 
 DriverManager.getConnection(dbUrltheUserthePw) 
 conc.createStatement() 
 catc(Exceptioe 
 e.printStackTrace() 
 publibooleaexecuteUpdate(Strinsql 
 tr 
 conn.executeUpdate(sql) 
 returtrue 
 catc(SQLExceptioe 
 e.printStackTrace() 
 returfalse 
 publiResultSeexecuteQuery(Strinsql 
 rnull 
 tr 
 rconn.executeQuery(sql) 
 catc(SQLExceptioe 
 e.printStackTrace() 
 returrs 
 publivoiclose( 
 tr 
 conn.close() 
 c.close() 
 catc(Exceptioe 
 e.printStackTrace() 
 publistativoimain(String[args 
 ResultSers 
 jdbconnejdbc() 
 rconn.executeQuery("selecfrodavwherrownum<") 
 tr 
 whil(rs.next() 
 System.out.println(rs.getString("username")+"--"+rs.getString("user_id")) 
 catc(Exceptioe 
 e.printStackTrace() 
 . 
 .--- The output is normal  
 .MGMT_VIEW-- 
 .ANQING-- 
 .DVD-- 
 .SYSMAN-- 

2.3 use service_name: Dave

Change the dbUrl of section 2.2 to 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 report:

Java.sqlexception: The Network Adapter could not establish The connection

You can try to replace the corresponding JDBC connection driver. The description on the official website is as follows:

JDBC Thin Driver 11g Causes" java.sql.Sqlexception: Io Exception: The Network Adapter Could NotEstablish The Connection" While Connecting to Oracle Database 11g [ID947653.1]

Change the JDBC connection driver class inyour application server from:

Oracle, JDBC driver. OracleDriver
The to
Oracle. JDBC. OracleDriver

2.4 use TNS name: DVD

String dbUrl = "JDBC: oracle: thin: @" on DVD.

Error report is as follows:

Java. SQL. SQLException: Unknown host specified

The problem is that the JVM does not have the system property for oracle.net.tns_admin.

There are two solutions:

Method 1: when the VM is started, add the following parameters : 

- Doracle.net.tns_admin=D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN

Method 2: add:


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
ResultSernul
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.printStackTrace(
 returfals
publiResultSeexecuteQuery(Strinsq
 rnul
 t
 rconn.executeQuery(sql
 catc(SQLExceptio
 e.printStackTrace(
 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(

  Here's a piece of code for connecting to Oracle databases in Java using 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 {
 
 public static void main(String[] args) {
 try {
 //The first step to get the Jar: in the directory where oracle is installed, disk :oracleora JDBC libojdbc.jar
 //The load driver
 Class.forName("oracle.jdbc.driver.OracleDriver");
 //Connection string (protocol name: JDBC, subprotocol name :oracle :thin subname :@localhost::oracleDB)
 String url ="jdbc:oracle:thin:@localhost::ora";
 try {
  //Establish a connection
  Connection conn = DriverManager.getConnection(url,"scott","");
  //Create a Statement or PreparedStatement Statement
  Statement st = conn.createStatement();
  String sqlStr= "select ename from emp";
  //Execute the query
  ResultSet rs = st.executeQuery(sqlStr);
  //Iterate through the result
  while (rs.next()) {
  System.out.println(rs.getString());
  }
 } catch (SQLException e) {
  e.printStackTrace();
 }
 } catch (ClassNotFoundException e) {
 e.printStackTrace();
 }
 }
 }

Related articles: