android从放弃到坚持放弃第六课(上)_you may include ?s in the where clause, which will-CSDN博客

本文链接：https://blog.csdn.net/JayWRZZ/article/details/70136435

第六章数据存储(上)

第六章数据存储上

持久化技术简介

指将那些内存中的瞬时数据保存到存储设备中，保证即使在手机或电脑关机的情况下，这些数据仍然不会丢失。保存在内存中的数据是处于瞬时状态的,而保存在存储设备中的数据是处于持久状态的，持久化技术提供了一种机制可以让数据在瞬时状态和持久状态间进行转换。

android系统中主要提供三种方式:

文件存储
sharedPreference存储
数据库存储。

当然还可以在SD卡中存储。不过前三者会比较简单，而且更安全一些。

文件存储

最基本的一种数据存储方式。不对存储内容进行任何的格式化处理，所有的数据都是原封不动保存的。因而它适用于一些简单的文本数据或二进制数据。如果你想使用文件存储的方式来保存复杂的文本数据，就需要定义一套自己的格式规范。这样可以方便之后重新解析出来的。

将数据存储到文件中

Context类中提供openFileOutput()方法，可以将数据存储到指定文件中。

public abstract FileOutputStream openFileOutput (String name, int mode)

Open a private file associated with this Context’s application package for writing. Creates the file if it doesn’t already exist.

Parameters

name The name of the file to open; can not contain path separators.
mode Operating mode. Use 0 or MODE_PRIVATE for the default operation, MODE_APPEND to append to an existing file, MODE_WORLD_READABLE and MODE_WORLD_WRITEABLE to control permissions.

Returns

The resulting FileOutputStream.

Throws

FileNotFoundException

See Also

MODE_APPEND
MODE_PRIVATE
MODE_WORLD_READABLE
MODE_WORLD_WRITEABLE
openFileInput(String)
fileList()
deleteFile(String)
FileOutputStream(String)

name	The name of the file to open; can not contain path separators.
mode	Operating mode. Use 0 or `MODE_PRIVATE` for the default operation, `MODE_APPEND` to append to an existing file, `MODE_WORLD_READABLE` and `MODE_WORLD_WRITEABLE` to control permissions.

MODE_PRIVATE是默认的操作模式（指定同样文件名的时候，所写入的内容将会覆盖原文件中的内容）；

MODE_APPEND则表示如果该文件已存在，就往文件里面追加内容，不存在就创建新文件夹。

openFileOutput()方法返回的是一个FileOutputStream对象，得到了这个对象之后就可以使用Java流的方式将数据写入到文件中。以下是一段简单的代码示例：

    public void save(){
        String data = "Data to save";
        FileOutputStream out = null;
        BufferedWriter writer = null;
        try{
            out = openFileOutput("data", Context.MODE_PRIVATE);
            writer = new BufferedWriter(new OutputStreamWriter(out));
            writer.write(data);
        }catch(IOException e){
            e.printStackTrace();
        }finally{
            try{
                if(writer != null){
                    writer.close();
                }
            }catch (IOException e){
                e.printStackTrace();
            }
        }
    }

这里通过openFileOutput()方法得到一个FileOutputStream对象，然后再借助他构建一个OutputStreamWriter对象，再使用OutputStreamWriter构建一个BufferedWriter对象，这样你就可以通过BufferedWriter来将文本内容写入到文件中。

新建project，并修改activity_main.xml中的代码。

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.wrjjrw.broadcasttest.MainActivity">

    <EditText
        android:id="@+id/edit"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="Type sth"/>

</LinearLayout>

修改MainActivity:


public class MainActivity extends AppCompatActivity {

    private EditText edit;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        edit = (EditText) findViewById(R.id.edit);
    }

    @Override
    protected void onDestroy(){
        super.onDestroy();
        String inputText = edit.getText().toString();
        save(inputText);
    }

    public void save(String inputText){
        FileOutputStream out = null;
        BufferedWriter writer = null;
        try{
            out = openFileOutput("data", Context.MODE_PRIVATE);
            writer = new BufferedWriter(new OutputStreamWriter(out));
            writer.write(inputText);
        }catch(IOException e){
            e.printStackTrace();
        }finally{
            try{
                if(writer != null){
                    writer.close();
                }
            }catch (IOException e){
                e.printStackTrace();
            }
        }
    }
}

很显然，在销毁前保留EditText中的数据。跑路，在Tools工具中找到Android,打开Android Device Monitor工具，然后进入了File Explorer标签页，在这里找到/data/data/com.example….就可以看到了那个文件了。右上角有导入导出按钮，导出就可以打开了。

恢复。

    public String load(){
        FileInputStream in = null;
        BufferedReader reader = null;
        StringBuilder content  = new StringBuilder();
        try{
            in = openFileInput("data");
            reader = new BufferedReader(new InputStreamReader(in));
            String line = "";
            while((line = reader.readLine()) != null){
                content.append(line);
            }
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return content.toString();
    }

先通过openFileInput()方法获取到了一个FileInputStream对象，然后借助它又构建出了一个InputStreamReader对象，接着再使用InputStreamReader构建出一个BufferedReader对象，这样我们就可以通过BufferedReader进行一行行地读取，把文件中所有文本内容全部读取出来，并存放在一个StringBuilder对象中，最后将读取的内容返回就可以了。

修改：

public class MainActivity extends AppCompatActivity {

    private EditText edit;

    @Override
    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        edit = (EditText) findViewById(R.id.edit);
        String inputText = load();
        if( !TextUtils.isEmpty(inputText)){
            edit.setText(inputText);
            edit.setSelection(inputText.length());
            Toast.makeText(this, "Restoring succeeded", Toast.LENGTH_LONG).show();
        }
    }

    @Override
    protected void onDestroy(){
        super.onDestroy();
        String inputText = edit.getText().toString();
        save(inputText);
    }

    public void save(String inputText){
        FileOutputStream out = null;
        BufferedWriter writer = null;
        try{
            out = openFileOutput("data", Context.MODE_PRIVATE);
            writer = new BufferedWriter(new OutputStreamWriter(out));
            writer.write(inputText);
        }catch(IOException e){
            e.printStackTrace();
        }finally{
            try{
                if(writer != null){
                    writer.close();
                }
            }catch (IOException e){
                e.printStackTrace();
            }
        }
    }

    public String load(){
        FileInputStream in = null;
        BufferedReader reader = null;
        StringBuilder content  = new StringBuilder();
        try{
            in = openFileInput("data");
            reader = new BufferedReader(new InputStreamReader(in));
            String line = "";
            while((line = reader.readLine()) != null){
                content.append(line);
            }
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            if (reader != null) {
                try {
                    reader.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return content.toString();
    }
}

setSelection()方法是移动光标。跑路，显而易见。

SharedPreferences 存储

使用键值对的方式存储数据，也就是说，当保存一条数据的时候，需要提供一个对应的键，这样在读取数据的时候就可以使用这个键把相应的值取出来。而且SharedPreferences支持多种不同的数据类型存储。

存储

要想获取SharedPreferences来存储数据，首先要获取到SharedPreferences对象。有以下三种方式：

context类中的getSharedPreferences()方法

此方法接受两个参数。

public abstract SharedPreferences getSharedPreferences (String name, int mode)

Retrieve and hold the contents of the preferences file ‘name’, returning a SharedPreferences through which you can retrieve and modify its values. Only one instance of the SharedPreferences object is returned to any callers for the same name, meaning they will see each other’s edits as soon as they are made.

Parameters

name Desired preferences file. If a preferences file by this name does not exist, it will be created when you retrieve an editor (SharedPreferences.edit()) and then commit changes (Editor.commit()).
mode Operating mode. Use 0 or MODE_PRIVATE for the default operation, MODE_WORLD_READABLE and MODE_WORLD_WRITEABLE to control permissions. The bitMODE_MULTI_PROCESS can also be used if multiple processes are mutating the same SharedPreferences file. MODE_MULTI_PROCESS is always on in apps targetting Gingerbread (Android 2.3) and below, and off by default in later versions.

Returns

The single SharedPreferences instance that can be used to retrieve and modify the preference values.

See Also

MODE_PRIVATE
MODE_WORLD_READABLE
MODE_WORLD_WRITEABLE
MODE_MULTI_PROCESS

文件一般都存放在/data/data//shared_prefs/目录。而且只有MODE_PRIVATE一种模式可以选择。

Activity类中的getPreferences()方法。

name	Desired preferences file. If a preferences file by this name does not exist, it will be created when you retrieve an editor (SharedPreferences.edit()) and then commit changes (Editor.commit()).
mode	Operating mode. Use 0 or `MODE_PRIVATE` for the default operation, `MODE_WORLD_READABLE` and `MODE_WORLD_WRITEABLE` to control permissions. The bit`MODE_MULTI_PROCESS` can also be used if multiple processes are mutating the same SharedPreferences file. `MODE_MULTI_PROCESS` is always on in apps targetting Gingerbread (Android 2.3) and below, and off by default in later versions.

public SharedPreferences getPreferences (int mode)

Retrieve a SharedPreferences object for accessing preferences that are private to this activity. This simply calls the underlying getSharedPreferences(String, int)method by passing in this activity’s class name as the preferences name.

Parameters

mode Operating mode. Use MODE_PRIVATE for the default operation, MODE_WORLD_READABLE and MODE_WORLD_WRITEABLE to control permissions.

Returns

Returns the single SharedPreferences instance that can be used to retrieve and modify the preference values.

和上面的一个方法类似，只不过只接受一种模式。使用这个方式时会自动将当前活动的类名作为文件名。

PreferenceManager类中的getDefaultSharedPerferences()方法

public static SharedPreferences getDefaultSharedPreferences (Context context)

Gets a SharedPreferences instance that points to the default file that is used by the preference framework in the given context.

Parameters

context The context of the preferences whose values are wanted.

Returns

A SharedPreferences instance that can be used to retrieve and listen to values of the preferences.

静态方法，接受一个Context参数。并自动使用当前应用程序的包名做前缀命名SharedPreferences文件.然后就开始存储数据，分三步骤：

调用SharedPreferences对象的edit()方法来获取一个对象。
向SharedPreferences.Editor对象中添加数据，比如添加一个布尔就使用putBoolean()方法。。。
调用apply()方法将添加的数据提交，从而完成数据存储操作。

栗子：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.wrjjrw.broadcasttest.MainActivity">

    <Button
        android:id="@+id/save_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Save data"
        android:textAllCaps="false"
        />
</LinearLayout>

修改MainActivity:

public class MainActivity extends AppCompatActivity {

    private EditText edit;

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

        Button saveData = (Button) findViewById(R.id.save_data);
        saveData.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v){
                SharedPreferences.Editor editor =
                        getSharedPreferences("data2", MODE_PRIVATE).edit();
                editor.putString("name", "huchi");
                editor.putInt("age", 18);
                editor.putBoolean("married", false);
                editor.apply();
            }
        });
    }
}

通过getSharedPreferences()方法指定SharedPreferences的文件名为data，并得到了SharedPreferences.Editor对象，接着向这个对象中添加了3条不同的数据，最后调用apply()方法。

跑路，自己看数据。

读取

修改

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.wrjjrw.broadcasttest.MainActivity">

    <Button
        android:id="@+id/save_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Save data"
        android:textAllCaps="false"
        />

    <Button
        android:id="@+id/restore_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" 
        android:text="Restore data"
        android:textAllCaps="false"
        />

</LinearLayout>

修改MainActivity:

public class MainActivity extends AppCompatActivity {

    private EditText edit;

    private static final String Tag = "MainActivity";

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

        Button saveData = (Button) findViewById(R.id.save_data);
        saveData.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v){
                SharedPreferences.Editor editor =
                        getSharedPreferences("data2", MODE_PRIVATE).edit();
                editor.putString("name", "huchi");
                editor.putInt("age", 18);
                editor.putBoolean("married", false);
                editor.apply();
            }
        });

        Button restoreData = (Button) findViewById(R.id.restore_data);
        restoreData.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v) {
                SharedPreferences pref = getSharedPreferences("data2", MODE_PRIVATE);
                String name = pref.getString("name","");
                int age = pref.getInt("age", 0);
                boolean married = pref.getBoolean("married", false);
                Log.d(Tag, "name is" + name);
                Log.d(Tag, "age is" + age);
                Log.d(Tag, "married is" + married);
                Toast.makeText(MainActivity.this ,"name is " + name + ",age is " + age +
                        ((married == true)?"and get married":"and is a single dog"),Toast.LENGTH_LONG).show();
            }
        });
    }
}

SharedPreferences存储比文本存储简单多了，应用场景也多。

实战：记住密码

打开上次BroadcastBestPractise项目：

修改login界面的xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_login"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.wrjjrw.broadcastbestpractise.LoginActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="60dp"
        android:orientation="horizontal"
        >
        <TextView
            android:layout_width="90dp"
            android:layout_height="match_parent"
            android:layout_gravity="center"
            android:layout_margin="10dp"
            android:textSize="18sp"
            android:inputType="number"
            android:text="     账号:"
            />
        <EditText
            android:id="@+id/account"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:layout_gravity="center_vertical"
            />
    </LinearLayout>
    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="60dp"
        android:orientation="horizontal"
        >
        <TextView
            android:layout_width="90dp"
            android:layout_height="match_parent"
            android:layout_gravity="center"
            android:layout_margin="10dp"
            android:textSize="18sp"
            android:text="     密码："
            />
        <EditText
            android:id="@+id/passord"
            android:layout_width="0dp"
            android:layout_height="match_parent"
            android:layout_weight="1"
            android:inputType="textPassword"
            />


    </LinearLayout>
        <LinearLayout
            android:orientation="horizontal"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:gravity="center"
            >

            <CheckBox
                android:id="@+id/remember_pass"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                />
            <TextView
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="记住密码"
                />
    </LinearLayout>
    <Button
        android:id="@+id/login"
        android:layout_width="match_parent"
        android:layout_height="80dp"
        android:layout_margin="30dp"
        android:layout_gravity="center"
        android:text="登陆"
        android:textSize="40dp"
        android:background="@mipmap/ic_launcher"
        />

    <ImageView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:src="@drawable/huchi"
        />

</LinearLayout>

这里用到了一个复选框控件CheckBox。

修改Activity:

public class LoginActivity extends BaseActivity {

    private EditText accountEdit;

    private EditText passwordEdit;

    private Button login;

    private SharedPreferences pref;

    private SharedPreferences.Editor editor;

    private CheckBox remember_pass;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_login);
        accountEdit = (EditText) findViewById(R.id.account);
        passwordEdit = (EditText) findViewById(R.id.passord);

        pref = PreferenceManager.getDefaultSharedPreferences(this);
        remember_pass = (CheckBox) findViewById(R.id.remember_pass);
        boolean isRemember = pref.getBoolean("remember_password",false);

        if(isRemember){
            String account = pref.getString("account","");
            String password = pref.getString("password","");
            accountEdit.setText(account);
            passwordEdit.setText(password);
            remember_pass.setChecked(true);
        }

        login = (Button) findViewById(R.id.login);
        login.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v) {
                String account = accountEdit.getText().toString();
                String password = passwordEdit.getText().toString();

                if(account.length() > 0 && password.length() > 0){
                    editor = pref.edit();
                    if(remember_pass.isChecked()){
                        editor.putString("account",account);
                        editor.putString("password",password);
                        editor.putBoolean("remember_password",true);
                    }else{
                        editor.clear();
                    }
                    editor.apply();
                    Intent intent = new Intent(LoginActivity.this, MainActivity.class);
                    startActivity(intent);
                    finish();
                }else{
                    Toast.makeText(LoginActivity.this, "account or password is invalid"
                            , Toast.LENGTH_LONG).show();
                }
            }
        });
    }
}

跑路。。。。

这只是一个示例，并不能这样使用，因为将密码以明文的形式存储在SharedPreferences文件中不安全，容易被盗取，需要结合一定的加密算法。

SQLite数据库存储

SQLite是一款轻量级的关系型数据库，运算速度非常快，占用资源少。不仅支持SQL语法，还遵循数据库ACID事务，所以只要你以前使用过其他数据库就可以快速上手了，它甚至不用设置用户名和密码就可以使用了。

前面学的文件存储和SharedPreferences存储毕竟只适用保存一些简单的数据和键值对，当存储复杂的关系型数据时，就需要SQLite数据库。比如手机短信程序中很多的会话，会话中很多的信息内容。

创建数据库

通过SQLiteOpenHelper帮助类，借助这个类就可以非常简单的对数据库进行创建和升级。

SQLiteOpenHelper是一个抽象类，这意味着我们想要使用它，就需要创建一个自己的帮助类去继承它。SQLiteOpenHelper中有两个抽象方法。分别是onCreate(),onUpgrade()，必须在自己的帮助类里面重写这两个方法。然后分别在这两个方法中去实现创建和升级数据库的逻辑。

SQLiteOpenHelper中有两个非常重要的实例方法：getReadableDatabase()和getWritableDatabase()。这两个方法都可以创建或打开一个现有的数据库，并返回一个可对数据库进行读写操作的对象。不同的是当数据库不可写入时(如磁盘空间已满),getReadableDatabase()方法返回的对象将以只读的方式去打开数据库，而getWritableDatabase()方法则将抛出异常。

SQLiteOpenHelper中有两个构造方法可供重写，一般使用参数少一点的那个构造方法即可。这个构造方法有四个参数。第二个是数据库名，第三个是允许我们在查询数据的时候返回一个自定义的Cursor,一般传入null,第四个参数是版本号，可用于对数据库进行升级操作。构建实例后，就可以调用他的getReadableDatabase()或getWritableDatabase()方法就能够创建数据库了。数据库文件会被放置在/data/data//databases/目录下。此时重写的onCreate()也会得到执行，所以通常会在这里处理一些创建表的逻辑。

Public constructors
SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)Create a helper object to create, open, and/or manage a database.

Public constructors
`SQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version)`Create a helper object to create, open, and/or manage a database.

Click me,我的mysql扫盲笔记

新建一个MyDatabaseHelper类继承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 mContent;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        Toast.makeText(mContent, "Create succeeded", Toast.LENGTH_LONG).show();
    }

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

    }
}

Tips:后期发现使用auto_increment会错误。。跟mysql还是有点区别的。。。。

可以发现把建表的语句定义成了一个字符串常量，然后在onCreate()方法中调用了SQLiteDatabase的execSQL()方法去执行这条建表语句。

修改activity_main.xml：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.wrjjrw.broadcastbestpractise.MainActivity">

    <Button
        android:id="@+id/create_base"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Creat database"
        />

</LinearLayout>

修改MainActivity：

public class MainActivity extends BaseActivity {

    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);
        Button createDatabase = (Button) findViewById(R.id.create_base);
        createDatabase.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
            }
        });
    }
}

成功构建一个数据库Bookstore.db。

跑路，我们发现点一下会显示Create succeeded，但是之后再点就没用了。是因为Bookstore.db已经存在。因此不会再创建。数据库和表已经创建成功了。现在打开ADM可以检查一下，是否如上所说在那个文件夹里面。

这次还可以使用adb shell进行查看，adb是Android SDK中自带的一个调试工具，使用这个工具可以直接对手机或者模拟器进行调试操作，他存放在sdk的platform-tools下，如果想要在命令行中使用这个工具，则需要添加环境变量。(此处略)

打开cmd, 输入adb shell ;然后cd data一直打开到我们所在的那个地方(可以用ls)，最后用sqlite3 Bookstore.db打开数据库。.table查看有哪些表。此时有两张表，一张是Book,就是我们创建的，还有一个是android_metadata表是自动生成的。.exit或者.quit退出数据库编辑，`exit退出设备控制台。

升级数据库

onUpgrade()方法是用户对数据库进行升级的。但现在还是个空方法。它在整个数据库的管理中起着非常重要的作用。

比如说我们现在要添加一张Category表用于记录图书的分类。

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 mContent;

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_BOOK);
        db.execSQL(CREATE_CATEGORY);
        Toast.makeText(mContent, "Create succeeded", Toast.LENGTH_LONG).show();
    }

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

    }
}

重新运行，你会发现，竟然没有新建。。。。成功。。。

当然是因为Bookstore.db数据库已经存在了。。

解决方法也很简单，只要把原先的数据库删掉就可以了。。只不过比较极端。

可以用onUpgrade()方法。

    @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);
    }

可以看到我们将可能存在的Book和Category表删掉了，然后重新执行了一遍onCreate()方法。

接下来的问题既是如何让onUpgrade()方法执行。

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

        dbHelper = new MyDatabaseHelper(this, "Bookstore.db", null, 2);
        Button createDatabase = (Button) findViewById(R.id.create_base);
        createDatabase.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v) {
                dbHelper.getWritableDatabase();
            }
        });
     }

只要将数据库的版本号改成一个比原来大的数就可以了。

让我们重新打开adb查看一下Bookstore.db数据库，键入.table命令。接着键入.schema查看建表语句。

添加数据

数据操作主要有四种CRUD。其中C代表是Create()，其他依次：查询Retrieve()，Update()，Delete().在android中即使不用SQL语句也能轻松完成所有的CRUD操作。

根据getReadableDatabase()返回的SQLiteDatabase对象，借助这个对象就可以对数据进行CRUD操作。

修改activity_main.xml

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.wrjjrw.broadcastbestpractise.MainActivity">

    <Button
        android:id="@+id/create_base"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Creat database"
        />

    <Button
        android:id="@+id/add_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" 
        android:text="Add data"
        android:textAllCaps="false"
        />

</LinearLayout>

在MainActivity添加:

        Button addData = (Button) findViewById(R.id.add_data);
        addData.setOnClickListener(new View.OnClickListener(){
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                ContentValues values = new ContentValues();
                //first data
                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);//insert first data
                values.clear();
                values.put("name", "The Lost Symbol");
                values.put("author", "Dan Brown");
                values.put("pages", 510);
                db.insert("Book", null, values);//insert second data
            }
        });

第二个参数用于给未指定添加的数据的情况下给某些可为空的列自动赋值，一般不用，直接null。

首先获取了SQLiteDatabase对象，然后使用ContentValues来对要添加的数据进行组装。我们只添加了期中的四组顺序，还有一组因为是primary key所以是自动生成的。

可以打开Bookstore.db数据库select * from Book查看。

更新数据

可以使用update()方法

update
int update (String table, 
                ContentValues values, 
                String whereClause, 
                String[] whereArgs)
Convenience method for updating rows in the database.

Parameters
table String: the table to update in
values ContentValues: a map from column names to new column values. null is a valid value that will be translated to NULL.
whereClause String: the optional WHERE clause to apply when updating. Passing null will update all rows.
whereArgs String: You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Returns
int the number of rows affected

第三第四个参数用语更新某一行或某几行的数据，不指定默认所有。

Parameters
`table`	`String`: the table to update in
`values`	`ContentValues`: a map from column names to new column values. null is a valid value that will be translated to NULL.
`whereClause`	`String`: the optional WHERE clause to apply when updating. Passing null will update all rows.
`whereArgs`	`String`: You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Returns
`int`	the number of rows affected

修改MainActivity：

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context="com.example.wrjjrw.broadcastbestpractise.MainActivity">

    <Button
        android:id="@+id/create_base"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Creat database"
        />

    <Button
        android:id="@+id/add_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Add data"
        android:textAllCaps="false"
        />

    <Button
        android:id="@+id/update_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" 
        android:text="Update data"
        android:textAllCaps="false"
        />

</LinearLayout>

修改MainActivity:

        Button updateData = (Button) findViewById(R.id.update_data);
        updateData.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 Da Vinci Code"});
            }
        });

我们指向把价格这一列的数据更新成10.99。然后调用了update()方法执行具体的更新操作，可以看到，第三第四个参数用来指定更新哪一行。？是占位符，可以通过第四个参数提供的字符串数组为第三个参数重点每一个占位符指定相应的内容。所以只是将名字是“The Da Vinci Code”这本书的价格改成10.99。update后查看数据。

删除数据

删除当然用delete();

delete
int delete (String table, 
               String whereClause, 
               String[] whereArgs)
Convenience method for deleting rows in the database.

Parameters
table String: the table to delete from
whereClause String: the optional WHERE clause to apply when deleting. Passing null will delete all rows.
whereArgs String: You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Returns
int the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass “1” as the whereClause.

Parameters
`table`	`String`: the table to delete from
`whereClause`	`String`: the optional WHERE clause to apply when deleting. Passing null will delete all rows.
`whereArgs`	`String`: You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.

Returns
`int`	the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass “1” as the whereClause.

给activity_main.xml增添

    <Button
        android:id="@+id/delete_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Delete data"
        android:textAllCaps="false"
        />

修改MainActivity：

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

自己运行自己看看。

查询数据

SQL全称既然是Structured Query Language，翻译成中文就是结构化查询语言。而且查询数据也是CRUD中最复杂的一种操作。query()方法可以查看数据。

query
Cursor query (String table, 
                String[] columns, 
                String selection, 
                String[] selectionArgs, 
                String groupBy, 
                String having, 
                String orderBy)
Query the given table, returning a Cursor over the result set.

Parameters
table String: The table name to compile the query against.
columns String: A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn’t going to be used.
selection String: A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
selectionArgs String: You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
groupBy String: A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
having String: A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
orderBy String: How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.

Returns
Cursor A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.

See also:

Cursor

来看看最短的也有7个参数。

Parameters
`table`	`String`: The table name to compile the query against.
`columns`	`String`: A list of which columns to return. Passing null will return all columns, which is discouraged to prevent reading data from storage that isn’t going to be used.
`selection`	`String`: A filter declaring which rows to return, formatted as an SQL WHERE clause (excluding the WHERE itself). Passing null will return all rows for the given table.
`selectionArgs`	`String`: You may include ?s in selection, which will be replaced by the values from selectionArgs, in order that they appear in the selection. The values will be bound as Strings.
`groupBy`	`String`: A filter declaring how to group rows, formatted as an SQL GROUP BY clause (excluding the GROUP BY itself). Passing null will cause the rows to not be grouped.
`having`	`String`: A filter declare which row groups to include in the cursor, if row grouping is being used, formatted as an SQL HAVING clause (excluding the HAVING itself). Passing null will cause all row groups to be included, and is required when row grouping is not being used.
`orderBy`	`String`: How to order the rows, formatted as an SQL ORDER BY clause (excluding the ORDER BY itself). Passing null will use the default sort order, which may be unordered.

Returns
`Cursor`	A `Cursor` object, which is positioned before the first entry. Note that `Cursor`s are not synchronized, see the documentation for more details.

    <Button
        android:id="@+id/query_data"
        android:layout_width="match_parent"
        android:layout_height="wrap_content" 
        android:text="Query data"
        android:textAllCaps="false"
        />

Button queryButton = (Button) findViewById(R.id.query_data);
        queryButton.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                SQLiteDatabase db = dbHelper.getWritableDatabase();
                //query all data
                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(Tag, "book name is "+name);
                        Log.d(Tag, "book author is "+author);
                        Log.d(Tag, "book pages is "+pages);
                        Log.d(Tag, "book price is "+ price);
                        Toast.makeText(MainActivity.this, "book name is "+name+" and author is "+ author+
                            " and pages is " + pages + " price is "+ price, Toast.LENGTH_LONG
                        ).show();
                    }while (cursor.moveToNext());
                }
                cursor.close();
            }
        });

使用SQL操作数据

还可以用一下语句。

添加：

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

更新

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);

rawQuery
Cursor rawQuery (String sql, 
                String[] selectionArgs, 
                CancellationSignal cancellationSignal)
Runs the provided SQL and returns a Cursor over the result set.

Parameters
sql String: the SQL query. The SQL string must not be ; terminated
selectionArgs String: You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.
cancellationSignal CancellationSignal: A signal to cancel the operation in progress, or null if none. If the operation is canceled, then OperationCanceledException will be thrown when the query is executed.

Returns
Cursor A Cursor object, which is positioned before the first entry. Note that Cursors are not synchronized, see the documentation for more details.

Parameters
`sql`	`String`: the SQL query. The SQL string must not be ; terminated
`selectionArgs`	`String`: You may include ?s in where clause in the query, which will be replaced by the values from selectionArgs. The values will be bound as Strings.
`cancellationSignal`	`CancellationSignal`: A signal to cancel the operation in progress, or null if none. If the operation is canceled, then `OperationCanceledException` will be thrown when the query is executed.

Returns
`Cursor`	A `Cursor` object, which is positioned before the first entry. Note that `Cursor`s are not synchronized, see the documentation for more details.

感觉这个蛮方便的，就贴出来了。