android sqlite CRUD 查询 事务

android sqlite CRUD 查询 事务

SQLite 操作详解–SQL语法
http://www.cnblogs.com/fuck1/p/5492884.html
Android程序员必掌握的sqlite数据库连表查询
http://www.android-study.com/jichuzhishi/580.html

SQLite
Sqlite数据库是一种轻量级数据库,是遵守ACID的关联式数据库管理系统，它具备跨平台，多语言操作(支持Windows/Linux/Unix等主流的操作系统，同时能够跟很多程序语言相结合，
如Tcl、PHP、Java、C++、.Net等，还有ODBC接口等优点，广泛应用于包括浏览器、IOS，Android以及一些便携需求的小型web应用系统)。它具备占用资源低(只需几百K的内存)，处理速度快(比起 Mysql、PostgreSQL这两款开源世界著名的数据库管理系统)等优点
特点：
开源、轻量级数据库：
SQLite和C/S模式的数据库软件不同，它是进程内的数据库引擎，不存在数据库的客户端和服务器。
使用SQLite一般只需要带上它的一个动态库，就可以享受它的全部功能。而且动态库的尺寸也挺小，几百K大小。
不需要”安装”：
SQLite的核心引擎本身不依赖第三方的软件，使用它也不需要”安装”。
单一文件:
数据库中所有的信息（比如表、视图等）都包含在一个文件内。这个文件可以自由复制到其它目录或其它机器上。
跨平台/可移植性:
除主流操作系统 windows/linux/unix之外，SQLite还支持ios\android等操作系统。
弱类型的字段：
同一列中的数据可以是不同类型

SQLite数据类型
一般数据采用的固定的静态数据类型，而SQLite采用的是动态数据类型，会根据存入值自动判断。SQLite数据类型：
NULL: 空值
VARCHAR(n)：长度不固定且其最大长度为n的字串，n不能超过4000。
CHAR(n)：长度固定为n的字串，n不能超过254。
INTEGER: 整数
REAL: 浮点数字,被存储为8字节的IEEE浮动标记序号.
TEXT: 文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
BLOB: BLOB数据块，以输入的数据格式进行存储。如何输入就如何存储,不改变格式。
DATA ：包含了 年份、月份、日期。
TIME： 包含了 小时、分钟、秒。

sqlite3还接受如下的数据类型：
smallint 16位元的整数。
interger 32位元的整数。
decimal(p,s) p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float 32位元的实数。
double 64位元的实数。
graphic(n) 和char(n)一样，不过其单位是两个字元double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n) 可变长度且其最大长度为 n 的双字元字串，n不能超过 2000
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。
datetime 包含日期时间格式，必须写成’2010-08-05’不能写为’2010-8-5’，否则在读取时会产生错误！

SQLite相关类、函数：
SQLiteOpenHelper：
是SQLiteDatabase一个辅助类。这个类主要生成一个数据库，并对数据库的版本进行管理。
当在程序当中调用这个类的方法getWritableDatabase()或者 getReadableDatabase()方法的时候，如果没有数据，Android系统就会自动生成一个数据库。
SQLiteOpenHelper是一个抽象类，通常需要继承它并实现里面的3个函数：
1、onCreate（SQLiteDatabase）
数据库第一次生成的时候会调用这个方法（创建数据库的时候才会调用），一般在这个方法里边生成数据库表。
2、onUpgrade（SQLiteDatabase，int，int）
数据库需要升级的时候，Android系统会主动的调用这个方法。一般在这个方法里边删除数据表，并建立新的数据表。
3、onOpen（SQLiteDatabase）：
打开数据库时的回调函数

SQLiteDatabase：
SQLiteDatabase代表一个数据库对象，该类封装了一些操作数据库的API，使用该类可以完成对数据进行添加(Create)、查询(Retrieve)、更新(Update)和删除(Delete)操作（这些操作简称为CRUD）。
在Android的SDK目录下有sqlite3工具，可以利用它创建数据库、创建表和执行一些SQL语句
SQLiteDatabase的常用方法：
openOrCreateDatabase(String path, SQLiteDatabase.CursorFactory factory) — 打开或创建数据库
insert(String table, String nullColumnHack, ContentValues values) — 插入一条记录
delete(String table, String whereClause, String[] whereArgs) — 删除一条记录
query(String table, String[] columns, String selection,String[] selectionArgs,String groupBy,String having,String orderBy) — 查询一条记录
rawQuery(String sql, String[] selectionArgs) — 查询记录
update(String table, ContentValues values, String whereClause,String[] whereArgs) — 修改记录
execSQL(String sql) — 执行一条SQL语句，execSQL()方法可以执行create、insert、delete、update有更改行为的SQL语句（不能用于查询SQL语句select）
beginTransaction() — 开启一个事务
endTransaction() — 检查事务的标志是否为成功，结束事务操作
setTransactionSuccessful() — 程序执行到endTransaction()之前调用了setTransactionSuccessful()方法设置事务的标志为成功则提交事务（开始执行事务中的操作），
如果没有调用setTransactionSuccessful()方法则回滚事务
close() — 关闭数据库

ContentValues类型的一个封装了列名称和列值的Map
SQLite却是大小写敏感的。
SQLite可以解析大部分标准SQL语句

1、打开或者创建数据库
openOrCreateDatabase(String path, SQLiteDatabae.CursorFactory factory) //SQLiteDatabase的静态方法
功能：打开或者创建数据库，它会自动去检测是否存在这个数据库，存在则打开，不存在则创建，创建成功则返回一个SQLiteDatabase对象，否则抛出异常FileNotFoundException。
path：数据库创建的路径
factory：一般设置为null
db=SQLiteDatabase.openOrCreateDatabase(“/data/data/com.sample.db/databases/sql.db”,null);

2、CRUD操作（有两种操作方式）：
（1）、函数execSQL(String sql) — sql语句

建表语句：creat table 表名(字段名&值列表)。如：creat table person (id integer primary key autoincrement, name varchar(20), age integer) 插入语句：insert into 表名(字段列表) values(值列表)。如：insert into person(name, age) values(‘xiaoxiao’,3)  更新语句：update 表名 set 字段名=值 where 条件子句。如：update person set name=‘xiaoxiao‘ where id=1  删除语句：delete from 表名 where 条件子句。如：delete from person where id=10 删除表的SQL语句drop table person

（2）、函数insert()、update()、delete()

//创建表 执行execSQL(String sql)创建String table="creat table person (id integer primary key autoincrement, name varchar(20), age integer) ";   //执行SQL语句   db.execSQL(table);    //插入数据SQLiteDatabase的insert(String table, String nullColumnHack, ContentValues values)table: 表名nullColumnHack: 空列的默认值values: ContentValues类型的键值对Key-Value示例：    ContentValues cValue = new ContentValues(); //实例化常量值   cValue.put("name","xiaoming"); //添加用户名     cValue.put("age",3);  //添加密码    db.insert("table",null,cValue); // 插入数据，相当于执行右边的sql语句：insert into table(name, age) values(‘xiaoming’,3)  SQLiteDatabase类提供了一个重载后的execSQL(String sql, Object[] bindArgs)方法，方法支持使用占位符参数(?)，可以解决String sql中若有转义字符的情况  db.execSQL("insert into person(name, age) values(?,?)", new Object[]{"xiaoxiao", 3}); //修改数据调用SQLiteDatabase的update(String table, ContentValues values, String whereClause, String[] whereArgs)table：表名values：行列ContentValues类型的键值对Key-ValuewhereClause： 更新条件（where字句）whereArgs：更新条件数组 示例：ContentValues values = new ContentValues();   //实例化内容值 values.put("name","dada");   //在values中添加内容     String whereClause = "id=?";   ///修改条件  String[] whereArgs={String.valuesOf(1)};  //修改添加参数db.update("table", values, whereClause, whereArgs); //修改数据，相当于执行右边的sql语句：update table set name = ‘dada‘ where id = 1  //删除数据调用SQLiteDatabase的delete(String table, String whereClause, String[] whereArgs)table: 表名whereClause: 删除条件whereArgs: 删除条件值数组  示例：  String whereClause = "id=?";  //删除条件  String[] whereArgs = {String.valueOf(1)}; //删除条件参数   db.delete("table",whereClause,whereArgs); //删除数据，相当于执行右边的sql语句：delete from table where id = 1 

3、查询数据：rawQuery()、query()
Cursor是SQLite数据库查询返回的行数集合, Cursor指向的就是每一条数据, Cursor是一个游标接口，提供了遍历查询结果的方法，如移动指针方法move()，获得列值方法getString()等。以下是Cursor遍历的方法
Cursor是结果集游标，用于对结果集进行随机访问，如果大家熟悉jdbc， 其实Cursor与JDBC中的ResultSet作用很相似
sqlite3多表、关联查询：
sql查询的join有以下几种：
cross join 是笛卡儿乘积 就是一张表的行数乘以另一张表的行数
left join 第一张表的连接列在第二张表中没有匹配是,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有匹配是,第一张表中的值返回null
full join 返回两张表中的行 left join+right join
inner join 只返回两张表连接列的匹配项
1、多表连接，如:
select * from animal,dog where animalt.cid = dog.id;
2、内查询
select * from animal inner join dog on animal.cid = dog.id; // 返回两张表连接列的匹配项
3、支持左外连接(left outer join), 如：
select * from animal left outer join dog on animal.cid=dog.id;
4、不支持右外连接和全连接。

//rawQuery用于执行select语句，获取结果集
public Cursor rawQuery(String sql, String[] selectionArgs)：
sql: select语句；
selectionArgs: select语句中占位符参数的值，参数值在数组中的顺序要和占位符(?)的位置对应, 如果select语句没有使用占位符，该参数可以设置为null
示例1：

SQLiteDatabase db = ....;   Cursor cursor = db.rawQuery(“select * from person”, null);   while (cursor.moveToNext()) {     int id = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始     String name = cursor.getString(1);//获取第二列的值     int age = cursor.getInt(2); //获取第三列的值    }    cursor.close();  db.close(); 

示例2：

Cursor cursor = db.rawQuery("select * from person where name like ? and age=?", new String[]{"xiaoxiao", "3"});

//Cursor类型的返回值，相当于结果集ResultSet ，query()相当于执行查询语句：
select * from 表名 where 条件子句 group by 分组子句 having … order by 排序子句 limit 记录数 offset 偏移量

public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit);

table: 表名, 如果是多表联合查询，可以用逗号将两个表名分开
columns: 列名称数组, 要查询出来的列名。相当于select语句select关键字后面的部分—”*”
selection: 查询条件子句，相当于select语句where关键字后面的部分，在条件子句允许使用占位符“?”
selectionArgs:条件字句的参数数组,对应于selection语句中占位符的值，值在数组中的位置与占位符在语句中的位置必须一致，否则就会有异常
groupBy: 分组子句, 相当于select语句group by关键字后面的部分
having: 分组条件, 相当于select语句having关键字后面的部分
orderBy: 排序列, 相当于select语句order by关键字后面的部分，如：personid desc, age asc;
limit: 分页查询限制, 指定偏移量和获取的记录数，相当于select语句limit关键字后面的部分

Cursor是一个游标接口，提供了遍历查询结果的方法，如移动指针方法move()，获得列值方法getString()等.
Cursor游标常用方法
getCount() — 获得总的数据项数
isFirst() — 判断是否第一条记录
isLast() — 判断是否最后一条记录
moveToFirst() — 移动到第一条记录
moveToLast() — 移动到最后一条记录
move(int offset) — 移动到指定记录
moveToNext() — 移动到下一条记录
moveToPrevious() — 移动到上一条记录
getColumnIndexOrThrow(String columnName) — 根据列名称获得列索引
getInt(int columnIndex) — 获得指定列索引的int类型值
getString(int columnIndex) — 获得指定列缩影的String类型值

示例：

SQLiteDatabase db = ...;   Cursor cursor = db.query("person", new String[]{"id,name,age"}, "name like ?", new String[]{"xiaoxiao"}, null, null, "age desc", "1,2");   while (cursor.moveToNext()) {      int personid = cursor.getInt(0); //获取第一列的值,第一列的索引从0开始     String name = cursor.getString(1);//获取第二列的值    int age = cursor.getInt(2);//获取第三列的值  }   cursor.close();    db.close();  

从rawQuery()、query()源码以及底层调用来看：
query() 参数是SQL语句中的各具体参数，
rawQuery() 参数是完整的SQL语句

public Cursor rawQuery(String sql, String[] selectionArgs) {    return rawQueryWithFactory(null, sql, selectionArgs, null, null); // 调用rawQueryWithFactory（）}public Cursor query(String table, String[] columns, String selection,     String[] selectionArgs, String groupBy, String having, String orderBy) {        return query(false, table, columns, selection, selectionArgs, groupBy, having, orderBy, null /* limit */);}public Cursor query(boolean distinct, String table, String[] columns,        String selection, String[] selectionArgs, String groupBy,        String having, String orderBy, String limit) {    return queryWithFactory(null, distinct, table, columns, selection, selectionArgs, groupBy, having, orderBy, limit, null);}public Cursor queryWithFactory(CursorFactory cursorFactory,        boolean distinct, String table, String[] columns,        String selection, String[] selectionArgs, String groupBy,        String having, String orderBy, String limit, CancellationSignal cancellationSignal) {    acquireReference();    try {        String sql = SQLiteQueryBuilder.buildQueryString(                distinct, table, columns, selection, groupBy, having, orderBy, limit);        return rawQueryWithFactory(cursorFactory, sql, selectionArgs, findEditTable(table), cancellationSignal); //调用rawQueryWithFactory（）    } finally {        releaseReference();    }}public Cursor rawQueryWithFactory(        CursorFactory cursorFactory, String sql, String[] selectionArgs,        String editTable, CancellationSignal cancellationSignal) {    acquireReference();    try {        SQLiteCursorDriver driver = new SQLiteDirectCursorDriver(this, sql, editTable,                cancellationSignal);        return driver.query(cursorFactory != null ? cursorFactory : mCursorFactory,                selectionArgs);    } finally {        releaseReference();    }}

4、事务（Transaction）
是恢复和并发控制的基本单位，是用户定义的一个操作序列，是一个不可分割的工作单位。通过事务，能将逻辑相关的一组操作绑定在一起，以便服务器保持数据的完整性。
事务通常是以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束。
COMMIT:
表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据库的更新写回到磁盘上的物理数据库中去，事务正常结束。
ROLLBACK:
表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有以完成的操作全部撤消，滚回到事务开始的状态。

事务运行的三种模式:
A:自动提交事务 — 每条单独的语句都是一个事务。每个语句后都隐含一个COMMIT。
B:显式事务 — 以BEGIN TRANSACTION显式开始，以COMMIT或ROLLBACK显式结束(sqlite以beginTransaction()显式开始，以endTransaction()显式结束）
C:隐性事务 — 在前一个事务完成时，新事务隐式启动，但每个事务仍以COMMIT或ROLLBACK显式结束。
事务的特性(ACID特性)
A:原子性(Atomicity) — 事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。
B:一致性(Consistency) — 事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
C:隔离性(Isolation) — 一个事务的执行不能被其他事务干扰。
D:持续性/永久性(Durability) — 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

示例：

SQLiteDatabase db = ...;      db.beginTransaction();  //开启事务try  {    db.execSQL("update person set age=5 where id=?", new Object[]{1});    db.execSQL("update person set age=6 where id=?", new Object[]{2});     db.execSQL("update person set name=‘xiaoxing‘ where id=?", new Object[]{3});         db.setTransactionSuccessful();  //设置事务标志为成功，当结束事务时就会提交事务  }catch（Exception e）{   throw(e);}finally  {     db.endTransaction();  //结束事务  }