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

目录

• 一、案例演示
• 二、实现步骤
• 1、activity_main.xml
• 2、MainActivity.java
• 3、UserDao.java
• 4、User.java
• 5、SQLiteOpenHelper.java
• 总结

一、案例演示

二、实现步骤

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) {
        /**
         * //如果数据表存在就删除
         *         sqLiteDatabase.execSQL("drop table if exists users");
         *         sqLiteDatabase.execSQL("drop table if exists department");
         *         onCreate(sqLiteDatabase);
          */
        switch (i){
            case 1:sqLiteDatabase.execSQL(CREATE_DEPARTMENT);
        }

    }
}

总结

到此这篇关于Android数据库增删改查的文章就介绍到这了,更多相关Android数据库增删改查内容请搜索自由互联以前的文章或继续浏览下面的相关文章希望大家以后多多支持自由互联！