· 9 years ago · Nov 11, 2016, 10:08 AM
1public static final String DATABASE_NAME = "AppData.db";
2
3/**
4 * table note contain id, title, content
5 */
6public static final String TABLE_DATA_NAME = "tb_mydata";
7public static final String TABLE_SETTING_NAME = "tb_mysetting";
8public static final String NOTE_COLUMN_ID = "id";
9public static final String NOTE_COLUMN_COLOR = "color";
10public static final String NOTE_COLUMN_SENSORID = "sensorid";
11public static final String NOTE_COLUMN_TITLE = "title";
12public static final String NOTE_COLUMN_VALUE = "value";
13public static final String NOTE_COLUMN_TIME = "time";
14public static final String NOTE_SETTING_LOC = "loc";
15public static final String NOTE_SETTING_LANG = "lang";
16public static final String NOTE_SETTING_SENS = "sensitive";
17/**
18 * string for create table note
19 */
20public static final String CREATE_TABLE_DATA_NOTE =
21 "CREATE TABLE " + TABLE_DATA_NAME + "(" +
22 NOTE_COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" +
23 "," + NOTE_COLUMN_SENSORID + "INTEGER" +
24 "," + NOTE_COLUMN_TITLE + " TEXT NOT NULL" +
25 "," + NOTE_COLUMN_VALUE + " TEXT NOT NULL" +
26 "," + NOTE_COLUMN_TIME + "DATETIME" +
27 "," + NOTE_COLUMN_COLOR + "TEXT" +
28 ")";
29public static final String CREATE_TABLE_SETTING_NOTE =
30 "CREATE TABLE " + TABLE_SETTING_NAME + "(" +
31 NOTE_COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL" +
32 "," + NOTE_SETTING_LANG + "TEXT NOT NULL" +
33 "," + NOTE_SETTING_LOC + " TEXT NOT NULL" +
34 "," + NOTE_SETTING_SENS + " TEXT NOT NULL" +
35 ")";
36
37/**
38 * value for update database
39 */
40public static final int DATA_VERSION = 1;
41
42/**
43 * Sqlite database
44 */
45private SQLiteDatabase db;
46
47public MySQLHelper(Context context) {
48
49 super(context, DATABASE_NAME, null, DATA_VERSION);
50}
51
52/**
53 * create db when app start, and only call when database don't create
54 * When database created, it will not call
55 */
56@Override
57public void onCreate(SQLiteDatabase db) {
58 db.execSQL(CREATE_TABLE_DATA_NOTE);
59 db.execSQL(CREATE_TABLE_SETTING_NOTE);
60}
61
62/**
63 * call when change DATA_VERSION
64 * help we update database
65 */
66@Override
67public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
68 // TODO Auto-generated method stub
69 db.execSQL("DROP TABLE IF EXISTS contacts");
70 onCreate(db);
71}
72
73/**
74 * open database
75 */
76public void open() {
77 try {
78 db = getWritableDatabase();
79 } catch (Exception e) {
80 e.printStackTrace();
81 }
82}
83
84/**
85 * close database
86 */
87public void close() {
88 if (db != null && db.isOpen()) {
89 try {
90 db.close();
91 } catch (SQLException e) {
92 e.printStackTrace();
93 }
94 }
95}
96
97public Cursor getAll(String sql) {
98 open();
99 Cursor cursor = db.rawQuery(sql, null);
100 if (cursor != null) {
101 cursor.moveToFirst();
102 }
103 close();
104 return cursor;
105}
106
107
108public boolean insertNote(String sensorid, String title, String value, String time, String color) {
109 SQLiteDatabase db = this.getWritableDatabase();
110 ContentValues contentValues = new ContentValues();
111 contentValues.put(NOTE_COLUMN_SENSORID,sensorid);
112 contentValues.put(NOTE_COLUMN_TITLE, title);
113 contentValues.put(NOTE_COLUMN_VALUE, value);
114 contentValues.put(NOTE_COLUMN_TIME,time);
115 contentValues.put(NOTE_COLUMN_COLOR, color);
116 try {
117 db.insert(TABLE_DATA_NAME, null, contentValues);
118 return true;
119 } catch (Exception e) {
120 return false;
121 }
122}
123
124public boolean insertSettingNote(String lang, String loc, String sens) {
125 SQLiteDatabase db = this.getWritableDatabase();
126 ContentValues contentValues = new ContentValues();
127 contentValues.put(NOTE_SETTING_LANG,lang);
128 contentValues.put(NOTE_SETTING_LOC, loc);
129 contentValues.put(NOTE_SETTING_SENS, sens);
130 try {
131 db.insert(TABLE_SETTING_NAME, null, contentValues);
132 return true;
133 } catch (Exception e) {
134 return false;
135 }
136}
137
138/**
139 * update values to table
140 *
141 * @return index row update
142 */
143public boolean update(String table, ContentValues values, String where) {
144 open();
145 //values.put();
146 long index = db.update(table, values, where, null);
147 close();
148 return index > 0;
149}
150
151
152
153/**
154 * delete id row of table
155 */
156public boolean delete(String table, String where) {
157 open();
158 long index = db.delete(table, where, null);
159 close();
160 return index > 0;
161}
162
163public Note getNote(String sql) {
164 Note note = null;
165 Cursor cursor = getAll(sql);
166 if (cursor != null) {
167 note = cursorToNote(cursor);
168 cursor.close();
169 }
170 return note;
171}
172public SettingNote getSettingNote(String sql) {
173 SettingNote stnote = null;
174 Cursor cursor = getAll(sql);
175 if (cursor != null) {
176 stnote = cursorToSettingNote(cursor);
177 cursor.close();
178 }
179 return stnote;
180}
181
182/**
183 * @param sql get all notes with sql command
184 * @return arraylist of note
185 */
186public ArrayList<Note> getListNote(String sql) {
187 ArrayList<Note> list = new ArrayList<>();
188 Cursor cursor = getAll(sql);
189
190 while (!cursor.isAfterLast()) {
191 list.add(cursorToNote(cursor));
192 cursor.moveToNext();
193 }
194 cursor.close();
195
196 return list;
197}
198
199/**
200 * insert note to table
201 *
202 * @param note note to insert
203 * @return id of note
204 */
205
206
207/**
208 * @param note note to update
209 * @return id of note update
210 */
211public boolean updateNote(Note note) {
212 return update(TABLE_DATA_NAME, noteToValues(note), NOTE_COLUMN_ID + " = " + note.getId());
213}
214
215/**
216 * delete id row of table
217 */
218public boolean deleteNote(Note note) {
219 return delete(TABLE_DATA_NAME, NOTE_COLUMN_ID + " = " + note.getId());
220}
221
222/**
223 * convert note to contentvalues
224 * don't put id of note because
225 * when insert id will auto create
226 * when update we don't update id
227 */
228private ContentValues noteToValues(Note note) {
229 ContentValues values = new ContentValues();
230 values.put(NOTE_COLUMN_TITLE, note.getTitle());
231 values.put(NOTE_COLUMN_VALUE, note.getValue());
232 return values;
233}
234
235private ContentValues noteToValues(SettingNote stnote) {
236 ContentValues values = new ContentValues();
237 values.put(NOTE_SETTING_SENS, stnote.getSensitive());
238 values.put(NOTE_SETTING_LOC, stnote.getLoc());
239 values.put(NOTE_SETTING_LANG, stnote.getLang());
240 return values;
241}
242/**
243 * convert cursor to note
244 */
245private Note cursorToNote(Cursor cursor) {
246 Note note = new Note();
247 note.setId(cursor.getInt(cursor.getColumnIndex(NOTE_COLUMN_ID)))
248 .setTitle(cursor.getString(cursor.getColumnIndex(NOTE_COLUMN_TITLE)))
249 .setvalue(cursor.getString(cursor.getColumnIndex(NOTE_COLUMN_VALUE)));
250 return note;
251}
252
253private SettingNote cursorToSettingNote(Cursor cursor) {
254 SettingNote Stnote = new SettingNote();
255 Stnote.setId(cursor.getInt(cursor.getColumnIndex(NOTE_COLUMN_ID)));
256 Stnote.setLang(cursor.getString(cursor.getColumnIndex(NOTE_SETTING_LANG)));
257 Stnote.setLoc(cursor.getString(cursor.getColumnIndex(NOTE_SETTING_LOC)));
258 Stnote.setSensitive(cursor.getString(cursor.getColumnIndex(NOTE_SETTING_SENS)));
259 return Stnote;
260}
261
262public long getProfilesCount(String databasename) {
263 SQLiteDatabase db = this.getReadableDatabase();
264 long cnt = DatabaseUtils.queryNumEntries(db, databasename);
265 db.close();
266 return cnt;
267}
268
269
270RadioButton mRGLangEng, mRGLangViet, mRGLocHN, mRGLocDN;
271CheckBox Sens;
272Locale myLocale;
273MySQLHelper sqlHelper;
274SettingNote stNote;
275Button Save;
276
277@Override
278public View onCreateView(LayoutInflater inflater, ViewGroup container,
279 Bundle savedInstanceState) {
280 View view = inflater.inflate(R.layout.view_setting, container, false);
281 Sens = (CheckBox) view.findViewById(R.id.SensCheck);
282 mRGLangEng = (RadioButton) view.findViewById(R.id.Lang_Eng);
283 mRGLangViet = (RadioButton) view.findViewById(R.id.Lang_Vie);
284 mRGLocHN = (RadioButton) view.findViewById(R.id.locHanoi);
285 mRGLocDN = (RadioButton) view.findViewById(R.id.locDanang);
286 Save = (Button) view.findViewById(R.id.save);
287 sqlHelper = new MySQLHelper(this.getActivity());
288 sqlHelper.getSettingNote(MySQLHelper.TABLE_SETTING_NAME);
289 if (stNote.getLang() == "en") {
290 mRGLangViet.setChecked(false);
291 mRGLangEng.setChecked(true);
292 } else {
293 mRGLangViet.setChecked(true);
294 mRGLangEng.setChecked(false);
295 }
296 if (stNote.getSensitive() == "no") {
297 Sens.setChecked(false);
298 Constants.USER_LIMITATION = 200;
299 } else {
300 Sens.setChecked(true);
301 Constants.USER_LIMITATION = 150;
302 }
303 if (stNote.getLang() == "hn") {
304 mRGLocHN.setChecked(true);
305 mRGLocDN.setChecked(false);
306 Constants.API_TYPE_AQI = 1; // Sensor's ID at HN_RDC
307 Constants.API_TYPE_TMP = 2;
308 Constants.API_TYPE_HUM = 3;
309
310 } else {
311 mRGLocHN.setChecked(false);
312 mRGLocDN.setChecked(true);
313 Constants.API_TYPE_AQI = 7;
314 Constants.API_TYPE_TMP = 8;
315 Constants.API_TYPE_HUM = 9;
316
317 }
318
319 mRGLocHN.setOnClickListener(new View.OnClickListener() {
320 @Override
321 public void onClick(View v) {
322 mRGLocDN.setChecked(false);
323 Constants.STR_LOC = "hn";
324 }
325 });
326
327 mRGLocDN.setOnClickListener(new View.OnClickListener() {
328 @Override
329 public void onClick(View v) {
330 mRGLocHN.setChecked(false);
331 Constants.STR_LOC = "dn";
332 }
333 });
334
335 mRGLangViet.setOnClickListener(new View.OnClickListener() {
336 @Override
337 public void onClick(View v) {
338 mRGLangEng.setChecked(false);
339 Constants.STR_LANG = "vi";
340 setLocale("vi");
341 }
342 });
343
344 mRGLangEng.setOnClickListener(new View.OnClickListener() {
345 @Override
346 public void onClick(View v) {
347 mRGLangViet.setChecked(false);
348 Constants.STR_LANG = "en";
349 setLocale("en");
350 }
351 });
352
353 Sens.setOnClickListener(new View.OnClickListener() {
354 @Override
355 public void onClick(View v) {
356 if (Sens.isChecked()){
357 Sens.setChecked(false);
358 Constants.STR_SENS = "no";
359 }else{
360 Sens.setChecked(true);
361 Constants.STR_SENS = "yes";
362 }
363 }
364 });
365
366 Save.setOnClickListener(new View.OnClickListener() {
367 @Override
368 public void onClick(View v) {
369 ContentValues cv = new ContentValues();
370 cv.put(MySQLHelper.NOTE_SETTING_LANG,Constants.STR_LANG);
371 cv.put(MySQLHelper.NOTE_SETTING_LOC, Constants.STR_LOC);
372 cv.put(MySQLHelper.NOTE_SETTING_SENS, Constants.STR_SENS);
373 sqlHelper.update(MySQLHelper.TABLE_SETTING_NAME,cv,"id = 1");
374 }
375 });
376
377
378 return view;
379}
380
381public void setLocale(String lang) {
382 myLocale = new Locale(lang);
383 Resources res = getResources();
384 DisplayMetrics dm = res.getDisplayMetrics();
385 Configuration conf = res.getConfiguration();
386 conf.locale = myLocale;
387 res.updateConfiguration(conf, dm);
388}
389}
390
39111-10 21:54:14.487 2174-2174/com.journaldev.viewpager E/AndroidRuntime: FATAL EXCEPTION: main
392Process: com.journaldev.viewpager, PID: 2174
393android.database.CursorIndexOutOfBoundsException: Index 0 requested, with a size of 0
394 at android.database.AbstractCursor.checkPosition(AbstractCursor.java:426)
395 at android.database.AbstractWindowedCursor.checkPosition(AbstractWindowedCursor.java:136)
396 at android.database.AbstractWindowedCursor.getInt(AbstractWindowedCursor.java:68)
397 at com.journaldev.viewpager.MyApplication.MyApp.MySQLHelper.cursorToSettingNote(MySQLHelper.java:270)
398 at com.journaldev.viewpager.MyApplication.MyApp.MySQLHelper.getSettingNote(MySQLHelper.java:191)
399 at com.journaldev.viewpager.MyApplication.MyApp.TabSetting.onCreateView(TabSetting.java:41)
400 at android.support.v4.app.Fragment.performCreateView(Fragment.java:1974)
401 at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1067)
402 at android.support.v4.app.FragmentManagerImpl.moveToState(FragmentManager.java:1252)
403 at android.support.v4.app.BackStackRecord.run(BackStackRecord.java:742)
404 at android.support.v4.app.FragmentManagerImpl.execPendingActions(FragmentManager.java:1617)
405 at android.support.v4.app.FragmentManagerImpl.executePendingTransactions(FragmentManager.java:570)
406 at android.support.v4.app.FragmentPagerAdapter.finishUpdate(FragmentPagerAdapter.java:141)
407 at android.support.v4.view.ViewPager.populate(ViewPager.java:1177)
408 at android.support.v4.view.ViewPager.populate(ViewPager.java:1025)
409 at android.support.v4.view.ViewPager$3.run(ViewPager.java:254)
410 at android.view.Choreographer$CallbackRecord.run(Choreographer.java:767)
411 at android.view.Choreographer.doCallbacks(Choreographer.java:580)
412 at android.view.Choreographer.doFrame(Choreographer.java:549)
413 at android.view.Choreographer$FrameDisplayEventReceiver.run(Choreographer.java:753)
414 at android.os.Handler.handleCallback(Handler.java:739)
415 at android.os.Handler.dispatchMessage(Handler.java:95)
416 at android.os.Looper.loop(Looper.java:135)
417 at android.app.ActivityThread.main(ActivityThread.java:5221)
418 at java.lang.reflect.Method.invoke(Native Method)
419 at java.lang.reflect.Method.invoke(Method.java:372)
420 at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:899)
421 at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:694)
422
42311-10 21:54:42.516 2174-2174/com.journaldev.viewpager I/Process: Sending signal. PID: 2174 SIG: 9
424
425Cursor cursor = null;
426
427try {
428
429 String selectQuery = "your query";
430
431 cursor = db.rawQuery(selectQuery, null);
432
433 if (cursor != null && cursor.getCount() > 0) {
434 if (cursor.moveToFirst()) {
435 do {
436 //Looping cursor and fetching the value...
437 } while (cursor.moveToNext());
438 }
439 }
440} catch (Exception e) {
441 e.printStackTrace();
442} finally {
443 if (cursor != null && !cursor.isClosed()) {
444 cursor.close();
445 }
446}
447
448sqlHelper.getSettingNote("select * from "+MySQLHelper.TABLE_SETTING_NAME);