android创建多个数据表，与数据库操作
---恢复内容开始---
新做了一个小项目，多数地方用到数据库操作，在此记录。
android自带sqlite，新建一个类继承SQLiteOpenHelper,重写oncreate，onupgrade代码，如下。
public class SkbDbHelper extends SQLiteOpenHelper { // If you change the database schema, you must increment the database version. private static final String TEXT_TYPE = " TEXT"; private static final String COMMA_SEP = ","; private static final String TIMESQL_CREATE_ENTRIES = "CREATE TABLE " + TimeEntry.TABLE_NAME + " (" + TimeEntry._ID + " INTEGER PRIMARY KEY," + TimeEntry.COLUMN_TITLE + TEXT_TYPE + COMMA_SEP + TimeEntry.COLUMN_TIME + TEXT_TYPE + COMMA_SEP + TimeEntry.COLUMN_BODY + TEXT_TYPE + COMMA_SEP + TimeEntry.COLUMN_CLOSE + TEXT_TYPE + " )"; private static final String STUSSQL_CREATE_ENTRIES = "CREATE TABLE " + StudentsEntry.TABLE_NAME + " (" + StudentsEntry._ID + " INTEGER PRIMARY KEY," + StudentsEntry.COLUMN_KID + TEXT_TYPE + COMMA_SEP + StudentsEntry.COLUMN_NAME + TEXT_TYPE + COMMA_SEP + StudentsEntry.COLUMN_CANJIA + TEXT_TYPE + COMMA_SEP + StudentsEntry.COLUMN_WENTI + TEXT_TYPE + COMMA_SEP + StudentsEntry.COLUMN_CLASS + TEXT_TYPE + " )"; private static final String BODYSQL_CREATE_ENTRIES = "CREATE TABLE " + BodyEntry.TABLE_NAME + " (" + BodyEntry._ID + " INTEGER PRIMARY KEY," + BodyEntry.COLUMN_NAME + TEXT_TYPE + COMMA_SEP + BodyEntry.COLUMN_CLASS + TEXT_TYPE + COMMA_SEP + BodyEntry.COLUMN_PWD + TEXT_TYPE + COMMA_SEP + BodyEntry.COLUMN_SHENFEN + TEXT_TYPE + COMMA_SEP + BodyEntry.COLUMN_SHIYUE + TEXT_TYPE + " )"; private static final String TIMESQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + TimeEntry.TABLE_NAME; private static final String STUSSQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + StudentsEntry.TABLE_NAME; private static final String BODYSQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS " + BodyEntry.TABLE_NAME; public static final int DATABASE_VERSION = 1; public static final String DATABASE_NAME = "FeedReader.db"; public SkbDbHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } public void onCreate(SQLiteDatabase db) { db.execSQL(TIMESQL_CREATE_ENTRIES); db.execSQL(STUSSQL_CREATE_ENTRIES); db.execSQL(BODYSQL_CREATE_ENTRIES); } public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // This database is only a cache for online data, so its upgrade policy is // to simply to discard the data and start over db.execSQL(TIMESQL_DELETE_ENTRIES); db.execSQL(STUSSQL_DELETE_ENTRIES); db.execSQL(BODYSQL_DELETE_ENTRIES); onCreate(db); } public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { onUpgrade(db, oldVersion, newVersion); } }
代码中创建了三个表，个人认为如果用到多个表，这算是一种简便的方法。
建好表以后创建数据表操作管理类，常用操作如下：
1、判断表中是否有数据
public boolean isDataExist(){ int count = 0; SQLiteDatabase db = null; Cursor cursor = null; try { db = SkbApplication.getInstance().getDbHelper().getReadableDatabase(); // select count(Id) from Orders cursor = db.query(BodyEntry.TABLE_NAME, new String[]{"COUNT(" + BodyEntry._ID + ")"}, null, null, null, null, null); if (cursor.moveToFirst()) { count = cursor.getInt(0); } if (count > 0) return true; } catch (Exception e) { Log.e("db", "", e); } finally { if (cursor != null) { cursor.close(); } if (db != null) { db.close(); } } return false; }
注1：dbhelper写到了application中，如下
public SkbDbHelper getDbHelper(){ if (dbHelper == null){ dbHelper = new SkbDbHelper(this); } return dbHelper; }
注2:bodyEntry为实体类，继承自BaseColumns，提供了_ID等。
public static class StudentsEntry implements BaseColumns { public static final String TABLE_NAME = "students"; public static final String COLUMN_KID= "kid"; //课程id public static final String COLUMN_NAME = "name"; public static final String COLUMN_CLASS = "class"; public static final String COLUMN_CANJIA = "canjia"; // 0 不参加， 1参加 public static final String COLUMN_WENTI = "wenti"; // 备注 }
继续表操作
2、查询所有数据
public List<StudentsBean> getAllDate(){ String[] projection = { StudentsEntry._ID, StudentsEntry.COLUMN_KID, StudentsEntry.COLUMN_NAME, StudentsEntry.COLUMN_CLASS, StudentsEntry.COLUMN_CANJIA, StudentsEntry.COLUMN_WENTI }; SQLiteDatabase db = null; Cursor cursor = null; try { db = SkbApplication.getInstance().getDbHelper().getReadableDatabase(); // select * from Orders cursor = db.query(StudentsEntry.TABLE_NAME, projection, null, null, null, null, null); if (cursor.getCount() > 0) { List<StudentsBean> orderList = new ArrayList<StudentsBean>(cursor.getCount()); while (cursor.moveToNext()) { orderList.add(parseOrder(cursor)); } return orderList; } } catch (Exception e) { Log.e("db", "", e); } finally { if (cursor != null) { cursor.close(); } if (db != null) { db.close(); } } return null; }
3、插入数据
public boolean insertDate(String kid,String name,String sclass,String wenti){ SQLiteDatabase db = null; try { db = SkbApplication.getInstance().getDbHelper().getWritableDatabase(); db.beginTransaction(); ContentValues contentValues = new ContentValues(); contentValues.put(StudentsEntry.COLUMN_KID, kid); contentValues.put(StudentsEntry.COLUMN_NAME, name); contentValues.put(StudentsEntry.COLUMN_CLASS, sclass); contentValues.put(StudentsEntry.COLUMN_CANJIA, "0"); contentValues.put(StudentsEntry.COLUMN_WENTI, wenti); db.insert(StudentsEntry.TABLE_NAME, null, contentValues); Toast.makeText(context,"报名成功",Toast.LENGTH_SHORT).show(); db.setTransactionSuccessful(); return true; }catch (SQLiteConstraintException e){ Toast.makeText(context, "主键重复", Toast.LENGTH_SHORT).show(); }catch (Exception e){ Log.e("db", "", e); }finally { if (db != null) { db.endTransaction(); db.close(); } } return false; }
4、删除数据
public boolean deleteOrder(String id) { // Define 'where' part of query. String selection = StudentsEntry.COLUMN_KID + " LIKE ?"; // Specify arguments in placeholder order. String[] selectionArgs = { id }; SQLiteDatabase db = null; try { db = SkbApplication.getInstance().getDbHelper().getWritableDatabase(); db.beginTransaction(); db.delete(StudentsEntry.TABLE_NAME, selection, selectionArgs); db.setTransactionSuccessful(); return true; } catch (Exception e) { Log.e("db", "", e); } finally { if (db != null) { db.endTransaction(); db.close(); } } return false; }
5、修改数据，此处查找_ID为“id”的数据，修改其COLUMN_CANJIA为“canjia”;
public boolean updateCanjia(String id,String canjia){ SQLiteDatabase db = null; try { db = SkbApplication.getInstance().getDbHelper().getWritableDatabase(); db.beginTransaction(); // update Orders set OrderPrice = 800 where Id = 6 ContentValues cv = new ContentValues(); cv.put(StudentsEntry.COLUMN_CANJIA, canjia); // Which row to update, based on the title String selection = StudentsEntry._ID + " = ?"; String[] selectionArgs = {id}; db.update(StudentsEntry.TABLE_NAME, cv, selection, selectionArgs); db.setTransactionSuccessful(); return true; } catch (Exception e) { Log.e("db", "", e); } finally { if (db != null) { db.endTransaction(); db.close(); } } return false; }
6、按条件查询，查询_ID为“id”并且COLUMN_CANJIA为“canjia”的数据。
public List<StudentsBean> gettitleBean(String id,String canjia){ String[] projection = { StudentsEntry._ID, StudentsEntry.COLUMN_KID, StudentsEntry.COLUMN_NAME, StudentsEntry.COLUMN_CLASS, StudentsEntry.COLUMN_CANJIA, StudentsEntry.COLUMN_WENTI }; // Filter results WHERE "title" = 'My Title' String selection = StudentsEntry.COLUMN_KID + " = ? and " + StudentsEntry.COLUMN_CANJIA + " = ?"; String[] selectionArgs = { id , canjia }; SQLiteDatabase db = null; Cursor cursor = null; try { db = SkbApplication.getInstance().getDbHelper().getReadableDatabase(); // select * from Orders where CustomName = 'Bor' cursor = db.query(StudentsEntry.TABLE_NAME, projection, selection, selectionArgs, null, null, null); if (cursor.getCount() > 0) { List<StudentsBean> orderList = new ArrayList<StudentsBean>(cursor.getCount()); while (cursor.moveToNext()) { StudentsBean order = parseOrder(cursor); orderList.add(order); } return orderList; } } catch (Exception e) { Log.e("db", "", e); } finally { if (cursor != null) { cursor.close(); } if (db != null) { db.close(); } } return null; }
最后附上 数据库数据插入list的语句
private StudentsBean parseOrder(Cursor cursor){ StudentsBean bean = new StudentsBean(); bean.setId(cursor.getInt(cursor.getColumnIndex(StudentsEntry._ID))); bean.setKid(cursor.getString(cursor.getColumnIndex(StudentsEntry.COLUMN_KID))); bean.setName(cursor.getString(cursor.getColumnIndex(StudentsEntry.COLUMN_NAME))); bean.setSclass(cursor.getString(cursor.getColumnIndex(StudentsEntry.COLUMN_CLASS))); bean.setCanjia(cursor.getString(cursor.getColumnIndex(StudentsEntry.COLUMN_CANJIA))); bean.setWenti(cursor.getString(cursor.getColumnIndex(StudentsEntry.COLUMN_WENTI))); return bean; }
目前用到这些。
