SQLite实时增删改查

fanfan

发布于 2022-05-07 13:46:18

1.2K0

发布于 2022-05-07 13:46:18

文章被收录于专栏：编程思想之路编程思想之路

基础知识：

先介绍一点儿基础知识

SQLite的增删改查以及创建表语句：

1，创建表语句：

 //CREATE TABLE+表名（字段列表）
<pre name="code" class="java">db.execSQL("CREATE TABLE Employee(EmployeeId integer primary key autoincrement,name varchar(20),
age varchar(20))");

2，增加一条数据语句：

<pre name="code" class="java">//如果像这样一个语句通过加号连接起来，一定要注意空格问题，
//insert into 表名 （字段列表）values（？,？） 
 db.execSQL("insert into "+tableName+" (name,age) values(?,?) ",new String[]{name,age});

否则会出现如下问题

﹕ FATAL EXCEPTION: main
    android.database.sqlite.SQLiteException: near "intoEmployee": syntax error (code 1): , 
        while compiling: insert intoEmployee(name,age) values(?,?)

3，删除一条数据：

//delete from 表名 where 字段=?
db.execSQL("delete from "+tableName+" where name=?",new String[]{name});

4，查询一条数据：

//select * from 表名 where 字段=？
db.rawQuery("select * from "+tableName+" where name = ?",new String[]{name});

5，修改一条数据：

//update 表名 set 字段=？，字段=？ where name = ?,以上代码出现的数组所传的参数与问号相对应，很容易理解
db.execSQL("update "+tableName+" set name=? , age=? where name=?",new String[]{newName,newAge,oldName});

至于要调用那些增删改查的方法，实质上就是在拼接这些基本语句，只要掌握住这些基本语句，那些方法要传什么参数也就很好理解了

上述的语句是根本，掌握了他们就可以很好的举一反三了

比如，我们一般会调用的数据库增删改查方法为：

db.insert(tableName, null, values);//增加一条数据<pre name="code" class="java">db.query(tableName, null, "name=?", new String[]{name}, null, null, null);//查询一条数据

db.delete(tableName, "name=?", new String[]{name});//删除一条数据
<pre name="code" class="java">db.update(tableName, values, "name=?", new String[]{oldName});//更新一条数据

实例展示：

先看一下我的实例运行效果吧，实现了增删改查并且实时刷新的功能，以及item 的点击事件

item点击时，弹出所选中的姓名和年龄：

如查询一条语句，并且显示出来：

如果所查询的语句不存在，则在显示搜索结果的地方显示未搜索到结果

增加一条数据和删除一条数据以及更新一条语句都可以进行实时更新，不需要手动刷新

实例源码：

只给效果图不给源码的不是好博主，上源码：

首先

MySQLiteHelper.java文件

public class MySQLiteHelper extends SQLiteOpenHelper {


    private static String versionName = "Employee";
    private static int versionCode = 1;



    public MySQLiteHelper(Context context) {
        super(context, versionName, null, versionCode);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE Employee (EmployeeId integer primary key autoincrement,name varchar(20),age varchar(20))");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //更新数据库操作
    }
}

以及自己创建的personbean数据：

public class PersonBean {
    private String name;
    private String age;

    public PersonBean() {
    }

    public PersonBean(String name, String age) {
        this.name = name;
        this.age = age;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }
}

对personbean集成的增删改查类

PersonSQLite.java

public class PersonSQLite {

    private SQLiteOpenHelper mDBOpenHelper;
    private SQLiteDatabase db;
    private Context mContext;
    private String tableName;

    public PersonSQLite(Context context,String tableName) {
        this.mContext = context;
        mDBOpenHelper = new MySQLiteHelper(context);
        this.tableName = tableName;

    }

    /**
     * 增加一条数据，
     *
     * @param name
     * @param age
     * @return 当i为-1时则表示添加失败
     */
    public long insert(String name, String age) {
        long i = -1;
        db = mDBOpenHelper.getWritableDatabase();
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("name", name);
            values.put("age", age);
            i = db.insert(tableName, null, values);
            //如果像这样一个语句通过加号连接起来，一定要注意空格问题，
//            db.execSQL("insert into "+tableName+" (name,age) values(?,?) ",new String[]{name,age});
            db.close();
        }
        return i;
    }

    /**
     * 删除一条数据
     *
     * @param name
     * @return
     */
    public boolean delete(String name) {
        db = mDBOpenHelper.getWritableDatabase();
        if (db.isOpen()) {
//            db.execSQL("delete from "+tableName+" where name =?",new String[]{name});
            db.delete(tableName, "name=?", new String[]{name});
            db.close();
            return true;
        }
        return false;
    }


    /**
     * 更新操作，
     *
     * @param oldName
     * @param newName
     * @param newAge
     * @return
     */
    public int update(String oldName, String newName, String newAge) {
        db = mDBOpenHelper.getWritableDatabase();
        int i = -1;
        if (db.isOpen()) {
            ContentValues values = new ContentValues();
            values.put("name", newName);
            values.put("age", newAge);
            i = db.update(tableName, values, "name=?", new String[]{oldName});
//            db.execSQL("update "+tableName+" set name=? , age=? where name=?",new String[]{newName,newAge,oldName});
            db.close();
        }
        return i;
    }

    /**
     * 查询数据的操作
     *
     * @param name
     * @return 返回查询到的数据
     */
    public PersonBean query(String name) {
        db = mDBOpenHelper.getReadableDatabase();
        PersonBean personBean = null;
        if (db.isOpen()) {
//            Cursor cursor = db.rawQuery("select * from "+tableName+" where name = ?",new String[]{name});
            Cursor cursor = db.query(tableName, null, "name=?", new String[]{name}, null, null, null);
            if (cursor.moveToFirst()) {
                personBean = new PersonBean();
                int nameIndex = cursor.getColumnIndex("name");
                int ageIndex = cursor.getColumnIndex("age");
                String nameStr = cursor.getString(nameIndex);
                String ageStr = cursor.getString(ageIndex);
                personBean.setAge(ageStr);
                personBean.setName(nameStr);
                cursor.close();
            }
            db.close();
        }
        return personBean;
    }


    /**
     * 查询数据库中的所有数据
     *
     * @return
     */
    public List<PersonBean> queryAll() {
        db = mDBOpenHelper.getReadableDatabase();
        List<PersonBean> personBeanList = null;
        if (db.isOpen()) {
            personBeanList = new ArrayList<>();
            Cursor cursor = db.query(tableName, null, null, null, null, null, null);
            while (cursor.moveToNext()) {
                PersonBean personBean = new PersonBean();
                int nameIndex = cursor.getColumnIndex("name");
                int ageIndex = cursor.getColumnIndex("age");
                String nameStr = cursor.getString(nameIndex);
                String ageStr = cursor.getString(ageIndex);
                personBean.setName(nameStr);
                personBean.setAge(ageStr);
                personBeanList.add(personBean);
            }
            cursor.close();
            db.close();
        }
        return personBeanList;
    }
}

接下来就是主程序了：

public class MySQLActivity extends Activity implements View.OnClickListener {

    private RecyclerView mRecyclerView;
    private MySQLRecyclerViewAdapter mAdapter;
    private static List<PersonBean> personBeanList = new ArrayList<>();
    private PersonSQLite personSQLite;




    private EditText mSearchEt;
    private TextView mSearchTv;

    private EditText mSqlDataEt;
    private TextView mInsert;
    private TextView mDelete;
    private TextView mUpdate;

    private EditText mSearchResultEt;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_mysql);


        mSearchEt = (EditText) findViewById(R.id.search_et);
        mSearchTv = (TextView) findViewById(R.id.search_tv);

        mRecyclerView = (RecyclerView) findViewById(R.id.rl_sql_data);
        mRecyclerView.setLayoutManager(new LinearLayoutManager(this));
        personSQLite = new PersonSQLite(this, "Employee");




        mSqlDataEt = (EditText) findViewById(R.id.sql_data_et);
        mInsert = (TextView) findViewById(R.id.insert);
        mDelete = (TextView) findViewById(R.id.delete);
        mUpdate = (TextView) findViewById(R.id.update);

        mSearchResultEt = (EditText) findViewById(R.id.search_data_result_et);
        setData();
        mRecyclerView.setAdapter(mAdapter);
        setOnClickListener(mSearchTv, mInsert, mDelete, mUpdate);

    }

    private void setData(){
        personBeanList = personSQLite.queryAll();
        mAdapter = new MySQLRecyclerViewAdapter(MySQLActivity.this);
        mAdapter.resetData(personBeanList);
    }




    @Override
    protected void onResume() {
        super.onResume();

    }

    @Override
    public void onClick(View v) {
        PersonBean personBean = null;
        String mSearchStr = mSearchEt.getText().toString().trim();
        String mSqlDataStr = mSqlDataEt.getText().toString().trim();
        int i = -1;
        switch (v.getId()) {
            case R.id.search_tv:
                personBean = personSQLite.query(mSearchStr);
                if (personBean != null) {
                    mSearchResultEt.setText("搜索结果为：姓名为："+personBean.getName()+"年龄为："+personBean.getAge());
                }else {
                    mSearchResultEt.setText("未能搜索到相关信息");
                }
                break;
            case R.id.insert:
                if (personSQLite.query(mSqlDataStr)!=null){
                    Toast.makeText(getApplicationContext(), "禁止重复添加数据", Toast.LENGTH_LONG).show();
                    return;
                }
                personSQLite.insert(mSqlDataStr, "100");
                break;
            case R.id.delete:
                personBean = personSQLite.query(mSqlDataStr);
                if (null == personBean ){
                    Toast.makeText(getApplicationContext(), "要删除的数据不存在", Toast.LENGTH_LONG).show();
                    return;
                }
                personSQLite.delete(mSqlDataStr);
                break;
            case R.id.update:
                personBean = personSQLite.query(mSqlDataStr);
                if (null == personBean){
                    Toast.makeText(getApplicationContext(), "要更新的数据不存在", Toast.LENGTH_LONG).show();
                    return;
                }
                personSQLite.update(mSqlDataStr, "更新后的姓名", "更新后的年龄");
                break;
            default:
                break;
        }

        mSearchEt.setText("");
        mSqlDataEt.setText("");
        personBeanList = personSQLite.queryAll();
        mAdapter.resetData(personBeanList);

    }

    private void setOnClickListener(View... views) {
        for (View view : views) {
            if (view != null) {
                view.setOnClickListener(MySQLActivity.this);
            }
        }
    }
}

因为用到的是Recyclerview，所以还需要自定义适配器adapter

public class MySQLRecyclerViewAdapter extends RecyclerView.Adapter<RecyclerView.ViewHolder> implements View.OnClickListener{

    private Context context;
    private List<PersonBean> personBeanList;

    public MySQLRecyclerViewAdapter(Context context) {
        this.context = context;
        personBeanList = new ArrayList<>();
    }

//每次数据改变时调用该方法，可以实现实时刷新效果
    public void resetData(List<PersonBean> list){
        personBeanList.clear();
        personBeanList.addAll(list);
        notifyDataSetChanged();
    }
    @Override
    public RecyclerView.ViewHolder onCreateViewHolder(ViewGroup parent, int viewType) {
        View view = View.inflate(context,R.layout.list_item_layout,null);
        return new ContentViewHolder(view);
    }

    @Override
    public void onBindViewHolder(RecyclerView.ViewHolder holder, final int position) {
        if (holder instanceof ContentViewHolder){
            ContentViewHolder contentViewHolder = (ContentViewHolder) holder;
            contentViewHolder.mName.setText("姓名："+personBeanList.get(position).getName());
            contentViewHolder.mAge.setText("年龄："+personBeanList.get(position).getAge());
            contentViewHolder.container.setTag(R.id.tag_age, personBeanList.get(position).getAge());
            contentViewHolder.container.setTag(R.id.tag_name,personBeanList.get(position).getName());
            contentViewHolder.container.setOnClickListener(this);
        }
    }

    @Override
    public int getItemCount() {
        return personBeanList.size();
    }

    @Override
    public void onClick(View v) {
        String name = (String) v.getTag(R.id.tag_name);
        String age = (String) v.getTag(R.id.tag_age);
        Toast.makeText(context,"姓名："+name+",年龄"+age,Toast.LENGTH_LONG).show();
    }

    private class ContentViewHolder extends RecyclerView.ViewHolder {

        TextView mName;
        TextView mAge;
        View container;
        public ContentViewHolder(View itemView) {
            super(itemView);
            container = itemView;
            mName = (TextView) itemView.findViewById(R.id.name);
            mAge = (TextView) itemView.findViewById(R.id.age);
        }
    }
}

至于布局文件，很简单，就不往上贴了，希望对大家有所帮助，

最后别忘了添加权限

<uses-permission android:name="android.permission.MOUNT_UNMOUNT_FILESYSTEMS"/>
    <uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE"/>
    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE"/>

附上demo下载地址 http://download.csdn.net/detail/zrf1335348191/9454485

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2016-01-11，如有侵权请联系 cloudcommunity@tencent.com 删除

sqlite