How to create and access arraylist(in java) of item rows from mysql database?

Question

I have a clas BookBean, where I have the fields from database: id, title and author. I want to create an ArrayList bookList of BookBean type, where to get each row from database. For example, bookList[0] should point to the first row from database: id=1, title=first title, author=first author.

I have tried declaring a BookBean variable and an ArrayList:

static ArrayList<BookBean> listBooks = new ArrayList<>();
static BookBean bookBean = new BookBean(1,"title", "author");

This is my function where I get items from database. I can access the items inside the ResultSet search, but not outside it. How can I store the items into the arraylist properly?

public static void generateBookList(){
        try {
            Connection connection = ConnectToDatabase.createConnection();
            if (connection != null) {
                PreparedStatement preparedStatement = connection.prepareStatement("SELECT * from book ");
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    bookBean.setId(resultSet.getInt("id_book"));
                    bookBean.setTitle(resultSet.getString("title"));
                    bookBean.setAuthor(resultSet.getString("author"));
                    listBooks.add(bookBean);

                    System.out.println(listBooks.get(0).getId());
                    System.out.println(listBooks.get(0).getTitle());
                    System.out.println(listBooks.get(0).getAuthor());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Your list must contain several books. Not just one. So you need to create a new book at each iteration of your loop, and ad this new book in the list (which probably shouldn't be static either, but instead created and returned by your method. — JB Nizet
– JB Nizet, Commented Apr 13, 2019 at 9:22

SamratV · Accepted Answer · 2019-04-13 09:21:23Z

You need to create new OBJECT of BookBean everytime you insert into ArrayList.

public static void generateBookList(){
        try {
            Connection connection = ConnectToDatabase.createConnection();
            if (connection != null) {
                PreparedStatement preparedStatement = connection.prepareStatement("SELECT * from book ");
                ResultSet resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    //You are actually using the same object again and again.
                    // Following line is important.
                    BookBean x = new BookBean(resultSet.getInt("id_book"), resultSet.getString("title"), resultSet.getString("author"));
                    listBooks.add(x);

                    System.out.println(listBooks.get(0).getId());
                    System.out.println(listBooks.get(0).getTitle());
                    System.out.println(listBooks.get(0).getAuthor());
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Collectives™ on Stack Overflow

How to create and access arraylist(in java) of item rows from mysql database?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related