博主前些天发现了一个巨牛的人工智能学习网站,通俗易懂,风趣幽默,忍不住也分享一下给大家,
👉点击跳转到网站
Android SQLite数据库相关API的介绍可以看这篇文章
Android SQLite数据库中基础的增删改查操作以及API的详解
效果如图所示:
一、向数据库中写入数据
1.首先创建数据库user和表user_info,详细的注释已经在代码中给出
public class UserDBHelper extends SQLiteOpenHelper {private static final String TAG = "UserDBHelper";//声明数据库帮助器的实例public static UserDBHelper userDBHelper = null;//声明数据库的实例private SQLiteDatabase db = null;//声明数据库的名称public static final String DB_NAME = "user.db";//声明表的名称public static final String TABLE_NAME = "user_info";//声明数据库的版本号public static int DB_VERSION = 1;public UserDBHelper(@Nullable Context context) {super(context, DB_NAME, null, DB_VERSION);}public UserDBHelper(@Nullable Context context, int version) {super(context, DB_NAME, null, version);}//利用单例模式获取数据库帮助器的实例public static UserDBHelper getInstance(Context context, int version) {if (userDBHelper == null && version > 0) {userDBHelper = new UserDBHelper(context, version);} else if (userDBHelper == null) {userDBHelper = new UserDBHelper(context);}return userDBHelper;}//打开数据库的写连接public SQLiteDatabase openWriteLink() {if (db == null || !db.isOpen()) {db = userDBHelper.getWritableDatabase();}return db;}//getWritableDatabase()与getReadableDatabase() 这两个方法都可以获取到数据库的连接//正常情况下没有区别,当手机存储空间不够了//getReadableDatabase()就不能进行插入操作了,执行插入没有效果//getWritableDatabase():也不能进行插入操作,如果执行插入数据的操作,则会抛异常。对于现在来说不会出现这种情况,用哪种方式都可以//打开数据库的读连接public SQLiteDatabase openReadLink() {if (db == null || !db.isOpen()) {db = userDBHelper.getReadableDatabase();}return db;}//关闭数据库的读连接public void closeLink() {if (db != null && db.isOpen()) {db.close();db = null;}}@Overridepublic void onCreate(SQLiteDatabase db) {//如果存在user_info表,则删除该表String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";db.execSQL(drop_sql);//创建user_info表String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + "("+ "_id INTEGER PRIMARY KEY AUTOINCREMENT,"+ "name VARCHAR NOT NULL,"+ "age INTEGER NOT NULL,"+ "height INTEGER NOT NULL,"+ "weight DECIMAL(10,2) NOT NULL,"+ "married INTEGER NOT NULL,"+ "update_time VARCHAR NOT NULL"+ ");";db.execSQL(create_sql);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {}//根据指定条件删除表记录public int delete(String condition) {// 执行删除记录动作,该语句返回删除记录的数目//参数一:表名//参数二:whereClause where子句//参数三:whereArgs 您可以在 where 子句中包含 ?s,// 它将被 whereArgs 中的值替换。这些值将绑定为字符串。return db.delete(TABLE_NAME, condition, null);}//删除该表所有记录public int deleteAll() {// 执行删除记录动作,该语句返回删除记录的数目return db.delete(TABLE_NAME, "1=1", null);}// 往该表添加一条记录public long insert(UserInfo userInfo) {List<UserInfo> infoList = new ArrayList<>();infoList.add(userInfo);return insert(infoList);}// 往该表添加多条记录public long insert(List<UserInfo> infoList) {long result = -1;for (int i = 0; i < infoList.size(); i++) {UserInfo userInfo = infoList.get(i);List<UserInfo> tempList = new ArrayList<>();// 如果存在同名记录,则更新记录// 注意条件语句的等号后面要用单引号括起来if (userInfo.name != null && userInfo.name.length() > 0) {String condition = String.format("name='%s'", userInfo.name);tempList = query(condition);if (tempList.size() > 0) {update(userInfo, condition);result = tempList.get(0).rowid;continue;}}// 不存在唯一性重复的记录,则插入新记录ContentValues cv = new ContentValues();cv.put("name", userInfo.name);cv.put("age", userInfo.age);cv.put("height", userInfo.height);cv.put("weight", userInfo.weight);cv.put("married", userInfo.married);cv.put("update_time", userInfo.update_time);// 执行插入记录动作,该语句返回插入记录的行号//参数二:参数未设置为NULL,参数提供可空列名称的名称,以便在 cv 为空的情况下显式插入 NULL。//参数三:values 此映射包含行的初始列值。键应该是列名,值应该是列值result = db.insert(TABLE_NAME, "", cv);// 添加成功则返回行号,添加失败则返回-1if (result == -1) {return result;}}return result;}//根据条件更新指定的表记录public int update(UserInfo userInfo, String condition) {ContentValues cv = new ContentValues();cv.put("name", userInfo.name);cv.put("age", userInfo.age);cv.put("height", userInfo.height);cv.put("weight", userInfo.weight);cv.put("married", userInfo.married);cv.put("update_time", userInfo.update_time);//执行更新记录动作,该语句返回更新的记录数量//参数二:values 从列名到新列值的映射//参数三:whereClause 更新时要应用的可选 WHERE 子句//参数四:whereArgs 您可以在 where 子句中包含 ?s,//它将被 whereArgs 中的值替换。这些值将绑定为字符串。return db.update(TABLE_NAME, cv, condition, null);}public int update(UserInfo userInfo) {// 执行更新记录动作,该语句返回更新的记录数量return update(userInfo, "rowid=" + userInfo.rowid);}public List<UserInfo> query(String condition) {String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time" +" from %s where %s;", TABLE_NAME, condition);List<UserInfo> infoList = new ArrayList<>();// 执行记录查询动作,该语句返回结果集的游标//参数一:SQL查询//参数二:selectionArgs//您可以在查询的 where 子句中包含 ?s,它将被 selectionArgs 中的值替换。这些值将绑定为字符串。Cursor cursor = db.rawQuery(sql, null);// 循环取出游标指向的每条记录while (cursor.moveToNext()) {UserInfo userInfo = new UserInfo();//Xxx getXxx(columnIndex):根据字段下标得到对应的值//int getColumnIndex():根据字段名得到对应的下标//cursor.getLong():以 long 形式返回所请求列的值。//getColumnIndex() 获取给定列名的从零开始的列索引,如果列名不存在返回-1userInfo.name = cursor.getString(cursor.getColumnIndex("name"));userInfo.age = cursor.getInt(cursor.getColumnIndex("age"));userInfo.height = cursor.getLong(cursor.getColumnIndex("height"));userInfo.weight = cursor.getFloat(cursor.getColumnIndex("weight"));//SQLite没有布尔型,用0表示false,用1表示trueuserInfo.married = (cursor.getInt(cursor.getColumnIndex("married")) == 0) ? false : true;userInfo.update_time = cursor.getString(cursor.getColumnIndex("update_time"));infoList.add(userInfo);}//查询完毕,关闭数据库游标cursor.close();return infoList;}
}
2.创建UserInfo实体类
//用户信息
public class UserInfo {public long rowid; // 行号public int xuhao; // 序号public String name; // 姓名public int age; // 年龄public long height; // 身高public float weight; // 体重public boolean married; // 婚否public String update_time; // 更新时间public UserInfo() {rowid = 0L;xuhao = 0;name = "";age = 0;height = 0L;weight = 0.0f;married = false;update_time = "";}
}
3.创建布局页面activity_sqilte_write.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"xmlns:app="http://schemas.android.com/apk/res-auto"xmlns:tools="http://schemas.android.com/tools"android:layout_width="match_parent"android:layout_height="match_parent"android:orientation="vertical"android:padding="5dp"tools:context=".SqilteWriteActivity"><RelativeLayoutandroid:layout_width="match_parent"android:layout_height="40dp"><TextViewandroid: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" /><EditTextandroid:id="@+id/edit_name"android:layout_width="match_parent"android:layout_height="match_parent"android:layout_marginTop="3dp"android:layout_marginBottom="3dp"android:layout_toRightOf="@+id/tv_name"android:background="@drawable/edit_style"android:gravity="center|left"android:hint="请输入姓名"android:inputType="text"android:maxLength="12"android:textSize="17sp" /></RelativeLayout><RelativeLayoutandroid:layout_width="match_parent"android:layout_height="40dp"><TextViewandroid: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" /><EditTextandroid:id="@+id/edit_age"android:layout_width="match_parent"android:layout_height="match_parent"android:layout_marginTop="3dp"android:layout_marginBottom="3dp"android:layout_toRightOf="@+id/tv_age"android:background="@drawable/edit_style"android:gravity="center|left"android:hint="请输入年龄"android:inputType="number"android:maxLength="2"android:textSize="17sp" /></RelativeLayout><RelativeLayoutandroid:layout_width="match_parent"android:layout_height="40dp"><TextViewandroid: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" /><EditTextandroid:id="@+id/edit_height"android:layout_width="match_parent"android:layout_height="match_parent"android:layout_marginTop="3dp"android:layout_marginBottom="3dp"android:layout_toRightOf="@+id/tv_height"android:background="@drawable/edit_style"android:gravity="center|left"android:hint="请输入身高"android:inputType="number"android:maxLength="3"android:textSize="17sp" /></RelativeLayout><RelativeLayoutandroid:layout_width="match_parent"android:layout_height="40dp"><TextViewandroid: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" /><EditTextandroid:id="@+id/edit_weight"android:layout_width="match_parent"android:layout_height="match_parent"android:layout_marginTop="3dp"android:layout_marginBottom="3dp"android:layout_toRightOf="@+id/tv_weight"android:background="@drawable/edit_style"android:gravity="center|left"android:hint="请输入体重"android:inputType="numberDecimal"android:maxLength="5"android:textSize="17sp" /></RelativeLayout><RelativeLayoutandroid:layout_width="match_parent"android:layout_height="40dp"><CheckBoxandroid:id="@+id/checkbox"android:layout_width="wrap_content"android:layout_height="match_parent"android:gravity="center" /><TextViewandroid:layout_width="wrap_content"android:layout_height="match_parent"android:layout_toRightOf="@+id/checkbox"android:gravity="center"android:text="已婚"android:textColor="@color/black"android:textSize="17sp" /></RelativeLayout><Buttonandroid:id="@+id/btn_save"android:layout_width="match_parent"android:layout_height="wrap_content"android:text="保存到数据库"android:textColor="@color/black"android:textSize="17sp" />
</LinearLayout>
4.创建SqilteWriteActivity类添加数据
public class SqilteWriteActivity extends AppCompatActivity implements View.OnClickListener, CompoundButton.OnCheckedChangeListener {private EditText edit_name;private EditText edit_age;private EditText edit_height;private EditText edit_weight;private CheckBox checkbox;private Button btn_save;private boolean isMarried = false;//声明数据库帮助器的对象private UserDBHelper userDBHelper;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_sqilte_write);edit_name = findViewById(R.id.edit_name);edit_age = findViewById(R.id.edit_age);edit_height = findViewById(R.id.edit_height);edit_weight = findViewById(R.id.edit_weight);checkbox = findViewById(R.id.checkbox);checkbox.setOnCheckedChangeListener(this);btn_save = findViewById(R.id.btn_save);btn_save.setOnClickListener(this);}@Overridepublic void onCheckedChanged(CompoundButton buttonView, boolean isChecked) {isMarried = isChecked;}@Overrideprotected void onStart() {super.onStart();userDBHelper = UserDBHelper.getInstance(this, 1);//打开数据库帮助器的写连接userDBHelper.openWriteLink();}@Overridepublic void onClick(View v) {if (v.getId() == R.id.btn_save) {String name = edit_name.getText().toString();String age = edit_age.getText().toString();String height = edit_height.getText().toString();String weight = edit_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 userInfo = new UserInfo();userInfo.name = name;userInfo.age = Integer.parseInt(age);userInfo.height = Long.parseLong(height);userInfo.weight = Float.parseFloat(weight);userInfo.married = isMarried;@SuppressLint("SimpleDateFormat")SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");String nowTime = sdf.format(new Date());userInfo.update_time = nowTime;userDBHelper.insert(userInfo);ToastUtil.show(this, "数据已写入SQLite数据库");}}@Overrideprotected void onStop() {super.onStop();userDBHelper.closeLink();}
}
以上就是添加数据的代码详解
二、读取数据库中的数据
1.布局页面activity_sqlite_read.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"xmlns:app="http://schemas.android.com/apk/res-auto"xmlns:tools="http://schemas.android.com/tools"android:layout_width="match_parent"android:layout_height="match_parent"android:orientation="vertical"tools:context=".SqliteReadActivity"><Buttonandroid:id="@+id/btn_delete_all"android:layout_width="match_parent"android:layout_height="wrap_content"android:text="删除所有记录"android:textColor="@color/black"android:textSize="17sp" /><TextViewandroid: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>
2.创建SqliteReadActivity类显示数据库中的数据,并且实现删除功能
public class SqliteReadActivity extends AppCompatActivity implements View.OnClickListener {private Button btn_delete_all;private TextView tv_sqlite;//声明数据库帮助器的实例private UserDBHelper userDBHelper;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_sqlite_read);btn_delete_all = findViewById(R.id.btn_delete_all);btn_delete_all.setOnClickListener(this);tv_sqlite = findViewById(R.id.tv_sqlite);}@Overrideprotected void onStart() {super.onStart();//获得数据库帮助器的实例userDBHelper = UserDBHelper.getInstance(this, 1);//打开数据库帮助器的读连接userDBHelper.openReadLink();// 读取数据库中保存的所有用户记录readSQLite();}@SuppressLint("DefaultLocale")private void readSQLite() {if (userDBHelper == null) {ToastUtil.show(this, "数据库连接为空!");return;}// 执行数据库帮助器的查询操作List<UserInfo> userInfoList = userDBHelper.query("1=1");String desc = String.format("数据库查询到%d条记录,详情如下:", userInfoList.size());for (int i = 0; i < userInfoList.size(); i++) {UserInfo userInfo = userInfoList.get(i);desc = String.format("%s\n第%d条记录信息如下:", desc, i + 1);desc = String.format("%s\n 姓名为%s", desc, userInfo.name);desc = String.format("%s\n 年龄为%d", desc, userInfo.age);desc = String.format("%s\n 身高为%d", desc, userInfo.height);desc = String.format("%s\n 体重为%f", desc, userInfo.weight);desc = String.format("%s\n 婚否为%b", desc, userInfo.married);desc = String.format("%s\n 更新时间为%s", desc, userInfo.update_time);}if (userInfoList.size() <= 0) {desc = "数据库查询到的记录为空";}tv_sqlite.setText(desc);}@Overridepublic void onClick(View v) {if (v.getId() == R.id.btn_delete_all) {//关闭数据库连接userDBHelper.closeLink();//打开数据库帮助器的写连接userDBHelper.openWriteLink();//删除所有记录userDBHelper.deleteAll();//关闭数据库连接userDBHelper.closeLink();//打开数据库帮助器的读连接userDBHelper.openReadLink();//读取数据库中保存的所有用户记录readSQLite();ToastUtil.show(this, "已删除所有记录");}}
}
以上就是显示数据库中的数据的详细步骤,和删除数据的步骤~