文章转自:http://blog.itpub.net/22664653/viewspace-1383092/
前言
本文是一篇学习笔记,学习如何通过java jdbc /ojdbc 连接oracle的几种方式。
一 使用方法
方法一:使用service_name 连接oracle
注意这里的格式,@后面有//, 这是与使用SID的主要区别。jdbc:oracle:thin:@//<host>:<port>/<service_name>例如: jdbc:oracle:thin:@//10.10.10.1:1521/TDB
对于集群来说,每个节点的SID 是不一样的,但是SERVICE_NAME 确可以包含所有节点。
方法二: 使用SID 连接oracle
jdbc:oracle:thin:@<host>:<port>:<SID>Example: jdbc:oracle:thin:@10.10.10.1:1521:testdb1
注意 该方法已经不做推荐,oracle 官方推荐使用service_name
方法三:使用tnsname 连接oracle
jdbc:oracle:thin:@<TNSName>Example: jdbc:oracle:thin:@TESTDB
二 源代码
import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class testDB {public static final String DBDRIVER = "oracle.jdbc.driver.OracleDriver";# public static final String DBURL = "jdbc:oracle:thin:@127.0.0.1:1521/testdb1"; //sid 格式 testdb1是sid# public static final String DBURL = "jdbc:oracle:thin:@//127.0.0.1:1521/TDB"; //servicename TDB是service_namepublic static final String DBUSER = "test";public static final String DBPASSWORD = "xxx";public static final String DBURL = "jdbc:oracle:thin:@TESTDB"; //tnsname 格式public static void main(String[] args) throws Exception{Connection con = null;PreparedStatement ps = null;ResultSet rs = null;String strSQL = "select count(*) from tsa_dim_deal";# System.setProperty("oracle.net.tns_admin", "/home/admin/oracle");//使用tnsname 方法的时候 需要制定tnsname.ora所在的文件夹绝对路径
Class.forName(DBDRIVER).newInstance();
con = DriverManager.getConnection(DBURL,DBUSER,DBPASSWORD);ps = con.prepareStatement(strSQL);rs = ps.executeQuery();while(rs.next()){System.out.println("num:"+rs.getString(1));
}rs.close();ps.close();con.close();}}
附录:
[qilong.yangql@rac1 oracle]$ more tnsnames.oratestdb=(DESCRIPTION =(FAILOVER = ON)
(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.2)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.3)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.4)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.5)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.6)(PORT = 1521)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = tdb)))
注意 本文里面 service_name tdb 和tnsname (testdb) 故意设置的不一样.