在表中添加具有唯一列的行:获取现有ID值以及新创建的ID值 [英] Adding rows to table with unique column: Get existing ID values plus newly-created ones

问题描述

我具有以下表格结构

  1. mail_contacts(id,mail_id);//stores mail id, id is primary key
                                  mail_id is unique key
  2. inv_table(mid,aid,add_date);//mapped mail id with user id. mid is 
                                   mail_contacts id and aid is userid
                                   (mid,aid) is primary key  

我在插入后将多个邮件ID存储在mail_contacts中，我将获取其插入的ID，并借助inv_table将其存储. 如果mail_id中的任何内容未存储在mail_contacts中，则说明它正常工作. 但是如果mail_id存储在mail_contacts中，则插入将终止.

我想要的内容如果mail_id存储在mail_contacts中，则它应获取其id以便存储在 inv_table.

我正在尝试

    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet rs = null, res = null;
    Connection con = null;
    String status = "success";
    ArrayList ar = null;
    Invitation invi = null;
    int i = 0;

    public String insert(List<MailidInvitation> invitationList, Long aid) {
        con = ConnectionFactory.getConnection();
        try {
            ps = con.prepareStatement("insert into mail_contacts(mail_id)"
                    + " values(?)", Statement.RETURN_GENERATED_KEYS);

            for (MailidInvitation a : invitationList) {
                ps.setString(1, a.getMailId());
                ps.addBatch();
            }
            ps.executeBatch();
            ResultSet keys = ps.getGeneratedKeys();
            while (keys.next()) {
                long id = keys.getLong(1);
                invitationList.get(i).setId(id);
                System.out.println("generated id is " + id);
                i++;
            }

            ps1 = con.prepareStatement("insert into inv_table(mid,aid)"
                    + " values(?,?)");
            for (MailidInvitation a : invitationList) {
                ps1.setLong(1, a.getId());
                ps1.setLong(2, aid);
                ps1.addBatch();
            }
            ps1.executeBatch();
        } catch (SQLException e) {
            status = "failure";
            System.out.println("SQLException1 " + e);
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                System.out.println("SQLException2 " + e);
            }
        }
        System.out.println("Status is " + status);

        return status;
    }

该怎么做?

解决此类问题的最佳方法是什么

我正在使用mysql数据库

解决方案

第一种方法在处理批处理INSERT时不对JDBC驱动程序的行为做任何假设.它通过以下方式避免了潜在的INSERT错误:

• 在表中查询当前数据集中的任何现有mail_id值，
• 记下那些确实存在的mail_id值的相应id值，
• 插入不存在的mail_id值，并检索(新的)id值，然后
• 将行插入另一个表(inv_table).

 try (Connection dbConn = DriverManager.getConnection(myConnectionString, "root", "usbw")) {
    dbConn.setAutoCommit(false);

    // test data and setup
    Long aid = 123L;
    List<MailidInvitation> invitationList = new ArrayList<MailidInvitation>();
    invitationList.add(new MailidInvitation(13L));
    invitationList.add(new MailidInvitation(11L));
    invitationList.add(new MailidInvitation(12L));
    // remove stuff from previous test run
    try (Statement s = dbConn.createStatement()) {
        s.executeUpdate("DELETE FROM mail_contacts WHERE mail_id IN (11,13)");
    }
    try (PreparedStatement ps = dbConn.prepareStatement(
            "DELETE FROM inv_table WHERE aid=?")) {
        ps.setLong(1, aid);
        ps.executeUpdate();
    }

    // real code starts here
    //
    // create a Map to hold `mail_id` and their corresponding `id` values 
    Map<Long, Long> mailIdMap = new TreeMap<Long, Long>();
    for (MailidInvitation a : invitationList) {
        // mail_id, id (id is null for now)
        mailIdMap.put(a.getId(), null);
    }

    // build an SQL statement to retrieve any existing values
    StringBuilder sb = new StringBuilder(
            "SELECT id, mail_id " +
            "FROM mail_contacts " +
            "WHERE mail_id IN (");
    int n = 0;
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        if (n++ > 0) sb.append(',');
        sb.append(entry.getKey());
    }
    sb.append(')');
    String sql = sb.toString();

    // run the query and save the results (if any) to the Map
    try (Statement s = dbConn.createStatement()) {
        // <demo>
        System.out.println(sql);
        // </demo>
        try (ResultSet rs = s.executeQuery(sql)) {
            while (rs.next()) {
                mailIdMap.put(rs.getLong("mail_id"), rs.getLong("id"));
            }
        }
    }

    // <demo>
    System.out.println();
    System.out.println("mailIdMap now contains:");
    // </demo>

    // build a list of the `mail_id` values to INSERT (where id == null)
    //     ... and print the existing mailIdMap values for demo purposes
    List<Long> mailIdsToInsert = new ArrayList<Long>();
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        String idValue = "";  // <demo />
        if (entry.getValue() == null) {
            mailIdsToInsert.add(entry.getKey());
            // <demo>
            idValue = "null";  
        } else {
            idValue = entry.getValue().toString();
            // </demo>
        }
        // <demo>
        System.out.println(String.format(
                "    %d - %s", 
                entry.getKey(),
                idValue));
        // </demo>
    }

    // batch insert `mail_id` values that don't already exist
    try (PreparedStatement ps = dbConn.prepareStatement(
            "INSERT INTO mail_contacts (mail_id) VALUES (?)", 
            PreparedStatement.RETURN_GENERATED_KEYS)) {
        for (Long mid : mailIdsToInsert) {
            ps.setLong(1, mid);
            ps.addBatch();
        }
        ps.executeBatch();
        // get generated keys and insert them into the Map
        try (ResultSet rs = ps.getGeneratedKeys()) {
            n = 0;
            while (rs.next()) {
                mailIdMap.put(mailIdsToInsert.get(n++), rs.getLong(1));
            }
        }
    }

    // <demo>
    System.out.println();
    System.out.println("After INSERT INTO mail_contacts, mailIdMap now contains:");
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        System.out.println(String.format(
                "    %d - %s", 
                entry.getKey(),
                entry.getValue()));
    }
    // </demo>

    // now insert the `inv_table` rows
    try (PreparedStatement ps = dbConn.prepareStatement(
            "INSERT INTO inv_table (mid, aid) VALUES (?,?)")) {
        ps.setLong(2, aid);
        for (MailidInvitation a : invitationList) {
            ps.setLong(1, mailIdMap.get(a.getId()));
            ps.addBatch();
        }
        ps.executeBatch();
    }
    dbConn.commit();
}
 

结果控制台输出如下:

 SELECT id, mail_id FROM mail_contacts WHERE mail_id IN (11,12,13)

mailIdMap now contains:
    11 - null
    12 - 1
    13 - null

After INSERT INTO mail_contacts, mailIdMap now contains:
    11 - 15
    12 - 1
    13 - 16
 

如果批处理中的一个或多个语句失败，则某些JDBC驱动程序允许批处理继续.例如，在MySQL Connector/J中，选项为continueBatchOnError，默认情况下为true.在这些情况下，另一种方法是尝试插入所有mail_id值，并检查Batch返回的更新计数.成功的INSERT将返回UpdateCount为1，而由于现有mail_id而失败的INSERT将返回EXECUTE_FAILED(-3).然后，我们可以通过.getGeneratedKeys()检索成功的INSERT的(新)id值，然后继续构建SELECT语句以返回并检索已经存在的mail_id条目的id值.

所以这样的代码

 // create a Map to hold `mail_id` and their corresponding `id` values 
Map<Long, Long> mailIdMap = new TreeMap<Long, Long>();
for (MailidInvitation a : invitationList) {
    // mail_id, id (id is null for now)
    mailIdMap.put(a.getId(), null);
}

// try INSERTing all `mail_id` values
try (PreparedStatement ps = dbConn.prepareStatement(
        "INSERT INTO mail_contacts (mail_id) VALUES (?)", 
        PreparedStatement.RETURN_GENERATED_KEYS)) {
    for (Long mid : mailIdMap.keySet()) {
        ps.setLong(1, mid);
        ps.addBatch();
    }
    int[] updateCounts = null;
    try {
        updateCounts = ps.executeBatch();
    } catch (BatchUpdateException bue) {
        updateCounts = bue.getUpdateCounts();
    }
    // get generated keys and insert them into the Map
    try (ResultSet rs = ps.getGeneratedKeys()) {
        int i = 0;
        for (Long mid : mailIdMap.keySet()) {
            if (updateCounts[i++] == 1) {
                rs.next();
                mailIdMap.put(mid, rs.getLong(1));
            }
        }
    }
}

// <demo>
System.out.println("mailIdMap now contains:");
// </demo>

// build a SELECT statement to get the `id` values for `mail_id`s that already existed
//     ... and print the existing mailIdMap values for demo purposes
StringBuilder sb = new StringBuilder(
        "SELECT id, mail_id " +
        "FROM mail_contacts " +
        "WHERE mail_id IN (");
int n = 0;
for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
    String idValue = "";  // <demo />
    if (entry.getValue() == null) {
        if (n++ > 0) sb.append(',');
        sb.append(entry.getKey());
        // <demo>
        idValue = "null";  
    } else {
        idValue = entry.getValue().toString();
        // </demo>
    }
    // <demo>
    System.out.println(String.format(
            "    %d - %s", 
            entry.getKey(),
            idValue));
    // </demo>
}
sb.append(')');
String sql = sb.toString();

// <demo>
System.out.println();
System.out.println(sql);
// </demo>
 

将产生如下控制台输出:

 mailIdMap now contains:
    11 - 17
    12 - null
    13 - 19

SELECT id, mail_id FROM mail_contacts WHERE mail_id IN (12)
 

其余过程与之前相同:

• 填写其余的mailIdMap条目，然后
• 使用mailIdMap中的id值处理另一个表上的INSERT.

I have following table structure

  1. mail_contacts(id,mail_id);//stores mail id, id is primary key
                                  mail_id is unique key
  2. inv_table(mid,aid,add_date);//mapped mail id with user id. mid is 
                                   mail_contacts id and aid is userid
                                   (mid,aid) is primary key  

I am storing multiple mail id in mail_contacts after insertion I am getting its inserted id and storing it with aid to inv_table. If any of mail_id is not stored in mail_contacts then it is working properly. but if mail_id stored in mail_contacts then insertion get terminated.

What I want If mail_id stored in mail_contacts then it should fetch its id to store in inv_table.

I am trying

    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    ResultSet rs = null, res = null;
    Connection con = null;
    String status = "success";
    ArrayList ar = null;
    Invitation invi = null;
    int i = 0;

    public String insert(List<MailidInvitation> invitationList, Long aid) {
        con = ConnectionFactory.getConnection();
        try {
            ps = con.prepareStatement("insert into mail_contacts(mail_id)"
                    + " values(?)", Statement.RETURN_GENERATED_KEYS);

            for (MailidInvitation a : invitationList) {
                ps.setString(1, a.getMailId());
                ps.addBatch();
            }
            ps.executeBatch();
            ResultSet keys = ps.getGeneratedKeys();
            while (keys.next()) {
                long id = keys.getLong(1);
                invitationList.get(i).setId(id);
                System.out.println("generated id is " + id);
                i++;
            }

            ps1 = con.prepareStatement("insert into inv_table(mid,aid)"
                    + " values(?,?)");
            for (MailidInvitation a : invitationList) {
                ps1.setLong(1, a.getId());
                ps1.setLong(2, aid);
                ps1.addBatch();
            }
            ps1.executeBatch();
        } catch (SQLException e) {
            status = "failure";
            System.out.println("SQLException1 " + e);
        } finally {
            try {
                con.close();
            } catch (SQLException e) {
                System.out.println("SQLException2 " + e);
            }
        }
        System.out.println("Status is " + status);

        return status;
    }

how to do this?

What is the best way to solve this kind of problem

I am using mysql database

解决方案

This first approach makes no assumptions about the behaviour of the JDBC driver when handling batch INSERTs. It avoids potential INSERT errors by

• querying the table for any existing mail_id values in our current data set,
• makes note of the corresponding id value for those mail_id values that do exist,
• INSERTs the mail_id values that don't exist, and retrieves their (new) id values, and then
• inserts the rows in the other table (inv_table).

try (Connection dbConn = DriverManager.getConnection(myConnectionString, "root", "usbw")) {
    dbConn.setAutoCommit(false);

    // test data and setup
    Long aid = 123L;
    List<MailidInvitation> invitationList = new ArrayList<MailidInvitation>();
    invitationList.add(new MailidInvitation(13L));
    invitationList.add(new MailidInvitation(11L));
    invitationList.add(new MailidInvitation(12L));
    // remove stuff from previous test run
    try (Statement s = dbConn.createStatement()) {
        s.executeUpdate("DELETE FROM mail_contacts WHERE mail_id IN (11,13)");
    }
    try (PreparedStatement ps = dbConn.prepareStatement(
            "DELETE FROM inv_table WHERE aid=?")) {
        ps.setLong(1, aid);
        ps.executeUpdate();
    }

    // real code starts here
    //
    // create a Map to hold `mail_id` and their corresponding `id` values 
    Map<Long, Long> mailIdMap = new TreeMap<Long, Long>();
    for (MailidInvitation a : invitationList) {
        // mail_id, id (id is null for now)
        mailIdMap.put(a.getId(), null);
    }

    // build an SQL statement to retrieve any existing values
    StringBuilder sb = new StringBuilder(
            "SELECT id, mail_id " +
            "FROM mail_contacts " +
            "WHERE mail_id IN (");
    int n = 0;
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        if (n++ > 0) sb.append(',');
        sb.append(entry.getKey());
    }
    sb.append(')');
    String sql = sb.toString();

    // run the query and save the results (if any) to the Map
    try (Statement s = dbConn.createStatement()) {
        // <demo>
        System.out.println(sql);
        // </demo>
        try (ResultSet rs = s.executeQuery(sql)) {
            while (rs.next()) {
                mailIdMap.put(rs.getLong("mail_id"), rs.getLong("id"));
            }
        }
    }

    // <demo>
    System.out.println();
    System.out.println("mailIdMap now contains:");
    // </demo>

    // build a list of the `mail_id` values to INSERT (where id == null)
    //     ... and print the existing mailIdMap values for demo purposes
    List<Long> mailIdsToInsert = new ArrayList<Long>();
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        String idValue = "";  // <demo />
        if (entry.getValue() == null) {
            mailIdsToInsert.add(entry.getKey());
            // <demo>
            idValue = "null";  
        } else {
            idValue = entry.getValue().toString();
            // </demo>
        }
        // <demo>
        System.out.println(String.format(
                "    %d - %s", 
                entry.getKey(),
                idValue));
        // </demo>
    }

    // batch insert `mail_id` values that don't already exist
    try (PreparedStatement ps = dbConn.prepareStatement(
            "INSERT INTO mail_contacts (mail_id) VALUES (?)", 
            PreparedStatement.RETURN_GENERATED_KEYS)) {
        for (Long mid : mailIdsToInsert) {
            ps.setLong(1, mid);
            ps.addBatch();
        }
        ps.executeBatch();
        // get generated keys and insert them into the Map
        try (ResultSet rs = ps.getGeneratedKeys()) {
            n = 0;
            while (rs.next()) {
                mailIdMap.put(mailIdsToInsert.get(n++), rs.getLong(1));
            }
        }
    }

    // <demo>
    System.out.println();
    System.out.println("After INSERT INTO mail_contacts, mailIdMap now contains:");
    for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
        System.out.println(String.format(
                "    %d - %s", 
                entry.getKey(),
                entry.getValue()));
    }
    // </demo>

    // now insert the `inv_table` rows
    try (PreparedStatement ps = dbConn.prepareStatement(
            "INSERT INTO inv_table (mid, aid) VALUES (?,?)")) {
        ps.setLong(2, aid);
        for (MailidInvitation a : invitationList) {
            ps.setLong(1, mailIdMap.get(a.getId()));
            ps.addBatch();
        }
        ps.executeBatch();
    }
    dbConn.commit();
}

The resulting console output looks like this:

SELECT id, mail_id FROM mail_contacts WHERE mail_id IN (11,12,13)

mailIdMap now contains:
    11 - null
    12 - 1
    13 - null

After INSERT INTO mail_contacts, mailIdMap now contains:
    11 - 15
    12 - 1
    13 - 16

Some JDBC drivers allow a Batch to continue if one or more statements in the Batch fails. For example, in MySQL Connector/J the option is continueBatchOnError which is true by default. In those cases an alternative approach would be to try and INSERT all of the mail_id values and check the update counts returned by the Batch. Successful INSERTs would return an UpdateCount of 1, while INSERTs that fail because of an existing mail_id would return EXECUTE_FAILED (-3). Then we could retrieve the (new) id values for the successful INSERTs via .getGeneratedKeys(), and then proceed to build a SELECT statement to go back and retrieve the id values for mail_id entries that already existed.

So code like this

// create a Map to hold `mail_id` and their corresponding `id` values 
Map<Long, Long> mailIdMap = new TreeMap<Long, Long>();
for (MailidInvitation a : invitationList) {
    // mail_id, id (id is null for now)
    mailIdMap.put(a.getId(), null);
}

// try INSERTing all `mail_id` values
try (PreparedStatement ps = dbConn.prepareStatement(
        "INSERT INTO mail_contacts (mail_id) VALUES (?)", 
        PreparedStatement.RETURN_GENERATED_KEYS)) {
    for (Long mid : mailIdMap.keySet()) {
        ps.setLong(1, mid);
        ps.addBatch();
    }
    int[] updateCounts = null;
    try {
        updateCounts = ps.executeBatch();
    } catch (BatchUpdateException bue) {
        updateCounts = bue.getUpdateCounts();
    }
    // get generated keys and insert them into the Map
    try (ResultSet rs = ps.getGeneratedKeys()) {
        int i = 0;
        for (Long mid : mailIdMap.keySet()) {
            if (updateCounts[i++] == 1) {
                rs.next();
                mailIdMap.put(mid, rs.getLong(1));
            }
        }
    }
}

// <demo>
System.out.println("mailIdMap now contains:");
// </demo>

// build a SELECT statement to get the `id` values for `mail_id`s that already existed
//     ... and print the existing mailIdMap values for demo purposes
StringBuilder sb = new StringBuilder(
        "SELECT id, mail_id " +
        "FROM mail_contacts " +
        "WHERE mail_id IN (");
int n = 0;
for (Map.Entry<Long, Long> entry : mailIdMap.entrySet()) {
    String idValue = "";  // <demo />
    if (entry.getValue() == null) {
        if (n++ > 0) sb.append(',');
        sb.append(entry.getKey());
        // <demo>
        idValue = "null";  
    } else {
        idValue = entry.getValue().toString();
        // </demo>
    }
    // <demo>
    System.out.println(String.format(
            "    %d - %s", 
            entry.getKey(),
            idValue));
    // </demo>
}
sb.append(')');
String sql = sb.toString();

// <demo>
System.out.println();
System.out.println(sql);
// </demo>

would produce console output like this:

mailIdMap now contains:
    11 - 17
    12 - null
    13 - 19

SELECT id, mail_id FROM mail_contacts WHERE mail_id IN (12)

The rest of the process would be the same as before:

• fill in the remaining mailIdMap entries, and
• process the INSERTs on the other table using the id values in mailIdMap.

这篇关于在表中添加具有唯一列的行:获取现有ID值以及新创建的ID值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！