5tKQs4ba

1public class NoteDatabase {
2	private static final String TAG = "NoteDatabase";
3	
4	public static final String TABLE_NAME = "notes";
5	public static final String KEY_ROWID = "_id";
6	public static final String KEY_TITLE = "title";
7	public static final String KEY_BODY = "body";
8	public static final String KEY_CREATED = "created";
9	public static final String KEY_MODIFIED = "modified";
10	
11	private NoteDatabaseHelper dbHelper;
12	private SQLiteDatabase db;
13	private Context context;
14
15    /** Internal sqlite helper class used to create or upgrade the database. */
16	private class NoteDatabaseHelper extends SQLiteOpenHelper {
17
18    	private static final String DATABASE_NAME = "notes.db";
19    	private static final int DATABASE_VERSION = 1;
20    	
21        /** Constructor */
22    	NoteDatabaseHelper(Context context) {
23            super(context, DATABASE_NAME, null, DATABASE_VERSION);
24        }
25
26    	/** Create the database */
27        @Override
28        public void onCreate(SQLiteDatabase db) {
29            db.execSQL(String.format("CREATE TABLE %s (%s INTEGER PRIMARY KEY AUTOINCREMENT, %s TEXT NOT NULL, %s TEXT NOT NULL, " +
30            		"%s INTEGER NOT NULL, %s INTEGER NOT NULL)", TABLE_NAME, KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_CREATED, KEY_MODIFIED));
31        }
32        
33        /** Upgrade the database */
34        @Override
35        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
36            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
37            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
38            onCreate(db);
39        }
40    }
41    
42    /** Constructor */
43    public NoteDatabase(Context context) {
44        this.context = context;
45    }
46
47    /** Open the database */
48    public NoteDatabase open() throws SQLException {
49    	dbHelper = new NoteDatabaseHelper(context);
50        db = dbHelper.getWritableDatabase();
51        return this;
52    }
53    
54    /** Close the database */
55    public void close() {
56    	dbHelper.close();
57    }
58
59    /** Insert a note into the database. */
60    public long insertNote(String title, String body) {
61    	long timestamp = System.currentTimeMillis();
62    	
63    	ContentValues values = new ContentValues();
64    	values.put(KEY_TITLE, title);
65    	values.put(KEY_BODY, body);
66    	values.put(KEY_CREATED, timestamp);
67    	values.put(KEY_MODIFIED, timestamp);
68        return db.insert(TABLE_NAME, null, values);
69    }
70
71    /** Update a note in the database. */
72    public boolean updateNote(long rowId, String title, String body) {
73    	ContentValues values = new ContentValues();
74    	values.put(KEY_TITLE, title);
75    	values.put(KEY_BODY, body);
76    	values.put(KEY_MODIFIED, System.currentTimeMillis());
77        return db.update(TABLE_NAME, values, KEY_ROWID + "=" + rowId, null) > 0;
78    }
79    
80    /** Delete a note from the database. */
81    public boolean deleteNote(long rowId) {
82        return db.delete(TABLE_NAME, KEY_ROWID + "=" + rowId, null) > 0;
83    }
84
85    /** Fetch the note that matches the given rowId */
86    public Cursor fetchNote(long rowId) {
87        Cursor cursor = db.query(true, TABLE_NAME, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_CREATED, KEY_MODIFIED}, 
88        		KEY_ROWID + "=" + rowId, null, null, null, null, null);
89        if (cursor != null) {
90        	cursor.moveToFirst();
91        }
92        return cursor;
93    }
94    
95    /** Fetch all notes that match the given title (using wildcards) */
96    public Cursor fetchNotes(String title, String sortOrder) {
97    	title = (!TextUtils.isEmpty(title)) ? title : "";  // make sure the title isn't null
98    	return db.query(TABLE_NAME, new String[] {KEY_ROWID, KEY_TITLE, KEY_BODY, KEY_CREATED, KEY_MODIFIED}, 
99    			KEY_TITLE + " LIKE '%" + title + "%'", null, null, null, sortOrder);
100    }
101}