Android SQLite数据库---实现增删改查功能

最新推荐文章于 2023-04-12 21:41:58 发布

原创最新推荐文章于 2023-04-12 21:41:58 发布 · 880 阅读

6 ·

CC 4.0 BY-SA版权

文章标签：

#SQLite

本文详细介绍了一个基于SQLite的数据库管理系统的设计与实现，包括MemberInfo类的定义、DBHelper类的数据库创建与升级、DBManager类的业务逻辑封装，以及MainActivity与DisplayActivity的交互流程。文章深入解析了数据增删查改的具体实现，适合初学者和开发者参考。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

（1）MemberInfo.java，内容为会员信息

public class MemberInfo {
    private int _id;
    private String name;
    private int age;
    private String department;
    private String telephone;

    public MemberInfo() {
    }

    public MemberInfo(String name, int age, String department, String telephone) {
        this.name = name;
        this.age = age;
        this.department = department;
        this.telephone = telephone;
    }

    public int get_id() {
        return _id;
    }

    public void set_id(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 String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }

    public String getTelephone() {
        return telephone;
    }

    public void setTelephone(String telephone) {
        this.telephone = telephone;
    }
}

（2）DBHelper.java，管理数据库的创建和升级

public class DBHelper extends SQLiteOpenHelper {
    //类没有实例化,是不能用作父类构造器的参数,必须声明为静态
    public static final String DB_NAME = "Member.db";//数据库名称
    public static final String DB_TABLE_NAME = "info";//数据库表名称
    public static final int DB_VERSION = 1;//数据库版本名称

    public DBHelper (Context context){
        //第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类,
        // 设置为null,代表使用系统默认的工厂类
        super(context,DB_NAME,null,DB_VERSION);
    }
    //数据第一次创建的时候会调用onCreate
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建表
        db.execSQL("CREATE TABLE IF NOT EXISTS info" + "(_id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR,age INTEGER,department VARCHAR,telephone VARCHAR)");
        Log.i("SOLite","create table");
    }

    //当系统发现版本变化后调用
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i("WIRELESSQA","update sqlite"+oldVersion+"---->"+newVersion);
    }
}

（3） DBManager.java，封装了常用的业务方法

public class DBManager {
    private DBHelper helper;
    private SQLiteDatabase db;//操作数据库
    public DBManager (Context context){
        helper = new DBHelper(context);
        db = helper.getWritableDatabase();//打开可读写的数据库
    }
    //向表info添加成员信息
    public void add(List<MemberInfo> memberInfo){
        //批量处理sql语句，如何提交事务未成功，之前的sql语句是不会执行的
        db.beginTransaction();//开始事务
        try {
                for (MemberInfo info : memberInfo) {
                    Log.i("SQLite", "--------add memberInfo------");
                    Log.i("SQLite", info.getName() + "/" + info.getAge() + "/" + info.getDepartment() + "/" + info.getTelephone());
                    //向表中插入数据
                /*execSQL(String sql, Object[] bindArgs)方法的第一个参数为SQL语句，
                第二个参数为SQL语句中占位符参数的值，参数值在数组中的顺序要和占位符的位置对应
                 */
                    db.execSQL("insert into info  values(null,?,?,?,?)", new Object[]{info.getName(), info.getAge(), info.getDepartment(), info.getTelephone()});
                    db.setTransactionSuccessful();//事务成功
                }
        }finally {
            db.endTransaction();//结束事务
        }
    }
    public void add(int _id,String name,String age,String department,String telephone){
        Log.i("SQLite","------------add data----------");
        ContentValues cv = new ContentValues();
        cv.put("name",name);
        cv.put("age",age);
        cv.put("department",department);
        cv.put("telephone",telephone);
        //不管第三个参数是否包含数据，执行Insert()方法必然会添加一条记录，
        // 如果第三个参数为空，会添加一条除主键之外其他字段值为Null的记录
        db.insert(DBHelper.DB_TABLE_NAME,null,cv);
        Log.i("SQLite",name+"/"+age+"/"+department+"/"+telephone);
    }

    //通过name删除数据
    public void delData(String name){
        //String [] args = {name};
        db.delete(DBHelper.DB_TABLE_NAME,"name = ?",new String[] {name});
        Log.i("SQLite","delete data by"+name);
    }

    //清空数据
    public void clearData(){
        String sql = "delete from info";
        ExecSQL(sql);
        Log.i("SQLite","clear data");
    }

    //通过名字查询信息，返回所有的数据
    public ArrayList<MemberInfo> searchData (final String name){
        String sql = "select * from info where name = "+"'"+name+"'";
        return ExecSQLForMemberInfo(sql);
    }

    public ArrayList<MemberInfo> searchAllData(){
        String sql = "select * from info";
        return ExecSQLForMemberInfo(sql);
    }

    //通过名字来修改值
    public void updateData(String raw,String rawValue,String whereName){
        String sql = "update info set "+raw+"="+" "+"'"+rawValue+"'"+"where name ="+"'"+whereName+"'";
        ExecSQL(sql);
    }
    //执行SQL命令返回
    private ArrayList<MemberInfo> ExecSQLForMemberInfo(String sql){
        ArrayList<MemberInfo>  list = new ArrayList<MemberInfo>();
        Cursor c = ExecSQLForCursor(sql);
        while (c.moveToNext()){
            MemberInfo info = new MemberInfo();
            info.set_id(c.getInt(c.getColumnIndex("_id")));
            info.setName(c.getString(c.getColumnIndex("name")));
            info.setAge(c.getInt(c.getColumnIndex("age")));
            info.setDepartment(c.getString(c.getColumnIndex("department")));
            info.setTelephone(c.getString(c.getColumnIndex("telephone")));
            list.add(info);
        }
        c.close();
        return list;
    }

    //执行一个SQL语句
    private void ExecSQL(String sql){
        db.execSQL(sql);
    }

    //执行SQL,返回一个游标
    private Cursor ExecSQLForCursor(String sql){
        //rawQuery()方法的第一个参数为select语句；第二个参数为select语句中占位符参数的值，
        // 如果select语句没有使用占位符，该参数可以设置为null。
        Cursor c= db.rawQuery(sql,null);
        return c;
    }

   
}

（4）MainActivity.java

public class MainActivity extends AppCompatActivity implements View.OnClickListener{

    private EditText et_name, et_age, et_department, et_telephone;
    private Button searchAll, clear, add, delete, search, update;
    private String name;
    private int age;
    private String department;
    private String telephone;
    private DBManager dbManager;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        bindView();
        dbManager = new DBManager(this);
        searchAll.setOnClickListener(this);
        clear.setOnClickListener(this);
        add.setOnClickListener(this);
        delete.setOnClickListener(this);
        search.setOnClickListener(this);
        update.setOnClickListener(this);
    }

    public void bindView() {
        et_name = (EditText) findViewById(R.id.et_name);
        et_age = (EditText) findViewById(R.id.et_age);
        et_department = (EditText) findViewById(R.id.et_department);
        et_telephone = (EditText) findViewById(R.id.et_tel);

        searchAll = (Button) findViewById(R.id.searchAll);
        clear = (Button) findViewById(R.id.clearAll);
        add = (Button) findViewById(R.id.add);
        delete = (Button) findViewById(R.id.delete);
        search = (Button) findViewById(R.id.search);
        update = (Button) findViewById(R.id.update);
    }

    @Override
    public void onClick(View v) {
        switch (v.getId()) {
            case R.id.searchAll: {
                ArrayList<MemberInfo> infoList = new ArrayList<MemberInfo>();
                infoList = dbManager.searchAllData();
                String result = "";
                for (MemberInfo info : infoList) {
                    result = result + String.valueOf(info.get_id() + "|" + String.valueOf(info.getName() + "|" + String.valueOf(info.getDepartment() + "|" + String.valueOf(info.getTelephone()))));
                    result = result + "-------------------------------" + "\n";
                }
                startDisplayActivity("searchResult", result);
            }
                break;
            case R.id.clearAll:
                dbManager.clearData();
                break;
            case R.id.add: {
                name = et_name.getText().toString();
                age = Integer.valueOf(et_age.getText().toString());
                department = et_department.getText().toString();
                telephone = et_telephone.getText().toString();
                MemberInfo memberInfo = new MemberInfo(name, age, department, telephone);
                ArrayList<MemberInfo> infoList = new ArrayList<MemberInfo>();
                infoList.add(memberInfo);
                dbManager.add(infoList);
                break;
            }


            case R.id.delete:
                name = et_name.getText().toString();
                dbManager.delData(name);
                break;
            case R.id.update:
                name = et_name.getText().toString();
                age = Integer.valueOf(et_age.getText().toString());
                department = et_department.getText().toString();
                telephone = et_telephone.getText().toString();
                if (name == null){
                    Toast.makeText(getApplicationContext(),"name不能为空",Toast.LENGTH_LONG).show();
                }else {
                    dbManager.updateData("age",String.valueOf(age),name);
                    dbManager.updateData("department",department,name);
                    dbManager.updateData("telephone",telephone,name);
                }
                break;
            case R.id.search:
                name = et_name.getText().toString();
                if (name == null){
                    Toast.makeText(getApplicationContext(),"name不能为空",Toast.LENGTH_LONG).show();
                }else {
                    ArrayList<MemberInfo> infoList = new ArrayList<MemberInfo>();
                    infoList = dbManager.searchData(name);
                    String result = "";
                    for (MemberInfo info : infoList) {
                        result = result + String.valueOf(info.get_id() + "|" + String.valueOf(info.getName() + "|" + String.valueOf(info.getDepartment() + "|" + String.valueOf(info.getTelephone()))));
                        result = result + "-------------------------------" + "\n";
                    }
                    startDisplayActivity("searchResult",result);
                }
                break;
        }
    }

    private void startDisplayActivity(String intentName,String intentValue){
        Intent intent = new Intent(MainActivity.this,DisplayActivity.class);
        intent.putExtra(intentName,intentValue);
        startActivity(intent);
    }
}

（6）DisplayActivity.java，查询会员的结果

public class DisplayActivity extends AppCompatActivity {

    private TextView display;
    private String result;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_display);
        display = (TextView) findViewById(R.id.tv_show);
        Bundle extras = getIntent().getExtras();
        result = extras.getString("searchResult");
        display.setText(result);
    }
}

（7）activity_main.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="com.example.administrator.member_manager.MainActivity">

    <EditText
        android:id="@+id/et_name"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="姓名"/>
    <EditText
        android:id="@+id/et_age"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="年龄"/>
    <EditText
        android:id="@+id/et_department"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="部门"/>
    <EditText
        android:id="@+id/et_tel"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="电话"/>
    <Button
        android:id="@+id/searchAll"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查看数据库"/>
    <Button
        android:id="@+id/clearAll"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="清空数据库"/>
    <Button
        android:id="@+id/add"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="增加会员"/>
    <Button
        android:id="@+id/delete"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除会员"/>
    <Button
    android:id="@+id/update"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:text="更新会员"/>
    <Button
        android:id="@+id/search"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查找会员"/>
</LinearLayout>

（8）运行结果