android--(SQLite)

/**
 * 
 * 数据库元数据的定义 用于给 DatabaseHelper类使用
 *
 * 1.不能被继承 final
 *
 * 2.不能被创建 私有化构造函数
 *
 *
 */
public final class PetMetaData {

    private PetMetaData() {

    }

    //定义表 表名和列名
    public static abstract class DogTable implements BaseColumns {
        public static final String TABLE_NAME = "dog";
        public static final String NAME ="name";
        public static final String AGE = "age";
    }
}

// sqlite的助手类，程序第一次运行时，就自动创建数据库并可以维护数据库
public class DatabaseHelper extends SQLiteOpenHelper {

    //定义数据库名称
    private static String DB_NAME = "pet.db";

    //版本号
    private static int VERSION = 1;

    //这里应该使用 定义元数据类的字段来拼接组成 sql语句，这里没有用
    private static final String CREATE_TABLE_DOG = "CREATE TABLE dog(_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,age INTEGER)";


    //删除表 应配合定义元数据的类拼接
    private static final String DROP_TABLE_DOG ="DROP TABLE IF EXISTS dog";

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }


    /***
     * 如果数据库表不存在，那么用调用该方法
     *
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {

        //SQLiteDatabase :用于操作数据库的工具类
        db.execSQL(CREATE_TABLE_DOG);//如果表不存，则创建，存在，则不创建

    }

    /***
     * 数据库 升级或更新
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        db.execSQL(DROP_TABLE_DOG);//删除表

        db.execSQL(CREATE_TABLE_DOG);//创建表，两句相当于更新表

    }
}

/**
 * 对数据库 增 删 改 查
 */
public class DataBaseAdapter {

    private DatabaseHelper dbHelper;

    public DataBaseAdapter(Context context) {
        this.dbHelper = new DatabaseHelper(context);
    }


    /**
     * 添加
     *
     * @param dog
     */
    public void add(Dog dog) {

        //获取 操作数据库的工具类
        SQLiteDatabase db = dbHelper.getWritableDatabase();//检查数据库中有没有这个表，用于触发sqlite助手类创建表方法

        //列数据对象
        ContentValues values = new ContentValues();

        //数据库元数据列名 所以应对象的值
        values.put(PetMetaData.DogTable.NAME, dog.getName());
        values.put(PetMetaData.DogTable.AGE, dog.getAge());

        // 参数(表名，可为null的列名(保证 values 为空的时侯保证生成sql语名的合法性，
        //     如果知道values不为null时，就填null)，ContentValues

        //合法：insert into dog(name,age) values('xx',2)
        //不合法： insert into dog() value()
        db.insert(PetMetaData.DogTable.TABLE_NAME, null, values);
        db.close();
    }

    /**
     * 删除
     *
     * @param id
     */
    public void delete(int id) {

        SQLiteDatabase db = dbHelper.getWritableDatabase();

        String whereclause = PetMetaData.DogTable._ID + "=?";
        String[] whereargs = {String.valueOf(id)};

        // 参数(表名,条件,条件的值)
        db.delete(PetMetaData.DogTable.TABLE_NAME, whereclause, whereargs);
        db.close();
    }

    /**
     * 更新
     *
     * @param dog
     */
    public void update(Dog dog) {

        SQLiteDatabase db = dbHelper.getWritableDatabase();

        ContentValues values = new ContentValues();
        //数据库元数据列名 所以应对象的值
        values.put(PetMetaData.DogTable.NAME, dog.getName());
        values.put(PetMetaData.DogTable.AGE, dog.getAge());

        String whereclause = PetMetaData.DogTable._ID + "=?";
        String[] whereargs = {String.valueOf(dog.getId())};

        //参数(表名,ContentValues,条件，条件的值)
        db.update(PetMetaData.DogTable.TABLE_NAME, values, whereclause, whereargs);
        db.close();

    }

    /**
     * 根据id 查找 一个
     *
     * @param id
     */
    public Dog findById(int id) {
        SQLiteDatabase db = dbHelper.getReadableDatabase();

        //要查询数据库的列名
        String[] columns = {PetMetaData.DogTable._ID, PetMetaData.DogTable.NAME, PetMetaData.DogTable.AGE};

        String selection = PetMetaData.DogTable._ID + "=?";
        String[] selectionArgs = {String.valueOf(id)};

        Cursor cursor = db.query(true, PetMetaData.DogTable.TABLE_NAME, columns, selection, selectionArgs, null, null, null, null);

        Dog dog = null;
        if (cursor.moveToNext()) {
            dog = new Dog();
            //通过 游标将元数据列名 所对应的值 填充到 对象的属性上
            dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
        }
        return dog;
    }

    /**
     * 查找所有
     *
     * @return
     */
    public ArrayList<Dog> findAll() {

        SQLiteDatabase db = dbHelper.getReadableDatabase();

        //要查询数据库的列名
        String[] columns = {PetMetaData.DogTable._ID, PetMetaData.DogTable.NAME, PetMetaData.DogTable.AGE};

        /**
         * 参数：
         * 1.是否去除重复记录
         * 2.表名
         * 3.要查询的列
         * 4.查询条件
         * 5.查询条件的值
         * 6.分组条件
         * 7.分组条件的值
         * 8.排序
         * 9.分页条件
         */
        Cursor cursor = db.query(true, PetMetaData.DogTable.TABLE_NAME, columns, null, null, null, null, null, null);

        ArrayList<Dog> dogs = new ArrayList<>();
        Dog dog = null;

        while (cursor.moveToNext()) {
            dog = new Dog();
            //通过 游标将元数据列名 所对应的值 填充到 对象的属性上
            dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));

            dogs.add(dog);
        }

        return dogs;
    }

}

// 原生的写法
public class DataBaseAdapter {

    private DatabaseHelper dbHelper;

    public DataBaseAdapter(Context context) {
        this.dbHelper = new DatabaseHelper(context);
    }
 /***
     * 原生的sql 添加
     *
     * @param dog
     */
    public void rawAdd(Dog dog) {
        String sql = "insert into dog(name,age) values(?,?)";
        Object[] args = {dog.getName(), dog.getAge()};

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL(sql, args);
        db.close();
    }

    /**
     * 原生 sql 删除
     *
     * @param id
     */
    public void rawDelete(int id) {
        String sql = "delete from dog where _id=?";
        Object[] args = {id};

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL(sql, args);
        db.close();
    }

    /***
     * 原生sql 更新
     *
     * @param dog
     */
    public void rawUpdate(Dog dog) {
        String sql = "update dog set name =?,age=? where _id =?";
        Object[] args = {dog.getName(), dog.getAge(), dog.getId()};

        SQLiteDatabase db = dbHelper.getWritableDatabase();
        db.execSQL(sql, args);
        db.close();

    }


    /**
     *  原生 sql查找一个
     * @param id
     * @return
     */
    public Dog rawFindById(int id) {
        String sql = "select _id,name,age,from dog where _id =?";

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, new String[]{String.valueOf(id)});

        Dog dog = null;
        if (cursor.moveToNext()) {
            dog = new Dog();
            //通过 游标将元数据列名 所对应的值 填充到 对象的属性上
            dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));
        }
        return dog;

    }


    /***
     *  原生sql 查找所有
     * @return
     */
    public ArrayList<Dog> rawFindAll() {

        String sql = "select _id,name,age,from dog";

        SQLiteDatabase db = dbHelper.getReadableDatabase();
        Cursor cursor = db.rawQuery(sql, null);

        ArrayList<Dog> dogs = new ArrayList<>();
        Dog dog = null;

        while (cursor.moveToNext()) {
            dog = new Dog();
            //通过 游标将元数据列名 所对应的值 填充到 对象的属性上
            dog.setId(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable._ID)));
            dog.setName(cursor.getString(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.NAME)));
            dog.setAge(cursor.getInt(cursor.getColumnIndexOrThrow(PetMetaData.DogTable.AGE)));

            dogs.add(dog);
        }

        return dogs;
    }
}


    /**
     * sql 事务的操作
     */
    public void transaction() {
        SQLiteDatabase db = dbHelper.getWritableDatabase();

        //开始事务
        db.beginTransaction();

        try {
            db.execSQL("insert into dog(name,age) values('dang',4)");
            db.execSQL("insert into dog(name,age) values('do',5)");

            db.setTransactionSuccessful();//设置事务成功的标记

        } finally {
            db.endTransaction();//结束事务，判断事务标记是否为true， 如果为true 那么就提交事务，否则回滚事务
            db.close();
        }
    }