In Android Sqlite database is used for storing data. There is two way to use database in Application.
1. Create database.
2. Used existing database.
here is below code for Used existing database in Application.
Put your database file in "assets" folder. so we can create copy of that db file into storage card of device.
Call Methods:
Below code is for create database.
DBhelperdb = new
DBhelper(getApplicationContext());
try
{
db.createDataBase();
}
catch (IOException e)
{
e.printStackTrace();
}
This is helper class which contain all Method of database and in this class i have put some dummy method for How its used in Application.
Database Helper Class
public class DBhelper extends SQLiteOpenHelper
{
private static String DATABASE_NAME = "HB.sqlitedb";
private SQLiteDatabase myDataBase;
private Context myContext;
private String path = "/data/data/com.hb/databases/";
public DBhelper(Context
context)
{
super(context, DATABASE_NAME, null, 1);
this.myContext = context;
}
// ---Create the
database---
public void createDataBase() throws IOException
{
// ---Check whether
database is already created or not---
boolean dbExist =
checkDataBase();
if (!dbExist)
{
this.getReadableDatabase();
try
{
// ---If not
created then copy the database---
copyDataBase();
}
catch (IOException e)
{
throw new Error("Error copying
database");
}
this.close();
}
}
// --- Check
whether database already created or not---
private boolean checkDataBase()
{
try
{
String
myPath = path + DATABASE_NAME;
File
f = new File(myPath);
if (f.exists())
return true;
else
return false;
}
catch (SQLiteException e)
{
e.printStackTrace();
return false;
}
}
// --- Copy the
database to the output stream---
private void copyDataBase() throws IOException
{
InputStream
myInput = myContext.getAssets().open(DATABASE_NAME);
String
outFileName = path + DATABASE_NAME;
OutputStream
myOutput = new FileOutputStream(outFileName);
byte[] buffer = new byte[1024];
int length;
while ((length =
myInput.read(buffer)) > 0)
{
myOutput.write(buffer,
0, length);
}
myOutput.flush();
myOutput.close();
myInput.close();
}
public void openDataBase() throws SQLException
{
// --- Open the
database---
String
myPath = path + DATABASE_NAME;
myDataBase = SQLiteDatabase.openDatabase(myPath,
null,SQLiteDatabase.OPEN_READWRITE);
myDataBase.setLockingEnabled(false);
}
@Override
public synchronized void close()
{
if (myDataBase != null)
myDataBase.close();
super.close();
}
@Override
public void onCreate(SQLiteDatabase
arg0)
{
}
@Override
public void onUpgrade(SQLiteDatabase
db, int oldVersion, int newVersion)
{
}
public void
update_delete_insertquery(String s)
{
myDataBase.execSQL(s);
}
public Cursor
selectquery(String s)
{
return myDataBase.rawQuery(s, null);
}
public int getTotalQue()
{
int count = 0;
openDataBase();
Cursor
c = myDataBase.rawQuery("SELECT
Count(*) FROM Questions", null);
if (c != null)
{
if (c.moveToFirst())
{
count
= c.getInt(0);
}
}
c.close();
myDataBase.close();
SQLiteDatabase.releaseMemory();
return count;
}
public int getusermasterID()
{
int temp = 0;
openDataBase();
Cursor
c = myDataBase.rawQuery("select
MAX(question_id) from Questions", null);
if (c != null)
{
if (c.moveToFirst())
{
temp
= c.getInt(0);
}
}
c.close();
myDataBase.close();
SQLiteDatabase.releaseMemory();
return temp;
}
public void Insert_Data(Question
Que)
{
int id =
getusermasterID();
openDataBase();
ContentValues
initialValues = new ContentValues();
initialValues.put("question_id", id + 1);
initialValues.put("songtext", Que.songtext);
initialValues.put("singertext", Que.singertext);
initialValues.put("questiontext", Que.questiontext);
initialValues.put("answertext",
Que.answertext);
initialValues.put("category", Que.category);
initialValues.put("isAsked", 0);
myDataBase.insert("Questions", null, initialValues);
myDataBase.close();
Log.i("QUESTIONID========", "" + id);
}
public Question
GET_QUESTIONS(int catid)
{
Question
que= new Question();
openDataBase();
//Cursor c =
myDataBase.rawQuery("SELECT * FROM Questions where category =" + catid
+" LIMIT 1", null);
Cursor
c = myDataBase.rawQuery("SELECT * FROM
Questions WHERE isAsked = 0 AND category ="+catid +" ORDER BY RANDOM() LIMIT
1",
null);
if (c != null)
{
if (c.moveToFirst())
{
que.questionid
= c.getInt(0);
que.songtext
= c.getString(1);
que.singertext
= c.getString(2);
que.questiontext
= c.getString(3);
que.answertext
= c.getString(4);
que.category
= c.getInt(5);
updatequestion(que.questionid);
}
}
c.close();
c
= myDataBase.rawQuery("SELECT
Count(*) FROM Questions WHERE isAsked = 0 AND category ="+ catid +" ", null);
if (c != null)
{
if (c.moveToFirst())
{
int count=c.getInt(0);
if(count<=1)
{
updatequestionasked(catid);
}
}
}
c.close();
myDataBase.close();
SQLiteDatabase.releaseMemory();
return que;
}
public synchronized void updatequestionasked(int cat)
{
ContentValues
initialValues = new ContentValues();
initialValues.put("isAsked", 0);
myDataBase.update("Questions", initialValues, "category
="+cat,null);
}
public synchronized void updatequestion(int id)
{
ContentValues
initialValues = new ContentValues();
initialValues.put("isAsked", 1);
myDataBase.update("Questions", initialValues, "question_id=" + id,null);
}
public void deleteQuestion()
{
openDataBase();
myDataBase.delete("Questions", null, null);
myDataBase.close();
SQLiteDatabase.releaseMemory();
}
}
Call Method of Helper Class:
private void GetData()
{
Question
que=new Question();
que=db.GET_QUESTIONS(1);
songtext=que.songtext;
singertext=que.singertext;
quetext=que.questiontext;
anstext=que.answertext;
}
2. This is the second way to create database and used in our application.
import
android.content.ContentValues;
import
android.content.Context;
import
android.database.Cursor;
import
android.database.sqlite.SQLiteDatabase;
import
android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHandler extends SQLiteOpenHelper {
private static final String DATABASE_NAME = "Registration";
private static final int DATABASE_VERSION = 1;
private static final String DATABASE_TABLE = "Reg";
private static final String KEY_ID = "id";
private static final String KEY_NAME = "name";
private static final String KEY_PASSWORD = "password";
private static final String KEY_GENDER = "gender";
private static final String KEY_CONTACTNO = "contact";
public
DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void
onCreate(SQLiteDatabase db) {
String
CREATE_TABLE = "CREATE TABLE " + DATABASE_TABLE + "(" + KEY_ID
+
"
INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_PASSWORD
+
"
TEXT," + KEY_GENDER + " TEXT," + KEY_CONTACTNO + " TEXT"
+
")";
db.execSQL(CREATE_TABLE);
}
@Override
public void
onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF
EXISTS " + DATABASE_TABLE);
onCreate(db);
}
public void Register(String
name, String password, String gender,
String
contact) {
SQLiteDatabase
db = this.getWritableDatabase();
ContentValues
cv = new ContentValues();
cv.put(KEY_NAME, name);
cv.put(KEY_PASSWORD, password);
cv.put(KEY_GENDER, gender);
cv.put(KEY_CONTACTNO, contact);
db.insert(DATABASE_TABLE, null, cv);
db.close();
}
public boolean isUserExists(String
name, String password, String id) {
boolean isExists = false;
SQLiteDatabase
db = this.getWritableDatabase();
String
strFilter = KEY_NAME + " ='" + name + "' and " + KEY_PASSWORD
+
"
='"
+ password + "' and " + KEY_ID + " <>'" + id + "'";
Cursor
mCursor = db.query(DATABASE_TABLE, null, strFilter, null, null,
null, null);
if (mCursor != null &&
mCursor.getCount() > 0) {
isExists
= true;
}
if (mCursor != null) {
mCursor.close();
}
db.close();
return isExists;
}
}
Insert and Retrieve Image into Database visit link
More Ref :
http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/
http://matrix-examplecode.blogspot.in/2011/08/sqlite-external-database-connectivity.html
I
will be happy if you will provide your feedback or follow this blog. Any suggestion
and help will be appreciated.
Thank
you :)
