java如何使用WHERE子句过滤出重复数据，而无需硬编码？

Question

好几天来，我一直坐在这里，在网上到处搜索，但都没有找到正确的答案

我的问题描述，我需要从传入数据中筛选重复数据。csv文件，请检查该数据是否已在表中，如果没有，则保留数据，否则不插入数据。我的方法，我在一个独特的阅读。csv文件逐行使用preparedStatement，假设使用WHERE子句检查重复项

我遇到的问题是，如何在我准备的报表中使用WHERE子句

下面是一些我的代码，以消除任何混乱

PS：这就是消息id的样子<；329577443.1427052727657.JavaMail。javamailuser@localhost&燃气轮机；有成千上万的

try { 
            dataRow = reader.readLine();
            while ((dataRow = reader.readLine()) != null) {     
                totalRecordsProcessed++;
                dataRow = dataRow.replace("\"", "");
                dataRow = dataRow.replace("<", "");
                dataRow = dataRow.replace(">", "");                         
                dataItems = dataRow.split(",", -1);             
                ExchangeReport exchangeReport = new ExchangeReport(dataItems);                                              
                //System.out.println(exchangeReport.toString());
                persistData(exchangeReport);                
            }           
        }

//在insert方法中执行检查，以查看是否正在持久化重复数据 public void persistData（ExchangeReport ExchangeReport）引发SQLException{

    Connection connection = super.getEnvironment().getConnection();   
    PreparedStatement preparedStatement = null;     
    String insertScript = null;     
    String skipScript = null;

    //ResultSet incomeSet;      
    ArrayList<String> rowVals = new ArrayList();

    ArrayList<String> dbList = new ArrayList<String>(); 
    ArrayList<String> incomeList = new ArrayList<String>();

    String tempVal = null;


    skipScript = "select unique_ref, message_id, event_id from InboundCorres " +
            "where message_id = " +
            "";
    preparedStatement = connection.prepareStatement(skipScript);
    ResultSet dbSet = preparedStatement.executeQuery();
    while (dbSet.next()) {
        rowVals.add(dbSet.getString(1).trim());
        rowVals.add(dbSet.getString(2).trim());
        rowVals.add(dbSet.getString(3).trim());
        System.out.println(rowVals.toString());
    }


    //List<String> incomeList = Arrays.asList(dataItems);                                                                           

    insertScript = "INSERT INTO INBOUNDCORRES(EVENT_ID, SOURCE_INBOUND, TIME_STAMP," + 
            "RECIPIENTS, MESSAGE_SUBJECT, SENDER, MESSAGE_ID," + 
            "CONNECTOR_ID, UNIQUE_REF)" + 
            "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
    preparedStatement = connection.prepareStatement(insertScript);
    preparedStatement.setString(1, exchangeReport.getEventId() );
    preparedStatement.setString(2, exchangeReport.getSourceInbound() );
    preparedStatement.setString(3, exchangeReport.getTimeStamp() ); 
    preparedStatement.setString(4, exchangeReport.getRecipient());
    preparedStatement.setString(5, exchangeReport.getMessageSubject());
    preparedStatement.setString(6, exchangeReport.getSender());
    preparedStatement.setString(7, exchangeReport.getMessageId());
    preparedStatement.setString(8, exchangeReport.getConnectorId());
    preparedStatement.setString(9, exchangeReport.getUniqueRef());
    preparedStatement.executeUpdate();              

}

Answer 1

skipScript中的SQL语句不完整

我希望看到这样的情况：

skipScript=“从InboundCorres中选择唯一的\u参考、消息\u id、事件\u id”+ “其中message_id=：msg_id”

因此，message_id是列名，msg_id是一个本地字符串变量，用于保存我们要查找的值

要获取msg_id中的值，我们可以调用一个名为saveMessage（）的方法，该方法类似于：

public void saveMessage (final String messageId) {
    Map<String, Object> values = new HashMap<String, Object>();
    values.put("msg_id", messageId);
    jdbcTemplate.update(skipScript , values);
}

请注意，我特意为message_id使用了三个不同的名称，以明确发生了什么

希望这有帮助。我意识到我的示例使用了jdbcTemplate。update（），与代码示例不同，但原理类似

Answer 2

感谢用户4810988，您帮助我找到了解决方案

skipScript = "select TIME_STAMP " +
                " from InboundCorres where unique_ref = ? " +
                "and message_id = ? " +
                "and event_id = ?";     
        preparedStatement = connection.prepareStatement(skipScript);
        preparedStatement.setString(1, exchangeReport.getUniqueRef());
        preparedStatement.setString(2, exchangeReport.getMessageId());
        preparedStatement.setString(3, exchangeReport.getEventId());

        ResultSet dbSet = preparedStatement.executeQuery();
        while (dbSet.next()) {
            rowVals.add(dbSet.getString(1).trim());
            rowVals.add(dbSet.getString(2).trim());
            rowVals.add(dbSet.getString(3).trim());
            System.out.println("Duplicates found ==>" + rowVals.toString());
        }

这就是我所期待的解决方案