锐客网

  1. 首页 > 睿知 > it技术 > >

SQLite with Foreign Key + Models Android Studio CRUD

androidIT知识

仓廪实则知礼节，衣食足则知荣辱。这篇文章主要讲述SQLite with Foreign Key + Models Android Studio CRUD相关的知识，希望能为你提供帮助。
抱歉我的英文
我将构建一个简单的例子：
表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)

表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）在这里我需要使用相同的表名？
public static final String TABLE = "T_User";

2）在这里我需要把我桌子的所有列？比如“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

user repo.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）我不需要这条线吗？ （因为自动增量）
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）这就是我从人那里得到的身份？
values.put(User.KEY_ID_PERSON , user.person.getId()); db.insert(User.TABLE, null, values); DatabaseManager.getInstance().closeDatabase(); }

5）如何进行更新和选择
答案这是一个基于您的设计的工作示例，包含更新和选择方法。
它由2个java类和调用活动MainActivity.java以及SQLiteOpenHelper类的子类组成，通常称为DBHelper，因此它是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); } }

  • 方法insertPerson用于向T_Person表添加行。
  • 方法insertUser用于向T_User表添加行，注意personid（第3个参数）必须引用Person。
  • 方法getAllPersons获取T_Person表中的所有行。
  • 方法getAllUsers从T_User表中获取所有行。
  • 方法getAllPersonsAndTheReferencedUser根据外键/引用从Person和User表中获取所有行。
  • 方法changePersonsName允许根据人员的ID更新Person的名称。
  • 方法changePersonscel允许根据人的id更新Person的cel。
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("Fred@email.moc","Fred1234567890",1); mDBHlpr.insertUser("Bert@email.moc","Bert1234567890",2); mDBHlpr.insertUser("Mary@email.moc","Mary1234567890",3); //ooops add some unanticipated rows mDBHlpr.insertUser("whoever@email.moc","xxxx",3); mDBHlpr.insertUser("whoever@email.moc","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(); } }

【SQLite with Foreign Key + Models Android Studio CRUD】这将调用DBHelper中的方法。
  • 首先，实例化DBHelper的一个实例（mDBHlpr）。
  • 接下来从两个表中删除所有行（如果有的话）（这样做是为了使App的结果一致）。
  • 接下来添加一些人员和用户（包括一些意外/意外用户（结果是3个用户引用了人员3））。
  • 然后做出一些改变（玛丽改为玛丽安，玛丽（玛丽安）玛丽从2222-222-222改为6666-666-66）。
  • 运行所有三个选择方法（getAll ....）并创建3个Cursors。
  • 游标被转储（写入日志）。
  • 游标已关闭。
3项产出是： - 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_user=Fred@email.moc pass_user=Fred1234567890 id_person=1 } 1 { _id=2 email_user=Bert@email.moc pass_user=Bert1234567890 id_person=2 } 2 { _id=3 email_user=Mary@email.moc pass_user=Mary1234567890 09-01 21:48:52.537 1919-1919/so52115977.so52115977 I/System.out:id_person=3 } 3 { _id=4 email_user=whoever@email.moc pass_user=xxxx id_person=3 } 4 { _id=5 email_user=whoever@email.moc pass_user=xxxx id_person=3 } < < < < <

The linked/referenced/joined (Foreign Keyed) Person/User tables (5 rows)

    推荐阅读


    上一篇：BlackBerry - html链接到App World中的应用程序

    下一篇：如何在Android中再次设置布尔值true（）