Android數(shù)據(jù)庫(kù)增刪改查實(shí)戰(zhàn)案例

更新時(shí)間：2023年04月19日 09:27:10 作者：微笑伴你而行

我們?cè)诰幊讨薪?jīng)常會(huì)遇到數(shù)據(jù)庫(kù)的操作,這篇文章主要給大家介紹了關(guān)于Android數(shù)據(jù)庫(kù)增刪改查的相關(guān)資料,文中通過(guò)圖文以及實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

一、案例演示

請(qǐng)?zhí)砑訄D片描述

二、實(shí)現(xiàn)步驟

1、activity_main.xml

頁(yè)面布局

代碼

<?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="用戶(hù)名："/>

    <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="查詢(xún)"
        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

通過(guò)點(diǎn)擊不同的按鈕，進(jìn)行不同的增刪改查操作

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:{
            //為了解決查詢(xún)重復(fù)問(wèn)題，需要先創(chuàng)建一個(gè)StringBuffer或者String類(lèi)型對(duì)象，用于存儲(chǔ)數(shù)據(jù)，存儲(chǔ)后在給控件賦值就可以解決
                ArrayList list=userDao.queryAll();
                StringBuffer buffer=new StringBuffer();
                if(list.size()==0){
                    tv_show.setText("沒(méi)有數(shù)據(jù)");
                }else {
                    for (int i=0;i<list.size();i++){
                        User user= (User) list.get(i);
                        buffer.append("id:" +user.getId()+
                                "用戶(hù)名:"+user.getUsername()+
                                "密碼:"+user.getPassword()+
                                "年齡:"+user.getAge()+"\n");
                    }
		                tv_show.setText(buffer);
                }
                break;
            }
        }
    }
}

3、UserDao.java

包含對(duì)數(shù)據(jù)庫(kù)的增刪改查方法

public class UserDao {
    private SQLiteOpenHelper helper;
    public UserDao(Context context){
        helper=new SQLiteOpenHelper(context,"user1",null,1);
    }
    //添加數(shù)據(jù)
    public long addUser(User user){
        //1.獲取數(shù)據(jù)庫(kù)對(duì)象
        SQLiteDatabase database=helper.getWritableDatabase();
        //那些列為空，可以設(shè)置為空
        ContentValues values=new ContentValues();
        //key是數(shù)據(jù)表的列名，value是要放進(jìn)去的值
        values.put("username",user.getUsername());
        values.put("password",user.getPassword());
        values.put("age",user.getAge());
        //第一個(gè)參數(shù)表明，第二個(gè)參數(shù)自動(dòng)賦值為null的列名，第三個(gè)參數(shù)數(shù)據(jù)
        //返回值long,插入成功行號(hào)，插入失敗-1
        long i=database.insert("users",null,values);
        //關(guān)閉數(shù)據(jù)庫(kù)
        database.close();
        return i;
    }
    //刪除
    public int deleteUser(String username){
        //1.獲取數(shù)據(jù)庫(kù)對(duì)象
        SQLiteDatabase database=helper.getWritableDatabase();
        //第一個(gè)參數(shù)表明，第二個(gè)參數(shù)為刪除條件，第三個(gè)參數(shù)為第二個(gè)參數(shù)中占位符所需值組成的字符串?dāng)?shù)組
        int i=database.delete("users","username=?",new String[]{username+""});
        //關(guān)閉數(shù)據(jù)庫(kù)
        database.close();
        return i;
    }
    //修改
    public int updateUser(User user){
        //1.獲取數(shù)據(jù)庫(kù)對(duì)象
        SQLiteDatabase database=helper.getWritableDatabase();
        //那些列為空，可以設(shè)置為空
        ContentValues values=new ContentValues();
        //key是數(shù)據(jù)表的列名，value是要放進(jìn)去的值
        values.put("username",user.getUsername());
        values.put("password",user.getPassword());
        values.put("age",user.getAge());
        //第一個(gè)參數(shù)表明，第二個(gè)參數(shù)新數(shù)據(jù)，第三個(gè)參數(shù)是條件
        int i=database.update("users",values,"username=?",new String[]{user.getUsername()});
        //關(guān)閉數(shù)據(jù)庫(kù)
        database.close();
        return i;
    }
    //查詢(xún)
    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;
    }
    //通過(guò)對(duì)Cursor對(duì)象遍歷查詢(xún)結(jié)果，并將其范圍為一個(gè)list集合
    private ArrayList convertFromCursor(Cursor cursor){
        ArrayList list=new ArrayList();
        if(cursor!=null&&cursor.moveToFirst()){
            //通過(guò)游標(biāo)遍歷這個(gè)集合
            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

實(shí)體類(lèi)對(duì)應(yīng)著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

創(chuàng)建表，更新表方法

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;
    }
    //創(chuàng)建數(shù)據(jù)表(只有在第一次創(chuàng)建數(shù)據(jù)庫(kù)的時(shí)候才會(huì)被調(diào)用)
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CREATE_TABLES);
        Toast.makeText(context, "success databases", Toast.LENGTH_SHORT).show();
    }
    //數(shù)據(jù)庫(kù)的更新（第一個(gè)參數(shù)數(shù)據(jù)庫(kù)對(duì)象，第二個(gè)參數(shù)舊版本號(hào)，第三個(gè)參數(shù)新版本號(hào)）
    //新版本號(hào)大于舊版本號(hào)就會(huì)調(diào)用onUpgrade方法
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        /**
         * //如果數(shù)據(jù)表存在就刪除
         *         sqLiteDatabase.execSQL("drop table if exists users");
         *         sqLiteDatabase.execSQL("drop table if exists department");
         *         onCreate(sqLiteDatabase);
          */
        switch (i){
            case 1:sqLiteDatabase.execSQL(CREATE_DEPARTMENT);
        }

    }
}