Android SQL 超实用设计

需求来源

1、最近由于工作原因，经常需要保存用户数据，其中涉及创建表格、增删改查操作。虽然 SQLiteDatabase 提供 insert 、delete、update、query 方法，但每次都要小心翼翼传入参数，对于频繁操作数据数据比较容易出错，影响工作效率。
2、现在我重新学习项目中数据库设计的方法，其中把每一张表字段写在一个bean，通过继承基础 BaseDao ，调用公共的数据库指令。 这样做的好处很明显：①逻辑清晰，方便以后数据的增添；②操作规范，不用每次打开数据库或者关闭数据库，避免忘记关闭数据库等误操作。

代码详解

1、布局文件相当简单，设置创建数据库、更新数据库和增删改查操作，代码和页面如下所示：

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical">

    <Button
        android:id="@+id/createDatabase"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:background="@color/colorBackGround"
        android:text="创建数据库" />

    <Button
        android:id="@+id/updateDatabase"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp"
        android:background="@color/colorBackGround"
        android:text="更新数据库" />

    <Button
        android:id="@+id/insert"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp"
        android:background="@color/colorBackGround"
        android:text="插入数据" />

    <Button
        android:id="@+id/update"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp"
        android:background="@color/colorBackGround"
        android:text="更新数据" />

    <Button
        android:id="@+id/query"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp"
        android:background="@color/colorBackGround"
        android:text="查询数据" />

    <Button
        android:id="@+id/delete"
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp"
        android:background="@color/colorBackGround"
        android:text="删除数据" />
</LinearLayout>

代码很简单，页面如下所示

2、自定义一个继承 SQLiteOpenHelper 的类 SQLiteHelper，重写 onCreate() 和 onUpgrade() 方法。其中当调用 SQLiteHelper 的 getReadableDatabase() 或者 getWritableDatabase() 时，如果没有创建数据库，则自动创建数据库；接着调用 onCreate() 方法，一般在这里创建数据库表，当有版本更新时，走 onUpgrade() 方法，更新最新的数据库表。

/**
 * Created by LCS on 2016/11/1.
 */
public class SQLiteHelper extends SQLiteOpenHelper {

    private static final String TAG = "LCS_SQLiteHelper";
    private static final String db_name = "sql.db";//数据库名称
    private static final SQLiteDatabase.CursorFactory factory = null;//暂时用不到
    private static final int version = 1;//版本号，方便以后项目更新用户数据库
    private static SQLiteHelper sqLiteHelper = null;//实例化 SQLiteHelper 对象

    //创建班级通讯录
    private static final String create_class_address = SQLInstruction.createClassAddress();
    /**
     * 构造函数
     * @param context
     */
    public SQLiteHelper(Context context){
        super(context, db_name, factory, version);
    }

    /**
     * 获取实例
     * @param context
     * @return
     */
    public static SQLiteHelper getInstance(Context context){

        if(sqLiteHelper == null){
            sqLiteHelper = new SQLiteHelper(context);
        }
        return sqLiteHelper;
    }

    /**
     * 第一次创建数据库，调用此方法
     * @param sqLiteDatabase
     */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

        //创建班级通讯录表
        sqLiteDatabase.execSQL(create_class_address);
        Log.d(TAG,"create db");
    }

    /**
     * 更新数据库
     * @param sqLiteDatabase
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {

        if(version > 1){
            sqLiteDatabase.execSQL(create_class_address);
            Log.d(TAG,"upgrade db");
        }
    }
}

3、先构建数据库连接管理器类 ConnectionProvider，这是真正执行数据库语句的位置。其中 execute(String sql) 可以执行数据库增加、删除和更新操作，query(String sql) 执行查找操作。被执行的的数据库指令为 Stiing 类型，我们只需要检查此语句正确性 ( 推荐使用 SQLite3 可视化工具 )，传入给对应的方法即可，是不是很方便？

/**
 * Created by lcs on 2016/11/2.
 * 连接管理器，数据库语句真正执行位置
 */
public class ConnectionProvider {
    private SQLiteDatabase db;
    private SQLiteHelper sqLiteHelper;
    private Context context;
    private static ConnectionProvider provider;

    public ConnectionProvider(Context context){
        this.context = context;
/*        //初始化
        initProvider();*/
    }
    //初始化
    private void initProvider() {
        if(sqLiteHelper == null){
            sqLiteHelper = SQLiteHelper.getInstance(context);
            db = null;
        }
        if(db == null){
            db = sqLiteHelper.getWritableDatabase();
        }
    }

    /**
     * 真正执行数据库操作
     * @param sql
     */
    public synchronized void execute(String sql){
        //打开数据库
        openDB();
        db.execSQL(sql);
        //关闭数据库
        closeDB(db);
    }

    /**
     * 真正执行数据库操作
     * @param sql
     */
    public synchronized Cursor query(String sql){
        //打开数据库
        openDB();
        return db.rawQuery(sql,null);
    }

    /**
     * 打开数据库
     */
    private void openDB() {
        try {
            initProvider();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 关闭数据库
     * @param db
     */
    private void closeDB(SQLiteDatabase db) {
        try {
            sqLiteHelper = null;
            db.releaseReference();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 获取 ConnectionProvider 实例
     * @return
     */
    public static ConnectionProvider getInstance(Context context){
        if(provider == null){
            provider = new ConnectionProvider(context);
        }
        return provider;
    }
}

4、构建基础 Dao。BaseDao 很简单，但很重要。当以后有操作数据库需求，只需要定义 某某 Dao 继承 BaseDao，即可调用公共的数据库操作。

/**
 * 基础 Dao
 * Created by lcs on 2016/11/2.
 */
public abstract class BaseDao <T>{
    private Context context;
    private ConnectionProvider provider;

    public BaseDao(Context context){
        this.context = context;
        this.provider = ConnectionProvider.getInstance(context);
    }

    /**
     * 执行 SQL 语句
     */
    public synchronized void execute(String sql){
        provider.execute(sql);
    }

    /**
     * 执行 SQL 语句
     */
    public synchronized Cursor query(String sql){
        return provider.query(sql);
    }

    /**
     * 是否有结果集
     * @param c
     * @return
     */
    protected synchronized boolean hasResult(Cursor c){
        boolean has=false;
        if(c.moveToFirst()&&c.getCount()>0){
            has=true;
        }
        return has;
    }

}

5、创建学生信息 bean 。其中 StudentInfoBean 为需要存储进数据库的字段信息

/**
 * Created by user on 2016/11/2.
 * 学生信息
 */
public class StudentInfoBean {
    private String name = "";//姓名
    private String id = "";//学号
    private String age = "";//年龄
    private String tall = "";//身高

    public String getName() {
        return name;
    }

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

    public String getId() {
        return id;
    }

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

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public String getTall() {
        return tall;
    }

    public void setTall(String tall) {
        this.tall = tall;
    }
}

6、创建学生 Dao。其中 StudentDao 继承 BaseDao，在通过学号 id 查找和删除学生信息方法中，其实也可以传入其他学生信息进行查找，例如学生 name 、age 等。还有通过 id 可能查到多条记录，规范写法应该写成返回 StudentInfoBean 的 List 。这里只是做测试，留给读者自己完善吧。

/**
 * Created by lcs on 2016/11/2.
 * 学生信息的 Dao
 */
public class StudentDao extends BaseDao {

    public StudentDao(Context context) {
        super(context);
    }

    /**
     * 增加一条学生记录
     * @param studentInfoBean
     * @return
     */
    public boolean addStudent(StudentInfoBean studentInfoBean){
        boolean flag = false;
        if(studentInfoBean != null){
            flag = true;
            execute(SQLInstruction.addStudent(studentInfoBean));
            return flag;
        }else {
            return flag;
        }
    }

    /**
     * 通过学号 id 删除学生信息
     * @param id
     * @return
     */
    public boolean deleteStudentById(String id){
        boolean flag = false;
        if(id != "" && !id.isEmpty()){
            flag = true;
            execute(SQLInstruction.deleteStudentById(id));
            return flag;
        }else {
            return flag;
        }
    }

    /**
     * 更新指定列学生信息
     * @param column
     * @param old_value
     * @param new_value
     * @return
     */
    public boolean updateStudent(String column ,String old_value,String new_value){
        boolean flag = false;
        if(column != "" && !column.isEmpty()){
            execute(SQLInstruction.updateStudent(column,old_value,new_value));
            flag = true;
            return flag;
        }else {
            return flag;
        }
    }

    /**
     * 通过学号 id 查询学生信息
     * @param id
     * @return
     */
    public StudentInfoBean queryStudent(String id){
        StudentInfoBean studentInfoBean = new StudentInfoBean();
        if(id != "" && !id.isEmpty()){
            Cursor cursor = query(SQLInstruction.queryStudentById(id));
            if(hasResult(cursor)){
                studentInfoBean.setName(cursor.getString(cursor.getColumnIndex("NAME")));
                studentInfoBean.setAge(cursor.getString(cursor.getColumnIndex("AGE")));
                studentInfoBean.setTall(cursor.getString(cursor.getColumnIndex("TALL")));
                cursor.moveToNext();
            }
            cursor.close();
        }
        return studentInfoBean;
    }
}

7、看到现在，不知道你会不会觉得很奇怪，为什么还没有涉及真正数据库操作语句？那是因为我们把所有数据库语句放在一个类中 SQLInstruction。为方便调用，将每一个数据库操作方法设置为 static。看到这里，你是否发现如此设计数据库操作好处呢？

/**
 * Created by user on 2016/11/1.
 * 数据库语句
 */
public class SQLInstruction {

    /**
     * 创建班级通讯录表
     *
     * @return
     */
    public static String createClassAddress() {
        StringBuffer sql = new StringBuffer();
        sql.append("create table IF NOT EXISTS CLASS_ADDRESS("
                + "NAME String," + "ID int," +"AGE int,"
                + "TALL int," + "CLASS_NAME String" + " )");
        return sql.toString();
    }

    /**
     * 在数据库添加一条学生记录
     */
    public static String addStudent(StudentInfoBean student){
        StringBuffer sql = new StringBuffer("insert into CLASS_ADDRESS(")
                .append("NAME,")
                .append("ID,")
                .append("AGE,")
                .append("TALL")
                .append(") VALUES(");
        sql.append("'").append(student.getName()).append("',");
        sql.append("'").append(student.getId()).append("',");
        sql.append("'").append(student.getAge()).append("',");
        sql.append("'").append(student.getTall()).append("')");
        return sql.toString();
    }

    /**
     * 通过学号 id 删除学生信息
     * @param id
     * @return
     */
    public static String deleteStudentById(String id){
        StringBuffer sql = new StringBuffer();
        sql.append("delete from CLASS_ADDRESS where ID = '");
        sql.append(id).append("'");
        return sql.toString();
    }

    /**
     * 更新指定列学生信息
     * @param column
     * @param old_value
     * @param new_value
     * @return
     */
    public static String updateStudent(String column ,String old_value,String new_value){
        StringBuffer sql = new StringBuffer();
        sql.append("update CLASS_ADDRESS set ");
        sql.append(column).append(" = ");
        sql.append("'").append(new_value).append("'");
        sql.append(" where ").append(column).append(" = ");
        sql.append("'").append(old_value).append("'");
        return sql.toString();
    }

    /**
     * 通过学号 id 查询学生信息
     * @param id
     * @return
     */
    public static String queryStudentById(String id){
        StringBuffer sql = new StringBuffer();
        sql.append("select NAME,ID,AGE,TALL ");
        sql.append("from CLASS_ADDRESS where ID = '");
        sql.append(id).append("'");
        return sql.toString();
    }

}

8、进入最后一步操作，在 Activity 进行数据库操作。

@Override
    public void onClick(View view) {
        StudentDao studentDao = null;
        switch (view.getId()){
            case R.id.createDatabase://创建数据库
                SQLiteHelper sqLiteHelper = new SQLiteHelper(SQLiteActivity.this);
                SQLiteDatabase database_create = sqLiteHelper.getReadableDatabase();
                database_create.close();
                break;

            case R.id.updateDatabase://更新数据库
                SQLiteHelper sqLiteHelper_update = new SQLiteHelper(SQLiteActivity.this);
                SQLiteDatabase database_update = sqLiteHelper_update.getWritableDatabase();
                database_update.close();
                break;

            case R.id.insert://插入两条学生记录
                if(studentDao == null){
                    studentDao = new StudentDao(SQLiteActivity.this);
                }
                boolean insert_result1 = studentDao.addStudent(studentInfoBean1);
                boolean insert_result2 = studentDao.addStudent(studentInfoBean1);
                Log.d(TAG,String.valueOf(insert_result1));
                Log.d(TAG,String.valueOf(insert_result2));
                break;

            case R.id.delete://通过学号 id 删除学生信息
                if(studentDao == null){
                    studentDao = new StudentDao(SQLiteActivity.this);
                }
                boolean delete_result = studentDao.deleteStudentById("1000001");
                Log.d(TAG,String.valueOf(delete_result));
                break;

            case R.id.update://更新指定列学生信息
                if(studentDao == null){
                    studentDao = new StudentDao(SQLiteActivity.this);
                }
                boolean update_result = studentDao.updateStudent("NAME", "小明", "飞天");
                Log.d(TAG,String.valueOf(update_result));
                break;

            case R.id.query://通过学号 id 查询学生信息
                if(studentDao == null){
                    studentDao = new StudentDao(SQLiteActivity.this);
                }
                StudentInfoBean studentInfoBean = studentDao.queryStudent("1000001");
                String age = studentInfoBean.getAge();
                String name = studentInfoBean.getName();
                String tall = studentInfoBean.getTall();
                Log.d(TAG,"age= " + age + "\nname = " + name + "\ntall= " + tall);
                break;
        }
    }