oracle在从java程序插入MS SQL Server 2008时避免重复记录

Question

我正在开发一个功能，我应该从oracle数据库获取数据，并使用Java编程将其插入MSSQL Server 2008数据库。（我知道还有其他选择，比如甲骨文金门（oracle golden gate）、链接服务器（linked server）。但这就是我被要求做的事情）。我能够从oracle server获取数据并将其插入sql server。但问题在于redunduncy。i、 e每当我触发或运行程序时，就会插入重复记录，因为我应该每6、12、24小时插入一次。我不希望它发生。既然源数据库具有不同的连接，而目标数据库具有不同的连接，那么如何避免这种情况呢

下面是代码。我希望您能帮助我解决这个问题，避免插入重复的数据

/*TO Retrieve data from oracle database and insert it into sql server*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;


public class states {

    public static void main(String[] args) {
        String statecode, statename;
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")
                    .newInstance();

            // Connecting to Oracle
            Connection oracleconn = DriverManager.getConnection(
                    "jdbc:oracle:thin:@ipaddress:1521:orcl",
                    "uname", "pwd");
            // Connecting to SQL SERVER
            Connection sqlconn = DriverManager
                    .getConnection("jdbc:sqlserver://localhost:1433;databaseName=dbname;user=sa;password=pwd;");
            System.out.println("connected");



            // create Statement for sql and oracle

            /*
             * A Statement is an interface that represents a SQL statement. You
             * execute Statement objects, and they generate ResultSet objects,
             * which is a table of data representing a database result set. You
             * need a Connection object to create a Statement object.
             */
            Statement oraclestatement = oracleconn.createStatement();

            Statement sqlstatement = sqlconn.createStatement();

            /*
             * The ResultSet interface provides methods for retrieving and
             * manipulating the results of executed queries, and ResultSet
             * objects can have different functionality and characteristics.
             */
            ResultSet oracle_rs = oraclestatement
                    .executeQuery("select substr(TRIIDTX,1,2),TRINAMETX from  T_TRISTATE  WHERE TRIIDTX IS NOT NULL AND TRINAMETX IS NOT NULL AND TRINAMETX  not LIKE '%''%' ESCAPE '/'");
            System.out.println("TRICODETX   TRINAMETX \n");

            // String dummytable="tbldummystate";

            while (oracle_rs.next()) {

                System.out.println("     " + oracle_rs.getString(1) + "      "
                        + oracle_rs.getString(2) + "   ");

                // converting the string value into integer value

                statecode = oracle_rs.getString(1);
                statename = oracle_rs.getString(2);

                sqlstatement
                        .executeUpdate("insert into tblStates(StateCode,StateName) values('"
                                + statecode + "','" + statename + "')");

                // sqlstatement.execute ();


            }// end of while loop

        } catch (Exception e) {
            e.printStackTrace();
        }

    }// end of
}

Answer 1

也许使用MERGE就可以了

比如：

.executeUpdate("
MERGE INTO tblStates AS Target
USING (VALUES ('"+ statecode + "','" + statename + "'))
       AS Source (StateCode, StateName)
ON Target.StateCode = Source.StateCode
WHEN NOT MATCHED BY TARGET THEN
    INSERT (StateCode, StateName) VALUES ('"+ statecode + "','" + statename + "');
)";

编辑：如果要更新目标表中已存在的StateCode的StateName（如果源表中已更改），可以在WHEN NOT MATCHED子句之前的语句中添加以下内容：

WHEN MATCHED THEN UPDATE SET StateName = ('" + statename + "')"

Answer 2

在执行insert into tblStates...之前，可以对sqlconn执行select操作，并检查要插入的记录是否已经存在。如果是，则跳过insert，否则，执行insert

但是，这不是很有效，对于每个记录，您将执行select。想象一张有10万行的桌子

为了提高性能，您可以执行以下操作。在迭代oracle_rs之前，将tblStates中的所有记录加载到List中。由于没有主键（根据您的评论），我建议使用List<Foo>，在这里定义一个名为Foo的类，其中包含要插入到目标数据库中的一行的值。这允许您在内存中检查目标数据库中是否存在。如果内存中的集合包含刚从源数据库获取的记录，请不要将其插入目标数据库

在伪代码中，大致如下所示：

String selectAllQuery = "select StateCode, StateName from tblStates";
Statement selectAllstatement = sqlconn.createStatement();
ResultSet selectAllResultset = selectAllstatement.executeQuery(selectAllQuery);

List<Foo> cache = new ArrayList<Foo>();
while (selectAllResultset.next()) {
    cache.add(new Foo(selectAllResultset.getString("StateCode"),
                      selectAllResultset.getString("StateName")));
}

while (oracle_rs.next()) {
    statecode = oracle_rs.getString(1);
    statename = oracle_rs.getString(2);

    if (!cache.contains(new Foo(statecode,
                                statename))) {
        sqlstatement.executeUpdate("insert into tblStates"
            + "(StateCode,StateName) values('"
            + statecode + "','" + statename + "')");
    }
}

使用这样的Foo类：

public class Foo {
    private String stateName;
    private String stateCode;
    // Insert constructor with two arguments
    // Insert equals() implementation based on the two instance variables
}