· 9 years ago · Nov 12, 2016, 06:46 PM
1public class DbAdapter {
2
3public static final String KEY_FROM = "title";
4public static final String KEY_TO = "body";
5public static final String KEY_ROWID = "_id";
6
7public static final String KEY_START = "start";
8public static final String KEY_ARRIVAL = "arrival";
9public static final String KEY_LINE = "line";
10public static final String KEY_DURATION = "duration";
11
12private static final String DATABASE_NAME = "data";
13private static final String DATABASE_NOTESTABLE = "notes";
14private static final String DATABASE_ROUTESTABLE = "routes";
15
16private static final String TAG = "DbAdapter";
17private DatabaseHelper mDbHelper;
18private SQLiteDatabase mDb;
19
20/**
21 * Database creation sql statement
22 */
23private static final String DATABASE_CREATE_NOTES =
24 "create table notes (_id integer primary key autoincrement, "
25 + "title text not null, body text not null)";
26
27private static final String DATABASE_CREATE_ROUTES =
28 "create table routes (_id integer primary key autoincrement, "
29 + "start , arrival , "
30 + "line , duration );";
31
32private static final int DATABASE_VERSION = 3;
33
34private final Context mCtx;
35
36private static class DatabaseHelper extends SQLiteOpenHelper {
37 DatabaseHelper(Context context) {
38 super(context, DATABASE_NAME, null, DATABASE_VERSION);
39 }
40
41 @Override
42 public void onCreate(SQLiteDatabase db) {
43 db.execSQL(DATABASE_CREATE_NOTES);
44 Log.d(TAG, "created notes table");
45
46 db.execSQL(DATABASE_CREATE_ROUTES);
47 Log.d(TAG, "created routes table");
48 }
49
50 @Override
51 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
52 Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
53 + newVersion + ", which will destroy all old data");
54 db.execSQL("DROP TABLE IF EXISTS notes");
55 db.execSQL("DROP TABLE IF EXISTS routes");
56 onCreate(db);
57 }
58}
59
60/**
61 * Constructor - takes the context to allow the database to be
62 * opened/created
63 *
64 * @param ctx the Context within which to work
65 */
66public DbAdapter(Context ctx) {
67 this.mCtx = ctx;
68}
69
70/**
71 * Open the notes database. If it cannot be opened, try to create a new
72 * instance of the database. If it cannot be created, throw an exception to
73 * signal the failure
74 *
75 * @return this (self reference, allowing this to be chained in an
76 * initialization call)
77 * @throws SQLException if the database could be neither opened or created
78 */
79public DbAdapter open() throws SQLException {
80 mDbHelper = new DatabaseHelper(mCtx);
81 mDb = mDbHelper.getWritableDatabase();
82 return this;
83}
84
85public void close() {
86 mDbHelper.close();
87}
88
89
90/**
91 * Create a new note using the title and body provided. If the note is
92 * successfully created return the new rowId for that note, otherwise return
93 * a -1 to indicate failure.
94 *
95 * @param title the title of the note
96 * @param body the body of the note
97 * @return rowId or -1 if failed
98 */
99public long createNote(String title, String body) {
100
101 ContentValues initialValues = new ContentValues();
102 initialValues.put(KEY_FROM, title);
103 initialValues.put(KEY_TO, body);
104
105 return mDb.insert(DATABASE_NOTESTABLE, null, initialValues);
106}
107
108/**
109 * Create a new route using the title and body provided. If the route is
110 * successfully created return the new rowId for that route, otherwise return
111 * a -1 to indicate failure.
112 *
113 * @param start the start time of the route
114 * @param arrival the arrival time of the route
115 * @param line the line number of the route
116 * @param duration the routes duration
117 * @return rowId or -1 if failed
118 */
119
120public long createRoute(String start, String arrival, String line, String duration){
121
122 ContentValues initialValues = new ContentValues();
123 initialValues.put(KEY_START, start);
124 initialValues.put(KEY_ARRIVAL, arrival);
125 initialValues.put(KEY_LINE, line);
126 initialValues.put(KEY_DURATION, duration);
127
128 return mDb.insert(DATABASE_ROUTESTABLE, null, initialValues);
129}
130
131/**
132 * Delete the note with the given rowId
133 *
134 * @param rowId id of note to delete
135 * @return true if deleted, false otherwise
136 */
137public boolean deleteNote(long rowId) {
138 return mDb.delete(DATABASE_NOTESTABLE, KEY_ROWID + "=" + rowId, null) > 0;
139}
140
141/**
142 * Return a Cursor over the list of all notes in the database
143 *
144 * @return Cursor over all notes
145 */
146public Cursor fetchAllNotes() {
147 return mDb.query(DATABASE_NOTESTABLE, new String[] {KEY_ROWID, KEY_FROM,
148 KEY_TO}, null, null, null, null, null);
149}
150
151/**
152 * Return a Cursor over the list of all routes in the database
153 *
154 * @return Cursor over all routes
155 */
156public Cursor fetchAllRoutes() {
157 return mDb.query(DATABASE_ROUTESTABLE, new String[] {KEY_ROWID, KEY_START,
158 KEY_ARRIVAL, KEY_LINE, KEY_DURATION}, null, null, null, null, null);
159}
160
161/**
162 * Return a Cursor positioned at the note that matches the given rowId
163 *
164 * @param rowId id of note to retrieve
165 * @return Cursor positioned to matching note, if found
166 * @throws SQLException if note could not be found/retrieved
167 */
168public Cursor fetchNote(long rowId) throws SQLException {
169 Cursor mCursor =
170 mDb.query(true, DATABASE_NOTESTABLE, new String[] {KEY_ROWID,
171 KEY_FROM, KEY_TO}, KEY_ROWID + "=" + rowId, null,
172 null, null, null, null);
173 if (mCursor != null) {
174 mCursor.moveToFirst();
175 }
176 return mCursor;
177
178}
179
180/**
181 * Return a Cursor positioned at the route that matches the given rowId
182 *
183 * @param rowId id of route to retrieve
184 * @return Cursor positioned to matching route
185 * @throws SQLException if note could not be found/retrieved
186 */
187public Cursor fetchRoute(long rowId) throws SQLException {
188 Cursor mCursor =
189 mDb.query(true, DATABASE_ROUTESTABLE, new String[] {KEY_ROWID,
190 KEY_START, KEY_ARRIVAL, KEY_LINE, KEY_DURATION}, KEY_ROWID + "=" + rowId, null,
191 null, null, null, null);
192 if (mCursor != null) {
193 mCursor.moveToFirst();
194 }
195 return mCursor;
196
197}
198
199/**
200 * Update the note using the details provided. The note to be updated is
201 * specified using the rowId, and it is altered to use the title and body
202 * values passed in
203 *
204 * @param rowId id of note to update
205 * @param title value to set note title to
206 * @param body value to set note body to
207 * @return true if the note was successfully updated, false otherwise
208 */
209public boolean updateNote(long rowId, String title, String body) {
210 ContentValues args = new ContentValues();
211 args.put(KEY_FROM, title);
212 args.put(KEY_TO, body);
213 return mDb.update(DATABASE_NOTESTABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
214}
215
216public void deleteRoutesTable(SQLiteDatabase db){
217 db.delete("routes", null, null);
218}
219}
220
221public void deleteTableRoutes(SQLiteDataBase db){
222 db.delete("routes", null, null);
223}
224
225@Override
226 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
227 Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
228 + newVersion + ", which will destroy all old data");
229 db.execSQL("DROP TABLE IF EXISTS notes");
230 onCreate(db);
231 }
232
233@Override
234 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
235 Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
236 + newVersion + ", which will destroy all old data");
237 db.execSQL("DROP TABLE IF EXISTS notes");
238 db.execSQL("DROP TABLE IF EXISTS routes"); // line added
239 onCreate(db);
240 }
241
242private static final int DATABASE_VERSION = 2;
243
244private static final int DATABASE_VERSION = 3;