mD20nDeR

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