public class DatabaseHandler extends SQLiteOpenHelper {
// Database Name
private static final String DATABASE_NAME = "notesManager";
// PersonNotes table name
private static final String TABLE_NOTES = "PersonNotes";
// Columns names for PersonNotes table
private static final String KEY_ID = "id";
private static final String KEY_NOTE = "note";
private static final String KEY_CREATED_AT = "createdAt";
private static final String KEY_TO_PERSONID = "toPersonID";
private static final String KEY_CUR_PERSONID = "cPersonID";
// PersonSessions table name
private static final String TABLE_SESSIONS = "PersonSession";
// Columns names for PersonSessions table
// private static final String KEY_ID = "id";
private static final String KEY_SESSIONID = "eventId";
private static final String KEY_TITLE = "title";
private static final String KEY_SUMMARY = "summary";
private static final String KEY_TIME = "time";
private static final String KEY_LOCATION = "location";
private static final String KEY_DATE = "date";
private static final String KEY_TIMEINSEC = "timeinsec";
private static final int version=1;
public DatabaseHandler(Context context,CursorFactory factory){
super(context, DATABASE_NAME, factory, version);
}
public DatabaseHandler(Context context, String name, CursorFactory factory,
int version) {
super(context, DATABASE_NAME, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
String CREATE_NOTES_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_NOTES + " (" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + KEY_NOTE + " TEXT," + KEY_CUR_PERSONID + " TEXT,"+ KEY_TO_PERSONID + " TEXT," + KEY_CREATED_AT + " TEXT" + ")";
String CREATE_SESSIONS_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_SESSIONS + " (" + KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + KEY_SESSIONID + " TEXT," + KEY_CUR_PERSONID + " TEXT,"+ KEY_TITLE + " TEXT,"+ KEY_SUMMARY + " TEXT,"+ KEY_TIME + " TEXT," + KEY_LOCATION + " TEXT," + KEY_DATE + " TEXT ," + KEY_TIMEINSEC + " REAL " +")";
db.execSQL(CREATE_NOTES_TABLE);
db.execSQL(CREATE_SESSIONS_TABLE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTES);
db.execSQL("DROP TABLE IF EXISTS " + TABLE_SESSIONS);
// Create tables again
onCreate(db);
}
/** to get the particular person notes*/
public List<Note> getPersonNotes(String cur_PersonId,String to_PersonId) {
List<Note> notesList = new ArrayList<Note>();
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_NOTES +
" where " + KEY_CUR_PERSONID + " = ? AND " + KEY_TO_PERSONID + " = ? " , new String[] { cur_PersonId, to_PersonId});
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Note note = new Note();
note.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(KEY_ID))));
note.setNote(cursor.getString(cursor.getColumnIndex(KEY_NOTE)));
note.setCur_personId(cursor.getString(cursor.getColumnIndex(KEY_CUR_PERSONID)));
note.setTo_personId(cursor.getString(cursor.getColumnIndex(KEY_TO_PERSONID)));
note.setCreatedAt(cursor.getString(cursor.getColumnIndex(KEY_CREATED_AT)));
// Adding contact to list
notesList.add(note);
} while (cursor.moveToNext());
}
if(cursor != null)
cursor.close();
if(db != null)
db.close();
return notesList;
}
// Deleting single note
public void deleteNote(Note note) {
SQLiteDatabase db = this.getWritableDatabase();
db.delete(TABLE_NOTES, KEY_ID + " = ?", new String[] { String.valueOf(note.getId()) });
if(db != null)
db.close();
}
// Getting notes Count
public int getNotesCount() {
String countQuery = "SELECT * FROM " + TABLE_NOTES;
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = db.rawQuery(countQuery, null);
cursor.close();
if(cursor != null)
cursor.close();
if(db != null)
db.close();
return cursor.getCount();
}
// Adding new session
public long addSession(Session session) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(KEY_SESSIONID, session.getEventId());
values.put(KEY_CUR_PERSONID, session.getCurPersonId());
values.put(KEY_TITLE, session.getTitle());
values.put(KEY_SUMMARY, session.getSummary());
values.put(KEY_TIME, session.getTime());
values.put(KEY_LOCATION, session.getLocation());
values.put(KEY_DATE, session.getDate());
values.put(KEY_TIMEINSEC, session.getTimeinSec());
// System.out.println("TimeIN SEC:"+session.getTimeinSec());
// Inserting Row
long row= db.insert(TABLE_SESSIONS, null, values);
db.close(); // Closing database connection
return row;
}
/** to get the particular person sessions with multiple where conditions and order by */
public List<Session> getPersonSeesions(String cur_PersonId,String date) {
List<Session> sessionsList = new ArrayList<Session>();
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_SESSIONS +
" where " + KEY_CUR_PERSONID + " = ? AND " + KEY_DATE + " = ? ORDER BY "+ KEY_TIMEINSEC , new String[] { cur_PersonId, date});
// looping through all rows and adding to list
if (cursor.moveToFirst()) {
do {
Session session=new Session();
session.setId(Integer.parseInt(cursor.getString(cursor.getColumnIndex(KEY_ID))));
session.setEventId(cursor.getString(cursor.getColumnIndex(KEY_SESSIONID)));
session.setCurPersonId(cursor.getString(cursor.getColumnIndex(KEY_CUR_PERSONID)));
session.setTitle(cursor.getString(cursor.getColumnIndex(KEY_TITLE)));
session.setSummary(cursor.getString(cursor.getColumnIndex(KEY_SUMMARY)));
session.setTime(cursor.getString(cursor.getColumnIndex(KEY_TIME)));
session.setLocation(cursor.getString(cursor.getColumnIndex(KEY_LOCATION)));
session.setDate(cursor.getString(cursor.getColumnIndex(KEY_DATE)));
// Adding Session to list
sessionsList.add(session);
} while (cursor.moveToNext());
}
if(cursor != null)
cursor.close();
if(db != null)
db.close();
return sessionsList;
}
public int deletePersonSession(String cPid,String eventId){
SQLiteDatabase db = this.getWritableDatabase();
int row = db.delete(TABLE_SESSIONS, KEY_CUR_PERSONID + " = ? AND " + KEY_SESSIONID + " = ?", new String[] { cPid, eventId});
if(db!=null)
db.close();
return row;
}
}