android读写数据库

SQLiteWriteActivity

package cn.edu.zufe.app002;

import android.os.Bundle;
import android.view.View;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Spinner;

import androidx.appcompat.app.AppCompatActivity;

import cn.edu.zufe.app002.bean.UserInfo;
import cn.edu.zufe.app002.database.UserDBHelper;
import cn.edu.zufe.app002.util.DateUtil;

public class SQLiteWriteActivity extends AppCompatActivity implements View.OnClickListener{

    private EditText etUsername;
    private EditText etAge;
    private EditText etHeight;
    private EditText etWeight;
    private Spinner spSex;
    private Button btnSave;
    private Button btnDelete;
    private Button btnUpdate;

    private ArrayAdapter<CharSequence> adapter;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite_write);

        etUsername = (EditText) findViewById(R.id.et_username);
        etAge = (EditText) findViewById(R.id.et_age);
        etHeight = (EditText) findViewById(R.id.et_height);
        etWeight = (EditText) findViewById(R.id.et_weight);
        spSex = (Spinner) findViewById(R.id.sp_sex);
        btnSave = (Button) findViewById(R.id.btn_save);
        btnDelete = (Button) findViewById(R.id.btn_delete);
        btnUpdate = (Button) findViewById(R.id.btn_update);

        adapter = ArrayAdapter.createFromResource(this, R.array.sex, android.R.layout.simple_list_item_1);
        spSex.setAdapter(adapter);
        btnSave.setOnClickListener(this);
        btnDelete.setOnClickListener(this);
        btnUpdate.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        if(v.getId() == R.id.btn_save) {
            UserInfo userInfo = new UserInfo();
            userInfo.setName(etUsername.getText().toString());
            userInfo.setAge(Integer.parseInt(etAge.getText().toString()));
            userInfo.setHeight(Integer.parseInt(etHeight.getText().toString()));
            userInfo.setWeight(Float.parseFloat(etWeight.getText().toString()));
            userInfo.setSex(spSex.getSelectedItem().toString());
            userInfo.setUpdateTime(DateUtil.getNowDateTime("yyyy-MM-dd HH:mm:ss"));
            UserDBHelper.insert(this, userInfo);
        } else if(v.getId() == R.id.btn_delete) {
            UserDBHelper.delete(this, String.format("name='%s'",etUsername.getText().toString()));
        } else if(v.getId() == R.id.btn_update) {
            UserInfo userInfo = new UserInfo();
            userInfo.setName(etUsername.getText().toString());
            userInfo.setAge(Integer.parseInt(etAge.getText().toString()));
            userInfo.setHeight(Integer.parseInt(etHeight.getText().toString()));
            userInfo.setWeight(Float.parseFloat(etWeight.getText().toString()));
            userInfo.setSex(spSex.getSelectedItem().toString());
            userInfo.setUpdateTime(DateUtil.getNowDateTime("yyyy-MM-dd HH:mm:ss"));
            UserDBHelper.update(this, userInfo, String.format("name='%s'",etUsername.getText().toString()));
        }
    }
}

activity_sqlite_write

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".SQLiteWriteActivity">

    <TextView
        android:id="@+id/tv_username"
        android:layout_width="50dp"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="10dp"
        android:text="姓名:"
        android:textSize="17sp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <EditText
        android:id="@+id/et_username"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_margin="10dp"
        android:textSize="17sp"
        app:layout_constraintBaseline_toBaselineOf="@+id/tv_username"
        app:layout_constraintHorizontal_weight="1"
        app:layout_constraintLeft_toRightOf="@+id/tv_username"
        app:layout_constraintRight_toRightOf="parent" />

    <TextView
        android:id="@+id/tv_age"
        android:layout_width="50dp"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="10dp"
        android:text="年龄:"
        android:textSize="17sp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/tv_username" />

    <EditText
        android:id="@+id/et_age"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_margin="10dp"
        android:textSize="17sp"
        app:layout_constraintBaseline_toBaselineOf="@+id/tv_age"
        app:layout_constraintHorizontal_weight="1"
        app:layout_constraintLeft_toRightOf="@+id/tv_age"
        app:layout_constraintRight_toRightOf="parent" />

    <TextView
        android:id="@+id/tv_height"
        android:layout_width="50dp"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="10dp"
        android:text="身高:"
        android:textSize="17sp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/tv_age" />

    <EditText
        android:id="@+id/et_height"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_margin="10dp"
        android:textSize="17sp"
        app:layout_constraintBaseline_toBaselineOf="@+id/tv_height"
        app:layout_constraintHorizontal_weight="1"
        app:layout_constraintLeft_toRightOf="@+id/tv_height"
        app:layout_constraintRight_toRightOf="parent" />

    <TextView
        android:id="@+id/tv_weight"
        android:layout_width="50dp"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="10dp"
        android:text="体重:"
        android:textSize="17sp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/tv_height" />

    <EditText
        android:id="@+id/et_weight"
        android:layout_width="0dp"
        android:layout_height="wrap_content"
        android:layout_margin="10dp"
        android:textSize="17sp"
        app:layout_constraintBaseline_toBaselineOf="@+id/tv_weight"
        app:layout_constraintHorizontal_weight="1"
        app:layout_constraintLeft_toRightOf="@+id/tv_weight"
        app:layout_constraintRight_toRightOf="parent" />

    <TextView
        android:id="@+id/tv_sex"
        android:layout_width="50dp"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="20dp"
        android:layout_marginRight="10dp"
        android:text="性别:"
        android:textSize="17sp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/tv_weight" />

    <Spinner
        android:id="@+id/sp_sex"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_margin="10dp"
        app:layout_constraintLeft_toRightOf="@+id/tv_sex"
        app:layout_constraintTop_toBottomOf="@+id/et_weight" />

    <Button
        android:id="@+id/btn_save"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="保存到数据库"
        android:textSize="17sp"
        android:layout_margin="20dp"
        app:layout_constraintTop_toBottomOf="@+id/tv_sex" />

    <Button
        android:id="@+id/btn_delete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除用户"
        android:textSize="17sp"
        android:layout_margin="20dp"
        app:layout_constraintTop_toBottomOf="@+id/btn_save" />

    <Button
        android:id="@+id/btn_update"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="修改用户信息"
        android:textSize="17sp"
        android:layout_margin="20dp"
        app:layout_constraintTop_toBottomOf="@+id/btn_delete" />

</androidx.constraintlayout.widget.ConstraintLayout>

UserInfo类

package cn.edu.zufe.app002.bean;

public class UserInfo {
    private int id;
    private String name;
    private int age;
    private long height;
    private float weight;
    private String sex;
    private String updateTime;
    private String phone;
    private String password;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public long getHeight() {
        return height;
    }

    public void setHeight(long height) {
        this.height = height;
    }

    public float getWeight() {
        return weight;
    }

    public void setWeight(float weight) {
        this.weight = weight;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(String updateTime) {
        this.updateTime = updateTime;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

}

UserDBHelper类

package cn.edu.zufe.app002.database;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

import androidx.annotation.Nullable;

import java.util.ArrayList;

import cn.edu.zufe.app002.bean.UserInfo;

public class UserDBHelper extends SQLiteOpenHelper {

    public static final String TAG = "cn.edu.zufe.app002.UserDBHelper";
    public UserDBHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String createTable = "create table user_info (id integer primary key autoincrement, name varchar(70), age integer, height long, weight float, sex varchar(70), update_time varchar(70), phone varchar(70), password varchar(70))";
        db.execSQL(createTable);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public static void insert(Context context, UserInfo userInfo) {
        UserDBHelper helper = new UserDBHelper(context, "user.db", null, 1);
        SQLiteDatabase database = helper.getWritableDatabase();
        if (query(context, String.format("name='%s'", userInfo.getName())).size() == 0) {
            database.execSQL("insert into user_info (name, age, height, weight, sex, update_time, phone, password) values (?, ?, ?, ?, ?, ?, ?, ?)",
                    new String [] {userInfo.getName(), String.valueOf(userInfo.getAge()), String.valueOf(userInfo.getHeight()), String.valueOf(userInfo.getWeight()), userInfo.getSex(), userInfo.getUpdateTime(), userInfo.getPhone(), userInfo.getPassword()});
        }
    }

    public static void delete(Context context, String condition) {
        UserDBHelper helper = new UserDBHelper(context, "user.db", null, 1);
        SQLiteDatabase database = helper.getWritableDatabase();
        database.execSQL("delete from user_info where " + condition);
    }

    public static void update(Context context, UserInfo userInfo, String condition) {
        UserDBHelper helper = new UserDBHelper(context, "user.db", null, 1);
        SQLiteDatabase database = helper.getWritableDatabase();
        database.execSQL("update user_info set name = ?, age = ?, height = ?, weight = ?, sex = ?, update_time = ?, phone = ?, password = ? where " + condition,
                new String [] {userInfo.getName(), String.valueOf(userInfo.getAge()), String.valueOf(userInfo.getHeight()), String.valueOf(userInfo.getWeight()),
                userInfo.getSex(), userInfo.getUpdateTime(), userInfo.getPhone(), userInfo.getPassword()});
    }

    public static ArrayList<UserInfo> query(Context context, String condition) {
        ArrayList<UserInfo> users = new ArrayList<>();
        UserDBHelper helper = new UserDBHelper(context, "user.db", null, 1);
        SQLiteDatabase database = helper.getReadableDatabase();
        String sql = String.format("select * from user_info where %s", condition);
        Cursor cursor = database.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            UserInfo userInfo = new UserInfo();
            userInfo.setId(cursor.getInt(0));
            userInfo.setName(cursor.getString(1));
            userInfo.setAge(cursor.getInt(2));
            userInfo.setHeight(cursor.getLong(3));
            userInfo.setWeight(cursor.getFloat(4));
            userInfo.setSex(cursor.getString(5));
            userInfo.setUpdateTime(cursor.getString(6));
            userInfo.setPhone(cursor.getString(7));
            userInfo.setPassword(cursor.getString(8));
            users.add(userInfo);
        }
        cursor.close();
        return users;
    }

}

SQLiteReadActivity

package cn.edu.zufe.app002;

import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

import androidx.appcompat.app.AppCompatActivity;

import java.util.ArrayList;

import cn.edu.zufe.app002.bean.UserInfo;
import cn.edu.zufe.app002.database.UserDBHelper;

public class SQLiteReadActivity extends AppCompatActivity implements View.OnClickListener {

    private TextView tvRead;
    private Button btnRead;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_sqlite_read);

        tvRead = (TextView) findViewById(R.id.tv_read);
        btnRead = (Button) findViewById(R.id.btn_read);

        btnRead.setOnClickListener(this);
    }

    private void readSQLite() {
        ArrayList<UserInfo> users = new ArrayList<>();
        users = UserDBHelper.query(this, "1=1");
        String msg = String.format("数据库读取到%d条数据\n",users.size());
        for(int i=0; i<users.size(); i++) {
            UserInfo info = users.get(i);
            msg = String.format("%s第%d条数据\nid=%s\nname=%s\nage=%s\nheight=%s\nweight=%s\nsex=%s\nupdate_time=%s\nphone=%s\npassword=%s\n"
                    , msg, i, info.getId(), info.getName(), info.getAge(), info.getHeight(), info.getWeight(), info.getSex(), info.getUpdateTime(), info.getPhone(), info.getPassword()
            );
        }
        tvRead.setText(msg);
    }

    @Override
    public void onClick(View v) {
        if(v.getId() == R.id.btn_read) {
            readSQLite();
        }
    }
}

activity_sqlite_read

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".SQLiteReadActivity">

    <TextView
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/tv_read"
        android:textSize="17sp"
        android:layout_margin="10dp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintTop_toTopOf="parent" />

    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:id="@+id/btn_read"
        android:text="读取数据库信息"
        android:textSize="17sp"
        android:layout_margin="10dp"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/tv_read" />

</androidx.constraintlayout.widget.ConstraintLayout>