SQLite

一，创建数据库
1，继承SQLiteOpenHelper类：
public class XxDatabaseHelper extends SQLiteOpenHelper {}
2，创建XxDatabaseHelper的实例：
XxDatabaseHelper xxDatabaseHelper = new XxDatabaseHelper(this, “xxx.db”, null, 1);
3，调用XxDatabaseHelper实例的getReadableDatabase()方法或者getWritableDatabase()方法得到SQLiteDatabase的对象：
SQLiteDatabase sqLiteDatabase = xxDatabaseHelper.getWritableDatabase();
或者SQLiteDatabase sqLiteDatabase =xxDatabaseHelper.getReadableDatabase();

package com.jackie.app_test;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by Administrator on 2016/11/9. */public class MyDatabaseHelpler extends SQLiteOpenHelper {    public static final String CREATE_BOOK = "create table Book (\n" +            "id       integer    primary key    autoincrement,\n" +            "author   text,\n" +            "price    real,\n" +            "pages    integer,\n" +            "name     text\n" +            ")";    public MyDatabaseHelpler(Context context, String name, SQLiteDatabase.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) {    }}

package com.jackie.app_test;import android.app.Activity;import android.os.Bundle;import android.view.View;import android.widget.Button;/** * Created by Administrator on 2016/11/9. */public class SQLiteActivity extends Activity {    private Button btn_create;    private MyDatabaseHelpler helpler;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_sqlite);        btn_create = (Button)findViewById(R.id.btn_create);        helpler = new MyDatabaseHelpler(this,"BookStore.db",null,1);        btn_create.setOnClickListener(new View.OnClickListener() {            @Override            public void onClick(View v) {                helpler.getWritableDatabase();            }        });    }}

二，升级数据库（也就是更新数据库中的表）
1，在onCreate()方法中执行要升级的表
2，重写onUpgrade()方法
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
switch (oldVersion) {
case 1:
db.execSQL(xxx);
case 2:
db.execSQL(“xxx”);
default:
}
}
注：每一个数据库版本都会对应一个版本号， 当指定的数据库版本号大于当前数据库版本号的时候，
就会进入到 onUpgrade()方法中去执行更新操作。switch 中每一个 case 的最后都是没有使用 break 的，为什么要这么做呢？这是为了保证在跨版本升级的时候，每一次的数据库修改都能被全部执行到。
3，创建XxDatabaseHelper的实例
XxDatabaseHelper xxDatabaseHelper = new XxDatabaseHelper(this, “xxx.db”, null, 2);
4，调用XxDatabaseHelper实例的getReadableDatabase()方法或者getWritableDatabase()方法得到SQLiteDatabase的对象：
SQLiteDatabase sqLiteDatabase = xxDatabaseHelper.getWritableDatabase();
或者SQLiteDatabase sqLiteDatabase =xxDatabaseHelper.getReadableDatabase();

package com.jackie.app_test;import android.content.Context;import android.database.sqlite.SQLiteDatabase;import android.database.sqlite.SQLiteOpenHelper;/** * Created by Administrator on 2016/11/9. */public class MyDatabaseHelpler extends SQLiteOpenHelper {    public static final String CREATE_BOOK = "create table Book (\n" +            "id       integer    primary key    autoincrement,\n" +            "author   text,\n" +            "price    real,\n" +            "pages    integer,\n" +            "name     text\n" +            ")";    public static final String CREATE_CATEGORY = "create table category (\n" +            "id              integer   primary key     autoincrement,\n" +            "category_name   text,\n" +            "category_code   text\n" +            ")";    public MyDatabaseHelpler(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {        super(context, name, factory, version);    }    @Override    public void onCreate(SQLiteDatabase db) {        db.execSQL(CREATE_BOOK);        db.execSQL(CREATE_CATEGORY);    }    @Override    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {        switch (oldVersion) {        case 1:            db.execSQL(CREATE_CATEGORY);        case 2:            db.execSQL("alter table Book add column category_id integer");        default:    }    }}

package com.jackie.app_test;import android.app.Activity;import android.os.Bundle;import android.view.View;import android.widget.Button;/** * Created by Administrator on 2016/11/9. */public class SQLiteActivity extends Activity {    private Button btn_create;    private MyDatabaseHelpler helpler;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_sqlite);        btn_create = (Button)findViewById(R.id.btn_create);        helpler = new MyDatabaseHelpler(this,"BookStore.db",null,2);        btn_create.setOnClickListener(new View.OnClickListener() {            @Override            public void onClick(View v) {                helpler.getWritableDatabase();            }        });    }}

三，对数据库的CRUD操作
a，添加数据
1，使用ContentValues 来对要添加的数据进行组装
ContentValues values = new ContentValues();
调用values.put()方法
2，插入数据
调用SQLiteDatabase对象的insert()方法

package com.jackie.app_test;import android.app.Activity;import android.content.ContentValues;import android.database.sqlite.SQLiteDatabase;import android.os.Bundle;import android.view.View;import android.widget.Button;public class SQLiteActivity extends Activity {    private Button btn_add;    private MyDatabaseHelpler helpler;    @Override    protected void onCreate(Bundle savedInstanceState) {        super.onCreate(savedInstanceState);        setContentView(R.layout.activity_sqlite);        btn_add = (Button) findViewById(R.id.btn_add);        helpler = new MyDatabaseHelpler(this, "BookStore.db", null, 2);        btn_add.setOnClickListener(new View.OnClickListener() {            @Override            public void onClick(View v) {                SQLiteDatabase db = helpler.getWritableDatabase();                ContentValues values = new ContentValues();                // 开始组装第一条数据                values.put("name", "The Da Vinci Code");                values.put("author", "Dan Brown");                values.put("pages", 454);                values.put("price", 16.96);                db.insert("Book", null, values); // 插入第一条数据                values.clear();                // 开始组装第二条数据                values.put("name", "The Lost Symbol");                values.put("author", "Dan Brown");                values.put("pages", 510);                values.put("price", 19.95);                db.insert("Book", null, values); // 插入第二条数据            }        });    }}

b，更新数据
1，使用ContentValues 来对要添加的数据进行组装
ContentValues values = new ContentValues();
调用values.put()方法
2，插入数据
调用SQLiteDatabase对象的update()方法。update()方法中的第三、第四个参数用于去约束更新某一行或某几行中的数据，不指定的话默认就是更新所有行。

//核心代码btn_update.setOnClickListener(new View.OnClickListener() {            @Override            public void onClick(View v) {                SQLiteDatabase db = helpler.getWritableDatabase();                ContentValues values = new ContentValues();                values.put("price", 10.99);                db.update("Book", values, "name = ?", new String[]{"The Da Vinci Code" });            }        });

c，删除数据
调用SQLiteDatabase对象的delete()方法。delete()方法中的第二、第三个参数又是用于去约束删除某一行或某几行的数据，不指定的话默认就是删除所有行。

//核心代码btn_delete.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();db.delete("Book", "pages > ?", new String[] { "500" });}});

d，查询数据
1，调用SQLiteDatabase对象的query()方法，返回一个Cursor对象。query()方法中至少有七个参数：

2，通过Cursor对象的 getColumnIndex()方法获取到某一列在表中对应的位置索引
3，查询完之后就得到了一个Cursor对象，接着我们利用while循环调用它的moveToNext()方法。通过Cursor对象的getXxx()方法得到相应的数据，并执行相关业务逻辑。
4，最后调用Cursor对象的close()方法关闭游标。

//简单运用，核心代码btn_query.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();// 查询Book表中所有的数据Cursor cursor = db.query("Book", null, null, null, null, null, null);if (cursor.moveToFirst()) {do {// 遍历Cursor对象，取出数据并打印String name = cursor.getString(cursor.getColumnIndex("name"));String author = cursor.getString(cursor.getColumnIndex("author"));int pages = cursor.getInt(cursor.getColumnIndex("pages"));double price = cursor.getDouble(cursor.getColumnIndex("price"));Log.d("MainActivity", "book name is " + name);Log.d("MainActivity", "book author is " + author);Log.d("MainActivity", "book pages is " + pages);Log.d("MainActivity", "book price is " + price);} while (cursor.moveToNext());}cursor.close();}});

四，使用事务

replaceData.setOnClickListener(new OnClickListener() {@Overridepublic void onClick(View v) {SQLiteDatabase db = dbHelper.getWritableDatabase();db.beginTransaction(); // 开启事务try {db.delete("Book", null, null);if (true) {// 在这里手动抛出一个异常，让事务失败。开启事务与事务执行成功之间，如果有异常，就会中断事务。throw new NullPointerException();}ContentValues values = new ContentValues();values.put("name", "Game of Thrones");values.put("author", "George Martin");values.put("pages", 720);values.put("price", 20.85);db.insert("Book", null, values);db.setTransactionSuccessful(); // 事务已经执行成功} catch (Exception e) {e.printStackTrace();} finally {db.endTransaction(); // 结束事务}}});