java - 使用 executeBatch() 获取插入的(或现有的)id

Question

我正在尝试将一些词插入数据库并返回新插入的 ID 或现有 ID(如果该词已在数据库中)。

我发现我可以使用 PreparedStatement 并包括 Statement.RETURN_GENERATED_KEYS 来做到这一点。但是 PreparedStatement 非常慢。我需要一次插入 5000 个单词。我可以通过在 for 循环中运行单个查询来实现它的另一种方法:

public ArrayList<Integer> addWords(ArrayList<String[]> allTermsForTag) {
    ArrayList ids = new ArrayList<Integer>();
    ResultSet rs = null;
    try{
        Statement st = connection.createStatement();
        for (String[] articleTerms: allTermsForTag) {
            for(String term: articleTerms) {
                String query = "WITH a AS (INSERT INTO tag (name) SELECT '"+term+"' WHERE NOT EXISTS (SELECT name FROM tag WHERE name = '"+term+"') " +
                        "RETURNING id) SELECT id FROM a UNION SELECT id FROM tag WHERE name = '"+term+"'";
                rs = st.executeQuery(query);
                while (rs.next())
                {
                    int id = rs.getInt(1);
                    ids.add(id);
                    System.out.printf("id: "+id);

                }
            }
        }
        rs.close();
        st.close();
    }catch(SQLException e){
        System.out.println("SQL exception was raised while performing SELECT: "+e);
    }
    return ids;


}

这很好地满足了我的需要，但也太慢了。

我写的另一个方法使用 executeBatch()，但是，它不返回 ids:

public ArrayList<Integer> addWords(ArrayList<String[]> allTermsForTag){
        ResultSet rs = null;
        ArrayList ids = new ArrayList<Integer>();

        try{
            Statement st = connection.createStatement();
            for (String[] articleTerms: allTermsForTag) {
                for(String term: articleTerms) {
                    String query = "WITH a AS (INSERT INTO tag (name) SELECT '"+term+"' WHERE NOT EXISTS (SELECT name FROM tag WHERE name = '"+term+"') " +
                            "RETURNING id) SELECT id FROM a UNION SELECT id FROM tag WHERE name = '"+term+"'";
                    st.addBatch(query);
                }
                st.executeBatch();
                rs = st.getGeneratedKeys();
                while (rs.next()) {
                    int id = rs.getInt(1);
                    ids.add(id);
                }
            }
            st.close();
            return ids;
        }catch (SQLException e){
            System.out.println("SQL exception was raised while performing batch INSERT: "+e.getNextException());
            System.out.println("dub");
        }
        return null;
    }

所以问题是 - 如何在使用 executeBatch() 时获取 ID，或者如果这不可能，如何解决这个问题？我需要它尽可能快地工作，因为会有很多 INSERT 操作和大量数据。谢谢!

Answer 1

Set set = new HashSet();
    try {
        PreparedStatement ps = cn.prepareStatement("delete  from myTable where... ", 
        Statement.RETURN_GENERATED_KEYS);
        ps.setInt(1,200);
        ps.setInt(2,262);
        ps.setString(3, "108gf99");
        ps.addBatch();
        ps.setInt(1,200);
        ps.setInt(2,250);
        ps.setString(3, "hgfha");
        ps.addBatch();
        ps.executeBatch();
        ResultSet rs = ps.getGeneratedKeys();
        while (rs.next()){

            set.addAll(Collections.singleton(rs.getLong(1)));
        }

        System.out.println(set);

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