I am a beginner in android programming and I am trying to store information and image of different books in SQLite. I got some part of the below code from a site. I am able to store the information i.e author and title of the book but don't know how to store an image. I written the code forBook.java and MySQLiteHelper.java but don't know how to store and insert image using SQLite statement in the MainActivity.java.Can someone help?
This is my Book.java
public class Book {
int id;
String title;
String author;
byte[] image;
public Book(){}
public Book(int id,String title, String author, byte[] image) {
this.id=id;
this.title = title;
this.author = author;
this.image=image;
}
public Book(String title, String author,byte[] image) {
this.title = title;
this.author = author;
this.image=image;
}
//getters & setters
// getting ID
public int getId(){
return this.id;
}
// setting id
public void setId(int id){
this.id = id;
}
// getting title
public String getTitle(){
return this.title;
}
// setting title
public void setTitle(String title){
this.title = title;
}
// getting authorname
public String getAuthor(){
return this.author;
}
// setting authorname
public void setAuthor(String author){
this.author = author;
}
//getting image
public byte[] getImage() {
return this.image;
}
//setting image
public void setImage(byte[] image) {
this.image = image;
}
}
This is my MySQLitehelper.java
public class MySQLiteHelper extends SQLiteOpenHelper {
// Database Version
private static final int DATABASE_VERSION = 1;
// Database Name
private static final String DATABASE_NAME = "BookDB";
// Books table name
private static final String TABLE_BOOKS = "books";
// Books Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_TITLE = "title";
private static final String KEY_AUTHOR = "author";
private static final String KEY_IMAGE = "image";
public MySQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
// Creating Tables
@Override
public void onCreate(SQLiteDatabase db) {
// SQL statement to create book table
String CREATE_BOOK_TABLE = "CREATE TABLE" + TABLE_BOOKS+"("+ KEY_ID +" INTEGER PRIMARY KEY,"+ KEY_TITLE+" TEXT,"+ KEY_AUTHOR+ " TEXT," + KEY_IMAGE + " BLOB" + ")";
// create books table
db.execSQL(CREATE_BOOK_TABLE);
}
// Upgrading database
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older books table if existed
db.execSQL("DROP TABLE IF EXISTS" + TABLE_BOOKS );
// create fresh books table
this.onCreate(db);
}
//---------------------------------------------------------------------
//*** CRUD operations (create "add", read "get", update, delete) book + get all books + delete all books */
private static final String[] COLUMNS = {KEY_ID,KEY_TITLE,KEY_AUTHOR,KEY_IMAGE};
public void addBook(Book book){
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
// 2. create ContentValues to add key "column"/value
ContentValues values = new ContentValues();
values.put(KEY_TITLE, book.getTitle()); // get title
values.put(KEY_AUTHOR, book.getAuthor()); // get author
values.put(KEY_IMAGE, book.getImage()); // get author
// 3. insert
db.insert(TABLE_BOOKS, // table
null, //nullColumnHack
values); // key/value -> keys = column names/ values = column values
// 4. close
db.close();
}
public Book getBook(int id){
// 1. get reference to readable DB
SQLiteDatabase db = this.getReadableDatabase();
// 2. build query
Cursor cursor =
db.query(TABLE_BOOKS, // a. table
COLUMNS, // b. column names
" id = ?", // c. selections
new String[] { String.valueOf(id) }, // d. selections args
null, // e. group by
null, // f. having
null, // g. order by
null); // h. limit
// 3. if we got results get the first one
if (cursor != null)
cursor.moveToFirst();
// 4. build book object
Book book = new Book();
book.setId(Integer.parseInt(cursor.getString(0)));
book.setTitle(cursor.getString(1));
book.setAuthor(cursor.getString(2));
book.setImage(cursor.getBlob(3));
// 5. return book
return book;
}
// Get All Books
public List<Book> getAllBooks() {
List<Book> books = new LinkedList<Book>();
// 1. build the query
String query = "SELECT * FROM " + TABLE_BOOKS;
// 2. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
// 3. go over each row, build book and add it to list
Book book = null;
if (cursor.moveToFirst()) {
do {
book = new Book();
book.setId(Integer.parseInt(cursor.getString(0)));
book.setTitle(cursor.getString(1));
book.setAuthor(cursor.getString(2));
book.setImage(cursor.getBlob(3));
// Add book to books
books.add(book);
} while (cursor.moveToNext());
}
// return books
return books;
}
// Updating single book
public int updateBook(Book book) {
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
// 2. create ContentValues to add key "column"/value
ContentValues values = new ContentValues();
values.put("title", book.getTitle()); // get title
values.put("author", book.getAuthor()); // get author
values.put("image", book.getImage()); // get image
// 3. updating row
return db.update(TABLE_BOOKS, //table
values, // column/value
KEY_ID+" = ?", // selections
new String[] { String.valueOf(book.getId()) }); //selection args
}
// Deleting single book
public void deleteBook(Book book) {
// 1. get reference to writable DB
SQLiteDatabase db = this.getWritableDatabase();
// 2. delete
db.delete(TABLE_BOOKS,
KEY_ID+" = ?",
new String[] { String.valueOf(book.getId()) });
// 3. close
db.close();
}
}
This is my MainActivity.java
public class MainActivity extends Activity {
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
MySQLiteHelper db = new MySQLiteHelper(this);
/**
* CRUD Operations
* */
// add Books
Log.d("Inserting: ", "Inserting all Books..");
db.addBook(new Book("Android Application Development Cookbook", "Wei Meng Lee"));
db.addBook(new Book("Android Programming: The Big Nerd Ranch Guide", "Bill Phillips and Brian Hardy"));
db.addBook(new Book("Learn Android App Development", "Wallace Jackson"));
//Reading and getting all books
Log.d("Reading: ", "Reading all Books..");
List<Book> list = db.getAllBooks();
for (Book cn:list) {
String log = "Id: "+cn.getId()+" ,Title: " + cn.getTitle() + " ,Author: " + cn.getAuthor() + ",Image: "+ cn.getImage();
// Writing Contacts to log
Log.d("Name: ", log);
// delete one book
db.deleteBook(list.get(0));
}
}
}
