Android学习笔记-数据库开发-2-使用SQLite

最新推荐文章于 2020-05-23 10:00:56 发布

谁能告诉俄

最新推荐文章于 2020-05-23 10:00:56 发布

阅读量364

点赞数

分类专栏： android

本文链接：https://blog.csdn.net/qi1017269990/article/details/78800480

版权

android 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

上一篇：介绍了SQLite概念和android中两个基本类：

SQLiteDataBase和SQLiteOpenHelper

本篇主要用代码实现对这两个方法的使用：

1、数据模型建立：UserModel

public class UserModel implements Serializable {
    public ContentValues toContentValues() {//返回一个ContentValues 、更新、插入时候用到
        ContentValues cv = new ContentValues();
        cv.put(Columns.NAME, name);
        cv.put(Columns.AGE, age);
        return cv;
    }

    public static class Columns {//对应数据库的列名
        public static final String _ID = "_id";//主键
        public static final String NAME = "NAME";//姓名
        public static final String AGE = "AGE";//年龄
    }
    public long _id;
    public String name;
    public int age;

    @Override
    public String toString() {
        return "UserModel{" +
                "_id='" + _id + '\'' +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

2、创建MyDataBaseHelper，继承SQLiteOpenHelper，用于管理数据库表的创建和更新。

public class MyDataBaseHelper extends SQLiteOpenHelper {
    public String TAG = "MyDataBaseHelper";

    public MyDataBaseHelper(Context mContext, String databaseName, SQLiteDatabase.CursorFactory factory, int version) {
        super(mContext, databaseName, factory, version);
    }

    public static final String USER_TABLE_NAME = "USER_TABLE";//表名
    private static final String CREATE_USER_TABLE = new StringBuffer(//创建表的SQL
            "CREATE TABLE IF NOT EXISTS "
                    + USER_TABLE_NAME
                    + String.format(
                    "("
                            + "%s INTEGER PRIMARY KEY AUTOINCREMENT, " // id
                            + "%s VARCHAR, " // name
                            + "%s INTEGER " //  age
                            + ")"
                    , UserModel.Columns._ID
                    , UserModel.Columns.NAME
                    , UserModel.Columns.AGE
            )
    )
            .toString();


    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_USER_TABLE);//创建表
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //根据版本号更新表
        switch (oldVersion) {
            case 1://todo 增加表、删除表、增加字段、删除字段、等操作
            case 2:


        }
    }


    /**
     * 给一张表增加一个字段
     *
     * @param db
     * @param dbTable
     * @param columnName
     * @param columnDefinition
     */
    private void addColumn(SQLiteDatabase db, String dbTable,
                           String columnName, String columnDefinition) {

        if (checkColumnExists(db, dbTable, columnName)) {
            Log.e("checkColumnExists", "true");
        } else {
            Log.e("checkColumnExists", "false");
            db.execSQL("ALTER TABLE " + dbTable + " ADD COLUMN " + columnName + " "
                    + columnDefinition);
        }
    }

    /**
     * 检查表中某列是否存在
     *
     * @param db
     * @param tableName  表名
     * @param columnName 列名
     * @return
     */
    private boolean checkColumnExists(SQLiteDatabase db, String tableName, String columnName) {
        boolean result = false;
        Cursor cursor = null;

        try {
            cursor = db.rawQuery("select * from sqlite_master where name =? and sql like ?"
                    , new String[]{tableName, "%" + columnName + "%"});
            result = null != cursor && cursor.moveToFirst();
        } catch (Exception e) {
            Log.e(TAG, "checkColumnExists2..." + e.getMessage());
        } finally {
            if (null != cursor && !cursor.isClosed()) {
                cursor.close();
            }
        }

        return result;
    }
}

3、写一个DBManager类，主要负责数据库资源SQLiteDatabase的初始化,开启,关闭,以及获得DatabaseHelper帮助类操作。

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


import java.io.File;

/**
 * SQLite数据库管理类
 * <p>
 * 主要负责数据库资源SQLiteDatabase 的初始化,开启,关闭,以及获得DatabaseHelper帮助类操作
 *
 * @author shimiso
 */
public class DBManager {

    public final static int version = 1;
    private String databaseName;

    public static final String DB_KEY = "";//包数据库标识

    // 本地Context对象
    private Context mContext = null;

    private static DBManager dBManager = null;

    /**
     * 构造函数
     *
     * @param mContext
     */
    private DBManager(Context mContext) {
        super();
        this.mContext = mContext;

    }

    /***
     * 单态对象值为null
     **/

    public static void clearDbmanager() {
        dBManager = null;
    }

    public static DBManager getInstance(Context mContext, String databaseName) {
        dBManager = new DBManager(mContext);
        dBManager.databaseName = DB_KEY + databaseName;
        return dBManager;
    }

    /**
     * 关闭数据库 注意:当事务成功或者一次性操作完毕时候再关闭
     */
    public void closeDatabase(SQLiteDatabase dataBase, Cursor cursor) {
        if (null != dataBase) {
            dataBase.close();
        }
        if (null != cursor) {
            cursor.close();
        }
    }

    /**
     * 打开数据库  
     */
    public SQLiteDatabase openDatabase() {
        return getDatabaseHelper().getWritableDatabase();
    }

    /**
     * 获取DataBaseHelper
     *
     * @return
     */
    public MyDataBaseHelper getDatabaseHelper() {
        return new MyDataBaseHelper(mContext, this.databaseName, null,
                this.version);
    }

    public int getVersion() {
        String path = getDatabasePath(databaseName).getPath();
        SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(path, null);
        return db.getVersion();
    }

    private File getDatabasePath(String name) {
        String EXTERN_PATH = "/data/data/" + mContext.getPackageName() + "/databases/";
        File f = new File(EXTERN_PATH);
        if (!f.exists()) {
            f.mkdirs();
        }
        return new File(EXTERN_PATH + name);
    }
}

4、UserDBController，通过DBManager获取SQLiteDatabase，用于对UserModel的数据持久化操作。可以包括一些增删改查等。

这里只写了了增加一条UserModel。

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import databasetest.zx.com.databasetest.AppApplication;
import databasetest.zx.com.databasetest.model.UserModel;

import static databasetest.zx.com.databasetest.db.MyDataBaseHelper.USER_TABLE_NAME;

/**
 * Created by zhoux on 2017/12/11.
 * 注释：
 */

public class UserDBController {
    private final SQLiteDatabase db;

    public UserDBController() {
//        MyDataBaseHelper openHelper = new MyDataBaseHelper(AppApplication.CONTEXT, "zxdbtest.db");
        db = DBManager.getInstance(AppApplication.CONTEXT, "zxdbtest.db").openDatabase();
    }


    public UserModel addOne(UserModel model) {
        try {
            long _id = db.insert(USER_TABLE_NAME, null, model.toContentValues());//见UserModel的注释
            final boolean succeed = _id != -1;
            model._id = _id;
            return succeed ? model : null;

        } catch (Exception e) {

        } finally {
            closeDatabase(null);
        }
        return null;
    }

    public void closeDatabase(Cursor cursor) {
        if (null != cursor) {
            cursor.close();
        }
    }

}

5、布局文件activity_main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical">

    <Button
        android:id="@+id/add_tv"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="addOne" />

    <TextView
        android:id="@+id/result_tv"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="result:" />
</LinearLayout>

6、MainActivity

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.TextView;

import databasetest.zx.com.databasetest.db.UserDBController;
import databasetest.zx.com.databasetest.model.UserModel;

public class MainActivity extends AppCompatActivity {
    TextView add_tv;
    TextView result_tv;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        add_tv = findViewById(R.id.add_tv);
        result_tv = findViewById(R.id.result_tv);
        addListener();
    }

    private void addListener() {
        add_tv.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                UserModel user = new UserModel();
                user.name = "周小小";
                user.age = 18;
                UserDBController mUserDBController = new UserDBController();
                UserModel userDB = mUserDBController.addOne(user);
                if (userDB != null) {
                    result_tv.setText("插入结果：成功：" + userDB.toString());
                } else {
                    result_tv.setText("插入结果：失败");

                }
            }
        });
    }


}

源码：https://gitee.com/zhou.xiang/databasetest.git

下一篇：Android学习笔记-数据库开发-3：使用SQLite Expert Pro