Android Sqlite数据库 增删改查应用

一、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;
    }
}