public class Out {
public static void main(String[] args) {
//查询配置表
doing();
}
public static void doing() {
//查询配置表
//循环配置表内容
//for()
//get(表名,主键字符串);
//拼接 orca 查询语句 select * from tableName order by 主键字符串
String sql = "select * from configure";
String select = "select * from";
String OracleSql = "";
String OrderBy = "order by";
try {
Connection conn = JdbcUtil.SQLServerGet();
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
ResultSetMetaData rm = rs.getMetaData();
int c = rm.getColumnCount();
String tableName ="";
String pk = null;
while (rs.next()) {
for (int i = 0; i < c - 1; i++) {
String columnName = rm.getColumnName(i + 1);
System.out.println("columnName = " + columnName);
if (i == 0) {
tableName = rs.getString(columnName);
// System.out.println("string = " + table_name);
pk = rs.getString("PK_STRING");
//System.out.println("pk_String = " + pk_String);
}
}
OracleSql = select + " " + tableName + OrderBy + " " + pk.toUpperCase();
System.out.println("OracleSql = " + OracleSql);
OracleSelectALL(OracleSql, tableName, pk);
}
JdbcUtil.close2(rs, ps, conn);
} catch (Exception e) {
e.printStackTrace();
}
}
public static List OracleSelectALL(String OracleSql, String tableName, String pk ) {
System.out.println("OracleSql = " + OracleSql);
try {
Connection conn = JdbcUtil.OracleGet(); //从工具类中获取到链接数据库
Connection sqlServerConn = JdbcUtil.SQLServerGet(); //从工具类中获取到链接数据库,把SqlServer的链接驱动提取出来,只需要链接一次
PreparedStatement stmt = conn.prepareStatement(OracleSql);
ResultSet rs = stmt.executeQuery(); //rs就是Oracle数据的结果集
ResultSetMetaData rm = rs.getMetaData(); //从结果集中获取数据
int c = rm.getColumnCount(); //从数据中获取到列的数量
while (rs.next()) {
String sqlColumn = "";
String where = "1=1"; //1=1,是为了防止where后面没有条件做查询的时候,以防报错,在拼接的时候有and,不加1=1会报错
String oracleResult = null; //定义 一个 Sting s来 拼接字符串
for (int i = 0; i < c; i++) { //循环,以列的数量来做次数
String columnName = rm.getColumnName(i + 1); //获取列值,是从1开始取值的,所以i要先等于0
System.out.println("columnName = " + columnName);
if (i == 0) {
sqlColumn = columnName;
System.out.println("sqlColumn1 = " + sqlColumn);
} else {
sqlColumn = sqlColumn + "," + columnName;
System.out.println("sqlColumn2 = " + sqlColumn);
}
//拼接
oracleResult += rs.getString(columnName); //a+=b <==> a=a+b s=s+rs.getString(columnName);
System.out.println("oracleResult = " + oracleResult);
if ((pk.toUpperCase()).contains(columnName)) { //pk.toUpperCase()把主键改成大写,不管字符串中是否有大写字母
//拼接where 语句
where += " and " + columnName + "='" + rs.getString(columnName) + "'";
}
}
System.out.println("pkString = " + where);
String sqlServerSql = "select " + sqlColumn + " from " + tableName + " where " + where + ";";
System.out.println("sqlserver = " + sqlServerSql);
String sqlserverResult = SQLServerSelectALL(sqlServerSql, sqlServerConn);
if (!oracleResult.equals(sqlserverResult)) { //equals 比较内容
//如果不一致就直接返回sqlserver的字符串
System.out.println("oracleResult=" + oracleResult);
System.out.println("sqlserverResult=" + sqlserverResult);
insertSqlServer(sqlserverResult);
}
}
JdbcUtil.close2(rs, stmt, conn);
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
public static void insertSqlServer(String sqlserverResult){
String sql = "INSERT INTO DIFERENCE (diversity) VALUES (?)";
Connection conn = JdbcUtil.SQLServerGet();
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,sqlserverResult); //修改diversity
ps.executeQuery();
JdbcUtil.close1(null,ps,conn);
} catch (Exception e) {
e.printStackTrace();
}
}
//sqlserver查询出来的结果集
public static String SQLServerSelectALL(String sqlServerSql, Connection conn) throws Exception {
String s = null; //定义出一个Sting 用来拼接字符串
PreparedStatement stmt = conn.prepareStatement(sqlServerSql); //执行sql
ResultSet rs = conn.createStatement().executeQuery(sqlServerSql); //rs就是sqlServer数据的结果集
ResultSetMetaData rm = rs.getMetaData(); //从结果集中获取数据
int c = rm.getColumnCount(); //从数据中获取到数据量
while (rs.next()) {
for (int i = 0; i < c; i++) {
String columnName = rm.getColumnName(i + 1); //获取列名
//拼接
s += rs.getString(columnName);
//通过列名获取列值拼接到 s 中
System.out.println("s = " + s);
}
}
rs.close();
stmt.close();
return s; //把 s 返回
}
}