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);
}
}
}
到此就结束了，希望能给您带去帮助！
