How to store data to external sqlite database and display in RecyclerView

Question

I have created an external sqlite database with book information stored in it. Its store in the asset folder. I have created a book object, but now i am not sure how to store values from database in it. I have used a list function to retrieve the values from database using the sqliteaccesshelper library and called it in the main activity. It would really help if someone suggests me how to store the values in my book object. This is the tutorial that i have followed.

My book object is book() with four parameters.

public book(String title, String author, byte[] image, String issue) {
        Title = title;
        Author = author;
        Image = image;
        Issue = issue;

    }

This is the Database Function. Note that its not proper and i am not sure how to make it store the value in my Book object.

public List<String> getdetails() {
        List<String> lstBook = new ArrayList<>();
        Cursor cursor = database.rawQuery("SELECT Book_name, Author, Book_cover, Issue_status FROM books", null);
        cursor.moveToFirst();
        while (!cursor.isAfterLast()) {
//This line is showing an error.
            lstBook.add(cursor.getString(cursor.getColumnIndex("Book_name")), cursor.getString(cursor.getColumnIndex("Author")), cursor.getBlob(cursor.getColumnIndex("Book_cover")), cursor.getString(cursor.getColumnIndex("Issue_Status")));

            book Book = new book(); //this is the book object
            Book.setTitle(cursor.getString(0));
            Book.setAuthor(cursor.getString(1));
            Book.setImage(cursor.getBlob(2));
            Book.setIssue(cursor.getString(3));
            cursor.moveToNext();
        }
        cursor.close();
        return lstBook; 
    }

This is my MainActivity

// Open the database
        databaseAccess.open();

        List<String> lstBook = databaseAccess.getdetails();



        RecyclerView myrv = (RecyclerView) findViewById(R.id.recyclerview_id);
        RecyclerViewAdapter myAdapter = new RecyclerViewAdapter(this, lstBook);
        myrv.setLayoutManager(new GridLayoutManager(this, 2));
        myrv.setAdapter(myAdapter;

        // Close the database
        databaseAccess.close();
    }

And this is my RecyclerView Adapter.(I am not sure about the set image part though)

@Override
    public void onBindViewHolder(MyViewHolder holder, int position) {

        holder.bookname.setText(mData.get(position).getTitle());
        holder.bookauthor.setText(mData.get(position).getAuthor());
        byte[] data = mData.get(position).getImage(); Bitmap image = toBitmap(data);
        holder.bookimg.setImageBitmap(image);
        holder.issuestatus.setText(mData.get(position).getIssue());

    }

either add some code for help or this might be helpful https://stackoverflow.com/questions/18971434/how-to-insert-value-in-data-base-using-sqlite-in-android — Ujjwal Jung Thapa, Mar 02 '19 at 05:37

score 0 · Answer 1 · answered Mar 02 '19 at 04:52

You can try with third party drivers, years back I connected with MSSQL with one of these:

http://www.sqlitetutorial.net/sqlite-java/sqlite-jdbc-driver/

https://bitbucket.org/xerial/sqlite-jdbc/src/default/

you can try these, or search others.

I have not used any of these for sqlite db stored in assets, you try and play around.

Hope it helps.

Prince Persia · Answer 2 · 2019-03-03T17:51:02.777

I figured it out and it works perfectly. This video helped me. I just followed his way of adding books to the list. I created a function in my DatabaseAccess class and just accessed it in the Required Activity. This is the function.

public ArrayList<book> getdetails(String query, String category) {
        ArrayList<book> list = new ArrayList<book>();
        String[] where= new String[]{category};
        Cursor cursor = database.rawQuery(query, where);
        if (cursor!=null) {
            if (cursor.moveToFirst()) {
                do {
                    book Book = new book();
                    Book.setTitle(cursor.getString(1));
                    Book.setAuthor(cursor.getString(6));
                    Book.setImage(cursor.getBlob(2));
                    Book.setPublisher(cursor.getString(3));
                    Book.setPublish_date(cursor.getString(4));
                    Book.setISBN(cursor.getString(5));
                    Book.setDescription(cursor.getString(9));
                    Book.setIssue(cursor.getString(10));
                    list.add(Book);
                }while (cursor.moveToNext());
            }
        }cursor.close();
        return list;
    }

How to store data to external sqlite database and display in RecyclerView

2 Answers2