In this tutorial you will learn basic SQLite CRUD functions (Create, Read, Update, Delete) for database operations. Actually, we will use Save, Fetch, Update and Delete method names.
Step 1: Create a new IntelliJ/Android Studio Project
- project name: Week7
- module name: BookShelf
Step 2: The first version of this app will not have any UI, edit strings.xml
<?xml version="1.0" encoding="utf-8"?>
<resources>
<string name="app_name">Book Shelf - SQLite example</string>
<string name="hello_world">See logcat output!</string>
<string name="action_settings">Settings</string>
</resources>
Step 3: Run the app to make sure everything is OK so far
Step 4: Create new package "model" and new Java class "Book"
In this example we will be operating on the OBJECT Book, therefore we need a model for it.
package com.chicagoandroid.cit299.week7.bookshelf.model;
public class Book {
private int id;
private String title;
private String author;
private String isbn;
private String location;
/**
* Constructor with no parameters
*/
public Book() {
}
/**
* Constructor with title and author parameters
* @param title
* @param author
*/
public Book(String title, String author) {
super();
this.title = title;
this.author = author;
}
/**
* Constructor with ISBN parameter
* @param isbn
*/
public Book(String isbn) {
super();
this.isbn = isbn;
}
For our convenience we will override toString() method that will show us the content of the Book.
@Override
public String toString() {
return "Book: id=" + id
+ "\n title = " + title
+ "\n author = " + author
+ "\n isbn = " + isbn;
}
Step 5: Generate getters and Setters methods for Book.java
Step 6: Create new package "database" and new Java Interface "DatabaseCrud"
package com.cyberwalkabout.database;
import java.util.List;
/**
* Created by uki on 10/11/14.
* This interface simply assures that we don't forget to implement most important methods.
* We are using Generic TYPE T as we don't know what objects we will be using in the database.
* The TYPE T could stand for any object e.g. Book, Person, Address, etc.
* You could add more methods of your own, or better method parameters.
*/
public interface DatabaseCrud<T> {
/**
* Saves an object to the database.
*/
public void create(T object);
/**
* This methods reads one record by id.
* This record has to be in the Database to have id.
* Please notice it returns Generic Type T.
*/
public T read(int dbRecordId);
/**
* fetches all objects that match the String searchText
*/
public List<T> fetch(String searchText);
/**
* Update given object in the database.
*/
public int update(T object);
/**
* Deletes given object from the database.
* This method should wrap delete(int objectDbId);
*/
public void delete(T object);
}
Step 7: Create Java class "BookSqlHelper"
package com.chicagoandroid.cit299.week7.bookshelf.database;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.chicagoandroid.cit299.week7.bookshelf.model.Book;
import com.cyberwalkabout.database.DatabaseCrud;
import java.nio.Buffer;
import java.util.LinkedList;
import java.util.List;
public class BookSqlHelper extends SQLiteOpenHelper implements DatabaseCrud<Book> {
private static final String TAG = BookSqlHelper.class.getSimpleName();
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_NAME = "DB_BOOK_SHELF";
private static final String TABLE_BOOKS = "books";
private static final String FIELD_ID = "id";
private static final String FIELD__TITLE = "title";
private static final String FIELD__AUTHOR = "author";
private static final String FIELD__ISBN = "isbn";
private static final String FIELD__LOCATION = "location";
private static final String[] COLUMNS = { //
FIELD_ID, // 0
FIELD__TITLE, // 1
FIELD__AUTHOR, // 2
FIELD__ISBN, // 3
FIELD__LOCATION // 4
};
public BookSqlHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_BOOK_TABLE = //
"CREATE TABLE " + TABLE_BOOKS + " ( " //
+ FIELD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " //
+ FIELD__TITLE + " TEXT, " //
+ FIELD__AUTHOR + " TEXT, " //
+ FIELD__ISBN + " TEXT, " //
+ FIELD__LOCATION + " TEXT " //
+ ")";
db.execSQL(CREATE_BOOK_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS books");
this.onCreate(db);
}
Step 8 : Add "create" method of BookSqlHelper.java
/**
* Inserts a Book object to the database.
*/
@Override
public void create(Book book) {
Log.w(TAG + "save()", book.toString());
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(FIELD__TITLE, book.getTitle());
values.put(FIELD__AUTHOR, book.getAuthor());
values.put(FIELD__ISBN, book.getIsbn());
values.put(FIELD__LOCATION, book.getLocation());
db.insert(TABLE_BOOKS, null, values);
db.close();
}
Step 9: Implement "read" method of BookSqlHelper.java
/**
* This methods reads one record by id.
*
* @param dbBookId
*/
@Override
public Book read(int dbBookId) {
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.query( //
TABLE_BOOKS, // String table
COLUMNS, // String[] columns
" id = ?", // selection
new String[]{String.valueOf(dbBookId)}, // String[] selection arguments
null, // String group by
null, // String having
null, // String order by
null); // String limit
return getBooksFromCursor(cursor).get(0);
}
Step 9 : Implement "update" method of BookSqlHelper.java /**
* Update given Book object in the database.
*/
@Override
public int update(Book book) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put("title", book.getTitle());
values.put("author", book.getAuthor());
values.put("isbn", book.getIsbn());
values.put("location", book.getLocation());
int i = db.update( //
TABLE_BOOKS, // String table
values, // ContentValues values - column/value pairs
FIELD_ID + " = ?", // String where clause
new String[]{String.valueOf(book.getId()) // String[] where arguments
});
db.close();
Log.w(TAG + "update(Book book)", book.toString());
return i;
}
Step 10: Implement "delete" method(s)
/**
* Deletes given object from the database.
*/
@Override
public void delete(Book book) {
delete(book.getId());
Log.d(TAG + "delete", book.toString());
}
/**
* Delete database object by it's id.
*
* @param bookDbId - database id of the object to be deleted.
*/
public void delete(int bookDbId) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete( //
TABLE_BOOKS, // String table
FIELD_ID + " = ?", // String where clause
new String[]{String.valueOf(bookDbId) // String[] where arguments
});
db.close();
Log.d(TAG + "delete(int bookDbId)", "ID: " + bookDbId);
}
