浅谈Android之Sqlite数据库

来源：互联网发布：淘宝店铺上传不了图片编辑：程序博客网时间：2024/06/07 19:24

浅谈Android之Sqlite数据库

在开发的过程中经常会出现需要使用数据库进行数据的保存，下面一起来谈谈android的sqlite数据库吧......

Android的五大存储方式：

1.文件存储

2.Sqlite数据库存储

3.ContentProvider存储数据

4.sharedpreference存储数据

5.网络存储

一.了解Sqplite数据库存储数据支持的几种数据格式

Sqlite存储类

存储类型描述NUll值是一个null值INTERGER值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。REAL值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。TEXT值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储BLOB值是一个 blob 数据，完全根据它的输入存储。
具体的数值类型请参考http://www.runoob.com/sqlite/sqlite-data-types.html，这里介绍的非常详细

二.开始使用Sqlite数据库

使用Android系统提供的接口SQLiteOpenHelper类创建数据库

public class MSqLiteHelper extends SQLiteOpenHelper {    //test_download 创建的数据库名称，1 数据库的版本    public MSqLiteHelper(Context context) {        super(context, "test_download.db", null, 1);    }    public MSqLiteHelper(Context context, String name,                         SQLiteDatabase.CursorFactory factory, int version) {        super(context, name, factory, version);    }    public MSqLiteHelper(Context context, String name, SQLiteDatabase.CursorFactory factory,                         int version, DatabaseErrorHandler errorHandler) {        super(context, name, factory, version, errorHandler);    }    @Override    public void onCreate(SQLiteDatabase db) {        String sql = "create table if not exists downstate(id Integer primary key autoincrement ," +                "name varchar(20),downurl varchar(50),allsize Integer(20),progress Integer(10));";        db.execSQL(sql);    }    //如果数据库版本值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        Log.i("tage","MSqLiteHelper onUpgrade" + newVersion);    }}

接下来创建自己的DbUtils

public class SqliteUtils {    private MSqLiteHelper mSqLiteHelper;    private SQLiteDatabase sqLiteDatabase;    public SqliteUtils(Context context) {        mSqLiteHelper =new MSqLiteHelper(context);    }    private void getWriteAbleDb(){        sqLiteDatabase = mSqLiteHelper.getWritableDatabase();    }    private void getReadAbleDb(){        sqLiteDatabase = mSqLiteHelper.getReadableDatabase();    }    public void addDownloadItem(DownLoadInfo info){        String sqlAdd = "insert into downstate values(NULL,?,?,?,?)";        getWriteAbleDb();        sqLiteDatabase.execSQL(sqlAdd,new String[]{                info.getName(),info.getUrl(),String.valueOf(info.getSize()),String.valueOf(info.getProgress())        });        closeDb();    }    public void deleteDownloadItem(DownLoadInfo info){        String sqlDelete = "delete from downstate where name=?";        getWriteAbleDb();        sqLiteDatabase.execSQL(sqlDelete,new String[]{                info.getName()        });        closeDb();    }    public DownLoadInfo queryOneitem(String nameQuery){        getReadAbleDb();        DownLoadInfo downLoadInfo;        String sqlQuery = "select * from downstate where name=?";        Cursor cursor = sqLiteDatabase.rawQuery(sqlQuery,new String[]{                nameQuery        });        if (cursor!=null){            while(cursor.moveToNext()){                int id = cursor.getInt(cursor.getColumnIndex(UserString.id));                String name = cursor.getString(cursor.getColumnIndex(UserString.name));                String downUrl = cursor.getString(cursor.getColumnIndex(UserString.downurl));                double size = cursor.getDouble(cursor.getColumnIndex(UserString.allsize));                double progress = cursor.getDouble(cursor.getColumnIndex(UserString.progress));                downLoadInfo = new DownLoadInfo(name,downUrl,size,progress);                return downLoadInfo;            }        }        return null;    }    public List<DownLoadInfo> queryAllitem(){        getReadAbleDb();        List list = new ArrayList<DownLoadInfo>();        DownLoadInfo downLoadInfo;        String sqlQuery = "select * from downstate";        Cursor cursor = sqLiteDatabase.rawQuery(sqlQuery,null);        if (cursor!=null){            while(cursor.moveToNext()){                int id = cursor.getInt(cursor.getColumnIndex(UserString.id));                String name = cursor.getString(cursor.getColumnIndex(UserString.name));                String downUrl = cursor.getString(cursor.getColumnIndex(UserString.downurl));                double size = cursor.getDouble(cursor.getColumnIndex(UserString.allsize));                double progress = cursor.getDouble(cursor.getColumnIndex(UserString.progress));                downLoadInfo = new DownLoadInfo(name,downUrl,size,progress);                list.add(downLoadInfo);            }        }        return list;    }    public int updateItemByName(String nameStart,String nameEnd){//        String sql = "update downstate set name=? where name=?";//        getWriteAbleDb();//        sqLiteDatabase.execSQL(sql,new String[]{//              nameEnd,nameStart//        });        ContentValues values = new ContentValues();        values.put(UserString.name, nameEnd);        getWriteAbleDb();        int rom = sqLiteDatabase.update("downstate",values,UserString.name +"=?",new String[]{                nameStart        });        return rom;    }    private void closeDb(){        if(sqLiteDatabase != null){            sqLiteDatabase.close();        }    }}

getWritableDatabase：返回SQLiteDataBase对象，可以进行读写操作，当磁盘已满或着权限不足时，会抛出异常

getReadableDatabase：返回SQLiteDataBase对象，可以进行读写操作，当磁盘已满或着权限不足时，不会抛出异常，而是返回一个可读的数据库对象

当我们调用这个方法getWritableDatabase，数据库文件才会创建，创建的数据库文件位于data/data/apk的包名/databases/的目录下

splite的常用语句

创建表：create table if not exists downstate(id Integer primary key autoincrement,name varchar(20),downurl varchar(50),alls，ize Integer(20),progress Integer(10));

插入数据：insert into downstate values(NULL,?,?,?,?)

删除数据：delete from downstate where name=?

查询单条数据：select * from downstate where name=?

查询所有数据：select * from downstate

更新数据：update downstate set name=? where name=?

完整代码下载地址：http://download.csdn.net/detail/qq_20302017/9905842

更多详细语法请见：http://www.w3school.com.cn/sql/sql_alter.asp

阅读全文

1 0