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<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); } @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<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; } }
三,调用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>();
@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()方法中调用。
看完上述内容是否对您有帮助呢?如果还想对相关知识有进一步的了解或阅读更多相关文章,请关注亿速云行业资讯频道,感谢您对亿速云的支持。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。