08、Sqlite数据库

一、SQLite

1.1、创建数据库

SQLite是一款轻量型的数据库,是遵守ACID(原子性、一致性、隔离性、持久性)的关联式数据库管理系统,多用于嵌入式开发中。

1、数据类型

SQLite数据类型:Typelessness(弱类型),可以保存任何想要保存的数据到任何表的任何列中。

常见类型:NULL、VARCHAR、TEXT、INTEGER、BLOB、CLOB...

注:integer primary key 此字段只能存储64位整数

2、SQLiteOpenHelper

SQLiteOpenHelper是一个抽象类,用于对数据库版本进行管理。该类有两个常用方法

◇onCreate 数据库创建

◇onUpgrade 数据库更新

3、创建数据库

a) 创建类继承SQLiteOpenHelper用于对数据库更新和管理。

public class MyDBOpenHelper extends SQLiteOpenHelper {
    /**
     * @param context：上下文
     * @param name：数据库文件名称
     * @param factory：用来创建游标对象，null就用默认游标工厂
     * @param version：数据库的版本号从1开始
     */
    public MyDBOpenHelper(Context context) {
        super(context, "legend.db", null, 1);
    }

    /**
     * 数据库第一次被创建的时候调用，如果数据库已经创建则不会再执行
     * 功能：适合初始化数据库的表结构
     * @param db：代表的是我们创建出来的数据库
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        System.out.println("数据库被创建");
        db.execSQL("create table info (_id integer primary key autoincrement,name vatchar(20),phone varchar(20))");
    }

    /**
     * 数据库版本号被修改时调用，适合修改数据库的表结构
     * 注：数据库的版本号只能变大不能变小
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("onupgrade：数据库被升级，适合修改数据库的表结构");
        // 修改表结构，添加一列money字段
        db.execSQL("alter table info add money varchar(10)");
    }

b) 在Activity的onCreate()方法中调用下面的语句则会创建数据库。

// 执行下面一行代码时，数据库是不会创建的
MyDBOpenHelper helper = new MyDBOpenHelper(this);
// 如果想创建数据库必须执行下面一行代码
helper.getWritableDatabase();

4、事务的操作

　　所谓事务是用户定义的一个数据库操作序列,这些操作要么全做要么全不做,是一个不可分割的工作单位。使用SQLiteDatabase的beginTransaction()方法可以开启一个事务，

程序执行到endTransaction() 方法时会检查事务的标志是否为成功，如果程序执行到endTransaction()之前调用了setTransactionSuccessful() 方法设置事务的标志为成功，

则所有从beginTransaction()开始的操作都会被提交，如果没有调用setTransactionSuccessful() 方法则回滚事务。

// 1.创建数据库 定义一个类继承sqliteopenhelper
MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getReadableDatabase();
// 2.开启事务
db.beginTransaction();
try {
    // 实现转账逻辑，实际上就是执行sql语句
    db.execSQL("update info set money = money-100 where name=?",new Object[]{"张三"});
    // 3.模拟一个异常
    int i = 1 / 0;
    db.execSQL("update info set money = money+100 where name=?",new Object[]{"李四"});
    // 设置一个标记，表示当前事务成功，如果该句没有执行则事务回滚
    db.setTransactionSuccessful();
} catch (Exception e) {
    Toast.makeText(MainActivity.this, "对不起，服务器忙，请稍后再试！", Toast.LENGTH_SHORT).show();
}finally{
    db.endTransaction(); // 关闭事务
}

5、命令行查看数据库

1.首先在dos下输入adb shell进入手机终端。

2.然后进入数据库所在目录:cd /data/data/完整包名/databases

3.输入:sqlite3 数据库名.db 来进入该数据库。

4.输入:.tables可以列出所有的表

5.输入select * from info; 来查看表的数据。

6.命令行中是看不到中文的,因为命令行的编码是gbk,想要查看中文需要修改编码为utf-8,输入:chcp 65001就可以查看中文了。

注:中文在sqlite中是可以存储的,因为它采用的编码方式就是utf-8。

1.2、操作数据库

1、SqliteDatabase

　　SQLiteDatabase类是Android提供给开发者用来操作(CRUD)数据库的, 此类可以直接执行sql语句来操作数据库, 但也针对没有sql语言基础的开发者,提供了一套CRUD的方式,

只需传递对应参数就可以使用, 并且这种操作方式和Android四大组件之一ContentProvider的CRUD方法是无缝对接的。主要功能:sql语句直接操作和通过android提供的CRUD method操作。

2、Sql语句

增: insert into info (name,phone) values ('lisi','120')

删: delete from info where _id = '2'

修: update info set phone='999' where name='zhangsan'

查: select * from info where _id='1'

注:SQLite中所有的数据都是以字符串形式存在的,每个程序的数据库都是私有的，所以不需要注册驱动,也不需要输入账号密码去连

3、Sql注入攻击

SQLite和Mysql一样,通过拼接可以转义sql语句的语意,导致了安全隐患。

• execSQL(String sql):执行sql语句的方法,拼接字符串会导致sql注入的隐患。
• execSQL(String sql,Object[] bindArgs):完美解决sql注入,第二个参数表示占位符

4、Sql语句增删改查

这种方式首先是可能被注入攻击导致语意改变外，其次数据命令容易写错，而且没有返回值导致操作不方便。参考api增删改查。

a) 增加数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getWritableDatabase();
Random random = new Random();
String sql = "insert into info (name,phone) value(?,?)";
db.execSQL(sql,new Object[]{"list" + random.nextInt(100),"110-" + random.nextInt(100)});
db.close();
Toast.makeText(this, "添加成功", Toast.LENGTH_SHORT).show();

b) 删除数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getWritableDatabase();
String sql = "delete from info";
db.execSQL(sql);
Toast.makeText(this, "全部删除", Toast.LENGTH_SHORT).show();

c) 修改数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getWritableDatabase();
String sql = "update info set phone=?";
db.execSQL(sql,new Object[]{"8888"});
db.close();

d) 查询数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getWritableDatabase();
String sql = "select * from info";
// 第二个参数是选择条件，Cursor是游标，相当于指针
Cursor cursor = db.rawQuery(sql, null);
while(cursor.moveToNext()){
    String id = cursor.getString(0);
    String name = cursor.getString(1);
    String phone = cursor.getString(2);
}
cursor.close();
db.close();

5、api增删改查

a) 增加数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getWritableDatabase();
Random random = new Random();
// ContentValues底层封装的map集合
ContentValues values = new ContentValues();
values.put("name", "王五" + random.nextInt(100));
// 返回-1表示添加失败
long id = db.insert("info", null, values);
db.close();
if(id != -1){
    Toast.makeText(this, "添加成功", Toast.LENGTH_SHORT).show();
}else{
    Toast.makeText(this, "添加失败", Toast.LENGTH_SHORT).show();
}

b) 删除数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getWritableDatabase();
// 返回的是删除了几行
int result = db.delete("info", null, null);
db.close();
if(result == 0){
    Toast.makeText(this, "删除失败", Toast.LENGTH_SHORT).show();
}else{
    Toast.makeText(this, "删除" + result + "条记录", Toast.LENGTH_SHORT).show();
}

c) 修改数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
SQLiteDatabase db = helper.getWritableDatabase();
// 和hashtable类似，存储都是键值对
ContentValues values = new ContentValues();
values.put("phone", "9999");
int result = db.update("info", values, null, null);
if(result == 0){
    Toast.makeText(this, "修改了0条记录", Toast.LENGTH_SHORT).show();
}else{
    Toast.makeText(this, "修改了" + result + "条记录", Toast.LENGTH_SHORT).show();
}
db.close();

d) 查询数据

MyDBOpenHelper helper = new MyDBOpenHelper(this);
// 注意这里是读取，因为查询不需要修改数据库的内容
SQLiteDatabase db = helper.getReadableDatabase();
Cursor cursor = db.query("info", new String[]{"_id","name","phone"}, null, null, null, null, null);
while(cursor.moveToNext()){
    String id = cursor.getString(0);
    String name = cursor.getString(1);
    String phone = cursor.getString(2);
}
cursor.close();
db.close();

注:getWriteDatabase()和getReadDatabase()方法的区别在于Write是线程安全的，而Read是线程不安全的。

1.3、数据库更新

SQLite提供了ALTER TABLE命令，允许用户重命名或添加新的字段到已有表中，但是不能从表中删除字段。

并且只能在表的末尾添加字段，比如,为 Subscription添加两个字段：

ALTER TABLE Subscription ADD COLUMN Activation BLOB;
ALTER TABLE Subscription ADD COLUMN Key BLOB;

1、更新表操作

另外，如果遇到复杂的修改操作，比如在修改的同时，需要进行数据的转移，那么可以采取在一个事务中执行如下语句来实现修改表的需求。

a) 将表名改为临时表 

ALTER TABLE Subscription RENAME TO __temp__Subscription;

b) 创建新表

CREATE TABLE Subscription (OrderId VARCHAR(32) PRIMARY KEY ,UserName VARCHAR(32) NOT NULL ,ProductId VARCHAR(16) NOT NULL);

c) 导入数据

INSERT INTO Subscription SELECT OrderId, “”, ProductId FROM __temp__Subscription;
或者
INSERT INTO Subscription() SELECT OrderId, “”, ProductId FROM __temp__Subscription;

注意： 双引号”” 是用来补充原来不存在的数据的

d) 删除临时表

DROP TABLE __temp__Subscription;

通过以上四个步骤，就可以完成旧数据库结构向新数据库结构的迁移，并且其中还可以保证数据不会应为升级而流失。当然，如果遇到减少字段的情况，也可以通过创建临时表的方式来实现。

2、更新字段操作

Android应用程序更新的时候如果数据库修改了字段需要更新数据库，并且保留原来的数据库数据：

a) 这是原有的数据库表

CREATE_BOOK = "create table book(bookId integer primarykey,bookName text);";

b) 然后我们增加一个字段

CREATE_BOOK = "create table book(bookId integer primarykey,bookName text,bookContent text);";

c) 首先我们需要把原来的数据库表重命名一下

CREATE_TEMP_BOOK = "alter table book rename to _temp_book";

d) 然后把备份表中的数据copy到新创建的数据库表中

INSERT_DATA = "insert into book select *,' ' from _temp_book";（注意' '是为新加的字段插入默认值的必须加上，否则就会出错）

e) 然后把备份表删除

DROP_BOOK = "drop table _temp_book";

然后把数据库的版本后修改一下，再次创建数据库操作对象的时候就会自动更新（注：更新的时候第一个创建的操作数据的对象必须是可写的，

也就是通过这个方法getWritableDatabase()获取的数据库操作对象）,然后在onUpgrade()方法中执行上述sql语句就OK

public class DBService extends SQLiteOpenHelper{

    private String CREATE_BOOK = "create table book(bookId integer primarykey,bookName text);";
    private String CREATE_TEMP_BOOK = "alter table book rename to _temp_book";
    private String INSERT_DATA = "insert into book select *,'' from _temp_book";
    private String DROP_BOOK = "drop table _temp_book";
    
    public DBService(Context context, String name, CursorFactory factory,int version) {
        super(context, name, factory, version);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch (newVersion) {
        case 2:
            db.execSQL(CREATE_TEMP_BOOK);
            db.execSQL(CREATE_BOOK);
            db.execSQL(INSERT_DATA);
            db.execSQL(DROP_BOOK);
            break;
        }
    }
}

1.4、数据库优化

1.由于SQLiteDatabase对象较为耗费资源，所以我们在使用完SQLiteDatabase对象之后，必须立即关闭它，避免它继续占用资源，否则我们程序可能会导致OOM或者其他异常；

2.同理，我们在使用完cursor之后也应该立即关闭cursor，避免资源浪费；

3.我们在关闭数据库之前，必须将取得的数据保存到Map或者List中，否则关闭数据库之后，数据也会随之消失；

4.使用明确的查询语句，在数据库的查询中避免使用select * from table这样的语句。

5.在较为频繁的数据库操作中（例如批量插入数据），可以使用数据库事物来处理频繁操作（如果只有一个数据操作，则android默认使用事物）；

6.在sqlite数据库中使用索引，给经常查询的列创建索引，但索引不适用于较大的数据库，较大的数据库创建索引会耗费太多的时间，而且每次改动一条数据，整个索引都将重新创建；

7.如果需要在pc上制作数据库db文件，推荐使用Navicat Premium，功能强大，但是费用也很强大，不过可以免费试用；

 

转载于:https://www.cnblogs.com/pengjingya/p/5507695.html