SQLite数据库存储

①创建数据库
新建Java文件继承SQLiteOpenHelper

public class MyDatabaseHelper extends SQLiteOpenHelper {

    public  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);
        Util.showToast(mContext,"Created Succeeded");
    }

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

    }
}

将建表语句定义成一个字符串常量，然后在onCreate()方法中调用了SQLiteDatabase的execSQL()方法去执行这条建表语句，保证数据库与Book表都能创建成功
②创建名为Data的Activity

package com.example.administrator.myapplication;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.View;
import android.widget.Button;

public class Data extends AppCompatActivity {

    private  MyDatabaseHelper dbHelper;
    @Override
    protected void onCreate(Bundle savedInstanceState){
        super.onCreate(savedInstanceState);
        setContentView(R.layout.data_activity);
        dbHelper = new MyDatabaseHelper(this,"BookStore.db",null,1);
        Button createDatabase = (Button)findViewById(R.id.create_database);
        Button selectDatabase = (Button)findViewById(R.id.select_database);
        Button addDatabase = (Button)findViewById(R.id.add_database);
        Button deleteDatabase = (Button)findViewById(R.id.delete_database);
        Button updateDatabases = (Button)findViewById(R.id.update_database);
        createDatabase.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
            }
        });
        addDatabase.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",90.09);
                db.insert("Book",null,values);
                values.clear();
                values.put("name","The Last");
                values.put("author","Willion");
                values.put("pages",1);
                values.put("price",9.87);
                db.insert("Book",null,values);
            }
        });
        updateDatabases.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                values.put("price",10.99);
                db.update("Book",values,"name = ?",new String[]{
                        "The DaVinci Code"
                });
            }
        });
        deleteDatabase.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                db.delete("Book","pages > ?",new String[]{"500"});
            }
        });
        selectDatabase.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()) {
                    do {
                        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("Data", name);
                        Log.d("Data", author);
                        Log.d("Data", "book pages is " +pages);
                        Log.d("Data", "book price is " +price);
                    } while (cursor.moveToNext());
                }
                cursor.close();
            }
        });
    }
}

③添加数据
在添加数据的按钮点击事件中，获取SQLiteDatabase对象，然后使用ContentValues来对要添加的数据进行组装，调用insert()方法将数据添加到表中

④更新数据
构建ContentValues对象，并且指定一组数据。调用SQLiteDatabase的update()执行更新操作。使用第三、第四个参数指定具体更新哪几行。第三个参数对应SQL的where部分，表示更新所有name=？的行，而？是一个占位符，可以通过第四个参数提供的一个字符串数组为第三个参数中的每个占位符指定相应内容。
⑤删除数据
调用delete方法，同上
⑥查询数据
查询主要使用query()方法，query()方法参数如下表

query()方法参数	对应SQL部分	描述
table	from table_name	指定查询的表名
columns	select column1, column2	指定查询的列名
selection	where column = value	指定where的约束条件
selectionArgs	-	为where中的占位符提供具体的值
groupBy	group by column	指定需要group by的列
having	having column = value	对group by后的结果进一步约束
orderBy	order by column1, column2	指定查询结果的排序方式

首先在查询按钮的点击事件中调用SQLiteDatabase的query()方法查询数据，在本例中只传入第一个参数查询Book表中所有数据。然后得到一个Cursor对象，接着调用moveToFirst()方法将数据的指针移动到第一行的位置，然后进入循坏，遍历查询到的每一行数据。在这个循坏中可以通过Cursor的getColumnIndex()方法获取到某一列在表中对应的位置索引，然后将这个索引传入到相应的取值方法中。

⑦直接使用SQL来完成前面的操作
添加数据的方法如下：

db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
			new String[] { "The Da Vinci Code", "Dan Brown", "454", "16.96" });
db.execSQL("insert into Book (name, author, pages, price) values(?, ?, ?, ?)",
			new String[] { "The Lost Symbol", "Dan Brown", "510", "19.95" });

更新数据的方法如下：

db.execSQL("update Book set price = ? where name = ?", new String[] { "10.99", "The Da Vinci Code" });

删除数据的方法如下：

db.execSQL("delete from Book where pages > ?", new String[] { "500" });

查询数据的方法如下：

db.rawQuery("select * from Book", null);

⑧升级数据库操作

public class MyDatabaseHelper extends SQLiteOpenHelper {

	public static final String CREATE_BOOK = "create table Book ("
			+ "id integer primary key autoincrement, " 
			+ "author text, "
			+ "price real, " 
			+ "pages integer, " 
			+ "name text)";
	
	public static final String CREATE_CATEGORY = "create table Category ("
			+ "id integer primary key autoincrement, "
			+ "category_name text, "
			+ "category_code integer)";

	private Context mContext;

	public MyDatabaseHelper(Context context, String name,
			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 succeeded", 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);
	}

}

我们在onUpgrade()方法中执行了两条DROP语句，如果发现数据库中已经存在Book表或Category表了，就将这两张表删除掉，然后再调用onCreate()方法去重新创建。这里先将已经存在的表删除掉，是因为如果在创建表时发现这张表已经存在了，就会直接报错。为了让onUpgrade()方法能够执行了，还记得SQLiteOpenHelper的构造方法中第四个参数传入一个比1大的数，更新版本号，就可以让onUpgrade()方法得到执行了