数据存储——四种存储方式——数据库SQLite

深碍√TFBOYSˉ_ 2023-09-28 14:30 57阅读 0赞

![1c55494d5fc6b1ce5994a92bf404c29f.png][]

![8dd9e5422be699381e6a95486371292e.png][]

======================================================================================

第一个界面：

![de7eab95caf66004bd3f5c25424d4c48.png][]

第一个界面代码：

package com.example.myapplication;
    
    import static java.lang.Thread.sleep;
    
    import android.content.Intent;
    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.text.TextUtils;
    import android.view.View;
    import android.widget.CheckBox;
    import android.widget.CompoundButton;
    import android.widget.EditText;
    
    
    public class MainActivity extends AppCompatActivity implements View.OnClickListener, CompoundButton.OnCheckedChangeListener
    {
    
        private UserDBHelper mHelper; // 声明一个用户数据库帮助器的对象
        private EditText et_name;
        private EditText et_age;
        private EditText et_height;
        private EditText et_weight;
        private boolean isMarried = false;
    
    
    
        @Override
        protected void onCreate(Bundle savedInstanceState)
        {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main);
    
    
            et_name = findViewById(R.id.et_name);
            et_age = findViewById(R.id.et_age);
            et_height = findViewById(R.id.et_height);
            et_weight = findViewById(R.id.et_weight);
            CheckBox ck_married = findViewById(R.id.ck_married);
            ck_married.setOnCheckedChangeListener(this);
            findViewById(R.id.btn_save).setOnClickListener(this);
        }
    
        @Override
        public void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {
            isMarried = isChecked;
        }
    
        @Override
        protected void onStart() {
            super.onStart();
            // 获得数据库帮助器的实例
            mHelper = UserDBHelper.getInstance(this, 1);
            mHelper.openWriteLink(); // 打开数据库帮助器的写连接
        }
    
        @Override
        protected void onStop() {
            super.onStop();
            mHelper.closeLink(); // 关闭数据库连接
        }
    
        @Override
        public void onClick(View v) {
            if (v.getId() == R.id.btn_save)
            {
                String name = et_name.getText().toString();
                String age = et_age.getText().toString();
                String height = et_height.getText().toString();
                String weight = et_weight.getText().toString();
    
                if (TextUtils.isEmpty(name))
                {
                    ToastUtil.show(this, "请先填写姓名");
                    return;
                } else if (TextUtils.isEmpty(age))
                {
                    ToastUtil.show(this, "请先填写年龄");
                    return;
                } else if (TextUtils.isEmpty(height))
                {
                    ToastUtil.show(this, "请先填写身高");
                    return;
                } else if (TextUtils.isEmpty(weight))
                {
                    ToastUtil.show(this, "请先填写体重");
                    return;
                }
    
                // 以下声明一个用户信息对象，并填写它的各字段值
                UserInfo info = new UserInfo();
                info.name = name;
                info.age = Integer.parseInt(age);
                info.height = Long.parseLong(height);
                info.weight = Float.parseFloat(weight);
                info.married = isMarried;
                info.update_time = DateUtil.getNowDateTime("yyyy-MM-dd HH:mm:ss");
    
                mHelper.insert(info); // 执行数据库帮助器的插入操作
    
                ToastUtil.show(this, "数据已写入SQLite数据库");
    
    
                startActivity(new Intent(this, MainActivity2.class));
            }
        }
    
    }

UserDBHelper代码：

package com.example.myapplication;
    
    import android.annotation.SuppressLint;
    import android.content.ContentValues;
    import android.content.Context;
    import android.database.Cursor;
    import android.database.sqlite.SQLiteDatabase;
    import android.database.sqlite.SQLiteOpenHelper;
    import android.util.Log;
    import java.util.ArrayList;
    import java.util.List;
    
    @SuppressLint("DefaultLocale")
    public class UserDBHelper extends SQLiteOpenHelper
    {
        private static final String TAG = "UserDBHelper";
        private static final String DB_NAME = "user.db"; // 数据库的名称
        private static final int DB_VERSION = 1; // 数据库的版本号
        private static UserDBHelper mHelper = null; // 数据库帮助器的实例
        private SQLiteDatabase mDB = null; // 数据库的实例
        public static final String TABLE_NAME = "user_info"; // 表的名称
    
        private UserDBHelper(Context context) {
            super(context, DB_NAME, null, DB_VERSION);
        }
    
        private UserDBHelper(Context context, int version) {
            super(context, DB_NAME, null, version);
        }
    
        // 利用单例模式获取数据库帮助器的唯一实例
        public static UserDBHelper getInstance(Context context, int version) {
            if (version > 0 && mHelper == null) {
                mHelper = new UserDBHelper(context, version);
            } else if (mHelper == null) {
                mHelper = new UserDBHelper(context);
            }
            return mHelper;
        }
    
        // 打开数据库的读连接
        public SQLiteDatabase openReadLink()
        {
            if (mDB == null || !mDB.isOpen())
            {
                mDB = mHelper.getReadableDatabase();
            }
            return mDB;
        }
    
        // 打开数据库的写连接
        public SQLiteDatabase openWriteLink()
        {
            if (mDB == null || !mDB.isOpen())
            {
                mDB = mHelper.getWritableDatabase();
            }
            return mDB;
        }
    
        // 关闭数据库连接
        public void closeLink()
        {
            if (mDB != null && mDB.isOpen())
            {
                mDB.close();
                mDB = null;
            }
        }
    
        // 创建数据库，执行建表语句
        public void onCreate(SQLiteDatabase db)
        {
            Log.d(TAG, "onCreate");
            String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
            Log.d(TAG, "drop_sql:" + drop_sql);
            db.execSQL(drop_sql);
            String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
                    + "_id INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL,"
                    + "name VARCHAR NOT NULL," + "age INTEGER NOT NULL,"
                    + "height INTEGER NOT NULL," + "weight FLOAT NOT NULL,"
                    + "married INTEGER NOT NULL," + "update_time VARCHAR NOT NULL"
                    //演示数据库升级时要先把下面这行注释
                    + ",phone VARCHAR" + ",password VARCHAR"
                    + ");";
            Log.d(TAG, "create_sql:" + create_sql);
            db.execSQL(create_sql); // 执行完整的SQL语句
        }
    
        // 升级数据库，执行表结构变更语句
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.d(TAG, "onUpgrade oldVersion=" + oldVersion + ", newVersion=" + newVersion);
            if (newVersion > 1) {
                //Android的ALTER命令不支持一次添加多列，只能分多次添加
                String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "phone VARCHAR;";
                Log.d(TAG, "alter_sql:" + alter_sql);
                db.execSQL(alter_sql);
                alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "password VARCHAR;";
                Log.d(TAG, "alter_sql:" + alter_sql);
                db.execSQL(alter_sql); // 执行完整的SQL语句
            }
        }
    
        // 根据指定条件删除表记录
        public int delete(String condition)
        {
            // 执行删除记录动作，该语句返回删除记录的数目
            return mDB.delete(TABLE_NAME, condition, null);
        }
    
        // 删除该表的所有记录
        public int deleteAll()
        {
            // 执行删除记录动作，该语句返回删除记录的数目
            return mDB.delete(TABLE_NAME, "1=1", null);
        }
    
        // 往该表添加一条记录
        public long insert(UserInfo info)
        {
            List<UserInfo> infoList = new ArrayList<UserInfo>();
            infoList.add(info);
            return insert(infoList);
        }
    
        // 往该表添加多条记录
        public long insert(List<UserInfo> infoList)
        {
            long result = -1;
            for (int i = 0; i < infoList.size(); i++)
            {
                UserInfo info = infoList.get(i);
                List<UserInfo> tempList = new ArrayList<UserInfo>();
                // 如果存在同名记录，则更新记录
                // 注意条件语句的等号后面要用单引号括起来
                if (info.name != null && info.name.length() > 0)
                {
                    String condition = String.format("name='%s'", info.name);
                    tempList = query(condition);
                    if (tempList.size() > 0)
                    {
                        update(info, condition);
                        result = tempList.get(0).rowid;
                        continue;
                    }
                }
                // 如果存在同样的手机号码，则更新记录
                if (info.phone != null && info.phone.length() > 0)
                {
                    String condition = String.format("phone='%s'", info.phone);
                    tempList = query(condition);
                    if (tempList.size() > 0)
                    {
                        update(info, condition);
                        result = tempList.get(0).rowid;
                        continue;
                    }
                }
    
                // 不存在唯一性重复的记录，则插入新记录
                ContentValues cv = new ContentValues();
                cv.put("name", info.name);
                cv.put("age", info.age);
                cv.put("height", info.height);
                cv.put("weight", info.weight);
                cv.put("married", info.married);
                cv.put("update_time", info.update_time);
                cv.put("phone", info.phone);
                cv.put("password", info.password);
    
                // 执行插入记录动作，该语句返回插入记录的行号
                result = mDB.insert(TABLE_NAME, "", cv);
    
                if (result == -1) { // 添加成功则返回行号，添加失败则返回-1
                    return result;
                }
            }
            return result;
        }
    
        // 根据条件更新指定的表记录
        public int update(UserInfo info, String condition)
        {
            ContentValues cv = new ContentValues();
            cv.put("name", info.name);
            cv.put("age", info.age);
            cv.put("height", info.height);
            cv.put("weight", info.weight);
            cv.put("married", info.married);
            cv.put("update_time", info.update_time);
            cv.put("phone", info.phone);
            cv.put("password", info.password);
            // 执行更新记录动作，该语句返回更新的记录数量
            return mDB.update(TABLE_NAME, cv, condition, null);
        }
    
        public int update(UserInfo info)
        {
            // 执行更新记录动作，该语句返回更新的记录数量
            return update(info, "rowid=" + info.rowid);
        }
    
        // 根据指定条件查询记录，并返回结果数据列表
        public List<UserInfo> query(String condition) {
            String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time," +
                    "phone,password from %s where %s;", TABLE_NAME, condition);
            Log.d(TAG, "query sql: " + sql);
            List<UserInfo> infoList = new ArrayList<UserInfo>();
            // 执行记录查询动作，该语句返回结果集的游标
            Cursor cursor = mDB.rawQuery(sql, null);
            // 循环取出游标指向的每条记录
            while (cursor.moveToNext()) {
                UserInfo info = new UserInfo();
                info.rowid = cursor.getLong(0); // 取出长整型数
                info.xuhao = cursor.getInt(1); // 取出整型数
                info.name = cursor.getString(2); // 取出字符串
                info.age = cursor.getInt(3); // 取出整型数
                info.height = cursor.getLong(4); // 取出长整型数
                info.weight = cursor.getFloat(5); // 取出浮点数
                //SQLite没有布尔型，用0表示false，用1表示true
                info.married = (cursor.getInt(6) == 0) ? false : true;
                info.update_time = cursor.getString(7); // 取出字符串
                info.phone = cursor.getString(8); // 取出字符串
                info.password = cursor.getString(9); // 取出字符串
                infoList.add(info);
            }
            cursor.close(); // 查询完毕，关闭数据库游标
            return infoList;
        }
    
        // 根据手机号码查询指定记录
        public UserInfo queryByPhone(String phone) {
            UserInfo info = null;
            List<UserInfo> infoList = query(String.format("phone='%s'", phone));
            if (infoList.size() > 0) { // 存在该号码的登录信息
                info = infoList.get(0);
            }
            return info;
        }
    
    }

package com.example.myapplication;
    
    import android.content.Context;
    import android.widget.Toast;
    
    public class ToastUtil
    {
    
        public static void show(Context ctx, String desc)
        {
            Toast.makeText(ctx, desc, Toast.LENGTH_SHORT).show();
        }
    }

![d83141d37ccd8373755ab6563e3f6543.png][]

================================================================================================================

第二个界面：

![fcda4adc0fa970a24fef06f476cbda56.png][]

第二个界面代码：

package com.example.myapplication;
    
    import android.support.v7.app.AppCompatActivity;
    import android.os.Bundle;
    import android.view.View;
    import android.widget.TextView;
    import java.util.List;
    
    
    public class MainActivity2 extends AppCompatActivity implements View.OnClickListener
    {
    
        private UserDBHelper mHelper; // 声明一个用户数据库帮助器的对象
        private TextView tv_sqlite;
    
        @Override
        protected void onCreate(Bundle savedInstanceState)
        {
            super.onCreate(savedInstanceState);
            setContentView(R.layout.activity_main2);
    
    
            tv_sqlite = findViewById(R.id.tv_sqlite);
            findViewById(R.id.btn_delete).setOnClickListener(this);
        }
    
        @Override
        protected void onStart()
        {
            super.onStart();
    
    
            // 获得数据库帮助器的实例
            mHelper = UserDBHelper.getInstance(this, 1);
            mHelper.openReadLink(); // 打开数据库帮助器的读连接
            readSQLite(); // 读取数据库中保存的所有用户记录
        }
    
        @Override
        protected void onStop()
        {
            super.onStop();
            mHelper.closeLink(); // 关闭数据库连接
        }
    
        // 读取数据库中保存的所有用户记录
        private void readSQLite()
        {
            if (mHelper == null)
            {
                ToastUtil.show(this, "数据库连接为空");
                return;
            }
    
            // 执行数据库帮助器的查询操作
            List<UserInfo> userList = mHelper.query("1=1");
    
            String desc = String.format("数据库查询到%d条记录，详情如下：", userList.size());
    
            for (int i = 0; i < userList.size(); i++) {
                UserInfo info = userList.get(i);
                desc = String.format("%s\n第%d条记录信息如下：", desc, i + 1);
                desc = String.format("%s\n　姓名为%s", desc, info.name);
                desc = String.format("%s\n　年龄为%d", desc, info.age);
                desc = String.format("%s\n　身高为%d", desc, info.height);
                desc = String.format("%s\n　体重为%f", desc, info.weight);
                desc = String.format("%s\n　婚否为%b", desc, info.married);
                desc = String.format("%s\n　更新时间为%s", desc, info.update_time);
            }
    
            if (userList.size() <= 0)
            {
                desc = "数据库查询到的记录为空";
            }
    
            tv_sqlite.setText(desc);
        }
    
        @Override
        public void onClick(View v)
        {
            if (v.getId() == R.id.btn_delete)
            {
                mHelper.closeLink(); // 关闭数据库连接
                mHelper.openWriteLink(); // 打开数据库帮助器的写连接
                mHelper.deleteAll(); // 删除所有记录
                mHelper.closeLink(); // 关闭数据库连接
                mHelper.openReadLink(); // 打开数据库帮助器的读连接
                readSQLite(); // 读取数据库中保存的所有用户记录
    
                ToastUtil.show(this, "已删除所有记录");
            }
        }
    
    }

=============================================================================

![a1a8c317c87882eb4a67d41bc34d3f2e.png][]

![696ac04c36320bd8b0128f4c8d19081d.png][]

![7b9eba9c174101d624d99a0a1486599b.png][]

[1c55494d5fc6b1ce5994a92bf404c29f.png]: https://img-blog.csdnimg.cn/img_convert/1c55494d5fc6b1ce5994a92bf404c29f.png
[8dd9e5422be699381e6a95486371292e.png]: https://img-blog.csdnimg.cn/img_convert/8dd9e5422be699381e6a95486371292e.png
[de7eab95caf66004bd3f5c25424d4c48.png]: https://img-blog.csdnimg.cn/img_convert/de7eab95caf66004bd3f5c25424d4c48.png
[d83141d37ccd8373755ab6563e3f6543.png]: https://img-blog.csdnimg.cn/img_convert/d83141d37ccd8373755ab6563e3f6543.png
[fcda4adc0fa970a24fef06f476cbda56.png]: https://img-blog.csdnimg.cn/img_convert/fcda4adc0fa970a24fef06f476cbda56.png
[a1a8c317c87882eb4a67d41bc34d3f2e.png]: https://img-blog.csdnimg.cn/img_convert/a1a8c317c87882eb4a67d41bc34d3f2e.png
[696ac04c36320bd8b0128f4c8d19081d.png]: https://img-blog.csdnimg.cn/img_convert/696ac04c36320bd8b0128f4c8d19081d.png
[7b9eba9c174101d624d99a0a1486599b.png]: https://img-blog.csdnimg.cn/img_convert/7b9eba9c174101d624d99a0a1486599b.png