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();