Android中SQLite的使用

Android平台内置了丰富的API来供开发人员操作SQLite，我们可以轻松的完成对数据的存取。下面为大家分享我在项目中使用SQLite的实例、简单实用可移植性、可操作性都很好，可直接放入项目中使用。

使用的jar包　ormlite-android-4.45.jar 和　ormlite-core-4.45.jar

第一步：创建数据缓存

/**
 * 数据缓存
 * @author zcq
 * Created by 2016/6/5.
 */
public class DataCache implements Serializable {
    @DatabaseField(id = true)
    public String id; // id

    @DatabaseField
    public String content; // 内容

    @DatabaseField
    public long createTime; // 创建时间

    public DataCache() {
    }

    public DataCache(String id, String content) {
        this.id = id;
        this.content = content;
        this.createTime = System.currentTimeMillis();
    }

    public static class Table {
        /**
         * 表名 *
         */
        public static final String TABLE_NAME = "DataCache";
        /**
         * 表字段-Id *
         */
        public static final String TABLE_FIELD_ID = "id";
        /**
         * 表字段-内容 *
         */
        public static final String TABLE_FIELD_CONTENT = "content";
        /**
         * 表字段-创建时间 *
         */
        public static final String TABLE_FIELD_CREATE_TIME = "createTime";
    }
}

第二步：创建数据库及数据库操作类

/**
 * @author zcq
 * Created by 2016/6/5.
 */
public class BaseDataHelper extends OrmLiteSqliteOpenHelper {
    public static final String DATABASE_NAME = "tt.db";
    public static final int DATABASE_VERSION = 9;

    private Context context;

    public BaseDataHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase sqliteDatabase,
                         ConnectionSource connectionSource) {
        LogUtil.i("-----创建表");
        try {
            TableUtils.createTableIfNotExists(connectionSource, DataCache.class);// 缓存表
        } catch (SQLException e) {
            e.printStackTrace();
            LogUtil.e("创建表不成功", e);
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqliteDatabase,
                          ConnectionSource connectionSource, int oldVersion, int newVersion) {
        onCreate(sqliteDatabase, connectionSource);
        LogUtil.i("-----更新表");
        try {
            TableUtils.dropTable(connectionSource, DataCache.class, true);
            TableUtils.createTableIfNotExists(connectionSource, DataCache.class);// 缓存表
        } catch (SQLException e) {
            LogUtil.e("表更新失败", e);
        }
    }
}

/**
 * @author zcq
 *  Created by 2017/6/5.
 */
public abstract class AbstractDao<T, K> {
    private final static String TAG = AbstractDao.class.getName();
    protected Context context = null;
    protected Dao<T, K> dao = null;
    private BaseDataHelper dbHelper = null;

    public AbstractDao(Context context, Class<T> cls) {
        dbHelper = OpenHelperManager.getHelper(context, BaseDataHelper.class);
        try {
            dao = dbHelper.getDao(cls);

        } catch (SQLException e) {
            // LogUtil.e(TAG, "获取dao出错", e);
            LogUtil.w("获取dao出错", e);
        }
        this.context = context;
    }

    protected SQLiteDatabase getWritableDatabase() {
        return dbHelper.getWritableDatabase();
    }

    protected SQLiteDatabase getReadableDatabase() {
        return dbHelper.getReadableDatabase();
    }

    /**
     * 增加或修改数据
     *
     * @param item
     */
    public boolean addOrUpdate(T item) {
        try {
            CreateOrUpdateStatus status = dao.createOrUpdate(item);
            // status.
            return true;
        } catch (SQLException e) {
            // e.printStackTrace();
        }
        return false;
    }

    /**
     * 批量增加或修改数据
     *
     * @param items
     */
    public void batchAddOrUpdate(List<T> items) {
        try {
            for (T t : items) {
                dao.createOrUpdate(t);
            }
        } catch (SQLException e) {
            // e.printStackTrace();
            LogUtil.w("获取dao出错", e);
        }
    }

    /**
     * 根据ID删除记录
     *
     * @param id
     */
    public void delete(K id) {
        try {
            dao.deleteById(id);
        } catch (SQLException e) {
            // e.printStackTrace();
            LogUtil.w("删除数据出错", e);
        }
    }

    /**
     * 删除所有记录
     */
    public void deleteALL() {
        try {
            List<T> list = dao.queryForAll();
            dao.delete(list);
        } catch (SQLException e) {
            // e.printStackTrace();
            LogUtil.w("清空数据出错", e);
        }
    }

    /**
     * 查找满足条件的第一条记录
     *
     * @param cloumn
     * @param value
     * @return
     */
    public T getItem(String cloumn, Object value) {
        QueryBuilder<T, K> query = dao.queryBuilder();
        try {
            query.where().eq(cloumn, value);
            return query.queryForFirst();
        } catch (SQLException e) {
            // e.printStackTrace();
            LogUtil.w("获取数据出错", e);
        }
        return null;
    }

    /**
     * 查找满足条件的所有记录
     *
     * @param cloumn
     * @param value
     * @return
     */
    public List<T> getItems(String cloumn, Object value) {
        QueryBuilder<T, K> query = dao.queryBuilder();
        try {
            query.where().eq(cloumn, value);
            return query.query();
        } catch (SQLException e) {
            // e.printStackTrace();
            LogUtil.w("获取数据集出错", e);
        }
        return null;
    }

    /**
     * 根据ID查找项
     *
     * @param id
     * @return
     */
    public T getItem(K id) {
        try {
            return dao.queryForId(id);
        } catch (SQLException e) {
            // e.printStackTrace();
            LogUtil.w("获取数据出错", e);
        }
        return null;
    }

    /**
     * 获取所有记录
     *
     * @return
     */
    public List<T> getALL() {
        try {
            return dao.queryForAll();
        } catch (SQLException e) {
            // e.printStackTrace();
            LogUtil.w("获取数据出错", e);
        }
        return null;
    }
}

第三步：封装缓存数据库操作类

/**
 * 缓存数据库操作封装
 * @author zcq
 * Created by 2016/6/5.
 */
public class DataCacheDao extends AbstractDao<DataCache, String>{
    private final static String TAG = DataCacheDao.class.getName();

    public DataCacheDao(Context context) {
        super(context, DataCache.class);
    }

    /**
     * 增加或修改数据
     * @param cache
     */
    @Override
    public boolean addOrUpdate(DataCache cache) {
        try {
            dao.createOrUpdate(cache);
            return true;
        } catch (Exception e) {
            // LogUtil.e(TAG, "获取dao出错", e);
            LogUtil.w("数据添加失败", e);
        }
        return false;
    }

    /**
     * 根据ID获取content缓存
     * @param cacheId
     * @return
     */
    public String getDataCache(String cacheId) {
        DataCache dataCache = getItem(cacheId);
        if (null == dataCache || null == dataCache.content || dataCache.content.length() == 0) {
            return null;
        }
        return dataCache.content;
    }

    /**
     * 根据ID删除缓存
     * @param cacheId
     * @return
     */
    public boolean deleteDataCache(String cacheId) {
        DeleteBuilder<DataCache, String> deletebuilder = dao.deleteBuilder();
        try {
            deletebuilder.where().eq(DataCache.Table.TABLE_FIELD_ID, cacheId);
            deletebuilder.delete();
            return true;
        } catch (SQLException e) {
            LogUtil.w("数据删除失败", e);
        }
        return false;
    }

    /**
     * 获取所有缓存数据
     * @return
     */
    public List<DataCache> getAll(){
        List<DataCache> dataCaches = getALL();
        if(null == dataCaches || dataCaches.size() == 0){
            return null;
        }
        return dataCaches;
    }
}

第四步：在Application中初始化

public class App extends Application{
     public static App mInstance;
    public DataCacheDao dataCacheDao;

    @Override
    public void onCreate() {
        super.onCreate();
        mInstance = this;
        dataCacheDao = new DataCacheDao(mInstance);
    }

    public static App getInstance() {
        return mInstance;
    }

    /**
     * 增加、更新缓存数据
     * @param id
     * @param content
     */
    public void addDataCache(String id, String content) {
        if (null == dataCacheDao) {
            dataCacheDao = new DataCacheDao(App.getInstance());
        }
        DataCache dataCache = new DataCache(id, content);
        dataCacheDao.addOrUpdate(dataCache);
    }

    /**
     * 根据ID获取缓存数据
     * @param id
     * @return
     */
    public String getDataCache(String id) {
        if (null == dataCacheDao) {
            dataCacheDao = new DataCacheDao(App.getInstance());
        }
        return dataCacheDao.getDataCache(id);
    }
   
   /**
     * 删除缓存数据（根据ID）
     * @param id
     * @return
     */
    public boolean deleteDataCache(String id){
        if (null == dataCacheDao) {
            dataCacheDao = new DataCacheDao(GlobalApplication.getInstance());
        }
        return dataCacheDao.deleteDataCache(id);
    }

   /**
     * 获取所有缓存数据
     * @return
     */
    public List<DataCache> getAll(){
        if (null == dataCacheDao) {
            dataCacheDao = new DataCacheDao(GlobalApplication.getInstance());
        }
        return dataCacheDao.getAll();
    }
}

第五步：　实际操作中的使用

1.添加缓存数据
//id 唯一标识　content缓存的内容（可以是对象json也可是listJson）
Application.getInstance().addDataCache(id, content);
2.根据id获取缓存数据
pplication.getInstance().getDataCache(id);
3.获取所有缓存数据
List<DataCache> dataCaches = Application.getInstance().getAll();
4.根据id删除缓存数据
Application.getInstance().deleteDataCache(id)

SQLite事例中出现的LogUtil类

/**
 * @author zcq
 * Created by 20166/5.
 */
public class LogUtil {
    private static final String TAG = "app";

    public final static void v(String msg) {
        if (true) { //可以配置成全局常量
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.v(TAG, fileInfo + msg);
        }

    }

    public final static void v(String msg, Throwable tr) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.v(TAG, fileInfo + msg, tr);
        }

    }

    public final static void d(String msg) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.d(TAG, fileInfo + msg);
        }
    }

    public final static void d(String msg, Throwable tr) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.d(TAG, fileInfo + msg, tr);
        }
    }

    public final static void i(String msg) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.i(TAG, fileInfo + msg);
        }
    }

    public final static void i(String msg, Throwable tr) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.i(TAG, fileInfo + msg, tr);
        }
    }

    public final static void e(String msg) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.e(TAG, fileInfo + msg);
        }
    }

    public final static void e(String msg, Throwable tr) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.e(TAG, fileInfo + msg, tr);
        }
    }

    public final static void w(String msg) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.w(TAG, fileInfo + msg);
        }
    }

    public final static void w(String msg, Throwable tr) {
        if (true) {
            StackTraceElement stackTrace = java.lang.Thread.currentThread()
                    .getStackTrace()[3];
            String fileInfo = stackTrace.getFileName() + "("
                    + stackTrace.getLineNumber() + ") "
                    + stackTrace.getMethodName() + "";
            Log.w(TAG, fileInfo + msg, tr);
        }
    }
}

到此就结束了，希望能给您带去帮助！