一、Sqlite数据库介绍
二、Sqlite数据库增删改查在Android开发中应用
Sqlite数据库数据常用类型介绍
1、每个存储在 SQLite 数据库中的值都具有以下存储类之一:
NULL :值是一个 NULL 值。
INTEGER: 值是一个带符号的整数,根据值的大小存储在 1、2、3、4、6 或 8 字节中。
REAL : 值是一个浮点值,存储为 8 字节的 IEEE 浮点数字
TEXT:值是一个文本字符串,使用数据库编码(UTF-8、UTF-16BE 或 UTF-16LE)存储
BLOB:值是一个 blob 数据,完全根据它的输入存储。
2、Sqlite亲和类型
亲和类型 | 描述 |
---|---|
TEXT | 数值型数据在被插入之前,需要先被转换为文本格式,之后再插入到目标字段中。 |
NUMERIC | 当文本数据被插入到亲缘性为NUMERIC的字段中时,如果转换操作不会导致数据信息丢失以及完全可逆,那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据,如果转换失败,SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据,SQLite将不做任何转换,直接以NULL或BLOB的方式存储该数据。需要额外说明的是,对于浮点格式的常量文本,如"30000.0",如果该值可以转换为INTEGER同时又不会丢失数值信息,那么SQLite就会将其转换为INTEGER的存储方式。 |
INTEGER | 对于亲缘类型为INTEGER的字段,其规则等同于NUMERIC,唯一差别是在执行CAST表达式时。 |
REAL | 其规则基本等同于NUMERIC,唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。 |
NONE | 不做任何的转换,直接以该数据所属的数据类型进行存储。 |
INTEGER : INT , INTEGER ,TINYINT , SMALLINT ,MEDIUMINT ,BIGINT ,UNSIGNED BIG INT ,INT2 ,INT8
TEXT: CHARACTER(20) 、VARCHAR(255)、VARYING CHARACTER(255)、NCHAR(55)、NATIVE CHARACTER(70)、NVARCHAR(100)、TEXT、CLOB
NONE:BLOB no datatype specified
REAL:REAL、DOUBLE、DOUBLE PRECISION、FLOAT
NUMERIC:NUMERIC、DECIMAL(10,5)、BOOLEAN、DATE、DATETIME
SQLite 没有单独的 Boolean 存储类。相反,布尔值被存储为整数 0(false)和 1(true)。
Sqlite数据库在Android应用中使用
Android 平台内置了丰富的API可以供开发者调用SQLIte数据库
1、首先继承 SQLiteOpenHelper 类 并实现里面的 onCreate(SQLiteDatabase db)方法和 onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
onCreate 方法是应用程创建数据库及其表
onUpgrade 数据库版更新调用
2、创建一个单例类 LeDBManager 管理数据的增删改查
public class LeDBManager {
private static Context mContext;
private LeDBOpenHelper mDbOpenHelper;
private LeDBManager(Context context) {
mDbOpenHelper = new LeDBOpenHelper(context);
}
private static class SingletonHolder {
private static final LeDBManager INSTANCE = new LeDBManager(mContext);
}
public static final LeDBManager getInstance(Context context) {
mContext = context;
return SingletonHolder.INSTANCE;
}
//....................
}
代码的具体实现如下:
LeDBOpenHelper 类实现
public class LeDBOpenHelper extends SQLiteOpenHelper { private static final int DB_VERSION = 1 ; // 数据库版本 private static final String DB_NAME = "leNet.db" ; // 数据库名称 public LeDBOpenHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(createDownloadTable); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } public static final String TABLE_DOWNLOAD = "table_download" ; public static final String DOWNLOAD_NAME = "download_name" ; public static final String DOWNLOAD_URL = "download_url" ; public static final String DOWNLOAD_PATH = "download_path" ; public static final String DOWNLOAD_TOTAL_SIZE = "download_total_size" ; public static final String DOWNLOAD_DOWNLOAD_SIZE = "download_download_size" ; public static final String DOWNLOAD_STATUS = "download_status" ; private String createDownloadTable = "create table if not exists table_download (" + "id integer primary key autoincrement," + "download_name varchar ," + "download_url varchar ," + "download_path varchar ," + "download_total_size long ," + "download_download_size long," + "download_status smallint" + ")" ; }
LeDBManager 中实现数据的增删改查
/** * Created by familylove on 2017/4/26. * 数据库 * 增 * 删 * 改 * 查 * 管理类 */ public class LeDBManager { private static Context mContext; private LeDBOpenHelper mDbOpenHelper; private LeDBManager(Context context) { mDbOpenHelper = new LeDBOpenHelper(context); } private static class SingletonHolder { private static final LeDBManager INSTANCE = new LeDBManager(mContext); } public static final LeDBManager getInstance(Context context) { mContext = context; return SingletonHolder.INSTANCE; } /** * 插入数据 * * @param bean */ public void insertDownload(DownloadBean bean) { if (bean == null) return; SQLiteDatabase db = mDbOpenHelper.getWritableDatabase(); db.beginTransaction(); if (!isExistsDownload(db, bean.getUrl())) { db.insert(LeDBOpenHelper.TABLE_DOWNLOAD, null, bean.toContentValues(bean)); } db.setTransactionSuccessful(); db.endTransaction(); if (db != null) db.close(); } /** * 删除数据 * @param tag */ public boolean deleteDownload(String tag){ boolean flag = false ; if (TextUtils.isEmpty(tag)) return flag; SQLiteDatabase db = mDbOpenHelper.getWritableDatabase() ; db.beginTransaction(); int deleteFlag = db.delete(LeDBOpenHelper.TABLE_DOWNLOAD,LeDBOpenHelper.DOWNLOAD_URL+" = ? ",new String[]{tag}) ; db.setTransactionSuccessful(); db.endTransaction(); if (db!=null) db.close(); if (deleteFlag==1) flag = true ; else if (deleteFlag==0) flag = false ; return flag ; } /** * 更新数据 * * @param tag * @param totalSize * @param downloadSize * @param status */ public void updateDownload(String tag, long totalSize, long downloadSize, int status) { SQLiteDatabase db = mDbOpenHelper.getWritableDatabase(); db.beginTransaction(); if (isExistsDownload(db, tag)) { ContentValues updateCV = new ContentValues(); updateCV.put(LeDBOpenHelper.DOWNLOAD_TOTAL_SIZE, totalSize); updateCV.put(LeDBOpenHelper.DOWNLOAD_DOWNLOAD_SIZE, downloadSize); updateCV.put(LeDBOpenHelper.DOWNLOAD_STATUS, status); db.update(LeDBOpenHelper.TABLE_DOWNLOAD, updateCV, LeDBOpenHelper.DOWNLOAD_URL + " = ?", new String[]{tag}); } db.setTransactionSuccessful(); db.endTransaction(); if (db != null) db.close(); db = null; } /** * 判断数据是否存在 * @param db * @param tag * @return */ private boolean isExistsDownload(SQLiteDatabase db, String tag) { boolean flag = false; String sql = "select * from " + LeDBOpenHelper.TABLE_DOWNLOAD + " where " + LeDBOpenHelper.DOWNLOAD_URL + " = ? "; Cursor cursor = db.rawQuery(sql, new String[]{tag}); if (cursor != null && cursor.getCount() > 0) { flag = true; } else { flag = false; } if (cursor != null) cursor.close(); return flag; } /** * 数据查询 * @param tag * @return */ public DownloadBean queryOneDownload(String tag) { DownloadBean bean = null; SQLiteDatabase db = mDbOpenHelper.getReadableDatabase(); Cursor cursor = db.rawQuery( "select * from " + LeDBOpenHelper.TABLE_DOWNLOAD + " where " + LeDBOpenHelper.DOWNLOAD_URL + " = ?", new String[]{tag}); if (cursor != null && cursor.getCount() > 0) { if (cursor.moveToNext()) { bean = cursorToDownloadBean(cursor); } } if (cursor != null) cursor.close(); if (db != null) db.close(); return bean; } /** * 计算表 tableName 数据总量 * @param tableName * @return 数据表数据总条数 */ public int countDownloadBeans(String tableName){ int count = 0 ; SQLiteDatabase db = mDbOpenHelper.getReadableDatabase() ; Cursor cursor = db.rawQuery("select count(*) from "+tableName,null) ; if (cursor!=null && cursor.getCount()>0){ cursor.moveToFirst() ; count = cursor.getInt(0) ; } return count ; } /** * cursor to bean * @param cursor * @return */ private DownloadBean cursorToDownloadBean(Cursor cursor) { DownloadBean bean = new DownloadBean(); bean.setStatus(cursor.getInt(cursor.getColumnIndex(LeDBOpenHelper.DOWNLOAD_STATUS))); bean.setDownloadSize(cursor.getLong(cursor.getColumnIndex(LeDBOpenHelper.DOWNLOAD_DOWNLOAD_SIZE))); bean.setTotalSize(cursor.getLong(cursor.getColumnIndex(LeDBOpenHelper.DOWNLOAD_TOTAL_SIZE))); bean.setUrl(cursor.getString(cursor.getColumnIndex(LeDBOpenHelper.DOWNLOAD_URL))); bean.setName(cursor.getString(cursor.getColumnIndex(LeDBOpenHelper.DOWNLOAD_NAME))); bean.setPath(cursor.getString(cursor.getColumnIndex(LeDBOpenHelper.DOWNLOAD_PATH))); return bean; } }
DownloadBean类public class DownloadBean implements Serializable { private String name; private String path; private String url; private int status = 0; private long totalSize; private long downloadSize; public long getDownloadSize() { return downloadSize; } public void setDownloadSize(long downloadSize) { this.downloadSize = downloadSize; } public long getTotalSize() { return totalSize; } public void setTotalSize(long totalSize) { this.totalSize = totalSize; } public DownloadBean() { } public DownloadBean(String name, String path, String url) { this.name = name; this.path = path; this.url = url; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPath() { return path; } public void setPath(String path) { this.path = path; } public String getUrl() { return url; } public void setUrl(String url) { this.url = url; } public int getStatus() { return status; } public void setStatus(int status) { this.status = status; } @Override public String toString() { return "DownloadBean{" + "name='" + name + '\'' + ", path='" + path + '\'' + ", url='" + url + '\'' + ", status=" + status + ", totalSize=" + totalSize + ", downloadSize=" + downloadSize + '}'; } public ContentValues toContentValues(DownloadBean bean) { ContentValues cv = new ContentValues(); cv.put(LeDBOpenHelper.DOWNLOAD_STATUS, bean.getStatus()); cv.put(LeDBOpenHelper.DOWNLOAD_DOWNLOAD_SIZE, bean.getDownloadSize()); cv.put(LeDBOpenHelper.DOWNLOAD_TOTAL_SIZE, bean.getTotalSize()); cv.put(LeDBOpenHelper.DOWNLOAD_PATH, bean.getPath()); cv.put(LeDBOpenHelper.DOWNLOAD_URL, bean.getUrl()); cv.put(LeDBOpenHelper.DOWNLOAD_NAME, bean.getName()); return cv; } }