练手写的小程序，还算是有点用。做个记录
import oracle.jdbc.driver.OracleDriver;
import java.sql.*;
import java.util.ArrayList;
import java.util.Properties;
import antlr.collections.List;
public class Findlink {
private static String USERNAMR = "";
private static String PASSWORD = "";
private static String DRVIER = "oracle.jdbc.OracleDriver";
private static String URL = "jdbc:oracle:thin:@192.XX.XXX.XXXX:1521:XXX";
// 创建一个数据库连接
Connection connection = null;
// 创建预编译语句对象，一般都是用这个而不用Statement
PreparedStatement pstm = null;
// 创建一个结果集对象
ResultSet rs = null;
// 查询
public void SelectData() {
//表一获取所有的列名
connection = getConnection();
//在这里输入想查找的两个表名，运行即可
String t1Name="XXX";
String t2Name="XXXX";
String sql = "select t1.COLUMN_NAME from USER_TAB_COLUMNS t1 where t1.TABLE_NAME = upper('"+t1Name+"') and t1.data_type='VARCHAR2'";
//表二获取所有的列名
String sql2 = "select t2.COLUMN_NAME from USER_TAB_COLUMNS t2 where t2.TABLE_NAME = upper('"+t2Name+"') and t2.data_type='VARCHAR2'";
ArrayList list1 = new ArrayList();//t1所有表头
ArrayList list2 = new ArrayList();//t2所有表头
try {
//输出表一所有列名
pstm = connection.prepareStatement(sql);
rs = pstm.executeQuery();
while (rs.next()) {
String id = rs.getString("COLUMN_NAME");
list1.add(id);
}
//输出表二所有列名
pstm = connection.prepareStatement(sql2);
rs = pstm.executeQuery();
while (rs.next()) {
String id = rs.getString("COLUMN_NAME");
list2.add(id);
}
for (int i = 0; i < list1.size(); i++) {
for (int j = 0; j < list2.size(); j++) {
//剔除空列，有空格的值
String sqls="select t1."+list1.get(i)+",t2."+list2.get(j)+" from "+t1Name+" t1,"+t2Name+" t2 where t1."+list1.get(i)+"= t2."+list2.get(j)
+" and trim (t1."+list1.get(i)+") is not null and trim (t2."+list2.get(j)+") is not null ";
pstm = connection.prepareStatement(sqls);
rs = pstm.executeQuery();
if(list1.get(i).equals(list2.get(j))){
System.out.println(sqls);
System.out.println("字段名相同 t1."+list1.get(i)+",t2."+list2.get(j));
System.out.println("●----------------------------------● ");
} else if(rs.next()){
System.out.println(sqls);
System.out.println("全关联有数据的字段 t1."+list1.get(i)+",t2."+list2.get(j));
System.out.println("●----------------------------------● ");
//不为空的情况
}else{
// System.out.println(sqls);
// System.out.println("Yes");
//为空的情况
}
pstm.close();
}
}
System.out.println("完成");
} catch (SQLException e) {
e.printStackTrace();
} finally {
ReleaseResource();
}
}
/**
* 获取Connection对象
*
* @return
*/
public Connection getConnection() {
try {
Class.forName(DRVIER);
connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
System.out.println("成功连接数据库");
} catch (ClassNotFoundException e) {
throw new RuntimeException("class not find !", e);
} catch (SQLException e) {
throw new RuntimeException("get connection error!", e);
}
return connection;
}
/**
* 释放资源
*/
public void ReleaseResource() {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstm != null) {
try {
pstm.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
//创建OperateOracle对象
Findlink find=new Findlink();
find.getConnection();
find.SelectData();
find.ReleaseResource();
}
}
