Android项目中如何在一个数据库里建立多张表,很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。

一,创建一个公共的DBAdapter;

为了在整个程序运行期间调用该公共的数据库,我们定义了一个扩展自Application的CommDB类:

1,创建唯一的数据库:


1publicclassCommDB{23publicstaticfinalStringDATABASE_NAME="myDatabase";//数据库名称45publicstaticfinalintDATABASE_VERSION=1;6//创建该数据库下学生表的语句7privatestaticfinalStringCREATE_TABLE_Students=8"CREATETABLEifnotexists"+StudentDB.SQLITE_TABLE+"("+9StudentDB.KEY_ROWID+"integerPRIMARYKEYautoincrement,"+10StudentDB.KEY_AGE+","+11StudentDB.KEY_GENDER+","+12StudentDB.KEY_NAME+","+13"UNIQUE("+StudentDB.KEY_NAME+"));";//暂时规定不能重名14//创建该数据库下教师表的语句15privatestaticfinalStringCREATE_TABLE_Teachers=16"CREATETABLEifnotexists"+TeacherDB.SQLITE_TABLE+"("+17TeacherDB.KEY_ROWID+"integerPRIMARYKEYautoincrement,"+18TeacherDB.KEY_AGE+","+19TeacherDB.KEY_GENDER+","+20TeacherDB.KEY_NAME+","+21"UNIQUE("+TeacherDB.KEY_AGE+"));";22privatefinalContextcontext;23privateDatabaseHelperDBHelper;24privateSQLiteDatabasedb;25/**26*Constructor27*@paramctx28*/29publicCommDB(Contextctx)30{31this.context=ctx;32this.DBHelper=newDatabaseHelper(this.context);33}3435privatestaticclassDatabaseHelperextendsSQLiteOpenHelper36{37DatabaseHelper(Contextcontext)38{39super(context,DATABASE_NAME,null,DATABASE_VERSION);40}4142@Override43publicvoidonCreate(SQLiteDatabasedb)44{45db.execSQL(CREATE_TABLE_Students);//创建学生表46db.execSQL(CREATE_TABLE_Teachers);//创建教师表47}4849@Override50publicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,51intnewVersion)52{53//Addinganytablemodstothisguyhere54}55}5657/**58*openthedb59*@returnthis60*@throwsSQLException61*returntype:DBAdapter62*/63publicCommDBopen()throwsSQLException64{65this.db=this.DBHelper.getWritableDatabase();66returnthis;67}6869/**70*closethedb71*returntype:void72*/73publicvoidclose()74{75this.DBHelper.close();76}77}


2,在app开始运行时,创建上述的数据库,并创建对应的数据表:


1publicclassGApplicationextendsApplication{2privateCommDBcomDBHelper;34@Override5publicvoidonCreate(){6//TODOAuto-generatedmethodstub7super.onCreate();8comDBHelper=newCommDB(this);9comDBHelper.open();10}1112}


二,分别创建对应的数据表;

1,建立学生数据表类:


publicclassStudentDB{publicstaticfinalStringKEY_ROWID="_id";publicstaticfinalStringKEY_AGE="age";publicstaticfinalStringKEY_GENDER="gender";publicstaticfinalStringKEY_NAME="name";privatestaticfinalStringTAG="StudentDbAdapter";privateDatabaseHelpermDbHelper;privateSQLiteDatabasemDb;//privatestaticfinalStringDATABASE_NAME="Fortrun_Ticket11";staticfinalStringSQLITE_TABLE="StudentTable";privatefinalContextmCtx;privatestaticclassDatabaseHelperextendsSQLiteOpenHelper{DatabaseHelper(Contextcontext){super(context,CommDB.DATABASE_NAME,null,CommDB.DATABASE_VERSION);}@OverridepublicvoidonCreate(SQLiteDatabasedb){}@OverridepublicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){Log.w(TAG,"Upgradingdatabasefromversion"+oldVersion+"to"+newVersion+",whichwilldestroyallolddata");db.execSQL("DROPTABLEIFEXISTS"+SQLITE_TABLE);onCreate(db);}}publicStudentDB(Contextctx){this.mCtx=ctx;}publicStudentDBopen()throwsSQLException{mDbHelper=newDatabaseHelper(mCtx);mDb=mDbHelper.getWritableDatabase();returnthis;}publicvoidclose(){if(mDbHelper!=null){mDbHelper.close();}}/***创建学生表的字段*@paramage*@paramgender*@paramname*@return*/publiclongcreateStudent(Stringage,Stringgender,Stringname){longcreateResult=0;ContentValuesinitialValues=newContentValues();initialValues.put(KEY_AGE,age);initialValues.put(KEY_GENDER,gender);initialValues.put(KEY_NAME,name);try{createResult=mDb.insert(SQLITE_TABLE,null,initialValues);}catch(Exceptione){//TODO:handleexception}returncreateResult;}/***删除表的全部字段数据*@return*/publicbooleandeleteAllStudents(){intdoneDelete=0;try{doneDelete=mDb.delete(SQLITE_TABLE,null,null);Log.w(TAG,Integer.toString(doneDelete));Log.e("doneDelete",doneDelete+"");}catch(Exceptione){//TODO:handleexceptione.printStackTrace();}returndoneDelete>0;}/***根据名称删除表中的数据*@paramname*@return*/publicbooleandeleteTicketByName(Stringname){intisDelete;String[]tName;tName=newString[]{name};isDelete=mDb.delete(SQLITE_TABLE,KEY_AGE+"=?",tName);Log.e("deleteTicket","isDelete:"+isDelete+"||"+"ticketID="+name);returnisDelete>0;}publicvoidinsertSomeTickets(){}/***获取表中的所有字段*@return*/publicArrayList<Student>fetchAll(){ArrayList<Student>allTicketsList=newArrayList<Student>();CursormCursor=null;mCursor=mDb.query(SQLITE_TABLE,newString[]{KEY_ROWID,KEY_AGE,KEY_GENDER,KEY_NAME},null,null,null,null,null);if(mCursor.moveToFirst()){do{Studentst=newStudent();st.setAge(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_AGE)));st.setGender(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_GENDER)));st.setName(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_NAME)));allTicketsList.add(st);}while(mCursor.moveToNext());}if(mCursor!=null&&!mCursor.isClosed()){mCursor.close();}returnallTicketsList;}}


2,创建教师数据表类:


publicclassTeacherDB{publicstaticfinalStringKEY_ROWID="_id";publicstaticfinalStringKEY_AGE="age";publicstaticfinalStringKEY_GENDER="gender";//还要保留publicstaticfinalStringKEY_NAME="name";privatestaticfinalStringTAG="TeacherDbAdapter";privateDatabaseHelpermDbHelper;privateSQLiteDatabasemDb;//privatestaticfinalStringDATABASE_NAME="Fortrun_Ticket11";staticfinalStringSQLITE_TABLE="TeacherTable";privatestaticfinalintDATABASE_VERSION=1;privatefinalContextmCtx;privatestaticclassDatabaseHelperextendsSQLiteOpenHelper{DatabaseHelper(Contextcontext){super(context,CommDB.DATABASE_NAME,null,CommDB.DATABASE_VERSION);}@OverridepublicvoidonCreate(SQLiteDatabasedb){//Log.w(TAG,DATABASE_CREATE);//db.execSQL(DATABASE_CREATE);}@OverridepublicvoidonUpgrade(SQLiteDatabasedb,intoldVersion,intnewVersion){Log.w(TAG,"Upgradingdatabasefromversion"+oldVersion+"to"+newVersion+",whichwilldestroyallolddata");db.execSQL("DROPTABLEIFEXISTS"+SQLITE_TABLE);onCreate(db);}}publicTeacherDB(Contextctx){this.mCtx=ctx;}publicTeacherDBopen()throwsSQLException{mDbHelper=newDatabaseHelper(mCtx);mDb=mDbHelper.getWritableDatabase();returnthis;}publicvoidclose(){if(mDbHelper!=null){mDbHelper.close();}}publiclongcreateTeacher(Stringage,Stringgender,Stringname){longcreateResult=0;ContentValuesinitialValues=newContentValues();initialValues.put(KEY_AGE,age);initialValues.put(KEY_GENDER,gender);initialValues.put(KEY_NAME,name);try{createResult=mDb.insert(SQLITE_TABLE,null,initialValues);}catch(Exceptione){//TODO:handleexception}returncreateResult;}publicbooleandeleteAllTeachers(){intdoneDelete=0;try{doneDelete=mDb.delete(SQLITE_TABLE,null,null);Log.w(TAG,Integer.toString(doneDelete));Log.e("doneDelete",doneDelete+"");}catch(Exceptione){//TODO:handleexceptione.printStackTrace();}returndoneDelete>0;}publicbooleandeleteTeacherByName(Stringname){intisDelete;String[]tName;tName=newString[]{name};isDelete=mDb.delete(SQLITE_TABLE,KEY_AGE+"=?",tName);Log.e("deleteTicket","isDelete:"+isDelete+"||"+"ticketID="+name);returnisDelete>0;}publicvoidinsertSomeTickets(){}//扫描时进行判断本地数据库是否有此ticketIDpublicArrayList<Teacher>fetchAll(){ArrayList<Teacher>allTeacherList=newArrayList<Teacher>();CursormCursor=null;mCursor=mDb.query(SQLITE_TABLE,newString[]{KEY_ROWID,KEY_AGE,KEY_GENDER,KEY_NAME},null,null,null,null,null);if(mCursor.moveToFirst()){do{Teacherst=newTeacher();st.setAge(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_AGE)));st.setGender(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_GENDER)));st.setName(mCursor.getString(mCursor.getColumnIndexOrThrow(KEY_NAME)));allTeacherList.add(st);}while(mCursor.moveToNext());}if(mCursor!=null&&!mCursor.isClosed()){mCursor.close();}returnallTeacherList;}}


三,调用publicclassShowActivityextendsActivity
{

private StudentDB studentDB;private TeacherDB teacherDB;private List<Student> stList = new ArrayList<Student>();private List<Teacher> trList = new ArrayList<Teacher>(); @Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState); setContentView(R.layout.activity_show); studentDB = new StudentDB(this); studentDB.open(); teacherDB = new TeacherDB(this); teacherDB.open(); studentDB.createStudent("28", "男", "阿武"); studentDB.createStudent("24", "女", "小铃"); teacherDB.createTeacher("40", "男", "何SIR"); teacherDB.createTeacher("45", "女", "MRS谢"); stList = studentDB.fetchAll(); trList = teacherDB.fetchAll();for (int i = 0; i < stList.size(); i++) { Log.e("stList value", stList.get(i).getName()); }for (int i = 0; i < trList.size(); i++) { Log.e("trList value", trList.get(i).getName()); } }
@Overrideprotected void onDestroy() {// TODO Auto-generated method stubsuper.onDestroy();if (studentDB != null) { studentDB.close(); }if (teacherDB != null) { teacherDB.close(); } } @Overridepublic boolean onCreateOptionsMenu(Menu menu) {// Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.show, menu);return true; }}


四,结果验证;

10-25 16:50:10.321: E/stList value(3953): 阿武

10-25 16:50:10.321: E/stList value(3953): 小铃

10-25 16:50:10.321: E/trList value(3953): 何SIR

10-25 16:50:10.321: E/trList value(3953): MRS谢

五,注意事项:

此例子中插入数据库的数据是以年龄作为唯一字段,当插入的数据中,年龄字段有重复时,数据库会报错,此例子只为说明如何在一个数据库中建立多张表,因此,在实际项目中,一般以某个实体的ID作为唯一字段,且插入前必须经过判断;

另外,数据库的关闭,我们选择在onDestroy()方法中调用。

看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。