SQLiteDatabase With Multiple Tables

In the previous post,We discuss about Android SQLite Database.In this post we are going to discuss about how to create multiple tables in SQLite database.Here, We  are going to use four classes.There are,

1.SQLiteActivity
2.DatabaseHandler
3.StudentModel
4.TeacherModel

SQLiteActivity :

    SQLiteActivity is used to create the instance of DatabaseHandler and access its methods.

DatabaseHandler :
    
    DatabaseHandler is used to create the SQLite database and to perform CRUD operation. 

StudentModel :

    StudentModel contains student get() and set() method.

TeacherModel :

    TeacherModel contains teacher get() and set method.

You can download source code here.

This is code for SQLiteActivity

package com.etr.sqlitedatabasewithtwotablesexample;

import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
import android.view.Menu;

public class SQLiteActivity extends Activity {
DatabaseHandler mDBHandler;
@Override
protected void onCreate(Bundle savedInstanceState) {
try {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_sqlite);

mDBHandler = new DatabaseHandler(this);
mDBHandler.insertStudentRecord(new StudentModel("Albert"));
mDBHandler.insertTeacherRecord(new TeacherModel("Johnson"));
for(StudentModel name : mDBHandler.getStudentRecord()){
Log.i("Student Name :  ",name.getStudentName());
}
for(TeacherModel name : mDBHandler.getTeacherRecord()){
Log.i("Teacher Name :  ",name.getTeacherName());
}
} catch(Exception e){
Log.d("SQLiteActivity", Log.getStackTraceString(e));
}
}

@Override
public boolean onCreateOptionsMenu(Menu menu) {
// Inflate the menu; this adds items to the action bar if it is present.
getMenuInflater().inflate(R.menu.sqlite, menu);
return true;
}

}

This is code for DatabaseHandler

package com.etr.sqlitedatabasewithtwotablesexample;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper {

private static final String databaseName = "INFO.db";
private static final int databaseVersion = 1;

// Student Table Information
private static final String studentTable = "STUDENTINFO";

private static final String studentId = "ID";
private static final String studentName = "NAME";

// Teacher Table
private static final String teacherTable = "TEACHERINFO";

private static final String teacherId = "ID";
private static final String teacherName = "NAME";

public DatabaseHandler(Context context) {
super(context, databaseName, null, databaseVersion);
}

@Override
public void onCreate(SQLiteDatabase db) {
try {
// Create table Place
String placeTableCreateQuery = "CREATE TABLE " + studentTable + "("
+ studentId + " INTEGER PRIMARY KEY," + studentName
+ " TEXT" + ")";

db.execSQL(placeTableCreateQuery);

// Create table Reminder
String reminderTableCreateQuery = "CREATE TABLE " + teacherTable
+ "(" + teacherId + " INTEGER PRIMARY KEY," + teacherName
+ " TEXT" + ")";

db.execSQL(reminderTableCreateQuery);

} catch (SQLException se) {
Log.v("DatabaseHandler Oncreate SQLException",
Log.getStackTraceString(se));
} catch (Exception e) {
Log.v("DatabaseHandler Oncreate Exception",
Log.getStackTraceString(e));
}

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
try {
// Drop table Place
String placeTableDropQuery = "DROP TABLE IF EXISTS " + studentTable;
db.execSQL(placeTableDropQuery);

// Drop table Reminder
String ReminderTableDropQuery = "DROP TABLE IF EXISTS "
+ teacherTable;
db.execSQL(ReminderTableDropQuery);

// Upgrade database
onCreate(db);

} catch (SQLException se) {
Log.v("DatabaseHandler onUpgrade SQLException",
Log.getStackTraceString(se));
} catch (Exception e) {
Log.v("DatabaseHandler onUpgrade Exception",
Log.getStackTraceString(e));
}

}

// Student CRUD Operation

public String insertStudentRecord(StudentModel model) {
SQLiteDatabase db = this.getWritableDatabase();
try {
ContentValues values = new ContentValues();
values.put(studentName, model.getStudentName());
db.insert(studentTable, null, values);
db.close();

return "Record insert succussfully...";
} catch (SQLiteException se) {
Log.v("DatabaseHandler insertStudentRecord Exception",
Log.getStackTraceString(se));
return se.getMessage();
} catch (Exception e) {
Log.v("DatabaseHandler insertStudentRecord Exception",
Log.getStackTraceString(e));
return e.getMessage();
} finally {
db.close();
}
}

public ArrayList<StudentModel> getStudentRecord() {
ArrayList<StudentModel> record = new ArrayList<StudentModel>();
SQLiteDatabase db = this.getReadableDatabase();
try {
String selectQuery = "SELECT * FROM " + studentTable;
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor != null && cursor.moveToFirst()) {
do {

StudentModel model = new StudentModel();
model.setStudentId(cursor.getInt(0));
model.setStudentName(cursor.getString(1));

record.add(model);

} while (cursor.moveToNext());
}
return record;
} catch (SQLiteException se) {
Log.v("DatabaseHandler getStudentRecord Exception",
Log.getStackTraceString(se));
} catch (Exception e) {
Log.v("DatabaseHandler getStudentRecord Exception",
Log.getStackTraceString(e));
} finally {
db.close();
}
return record;
}

// Teacher CRUD Operation
public String insertTeacherRecord(TeacherModel model) {
SQLiteDatabase db = this.getWritableDatabase();
try {
ContentValues values = new ContentValues();
values.put(teacherName, model.getTeacherName());
db.insert(teacherTable, null, values);
db.close();

return "Record insert succussfully...";
} catch (SQLiteException se) {
Log.v("DatabaseHandler insertTeacherRecord Exception",
Log.getStackTraceString(se));
return se.getMessage();
} catch (Exception e) {
Log.v("DatabaseHandler insertTeacherRecord Exception",
Log.getStackTraceString(e));
return e.getMessage();
} finally {
db.close();
}
}

public ArrayList<TeacherModel> getTeacherRecord() {
ArrayList<TeacherModel> record = new ArrayList<TeacherModel>();
SQLiteDatabase db = this.getReadableDatabase();
try {
String selectQuery = "SELECT * FROM " + teacherTable;
Cursor cursor = db.rawQuery(selectQuery, null);
if (cursor != null && cursor.moveToFirst()) {
do {

TeacherModel model = new TeacherModel();
model.setTeacherId(cursor.getInt(0));
model.setTeacherName(cursor.getString(1));

record.add(model);

} while (cursor.moveToNext());
}
return record;
} catch (SQLiteException se) {
Log.v("DatabaseHandler getTeacherRecord Exception",
Log.getStackTraceString(se));
} catch (Exception e) {
Log.v("DatabaseHandler getTeacherRecord Exception",
Log.getStackTraceString(e));
} finally {
db.close();
}
return record;
}

}

This is code for StudentModel

package com.etr.sqlitedatabasewithtwotablesexample;

public class StudentModel {

int studentId;
String studentName;

public StudentModel() {

}

public StudentModel(int studentId, String studentName) {

this.studentId = studentId;
this.studentName = studentName;

}

public StudentModel(String studentName) {

this.studentName = studentName;

}

// Id
public int getStudentId() {
return this.studentId;
}

public void setStudentId(int studentId) {
this.studentId = studentId;
}

// Name
public String getStudentName() {
return this.studentName;
}

public void setStudentName(String studentName) {
this.studentName = studentName;
}

}

This is code TeacherModel

package com.etr.sqlitedatabasewithtwotablesexample;

public class TeacherModel {

int teacherId;
String teacherName;

public TeacherModel() {

}

public TeacherModel(int teacherId, String teacherName) {

this.teacherId = teacherId;
this.teacherName = teacherName;

}

public TeacherModel(String teacherName) {

this.teacherName = teacherName;

}

// Id
public int getTeacherId() {
return this.teacherId;
}

public void setTeacherId(int teacherId) {
this.teacherId = teacherId;
}

// Name
public String getTeacherName() {
return this.teacherName;
}

public void setTeacherName(String teacherName) {
this.teacherName = teacherName;
}

}

You can see the output in LogCat.