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


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
一个简单的基于Android的Sqlite数据库的操作封装,它有如下的好处:便捷地创建表和增添表字段灵活的数据类型处理通过操作对象来insert或者update表记录支持多种查询方式,支持多表自定义的复杂查询,支持分页查询支持事务快速开始:    1. 设计表:@Table(name="t_user") public class UserModel {     @Table.Column(name="user_id",type=Column.TYPE_INTEGER,isPrimaryKey=true)     public Integer userId;     @Table.Column(name="user_name",type=Column.TYPE_STRING,isNull=false)     public String userName;     @Table.Column(name="born_date",type=Column.TYPE_TIMESTAMP)     public Date bornDate;     @Table.Column(name="pictrue",type=Column.TYPE_BLOB)     public byte[] pictrue;     @Table.Column(name="is_login",type=Column.TYPE_BOOLEAN)     public Boolean isLogin;     @Table.Column(name="weight",type=Column.TYPE_DOUBLE)     public Double weight; }2. 初始化对象:SQLiteDatabase db = context.openOrCreateDatabase("test.db", Context.MODE_PRIVATE, null); DbSqlite dbSqlite = new DbSqlite(db); IBaseDao userDAO = DaoFactory.createGenericDao(dbSqlite, UserModel.class);3. 创建表:userDAO.createTable(); 4. Insert 记录:UserModel user = new UserModel(); user.userName = "darcy"; user.isLogin = true; user.weight = 60.5; user.bornDate = new Date(); byte[] picture = {0x1,0x2,0x3,0x4}; user.pictrue = picture; userDAO.insert(user);5. Update 记录:UserModel user = new UserModel(); user.weight = 88.0; userDAO.update(user, "user_name=?", "darcy");6. 查询://单条结果查询 UserModel user = userDAO.queryFirstRecord("user_name=?", "darcy"); //一般查询 List userList = userDAO.query("user_name=? and weight > ?", "darcy" , "60"); //分页查询 PagingList pagingList = userDAO.pagingQuery(null, null, 1, 3);7. 事务支持:DBTransaction.transact(mDb, new DBTransaction.DBTransactionInterface() {         @Override         public void onTransact() {             // to do                 } };8. 更新表(目前只支持添加字段)@Table(name="t_user" , version=2) //修改表版本 public class UserModel {     //members above...     //new columns     @Table.Column(name="new_column_1",type=Column.TYPE_INTEGER)     public Integer newColumn;     @Table.Column(name="new_column_2",type=Column.TYPE_INTEGER)     public Integer newColumn2; } userDAO.updateTable();缺点和不足:还没支持多对一或者一多的关系没支持联合主键没支持表的外键设计其他...实例:SqliteLookup(Android内查看Sqlite数据库利器): https://github.com/YeDaxia/SqliteLookup 标签:SQLiteUtils

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值