oracle database link,它是用来更方便的一个数据库中访问另一个数据库(包括本地和远程的,道理一样)在本地建立的。即数据库连接只是连到别的数据库的快捷方式。
例如在192.168.104.191上有个nsbd数据库,用户名和密码分别是aqjc、aqjc,LISTENER 端口号是1522
在192.168.104.178上有个orcl数据库,用户名和密码分别是aqjc、aqjc, LISTENER端口号是1521,数据库有张表名为static_pointformula,表中字段有spointformulaguid和spointguid
1.创建database link
先在191机器上创建database link,创建语句有两种方式:
第一种,先在tnsnames.ora里配置连接178的字符串,如下所示
178 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.104.178)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
然后用sql 语句创建database link,创建语句如下所示:
Create public database link dblinkName connect to username identified by password using ‘connect string’
其中dblinkName为创建database link的名称,username和password分别为连接字符串’connect string ‘所指向的服务器中的数据库的用户名和密码,所以创建连接178机器上的database link如下所示:
Create public database link aaa connect to aqjc identified by aqjc using ‘178’
第二种,不用在tnsnames.ora里配置连接178的字符串,可以直接写在创建database link语句里,如下所示:
Create public database link aaa connect to aqjc identified by aqjc using ‘(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.104.178)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)’
2. 使用database link
public class TestDatabaseLink {
static Connection conn = null;
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
String user = "aqjc";
String password = "aqjc";
String url = "jdbc:oracle:thin:@localhost:1522:nsbd";
conn = DriverManager.getConnection(url,user,password);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
try {
//增、删、改、查语句如下所示,其中每个语句的表名static_pointformula后面跟的aaa是前面创建的database link的名称
conn.setAutoCommit(false);
Statement stat = conn.createStatement();
String sqlString = "insert into static_pointformula@aaa (spointformulaguid,spointguid) values('1','S_145')";
//sqlString = "update static_pointformula@aaa set norderid = '1' where spointformulaguid = '1'";
//sqlString = "delete from static_pointformula@aaa where spointformulaguid = '1'";
//System.out.println(stat.executeUpdate(sqlString));
conn.commit();
ResultSet rs = stat.executeQuery("select * from static_pointformula@dblinkName");
while(rs.next()){
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3));
}
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}