9iCx8Khc

1// A helper class to work with SQLite database
2public class DatabaseHelper extends SQLiteOpenHelper {
3    // Database version, which should be upgraded each time we made changes in database structure
4    private static final int DATABASE_VERSION = 1;
5
6    // Database name
7    private static final String DATABASE_NAME = "notes_db";
8
9    public DatabaseHelper(Context context) {
10        super(context, DATABASE_NAME, null, DATABASE_VERSION);
11    }
12
13    // Creating tables
14    @Override
15    public void onCreate(SQLiteDatabase db) {
16
17        // create notes table
18        db.execSQL(Note.CREATE_TABLE);
19    }
20
21    // Upgrading database
22    @Override
23    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
24        // Drop (or delete) older table if existed
25        db.execSQL("DROP TABLE IF EXISTS " + Note.TABLE_NAME);
26
27        // Create tables again
28        onCreate(db);
29    }
30
31    public long insertNote(String note) {
32        // get writable database as we want to write data
33        SQLiteDatabase db = this.getWritableDatabase();
34
35        ContentValues values = new ContentValues();
36        // `id` and `timestamp` will be inserted automatically.
37        // no need to add them
38        values.put(Note.COLUMN_NOTE, note);
39
40        // insert row
41        long id = db.insert(Note.TABLE_NAME, null, values);
42
43        // close db connection
44        db.close();
45
46        // return newly inserted row id
47        return id;
48    }
49
50    public Note getNote(long id) {
51        // get readable database as we are not inserting anything
52        SQLiteDatabase db = this.getReadableDatabase();
53
54        // creating a Cursor object to be able to lookup the table
55        Cursor cursor = db.query(Note.TABLE_NAME,
56                new String[]{Note.COLUMN_ID, Note.COLUMN_NOTE, Note.COLUMN_TIMESTAMP},
57                Note.COLUMN_ID + "=?",
58                new String[]{String.valueOf(id)}, null, null, null, null);
59
60        if (cursor != null)
61            cursor.moveToFirst();
62
63        // prepare note object
64        Note note = new Note(
65                cursor.getInt(cursor.getColumnIndex(Note.COLUMN_ID)),
66                cursor.getString(cursor.getColumnIndex(Note.COLUMN_NOTE)),
67                cursor.getString(cursor.getColumnIndex(Note.COLUMN_TIMESTAMP)));
68
69        // close the db connection
70        cursor.close();
71
72        return note;
73    }
74
75    public List<Note> getAllNotes() {
76        List<Note> notes = new ArrayList<>();
77
78        // query to select all the data from the notes table
79        String selectQuery = "SELECT * FROM " + Note.TABLE_NAME + " ORDER BY " +
80                Note.COLUMN_TIMESTAMP + " DESC";
81
82        SQLiteDatabase db = this.getWritableDatabase();
83        Cursor cursor = db.rawQuery(selectQuery, null);
84
85        // looping through all rows and adding to list
86        if (cursor.moveToFirst()) {
87            do {
88                Note note = new Note();
89                note.setId(cursor.getInt(cursor.getColumnIndex(Note.COLUMN_ID)));
90                note.setNote(cursor.getString(cursor.getColumnIndex(Note.COLUMN_NOTE)));
91                note.setTimestamp(cursor.getString(cursor.getColumnIndex(Note.COLUMN_TIMESTAMP)));
92
93                notes.add(note);
94            } while (cursor.moveToNext());
95        }
96
97        // close db connection
98        db.close();
99
100        // return notes list
101        return notes;
102    }
103
104    public int getNotesCount() {
105        String countQuery = "SELECT  * FROM " + Note.TABLE_NAME;
106        SQLiteDatabase db = this.getReadableDatabase();
107        Cursor cursor = db.rawQuery(countQuery, null);
108
109        int count = cursor.getCount();
110        cursor.close();
111
112        // return count
113        return count;
114    }
115
116    public int updateNote(Note note) {
117        SQLiteDatabase db = this.getWritableDatabase();
118
119        ContentValues values = new ContentValues();
120        values.put(Note.COLUMN_NOTE, note.getNote());
121
122        // updating row
123        int row = db.update(Note.TABLE_NAME, values, Note.COLUMN_ID + " = ?",
124                new String[]{String.valueOf(note.getId())});
125        
126        db.close();
127        
128        return row;
129    }
130
131    public void deleteNote(Note note) {
132        SQLiteDatabase db = this.getWritableDatabase();
133        db.delete(Note.TABLE_NAME, Note.COLUMN_ID + " = ?",
134                new String[]{String.valueOf(note.getId())});
135        db.close();
136    }
137}