SQLite数据库的增删改查

最近在学习关于SQLite的一些基本操作做了一个demo,这是做完的样子

上面是4个按钮，下面是listview用来显示查询到的结果
下面是代码
MainActivity:主要是做几个按钮的点击事件

public class MainActivity extends AppCompatActivity implements View.OnClickListener {
    private Context mContext;
    private Button add;
    private Button del;
    private Button update;
    private Button query;
    private ListView lv_databases;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mContext = this;

        //创建一个帮助类对象
        MySqliteOpenHelper mySqliteOpenHelper = new MySqliteOpenHelper(this);
        //调用getReadableDatabase方法来初始化数据库的创建
        SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();

        add = (Button) findViewById(R.id.bt_add);
        del = (Button) findViewById(R.id.bt_del);
        update = (Button) findViewById(R.id.bt_update);
        query = (Button) findViewById(R.id.bt_query);
        lv_databases = (ListView) findViewById(R.id.lv_databases);
        add.setOnClickListener(this);
        del.setOnClickListener(this);
        update.setOnClickListener(this);
        query.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {

        InfoDao infoDao = new InfoDao(mContext); //创建一个dao对象做增删改查
        switch (v.getId()) {
            case R.id.bt_add:

                InfoBean bean = new InfoBean();
                bean.name = "张三";
                bean.phone = "1387011254215";
                boolean result = infoDao.add(bean);
                if (result) {
                    Toast.makeText(mContext, "添加成功", Toast.LENGTH_SHORT).show();
                } else {
                    Toast.makeText(mContext, "添加失败", Toast.LENGTH_SHORT).show();
                }


                break;

            case R.id.bt_del:
                int del = infoDao.del("张三");
                Toast.makeText(mContext, "成功删除了"+del+"行", Toast.LENGTH_SHORT).show();
                break;

            case R.id.bt_update:
                InfoBean bean2 = new InfoBean();
                bean2.name = "张三";
                bean2.phone = "124555";
                int update = infoDao.update(bean2);
                Toast.makeText(mContext, "成功修改了"+update+"行", Toast.LENGTH_SHORT).show();
                break;

            case R.id.bt_query:
                ArrayList<InfoBean>  arrayList = infoDao.query("张三");
                QueryAdapter queryAdapter = new QueryAdapter(mContext, arrayList);
                lv_databases.setAdapter(queryAdapter);
                break;
        }
    }
}

MySqliteOpenHelper:用来创建数据库

public class MySqliteOpenHelper extends SQLiteOpenHelper {
    public MySqliteOpenHelper(Context context) {
        //context:上下文  name:数据库文件的名称  factory：用来创建curson对象，默认为null  version：数据库的版本号，从1开始，如果发生改变，onUpfrade方法调用
        super(context, "info.db", null, 1);
    }

    //onCreate方法是数据库第一次创建的时候被调用；特别适合做表结构的初始化，需要执行sql语句
    @Override
    public void onCreate(SQLiteDatabase db) {
        //通过SQLiteDatabase执行一个创建表的sql语句
        db.execSQL("create table info(_id integer primary key autoincrement,name varchar(20),phone varchar(11))");

    }


    //数据库版本号发生改变时才会执行；特别适合做表结构的修改
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

QueryAdapter:做listview的适配器

public class QueryAdapter extends BaseAdapter {

    private Context mContext;
    private ArrayList<InfoBean> arrayList;

    public QueryAdapter(Context mContext, ArrayList<InfoBean> arrayList) {
        this.mContext = mContext;
        this.arrayList = arrayList;
    }

    @Override
    public int getCount() {
        return arrayList.size();
    }

    @Override
    public Object getItem(int position) {
        return arrayList.get(position);
    }

    @Override
    public long getItemId(int position) {
        return position;
    }

    @Override
    public View getView(int position, View convertView, ViewGroup parent) {

        //复用convertView
        View view = null;
        if (convertView != null) {
            view = convertView;
        } else {
            view = View.inflate(mContext, R.layout.item, null);
        }

        //找到控件
        TextView item_tv_id = (TextView) view.findViewById(R.id.item_tv_id);
        TextView item_tv_name = (TextView) view.findViewById(R.id.item_tv_name);
        TextView item_tv_phone = (TextView) view.findViewById(R.id.item_tv_phone);

        //找到内容
        InfoBean bean = arrayList.get(position);

        //设置内容
        item_tv_id.setText(bean.id);
        item_tv_name.setText(bean.name);
        item_tv_phone.setText(bean.phone);

        return view;
    }
}

InfoDao:做数据库增删改查的逻辑

public class InfoDao {
    private MySqliteOpenHelper mySqliteOpenHelper;

    public InfoDao(Context context) {
        //创建一个帮助类对象
        mySqliteOpenHelper = new MySqliteOpenHelper(context);

    }

    public boolean add(InfoBean bean) {
        //执行sql语句需要sqliteDatabase对象
        //调用getReadableDatabase方法来初始化数据库的创建
        SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();

        ContentValues values = new ContentValues();//使用map封装的对象，用来存放值
        values.put("name", bean.name);
        values.put("phone", bean.phone);

        //参数 table：表名  nullColumnHack：可以为空，表示添加一个空行   valuse：数据一行的值   返回值：代表添加这个新行的id，-1表示添加失败
        long result = db.insert("info", null, values);
        db.close();
        return result != -1;
    }

    public int del(String name) {
        SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();

        //参数  whereClause：删除条件    whereArgs：条件的占位符参数    返回值：成功删除多少行
        int result = db.delete("info", "name = ?", new String[]{name});
        db.close();

        return result;
    }

    public int update(InfoBean bean) {
        SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();

        ContentValues values = new ContentValues();//使用map封装的对象，用来存放值
        values.put("phone", bean.phone);

        int result = db.update("info", values, "name=?", new String[]{bean.name});
        db.close();

        return result;
    }

    public ArrayList<InfoBean> query(String name) {

        ArrayList<InfoBean> list = new ArrayList<>();


        //执行sql语句需要SQLiteDatabase对象
        SQLiteDatabase db = mySqliteOpenHelper.getReadableDatabase();

        //参数 table：表名  columns：查询的列名，如果null则代表查村所有列  selection：查询条件  selectionArgs：条件占位符的参数值
        //groupBy：按什么字段分组  having：分组的条件  orderBy：按什么字段排序
        Cursor cursor = db.query("info", new String[]{"_id", "name", "phone"}, "name=?", new String[]{name}, null, null, "_id asc");

        //解析Cursor中的数据
        if (cursor != null && cursor.getCount() > 0) {//判断cursor中是否存在数据

            //循环遍历结果集，获取每一行的内容
            while (cursor.moveToNext()) {//条件，游标能否定位到下一行
                InfoBean bean = new InfoBean();
                //获取数据
                bean.id = cursor.getInt(0) + "";
                bean.name = cursor.getString(1);
                bean.phone = cursor.getString(2);

                list.add(bean);
            }
            cursor.close();//关闭结果集

        }
        db.close();
        return list;
    }
}

下面是增删改查的sql语句
insert into info(name,phone) values(‘王五’,’13777777’);
delete from info where name=’王五’;
update info set phone=’139999999’where name=’王五’;
select name,phone from info

最后一个封装属性的类
public class InfoBean {
public String id;
public String name;
public String phone;
}