Android 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)运行结果

       

里面的一些关于数据库的方法,可以参考这篇文章,写的很详细 

https://blog.youkuaiyun.com/vrix/article/details/7445823

 

 

 

评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值