Leonardo
Reputation: 1
SQLite with Foreign Key + Models Android Studio CRUD
Sorry with my English
I'll build a simple example:
Table T_User
id_user integer primary key autoincrement,
email_user text not null,
pass_user text not null,
id_person integer foreign key(id_person) references T_Person(id_person)
Table T_Person
id_person integer primary key autoincrement,
name_person text not null,
cel_person text not null
Android Studio:
User.java
public static final String TAG = User.class.getSimpleName();
1) Here i need to put the same name of the table ?
public static final String TABLE = "T_User";
2) Here i need to put all of columns of my table? like "id_user"
public static final int KEY_ID_USER = "id_user";
public static final String KEY_EMAIL_USER = "email_user";
public static final String KEY_PASS_USER= "pass_user";
public static final int KEY_ID_PERSON = "id_person";
private int id;
private String email;
private String pass;
private Person person;
//constructor
//getters & setters
UserRepo.java
public static String createTable(){
return "CREATE TABLE " + User.TABLE + "("
+ User.KEY_ID_USER + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ User.KEY_EMAIL_USER + " TEXT,"
+ User.KEY_PASS_USER + " TEXT,"
+ User.KEY_ID_PERSON + " INTEGER)";
}
public void insert(User user) {
SQLiteDatabase db = DatabaseManager.getInstance().openDatabase();
ContentValues values = new ContentValues();
3) I dont need this line right? (because of autoincrement)
values.put(User.KEY_ID_USER, user.getId());
values.put(User.KEY_EMAIL_USER, user.getEmail());
values.put(User.KEY_PASS_USER, user.getPass());
4) Here is like that to i get the id from person?
values.put(User.KEY_ID_PERSON , user.person.getId());
db.insert(User.TABLE, null, values);
DatabaseManager.getInstance().closeDatabase();
}
5) How can i do Update and Select
Upvotes: 0
Views: 877
Answers (1)
MikeT
Reputation: 57043
Here's a working example based upon your design with update and select methods.
This consists of 2 java classes, and invoking activity MainActivity.java and a subclass of the SQLiteOpenHelper class, often called the DBHelper, hence it being DBHelper.java
DBHelper.java
public class DBHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
public static final int DBVERSION = 1;
public static final String TB_USER = "T_User";
public static final String TB_PERSON = "T_Person";
public static final String COl_USER_ID = BaseColumns._ID;
public static final String COl_USER_EMAIL = "email_user";
public static final String COL_USER_PASS = "pass_user";
public static final String COl_USER_PERSON = "id_person";
public static final String COL_PERSON_ID = BaseColumns._ID;
public static final String COl_PERSON_NAME = "name_person";
public static final String COL_PERSON_CEL = "cel_person";
SQLiteDatabase mDB;
public DBHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
mDB = this.getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
String crtpersonsql = "CREATE TABLE IF NOT EXISTS " + TB_PERSON + "(" +
COL_PERSON_ID + " INTEGER PRIMARY KEY, " +
COl_PERSON_NAME + " TEXT NOT NULL," +
COL_PERSON_CEL + " TEXT NOT NULL " +
")";
String crtusersql = "CREATE TABLE If NOT EXISTS " + TB_USER + "(" +
COl_USER_ID + " INTEGER PRIMARY KEY, " +
COl_USER_EMAIL + " TEXT NOT NULL, " + //perhaps make unique
COL_USER_PASS + " TEXT NOT NULL, " +
COl_USER_PERSON + " INTEGER REFERENCES " + TB_PERSON + "(" +
COL_PERSON_ID +
")" +
")";
db.execSQL(crtpersonsql);
db.execSQL(crtusersql);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
public long insertPerson(String name, String cel) {
ContentValues cv = new ContentValues();
cv.put(COl_PERSON_NAME,name);
cv.put(COL_PERSON_CEL,cel);
return mDB.insert(TB_PERSON,null,cv);
}
public long insertUser(String email, String pass, long personid) {
ContentValues cv = new ContentValues();
cv.put(COl_USER_EMAIL,email);
cv.put(COL_USER_PASS,pass);
cv.put(COl_USER_PERSON,personid);
return mDB.insert(TB_USER,null,cv);
}
public Cursor getAllPersons() {
return mDB.query(TB_PERSON,null,null,null,null,null,null);
}
public Cursor getAllUsers() {
return mDB.query(TB_USER,null,null,null,null,null,null);
}
public Cursor getAllPersonsAndTheReferencedUser() {
String table = TB_PERSON +
" JOIN " + TB_USER +
" ON " + COl_USER_PERSON + "=" +
TB_PERSON + "." + COL_PERSON_ID;
String[] columns = new String[]{
TB_PERSON + "." + COL_PERSON_ID + " AS " + TB_PERSON + "_" + COL_PERSON_ID,
COl_PERSON_NAME,
COL_PERSON_CEL,
TB_USER + "." + COl_USER_ID + " AS " + TB_USER + "_" + COl_USER_ID,
COl_USER_EMAIL,
COL_USER_PASS
};
Log.d("GETALLPandU","Table (FROM) clause = " + table);
return mDB.query(table,columns,null,null,null,null,null);
}
public int changePersonsName(long personid, String newname) {
ContentValues cv = new ContentValues();
cv.put(COl_PERSON_NAME,newname);
String whereclause = COL_PERSON_ID + "=?";
String[] whereargs = new String[]{String.valueOf(personid)};
return mDB.update(TB_PERSON,cv,whereclause,whereargs);
}
public int changePersonsCel(long personid, String newcel) {
ContentValues cv = new ContentValues();
cv.put(COL_PERSON_CEL,newcel);
String whereclause = COL_PERSON_ID + "=?";
String[] whereargs = new String[]{String.valueOf(personid)};
return mDB.update(TB_PERSON,cv,whereclause,whereargs);
}
}
- Method insertPerson is used to add a row to the T_Person table.
- Method insertUser is used to add a row to the T_User table, noting that the personid (the 3rd parameter) has to reference a Person.
- Method getAllPersons gets all the rows from the T_Person table.
- Method getAllUsers gets all the rows from the T_User table.
- Method getAllPersonsAndTheReferencedUser gets all the rows, according to the Foreign Keys/references, from the Person and User tables.
- Method changePersonsName allows the name of a Person to be updated according to the id of the person.
- Method changePersonscel allows the cel of a Person to be updated according to the id of the person.
MainActivity.java
public class MainActivity extends AppCompatActivity {
DBHelper mDBHlpr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr = new DBHelper(this);
// Delete all rows from both tables (if any)
mDBHlpr.getWritableDatabase().delete(DBHelper.TB_USER,null,null);
mDBHlpr.getWritableDatabase().delete(DBHelper.TB_PERSON,null,null);
// Add some person rows
mDBHlpr.insertPerson("Fred","0000-000-000"); // id will virtually certainly be 1
mDBHlpr.insertPerson("Bert", "1111-111-111"); // id likely 2
mDBHlpr.insertPerson("Mary","2222-222-222"); // id likely 3
// Add some user rows
mDBHlpr.insertUser("[email protected]","Fred1234567890",1);
mDBHlpr.insertUser("[email protected]","Bert1234567890",2);
mDBHlpr.insertUser("[email protected]","Mary1234567890",3);
//ooops add some unanticipated rows
mDBHlpr.insertUser("[email protected]","xxxx",3);
mDBHlpr.insertUser("[email protected]","xxxx",3);
// Make some changes (updates)
mDBHlpr.changePersonsCel(3,"6666-666-66");
mDBHlpr.changePersonsName(3,"Marian");
// get some data
Cursor crs1 = mDBHlpr.getAllPersons();
Cursor csr2 = mDBHlpr.getAllUsers();
Cursor csr3 = mDBHlpr.getAllPersonsAndTheReferencedUser();
// Output the retrieved data
DatabaseUtils.dumpCursor(crs1);
DatabaseUtils.dumpCursor(csr2);
DatabaseUtils.dumpCursor(csr3);
// Close the Cursors (should always be done when finished with the Cursor)
crs1.close();
crs1.close();
csr3.close();
}
}
This invokes the methods in the DBHelper.
- First an instance of the DBHelper is instantiated (mDBHlpr).
- Next all rows (if any) are deleted from both tables (this is done to make the App's results consistent).
- Next some Persons and then users are added (including some unanticipated/unexpected users (the result being that Person 3 is referenced by 3 users)).
- Some changes are then made (Mary is changed to Marian, Mary's(nor Marian's) cel is changed from 2222-222-222 to 6666-666-66).
- All three of the selection methods (getAll....) are run and 3 Cursors are created.
- The Cursors are dumped (written to the log).
- The Cursors are closed.
The 3 outputs are :-
The Person table (3 rows) :-
>>>>> Dumping cursor android.database.sqlite.SQLiteCursor@a98de41
09-01 21:48:52.535 1919-1919/so52115977.so52115977 I/System.out: 0 {
_id=1
09-01 21:48:52.536 1919-1919/so52115977.so52115977 I/System.out: name_person=Fred
cel_person=0000-000-000
}
1 {
_id=2
name_person=Bert
cel_person=1111-111-111
}
2 {
_id=3
name_person=Marian
cel_person=6666-666-66
}
<<<<<
The User table (5 rows)
>>>>> Dumping cursor android.database.sqlite.SQLiteCursor@e149fe6
0 {
_id=1
[email protected]
pass_user=Fred1234567890
id_person=1
}
1 {
_id=2
[email protected]
pass_user=Bert1234567890
id_person=2
}
2 {
_id=3
[email protected]
pass_user=Mary1234567890
09-01 21:48:52.537 1919-1919/so52115977.so52115977 I/System.out: id_person=3
}
3 {
_id=4
[email protected]
pass_user=xxxx
id_person=3
}
4 {
_id=5
[email protected]
pass_user=xxxx
id_person=3
}
<<<<<
The linked/referenced/joined (Foreign Keyed) Person/User tables (5 rows)
>>>>> Dumping cursor android.database.sqlite.SQLiteCursor@9645927
0 {
T_Person__id=1
name_person=Fred
cel_person=0000-000-000
T_User__id=1
[email protected]
pass_user=Fred1234567890
}
1 {
T_Person__id=2
name_person=Bert
cel_person=1111-111-111
T_User__id=2
[email protected]
pass_user=Bert1234567890
}
2 {
T_Person__id=3
name_person=Marian
cel_person=6666-666-66
T_User__id=3
[email protected]
pass_user=Mary1234567890
}
3 {
T_Person__id=3
name_person=Marian
cel_person=6666-666-66
T_User__id=4
[email protected]
pass_user=xxxx
}
4 {
T_Person__id=3
09-01 21:48:52.538 1919-1919/so52115977.so52115977 I/System.out: name_person=Marian
cel_person=6666-666-66
T_User__id=5
[email protected]
pass_user=xxxx
}
<<<<<
-NOTE AUTOINCREMENT has not been used.
- It's not actually AUTOINCREMENT in SQLite that results in the generation of a unique normally incrementing number.
- Specifying
INTEGER PRIMARY KEYis generally sufficient and does not incur the overheads of the rarely needed AUTOINCREMENT SQLite Autoincrement - In fact, unless WITHOUT ROWID is specified then an SQLite table has a special, normally hidden column, called rowid, which is a 64bit signed integer, that will initially be 1, then likely 2, etc. Coding
INTEGER PRIMARY KEYmakes the column an alias of the rowid column (AUTOINCREMENT can only be coded if INTEGER PRIMARY KEY is coded).
Upvotes: 1
Related Questions
- Creating SQLite Database table with Foreign Keys?
- Android SQLite foreign key for 2 tables
- SQlite - Android - Foreign key syntax
- How to use a foreign key in sqlite?
- foreign key issue
- Foreign key in SQLite Database
- Android SQLite Querying for Foreign Key
- How to use correctly foreign keys in Android, using SQLite and OrmLite
- SQLite foreign key in android/
- Foreign keys definition