Android数据存储之Sqlite

1、SQLiteOpenHelper抽象类

---

首先创建一个类继承SQliteOpenHelper；代码如下：

public class MyDatabaseHelper extends SQLiteOpenHelper {

    //创建一张表Book
    public static final String CREATE_BOOK = "create table Book("
            + "id integer primary key autoincrement,"
            + "author text,"
            + "price real,"
            + "pages integer,"
            + "name text)";
       //创建一张表Category
    public static final String CREATE_CATEGORY = "create table Category(" +
            "id integer primary key autoincrement," +
            "category_name text," +
            "category_code integer)";
    private Context mContext;

    //该构造函数第一个参数表示上下文，第二个是数据库名，第三个是个自定义的Cursor，默认为null，第四个参数是版本号
    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);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContext, "Create successed", Toast.LENGTH_SHORT).show();
    }

    //版本号的改变会导致该方法执行
    @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);*/
        switch (oldVersion){//这是升级数据库的最佳写法
            case 1:
                db.execSQL(CREATE_CATEGORY);
            case 2:
                db.execSQL("alter table Book add column category_id integer");
        }
    }
}

2、在Activity中实现对数据库的操作

public class Main2Activity extends AppCompatActivity implements View.OnClickListener {

    private MyDatabaseHelper dbHelper;
    private Button create_table;
    private Button c, r, u, d;
    private Button commit;
    SQLiteDatabase db;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main2);
        create_table = (Button) findViewById(R.id.create_table);
        c= (Button) findViewById(R.id.c);
        r= (Button) findViewById(R.id.r);
        u= (Button) findViewById(R.id.u);
        commit= (Button) findViewById(R.id.commit);
        commit.setOnClickListener(this);
        u.setOnClickListener(this);
        r.setOnClickListener(this);
        c.setOnClickListener(this);
        d= (Button) findViewById(R.id.d);
        d.setOnClickListener(this);
        create_table.setOnClickListener(this);
    }

    @Override
    protected void onStart() {
        super.onStart();
        dbHelper = new MyDatabaseHelper(this, "BookStore.db", null, 1);
        db= dbHelper.getWritableDatabase();
    }

    private void createTable() {
        dbHelper = new MyDatabaseHelper(this, "BookStore.db", null, 1);
        db= dbHelper.getWritableDatabase();//当数据库不存在时候，执行MyDatabasesHelper的onCreate（）方法来创建数据库；
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.create_table:
                createTable();
                break;
            case R.id.c:
                create();
                break;
            case R.id.r:
                retrieve();
                break;
            case R.id.u:
                update();
                break;
            case R.id.d:
                delete();
                break;
            case R.id.commit:
                commit();
                break;
        }

    }


    //给数据库中添加数据
    private void create() {
        ContentValues values = new ContentValues();
        values.put("name", "The Da Vinci Code");
        values.put("author", "Dan Brown");
        values.put("pages", 454);
        values.put("price", 16.55);
        db.insert("Book",null,values);

        values.clear();
        values.put("name", "The Da Lost Symbol");
        values.put("author", "Dan Brown");
        values.put("pages", 510);
        values.put("price", 20.55);
        db.insert("Book",null,values);


    }


    //修改数据
    private void update(){
        ContentValues values=new ContentValues();
        values.put("price",10.88);
        db.update("Book",values,"name=?",new String[]{"The Da Vinci Code"});
        Toast.makeText(getApplicationContext(), "更新成功！", Toast.LENGTH_SHORT).show();
    }
    //删除数据
    private void delete(){
        db.delete("Book","pages>?",new String[]{"500"});
    }

//查询所有数据
    private void retrieve(){
        Cursor cursor=db.query("Book",null,null,null,null,null,null);
        if(cursor.moveToFirst()){
            do{
              String name =cursor.getString(cursor.getColumnIndex("name"));
                String author =cursor.getString(cursor.getColumnIndex("author"));
                String pages =cursor.getString(cursor.getColumnIndex("pages"));
                String price =cursor.getString(cursor.getColumnIndex("price"));

                Log.d("info",name+author+pages+price);

            }while(cursor.moveToNext());
        }
        cursor.close();
    }
    //运用事务。
    private void commit(){
        Toast.makeText(getApplicationContext(), "提交成功", Toast.LENGTH_SHORT).show();
        db.beginTransaction();
        try {
            db.execSQL("delete from Book ");
           /* if (true) {
                throw new NullPointerException();
            }*/
            ContentValues values = new ContentValues();
            values.put("name", "The Da Lost Symbol");
            values.put("author", "Dan Brown");
            values.put("pages", 510);
            values.put("price", 20.55);
            db.insert("Book", null, values);
            db.setTransactionSuccessful();
        }catch(Exception e){
            e.printStackTrace();
        }finally {
            db.endTransaction();
        }
    }
}