There is a database with tables "books", "books_authors", "authors". "books_authors" is used to display many-to-many relationships. It is necessary to obtain from the database a list of books within which there will be authors. Here is how I do it:

public ArrayList<Book> getBooks() { Cursor booksCursor = mDB.rawQuery(TOP_SALES_BOOKS_QUERY, null); ArrayList<Book> books = new ArrayList<>(); if(booksCursor.moveToFirst()) { int idIndex = booksCursor.getColumnIndex(Book.ID_TAG); int nameIndex = booksCursor.getColumnIndex(Book.NAME_TAG); //... do { int bookId = booksCursor.getInt(idIndex); String bookName = booksCursor.getString(nameIndex); //... Cursor authorsBooksCursor = mDB.rawQuery(AUTHORS_BY_BOOK_ID_QUERY, new String[] {String.valueOf(bookId)}); ArrayList<Author> authors = new ArrayList<>(); if(authorsBooksCursor.moveToFirst()) { int authorNameIndex = authorsBooksCursor.getColumnIndex(Author.NAME_TAG); do { String authorName = authorsBooksCursor.getString(authorNameIndex); authors.add(new Author(authorName)); } while (authorsBooksCursor.moveToNext()); } authorsBooksCursor.close(); books.add(new Book(bookId, bookName, authors, ...)); } while (booksCursor.moveToNext()); } booksCursor.close(); return books; }

The method is executed ~ 40 seconds. If you don’t get authorsBooksCursor, then ~ 1 second. How can I improve a method or query for faster execution?

Here are the queries themselves:

 String TOP_SALES_BOOKS_QUERY = "SELECT * FROM " + Book.TABLE_NAME; String AUTHORS_BY_BOOK_ID_QUERY = "SELECT BAT." + DEFAULT_ID_TAG + ", AT." + Author.NAME_TAG + " FROM " + BOOKS_AUTHORS_TABLE_NAME + " AS BAT INNER JOIN " + Author.TABLE_NAME + " AS AT ON BAT." + Author.ID_TAG + " = AT." + Author.ID_TAG + " WHERE BAT." + Book.ID_TAG + " = ?";
  • Can query texts TOP_SALES_BOOKS_QUERY and AUTHORS_BY_BOOK_ID_QUERY? As a rule, it is better to do everything in one request. - iksuy
  • make a query to books_authors , sort by book title, and add authors to the book sequentially. So there will be one request. - Vladyslav Matviienko
  • @iksuy added. - iamtihonov
  • Make one request with a double join - etki
  • @Etki, I don’t understand much, what kind of cursor will I get then? In which there will be about such drains (author 1, book 1; author 2, book 1; author 3, book 2; author 4, book 2; ...)? - iamtihonov

1 answer 1

 private Map<Integer, Book> booksMap = new HashMap<>(); ... //Запрос String query = "SELECT ba.book_id, b.name, a.name FROM books_authors ba INNER JOIN authors a ON ba.author_id = a.id INNER JOIN books b ON ba.book_id = b.id" ... //Заполнение if(cursor.moveToFirst()) { do { int id = cursor.getInt(0); Book book = booksMap.get(id); if(book == null) { book = new Book(); book.setName(cursor.getString(1)); booksMap.put(id, book); } String authorName = cursor.getString(2); book.addAuthor(authorName); } while (authorsBooksCursor.moveToNext()); }

He wrote on his knee. The idea expressed, but not the fact that compile. The request was written figuratively, substitute your names into it.

  • Thanks for the help! - iamtihonov