SQLiteOpenHelper: Mit Upgrade, Beispielen und zentraler Instanz

1public class MyApplication extends Application {
2
3 private static SQLiteDatabase sqliteDatabase;
4 private static MySQLiteOpenHelper sqliteOpenHelper;
5
6 public static SQLiteDatabase getSqliteDatabase() {
7 return sqliteDatabase;
8 }
9
10 public static MySQLiteOpenHelper getSqliteOpenHelper() {
11 return sqliteOpenHelper;
12 }
13
14 @Override
15 public void onCreate() {
16 super.onCreate();
17
18 Context context = getApplicationContext();
19
20 sqliteOpenHelper = new MySQLiteOpenHelper(context);
21 if (sqliteOpenHelper != null) {
22 sqliteDatabase = sqliteOpenHelper.getWritableDatabase();
23 if (sqliteDatabase != null) {
24 }
25 }
26 }
27
28 @Override
29 public void onTerminate() {
30 if (sqliteDatabase != null) {
31 sqliteDatabase.close();
32 }
33
34 if (sqliteOpenHelper != null) {
35 sqliteOpenHelper.close();
36 }
37
38 super.onTerminate();
39 }
40}

1<application
2 ...
3 android:name="MyApplication" >

1public class MySQLiteOpenHelper extends SQLiteOpenHelper {
2
3 private static final int DATENBANK_VERSION = 3;
4 private static final String DATENBANK_NAME = "deine.db";
5
6 private Context context;
7 private SQLiteDatabase sqliteDatabase;
8
9 public MySQLiteOpenHelper(Context context) {
10 super(context, DATENBANK_NAME, null, DATENBANK_VERSION);
11
12 this.context = context;
13 }
14
15 @Override
16 public void onCreate(SQLiteDatabase sqliteDatabase) {
17 this.sqliteDatabase = sqliteDatabase;
18
19 sqliteDatabase.execSQL("create table if not exists tablename (...)");
20 }
21
22 @Override
23 public void onUpgrade(SQLiteDatabase sqliteDatabase, int oldVersion, int newVersion) {
24 this.sqliteDatabase = sqliteDatabase;
25
26 switch (newVersion) {
27 case DATENBANK_VERSION:
28 switch (oldVersion) {
29 case 1:
30 upgradeFrom1To2();
31 case 2:
32 upgradeFrom2To3();
33 }
34
35 break;
36 }
37 }
38
39 private void upgradeFrom1To2() {
40 sqliteDatabase.execSQL("alter table tablename add column columnname integer default 0");
41 }
42
43 private void upgradeFrom2To3() {
44 sqliteDatabase.execSQL("alter table tablename add column columnname2 integer default 0");
45 }
46
47 @Override
48 public SQLiteDatabase getWritableDatabase() {
49 try {
50 sqliteDatabase = super.getWritableDatabase();
51 } catch (Exception exception) {
52 }
53
54 return sqliteDatabase;
55 }
56
57 public long deleteTablename(long _id) {
58 return sqliteDatabase.delete("tablename", "_id = ?", new String[] { String.valueOf(_id) } );
59 }
60
61 public Cursor fetchTablename() {
62 return sqliteDatabase.query("tablename",
63 new String[] { "_id",
64 "name" },
65 null,
66 null,
67 null,
68 null,
69 "name");
70 }
71
72 public Tablename getTablename(long _id) {
73 Cursor cursor;
74 Tablename tablename = null;
75
76 if ((cursor = sqliteDatabase.rawQuery("select " +
77 "name" +
78 " from tablename" +
79 " where _id = ?",
80 new String[] { String.valueOf(_id) } )) != null) {
81 if (cursor.moveToFirst()) {
82 tablename = new Tablename();
83
84 tablename.setId(_id);
85 tablename.setName(cursor.getString(0));
86 }
87
88 cursor.close();
89 }
90
91 return tablename;
92 }
93
94 public long insertTablename(Tablename tablename) {
95 ContentValues values = new ContentValues();
96 values.put("name", tablename.getName());
97 return sqliteDatabase.insert("tablename", null, values);
98 }
99
100 public long updateTablename(Tablename tablename) {
101 ContentValues values = new ContentValues();
102 values.put("name", tablename.getName());
103 return sqliteDatabase.update("tablename", values, "_id = ?", new String[] { String.valueOf(tablename.getId()) } );
104 }
105}

1Cursor cursor = MyApplication.getSqliteOpenHelper().fetchTablename();
2oder
3Tablename tablename = MyApplication.getSqliteOpenHelper().getTablename(1);
4oder
5MyApplication.getSqliteOpenHelper().deleteTablename(2);
6oder
7MyApplication.getSqliteOpenHelper().updateTablename(tablename);
8oder
9MyApplication.getSqliteOpenHelper().insertTablename(tablename);