数据存储——四种存储方式——数据库SQLite
======================================================================================
第一个界面：
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="vertical" android:padding="5dp" > <RelativeLayout android:layout_width="match_parent" android:layout_height="40dp" > <TextView android:id="@+id/tv_name" android:layout_width="wrap_content" android:layout_height="match_parent" android:gravity="center" android:text="姓名：" android:textColor="@color/black" android:textSize="17sp" /> <EditText android:id="@+id/et_name" android:layout_width="match_parent" android:layout_height="match_parent" android:layout_marginBottom="3dp" android:layout_marginTop="3dp" android:layout_toRightOf="@+id/tv_name" android:background="@drawable/editext_selector" android:gravity="left|center" android:hint="请输入姓名" android:inputType="text" android:maxLength="12" android:textColor="@color/black" android:textSize="17sp" /> </RelativeLayout> <RelativeLayout android:layout_width="match_parent" android:layout_height="40dp" > <TextView android:id="@+id/tv_age" android:layout_width="wrap_content" android:layout_height="match_parent" android:gravity="center" android:text="年龄：" android:textColor="@color/black" android:textSize="17sp" /> <EditText android:id="@+id/et_age" android:layout_width="match_parent" android:layout_height="match_parent" android:layout_marginBottom="3dp" android:layout_marginTop="3dp" android:layout_toRightOf="@+id/tv_age" android:background="@drawable/editext_selector" android:gravity="left|center" android:hint="请输入年龄" android:inputType="number" android:maxLength="2" android:textColor="@color/black" android:textSize="17sp" /> </RelativeLayout> <RelativeLayout android:layout_width="match_parent" android:layout_height="40dp" > <TextView android:id="@+id/tv_height" android:layout_width="wrap_content" android:layout_height="match_parent" android:gravity="center" android:text="身高：" android:textColor="@color/black" android:textSize="17sp" /> <EditText android:id="@+id/et_height" android:layout_width="match_parent" android:layout_height="match_parent" android:layout_marginBottom="3dp" android:layout_marginTop="3dp" android:layout_toRightOf="@+id/tv_height" android:background="@drawable/editext_selector" android:gravity="left|center" android:hint="请输入身高" android:inputType="number" android:maxLength="3" android:textColor="@color/black" android:textSize="17sp" /> </RelativeLayout> <RelativeLayout android:layout_width="match_parent" android:layout_height="40dp" > <TextView android:id="@+id/tv_weight" android:layout_width="wrap_content" android:layout_height="match_parent" android:gravity="center" android:text="体重：" android:textColor="@color/black" android:textSize="17sp" /> <EditText android:id="@+id/et_weight" android:layout_width="match_parent" android:layout_height="match_parent" android:layout_marginBottom="3dp" android:layout_marginTop="3dp" android:layout_toRightOf="@+id/tv_weight" android:background="@drawable/editext_selector" android:gravity="left|center" android:hint="请输入体重" android:inputType="numberDecimal" android:maxLength="5" android:textColor="@color/black" android:textSize="17sp" /> </RelativeLayout> <RelativeLayout android:layout_width="match_parent" android:layout_height="40dp" > <CheckBox android:id="@+id/ck_married" android:layout_width="wrap_content" android:layout_height="match_parent" android:gravity="center" android:checked="false" android:text="已婚" android:textColor="@color/black" android:textSize="17sp" /> </RelativeLayout> <Button android:id="@+id/btn_save" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="保存到数据库" android:textColor="@color/black" android:textSize="17sp" /> </LinearLayout>
第一个界面代码：
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(); } }
================================================================================================================
第二个界面：
<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/btn_delete" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="删除所有记录" android:textColor="@color/black" android:textSize="17sp" /> <TextView android:id="@+id/tv_sqlite" android:layout_width="match_parent" android:layout_height="wrap_content" android:paddingLeft="5dp" android:textColor="@color/black" android:textSize="17sp" /> </LinearLayout>
第二个界面代码：
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, "已删除所有记录"); } } }
=============================================================================
