【Android】原生DBUtils工具类

MyDBHelper.java

<span style="font-size:14px;">package com.Lu.omw.db;import android.content.Context;import android.database.sqlite.SQLiteDatabase.CursorFactory;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;public class MyDBHelper extends SQLiteOpenHelper {public static final String DB_NAME = "mydb";public MyDBHelper(Context context) {super(context, DB_NAME, null, 6); // 从1开始}//只运行一次@Overridepublic void onCreate(SQLiteDatabase db) { // manage database creation// 创建表db.execSQL("create table gift(_id integer primary key autoincrement,name text) ");}// manage version management@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {// 假如有新版本:删除旧表，重新新建一个张表if (newVersion > oldVersion) {db.execSQL("drop table gift");onCreate(db);}</span>}}

DBUtils.java

package com.Lu.omw.utils;import java.util.ArrayList;import java.util.List;import com.Lu.omw.bean.GiftBean;import com.Lu.omw.db.MyDBHelper;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;import android.os.SystemClock;import android.support.v4.app.FragmentStatePagerAdapter;import android.text.GetChars;import android.util.Log;public class DBUtils {public static final String TABLE_NAME = "gift";/** * 插入 * @param context */public static void insert(Context context){//装载数据List<GiftBean> gifts = FileUtils.readDataFromAssets(context, GiftBean.class, "student.txt");Log.d("lulu", gifts.toString());//A helper class to manage database creation and version management.MyDBHelper myDBHelper = new MyDBHelper(context);//Create and/or open a database that will be used for reading and writing.SQLiteDatabase db = myDBHelper.getWritableDatabase();for(int i = 0; i < gifts.size(); i++){GiftBean giftBean = gifts.get(i);ContentValues contentValues = new ContentValues();  //HashMapcontentValues.put("name", giftBean.getGiftName());db.insert(TABLE_NAME, null, contentValues);}db.close();}public static void insertBySql(Context context){//装载数据到集合List<GiftBean> gifts = FileUtils.readDataFromAssets(context, GiftBean.class, "student.txt");MyDBHelper myDBHelper = new MyDBHelper(context);SQLiteDatabase db = myDBHelper.getWritableDatabase();long start = SystemClock.currentThreadTimeMillis();for(int i = 0; i < gifts.size(); i++){GiftBean gift = gifts.get(i);db.execSQL("insert into gift(name) values(?)", new String[]{gift.getGiftName()});}long end = SystemClock.currentThreadTimeMillis();Log.d("time : ", String.valueOf((end - start)/1000));db.close();}/** * 查询所有 * @param context * @return */public static List<GiftBean> query(Context context) {List<GiftBean> gifts = new ArrayList<GiftBean>();//创建sqli帮助类MyDBHelper myDBHelper = new MyDBHelper(context);//获取可读数据库SQLiteDatabase db = myDBHelper.getReadableDatabase();//获取数据库的游标//This interface provides random read-write access to the result set returned// by a database query.Cursor cursor = db.query(TABLE_NAME, new String[]{"_id, name"}, null, null, null,null, null);//迭代游标if(cursor != null){boolean firstCursor = cursor.moveToFirst();if(!firstCursor){return  null;}for(int i = 0; i < cursor.getCount(); i++){GiftBean giftBean = new GiftBean();String giftName = cursor.getString(cursor.getColumnIndex("name"));int id = cursor.getInt(cursor.getColumnIndex("_id"));giftBean.setGiftName(giftName);giftBean.setId(id);gifts.add(giftBean);cursor.moveToNext();}cursor.close();}db.close();return gifts;}/** * @param context * @return 查询 所有的 */public static List<GiftBean> queryBySql(Context context) {// 声明GiftBean 的集合List<GiftBean> gifts = new ArrayList<GiftBean>();MyDBHelper mDBHelper = new MyDBHelper(context);// 获取可读数据库SQLiteDatabase db = mDBHelper.getReadableDatabase();// 获取数据库的游标Cursor cursor = db.rawQuery("select _id,name from gift", null);// 迭代游标if (cursor != null) {while (cursor.moveToNext()) {GiftBean giftBean = new GiftBean();String giftName = cursor.getString(cursor.getColumnIndex("name"));int id = cursor.getInt(cursor.getColumnIndex("_id"));giftBean.setGiftName(giftName);giftBean.setId(id);gifts.add(giftBean);}}db.close();return gifts;}/** * @param context * @return Curosr */public static Cursor queryCursorBySql(Context context) {MyDBHelper mDBHelper = new MyDBHelper(context);// 获取可读数据库SQLiteDatabase db = mDBHelper.getReadableDatabase();// 获取数据库的游标Cursor cursor = db.rawQuery("select _id,name from gift", null);return cursor;}/** * @param context * @return Curosr.getCount() */public static int getCurcorCount(Context context) {MyDBHelper mDBHelper = new MyDBHelper(context);// 获取可读数据库SQLiteDatabase db = mDBHelper.getReadableDatabase();// 获取数据库的游标Cursor cursor = db.rawQuery("select _id,name from gift", null);return cursor.getCount();}/** * @param context * @param start 开始位置 * @param offset 偏移数量 * @return 返回从start开始往后的offset条数据的List */public static List<GiftBean> queryBySqlFromOffset(Context context,int start, int offset) {List<GiftBean> gifts = new ArrayList<GiftBean>();MyDBHelper myDBHelper = new MyDBHelper(context);SQLiteDatabase db = myDBHelper.getReadableDatabase();//获取数据库的游标Cursor cursor = db.rawQuery("select _id,name from gift limit ?,?",new String[]{String.valueOf(start), String.valueOf(offset)});if(cursor != null && cursor.moveToFirst()){while(cursor.moveToNext()){GiftBean gift = new GiftBean();gift.setGiftName(cursor.getString(cursor.getColumnIndex("name")));gift.setId(cursor.getInt(cursor.getColumnIndex("_id")));gifts.add(gift);}}db.close();return gifts;}/** * 查询id * @param context * @param id * @return */public static GiftBean queryById(Context context, int id){MyDBHelper myDBHelper = new MyDBHelper(context);SQLiteDatabase db = myDBHelper.getReadableDatabase();// select _id,name from gift where _id=?;Cursor cursor = db.query(TABLE_NAME, new String[]{"_id", "name"}, "_id=?",new String[]{String.valueOf(id)}, null, null, null);GiftBean giftBean = null;// cursor 不为空并且 存在第一条if(cursor != null && cursor.moveToFirst()){giftBean = new GiftBean();giftBean.setGiftName(cursor.getString(cursor.getColumnIndex("name")));giftBean.setId(id);}db.close();return giftBean;}/** * @param context * @return 查询 通过Id 出现 */public static GiftBean queryByIdBySql(Context context, int id) {// 创建数据库的帮助类MyDBHelper mDBHelper = new MyDBHelper(context);// 获得一个可读数据库SQLiteDatabase db = mDBHelper.getReadableDatabase();Cursor cursor = db.rawQuery("select _id,name from gift where _id=?",new String[] { String.valueOf(id) });// select _id,name from gift where _id=?;GiftBean giftBean = null;// cursor 不为空并且 存在第一条if (cursor != null && cursor.moveToFirst()) {giftBean = new GiftBean();String name = cursor.getString(cursor.getColumnIndex("name"));giftBean.setGiftName(name);giftBean.setId(id);}db.close();return giftBean;}/** * @param context * @return the number of rows affected if a whereClause is passed in, *    0 otherwise. To remove all rows and get a count pass "1"  *    as the whereClause. */public static int deleteAll(Context context){MyDBHelper myDBHelper = new MyDBHelper(context);SQLiteDatabase db = myDBHelper.getWritableDatabase();int row = db.delete(TABLE_NAME, null, null);db.close();return row;}/** * @param context * 删除所有的数据 */public static void deleteAllBySql(Context context) {MyDBHelper mDBHelper = new MyDBHelper(context);// 获得一个可读数据库SQLiteDatabase db = mDBHelper.getWritableDatabase();// db.delete(TABLE_NAME, null, null);db.execSQL("delete from gift");db.close();}/** * 通过id删除某条记录 * @param context * @param id * @return */public static int deleteById(Context context, int id){MyDBHelper myDBHelper = new MyDBHelper(context);SQLiteDatabase db = myDBHelper.getWritableDatabase();int row = db.delete(TABLE_NAME, "_id=?", new String[]{String.valueOf(id)});db.close();return row;}/** * @param context * @param id *            通过Id 删除某条记录 */public static void deleteByIdBySql(Context context, int id) {MyDBHelper mDBHelper = new MyDBHelper(context);// 获得一个可读数据库SQLiteDatabase db = mDBHelper.getWritableDatabase();// db.delete(TABLE_NAME, "_id=?", new String[] { String.valueOf(id) });db.execSQL("delete from gift where _id=?", new Object[] { id });// 关闭数据库db.close();}/** * 通过id更新名字 * @param context * @param id * @param name * @return */public static int updateById(Context context, int id, String name){MyDBHelper myDBHelper = new MyDBHelper(context);SQLiteDatabase db = myDBHelper.getWritableDatabase();ContentValues contentValues = new ContentValues();contentValues.put("name", name);// 第二参数：whereClause the optional WHERE clause to apply when updating.// Passing null will update all rows.// 第三参数 ：whereArgs You may include ?s in the where clause, which// will be replaced by the values from whereArgs. The values// will be bound as Strings.int row = db.update(TABLE_NAME, contentValues, "_id=?", new String[]{String.valueOf(id)});db.close();return row;}/** * @param context * @param id * @param name * 通过id 更新名字 */public static void updateByIdBySql(Context context, int id, String name) {MyDBHelper mDBHelper = new MyDBHelper(context);// 获得一个可读数据库SQLiteDatabase db = mDBHelper.getWritableDatabase();db.execSQL("update gift set name=? where _id=?", new Object[] { name,id });// update gift set name=? where _id=?db.close();}}