SQLite

/*
SQLite的增删查改   db.rawQuery();        查询    select * from 表名
                  db.execSQL();         添加、删除、修改、创建表
SQLiteOpenHelper  Android平台里一个数据库辅助类，用于创建或打开数据库，并且对数据库的创建和版本进行管理。
SQLiteDatabase  用于管理和操作SQLite数据库，几乎所有的数据库操作。

 */

public class MainActivity extends Activity {

    private EditText nameEdt, ageEdt, idEdt;
    private RadioGroup genderGp;
    private ListView stuList;
    private RadioButton malerb;
    private String genderStr = "男";
    private SQLiteDatabase db;

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

        //添加操作
        //数据库名称
        //如果只有一个数据库名称，那么这个数据库的位置会是在私有目录中(data
        //如果带SD卡路径，那么数据库位置则在指定的路径下
        String path = Environment.getExternalStorageDirectory() + "/stu.db";
        //Android平台里一个数据库辅助类，用于创建或打开数据库，并且对数据库的创建和版本进行管理。
        SQLiteOpenHelper helper = new SQLiteOpenHelper(this, path, null, 2) {
            @Override
            public void onCreate(SQLiteDatabase sqLiteDatabase) {
                //创建
                Toast.makeText(MainActivity.this, "数据库创建", Toast.LENGTH_SHORT).show();

                //如果数据库不存在，则会调用onCreate方法，那么我们可以将表的创建工作放在这里面完成
                String sql = "create table test_tb (_id integer primary key autoincrement," +
                        "name varhcar(20)," +
                        "age integer)";
                sqLiteDatabase.execSQL(sql);
            }

            @Override
            public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
                //升级
                Toast.makeText(MainActivity.this, "数据库升级", Toast.LENGTH_SHORT).show();
            }
        };

        //用于获取数据库对象//获取可读数据库
        //1.数据库存在，则直接打开数据库
        //2.数据库不存在，则调用创建数据库的方法，再打开数据库
        //3.数据库存在，但版本号升高了，则调用数据库升级方法
        db = helper.getReadableDatabase();

        nameEdt = (EditText) findViewById(R.id.name_edt);
        ageEdt = (EditText) findViewById(R.id.age_edt);
        idEdt = (EditText) findViewById(R.id.id_edt);
        malerb = (RadioButton) findViewById(R.id.male);

        genderGp = (RadioGroup) findViewById(R.id.gender_gp);
        genderGp.setOnCheckedChangeListener(new RadioGroup.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(RadioGroup radioGroup, int i) {
                if (i == R.id.male) {
                    genderStr = "男";
                } else {
                    genderStr = "女";
                }
            }
        });

        stuList = (ListView) findViewById(R.id.stu_list);
    }

    //   SQLiteOpenHelper
    //   SQLiteDatabase  用于管理和操作SQLite数据库，几乎所有的数据库操作。
    //db.rawQuery();        查询    select * from 表名
    //db.execSQL();         添加、删除、修改、创建表
    public void operate(View v) {
        String nameStr = nameEdt.getText().toString();
        String ageStr = ageEdt.getText().toString();
        String idStr = idEdt.getText().toString();
        switch (v.getId()) {
            //两种方法添加
            case R.id.insert_btn:
                //String sql = "insert into info_tb (name,age,gender) values ('"+nameStr+"',"+ageStr+",'"+genderStr+"')";
                String sql = "insert into info_tb (name,age,gender) values (?,?,?)";
                //执行数据库
                db.execSQL(sql, new String[]{nameStr, ageStr, genderStr});
                Toast.makeText(this, "添加成功", Toast.LENGTH_SHORT).show();
                break;
            //查询并显示出来
            case R.id.select_btn:
                //select * from 表名 where _id = ?
                //1、查询所有
                String sql2 = "select * from info_tb";
                //2、id输入框不为空就可以带条件查询
                if (!idStr.equals("")) {
                    sql2 += " where _id=" + idStr;
                }
                //查询结果，用Cursor  代表数据源   rawQuery的返回值是Cursor类型，想象成一张表
                Cursor c = db.rawQuery(sql2, null);

                //有了结果之后用游标适配器与ListView匹配，显示出来
                //SimpleCursorAdapter
                //SimpleAdapter a = new SimpleAdapter()
                //参数3：数据源  即Cursor c
                SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                        this, R.layout.item, c,
                        new String[]{"_id", "name", "age", "gender"},
                        new int[]{R.id.id_item, R.id.name_item, R.id.age_item, R.id.gender_item}
                        , CursorAdapter.FLAG_REGISTER_CONTENT_OBSERVER);
                stuList.setAdapter(adapter);
                break;
            //删除所设编号的数据
            case R.id.delete_btn:
                String sql3 = "delete from info_tb where _id=?";
                db.execSQL(sql3, new String[]{idStr});
                Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();
                break;
            case R.id.update_btn:
                String sql4 = "update info_tb set name=? , age=? , gender=?  where _id=?";
                db.execSQL(sql4, new String[]{nameStr, ageStr, genderStr, idStr});
                Toast.makeText(this, "修改成功", Toast.LENGTH_SHORT).show();
                break;
        }
        nameEdt.setText("");
        ageEdt.setText("");
        idEdt.setText("");
        malerb.setChecked(true);
    }
}

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/activity_main"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical"
    android:padding="10dp"
    tools:context="MainActivity">

    <EditText
        android:id="@+id/name_edt"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="姓名：" />

    <EditText
        android:id="@+id/age_edt"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="年龄："
        android:numeric="integer" />

    <RadioGroup
        android:id="@+id/gender_gp"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="5dp"
            android:text="性别:" />

        <RadioButton
            android:id="@+id/male"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="15dp"
            android:checked="true"
            android:text="男" />

        <RadioButton
            android:id="@+id/female"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_marginLeft="15dp"
            android:text="女" />
    </RadioGroup>

    <EditText
        android:id="@+id/id_edt"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:hint="编号" />

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal">

        <Button
            android:id="@+id/insert_btn"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="operate"
            android:text="添加" />

        <Button
            android:id="@+id/select_btn"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="operate"
            android:text="查询" />

        <Button
            android:id="@+id/delete_btn"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="operate"
            android:text="删除" />

        <Button
            android:id="@+id/update_btn"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:layout_weight="1"
            android:onClick="operate"
            android:text="修改" />
    </LinearLayout>

    <ListView
        android:id="@+id/stu_list"
        android:layout_width="match_parent"
        android:layout_height="match_parent"></ListView>
</LinearLayout>

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="horizontal">

    <TextView
        android:id="@+id/id_item"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight="1" />

    <TextView
        android:id="@+id/name_item"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight="1" />

    <TextView
        android:id="@+id/age_item"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight="1" />

    <TextView
        android:id="@+id/gender_item"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_weight="1" />

</LinearLayout>

/*
API
 */
public class MainActivity2 extends Activity {

    private EditText nameEdt, ageEdt, idEdt;
    private RadioGroup genderGp;
    private ListView stuList;
    private RadioButton malerb;
    private String genderStr = "男";
    private SQLiteDatabase db;

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

        //添加操作
        //数据库名称
        //如果只有一个数据库名称，那么这个数据库的位置会是在私有目录中
        //如果带SD卡路径，那么数据库位置则在指定的路径下
        String path = Environment.getExternalStorageDirectory() + "/stu.db";
        SQLiteOpenHelper helper = new SQLiteOpenHelper(this, path, null, 2) {
            @Override
            public void onCreate(SQLiteDatabase sqLiteDatabase) {
                //创建
                Toast.makeText(MainActivity2.this, "数据库创建", Toast.LENGTH_SHORT).show();
                //如果数据库不存在，则会调用onCreate方法，那么我们可以将表的创建工作放在这里面完成
                        /*
                        String sql = "create table test_tb (_id integer primary key autoincrement," +
                                "name varhcar(20)," +
                                "age integer)";
                        sqLiteDatabase.execSQL(sql);*/
            }

            @Override
            public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
                //升级
                Toast.makeText(MainActivity2.this, "数据库升级", Toast.LENGTH_SHORT).show();
            }
        };

        //用于获取数据库库对象
        //1.数据库存在，则直接打开数据库
        //2.数据库不存在，则调用创建数据库的方法，再打开数据库
        //3.数据库存在，但版本号升高了，则调用数据库升级方法
        db = helper.getReadableDatabase();

        nameEdt = (EditText) findViewById(R.id.name_edt);
        ageEdt = (EditText) findViewById(R.id.age_edt);
        idEdt = (EditText) findViewById(R.id.id_edt);
        malerb = (RadioButton) findViewById(R.id.male);

        genderGp = (RadioGroup) findViewById(R.id.gender_gp);
        genderGp.setOnCheckedChangeListener(new RadioGroup.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(RadioGroup radioGroup, int i) {
                if (i == R.id.male) {
                    genderStr = "男";
                } else {
                    genderStr = "女";
                }
            }
        });

        stuList = (ListView) findViewById(R.id.stu_list);
    }

    //    SQLiteOpenHelper
//    SQLiteDatabase
    public void operate(View v) {

        String nameStr = nameEdt.getText().toString();
        String ageStr = ageEdt.getText().toString();
        String idStr = idEdt.getText().toString();
        switch (v.getId()) {
            case R.id.insert_btn:
                //在SqliteDatabase类下，提供四个方法
                //insert（添加）、delete（删除）、update（修改）、query（查询）
                //都不需要写sql语句
                //参数1：你所要操作的数据库表的名称
                //参数2：可以为空的列.  如果第三个参数是null或者说里面没有数据
                //那么我们的sql语句就会变为insert into info_tb () values ()  ，在语法上就是错误的
                //此时通过参数3指定一个可以为空的列，语句就变成了insert into info_tb (可空列) values （null）
                ContentValues values = new ContentValues();
                //insert into 表明(列1，列2) values（值1，值2）
                values.put("name", nameStr);
                values.put("age", ageStr);
                values.put("gender", genderStr);
                long id = db.insert("info_tb", null, values);
                Toast.makeText(this, "添加成功，新学员学号是：" + id, Toast.LENGTH_SHORT).show();
                break;
            case R.id.select_btn:
                //select 列名 from 表名 where 列1 = 值1 and 列2 = 值2
                //参数2：你所要查询的列。{”name","age","gender"},查询所有传入null/{“*”}
                //参数3：条件（针对列）
                //参数5:分组
                //参数6：当 group by对数据进行分组后，可以通过having来去除不符合条件的组
                //参数7:排序
                Cursor c = db.query("info_tb", null, null, null, null, null, null);

                //SimpleCursorAdapter
                //SimpleAdapter a = new SimpleAdapter()
                //参数3：数据源
                SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                        this, R.layout.item, c,
                        new String[]{"_id", "name", "age", "gender"},
                        new int[]{R.id.id_item, R.id.name_item, R.id.age_item, R.id.gender_item});
                stuList.setAdapter(adapter);
                break;
            case R.id.delete_btn:
                int count = db.delete("info_tb", "_id=?", new String[]{idStr});
                if (count > 0) {
                    Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();
                }
                break;
            case R.id.update_btn:
                ContentValues values2 = new ContentValues();
                //update info_tb set 列1=xx , 列2=xxx where 列名 = 值
                values2.put("name", nameStr);
                values2.put("age", ageStr);
                values2.put("gender", genderStr);
                int count2 = db.update("info_tb", values2, "_id=?", new String[]{idStr});
                if (count2 > 0) {
                    Toast.makeText(this, "修改成功", Toast.LENGTH_SHORT).show();
                }
                break;
        }
        nameEdt.setText("");
        ageEdt.setText("");
        idEdt.setText("");
        malerb.setChecked(true);
    }
}

/*
GreenDao  实现封装数据库操作类
MainActivity+实体类+数据库封装操作类
https://www.jianshu.com/p/967d402d411d
 */

public class MainActivity3 extends Activity {

    private EditText nameEdt, ageEdt, idEdt;
    private RadioGroup genderGp;
    private ListView stuList;
    private RadioButton malerb;
    private String genderStr = "男";
    private StudentDao dao;

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

        dao = new StudentDao(this);

        nameEdt = (EditText) findViewById(R.id.name_edt);
        ageEdt = (EditText) findViewById(R.id.age_edt);
        idEdt = (EditText) findViewById(R.id.id_edt);
        malerb = (RadioButton) findViewById(R.id.male);

        genderGp = (RadioGroup) findViewById(R.id.gender_gp);
        genderGp.setOnCheckedChangeListener(new RadioGroup.OnCheckedChangeListener() {
            @Override
            public void onCheckedChanged(RadioGroup radioGroup, int i) {
                if (i == R.id.male) {
                    //“男”
                    genderStr = "男";
                } else {
                    //"女"
                    genderStr = "女";
                }
            }
        });

        stuList = (ListView) findViewById(R.id.stu_list);
    }

    public void operate(View v) {

        String nameStr = nameEdt.getText().toString();
        String ageStr = ageEdt.getText().toString();
        String idStr = idEdt.getText().toString();
        switch (v.getId()) {
            case R.id.insert_btn:
                Student stu = new Student(nameStr, Integer.parseInt(ageStr), genderStr);
                dao.addStudent(stu);
                Toast.makeText(this, "添加成功", Toast.LENGTH_SHORT).show();
                break;
            case R.id.select_btn:
                String key = "", value = "";
                if (!nameStr.equals("")) {
                    value = nameStr;
                    key = "name";
                } else if (!ageStr.equals("")) {
                    value = ageStr;
                    key = "age";
                } else if (!idStr.equals("")) {
                    value = idStr;
                    key = "_id";
                }
                Cursor c;
                if (key.equals("")) {
                    c = dao.getStudent();
                } else {
                    c = dao.getStudent(key, value);
                }

                SimpleCursorAdapter adapter = new SimpleCursorAdapter(
                        this, R.layout.item, c,
                        new String[]{"_id", "name", "age", "gender"},
                        new int[]{R.id.id_item, R.id.name_item, R.id.age_item, R.id.gender_item});
                stuList.setAdapter(adapter);
                break;
            case R.id.delete_btn:

                String[] params = getParams(nameStr, ageStr, idStr);

                dao.deleteStudent(params[0], params[1]);
                Toast.makeText(this, "删除成功", Toast.LENGTH_SHORT).show();
                break;
            case R.id.update_btn:
                Student stu2 = new Student(Integer.parseInt(idStr), nameStr, Integer.parseInt(ageStr), genderStr);
                dao.updateStudent(stu2);
                Toast.makeText(this, "修改成功", Toast.LENGTH_SHORT).show();
                break;
        }
        nameEdt.setText("");
        ageEdt.setText("");
        idEdt.setText("");
        malerb.setChecked(true);
    }

    public String[] getParams(String nameStr, String ageStr, String idStr) {
        String[] params = new String[2];
        if (!nameStr.equals("")) {
            params[1] = nameStr;
            params[0] = "name";
        } else if (!ageStr.equals("")) {
            params[1] = ageStr;
            params[0] = "age";
        } else if (!idStr.equals("")) {
            params[1] = idStr;
            params[0] = "_id";
        }
        return params;
    }
}

/**
 * 实体类
 */

public class Student {
    //私有属性
    private int id;
    private String name;
    private int age;
    private String gender;

    //无参构造
    public Student() {

    }

    public Student(String name, int age, String gender) {
        this.name = name;
        this.age = age;
        this.gender = gender;
    }

    //有参构造
    public Student(int id, String name, int age, String gender) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
    }

    //创建的setter和getter方法
    public int getId() {
        return id;
    }

    public void setId(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 getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }


}

/**
 数据库封装操作类
 */

public class StudentDao {
    private SQLiteDatabase db;

    public StudentDao(Context context) {
        String path = Environment.getExternalStorageDirectory() + "/stu.db";
        SQLiteOpenHelper helper = new SQLiteOpenHelper(context, path, null, 2) {
            @Override
            public void onCreate(SQLiteDatabase sqLiteDatabase) {
            }

            @Override
            public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
            }
        };
        db = helper.getReadableDatabase();
    }

    public void addStudent(Student stu) {
        String sql = "insert into info_tb (name,age,gender) values(?,?,?)";
        db.execSQL(sql, new Object[]{stu.getName(), stu.getAge() + "", stu.getGender()});
    }

    public Cursor getStudent(String... strs) {
        //1.查询所有(没有参数)
        String sql = "select * from info_tb ";
        //2.含条件查询（姓名/年龄/编号）（参数形式：第一个参数指明条件，第二个参数指明条件值）
        if (strs.length != 0) {
            sql += " where " + strs[0] + "='" + strs[1] + "'";
        }
        Cursor c = db.rawQuery(sql, null);
        return c;
    }

    public ArrayList<Student> getStudentInList(String... strs) {
        ArrayList<Student> list = new ArrayList<>();
        Cursor c = getStudent(strs);
        while (c.moveToNext()) {
            int id = c.getInt(0);
            String name = c.getString(1);
            int age = c.getInt(2);
            String gender = c.getString(3);
            Student s = new Student(id, name, age, gender);
            list.add(s);
        }
        return list;
    }

    public void deleteStudent(String... strs) {
        String sql = "delete from info_tb where " + strs[0] + "='" + strs[1] + "'";
        db.execSQL(sql);
    }

    public void updateStudent(Student stu) {
        String sql = "update info_tb set name=?,age=?,gender=? where _id=?";
        db.execSQL(sql, new Object[]{stu.getName(), stu.getAge(), stu.getGender(), stu.getId()});
    }

}