Posts sqlitehelper way
Post
Cancel

sqlitehelper way

Posted Nov 17, 2014 2014-11-17T00:00:00+08:00 by PipisCrew

reference : http://stackoverflow.com/a/8889012 http://commonsware.com/cwac http://www.androiddesignpatterns.com/2012/05/correctly-managing-your-sqlite-database.html http://www.androiddesignpatterns.com/2012/07/understanding-loadermanager.html

AndroidManifest.xml

1
2
3
4
5

    //at MANIFEST
    <uses-permission android:name="android.permission.WRITE_INTERNAL_STORAGE"></uses-permission>
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"></uses-permission>

   <application android:name=".Dynomite" android:allowbackup="true" android:icon="@drawable/ic_launcher" android:label="@string/app_name" android:theme="@style/AppTheme">

Dynomite.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

public class Dynomite extends Application {

	private static SQLiteDatabase database = null;
	private static SQLiteHelper dbHelper = null;

	@Override
	public void onCreate() {
		super.onCreate();

	}

	// method to get the sqlite db instance
	public SQLiteDatabase getSQLiteInstance() {
		if (database == null) {
			dbHelper = new SQLiteHelper(this.getApplicationContext());
			database = dbHelper.getWritableDatabase();

			// enable Foreign Keys [http://www.sqlite.org/foreignkeys.html]
			database.execSQL("PRAGMA foreign_keys = ON");
		}

		return database;
	}

	// method to close
	public void closeDB() {
		dbHelper.close();
	}

}

SQLiteHelper.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

//automatic generated by https://www.pipiscrew.com/works/sqlite-manager-v1-0/
import java.io.File;
import java.io.InputStream;

import com.x.gr.R;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;

public class SQLiteHelper extends SQLiteOpenHelper {

	private Context c=null;

	////////////////// AUTOMOTODETAILS //////////////////
	public static final String TABLE_AUTOMOTODETAILS = "AUTOMOTODETAILS";
	public static final String AUTOMOTODETAIL_ID = "AUTOMOTODETAIL_ID";
	public static final String DATEREC = "DATEREC";
	public static final String KM_COUNTER = "KM_COUNTER";
	public static final String FIXES = "FIXES";
//	public static final String COMMENT = "COMMENT";

	////////////////// AUTOMOTOS //////////////////
	public static final String TABLE_AUTOMOTOS = "AUTOMOTOS";
	public static final String AUTOMOTO_ID = "AUTOMOTO_ID";
	public static final String DESCRIPTION = "DESCRIPTION";
	public static final String PLATE = "PLATE";
//	public static final String COUNTRY_ID = "COUNTRY_ID";
	public static final String COMMENT = "COMMENT";
	public static final String AUTOMOTO_DATEREC = "AUTOMOTO_DATEREC";

	////////////////// COUNTRIES //////////////////
	public static final String TABLE_COUNTRIES = "COUNTRIES";
	public static final String COUNTRY_ID = "COUNTRY_ID";
	public static final String COUNTRY_NAME = "COUNTRY_NAME";
	public static final String COUNTRY_sort_ORDER = "COUNTRY_sort_ORDER";

	////////////////// SETTINGS //////////////////
	public static final String TABLE_SETTINGS = "SETTINGS";
	public static final String SETTING_ID = "SETTING_ID";
	public static final String USERNAME = "USERNAME";
	public static final String PASSWORD = "PASSWORD";

	//public static final String  DATABASE_FILE_PATH = Environment.getExternalStorageDirectory().toString();
	private static final String DATABASE_NAME = "x.db";
	private static final int DATABASE_VERSION = 1;

	// Database creation sql statement
	private static final String DATABASE_CREATE = "CREATE TABLE [AUTOMOTODETAILS] (" +
				"automotodetail_id INTEGER PRIMARY KEY, " + 
		"daterec TEXT, " + 
		"km_counter TEXT, " + 
		"fixes TEXT, " + 
		"comment TEXT);CREATE TABLE [AUTOMOTOS] (" +
				"automoto_id INTEGER PRIMARY KEY, " + 
		"description TEXT, " + 
		"plate TEXT, " + 
		"country_id INTEGER, " + 
		"comment TEXT, " + 
		"automoto_daterec TEXT, " + 
		"FOREIGN KEY(country_id) REFERENCES countries(country_id));CREATE TABLE [COUNTRIES] (" +
				"country_id INTEGER PRIMARY KEY, " + 
		"country_name TEXT, " + 
		"country_sort_order INTEGER);CREATE TABLE [SETTINGS] (" +
				"setting_id INTEGER PRIMARY KEY, " + 
		"username TEXT, " + 
		"password TEXT);";

	public SQLiteHelper(Context context) {
//		super(context, DATABASE_FILE_PATH
//	            + File.separator + DATABASE_NAME, null, DATABASE_VERSION);

		//internal storage (can be viewed only when rooted)
		//location : data/user/0/x.gr
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

		c= context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.beginTransaction();

		try {
			String[] queries = DATABASE_CREATE.split(";");
			for (String query : queries)
				db.execSQL(query);

			//Add predefined records to tables (TSQL already stored as resource file)
			InputStream Stream = c.getResources().openRawResource(R.raw.countries);
			String countries_predefined_sql = General.convertStreamToString(Stream);
			queries = countries_predefined_sql.split(";");
			for (String query : queries)
				db.execSQL(query);

			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

//		db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);
//		onCreate(db);
	}

}

R.raw.countries

1
2
3
4
5
6

PRAGMA foreign_keys = OFF;
INSERT INTO "main"."countries" VALUES (1, 'Afghanistan', null);
.
.
.
INSERT INTO "main"."countries" VALUES (81, 'Greece', null);

CountriesDatasource.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90

//automatic generated by https://www.pipiscrew.com/works/sqlite-manager-v1-0/
import java.util.ArrayList;
import java.util.List;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class CountriesDatasource {

	private SQLiteDatabase database;
	private static final String[] allColumns = { SQLiteHelper.COUNTRY_ID, SQLiteHelper.COUNTRY_NAME, SQLiteHelper.COUNTRY_sort_ORDER };

	 public CountriesDatasource(Context c) {
		 database = ((Dynomite) c.getApplicationContext()).getSQLiteInstance();
	 }

	public Countries createCountries(Countries countries) {
		ContentValues values = new ContentValues();
		values.put(SQLiteHelper.COUNTRY_NAME, countries.getcountry_name());
		values.put(SQLiteHelper.COUNTRY_sort_ORDER, countries.getcountry_sort_order());

		long insertId = database.insert(SQLiteHelper.TABLE_COUNTRIES, null, values);

		Cursor cursor = database.query(SQLiteHelper.TABLE_COUNTRIES, allColumns, SQLiteHelper.COUNTRY_ID + " = " + insertId, null, null, null, null);

		cursor.moveToFirst();
		Countries countriesRET = cursorToCountries(cursor);
		cursor.close();
		return countriesRET;
	}

	public int updateCountries(Countries countries) {
		ContentValues values = new ContentValues();
		values.put(SQLiteHelper.COUNTRY_NAME, countries.getcountry_name());
		values.put(SQLiteHelper.COUNTRY_sort_ORDER, countries.getcountry_sort_order());

		return database.update(SQLiteHelper.TABLE_COUNTRIES, values, SQLiteHelper.COUNTRY_ID + " = ?", new String[] { String.valueOf(countries.getcountry_id()) });

	}

	public void deleteCountries(long ID) {
		database.delete(SQLiteHelper.TABLE_COUNTRIES, SQLiteHelper.COUNTRY_ID + " = " + ID, null);
	}

	public List<countries> getAllCountriess() {
		List<countries> countriess = new ArrayList<countries>();
		Countries countries = null;

		Cursor cursor = database.query(SQLiteHelper.TABLE_COUNTRIES, allColumns, null, null, null, null, null);

		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			countries = cursorToCountries(cursor);
			countriess.add(countries);
			cursor.moveToNext();
		}

		// Make sure to close the cursor
		cursor.close();
		return countriess;
	}

	private Countries cursorToCountries(Cursor cursor) {
		Countries countries = new Countries();
		countries.setcountry_id(cursor.getLong(0));
		countries.setcountry_name(cursor.getString(1));
		countries.setcountry_sort_order(cursor.getLong(2));

		return countries;
	}

	public Countries getID(long ID) {
		Cursor c = database.rawQuery("select country_id as [country_id],country_name as [country_name],country_sort_order as [country_sort_order] from COUNTRIES where COUNTRY_ID=" + ID, null);

		c.moveToFirst();

		return cursorToCountries(c);
	}

	public Cursor countriessCursor() {
		Cursor c = database.rawQuery("select COUNTRY_ID as _id, country_name as [country_name],country_sort_order as [country_sort_order]  from COUNTRIES", null);
		// database.query("COUNTRIES",allColumns, null, null, null,null,null);

		c.moveToFirst();

		return c;
	}

}

MainActivity.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14

public class MainActivity extends Activity {
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_main);

		CountriesDatasource x =  new CountriesDatasource(MainActivity.this);
		List<countries> t =  x.getAllCountriess();

		for (Countries countries : t) {
			Log.w(String.valueOf( countries.getcountry_id()), countries.getcountry_name());
		}
	}
}

Countries.java

```js //automatic generated by https://www.pipiscrew.com/works/sqlite-manager-v1-0/ public class Countries {

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

private long country_id;
private String country_name;
private long country_sort_order;

public Countries() {

}

public Countries(String country_name, long country_sort_order) {
	this.country_name = country_name;
	this.country_sort_order = country_sort_order;
}

public long getcountry_id() {
	return country_id;
}

public void setcountry_id(long country_id) {
	this.country_id = country_id;
}

public String getcountry_name() {
	return country_name;
}

public void setcountry_name(String country_name) {
	this.country_name = country_name;
}

public long getcountry_sort_order() {
	return country_sort_order;
}

public void setcountry_sort_order(long country_sort_order) {
	this.country_sort_order = country_sort_order;
}

} ```</countries></countries></countries></countries></application>

origin - http://www.pipiscrew.com/?p=1892 android-sqlitehelper-way

This post is licensed under CC BY 4.0 by the author.
Contents

NET Core is Open Source

XMAS Elf - Elfyourself

Trending Tags