SQLite 操作封装 —— DatabaseUtil

DatabaseUtil.java view source print? 001 package com.dbexample; 002 003 import android.content.ContentValues; 004 import android.content.Context; 005 import android.database.Cursor; 006 import android.database.SQLException; 007 import android.database.sqlite.SQLiteDatabase; 008 import android.database.sqlite.SQLiteOpenHelper; 009 import android.util.Log; 010 011 public class DatabaseUtil{ 012 013 private static final String TAG = "DatabaseUtil"; 014 015 /** 016 * Database Name 017 */ 018 private static final String DATABASE_NAME = "student_database"; 019 020 /** 021 * Database Version 022 */ 023 private static final int DATABASE_VERSION = 1; 024 025 /** 026 * Table Name 027 */ 028 private static final String DATABASE_TABLE = "tb_student"; 029 030 /** 031 * Table columns 032 */ 033 public static final String KEY_NAME = "name"; 034 public static final String KEY_GRADE = "grade"; 035 public static final String KEY_ROWID = "_id"; 036 037 /** 038 * Database creation sql statement 039 */ 040 private static final String CREATE_STUDENT_TABLE = 041 "create table " + DATABASE_TABLE + " (" + KEY_ROWID + " integer primary key autoincrement, " 042 + KEY_NAME +" text not null, " + KEY_GRADE + " text not null);"; 043 044 /** 045 * Context 046 */ 047 private final Context mCtx; 048 049 private DatabaseHelper mDbHelper; 050 private SQLiteDatabase mDb; 051 052 /** 053 * Inner private class. Database Helper class for creating and updating database. 054 */ 055 private static class DatabaseHelper extends SQLiteOpenHelper { 056 DatabaseHelper(Context context) { 057 super(context, DATABASE_NAME, null, DATABASE_VERSION); 058 } 059 /** 060 * onCreate method is called for the 1st time when database doesn't exists. 061 */ 062 @Override 063 public void onCreate(SQLiteDatabase db) { 064 Log.i(TAG, "Creating DataBase: " + CREATE_STUDENT_TABLE); 065 db.execSQL(CREATE_STUDENT_TABLE); 066 } 067 /** 068 * onUpgrade method is called when database version changes. 069 */ 070 @Override 071 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 072 Log.w(TAG, "Upgrading database from version " + oldVersion + " to " 073 + newVersion); 074 } 075 } 076 077 /** 078 * Constructor - takes the context to allow the database to be 079 * opened/created 080 * 081 * @param ctx the Context within which to work 082 */ 083 public DatabaseUtil(Context ctx) { 084 this.mCtx = ctx; 085 } 086 /** 087 * This method is used for creating/opening connection 088 * @return instance of DatabaseUtil 089 * @throws SQLException 090 */ 091 public DatabaseUtil open() throws SQLException { 092 mDbHelper = new DatabaseHelper(mCtx); 093 mDb = mDbHelper.getWritableDatabase(); 094 return this; 095 } 096 /** 097 * This method is used for closing the connection. 098 */ 099 public void close() { 100 mDbHelper.close(); 101 } 102 103 /** 104 * This method is used to create/insert new record Student record. 105 * @param name 106 * @param grade 107 * @return long 108 */ 109 public long createStudent(String name, String grade) { 110 ContentValues initialValues = new ContentValues(); 111 initialValues.put(KEY_NAME, name); 112 initialValues.put(KEY_GRADE, grade); 113 return mDb.insert(DATABASE_TABLE, null, initialValues); 114 } 115 /** 116 * This method will delete Student record. 117 * @param rowId 118 * @return boolean 119 */ 120 public boolean deleteStudent(long rowId) { 121 return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0; 122 } 123 124 /** 125 * This method will return Cursor holding all the Student records. 126 * @return Cursor 127 */ 128 public Cursor fetchAllStudents() { 129 return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_NAME, 130 KEY_GRADE}, null, null, null, null, null); 131 } 132 133 /** 134 * This method will return Cursor holding the specific Student record. 135 * @param id 136 * @return Cursor 137 * @throws SQLException 138 */ 139 public Cursor fetchStudent(long id) throws SQLException { 140 Cursor mCursor = 141 mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, 142 KEY_NAME, KEY_GRADE}, KEY_ROWID + "=" + id, null, 143 null, null, null, null); 144 if (mCursor != null) { 145 mCursor.moveToFirst(); 146 } 147 return mCursor; 148 } 149 150 /** 151 * This method will update Student record. 152 * @param id 153 * @param name 154 * @param standard 155 * @return boolean 156 */ 157 public boolean updateStudent(int id, String name, String standard) { 158 ContentValues args = new ContentValues(); 159 args.put(KEY_NAME, name); 160 args.put(KEY_GRADE, standard); 161 return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + id, null) > 0; 162 } 163 } [代码] 使用方法 01 //插入 02 DatabaseUtil dbUtil = new DatabaseUtil(this); 03 dbUtil.open(); 04 dbUtil.createStudent("Prashant Thakkar", "10th"); 05 dbUtil.close(); 06 07 //查询 08 DatabaseUtil dbUtil = new DatabaseUtil(this); 09 dbUtil.open(); 10 Cursor cursor = dbUtil.fetchAllStudents(); 11 if(cursor != null){ 12 while(cursor.moveToNext()){ 13 Log.i("Student", "Student Name: " + cursor.getString(1) + 14 " Grade " + cursor.getString(2)); 15 } 16 } 17 dbUtil.close();