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;
}