Android数据库增删改查实战案例

android数据库增删改查实例 android数据库增删改查代码 android中数据库的增删改查 2023-05-16 11:05:48 725人浏览八月长安

摘要

目录一、案例演示二、实现步骤1、activity_main.xml2、MainActivity.java3、UserDao.java4、User.java5、sqliteOpenHe

一、案例演示

请添加图片描述

二、实现步骤

1、activity_main.xml

页面布局

代码

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:Android="Http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    tools:context=".MainActivity">
    <TextView
        android:id="@+id/tv_username"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textSize="30sp"
        android:layout_marginTop="10dp"
        android:text="用户名："/>

    <EditText
        android:id="@+id/et_username"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_marginTop="10dp"
        android:layout_toRightOf="@id/tv_username"
        android:minLines="2" />


    <EditText
        android:id="@+id/et_passWord"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@id/et_username"
        android:layout_alignLeft="@id/et_username"
        android:layout_marginTop="10dp"
        android:inputType="textPassword"
        android:minLines="2"
        android:textSize="30sp" />

    <TextView
        android:id="@+id/tv_choiceB"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignTop="@id/et_password"
        android:textSize="30sp"
        android:text="密    码："/>

    <EditText
        android:id="@+id/et_age"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_below="@id/et_password"
        android:layout_alignLeft="@id/et_password"
        android:layout_marginTop="10dp"
        android:minLines="2" />

    <TextView
        android:id="@+id/tv_age"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_alignTop="@id/et_age"
        android:textSize="30sp"
        android:text="年    龄："/>

    <Button
        android:id="@+id/bt_save"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@id/tv_age"
        android:layout_alignTop="@id/bt_query"
        android:text="保存"
        android:textSize="25sp" />

    <Button
        android:id="@+id/bt_update"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="修改"
        android:layout_toRightOf="@id/bt_query"
        android:layout_below="@id/et_age"
        android:layout_marginTop="10dp"
        android:layout_marginLeft="10dp"
        android:textSize="25sp"
        />

    <Button
        android:id="@+id/bt_delete"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="删除"
        android:layout_toRightOf="@id/bt_update"
        android:layout_below="@id/et_age"
        android:layout_marginTop="10dp"
        android:layout_marginLeft="10dp"
        android:textSize="25sp"
        />

    <Button
        android:id="@+id/bt_query"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_below="@id/et_age"
        android:layout_marginLeft="10dp"
        android:layout_marginTop="10dp"
        android:layout_toRightOf="@id/bt_save"
        android:text="查询"
        android:textSize="25sp" />

    <TextView
        android:id="@+id/tv_show"
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:layout_below="@id/bt_save"
        android:textSize="25dp" />

</RelativeLayout>

2、MainActivity.java

通过点击不同的按钮，进行不同的增删改查操作

public class MainActivity extends AppCompatActivity implements View.OnClickListener {

    private SQLiteOpenHelper helper;
    private UserDao userDao;
    private User user;
    private EditText et_username,et_password,et_age;
    private Button bt_save,bt_query,bt_update,bt_delete;
    private TextView tv_show;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //初始化
        init();
        userDao=new UserDao(this);
    }

    public void init(){
        et_username = findViewById(R.id.et_username);
        et_password = findViewById(R.id.et_password);
        et_age = findViewById(R.id.et_age);
        bt_save = findViewById(R.id.bt_save);
        bt_query = findViewById(R.id.bt_query);
        bt_update = findViewById(R.id.bt_update);
        bt_delete = findViewById(R.id.bt_delete);
        tv_show = findViewById(R.id.tv_show);

        bt_save.setOnClickListener(this);
        bt_query.setOnClickListener(this);
        bt_update.setOnClickListener(this);
        bt_delete.setOnClickListener(this);
    }

    @Override
    public void onClick(View view) {
        switch (view.getId()){
            case R.id.bt_save:{
                user=new User(et_username.getText().toString(),et_password.getText().toString(),Integer.parseInt((et_age.getText().toString())));
                long i=userDao.addUser(user);
                if(i!=-1){
                    Toast.makeText(this, "添加成功", Toast.LENGTH_SHORT).show();
                }else {
                    Toast.makeText(this, "添加失败", Toast.LENGTH_SHORT).show();
                }
                break;
            }
            case R.id.bt_delete:{
                int i=  userDao.deleteUser(et_username.getText().toString());
                if(i!=0){
                    Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();
                }else {
                    Toast.makeText(this, "删除失败", Toast.LENGTH_SHORT).show();
                }
                break;
            }
            case R.id.bt_update:{
                user=new User(et_username.getText().toString(),et_password.getText().toString(),Integer.parseInt((et_age.getText().toString())));
                int i=  userDao.updateUser(user);
                if(i!=0){
                    Toast.makeText(this, "修改成功", Toast.LENGTH_SHORT).show();
                }else {
                    Toast.makeText(this, "修改失败", Toast.LENGTH_SHORT).show();
                }
                break;
            }
            case R.id.bt_query:{
            //为了解决查询重复问题，需要先创建一个StringBuffer或者String类型对象，用于存储数据，存储后在给控件赋值就可以解决
                ArrayList list=userDao.queryAll();
                StringBuffer buffer=new StringBuffer();
                if(list.size()==0){
                    tv_show.setText("没有数据");
                }else {
                    for (int i=0;i<list.size();i++){
                        User user= (User) list.get(i);
                        buffer.append("id:" +user.getId()+
                                "用户名:"+user.getUsername()+
                                "密码:"+user.getPassword()+
                                "年龄:"+user.getAge()+"\n");
                    }
		                tv_show.setText(buffer);
                }
                break;
            }
        }
    }
}

3、UserDao.java

包含对数据库的增删改查方法

public class UserDao {
    private SQLiteOpenHelper helper;
    public UserDao(Context context){
        helper=new SQLiteOpenHelper(context,"user1",null,1);
    }
    //添加数据
    public long addUser(User user){
        //1.获取数据库对象
        SQLiteDatabase database=helper.getWritableDatabase();
        //那些列为空，可以设置为空
        ContentValues values=new ContentValues();
        //key是数据表的列名，value是要放进去的值
        values.put("username",user.getUsername());
        values.put("password",user.getPassword());
        values.put("age",user.getAge());
        //第一个参数表明，第二个参数自动赋值为null的列名，第三个参数数据
        //返回值long,插入成功行号，插入失败-1
        long i=database.insert("users",null,values);
        //关闭数据库
        database.close();
        return i;
    }
    //删除
    public int deleteUser(String username){
        //1.获取数据库对象
        SQLiteDatabase database=helper.getWritableDatabase();
        //第一个参数表明，第二个参数为删除条件，第三个参数为第二个参数中占位符所需值组成的字符串数组
        int i=database.delete("users","username=?",new String[]{username+""});
        //关闭数据库
        database.close();
        return i;
    }
    //修改
    public int updateUser(User user){
        //1.获取数据库对象
        SQLiteDatabase database=helper.getWritableDatabase();
        //那些列为空，可以设置为空
        ContentValues values=new ContentValues();
        //key是数据表的列名，value是要放进去的值
        values.put("username",user.getUsername());
        values.put("password",user.getPassword());
        values.put("age",user.getAge());
        //第一个参数表明，第二个参数新数据，第三个参数是条件
        int i=database.update("users",values,"username=?",new String[]{user.getUsername()});
        //关闭数据库
        database.close();
        return i;
    }
    //查询
    public ArrayList queryAll(){
        ArrayList list=new ArrayList();
        SQLiteDatabase database=helper.getWritableDatabase();
        Cursor cursor=database.query("users",null,null,null,null,null,null);
        list=convertFromCursor(cursor);
        return list;
    }
    //通过对Cursor对象遍历查询结果，并将其范围为一个list集合
    private ArrayList convertFromCursor(Cursor cursor){
        ArrayList list=new ArrayList();
        if(cursor!=null&&cursor.moveToFirst()){
            //通过游标遍历这个集合
            do{
                int id=cursor.getInt(cursor.getColumnIndex("id"));
                String username=cursor.getString(cursor.getColumnIndex("username"));
                String password=cursor.getString(cursor.getColumnIndex("password"));
                int age=cursor.getInt(cursor.getColumnIndex("age"));
                User user=new User(id,username,password,age);
                list.add(user);
            }while (cursor.moveToNext());
        }
        return list;
    }
}

4、User.java

实体类对应着user表中的字段

public class User {
    private int id;
    private String username;
    private String password;
    private int age;

    public User(int id, String username, String password, int age) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.age = age;
    }

    public User(String username, String password, int age) {
        this.username = username;
        this.password = password;
        this.age = age;
    }
    public User(){};

    public int getId() {
        return id;
    }

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

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

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

    public int getAge() {
        return age;
    }

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

5、SQLiteOpenHelper.java

创建表，更新表方法

public class SQLiteOpenHelper extends android.database.sqlite.SQLiteOpenHelper {
    private Context context;
    public static final String CREATE_TABLES="create table users ("+
            "id integer primary key autoincrement,"+
            "username text,"+
            "password text,"+
            "age integer)";
    public static final String CREATE_DEPARTMENT="create table department ("+
            "id integer primary key autoincrement,"+
            "departmentName text,"+
            "departCode text)";
    public SQLiteOpenHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
        this.context=context;
    }
    //创建数据表(只有在第一次创建数据库的时候才会被调用)
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_TABLES);
        Toast.makeText(context, "success databases", Toast.LENGTH_SHORT).show();
    }
    //数据库的更新（第一个参数数据库对象，第二个参数旧版本号，第三个参数新版本号）
    //新版本号大于旧版本号就会调用onUpgrade方法
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        
        switch (i){
            case 1:sqLiteDatabase.execSQL(CREATE_DEPARTMENT);
        }

    }
}