【攻克Android (19)】SQLite数据库

本文围绕以下两个部分展开：

一、SQLite数据库
二、案例：SQLite数据库实现增删改查
        附   代码补充





一、SQLite数据库


二、案例：SQLite数据库实现增删改查

        1. strings.xml。定义所需字符串。

<resources>
  <string name="app_name">DB</string>

  <string name="action_insert">C</string>
  <string name="action_read">R</string>
  <string name="action_update">U</string>
  <string name="action_delete">D</string>

  <string name="hint_id">编号</string>
  <string name="hint_name">部门名</string>
  <string name="hint_loc">地点</string>

</resources>

        2. activity_main.xml。写主界面。

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:paddingBottom="@dimen/activity_vertical_margin"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    tools:context=".MainActivity">

    <EditText
        android:id="@+id/txtId"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="@string/hint_id"
        android:inputType="number" />

    <EditText
        android:id="@+id/txtName"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/txtId"
        android:hint="@string/hint_name" />

    <EditText
        android:id="@+id/txtLoc"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@+id/txtName"
        android:hint="@string/hint_loc" />

    <TextView
        android:id="@+id/tvResult"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@+id/txtLoc" />

</RelativeLayout>

        3. menu_main.xml。写菜单。

<menu xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    tools:context=".MainActivity">
    <item
        android:id="@+id/action_insert"
        android:orderInCategory="100"
        android:showAsAction="always"
        android:title="@string/action_insert" />

    <item
        android:id="@+id/action_read"
        android:orderInCategory="101"
        android:showAsAction="always"
        android:title="@string/action_read" />

    <item
        android:id="@+id/action_update"
        android:orderInCategory="102"
        android:showAsAction="always"
        android:title="@string/action_update" />

    <item
        android:id="@+id/action_delete"
        android:orderInCategory="103"
        android:showAsAction="always"
        android:title="@string/action_delete" />
</menu>

        4. MainActivity。声明控件、初始化控件。

private EditText txtId;
    private EditText txtName;
    private EditText txtLoc;
    private TextView tvResult;

        txtId = (EditText) findViewById(R.id.txtId);
        txtName = (EditText) findViewById(R.id.txtName);
        txtLoc = (EditText) findViewById(R.id.txtLoc);
        tvResult = (TextView) findViewById(R.id.tvResult);

        5. MainActivity。写菜单被选中后的事件。

@Override
    public boolean onOptionsItemSelected(MenuItem item) {
        switch (item.getItemId()) {
            case R.id.action_insert:
                // 新增

                break;
            case R.id.action_read:
                // 查询

                break;
            case R.id.action_update:
                // 更新

                break;
            case R.id.action_delete:
                // 删除

                break;
        }

        return super.onOptionsItemSelected(item);
    }

        6. 在 java 下面的包名下，创建 数据库帮助类：DBOpenHelper，继承于 SQLiteOpenHelper。（继承之后，需要重写父类的两个方法：onCreate()和onUpgrade()，并写出构造方法。）

    /**
     * 在数据库第一次建立时被调用， 一般用来创建数据库中的表，
     * 并做适当的初始化工作
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    /**
     * 在数据库需要升级时被调用， 一般用来删除旧的数据库表，
     * 并将数据转移到新版本的数据库表中
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

        创建的构造方法如下：

    public DBOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        // 参数1：context 上下文
        // 参数2：name 数据库名 （对于一个项目，数据库名是固定的。）
        // 参数3：CursorFactory 指定在执行查询时获得一个游标实例的工厂类,
        //       设置为null,代表使用系统默认的工厂类
        // 参数4：version 数据库版本 (必须>=1,否则抛异常)
        //      （数据库版本，也是固定的。只有升级软件的时候，才改。）
        super(context, name, factory, version);
    }

        由于数据库名和数据库版本是固定的，游标实例工厂类可以使用默认的，因此，构造方法原来需要传4个参数，现在只需要传1个参数：“上下文”，即可。写成如下的样子：

    private final static String DB_NAME = "google.db";
    private final static int VERSION = 2;

    public DBOpenHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

        7. DBOpenHelper。写 onCreate()方法 和 onUpgrade()方法。

    /**
     * 在数据库第一次建立时被调用， 一般用来创建数据库中的表，
     * 并做适当的初始化工作
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        // sql语句
        String sql = "create table dept " +
                "(id integer primary key autoincrement," +
                "name text not null,loc text not null)";
        // 执行 sql语句
        db.execSQL(sql);
    }

    /**
     * 在数据库需要升级时被调用， 一般用来删除旧的数据库表，
     * 并将数据转移到新版本的数据库表中
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //String sql = "drop table if exists dept";  // 把原来的表直接删掉
        //db.execSQL(sql);  // 执行 sql语句
        //onCreate(db);  // 创建新表

        // 升级软件的步骤:
        // 1.修改数据版本 +1
        // 2.修改表结构,尽量不删除原有数据，再重建表
        String sql = "alter table dept ";
        sql += " add column sal integer default 0";
        // 执行 sql语句
        db.execSQL(sql);
    }

        8. 在 java 下面的包名下，创建 一个类：DeptService，用来操作 Dept 表：增删改查。先写一个构造方法：

    // 在构造函数中，传一个“上下文”的参数，通过数据库帮助类 创建数据库
    public DeptService(Context context) {
        dbOpenHelper = new DBOpenHelper(context);
    }

        （1）增（插入数据）：先在 DeptService 类中封装“增”的操作方法，然后去 MainActivity 中使用该方法进行“增”。

    /**
     * 插入
     * @param values
     */
    // ContentValues 相当于 Map 集合，用来存储 键值对数据
    public void insert(ContentValues values) {
        // 通过数据库帮助类获得 数据库对象(可以写的操作)
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        // 参数1：操作的表名
        // 参数3为null /长度为0
        // 参数2：为了满足SQL语句insert into dept () values()要求设置值
        // db.insert("dept", "name", null);
        // sql:insert into dept (name) values(null)

        // insert into dept(null) values(null);
        // insert into dept(name,loc) values('sales','LA')
        db.insert("dept", null, values);
    }

            case R.id.action_insert:
                // 新增
                values.put("name", txtName.getText().toString());
                values.put("loc", txtLoc.getText().toString());
                service.insert(values);
                break;

        （2）更新：先在 DeptService 类中封装“更新”的操作方法，然后去 MainActivity 中使用该方法进行“更新”。

    /**
     * 更新
     * @param values
     */
    public void update(ContentValues values) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.update("dept", values, "id=?",
                new String[]{String.valueOf(values.getAsInteger("id"))});
    }

            case R.id.action_update:
                // 更新
                values.put("id", txtId.getText().toString());
                values.put("name", txtName.getText().toString());
                values.put("loc", txtLoc.getText().toString());
                service.update(values);
                break;

        （3）删除：先在 DeptService 类中封装“删除”的操作方法，然后去 MainActivity 中使用该方法进行“删除”。

    /**
     * 删除
     * @param id
     */
    public void delete(Integer id) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.delete("dept", "id=?", new String[]{String.valueOf(id)});
    }

            case R.id.action_delete:
                // 删除
                service.delete(Integer.parseInt(txtId.getText().toString()));
                break;

        （4）查询所有记录：先在 DeptService 类中封装“查询所有记录”的操作方法，然后去 MainActivity 中使用该方法进行“查询所有记录”。（因为，查询要用到 实体类 Dept，因此要先创建 实体类 Dept 来封装 Dept表，见第9步）

    /**
     * 查询所有记录
     * @return
     */
    public List<Dept> find() {
        List<Dept> list = new ArrayList<>();
        // 通过数据库帮助类获得 数据库对象(可以读的操作)
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", null, null, null, null, null, "id desc");
        // query(表名,所有字段,where子句,where子句对应的值,groupBy子句,having子句,排序)

        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String loc = cursor.getString(cursor.getColumnIndex("loc"));
            list.add(new Dept(id, name, loc));
        }
        cursor.close();
        return list;
    }

            case R.id.action_read:
                // 查询
                String text = "";
                for (Dept dept : service.find()) {
                    text += dept + "\n";
                }
                tvResult.setText(text);
                break;

        （5）DeptService。其他查询方法。

    /**
     * 通过 id 查询
     * @param id
     * @return
     */
    public Dept find(Integer id) {
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", new String[]{"id", "name", "loc"},
                "id=?", new String[]{String.valueOf(id)}, null, null, null);
        if (cursor.moveToFirst()) {
            // int id=cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String loc = cursor.getString(cursor.getColumnIndex("loc"));
            return new Dept(id, name, loc);
        }
        return null;
    }

    /**
     * 分页查询（用得非常少）
     *
     * @param offset    忽略前面指定的记录数
     * @param maxResult 一页显示记录数
     * @return
     */
    public List<Dept> find(int offset, int maxResult) {
        List<Dept> list = new ArrayList<Dept>();
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", null, null, null, null, null, null, offset
                + "," + maxResult); // limit 0,3 / 3,3
        // query(表名,所有字段,where子句,where子句对应的值,groupBy子句,having子句,排序,分页)

        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String loc = cursor.getString(cursor.getColumnIndex("loc"));
            list.add(new Dept(id, name, loc));
        }
        cursor.close();
        return list;
    }

    /**
     * 统计
     * select count(*) from emp
     * @return
     */
    public long getCount() {
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", new String[]{"count(*)"}, null, null,
                null, null, null);
        cursor.moveToFirst();
        return cursor.getLong(0);
    }

    /**
     * 转账(SQLite事务)
     */
    public void trans(ContentValues fromValues, ContentValues toValues) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

        db.beginTransaction(); // 开启事务
        try {
            db.update("dept", fromValues, "id=?",
                    new String[]{String.valueOf(fromValues.getAsInteger("id"))});
            db.update("dept", toValues, "id=?",
                    new String[]{String.valueOf(toValues.getAsInteger("id"))});

            // 设置事务标志为成功,当结束事务时就会提交事务
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction(); // 结束事务
        }
    }

        9. 在 java 下面的包名下，创建 一个实体类：Dept，用来封装 Dept 表。

package com.android.db;

public class Dept {
    private int id;
    private String name;
    private String loc;

    public Dept() {
    }

    public Dept(int id, String name, String loc) {
        this.id = id;
        this.name = name;
        this.loc = loc;
    }

    @Override
    public String toString() {
        return "Dept{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", loc='" + loc + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getLoc() {
        return loc;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }
}

        附   代码补充

        1. DBOpenHelper

package com.android.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库帮助类
 */
public class DBOpenHelper extends SQLiteOpenHelper {
    private final static String DB_NAME = "google.db";
    private final static int VERSION = 2;

    public DBOpenHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    /**
     * 在数据库第一次建立时被调用， 一般用来创建数据库中的表，
     * 并做适当的初始化工作
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        // sql语句
        String sql = "create table dept " +
                "(id integer primary key autoincrement," +
                "name text not null,loc text not null)";
        // 执行 sql语句
        db.execSQL(sql);
    }

    /**
     * 在数据库需要升级时被调用， 一般用来删除旧的数据库表，
     * 并将数据转移到新版本的数据库表中
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //String sql = "drop table if exists dept";  // 把原来的表直接删掉
        //db.execSQL(sql);  // 执行 sql语句
        //onCreate(db);  // 创建新表

        // 升级软件的步骤:
        // 1.修改数据版本 +1
        // 2.修改表结构,尽量不删除原有数据，再重建表
        String sql = "alter table dept ";
        sql += " add column sal integer default 0";
        // 执行 sql语句
        db.execSQL(sql);
    }
}

        2. DeptService

package com.android.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;
import java.util.List;


public class DeptService {
    private DBOpenHelper dbOpenHelper;

    // 在构造函数中，传一个“上下文”的参数，通过数据库帮助类 创建数据库
    public DeptService(Context context) {
        dbOpenHelper = new DBOpenHelper(context);
    }

    /**
     * 插入
     * @param values
     */
    // ContentValues 相当于 Map 集合，用来存储 键值对数据
    public void insert(ContentValues values) {
        // 通过数据库帮助类获得 数据库对象(可以写的操作)
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        // 参数1：操作的表名
        // 参数3为null /长度为0
        // 参数2：为了满足SQL语句insert into dept () values()要求设置值
        // db.insert("dept", "name", null);
        // sql:insert into dept (name) values(null)

        // insert into dept(null) values(null);
        // insert into dept(name,loc) values('sales','LA')
        db.insert("dept", null, values);
    }

    /**
     * 更新
     * @param values
     */
    public void update(ContentValues values) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.update("dept", values, "id=?",
                new String[]{String.valueOf(values.getAsInteger("id"))});
    }

    /**
     * 删除
     * @param id
     */
    public void delete(Integer id) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();
        db.delete("dept", "id=?", new String[]{String.valueOf(id)});
    }

    /**
     * 查询所有记录
     * @return
     */
    public List<Dept> find() {
        List<Dept> list = new ArrayList<>();
        // 通过数据库帮助类获得 数据库对象(可以读的操作)
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", null, null, null, null, null, "id desc");
        // query(表名,所有字段,where子句,where子句对应的值,groupBy子句,having子句,排序)

        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String loc = cursor.getString(cursor.getColumnIndex("loc"));
            list.add(new Dept(id, name, loc));
        }
        cursor.close();
        return list;
    }

    /**
     * 通过 id 查询
     * @param id
     * @return
     */
    public Dept find(Integer id) {
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", new String[]{"id", "name", "loc"},
                "id=?", new String[]{String.valueOf(id)}, null, null, null);
        if (cursor.moveToFirst()) {
            // int id=cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String loc = cursor.getString(cursor.getColumnIndex("loc"));
            return new Dept(id, name, loc);
        }
        return null;
    }

    /**
     * 分页查询
     *
     * @param offset    忽略前面指定的记录数
     * @param maxResult 一页显示记录数
     * @return
     */
    public List<Dept> find(int offset, int maxResult) {
        List<Dept> list = new ArrayList<Dept>();
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", null, null, null, null, null, null, offset
                + "," + maxResult); // limit 0,3 / 3,3
        // query(表名,所有字段,where子句,where子句对应的值,groupBy子句,having子句,排序,分页)

        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex("id"));
            String name = cursor.getString(cursor.getColumnIndex("name"));
            String loc = cursor.getString(cursor.getColumnIndex("loc"));
            list.add(new Dept(id, name, loc));
        }
        cursor.close();
        return list;
    }

    /**
     * 统计
     * select count(*) from emp
     * @return
     */
    public long getCount() {
        SQLiteDatabase db = dbOpenHelper.getReadableDatabase();
        Cursor cursor = db.query("dept", new String[]{"count(*)"}, null, null,
                null, null, null);
        cursor.moveToFirst();
        return cursor.getLong(0);
    }

    /**
     * 转账(SQLite事务)
     */
    public void trans(ContentValues fromValues, ContentValues toValues) {
        SQLiteDatabase db = dbOpenHelper.getWritableDatabase();

        db.beginTransaction(); // 开启事务
        try {
            db.update("dept", fromValues, "id=?",
                    new String[]{String.valueOf(fromValues.getAsInteger("id"))});
            db.update("dept", toValues, "id=?",
                    new String[]{String.valueOf(toValues.getAsInteger("id"))});

            // 设置事务标志为成功,当结束事务时就会提交事务
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction(); // 结束事务
        }
    }
}

        3. MainActivity

package com.android.db;

import android.app.Activity;
import android.content.ContentValues;
import android.os.Bundle;
import android.view.Menu;
import android.view.MenuItem;
import android.widget.EditText;
import android.widget.TextView;


public class MainActivity extends Activity {
    private EditText txtId;
    private EditText txtName;
    private EditText txtLoc;
    private TextView tvResult;

    private DeptService service;

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

        txtId = (EditText) findViewById(R.id.txtId);
        txtName = (EditText) findViewById(R.id.txtName);
        txtLoc = (EditText) findViewById(R.id.txtLoc);
        tvResult = (TextView) findViewById(R.id.tvResult);

        service = new DeptService(this);
    }

    @Override
    public boolean onOptionsItemSelected(MenuItem item) {
        ContentValues values = new ContentValues();
        switch (item.getItemId()) {
            case R.id.action_insert:
                // 新增
                values.put("name", txtName.getText().toString());
                values.put("loc", txtLoc.getText().toString());
                service.insert(values);
                break;
            case R.id.action_read:
                // 查询
                String text = "";
                for (Dept dept : service.find()) {
                    text += dept + "\n";
                }
                tvResult.setText(text);
                break;
            case R.id.action_update:
                // 更新
                values.put("id", txtId.getText().toString());
                values.put("name", txtName.getText().toString());
                values.put("loc", txtLoc.getText().toString());
                service.update(values);
                break;
            case R.id.action_delete:
                // 删除
                service.delete(Integer.parseInt(txtId.getText().toString()));
                break;
        }

        return super.onOptionsItemSelected(item);
    }

    @Override
    public boolean onCreateOptionsMenu(Menu menu) {
        getMenuInflater().inflate(R.menu.menu_main, menu);
        return true;
    }
}