CODEWITHSUNDEEP
javamysql
HeyAwesomePeople
HeyAwesomePeople

Reputation: 43

MySQL mass insert acting weird

I'm working with MySQL to upload around 10000 entries into the database. Here is my code:

public void addChests(HashMap<Location, ChestGeneratorType> map) {

    java.sql.PreparedStatement statement;
    try {
        statement = plugin.sql.openConnection().prepareStatement("INSERT INTO chests (Location, Generator, ToAdd) VALUES (?,?,?)");

        for (Location l : map.keySet()) {
            map.get(l).chests.add(new Chests(l, map.get(l), 0));

            statement.setString(1, Utils.locationToString(l));
            statement.setString(2, map.get(l).configName);
            statement.setInt(3, 0);

            statement.addBatch();
        }

        statement.executeBatch();
    } catch (SQLException sqlE) {
        sqlE.printStackTrace();
    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    }

}

This piece of code should be going through my map and inserting a new row for each value in the map. The map has around 10000 entries, and I tested that by dumping the map into a text file, one per line, and I got 9800+ entries. So I know my HashMap is full. But what is happening is it's only putting in 1513 entries, not 9800+. And whats weird is in all those entries, the "generator" value is "irongenerator". It's only inserting it if the ChestGeneratorType.configName is irongenerator? Why?

MySQL database: https://gyazo.com/5a0686a48a6238d2d958f7a7411696b2

Better explaination(I'm working in a bukkit plugin): https://www.spigotmc.org/threads/mysql-mass-insert-acting-weird.99373/

Thanks

Upvotes: 0

Views: 45

Answers (2)

HeyAwesomePeople
HeyAwesomePeople

Reputation: 43

I found the issue..

I had another task which would TRUNCATE the table to then insert the values again. It would TRUNCATE the table right after I inserted all the irongenerators, which explains why the column generator were all the same..

Thanks guys.

Upvotes: 1

Andreas
Andreas

Reputation: 159086

You likely have a constraint (FOREIGN KEY or CHECK) on the Generator column, or your other values are too long for the VARCHAR type, or the values are null and Generator is NOT NULL.

This is causing some of the INSERT statements to fail, but executeBatch() is allowed to continue (feature since Java 1.3), and will execute all the statements, then throw a BatchUpdateException (subclass of SQLException).

The BatchUpdateException.getUpdateCounts() method can then be called to get an int[], and iterating that array will let you know which statements failed (value EXECUTE_FAILED).

But, you catch that exception, call printStackTrace() (you should have seen this), and then (OH HORROR!) continue normally, allowing addChests() to return normally, and all the successful inserts are committed. OOPS!

Unrelated

You are calling map.get(l) 3 times inside the loop. Very inefficient. Either assign to variable, or, better yet, iterate the map entries, not the keys:

for (Map.Entry<Location, ChestGeneratorType>> entry : map.entrySet()) {
    Location           loc  = entry.getKey();
    ChestGeneratorType type = entry.getValue();
    type.chests.add(new Chests(loc, type, 0));

Upvotes: 0

Related Questions