Sqlite数据库增删减改操作

效果图：

注意：需将数据表加载到sdcard文件夹中

manifest.xml:

//添加sd卡读写权限
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />
    <uses-permission android:name="android.permission.READ_EXTERNAL_STORAGE" />

activity_main.xml:

<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"
    >


    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="创建或打开数据库"
        android:onClick="createDB"
        android:id="@+id/btn1"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="插入数据"

        android:id="@+id/btn2"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="删除数据"

        android:id="@+id/btn3"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="修改数据"

        android:id="@+id/btn4"/>
    <Button
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="查询并显示数据"

        android:id="@+id/btn5"/>
</LinearLayout>

activity_list.xml:

<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent"
    android:layout_height="match_parent">
    <ListView
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:id="@+id/listview">
    </ListView>

</LinearLayout>

tabview.xml:

<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal" android:layout_width="match_parent"
    android:layout_height="match_parent"
    >
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="id:"
        android:id="@+id/id"/>
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="name:"
        android:layout_marginLeft="100dp"
        android:id="@+id/name"/>
    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="100dp"
        android:text="age:"
        android:id="@+id/age"
        />

</LinearLayout>

SqliteActivity.java:

public class SqliteActivity extends AppCompatActivity implements View.OnClickListener {

    private MysqliteHelper helper;
    private SQLiteDatabase db;
    private Button btn1;
    private Button btn2;
    private Button btn3;
    private Button btn4;
    private Button btn5;
    private Button btn6;


    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        //获取MysqliteHelper对象
        helper = DataBaseManger.getIntance(this);
        btn1= findViewById(R.id.btn1);
        btn2 = findViewById(R.id.btn2);
        btn3 = findViewById(R.id.btn3);
        btn4 = findViewById(R.id.btn4);
        btn5 = findViewById(R.id.btn5);



        btn2.setOnClickListener(this);
        btn3.setOnClickListener(this);
        btn4.setOnClickListener(this);
        btn5.setOnClickListener(this);

    }

    public void createDB(View view){
        /**
         * getReadableDatabase
         * getWritableDatabase
         * 这两个方法的作用是创建或打开数据库 如果数据库存在 则打开 否则的话就
         * 创建后再打开
         * 当磁盘已满或者权限问题等特殊情况时它们才会区分开来 如下
         * getReadableDatabase是只打开可读数据库 getWritableDatabase只打开可写数据库
         */
        //创建数据库
        SQLiteDatabase db = helper.getWritableDatabase();

    }

    @Override
    public void onClick(View view) {
        switch (view.getId()) {
            //添加数据
            case R.id.btn2:
                //打开数据库
                db = helper.getWritableDatabase();
                ContentValues values = new ContentValues();

                //开启事务
                db.beginTransaction();
                for (int i = 1; i <= 100; i++) {
                    /*向values中传入数据
                参数一：字段名称，参数二：插入该字段的数据*/
                    values.put(Constant._ID,i);
                    values.put(Constant.NAME, "李四");
                    values.put(Constant.AGE, 25);
                    /**
                     * 向数据表中插入数据
                     * 参数一:表名，参数二：当插入的一行数据为空时，设置值为null,参数三：values对象
                     */
                    db.insert(Constant.TABLE_NAME,null,values);
                }
                //提交事务
                db.setTransactionSuccessful();
                //关闭事务
                db.endTransaction();
                db.close();

            break;
                //删除数据
            case R.id.btn3:
                db = helper.getWritableDatabase();
                db.delete(Constant.TABLE_NAME,Constant._ID + "=?",new String[]{"1"});
                db.close();

                break;
                //修改数据
            case R.id.btn4:
                db = helper.getWritableDatabase();
                values = new ContentValues();
                values.put(Constant.NAME,"小沐");
                //修改数据表中的数据
                //参数一：表名，参数二：values对象，参数三：修改条件，参数三：占位符,参数四：替换的数据
                db.update(Constant.TABLE_NAME, values, Constant._ID + "=?", new String[]{"1"});
                db.close();
                break;
                //显示启动listviewActivity
            case R.id.btn5:
                Intent intent = new Intent(this,ListviewActivity.class);
                startActivity(intent);
                break;

        }
    }
}

listviewAcivivty.java:

public class ListviewActivity extends AppCompatActivity {

    private ListView listView;
    private SQLiteDatabase db;


    @Override

    protected void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_listview);

        listView = findViewById(R.id.listview);



        /**
         * 准备数据源
         * 参数一:表名
         * 参数二：字段名（null表示查询所有）
         * 参数三：查询条件
         * 参数四：占位符
         * 参数五：占位符具体数值
         * 参数六：分组条件
         * 参数七：筛选条件
         * 参数八：排序条件（null表示升序排列）
         *
         */


        String path = Environment.getExternalStorageDirectory().getAbsolutePath()
                + File.separator + "DB";
        //打开数据库
        //参数一：当前数据库所在路径，参数二：游标工厂，参数三：数据库操作模式
        db = SQLiteDatabase.openDatabase(path, null, SQLiteDatabase.OPEN_READONLY);
        //准备数据源
        Cursor cursor = db.query(Constant.TABLE_NAME,null,Constant._ID+">?"
                ,new String[]{"0"},null,null,null);
        //配置适配器
        SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                this,
                R.layout.itemview,
                cursor,
                //字段名
                new String[]{Constant._ID,Constant.NAME,Constant.AGE},
                new int[]{R.id.id,R.id.name,R.id.age},
                        //适配器标记
                 SimpleCursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER
        );
        listView.setAdapter(adapter);
    }

}

MysqliteHelper.java:

/**
 * 数据库帮助类:
 */

public class MysqliteHelper extends SQLiteOpenHelper {
    //参数一:上下文，参数二：当前创建的数据库名，参数三:游标工厂，参数四:数据库版本>=1
    public MysqliteHelper(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public MysqliteHelper(Context context) {
        super(context,Constant.DATABASE_NAME,null,Constant.DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
       //配置表
        String sql = "create table "+Constant.TABLE_NAME+"("
                +Constant._ID+" Integer primary key,"
                +Constant.NAME+" varchat(10),"
                +Constant.AGE+" Interger)";
        //创建表
        db.execSQL(sql);
    }

    @Override
    //更新数据库
    public void onUpgrade(SQLiteDatabase db, int oldversion, int newversion) {
        String sql_onupgrade = "DROP TABLE IF EXISTS user";
        //删除表
        db.execSQL(sql_onupgrade);
        //重建表
        onCreate(db);

    }
}

DataBaseManger.java:

/**
 * 数据库工具类
 */
public class DataBaseManger {
    private static MysqliteHelper helper;

    public static MysqliteHelper getIntance(Context context) {
        if (helper == null) {
            helper = new MysqliteHelper(context);
        }
        return helper;
    }

    //将cursor对象转化为list集合
    public static List<Student> cursorToList(Cursor cursor) {
        List<Student> list = new ArrayList<>();
        //遍历游标中的查询结果
        while (cursor.moveToNext()) {
            //根据字段名得到索引再由字段索引得到数据
            int id = cursor.getInt(cursor.getColumnIndex(Constant._ID));
            String name = cursor.getString(cursor.getColumnIndex(Constant.NAME));
            int age = cursor.getInt(cursor.getColumnIndex(Constant.AGE));

            Student student = new Student(id, name, age);
            list.add(student);
        }
        return list;
    }
}

Student.java:

/**
 * 数据库实体类
 */

public class Student {
    //定义与数据表字段对应的成员变量
    private int id;
    private String name;
    private int age;

    //定义构造方法
    public Student(int id, String name, int age) {
        this.id = id;
        this.name = name;
        this.age = age;
    }

    public String toString() {
        return  "id="+this.id+",name="+this.name+",age="+this.age;
    }
    //定义set和get方法
    public int getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public int getAge() {
        return age;
    }

    public void setId(int id) {
        this.id = id;
    }

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

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

Constant.java:

/**
常量类
/

public class Constant {
    public static final String DATABASE_NAME = "DB";
    public static final int DATABASE_VERSION = 1;
    public static final String TABLE_NAME = "name";
    public static final String _ID = "_id";
    public static final String NAME = "_name";
    public static final String AGE = "_age";
}