转发：Android项目中，在一个数据库里建立多张表

一，创建一个公共的DBAdapter;

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

1，创建唯一的数据库：

public class CommDB { public static final String DATABASE_NAME = "myDatabase"; //数据库名称 public static final int DATABASE_VERSION = 1; //创建该数据库下学生表的语句 private static final String CREATE_TABLE_Students = "CREATE TABLE if not exists " + StudentDB.SQLITE_TABLE + " (" + StudentDB.KEY_ROWID + " integer PRIMARY KEY autoincrement," + StudentDB.KEY_AGE + "," + StudentDB.KEY_GENDER + "," + StudentDB.KEY_NAME + "," + " UNIQUE (" + StudentDB.KEY_NAME +"));";//暂时规定不能重名 //创建该数据库下教师表的语句 private static final String CREATE_TABLE_Teachers = "CREATE TABLE if not exists " + TeacherDB.SQLITE_TABLE + " (" + TeacherDB.KEY_ROWID + " integer PRIMARY KEY autoincrement," + TeacherDB.KEY_AGE + "," + TeacherDB.KEY_GENDER + "," + TeacherDB.KEY_NAME + "," + " UNIQUE (" + TeacherDB.KEY_AGE +"));"; private final Context context; private DatabaseHelper DBHelper; private SQLiteDatabase db; /** * Constructor * @param ctx */ public CommDB(Context ctx) { this.context = ctx; this.DBHelper = new DatabaseHelper(this.context); } private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE_Students);//创建学生表 db.execSQL(CREATE_TABLE_Teachers);//创建教师表 } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Adding any table mods to this guy here } } /** * open the db * @return this * @throws SQLException * return type: DBAdapter */ public CommDB open() throws SQLException { this.db = this.DBHelper.getWritableDatabase(); return this; } /** * close the db * return type: void */ public void close() { this.DBHelper.close(); } }

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

public class GApplication extends Application { private CommDB comDBHelper; @Override public void onCreate() { // TODO Auto-generated method stub super.onCreate(); comDBHelper = new CommDB(this); comDBHelper.open(); } }

3.在manifest.xml文件中添加Application

<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.example.fxjzzyo.phome"> <uses-permission android:name="android.permission.INTERNET" /> <application android:allowBackup="true" android:icon="@mipmap/ic_launcher" android:label="@string/app_name" android:name=".application.GApplication" android:supportsRtl="true" android:theme="@style/Theme.AppCompat.Light.NoActionBar"> <activity android:name=".activity.LoginActivity"> <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> <activity android:name=".activity.MainActivity" /> <activity android:name=".activity.RegiserActivity"></activity> </application> </manifest>

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

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); } @Override public void onCreate(SQLiteDatabase db) { } @Override public 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<Student> fetchAll() { ArrayList<Student> allTicketsList = new ArrayList<Student>(); 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); } @Override public void onCreate(SQLiteDatabase db) { // Log.w(TAG, DATABASE_CREATE); // db.execSQL(DATABASE_CREATE); } @Override public 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() { } // 扫描时进行判断本地数据库是否有此ticketID public ArrayList<Teacher> fetchAll() { ArrayList<Teacher> allTeacherList = new ArrayList<Teacher>(); 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; } }

三，在Activity中调用

public class ShowActivity extends Activity { private StudentDB studentDB; private TeacherDB teacherDB; private List<Student> stList = new ArrayList<Student>(); private List<Teacher> trList = new ArrayList<Teacher>(); @Override protected 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()); } } @Override protected void onDestroy() { // TODO Auto-generated method stub super.onDestroy(); if (studentDB != null) { studentDB.close(); } if (teacherDB != null) { teacherDB.close(); } } @Override public 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()方法中调用。

原文地址：http://www.cnblogs.com/crashmaker/p/3368812.html