Android项目中如何在一个数据库里建立多张表

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

一，创建一个公共的DBAdapter;

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

1，创建唯一的数据库：

 1 public class CommDB { 2  3     public static final String DATABASE_NAME = "myDatabase"; //数据库名称 4  5     public static final int DATABASE_VERSION = 1; 6     //创建该数据库下学生表的语句 7     private static final String CREATE_TABLE_Students = 8         "CREATE TABLE if not exists " + StudentDB.SQLITE_TABLE + " (" + 9       StudentDB.KEY_ROWID + " integer PRIMARY KEY autoincrement," +10       StudentDB.KEY_AGE + "," +11       StudentDB.KEY_GENDER + "," +12       StudentDB.KEY_NAME + "," +13         " UNIQUE (" + StudentDB.KEY_NAME +"));";//暂时规定不能重名14   //创建该数据库下教师表的语句15     private static final String CREATE_TABLE_Teachers =16           "CREATE TABLE if not exists " + TeacherDB.SQLITE_TABLE + " (" +17                   TeacherDB.KEY_ROWID + " integer PRIMARY KEY autoincrement," +18                   TeacherDB.KEY_AGE + "," +19                   TeacherDB.KEY_GENDER + "," +20                   TeacherDB.KEY_NAME + "," +21           " UNIQUE (" + TeacherDB.KEY_AGE +"));";22     private final Context context; 23     private DatabaseHelper DBHelper;24     private SQLiteDatabase db;25     /**26      * Constructor27      * @param ctx28      */29     public CommDB(Context ctx)30     {31         this.context = ctx;32         this.DBHelper = new DatabaseHelper(this.context);33     }34 35     private static class DatabaseHelper extends SQLiteOpenHelper 36     {37         DatabaseHelper(Context context) 38         {39             super(context, DATABASE_NAME, null, DATABASE_VERSION);40         }41 42         @Override43         public void onCreate(SQLiteDatabase db) 44         {45             db.execSQL(CREATE_TABLE_Students);//创建学生表46             db.execSQL(CREATE_TABLE_Teachers);//创建教师表 47         }48 49         @Override50         public void onUpgrade(SQLiteDatabase db, int oldVersion, 51         int newVersion) 52         {               53             // Adding any table mods to this guy here54         }55     } 56 57    /**58      * open the db59      * @return this60      * @throws SQLException61      * return type: DBAdapter62      */63     public CommDB open() throws SQLException 64     {65         this.db = this.DBHelper.getWritableDatabase();66         return this;67     }68 69     /**70      * close the db 71      * return type: void72      */73     public void close() 74     {75         this.DBHelper.close();76     }77 }

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

 1 public class GApplication extends Application { 2     private CommDB comDBHelper; 3  4     @Override 5     public void onCreate() { 6         // TODO Auto-generated method stub 7         super.onCreate(); 8         comDBHelper = new CommDB(this); 9         comDBHelper.open();10     }11     12 }

二，分别创建对应的数据表；

1，建立学生数据表类：

public class StudentDB {public static final String KEY_ROWID = "_id";public static final String KEY_AGE = "age";public static final String KEY_GENDER = "gender";public static final String KEY_NAME = "name";private static final String TAG = "StudentDbAdapter";private DatabaseHelper mDbHelper;private SQLiteDatabase mDb;    // private static final String DATABASE_NAME = "Fortrun_Ticket11";static final String SQLITE_TABLE = "StudentTable";private final Context mCtx;private static class DatabaseHelper extends SQLiteOpenHelper {        DatabaseHelper(Context context) {super(context, CommDB.DATABASE_NAME, null, CommDB.DATABASE_VERSION);        }        @Overridepublic void onCreate(SQLiteDatabase db) {                    }        @Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "                    + newVersion + ", which will destroy all old data");            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);            onCreate(db);        }    }public StudentDB(Context ctx) {this.mCtx = ctx;    }public StudentDB open() throws SQLException {        mDbHelper = new DatabaseHelper(mCtx);        mDb = mDbHelper.getWritableDatabase();return this;    }public void close() {if (mDbHelper != null) {            mDbHelper.close();        }    }/** * 创建学生表的字段     * @param age     * @param gender     * @param name     * @return */public long createStudent(String age, String gender, String name) {long createResult = 0;        ContentValues initialValues = new ContentValues();        initialValues.put(KEY_AGE, age);        initialValues.put(KEY_GENDER, gender);        initialValues.put(KEY_NAME, name);try {            createResult = mDb.insert(SQLITE_TABLE, null, initialValues);        } catch (Exception e) {// TODO: handle exception        }return createResult;    }/** * 删除表的全部字段数据     * @return */public boolean deleteAllStudents() {int doneDelete = 0;try {            doneDelete = mDb.delete(SQLITE_TABLE, null, null);            Log.w(TAG, Integer.toString(doneDelete));            Log.e("doneDelete", doneDelete + "");        } catch (Exception e) {// TODO: handle exception            e.printStackTrace();        }return doneDelete > 0;    }/** * 根据名称删除表中的数据      * @param name     * @return */public boolean deleteTicketByName(String name) {int isDelete;        String[] tName;        tName = new String[] { name };        isDelete = mDb.delete(SQLITE_TABLE, KEY_AGE + "=?", tName);        Log.e("deleteTicket", "isDelete:" + isDelete + "||" + "ticketID="                + name);return isDelete > 0;    }public void insertSomeTickets() {            }/** * 获取表中的所有字段     * @return */public ArrayList fetchAll() {        ArrayList allTicketsList = new ArrayList();        Cursor mCursor = null;        mCursor = mDb.query(SQLITE_TABLE, new String[] { KEY_ROWID, KEY_AGE,                KEY_GENDER, KEY_NAME }, null, null, null, null, null);if (mCursor.moveToFirst()) {do {                Student st = new Student();                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();        }return allTicketsList;    }}

2，创建教师数据表类：

public class TeacherDB {public static final String KEY_ROWID = "_id";public static final String KEY_AGE = "age";public static final String KEY_GENDER = "gender";// 还要保留public static final String KEY_NAME = "name";private static final String TAG = "TeacherDbAdapter";private DatabaseHelper mDbHelper;private SQLiteDatabase mDb;// private static final String DATABASE_NAME = "Fortrun_Ticket11";static final String SQLITE_TABLE = "TeacherTable";private static final int DATABASE_VERSION = 1;private final Context mCtx;private static class DatabaseHelper extends SQLiteOpenHelper {        DatabaseHelper(Context context) {super(context, CommDB.DATABASE_NAME, null, CommDB.DATABASE_VERSION);        }        @Overridepublic void onCreate(SQLiteDatabase db) {// Log.w(TAG, DATABASE_CREATE);// db.execSQL(DATABASE_CREATE);        }        @Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "                    + newVersion + ", which will destroy all old data");            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);            onCreate(db);        }    }public TeacherDB(Context ctx) {this.mCtx = ctx;    }public TeacherDB open() throws SQLException {        mDbHelper = new DatabaseHelper(mCtx);        mDb = mDbHelper.getWritableDatabase();return this;    }public void close() {if (mDbHelper != null) {            mDbHelper.close();        }    }public long createTeacher(String age, String gender, String name) {long createResult = 0;        ContentValues initialValues = new ContentValues();        initialValues.put(KEY_AGE, age);        initialValues.put(KEY_GENDER, gender);        initialValues.put(KEY_NAME, name);try {            createResult = mDb.insert(SQLITE_TABLE, null, initialValues);        } catch (Exception e) {// TODO: handle exception        }return createResult;    }public boolean deleteAllTeachers() {int doneDelete = 0;try {            doneDelete = mDb.delete(SQLITE_TABLE, null, null);            Log.w(TAG, Integer.toString(doneDelete));            Log.e("doneDelete", doneDelete + "");        } catch (Exception e) {// TODO: handle exception            e.printStackTrace();        }return doneDelete > 0;    }public boolean deleteTeacherByName(String name) {int isDelete;        String[] tName;        tName = new String[] { name };        isDelete = mDb.delete(SQLITE_TABLE, KEY_AGE + "=?", tName);        Log.e("deleteTicket", "isDelete:" + isDelete + "||" + "ticketID="                + name);return isDelete > 0;    }public void insertSomeTickets() {            }// 扫描时进行判断本地数据库是否有此ticketIDpublic ArrayList fetchAll() {        ArrayList allTeacherList = new ArrayList();        Cursor mCursor = null;        mCursor = mDb.query(SQLITE_TABLE, new String[] { KEY_ROWID, KEY_AGE,                KEY_GENDER, KEY_NAME }, null, null, null, null, null);if (mCursor.moveToFirst()) {do {                Teacher st = new Teacher();                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();        }return allTeacherList;    }}

三，调用public class ShowActivity extends Activity
{

private StudentDB studentDB;private TeacherDB teacherDB;private List stList = new ArrayList();private List trList = new ArrayList();        @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()方法中调用。

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