Three methods for connecting Java JDBC Thin Driver to Oracle: jdbcthin

Three methods for connecting Java JDBC Thin Driver to Oracle (reproduced), jdbcthin
1. JDBC connection to Oracle

An error occurred while connecting JDBC applications to Oracle:

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

 

I configured static registration at the DB level, and GLOBAL_DBNAME and SID_NAME are different. The previous configuration is the same, so I did not find this problem.

 

(SID_DESC =

(GLOBAL_DBNAME = dave)

(ORACLE_HOME = D: \ app \ Administrator \ product \ 11.2.0 \ dbhome_1)

(SID_NAME = NEWCCS)

)

 

 

Dynamic Registration and static registration of Oracle Listener

Http://blog.csdn.net/tianlesoftware/article/details/5543166

 

After logging on to the Internet, I found that JDBC Thin Driver has three formats:

 

Format 1: Oracle JDBC Thin using a ServiceName:

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

 

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

This format is recommended by Oracle, because for clusters, the SID of each node is different, but SERVICE_NAME can indeed contain all nodes.

 

Format 2: Oracle JDBC Thin using an SID:

Jdbc: oracle: thin :@< host >:< port >:< SID>
Example: jdbc: oracle: thin: 192.168.2.1: 1521: X01A

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

 

 

Format 3: 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

 

 

 

Ii. Test

 

2.1 preparations:

 

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

 

1. Import java. SQL .*;
3. Public class jdbc {
4. String dbUrl = "jdbc: oracle: thin: @ 127.0.0.1: 1521: newccs ";
5. String theUser = "dave ";
6. String thePw = "dave ";
7. Connection c = null;
8. Statement conn;
9. ResultSet rs = null;
11. Public jdbc (){
12. Try {
13. Class. forName ("oracle. jdbc. driver. OracleDriver"). newInstance ();
14. C = DriverManager. getConnection (dbUrl, theUser, thePw );
15. Conn = c. createStatement ();
16. } Catch (Exception e ){
17. E. printStackTrace ();
18. }
19. }
21. Public boolean executeUpdate (String SQL ){
22. Try {
23. Conn.exe cuteUpdate (SQL );
24. Return true;
25. } Catch (SQLException e ){
26. E. printStackTrace ();
27. Return false;
28. }
29. }
31. Public ResultSet executeQuery (String SQL ){
32. Rs = null;
33. Try {
34. Rs = conn.exe cuteQuery (SQL );
35. } Catch (SQLException e ){
36. E. printStackTrace ();
37. }
38. Return rs;
39. }
41. Public void close (){
42. Try {
43. Conn. close ();
44. C. close ();
45. } Catch (Exception e ){
46. E. printStackTrace ();
47. }
48. }
50. Public static void main (String [] args ){
51. ResultSet rs;
52. Jdbc conn = new jdbc ();
53. Rs = conn.exe cuteQuery ("select * from dave where rownum <5 ");
54. Try {
55. While (rs. next ()){
56. System. out. println (rs. getString ("username") + "--" + rs. getString ("user_id "));
57. }
58. } Catch (Exception e ){
59. E. printStackTrace ();
60. }
61. }
62. }
64. --- The output is normal:
65. MGMT_VIEW--97
66. ANQING--94
67. DVD--93
68. SYSMAN--95

 

 

2.3 use service_name: dave

Change the dbUrl in section 2.2 to the following:

String dbUrl = "jdbc: oracle: thin: @ // 127.0.0.1: 1521/dave ";

 

Output result:

MGMT_VIEW--97

ANQING--94

DVD--93

SYSMAN--95

 

If the following error occurs in 11 GB:

Test and run Java class, error:

Java. SQL. SQLException: The Network Adapter cocould not establish the connection

 

You can try to change the corresponding jdbc connection driver. The official website is described as follows:

 

JDBC Thin Driver 11g Causes "Java. SQL. Sqlexception: Io Exception: The Network Adapter cocould 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

To

Oracle. jdbc. OracleDriver

 

 

2.4 use TNS name: dvd

String dbUrl = "jdbc: oracle: thin: @ dvd ";

 

The following error is reported:

Java. SQL. SQLException: Unknown host specified

 

This problem occurs because the JVM does not have the system property of oracle.net. tns_admin. There are two solutions:

 

Method 1: Add the following parameters when starting the VM:

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

Method 2: Add the following content to the java code:

System. setProperty ("oracle.net. tns_admin", "D :\\ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN ");

 

After adding, you can use tnsnama in JDBC normally.

 

1. Import java. SQL .*;
3. Public class jdbc {
4. String dbUrl = "jdbc: oracle: thin: @ dvd ";
5. // String dbUrl = "jdbc: oracle: thin: @ // 127.0.0.1: 1521/dave ";
6. // String dbUrl = "jdbc: oracle: thin: @ 127.0.0.1: 1521: newccs ";
7. String theUser = "dave ";
8. String thePw = "dave ";
9. Connection c = null;
10. Statement conn;
11. ResultSet rs = null;
13. Public jdbc (){
14. Try {
15. System. setProperty ("oracle.net. tns_admin", "D :\\ app \ Administrator \ product \ 11.2.0 \ dbhome_1 \ NETWORK \ ADMIN ");
16. Class. forName ("oracle. jdbc. driver. OracleDriver"). newInstance ();
17. // Class. forName ("oracle. jdbc. OracleDriver"). newInstance ();
18. C = DriverManager. getConnection (dbUrl, theUser, thePw );
19. Conn = c. createStatement ();
20. } Catch (Exception e ){
21. E. printStackTrace ();
22. }
23. }
25. Public boolean executeUpdate (String SQL ){
26. Try {
27. Conn.exe cuteUpdate (SQL );
28. Return true;
29. } Catch (SQLException e ){
30. E. printStackTrace ();
31. Return false;
32. }
33. }
35. Public ResultSet executeQuery (String SQL ){
36. Rs = null;
37. Try {
38. Rs = conn.exe cuteQuery (SQL );
39. } Catch (SQLException e ){
40. E. printStackTrace ();
41. }
42. Return rs;
43. }
45. Public void close (){
46. Try {
47. Conn. close ();
48. C. close ();
49. } Catch (Exception e ){
50. E. printStackTrace ();
51. }
52. }
54. Public static void main (String [] args ){
55. ResultSet rs;
56. Jdbc conn = new jdbc ();
57. Rs = conn.exe cuteQuery ("select * from dave where rownum <5 ");
58. Try {
59. While (rs. next ()){
60. System. out. println (rs. getString ("username") + "--" + rs. getString ("user_id "));
61. }
62. } Catch (Exception e ){
63. E. printStackTrace ();
64. }
65. }
66. }

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Blog: http://blog.csdn.net/tianlesoftware

WEAVER: http://weibo.com/tianlesoftware

Bytes -------------------------------------------------------------------------------------------------------