Database links小案例

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();
		}

	}

}