Android :Working with DB
You will be needing 3 user defined class files.
- File A which extends sqlitehelper (compulsory)
- File B which contains all methods like - create table,Insert data,Select etc.,
- File C , for Getter and Setter Class which is more for making your code more classy.
Let File A =sqllitehelperclass
Let File B = DBOperations
Let File C= Persons
sqllitehelperclass.java
public class sqllitehelperclass extends SQLiteOpenHelper
{
public sqllitehelperclass(Context context)
{
super(context, "test_db", null, 1);
}
@Override // onCreate() is only run when the database file did not exist and was just created
public sqllitehelperclass(Context context)
{
super(context, "test_db", null, 1);
}
@Override // onCreate() is only run when the database file did not exist and was just created
public void onCreate(SQLiteDatabase db)
{
db.execSQL("CREATE TABLE Persons( PersonID INTEGER PRIMARY KEY AUTOINCREMENT,FirstName TEXT, Age INT)");
}
@Override // onUpgrade() is only called when the database file exists but the stored version number is lower than requested in //constructor.
{
db.execSQL("CREATE TABLE Persons( PersonID INTEGER PRIMARY KEY AUTOINCREMENT,FirstName TEXT, Age INT)");
}
@Override // onUpgrade() is only called when the database file exists but the stored version number is lower than requested in //constructor.
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
}
}
DBOperations.java
public class DBOperations {
SQLiteOpenHelper sql_helper;
DBOperations(Context context){
sql_helper=new sqllitehelperclass(context);
//Downcasting SQLiteOpenHelper instance with the user defined class "SQLiteOpenHelperclass"
}
void create_table(){
sql_helper.getWritableDatabase().execSQL("CREATE TABLE Persons( PersonID INTEGER PRIMARY KEY AUTOINCREMENT,FirstName TEXT, Age INT)");
Log.d("db","New db created");
sql_helper.getWritableDatabase().close();
}
void delete_table(){
sql_helper.getWritableDatabase().execSQL("DROP TABLE IF EXISTS Persons");
Log.i("db", "database deleted");
sql_helper.getWritableDatabase().close();
}
void insert(){
// sql_helper.getWritableDatabase().rawQuery("INSERT INTO Persons (FirstName,Age) VALUES ('Deepak','100');", null);
}
List<persons> db_getall_data()
{
int record_count;
List<persons> query_res=new ArrayList<persons>();
String[] args={"5P"};
Cursor resultSet=sql_helper.getReadableDatabase().rawQuery("SELECT * FROM Persons", args);
Log.d("db", "No of Records present="+String.valueOf(resultSet.getCount()));
record_count=resultSet.getCount();
resultSet.moveToFirst();
if(record_count>0){
while (record_count!=0)
{
persons Persons=new persons();
Persons.put_age(resultSet.getInt(resultSet.getColumnIndex("Age")));
Persons.put_name(resultSet.getString(resultSet.getColumnIndex("FirstName")));
query_res.add(Persons);
resultSet.moveToNext();
record_count=record_count-1;
}
}
sql_helper.getReadableDatabase().close();
return query_res;
}
public boolean isTableExists()
{
try{
Cursor cursor = sql_helper.getReadableDatabase().rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"+"Persons"+"'", null);
Log.d("DB","rows="+cursor.getCount());
cursor.close();
}catch(Exception e){
sql_helper.getReadableDatabase().close();
Log.d("Db","Table does not exist ");
return false;
}
sql_helper.getReadableDatabase().close();
return true;
}
}
SQLiteOpenHelper sql_helper;
DBOperations(Context context){
sql_helper=new sqllitehelperclass(context);
//Downcasting SQLiteOpenHelper instance with the user defined class "SQLiteOpenHelperclass"
}
void create_table(){
sql_helper.getWritableDatabase().execSQL("CREATE TABLE Persons( PersonID INTEGER PRIMARY KEY AUTOINCREMENT,FirstName TEXT, Age INT)");
Log.d("db","New db created");
sql_helper.getWritableDatabase().close();
}
void delete_table(){
sql_helper.getWritableDatabase().execSQL("DROP TABLE IF EXISTS Persons");
Log.i("db", "database deleted");
sql_helper.getWritableDatabase().close();
}
void insert(){
// sql_helper.getWritableDatabase().rawQuery("INSERT INTO Persons (FirstName,Age) VALUES ('Deepak','100');", null);
sql_helper.getWritableDatabase().execSQL("INSERT INTO Persons (FirstName,Age) VALUES ('Deepak','100');");sql_helper.getWritableDatabase().close();
}
List<persons> db_getall_data()
{
int record_count;
List<persons> query_res=new ArrayList<persons>();
String[] args={"5P"};
Cursor resultSet=sql_helper.getReadableDatabase().rawQuery("SELECT * FROM Persons", args);
Log.d("db", "No of Records present="+String.valueOf(resultSet.getCount()));
record_count=resultSet.getCount();
resultSet.moveToFirst();
if(record_count>0){
while (record_count!=0)
{
persons Persons=new persons();
Persons.put_age(resultSet.getInt(resultSet.getColumnIndex("Age")));
Persons.put_name(resultSet.getString(resultSet.getColumnIndex("FirstName")));
query_res.add(Persons);
resultSet.moveToNext();
record_count=record_count-1;
}
}
sql_helper.getReadableDatabase().close();
return query_res;
}
public boolean isTableExists()
{
try{
Cursor cursor = sql_helper.getReadableDatabase().rawQuery("select DISTINCT tbl_name from sqlite_master where tbl_name = '"+"Persons"+"'", null);
Log.d("DB","rows="+cursor.getCount());
cursor.close();
}catch(Exception e){
sql_helper.getReadableDatabase().close();
Log.d("Db","Table does not exist ");
return false;
}
sql_helper.getReadableDatabase().close();
return true;
}
}
person.java
public class persons {
int age;
String name;
int get_age(){
return age;
}
String get_name(){
return this.name;
}
void put_name(String name){
this.name=name;
}
void put_age(int age){
this.age=age;
}
}
int age;
String name;
int get_age(){
return age;
}
String get_name(){
return this.name;
}
void put_name(String name){
this.name=name;
}
void put_age(int age){
this.age=age;
}
}
