Android Sqlite数据库增删改查应用

小灰灰 2022-06-17 12:47 255阅读 0赞

一、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亲和类型

<table style="border:0px; margin:4px 0px; padding:0px; border-collapse:collapse; width:729.6px; color:rgb(51,51,51); font-family:&quot;Open Sans&quot;,&quot;Helvetica Neue&quot;,Helvetica,Arial,STHeiti,&quot;Microsoft Yahei&quot;,sans-serif; font-size:12px"> 
 <tbody style="border:0px; margin:0px; padding:0px"> 
  <tr style="border:0px; margin:0px; padding:0px; background-color:rgb(246,244,240)"> 
   <th style="border:1px solid rgb(85,85,85); margin:0px; padding:3px; color:rgb(255,255,255); background-color:rgb(85,85,85); vertical-align:top; text-align:left; width:212.713px"> 亲和类型</th> 
   <th style="border:1px solid rgb(85,85,85); margin:0px; padding:3px; color:rgb(255,255,255); background-color:rgb(85,85,85); vertical-align:top; text-align:left; width:503.217px"> 描述</th> 
  </tr> 
  <tr style="border:0px; margin:0px; padding:0px"> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; min-width:24px; line-height:2em; vertical-align:top"> TEXT</td> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; line-height:2em; min-width:24px; vertical-align:top"> 数值型数据在被插入之前，需要先被转换为文本格式，之后再插入到目标字段中。</td> 
  </tr> 
  <tr style="border:0px; margin:0px; padding:0px; background-color:rgb(246,244,240)"> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; min-width:24px; line-height:2em; vertical-align:top"> NUMERIC</td> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; line-height:2em; min-width:24px; vertical-align:top"> 当文本数据被插入到亲缘性为NUMERIC的字段中时，如果转换操作不会导致数据信息丢失以及完全可逆，那么SQLite就会将该文本数据转换为INTEGER或REAL类型的数据，如果转换失败，SQLite仍会以TEXT方式存储该数据。对于NULL或BLOB类型的新数据，SQLite将不做任何转换，直接以NULL或BLOB的方式存储该数据。需要额外说明的是，对于浮点格式的常量文本，如"30000.0"，如果该值可以转换为INTEGER同时又不会丢失数值信息，那么SQLite就会将其转换为INTEGER的存储方式。</td> 
  </tr> 
  <tr style="border:0px; margin:0px; padding:0px"> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; min-width:24px; line-height:2em; vertical-align:top"> INTEGER</td> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; line-height:2em; min-width:24px; vertical-align:top"> 对于亲缘类型为INTEGER的字段，其规则等同于NUMERIC，唯一差别是在执行CAST表达式时。</td> 
  </tr> 
  <tr style="border:0px; margin:0px; padding:0px; background-color:rgb(246,244,240)"> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; min-width:24px; line-height:2em; vertical-align:top"> REAL</td> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; line-height:2em; min-width:24px; vertical-align:top"> 其规则基本等同于NUMERIC，唯一的差别是不会将"30000.0"这样的文本数据转换为INTEGER存储方式。</td> 
  </tr> 
  <tr style="border:0px; margin:0px; padding:0px"> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; min-width:24px; line-height:2em; vertical-align:top"> NONE</td> 
   <td style="border:1px solid rgb(212,212,212); margin:0px; padding:7px 5px; font-size:13px; line-height:2em; min-width:24px; vertical-align:top"> 不做任何的转换，直接以该数据所属的数据类型进行存储。</td> 
  </tr> 
 </tbody> 
</table>

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：BLOBno 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;
        }
    }