第六章内容之SQLite数据库存储

SQLite

数据类型

Integer 表示整型，real表示浮点型，text表示文本类型，blob表示二进制类型

使用autoincrement关键字表示id列是自增长的

create table Book（

id integer primary key autoincrement,

author text,

price real,

pages integer,

name text）

点击按钮创建一个数据库，弹吐司提示，MainActivity代码

public class MainActivity extends ActionBarActivity {

    private MyDatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new MyDatabaseHelper(this, "Bookstore.db", null, 1);

        findViewById(R.id.btn_newDatabase).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
            }
        });
    }
}

databaseHelper的代码

ublic class MyDatabaseHelper extends SQLiteOpenHelper {
    private static final String CREATE_BOOK = "create table Book(" +
            "id integer primary key autoincrement," +
            "author text," +
            "price real," +
            "pages integer," +
            "name text)";
    private Context mContext;

    public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        Toast.makeText(mContext, "create successed", Toast.LENGTH_SHORT).show();
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

升级数据库，增加一个表

private static final String CREATE_CATEGORY = "create table Category("+
        "id integer primary key autoincrement,"+
        "category_name text,"+
        "category_code integer)";

db.execSQL(CREATE_CATEGORY);

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("drop table if exists Book");
    db.execSQL("drop table if exists Category");
    onCreate(db);
}

在MainActivity中改版本号

dbHelper = new MyDatabaseHelper(this, "Bookstore.db", null, 2);

CRUD

C  create

R  retrieve 查询

U update

D delete

CRUD的所有代码

public class MainActivity extends ActionBarActivity {

    private MyDatabaseHelper dbHelper;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        dbHelper = new MyDatabaseHelper(this, "Bookstore.db", null, 2);

        findViewById(R.id.btn_newDatabase).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
            }
        });

        findViewById(R.id.btn_insert).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.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.12);
                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.32);
                db.insert("Book", null, values);
            }
        });

        findViewById(R.id.btn_update).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put("price", 14.14);
                db.update("Book", values, "name=?", new String[]{"The Da Vinci Code"});
            }
        });

        findViewById(R.id.btn_delete).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                db.delete("Book", "pager>?", new String[]{"500"});
            }
        });

        findViewById(R.id.btn_query).setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                Cursor cursor = db.query("Book", null, null, null, null, null, null);
                if (cursor.moveToFirst()) {
                    while (cursor.moveToNext()) {
                        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", "name" + name);
                        Log.d("MainActivity", "author" + author);
                        Log.d("MainActivity", "pages" + pages);
                        Log.d("MainActivity", "price" + price);
                    }
                    cursor.close();
                }
            }
        });
    }
}

启用事务，手动异常，事务失败，旧数据还在，去除手动异常代码，替换新数据

findViewById(R.id.btn_replace).setOnClickListener(new View.OnClickListener() {
    @Override//用到事务
    public void onClick(View v) {
        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.beginTransaction();
        try {
            db.delete("Book", null, null);
            if (true) {//手动抛出异常，让事务失败
                throw new NullPointerException();
            }
            ContentValues contentValues = new ContentValues();
            contentValues.put("name", "Game of Thrones");
            contentValues.put("author", "George");
            contentValues.put("pages", 721);
            contentValues.put("price", 20.21);
            db.insert("Book", null, contentValues);
            db.setTransactionSuccessful();//事务执行成功
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            db.endTransaction();//结束事务
        }
    }
});

数据库升级

在onUpgrade()方法中

switch （oldVersion）{

case 1：

 db.execSQL(**)

case 2：

db.execSQL(***)

default

注意每个case没有break，为了保证跨版本升级