2022-10-30学习内容
1.SQLiteOpenHelper、增删改查、事务
1.1UserDBHelper.java
package com.example.chapter06.database; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import com.example.chapter06.entity.User; import java.util.ArrayList; import java.util.List; public class UserDBHelper extends SQLiteOpenHelper { private static final String DB_NAME = "user.db"; private static final String TABLE_NAME = "USER_INFO"; private static final int DB_VERSION = 1; private static UserDBHelper mHelper = null; private SQLiteDatabase mRDB = null; private SQLiteDatabase mWDB = null; private UserDBHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); } // 打开数据库的读连接 public SQLiteDatabase openReadLink() { if (mRDB == null || !mRDB.isOpen()) { mRDB = mHelper.getReadableDatabase(); } return mRDB; } // 打开数据库的写连接 public SQLiteDatabase openWriteLink() { if (mWDB == null || !mWDB.isOpen()) { mWDB = mHelper.getWritableDatabase(); } return mWDB; } // 关闭数据库连接 public void closeLink() { if (mRDB != null && mRDB.isOpen()) { mRDB.close(); mRDB = null; } if (mWDB != null && mWDB.isOpen()) { mWDB.close(); mWDB = null; } } // 利用单例模式获取数据库帮助器的唯一实例 public static UserDBHelper getInstance(Context context) { if (mHelper == null) { mHelper = new UserDBHelper(context); } return mHelper; } // 创建数据库，执行建表语句 @Override public void onCreate(SQLiteDatabase db) { String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " (" + "_ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," + " NAME VARCHAR NOT NULL," + " AGE INTEGER NOT NULL," + " HEIGHT LONG NOT NULL," + " WEIGHT FLOAT NOT NULL," + " MARRIED INTEGER NOT NULL);"; db.execSQL(sql); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } public long insert(User user) { ContentValues values = new ContentValues(); values.put("NAME", user.name); values.put("AGE", user.age); values.put("HEIGHT", user.height); values.put("WEIGHT", user.weight); values.put("MARRIED", user.married); // 执行插入记录动作，该语句返回插入记录的行号 // 如果第三个参数values 为null或者元素个数为0，由于insert()方法要求必须添加一条除了主键之外其他字段为Null值的记录， // 为了满足SQL语法的需要，insert语句必须给定一个字段名，如：insert into person(name) values(NULL)， // 倘若不给定字段名，insert语句就成了这样：insert into person() values()，显然这不满足标准SQL的语法。 // 如果第三个参数values 不为Null并且元素的个数大于0，可以把第二个参数设置为null。 // return mWDB.insert(TABLE_NAME, null, values); try { mWDB.beginTransaction(); mWDB.insert(TABLE_NAME, null, values); // int i = 10 / 0; mWDB.insert(TABLE_NAME, null, values); mWDB.setTransactionSuccessful(); } catch (Exception e) { e.printStackTrace(); } finally { mWDB.endTransaction(); } return 1; } public long deleteByName(String name) { // 删除所有 // mWDB.delete(TABLE_NAME, "1=1", null); return mWDB.delete(TABLE_NAME, "NAME=?", new String[]{name}); } public long update(User user) { ContentValues values = new ContentValues(); values.put("NAME", user.name); values.put("AGE", user.age); values.put("HEIGHT", user.height); values.put("WEIGHT", user.weight); values.put("MARRIED", user.married); return mWDB.update(TABLE_NAME, values, "NAME=?", new String[]{user.name}); } public List<User> queryAll() { List<User> list = new ArrayList<>(); // 执行记录查询动作，该语句返回结果集的游标 Cursor cursor = mRDB.query(TABLE_NAME, null, null, null, null, null, null); // 循环取出游标指向的每条记录 while (cursor.moveToNext()) { User user = new User(); user.id = cursor.getInt(0); user.name = cursor.getString(1); user.age = cursor.getInt(2); user.height = cursor.getLong(3); user.weight = cursor.getFloat(4); // SQLite没有布尔型，用0表示false，用1表示true user.married = (cursor.getInt(5) == 0) ? false : true; list.add(user); } return list; } public List<User> queryByName(String name) { List<User> list = new ArrayList<>(); // 执行记录查询动作，该语句返回结果集的游标 Cursor cursor = mRDB.query(TABLE_NAME, null, "NAME=?", new String[]{name}, null, null, null); // 循环取出游标指向的每条记录 while (cursor.moveToNext()) { User user = new User(); user.id = cursor.getInt(0); user.name = cursor.getString(1); user.age = cursor.getInt(2); user.height = cursor.getLong(3); user.weight = cursor.getFloat(4); // SQLite没有布尔型，用0表示false，用1表示true user.married = (cursor.getInt(5) == 0) ? false : true; list.add(user); } return list; } }
1.2SQLiteHelperActivity.java
package com.example.chapter06; import androidx.appcompat.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import android.view.View; import android.widget.Button; import android.widget.CheckBox; import android.widget.EditText; import com.example.chapter06.database.UserDBHelper; import com.example.chapter06.entity.User; import com.example.chapter06.util.ToastUtil; import java.util.List; public class SQLiteHelperActivity extends AppCompatActivity implements View.OnClickListener { private EditText et_name; private EditText et_age; private EditText et_height; private EditText et_weight; private CheckBox ck_married; private UserDBHelper mHelper; private Button btn_save; private Button btn_delete; private Button btn_update; private Button btn_query; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_sqlite_helper); 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); ck_married = findViewById(R.id.ck_married); btn_save = findViewById(R.id.btn_save); btn_delete = findViewById(R.id.btn_delete); btn_update = findViewById(R.id.btn_update); btn_query = findViewById(R.id.btn_query); findViewById(R.id.et_name).setOnClickListener(this); findViewById(R.id.et_age).setOnClickListener(this); findViewById(R.id.et_height).setOnClickListener(this); findViewById(R.id.et_weight).setOnClickListener(this); findViewById(R.id.ck_married).setOnClickListener(this); findViewById(R.id.btn_save).setOnClickListener(this); findViewById(R.id.btn_delete).setOnClickListener(this); findViewById(R.id.btn_update).setOnClickListener(this); findViewById(R.id.btn_query).setOnClickListener(this); } @Override protected void onStart() { super.onStart(); // 获得数据库帮助器的实例 mHelper = UserDBHelper.getInstance(this); // 打开数据库帮助器的读写连接 mHelper.openWriteLink(); mHelper.openReadLink(); } @Override protected void onStop() { super.onStop(); // 关闭数据库连接 mHelper.closeLink(); } @Override public void onClick(View v) { String name = et_name.getText().toString(); String age = et_age.getText().toString(); String height = et_height.getText().toString(); String weight = et_weight.getText().toString(); User user = null; switch (v.getId()) { case R.id.btn_save: // 以下声明一个用户信息对象，并填写它的各字段值 user = new User(name, Integer.parseInt(age), Long.parseLong(height), Float.parseFloat(weight), ck_married.isChecked()); if (mHelper.insert(user) > 0) { ToastUtil.show(this, "添加成功"); } break; case R.id.btn_delete: if (mHelper.deleteByName(name) > 0) { ToastUtil.show(this, "删除成功"); } break; case R.id.btn_update: user = new User(name, Integer.parseInt(age), Long.parseLong(height), Float.parseFloat(weight), ck_married.isChecked()); if (mHelper.update(user) > 0) { ToastUtil.show(this, "修改成功"); } break; case R.id.btn_query: List<User> list = mHelper.queryAll(); // List<User> list = mHelper.queryByName(name); for (User u : list) { Log.d("ning", u.toString()); } break; } } }
1.3activity_sqlite_helper.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" android:padding="5dp"> <LinearLayout android:layout_width="match_parent" android:layout_height="40dp" android:orientation="horizontal"> <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="0dp" android:layout_height="match_parent" android:layout_weight="1" android:layout_marginTop="3dp" android:layout_marginBottom="3dp" android:background="@drawable/editext_selector" android:hint="请输入姓名" android:inputType="text" android:maxLength="12" android:textColor="@color/black" android:textSize="17sp" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="40dp" android:orientation="horizontal"> <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="0dp" android:layout_height="match_parent" android:layout_weight="1" android:layout_marginTop="3dp" android:layout_marginBottom="3dp" android:background="@drawable/editext_selector" android:hint="请输入年龄" android:inputType="number" android:maxLength="2" android:textColor="@color/black" android:textSize="17sp" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="40dp" android:orientation="horizontal"> <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="0dp" android:layout_height="match_parent" android:layout_weight="1" android:layout_marginTop="3dp" android:layout_marginBottom="3dp" android:background="@drawable/editext_selector" android:hint="请输入身高" android:inputType="number" android:maxLength="3" android:textColor="@color/black" android:textSize="17sp" /> </LinearLayout> <LinearLayout android:layout_width="match_parent" android:layout_height="40dp" android:orientation="horizontal"> <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="0dp" android:layout_height="match_parent" android:layout_weight="1" android:layout_marginTop="3dp" android:layout_marginBottom="3dp" android:background="@drawable/editext_selector" android:hint="请输入体重" android:inputType="numberDecimal" android:maxLength="5" android:textColor="@color/black" android:textSize="17sp" /> </LinearLayout> <CheckBox android:id="@+id/ck_married" android:layout_width="wrap_content" android:layout_height="wrap_content" android:checked="false" android:gravity="center" android:text="已婚" android:textColor="@color/black" android:textSize="17sp" /> <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" /> <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" /> <Button android:id="@+id/btn_update" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="修改" android:textColor="@color/black" android:textSize="17sp" /> <Button android:id="@+id/btn_query" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="查询" android:textColor="@color/black" android:textSize="17sp" /> </LinearLayout>
1.4User.java
package com.example.chapter06.entity; public class User { public int id; // 序号 public String name; // 姓名 public int age; // 年龄 public long height; // 身高 public float weight; // 体重 public boolean married; // 婚否 public User() { } public User(String name, int age, long height, float weight, boolean married) { this.name = name; this.age = age; this.height = height; this.weight = weight; this.married = married; } @Override public String toString() { return "User{" + "id=" + id + ", name='" + name + '\'' + ", age=" + age + ", height=" + height + ", weight=" + weight + ", married=" + married + '}'; } }
1.5ToastUtil.java
package com.example.chapter06.util; 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(); } }
1.6效果：
1.6.1添加：
1.6.2删除：
1.6.3修改：
修改前：
修改中：
修改效果：
1.6.4查询
1.6.4.1查询所有：
1.6.4.2通过名字来查询：
