package com.google.android.sqlite;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import com.google.android.R;
import com.google.android.entity.Author;
import com.google.android.entity.Category;
import com.google.android.entity.Quote;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;
public class DBAdapter {
public static final String TAG ="DBAdapter";
private static String DB_PATH = "/data/data/com.google.android/databases/";
protected Cursor dbCursor;
protected SQLiteDatabase db;
protected String dbquery;
protected DatabaseHelper dbOpenHelper;
protected Context dbContext;
protected static final int DB_VERSION = 1;
protected static final String DB_NAME = "DBDemo";
private static final String CATEGORY = "CREATE TABLE CATEGORY ( "
+ "CategoryId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ "CategoryName NVARCHAR(50) NOT NULL"
+ ")";
private static final String AUTHOR = "CREATE TABLE AUTHOR ( "
+ "AuthorId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ "AuthorName NVARCHAR(50) NOT NULL, "
+ "Job NVARCHAR(50) NOT NULL, "
+ "Image INTEGER NOT NULL"
+ ")";
private static final String QUOTE = "CREATE TABLE QUOTE ( "
+ "QuoteId INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
+ "QuoteText NVARCHAR(300) NOT NULL, "
+ "CategoryId INTEGER NOT NULL, "
+ "AuthorId INTEGER NOT NULL"
+ ")";
private static class DatabaseHelper extends SQLiteOpenHelper{
private Context myContext;
public DatabaseHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
myContext = context;
// TODO Auto-generated constructor stub
}
@Override
public void onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
// db.execSQL(CATEGORY);
// db.execSQL(AUTHOR);
// db.execSQL(QUOTE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// TODO Auto-generated method stub
//Log.i(TAG, "Upgrading DB");
}
public void createDataBase() throws IOException{
boolean dbExist = checkDataBase();
if(dbExist){
//do nothing - database already exist
}else{
//By calling this method and empty database will be created into the default system path
//of your application so we are gonna be able to overwrite that database with our database.
this.getReadableDatabase();
try {
copyDataBase();
} catch (IOException e) {
throw new Error("Error copying database");
}
}
}
private boolean checkDataBase(){
SQLiteDatabase checkDB = null;
try{
String myPath = DB_PATH + DB_NAME;
checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
}catch(SQLiteException e){
}
if(checkDB != null){
checkDB.close();
}
return checkDB != null ? true : false;
}
/**
* Copies your database from your local assets-folder to the just created empty database in the
* system folder, from where it can be accessed and handled.
* This is done by transfering bytestream.
* */
private void copyDataBase() throws IOException{
//Open your local db as the input stream
InputStream myInput = myContext.getAssets().open(DB_NAME);
// Path to the just created empty db
String outFileName = DB_PATH + DB_NAME;
//Open the empty db as the output stream
OutputStream myOutput = new FileOutputStream(outFileName);
//transfer bytes from the inputfile to the outputfile
byte[] buffer = new byte[1024];
int length;
while ((length = myInput.read(buffer))>0){
myOutput.write(buffer, 0, length);
}
//Close the streams
myOutput.flush();
myOutput.close();
myInput.close();
}
}
public DBAdapter(Context ctx){
this.dbContext = ctx;
}
/**
* Check if the database already exist to avoid re-copying the file each time you open the application.
* @return true if it exists, false if it doesn't
*/
public DBAdapter open()
{
try{
dbOpenHelper = new DatabaseHelper(dbContext, DB_NAME, null, DB_VERSION);
dbOpenHelper.createDataBase();
db = dbOpenHelper.getWritableDatabase();
}catch(IOException ie){
}
return this;
}
public void close(){
dbOpenHelper.close();
}
// public void createCategory(){
// ContentValues inititalValues1 = new ContentValues();
// inititalValues1.put("CategoryName", "Category1");
// db.insert("CATEGORY", null, inititalValues1);
//
// ContentValues inititalValues2 = new ContentValues();
// inititalValues2.put("CategoryName", "Category2");
// db.insert("CATEGORY", null, inititalValues2);
// }
// public void createAuthor(){
// ContentValues inititalValues1 = new ContentValues();
// inititalValues1.put("AuthorName", "Author1");
// inititalValues1.put("Job", "Job1");
// inititalValues1.put("Image", R.drawable.sample_0);
// db.insert("AUTHOR", null, inititalValues1);
//
// ContentValues inititalValues2 = new ContentValues();
// inititalValues2.put("AuthorName", "Author2");
// inititalValues2.put("Job", "Job2");
// inititalValues2.put("Image", R.drawable.sample_1);
// db.insert("AUTHOR", null, inititalValues2);
//
// ContentValues inititalValues3 = new ContentValues();
// inititalValues3.put("AuthorName", "Author3");
// inititalValues3.put("Job", "Job3");
// inititalValues3.put("Image", R.drawable.sample_2);
// db.insert("AUTHOR", null, inititalValues3);
// }
//
// public void createQuote(){
// ContentValues inititalValues1 = new ContentValues();
// inititalValues1.put("QuoteText", "Quote1");
// inititalValues1.put("CategoryId", 1);
// inititalValues1.put("AuthorId", 1);
// db.insert("QUOTE", null, inititalValues1);
//
// ContentValues inititalValues2 = new ContentValues();
// inititalValues2.put("QuoteText", "Quote2");
// inititalValues2.put("CategoryId", 1);
// inititalValues2.put("AuthorId", 1);
// db.insert("QUOTE", null, inititalValues2);
//
// ContentValues inititalValues3 = new ContentValues();
// inititalValues3.put("QuoteText", "Quote3");
// inititalValues3.put("CategoryId", 1);
// inititalValues3.put("AuthorId", 2);
// db.insert("QUOTE", null, inititalValues3);
//
// ContentValues inititalValues4 = new ContentValues();
// inititalValues4.put("QuoteText", "Quote4");
// inititalValues4.put("CategoryId", 2);
// inititalValues4.put("AuthorId", 2);
// db.insert("QUOTE", null, inititalValues4);
// }
public ArrayList<Category> getCategories() throws Exception {
ArrayList<Category> list = new ArrayList<Category>();
try {
this.dbquery = "SELECT * FROM CATEGORY";
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
Category cat = new Category();
cat.setCategoryId(dbCursor.getInt(0));
cat.setCategoryName(dbCursor.getString(1));
list.add(cat);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public Quote getQuoteById(int quoteId) throws Exception {
Quote quote = new Quote();
try {
this.dbquery = "SELECT * FROM QUOTES WHERE QuoteId = " + quoteId;
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
quote.setQuoteId(dbCursor.getInt(dbCursor.getColumnIndex("QuoteId")));
quote.setQuoteText(dbCursor.getString(dbCursor.getColumnIndex("QuoteText")));
quote.setCategoryId(dbCursor.getInt(dbCursor.getColumnIndex("CategoryId")));
quote.setAuthorId(dbCursor.getInt(dbCursor.getColumnIndex("AuthorId")));
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return quote;
}
public ArrayList<Quote> getQuotesByCategoryId(int categoryId) throws Exception {
ArrayList<Quote> list = new ArrayList<Quote>();
try {
this.dbquery = "SELECT * FROM QUOTES WHERE CategoryId = " + categoryId;
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
Quote quote = new Quote();
quote.setQuoteId(dbCursor.getInt(dbCursor.getColumnIndex("QuoteId")));
quote.setQuoteText(dbCursor.getString(dbCursor.getColumnIndex("QuoteText")));
quote.setCategoryId(dbCursor.getInt(dbCursor.getColumnIndex("CategoryId")));
quote.setAuthorId(dbCursor.getInt(dbCursor.getColumnIndex("AuthorId")));
list.add(quote);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public ArrayList<Quote> getQuotes() throws Exception {
ArrayList<Quote> list = new ArrayList<Quote>();
try {
this.dbquery = "SELECT * FROM QUOTES";
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
Quote quote = new Quote();
quote.setQuoteId(dbCursor.getInt(dbCursor.getColumnIndex("QuoteId")));
quote.setQuoteText(dbCursor.getString(dbCursor.getColumnIndex("QuoteText")));
quote.setCategoryId(dbCursor.getInt(dbCursor.getColumnIndex("CategoryId")));
quote.setAuthorId(dbCursor.getInt(dbCursor.getColumnIndex("AuthorId")));
list.add(quote);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public ArrayList<Quote> getQuotesByAuthorId(int authorId) throws Exception {
ArrayList<Quote> list = new ArrayList<Quote>();
try {
this.dbquery = "SELECT * FROM QUOTES WHERE AuthorId = " + authorId;
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
Quote quote = new Quote();
quote.setQuoteId(dbCursor.getInt(dbCursor.getColumnIndex("QuoteId")));
quote.setQuoteText(dbCursor.getString(dbCursor.getColumnIndex("QuoteText")));
quote.setCategoryId(dbCursor.getInt(dbCursor.getColumnIndex("CategoryId")));
quote.setAuthorId(dbCursor.getInt(dbCursor.getColumnIndex("AuthorId")));
list.add(quote);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public Author getAuthorById(int authorId) throws Exception {
Author author = new Author();
try {
this.dbquery = "SELECT * FROM AUTHOR WHERE AuthorId = " + authorId;
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
author.setAuthorId(dbCursor.getInt(0));
author.setAuthorName(dbCursor.getString(1));
author.setJob(dbCursor.getString(2));
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
dbCursor.close();
// TODO: handle exception
throw ex;
}
return author;
}
public ArrayList<Author> getAuthors() throws Exception {
ArrayList<Author> list = new ArrayList<Author>();
try {
this.dbquery = "SELECT * FROM AUTHOR";
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
// Fetch your data values
Author author = new Author();
author.setAuthorId(dbCursor.getInt(0));
author.setAuthorName(dbCursor.getString(1));
author.setJob(dbCursor.getString(2));
list.add(author);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public ArrayList<Author> getAuthors(String keyword) throws Exception {
ArrayList<Author> list = new ArrayList<Author>();
try {
this.dbquery = "SELECT * FROM AUTHOR WHERE AuthorName LIKE '%" + keyword + "%' OR Job LIKE '%" + keyword + "%'";
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
Author author = new Author();
author.setAuthorId(dbCursor.getInt(0));
author.setAuthorName(dbCursor.getString(1));
author.setJob(dbCursor.getString(2));
list.add(author);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public ArrayList<Category> getCategories(String keyword) throws Exception {
ArrayList<Category> list = new ArrayList<Category>();
try {
this.dbquery = "SELECT * FROM CATEGORY WHERE CategoryName LIKE '%" + keyword + "%'";
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
Category cat = new Category();
cat.setCategoryId(dbCursor.getInt(0));
cat.setCategoryName(dbCursor.getString(1));
list.add(cat);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public ArrayList<Quote> getQuotes(String keyword) throws Exception {
ArrayList<Quote> list = new ArrayList<Quote>();
try {
this.dbquery = "SELECT * FROM QUOTES WHERE QuoteText LIKE '%" + keyword + "%'";
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
Quote quote = new Quote();
quote.setQuoteId(dbCursor.getInt(dbCursor.getColumnIndex("QuoteId")));
quote.setQuoteText(dbCursor.getString(dbCursor.getColumnIndex("QuoteText")));
quote.setCategoryId(dbCursor.getInt(dbCursor.getColumnIndex("CategoryId")));
quote.setAuthorId(dbCursor.getInt(dbCursor.getColumnIndex("AuthorId")));
list.add(quote);
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return list;
}
public int getTotalQuotes() throws Exception {
int count = 0;
try {
this.dbquery = "SELECT * FROM QUOTES";
this.dbCursor = db.rawQuery(dbquery, null);
if(dbCursor.moveToFirst()){
do{
count++;
}while(dbCursor.moveToNext());
}
dbCursor.close();
} catch (Exception ex) {
// TODO: handle exception
dbCursor.close();
throw ex;
}
return count;
}
}
|