CODEWITHSUNDEEP
javamysql
Jacob Macallan
Jacob Macallan

Reputation: 979

Is there a reason why executeBatch does not return multiple ResultSets

I was going through some old code someone else wrote and noticed that the performance could have been increased if they had used batch update rather than executing one update at a time (we're currently having some lag issues with issuing saves with > 150 players on a game we're writing).

The issue, however, is that I need to grab the ResultSets from the batch, but it only returns one.

        try (PreparedStatement ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
             Statement.RETURN_GENERATED_KEYS);
             PreparedStatement pse = con.prepareStatement(
             "INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)")) {

            HashMap<Integer, List<Equip>> equips = new HashMap<Integer, List<Equip>>();

            if (!items.isEmpty()) {
                for (Pair<Item, MapleInventoryType> pair : items) {
                    Item item = pair.getLeft();
                    if (item.disappearsAtLogout())
                        continue;
                    MapleInventoryType mit = pair.getRight();
                    ps.setInt(1, value);
                    ps.setString(2, account ? null : String.valueOf(id));
                    ps.setString(3, account ? String.valueOf(id) : null);
                    ps.setInt(4, item.getItemId());
                    ps.setInt(5, mit.getType());
                    ps.setInt(6, item.getPosition());
                    ps.setInt(7, item.getQuantity());
                    ps.setString(8, item.getOwner());
                    ps.setInt(9, item.getPetId());
                    ps.setInt(10, item.getFlag());
                    ps.setLong(11, item.getExpiration());
                    ps.setString(12, item.getGiftFrom());
                    ps.addBatch();

                    if (item instanceof Equip) {
                        if (!equips.containsKey(item.getItemId()))
                            equips.put(item.getItemId(), new ArrayList<Equip>());
                        equips.get(item.getItemId()).add((Equip) item);
                    }
                }
                ps.executeBatch();

                try (ResultSet rs = ps.getGeneratedKeys()) {
                    while (rs.next()) {
                        MapleInventoryType mit = MapleInventoryType.getByType((byte) rs.getInt(5));
                        if (mit.equals(MapleInventoryType.EQUIP) || mit.equals(MapleInventoryType.EQUIPPED)) {
                            Equip equip = equips.get(rs.getInt(4)).get(0);
                            pse.setInt(1, rs.getInt(1));
                            pse.setInt(2, equip.getUpgradeSlots());
                            pse.setInt(3, equip.getLevel());
                            pse.setInt(4, equip.getStr());
                            pse.setInt(5, equip.getDex());
                            pse.setInt(6, equip.getInt());
                            pse.setInt(7, equip.getLuk());
                            pse.setInt(8, equip.getHp());
                            pse.setInt(9, equip.getMp());
                            pse.setInt(10, equip.getWatk());
                            pse.setInt(11, equip.getMatk());
                            pse.setInt(12, equip.getWdef());
                            pse.setInt(13, equip.getMdef());
                            pse.setInt(14, equip.getAcc());
                            pse.setInt(15, equip.getAvoid());
                            pse.setInt(16, equip.getHands());
                            pse.setInt(17, equip.getSpeed());
                            pse.setInt(18, equip.getJump());
                            pse.setInt(19, 0);
                            pse.setInt(20, equip.getVicious());
                            pse.setInt(21, equip.getItemLevel());
                            pse.setInt(22, equip.getItemExp());
                            pse.setInt(23, equip.getRingId());
                            equips.get(rs.getInt(4)).remove(0);
                            pse.addBatch();

                        }
                    }
                    pse.executeBatch();
                }
            }
        }

What you basically take away from this is that the first run of adding inventory items to the database is added. This batch relates to the table inventoryitems. However, we also want to store information regarding to Equipment, which extends from the Item class. At first, my solution to increasing speed was to batch execute the inventoryitems, and then batch execute inventoryequipment. The issue, however, is that executeBatch() only returns one ResultSet. Is there a solution to this? I need to grab the ResultSets generated by executeBatch to grab the unique identifier along with the inventoryitemid to insert as a value.

Upvotes: 0

Views: 816

Answers (1)

Porkko M
Porkko M

Reputation: 307

The return type of execute batch is int array,which contains update counts of the statement.

Refer Here https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html

Upvotes: 1

Related Questions