How do I join two SQLite tables in my Android application?
You need rawQuery method.
Example:
private final String MY_QUERY = "SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.other_id WHERE b.property_id=?";
db.rawQuery(MY_QUERY, new String[]{String.valueOf(propertyId)});
Use ? bindings instead of putting values into raw sql query.
How to Join Tables in SQLite Android Studio
I'd suggest that your schema is only going to cause you issues as :-
The CLASS_TABLE would only allow a single student per class as other than the student id the other columns have the UNIQUE constraint.
The GRADE_TABLE is introducing redundancy, rather than limiting it, by storing the class name.
I'd also suggest that you concept of creating some master all columns combined table will cause you issues (e.g. to obtain the overallgrade results shown below would likely require a whole lot of coding to accomplish using such a combined table as the source)
I'd suggest a subtle change to the schema e.g. :-
CREATE TABLE IF NOT EXISTS STUDENT_TABLE (studentid TEXT, fname TEXT, lname TEXT);
CREATE TABLE IF NOT EXISTS CLASS_TABLE(classid TEXT PRIMARY KEY, classname TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS GRADE_TABLE (studentid TEXT, classid TEXT, pointgrade INTEGER, lettergrade TEXT);
Let's say you then used the following to insert some data :-
INSERT INTO STUDENT_TABLE
VALUES('00001','Fred','Smith'),('00010','Mary','Thomas'),('00910','Angela','Jones')
;
INSERT INTO CLASS_TABLE VALUES('001','English'),('101','Mathematics'),('201','Chemistry');
INSERT INTO GRADE_TABLE VALUES
('00001','001',99,'A'), -- Fred Smith has 99 point grade as an A in English
('00001','101',25,'F'), -- Fred Smith has 25 point grade as an F on Mathematics
('00010','201',76,'B'), -- Angela Jones 76 a B in Chemistry
('00910','101',50,'C'),
('00910','201',63,'C'),
('00910','001',89,'A')
;
The resultant tables would then be :-
STUDENT_TABLE :-
CLASS_TABLE :-
GRADE_TABLE
You could then do the magic with joins, say to produce a list of students overall points grade (all points per student added for all their classes) along with the classes they are in e.g. :-
SELECT fname,
lname,
sum(pointgrade) AS overallgrade,
group_concat(classname,' - ') AS classes
FROM GRADE_TABLE
JOIN STUDENT_TABLE ON GRADE_TABLE.studentid = STUDENT_TABLE.studentid
JOIN CLASS_TABLE ON GRADE_TABLE.classid = CLASS_TABLE.classid
GROUP BY STUDENT_TABLE.studentid
ORDER BY overallgrade DESC
;
Which would result in :-
- Note
sumandgroup_contactare aggregate functions explained at SQL As Understood By SQLite - Aggregate Functions, they will work on the GROUPED results.- If there is no GROUP BY then you will get **1* result row for all rows.
- However as
GROUP BY STUDENT_TABLE.studentidis used a result is returned for each GROUP i.e. student - note
GROUP BY GRADE_TABLE.studentidwould have the same result.
To fully answer your question, but with the suggested schema then the following SQL would join accordingly :-
SELECT
STUDENT_TABLE.studentid,
fname,
lname,
CLASS_TABLE.classid,
classname,
pointgrade,
lettergrade
FROM GRADE_TABLE
JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid
JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid;
This would result in the following :-
Based upon the original code and suggested schema and uitilising a class available here Are there any methods that assist with resolving common SQLite issues?
NOTE the above code will only work once, as subsequent runs will result in UNIQUE constraint conflicts (aka - you can't add duplicate rows to the CLASS_TABLE)
The following code :-
SQLiteDatabase db;
db=openOrCreateDatabase("STUDENTGRADES", Context.MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS STUDENT_TABLE (studentid TEXT, fname TEXT, lname TEXT)");
db.execSQL("CREATE TABLE IF NOT EXISTS CLASS_TABLE(classid TEXT PRIMARY KEY, classname TEXT UNIQUE)");
db.execSQL("CREATE TABLE IF NOT EXISTS GRADE_TABLE (studentid TEXT, classid TEXT, pointgrade INTEGER, lettergrade TEXT)");
db.execSQL("INSERT INTO STUDENT_TABLE \n" +
" VALUES('00001','Fred','Smith'),('00010','Mary','Thomas'),('00910','Angela','Jones')"
);
db.execSQL("INSERT INTO CLASS_TABLE VALUES('001','English'),('101','Mathematics'),('201','Chemistry');");
db.execSQL("INSERT INTO GRADE_TABLE VALUES\n" +
" ('00001','001',99,'A'), -- Fred Smith has 99 point grade as an A in English\n" +
" ('00001','101',25,'F'), -- Fred Smith has 25 point grade as an F on Mathematics\n" +
" ('00010','201',76,'B'), -- Angela Jones 76 a B in Chemistry\n" +
" ('00910','101',50,'C'), \n" +
" ('00910','201',63,'C'),\n" +
" ('00910','001',89,'A')\n" +
";");
Cursor csr = db.query("GRADE_TABLE JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid",null,null,null,null,null,null);
CommonSQLiteUtilities.logDatabaseInfo(db);
CommonSQLiteUtilities.logCursorData(csr);
csr.close();
Results in :-
05-31 04:20:32.605 3582-3582/? D/SQLITE_CSU: DatabaseList Row 1 Name=main File=/data/data/cpa.carpurchases/databases/STUDENTGRADES
Database Version = 0
Table Name = android_metadata Created Using = CREATE TABLE android_metadata (locale TEXT)
Table = android_metadata ColumnName = locale ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table Name = STUDENT_TABLE Created Using = CREATE TABLE STUDENT_TABLE (studentid TEXT, fname TEXT, lname TEXT)
Table = STUDENT_TABLE ColumnName = studentid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table = STUDENT_TABLE ColumnName = fname ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table = STUDENT_TABLE ColumnName = lname ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table Name = CLASS_TABLE Created Using = CREATE TABLE CLASS_TABLE(classid TEXT PRIMARY KEY, classname TEXT UNIQUE)
Table = CLASS_TABLE ColumnName = classid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 1
Table = CLASS_TABLE ColumnName = classname ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table Name = GRADE_TABLE Created Using = CREATE TABLE GRADE_TABLE (studentid TEXT, classid TEXT, pointgrade INTEGER, lettergrade TEXT)
Table = GRADE_TABLE ColumnName = studentid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table = GRADE_TABLE ColumnName = classid ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
Table = GRADE_TABLE ColumnName = pointgrade ColumnType = INTEGER Default Value = null PRIMARY KEY SEQUENCE = 0
Table = GRADE_TABLE ColumnName = lettergrade ColumnType = TEXT Default Value = null PRIMARY KEY SEQUENCE = 0
05-31 04:20:32.609 3582-3582/? D/SQLITE_CSU: logCursorData Cursor has 12 rows with 9 columns.
Information for row 1 offset=0
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column pointgrade Type is INTEGER value as String is 99 value as long is 99 value as double is 99.0
For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0
Information for row 2 offset=1
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column pointgrade Type is INTEGER value as String is 99 value as long is 99 value as double is 99.0
For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0
Information for row 3 offset=2
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column pointgrade Type is INTEGER value as String is 25 value as long is 25 value as double is 25.0
For Column lettergrade Type is STRING value as String is F value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
05-31 04:20:32.621 3582-3584/? D/dalvikvm: GC_CONCURRENT freed 338K, 11% free 6233K/6983K, paused 10ms+0ms, total 12ms
05-31 04:20:32.621 3582-3582/? D/SQLITE_CSU: Information for row 4 offset=3
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column pointgrade Type is INTEGER value as String is 25 value as long is 25 value as double is 25.0
For Column lettergrade Type is STRING value as String is F value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00001 value as long is 1 value as double is 1.0
For Column fname Type is STRING value as String is Fred value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Smith value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
Information for row 5 offset=4
For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column pointgrade Type is INTEGER value as String is 76 value as long is 76 value as double is 76.0
For Column lettergrade Type is STRING value as String is B value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
For Column fname Type is STRING value as String is Mary value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Thomas value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
Information for row 6 offset=5
For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column pointgrade Type is INTEGER value as String is 76 value as long is 76 value as double is 76.0
For Column lettergrade Type is STRING value as String is B value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00010 value as long is 8 value as double is 10.0
For Column fname Type is STRING value as String is Mary value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Thomas value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
Information for row 7 offset=6
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column pointgrade Type is INTEGER value as String is 50 value as long is 50 value as double is 50.0
For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
05-31 04:20:32.637 3582-3584/? D/dalvikvm: GC_CONCURRENT freed 409K, 12% free 6226K/7047K, paused 11ms+0ms, total 13ms
05-31 04:20:32.637 3582-3582/? D/SQLITE_CSU: Information for row 8 offset=7
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column pointgrade Type is INTEGER value as String is 50 value as long is 50 value as double is 50.0
For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 101 value as long is 101 value as double is 101.0
For Column classname Type is STRING value as String is Mathematics value as long is 0 value as double is 0.0
Information for row 9 offset=8
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column pointgrade Type is INTEGER value as String is 63 value as long is 63 value as double is 63.0
For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
Information for row 10 offset=9
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column pointgrade Type is INTEGER value as String is 63 value as long is 63 value as double is 63.0
For Column lettergrade Type is STRING value as String is C value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 201 value as long is 201 value as double is 201.0
For Column classname Type is STRING value as String is Chemistry value as long is 0 value as double is 0.0
Information for row 11 offset=10
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column pointgrade Type is INTEGER value as String is 89 value as long is 89 value as double is 89.0
For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0
Information for row 12 offset=11
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column pointgrade Type is INTEGER value as String is 89 value as long is 89 value as double is 89.0
For Column lettergrade Type is STRING value as String is A value as long is 0 value as double is 0.0
For Column studentid Type is STRING value as String is 00910 value as long is 0 value as double is 910.0
For Column fname Type is STRING value as String is Angela value as long is 0 value as double is 0.0
For Column lname Type is STRING value as String is Jones value as long is 0 value as double is 0.0
For Column classid Type is STRING value as String is 001 value as long is 1 value as double is 1.0
For Column classname Type is STRING value as String is English value as long is 0 value as double is 0.0
Additional re duplicate class
The following code extends the above by adding a new student to be enrolled in the 001 - English class (which already exists) using :-
ContentValues cv = new ContentValues();
String studentid = "00002";
String fname = "Bert";
String lname = "Jones";
String classid = "001";
String classname = "English";
int pointgrade = 56;
String lettergrade = "C";
cv.put("studentid",studentid);
cv.put("fname",fname);
cv.put("lname",lname);
if (db.insert("STUDENT_TABLE",null,cv) > 0) {
cv.clear();
cv.put("classid",classid);
cv.put("classname",classname);
if (db.insert("CLASS_TABLE",null,cv) > 0) {
Log.d(
"CLASSADD",
"Class " +
cv.getAsString("classid") +
" - " +
cv.getAsString("classname") +
" ADDED."
);
} else {
Log.d(
"CLASSADD",
"Class " +
cv.getAsString(
"classid") +
" - " +
cv.getAsString("classname") +
" NOT ADDED (Exists)"
);
}
cv.clear();
cv.put("studentid",studentid);
cv.put("classid",classid);
cv.put("pointgrade",pointgrade);
cv.put("lettergrade",lettergrade);
if (db.insert("GRADE_TABLE",null,cv) > 0) {
Log.d("ENROLRESULT",
"Student " +
cv.getAsString("studentid") +
" - " +
cv.getAsString("fname") +
" " +
cv.getAsString("lname")
+ " pointgrade " +
String.valueOf(cv.getAsInteger("pointgrade")) +
" lettergrade " +
cv.getAsString("lettergrade") +
" ENROLLED"
);
}
}
This :-
- Attempts to add (insert) the new student.
- If the student wasn't added then nothing else is done.
- Otherwise :-
- Attempts to add (insert) the class (001 - English) which already exists using the normal
insertmethod (SQL would be INSERT OR IGNORE INTO CLASS_TABLE .....). - Logs message indicating added or not <<<< The important bit
- Adds the appropriate GRADE_TABLE row
- Attempts to add (insert) the class (001 - English) which already exists using the normal
Running the above issues the following messages to the log :-
05-31 08:13:43.557 4476-4476/? D/CLASSADD: Class 001 - English NOT ADDED (Exists)
05-31 08:13:43.565 4476-4476/? D/ENROLRESULT: Student 00002 - null null pointgrade 56 lettergrade C ENROLLED
The App doesn't crash.
However the log also includes :-
05-31 08:13:43.557 4476-4476/? E/SQLiteDatabase: Error inserting classname=English classid=001
android.database.sqlite.SQLiteConstraintException: column classname is not unique (code 19)
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:775)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1469)
at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1339)
at cpa.carpurchases.MainActivity.onCreate(MainActivity.java:99)
at android.app.Activity.performCreate(Activity.java:5008)
at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1079)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2023)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2084)
at android.app.ActivityThread.access$600(ActivityThread.java:130)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1195)
at android.os.Handler.dispatchMessage(Handler.java:99)
at android.os.Looper.loop(Looper.java:137)
at android.app.ActivityThread.main(ActivityThread.java:4745)
at java.lang.reflect.Method.invokeNative(Native Method)
at java.lang.reflect.Method.invoke(Method.java:511)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:786)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:553)
at dalvik.system.NativeStart.main(Native Method)
- Note the absence of a FATAL RUNTIME EXCEPTION
- Note the timestamps
- fails to insert @
- 05-31 08:13:43.557
- but reports enrollment @
- 05-31 08:13:43.565
Basically SQLite's insert has trapped the error allowing processing to continue but has printed the stack trace (arguably it shouldn't, arguably it should)
Join tables in android
you can just execute a rawQuery.
For example something like this:
db.rawQuery("SELECT a.*
FROM table_1 a
INNER JOIN table_2 b ON a.id=b.anyId
INNER JOIN table_3 c ON b.id= c.anyId
WHERE c.key = ?", new String[]{"test"});
The first parameter is the query you want to execute. For all your keys you want to add to your query just add an ? in the query.
The second parameter is a String Array. In this array you put your keys, like the example given above the value test.
EDIT:
it's also possible to use rawQuery for update, insert or delete.
For example one simple update query:
db.rawQuery("UPDATE table_1
SET fieldA = ?,
fieldB = ?
WHERE id = ?", new String[]{"test", "test2", "1"});
How to use two sqlite database within single application and how to use join operation with them?
Im not sure if i understand your question,
but you can't use SQL JOIN on two (or more) databases. You can only JOIN Tables of the same database. If you really want to JOIN databases, then you have to do that by hand
i.e. query a list of records from database1.tableA , query list of records from database2.tableB, then save them in a hashmap with the join condition as key and join them and save the join result
Update:
Ok, so if you want to add records from "newDatabase" (provided to you) to the "usersDatatbase" (the current database of the user of your app) with an app update you simply have to versioning the database. Every database in android has a version and you can react on version changes in SQLiteOpenHelper onUpgrade(). Usually you would use this method to alter the table schema, but I think it's completely ok for what you are going to do. So let's assume you have released you app to the playstore with final int DATABASE_VERSION = 1. After some weeks you decide to update the database. So what you have to do is set final int DATABASE_VERSION = 2 (sets database version to 2) and release the new update to the playstore. If the user of your app installs your update from playstore (with database version == 2) onUpgrade() will be called, where you check the old version of the database and the new version and do the data migration (i.e. rename the names with the new provided ones). After a month you want to update the database to final int DATABASE_VERSION = 3 and so on. I hope you get the point. The only thing you have to keep in mind is, that a user can skip an playstore update of your app. For instance a user with app version 1 have not updated to version 2 but directly upgrades from version 1 to 3. In that case you may (depends on your database changes you want to apply) have to migrate from the database from 1 to 2 and then from 2 to 3.
Example:
public class MySQLiteHelper extends SQLiteOpenHelper {
public static final String TABLE_COMMENTS = "comments"; public static final String COLUMN_ID = "_id"; public static final String COLUMN_COMMENT = "comment";
private static final String DATABASE_NAME = "commments.db"; private static final int DATABASE_VERSION = 3;
// Database creation sql statement private static final String DATABASE_CREATE = "create table "
+ TABLE_COMMENTS + "(" + COLUMN_ID
+ " integer primary key autoincrement, " + COLUMN_COMMENT
+ " text not null);";
public MySQLiteHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION); }
@Override
public void onCreate(SQLiteDatabase database) {
database.execSQL(DATABASE_CREATE);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
if (newVersion == 2){
migrateFrom1To2(db);
}
if (newVersion == 3){
if (oldVersion == 1){
migrateFrom1To2(db);
migrateFrom2To3(db);
} else {
// oldversion == 2
migrateFrom2To3(db);
}
}
}
private void migrateFrom1to2(SQLiteDatabase db){
// TODO insert the new data into the users local database
}
private void migrateFrom2To3(SQLiteDatabase db){
// TODO insert the new data into the users local database
}
}
Related Topics
Intercept Back Button from Soft Keyboard
Googleservice Failed to Initialize
Failed to Resolve: Com.Google.Firebase:Firebase-Core:9.0.0
Date and Time Change Listener in Android
Couldn't Load Memtrack Module Logcat Error
Tabwidget Current Tab Bottom Line Color
Google Map Signed API Key Errors in Android
An Established Connection Was Aborted by the Software in Your Host MAChine
Bad Class File Magic or Version
Importing Google-Play-Service Library Showing a Red X Next to This Reference Android
Could Not Find Com.Android.Tools.Build:Gradle:3.0.0-Alpha1 in Circle Ci
Pass Data from Activity to Service Using an Intent
Turn Off Autosuggest for Edittext
Android: Accessing Assets Folder SQLite Database File with .Sqlite Extension
How to Detect User Pressing Home Key in My Activity
How to Get Return Value from JavaScript in Webview of Android