· 10 years ago · Sep 23, 2016, 12:38 PM
1DatabaseHelper db = new DatabaseHelper(this);
2 db.OpenDB();
3 try {
4 db.CreateTable(new myClass1());
5 db.CreateTable(new myClass2());
6 ...
7 } catch (Exception e) {
8 e.printStackTrace();
9 }
10
11import android.content.Context;
12import java.util.ArrayList;
13import java.util.List;
14import java.util.UUID;
15
16interface IGenericClass {
17 ArrayList<?> SelectAll(Class<?> type, Context ctx, String whereClause);
18
19 int SelectCount(Class<?> type, Context ctx, String whereClause);
20
21 boolean SaveAll(List<?> objects, Context ctx);
22
23 boolean Save(Object object, Context ctx);
24
25 boolean Save(Context ctx);
26
27 boolean UpdateObject(Context ctx);
28
29 Integer SumColumn(Class<?> type, Context ctx, String whereClause, String columnName);
30
31 boolean DeleteAll(Class<?> type, Context ctx);
32
33 boolean Delete(Class<?> ciboClass, Context ctx, String whereClause);
34
35 Object SelectById(Class<?> type, Context ctx, UUID id);
36}
37
38public class GenericClass implements IGenericClass {
39
40 public ArrayList<?> SelectAll(Class<?> type, Context ctx, String whereClause) {
41
42 DatabaseHelper db = new DatabaseHelper(ctx);
43 db.OpenDB();
44
45 ArrayList<?> returnList = new ArrayList<>();
46 try {
47 return db.SelectAll(this.getClass(), whereClause);
48 } catch (Exception ex) {
49 return null;
50 }
51 }
52
53 public int SelectCount(Class<?> type, Context ctx, String whereClause) {
54
55 DatabaseHelper db = new DatabaseHelper(ctx);
56 db.OpenDB();
57
58 try {
59 return db.SelectCount(this.getClass(), whereClause);
60 } catch (Exception ex) {
61 return -1;
62 }
63 }
64
65 public Integer SumColumn(Class<?> type, Context ctx, String whereClause, String columnName) {
66 DatabaseHelper db = new DatabaseHelper(ctx);
67 db.OpenDB();
68 try {
69 return db.SumColumn(this.getClass(), whereClause, columnName);
70 } catch (Exception ex) {
71 return -1;
72 }
73 }
74
75 public boolean Save(Object object, Context ctx) {
76 DatabaseHelper db = new DatabaseHelper(ctx);
77 db.OpenDB();
78 return db.Save(object);
79 }
80
81 public boolean Save(Context ctx) {
82 DatabaseHelper db = new DatabaseHelper(ctx);
83 db.OpenDB();
84
85 return db.Save(this);
86 }
87
88 public boolean SaveAll(List<?> objects, Context ctx) {
89 DatabaseHelper db = new DatabaseHelper(ctx);
90 db.OpenDB();
91
92 return db.SaveAll(objects);
93 }
94
95 public boolean UpdateObject(Context ctx) {
96 DatabaseHelper db = new DatabaseHelper(ctx);
97 db.OpenDB();
98 db.UpdateObject(this);
99
100 return true;
101
102 }
103
104 public boolean DeleteAll(Class<?> type, Context ctx) {
105 DatabaseHelper db = new DatabaseHelper(ctx);
106 db.OpenDB();
107 return db.DeleteAll(type);
108 }
109
110 public boolean Delete(Class<?> ciboClass, Context ctx, String whereClause) {
111 DatabaseHelper db = new DatabaseHelper(ctx);
112 db.OpenDB();
113 return db.Delete(ciboClass, whereClause);
114 }
115
116 public Object SelectById(Class<?> type, Context ctx, UUID id) {
117 DatabaseHelper db = new DatabaseHelper(ctx);
118 db.OpenDB();
119 return db.SelectById(type, id);
120 }
121}
122
123import android.content.Context;
124import android.database.Cursor;
125import android.database.sqlite.SQLiteDatabase;
126import android.os.Build;
127import android.util.Pair;
128import java.lang.reflect.Field;
129import java.util.ArrayList;
130import java.util.Date;
131import java.util.List;
132import java.util.Objects;
133import java.util.UUID;
134
135interface IDatabaseHelper {
136 ArrayList<?> SelectAll(Class<?> tipo, String whereClause);
137
138 int SelectCount(Class<?> type, String whereClause);
139
140 boolean Save(Object object);
141
142 boolean SaveAll(List<?> objects);
143
144 boolean OpenDB();
145
146 boolean CreateTable(Object object);
147
148 void Close();
149
150 Integer SumColumn(Class<?> type, String whereClause, String columnName);
151
152 boolean DeleteAll(Class<?> type);
153
154 Object SelectById(Class<?> type, UUID id);
155
156 boolean Delete(Class<?> type, String whereClause);
157
158 boolean UpdateObject(Object objToUpdate);
159
160}
161
162public class DatabaseHelper implements IDatabaseHelper {
163 private static final String DATABASE_NAME = "myDatabase.db";
164 private static String DATABASE_FULLPATH = "";
165 private static SQLiteDatabase database;
166// private static SimpleDateFormat simpleDateFormat = new SimpleDateFormat("dd/MM/yyyy", Locale.getDefault());
167
168
169 //constructor
170 public DatabaseHelper(Context context) {
171 DATABASE_FULLPATH = context.getFilesDir().getPath() + "/" + DATABASE_NAME;
172 }
173
174 //returns all object of the given class
175 public ArrayList<?> SelectAll(Class<?> type, String whereClause) {
176 if (whereClause == null) {
177 whereClause = "";
178 }
179 String query = "select * from " + type.getSimpleName() + " " + whereClause;
180 Cursor cursor = database.rawQuery(query, null);
181 ArrayList list = new ArrayList();
182 try {
183 if (cursor.moveToFirst()) {
184 while (!cursor.isAfterLast()) {
185 Object o = GetObjectFromCursor(type, cursor);
186 list.add(o);
187 cursor.moveToNext();
188 }
189 }
190 cursor.close();
191 return list;
192 } catch (Exception ex) {
193 return null;
194 }
195 }
196
197 //returns the count of records of given type
198 public int SelectCount(Class<?> type, String whereClause) {
199 try {
200 if (whereClause == null) {
201 whereClause = "";
202 }
203 String query = "select count(*) from " + type.getSimpleName() + " " + whereClause;
204 Cursor cursor = database.rawQuery(query, null);
205 cursor.moveToFirst();
206 int count = cursor.getInt(0);
207 cursor.close();
208 return count;
209 } catch (Exception ex) {
210 return -1;
211 }
212 }
213
214 //save an object
215 public boolean Save(Object object) {
216
217 //we build the query for each object
218 String insertQuery = "insert into " + object.getClass().getSimpleName() + "(";
219 try {
220 ArrayList<Pair<String, Object>> name_value = GetFieldNameValue(object);
221
222 String tableNames = "";
223 String tableValues = "";
224 //for each record we add the values and the field names
225 for (Pair<String, Object> pair : name_value) {
226 tableNames += pair.first + ",";
227 tableValues += "'" + pair.second.toString() + "'" + ",";
228 }
229
230 //remove the last comma
231 tableNames = tableNames.substring(0, tableNames.length() - 1);
232 tableValues = tableValues.substring(0, tableValues.length() - 1);
233
234 //finished adjusting query
235 insertQuery += tableNames + ")values(" + tableValues + ");";
236 database.execSQL(insertQuery);
237 return true;
238 } catch (Exception ex) {
239 return false;
240 }
241 }
242
243 //save multiple objects into db
244 public boolean SaveAll(List<?> objects) {
245 int saved = 0;
246 for (Object object : objects) {
247 if (Save(object)) {
248 saved++;
249 } else {
250 return false;
251 }
252 }
253 return saved == objects.size();
254 }
255
256 //open db
257 public boolean OpenDB() {
258 try {
259 database = SQLiteDatabase.openOrCreateDatabase(DATABASE_FULLPATH, null, null);
260 return true;
261 } catch (Exception ex) {
262 return false;
263 }
264 }
265
266 //create a table if not exists
267 public boolean CreateTable(Object object) {
268 try {
269 String query = GetCreateQueryFromObject(object);
270 database.execSQL(query);
271
272 //we check for each field if it exists, if not it create the field on the database
273 String className = object.getClass().getSimpleName();
274 try {
275 List<Pair<String, Object>> fields = GetFieldNameValue(object);
276 for (Pair<String, Object> c : fields) {
277 if (!CheckColumnExistInTable(className, c.first)) {
278 String addColumnSql = "alter table ";
279 addColumnSql += className;
280 addColumnSql += " add ";
281 addColumnSql += c.first;
282 String columnType = GetSQLFieldType(c.first, c.second.getClass().getSimpleName());
283 addColumnSql += " " + columnType;
284 database.execSQL(addColumnSql);
285 }
286 }
287 return true;
288 } catch (Exception ex) {
289 return false;
290 }
291 }catch(Exception ex){
292 return false;
293 }
294 }
295
296 //closes db
297 public void Close() {
298 database.close();
299 }
300
301 //sum a column value for a given table
302 public Integer SumColumn(Class<?> type, String whereClause, String columnName) {
303 String sql = "select sum(" + columnName + ") as total from " + type.getSimpleName() + " " + whereClause;
304 Cursor cursor = database.rawQuery(sql, null);
305 int columnIndex = cursor.getColumnIndex("total");
306 if (columnIndex == -1) {
307 return 0;
308 }
309 if (cursor.moveToFirst()) {
310 int value = cursor.getInt(columnIndex);
311 cursor.close();
312 return value;
313 } else {
314 cursor.close();
315 return 0;
316 }
317 }
318
319 //delete all record in a table
320 public boolean DeleteAll(Class<?> type) {
321 String sql = "delete from " + type.getSimpleName();
322 database.execSQL(sql);
323 return true;
324 }
325
326 //select a record from id
327 public Object SelectById(Class<?> type, UUID id) {
328
329 String query = "select * from " + type.getSimpleName() + " where id='" + id.toString() + "'";
330 Cursor cursor = database.rawQuery(query, null);
331 if (cursor.moveToFirst()) {
332 try {
333 Object object = GetObjectFromCursor(type, cursor);
334 cursor.close();
335 return object;
336 } catch(Exception ex){
337 return null;
338 }
339 } else {
340 return null;
341 }
342 }
343
344 //delete all records with a given condition
345 public boolean Delete(Class<?> type, String whereClause) {
346 try {
347 String sql = "delete from " + type.getSimpleName() + " " + whereClause;
348 database.execSQL(sql);
349 return true;
350 } catch (Exception ex) {
351 return false;
352 }
353 }
354
355 //update an object from his id
356 public boolean UpdateObject(Object objToUpdate) {
357 try {
358 Field field = objToUpdate.getClass().getField("id");
359 int id = (int) field.get(objToUpdate);
360 String whereClause = "where id = " + id;
361
362 String sqlQuery = "update " + objToUpdate.getClass().getSimpleName() + " set ";
363 ArrayList<Pair<String, Object>> name_value = GetFieldNameValue(objToUpdate);
364
365 //for each field we add name and value
366 for (Pair<String, Object> pair : name_value) {
367 if (!pair.first.equals("id")) {
368 sqlQuery += pair.first + "=";
369 sqlQuery += "'" + pair.second.toString() + "'" + ",";
370 }
371 }
372
373 sqlQuery = sqlQuery.substring(0, sqlQuery.length() - 1);
374
375 sqlQuery += " ";
376 sqlQuery += whereClause;
377
378 database.execSQL(sqlQuery);
379 return true;
380
381
382 } catch (Exception ex) {
383 return false;
384 }
385 }
386
387 private boolean CheckColumnExistInTable(String tableName, String columnName) {
388 Cursor mCursor = null;
389 try {
390 // Query 1 row
391 mCursor = database.rawQuery("SELECT * FROM " + tableName + " LIMIT 0", null);
392
393 // getColumnIndex() gives us the index (0 to ...) of the column - otherwise we get a -1
394 return mCursor.getColumnIndex(columnName) != -1;
395
396 } catch (Exception Exp) {
397 return false;
398 } finally {
399 if (mCursor != null) mCursor.close();
400 }
401 }
402
403 //given a cursor and a class, it returns the object from the cursor
404 private Object GetObjectFromCursor(Class<?> tipo, Cursor cursor) throws Exception {
405 Field[] fields = tipo.getFields();
406 Object o = tipo.newInstance();
407 for (int i = 0; i < fields.length; i++) {
408 Object fieldValue = GetCursorFieldValue(cursor, i);
409 if (fieldValue != null) {
410 o = SetUnknownFieldValue(o, cursor.getColumnName(i), fieldValue);
411 }
412 }
413 return o;
414 }
415
416 //returns from a given object a fieldName - fieldValue map
417 private ArrayList<Pair<String, Object>> GetFieldNameValue(Object object) throws Exception {
418 Field[] fields = object.getClass().getFields();
419 ArrayList<Pair<String, Object>> pairs = new ArrayList<>();
420 for (Field f : fields) {
421 Object value = GetUnknownObjectFieldValue(f, object);
422 String fieldName = f.getName();
423
424 if (value == null || fieldName.isEmpty()) {
425 continue;
426 }
427 pairs.add(new Pair(fieldName, value));
428 }
429 return pairs;
430 }
431
432 //returns from an object and a field name, the value
433 private Object GetUnknownObjectFieldValue(Field field, Object object) throws Exception {
434 field.setAccessible(true);
435 Object o = field.get(object);
436
437 //we save dates as longs
438 Date date = new Date();
439 UUID uuid = UUID.randomUUID();
440 if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.KITKAT) {
441 if (o != null && Objects.equals(o.getClass(), date.getClass())) {
442 return new Date((long) o);
443 }
444 if (o != null && Objects.equals(o.getClass(), uuid.getClass())) {
445 return o.toString();
446 }
447 } else {
448 if (o != null && o.getClass().equals(date.getClass())) {
449 return new Date((long) o);
450 }
451 if (o != null && o.getClass().equals(uuid.getClass())) {
452 return o.toString();
453 }
454 }
455 return o;
456 }
457
458 //we set the value "val" on the field "field". we use this to value an object without having its name
459 private Object SetUnknownFieldValue(Object object, String fieldName, Object fieldValue) throws Exception {
460 Class<?> clazz = object.getClass();
461 Field field = clazz.getDeclaredField(fieldName);
462 field.setAccessible(true);
463 Object fieldCasted = CastField(field.getType(), fieldValue);
464 field.set(object, fieldCasted);
465 return object;
466
467 }
468
469 //we take the field type and the object way to convert the object in the required field
470 private Object CastField(Class fieldType, Object fieldValue) throws Exception {
471 switch (fieldType.getSimpleName().toLowerCase()) {
472 case "boolean":
473 if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.KITKAT) {
474 if (Objects.equals(fieldValue.getClass().getSimpleName(), "String")) {
475 return Objects.equals(fieldValue, "true");
476 }
477 } else {
478 if (fieldValue.getClass().getSimpleName().equals("String")) {
479 return fieldValue.equals("true");
480 }
481 }
482 break;
483 case "double":
484 if (fieldValue == null) {
485 return null;
486 }
487 return (double) Float.parseFloat(fieldValue.toString());
488 case "date":
489 return new Date((long) fieldValue);
490 case "uuid":
491 return UUID.fromString((String) fieldValue);
492 default:
493 return fieldValue;
494
495 }
496 return fieldValue;
497 }
498
499 //return a generic field value from cursor
500 private Object GetCursorFieldValue(Cursor cursor, int i) {
501 switch (cursor.getType(i)) {
502 /* FIELD_TYPE_NULL
503 FIELD_TYPE_INTEGER
504 FIELD_TYPE_FLOAT
505 FIELD_TYPE_STRING
506 FIELD_TYPE_BLOB */
507 case 0:
508 return null;
509 case 1:
510 return cursor.getInt(i);
511 case 2:
512 return cursor.getFloat(i);
513 case 3:
514 return cursor.getString(i);
515 case 4:
516 return cursor.getBlob(i);
517 default:
518 cursor.close();
519 return null;
520 }
521 }
522
523 //prende un oggetto e ne crea la query di creazione tabella
524 private String GetCreateQueryFromObject(Object object) throws Exception {
525 String fullQuery = "create table if not exists ";
526 String objectName = GetTableName(object);
527 fullQuery += objectName + " ";
528 String properties = GetPropertiesFromObject(object);
529 fullQuery += "(" + properties + ");";
530 return fullQuery;
531 }
532
533 //returns object properties as: (fieldName fieldType, fieldName fieldType,..)
534 private String GetPropertiesFromObject(Object object) throws Exception {
535 Class<?> tClass = object.getClass();
536 Field[] fieldsArray = tClass.getFields();
537 ArrayList<Pair<String, String>> fieldMap = GetFields(fieldsArray);//1 field name, 2 field type
538 String fields = "";
539 for (Pair<String, String> field : fieldMap) {
540 fields += field.first + " ";
541 fields += GetSQLFieldType(field.first, field.second) + ", ";
542 }
543 return fields.substring(0, fields.length() - 2);
544 }
545
546 //returns from a given type, the sql type required
547 private String GetSQLFieldType(String fieldName, String fieldType) throws Exception {
548
549 if (fieldName.toLowerCase().equals("id")) {
550 return "TEXT PRIMARY KEY UNIQUE";
551 }
552 switch (fieldType.toLowerCase()) {
553 case "uuid":
554 return "TEXT";
555 case "string":
556 return "TEXT";
557 case "int":
558 return "INT";
559 case "double":
560 return "DOUBLE";
561 case "boolean":
562 return "BOOLEAN";
563 case "float":
564 return "FLOAT";
565 case "integer":
566 return "INT";
567 case "date":
568 return "INT";
569 default:
570 return "BLOB";
571 }
572 }
573
574 //returns from an object the table name
575 private String GetTableName(Object object) {
576 return object.getClass().getSimpleName();
577 }
578
579 //returns a map with an object properties as <fieldType-fieldName>
580 private ArrayList<Pair<String, String>> GetFields(Field[] fields) {
581 ArrayList<Pair<String, String>> pairs = new ArrayList<>();
582 for (Field f : fields) {
583 if (!f.getName().equals("shadow$_klass_") && !f.getName().equals("shadow$_monitor_") && !f.getName().equals("$change")) {
584 pairs.add(new Pair<>(f.getName(), f.getType().getSimpleName()));
585 }
586 }
587 return pairs;
588 }
589}