Inserting ArrayList of String into a table in MySQL JDBC

Question

I am trying to insert a Collection of Strings while looping into a table in MySQL using PreparedStatement.

Categories table contains ID (AUTOINCREMENT) and NAME.

My Method

private ConnectionPool pool = ConnectionPool.getInstance();

@Override
public void addCategories(List<Category> category) throws SQLException {
    Connection connection = pool.getConnection();

    category = new ArrayList<>();

    try {
    PreparedStatement statement = connection
            .prepareStatement("insert into `couponsystem`.`categories` (NAME) VALUES (?)");
    for (Category categories : category) {

        statement.setString(1, category.toString());
        category.add(categories);

        statement.executeUpdate();
    }
    
    } finally {
        pool.restoreConnection(connection);
    }

}

Category class

public enum Category {

FOOD(1), ELECTRICITY(2), RESTAURANT(3), VACATION(4), HOTEL(5);

private Category(final int cat) {
    this.cat = cat;
}

private int cat;

public int getIDX() {
    return cat;
}

private Category(String cat1) {
    this.cat1 = cat1;
}

private String cat1;

public String getName() {
    return cat1;
}

}

Program with MAIN

List<Category> cats = new ArrayList<Category>(EnumSet.allOf(Category.class));
cat.addCategories(cats);

I do not get any exceptions, however list stays null. I am kind of new to JDBC and cant seem to find the issue to resolve it.

thanks.

score 0 · Accepted Answer · answered Feb 03 '21 at 18:21

For bulk insert of your categories you need to use batches: https://stackoverflow.com/a/4355097/6916890

As mentioned in answer above:

public void save(List<Entity> entities) throws SQLException {
    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT);
    ) {
        int i = 0;

        for (Entity entity : entities) {
            statement.setString(1, entity.getSomeProperty());
            // ...

            statement.addBatch();
            i++;

            if (i % 1000 == 0 || i == entities.size()) {
                statement.executeBatch(); // Execute every 1000 items.
            }
        }
    }
}

Your case may work only with a single insert statement.

This actually worked. first time I learned about addBatch. thanks — Daniel R, Feb 03 '21 at 18:34

score 0 · Answer 2 · answered Feb 03 '21 at 18:21

0

Pay attention to this line

category = new ArrayList<>();

You clear the list before data processing. And please work on your code readability. Read about name conventions. For example List object should be called 'categories', and item - category.

answered Feb 03 '21 at 18:21

sergio1399

11
2

Inserting ArrayList of String into a table in MySQL JDBC

2 Answers2