-- 建库语句
CREATE DATABASE 数据库名称;

-- 举例
CREATE DATABASE mydata.db;

-- 删库语句
DROP DATABASE 数据库名;

-- 举例
DROP DATABASE mydata.db;

-- 建表语句
CREATE TABLE 表名(列名1 类型(尺寸) 属性(一个或者多个), 列名2 类型(可带尺寸) 属性(一个或者多个), ...);

-- 举例：创建一个存储人的信息表，包括id，姓，名，年龄
CREATE TABLE Person(
    id        INTEGER   NOT NULL PRIMARY KEY AUTOINCREMENT,
    lastName  TEXT      NOT NULL,  
    firstName TEXT      NOT NULL,
    age       INTEGER   NOT NULL
); 

-- 删表语句
DROP TABLE 表名;

-- 举例：创建删除上面创建的表
DROP TABLE Person;

-- 重命名表语句
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 举例：重命名上面创建的表
ALTER TABLE Person RENAME TO People;

-- 增加字段语句
ALTER TABLE 表名 ADD COLUMN 字段名 类型 属性;

-- 举例：在上面的表中新增一项城市信息
DROP TABLE Person ADD COLUMN city INTEGER NOT NULL;

-- 删除字段语句
ALTER TABLE 表名 DROP COLUMN 字段名;

-- 举例：删除上面新增的城市信息
DROP TABLE Person DROP COLUMN city;

-- 修改字段语句
ALTER TABLE 表名 ALTER COLUMN 字段名 类型 属性;

-- 举例：修改上面的城市信息，城市名用文本存储
ALTER TABLE Person ALTER COLUMN city TEXT NOT NULL;

-- 增加记录语句
-- 增加特定字段数据
INSERT INTO 表名(字段1, 字段, ...) VALUES (值1, 值2, ...);
-- 向所有列插入数据
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 举例：插入一个人的信息
INSERT INTO Person(id, lastName, firstName, age, city) VALUES (1, "张", "三", 23, "北京");
-- 举例：插入一个人的信息，向所有列都插入
INSERT INTO Person VALUES (1, "李", "四", 24, "上海");

-- 删除记录语句
DELETE FROM 表名 WHERE 条件语句;

-- 举例：删除 id = 1 的的信息
DELETE FROM Person WHERE id=1;

-- 更新记录语句
UPDATE 表名 SET 字段1=值1, 字段2=值2 WHERE 条件语句;

-- 举例：更新 id = 1 的记录姓名为李四
UPDATE Person SET lastName="李", firstName="四" WHERE id=1;

-- 查询记录语句
-- 查询特定字段
SELECT 字段1, 字段2, ... FROM 表名 WHERE 条件语句;
-- 查询所有列
SELECT * FROM 表名 WHERE 条件语句;
-- 查询去重
SELECT DISTINCT 字段1, 字段2, ... FROM 表名

-- 举例：查询 id = 1 的个人的年龄，城市
SELECT age, city FROM Person WHERE id=1;
-- 举例：查询 id = 2 的个人的所有信息
SELECT * FROM Person WHERE id=2;
-- 举例：假如有4个人的信息，其中两人是北京的，两人是上海的。
-- 现在我只想知道目前数据库中有哪些城市信息，并不想他们重复
SELECT DISTINCT city FROM Person;

-- 查询年龄段不是 20 到 50 岁的人
-- 写法1：NOT+IN用法举例
SELECT * FROM Person WHERE age NOT IN(20, 50);
-- 写法2
SELECT * FROM Person WHERE age >20 AND age < 50;

-- LIKE+%用法举例
-- 查询居住在以 "Ne" 开始的城市里的人
SELECT * FROM Person WHERE city LIKE "Ne%";
-- LIKE+_用法举例
-- 查询居住在以京字结尾的城市的人，比如居住在北京、南京、东京、西京等等
SELECT * FROM Person WHERE city LIKE "_京";

-- 选择人的信息，字段重命名为姓名
SELECT lastName AS 姓, firstName AS 名 FROM Person;
-- 将姓名合在一起打印
SELECT CONCAT(lastName, firstName) AS 姓名 FROM Person;

-- 排序语法，[]包含的内容表示可选
SELECT 字段1, 字段2, ... FROM 表名 ORDER BY 字段名 [DESC]

-- 选出所有的姓名，按照姓分类，统计个数
SELECT lastName, SUM(firstName) AS nums FROM Person GROUP BY lastName; 

-- 查询居住的城市以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Person WHERE city LIKE "[ALN]%";
-- 查询居住的城市不以 "A" 或 "L" 或 "N" 开头的人
SELECT * FROM Person WHERE city LIKE "[!ALN]%";

CREATE TABLE Person(
    id        INTEGER   NOT NULL PRIMARY KEY AUTOINCREMENT, -- 设置了自增主键
    lastName  TEXT      NOT NULL,  
    firstName TEXT      NOT NULL,
    age       INTEGER   NOT NULL
); 

CREATE TABLE Person(
    lastName  TEXT      NOT NULL,  
    firstName TEXT      NOT NULL,
    age       INTEGER   NOT NULL,
    PRIMARY KEY(lastName, firstName) -- 设置了联合主键，唯一标识一条记录
);

CREATE TABLE Orders(
	id_o int NOT NULL,
	orderNo int NOT NULL,
	id_p int, -- 依赖于 Person 表的主键 id_p
	PRIMARY KEY (id_o),
	FOREIGN KEY (id_p) REFERENCES Persons(id_p)
);

INSERT INTO Orders VALUES(1, 22456, 5);

// 引入 Room Database
implementation 'android.arch.persistence.room:runtime:1.1.0'
// 引入额外的处理注解的工具
annotationProcessor 'android.arch.persistence.room:compiler:1.1.0'
//添加测试支持，我们可以对数据库进行androidTest（后面会介绍）
implementation 'android.arch.persistence.room:testing:1.1.0'

// 1、定义部分数据库常量
public interface RecordConstant {
    /**
     * 数据库名
     * */
    String DATABASE_NAME = "app_database";
    /**
     * 表名
     * */
    String TABLE_NAME = "record";
    /**
     * 一次打卡记录的时间标识分为两段，当前秒数(主时间)，和秒内的纳秒数(副时间)
     * */
    String PRIMARY_TIME_COLUMN_ANME = "click_second";
    String SECONDARY_TIME_COLUMN_ANME = "nano_in_second";
}

// 2. 定义数据表 Record
// 使用 @Entity 标识这是一张数据表
@Entity(tableName = RecordConstant.TABLE_NAME)
public class Record {
    // 设置主键、自增
    @PrimaryKey(autoGenerate = true)
    private long id;
    // @ColumnInfo 设置数据库字段名
    @ColumnInfo(name = RecordConstant.PRIMARY_TIME_COLUMN_ANME)
    private long clickSecond;
    // 设置数据库字段名，副时间
    @ColumnInfo(name = RecordConstant.SECONDARY_TIME_COLUMN_ANME)
    private int nanoInSecond;

    public Record() {
    }

    // 不映射数据库的字段或方法，使用 @Ignore 标记
    @Ignore
    public Record(long clickSecond, int nanoInSecond) {
        this.clickSecond = clickSecond;
        this.nanoInSecond = nanoInSecond;
    }

    public long getId() {return id;}
    public void setId(long id) {this.id = id;}

    public long getClickSecond() {return clickSecond;}
    public void setClickSecond(long clickSecond) {this.clickSecond = clickSecond;}

    public int getNanoInSecond() {return nanoInSecond;}

    public void setNanoInSecond(int nanoInSecond) {this.nanoInSecond = nanoInSecond;}
}

// @Dao 标识这是一个 Dao 类
@Dao
public interface RecordDao {
    /**
     * 下面的方法介绍包括增删查改，数据根据主键匹配，多个数据可以用列表和数据存储
     * */
    // ---------------插入----------------
    /**
     * @Insert 标识这个方法是数据库插入操作
     * 插入一条数据
     * OnConflictStrategy.REPLACE表示如果已经有数据，那么就覆盖掉
     *
     * @param record 待插入数据库的数据
     *
     * @return 被插入数据的主键值（即行号）
     * */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    long insert(Record record);

    /**
     * 插入多组数据
     *
     * @param records 待插入数据库的一组数据，使用列表存储
     *
     * @return 被插入的数据的主键列表
     * */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    List<Long> insertAll(List<Record> records);

    /**
     * 插入多组数据
     *
     * @param records 待插入数据库的一组数据，使用数组存储
     *
     * @return 被插入的数据的主键列表
     * */
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    List<Long> insertAll(Record... records);

    // ---------------删除----------------
    /**
     * @Delete 标识这个方法是删除操作
     * 删除一行数据库数据，待删除数据通过主键匹配
     *
     * @param record 待删除的数据
     *
     * @return 被删除数据的数目
     * */
    @Delete
    int delete(Record record);

    /**
     * 删除一组数据，被删除的数据通过列表存储
     *
     * @param records 待删除的数据
     *
     * @return 被删除数据的数目
     * */
    @Delete
    int deleteAll(List<Record> records);

    // ---------------查询----------------
    /**
     * @Query 标识这是查询操作，具体怎么查询。得定义 SQL 语句
     * 得到数据库中存储的所有数据
     *
     * @return 数据库中所有的数据的列表
     * */
    @Query("SELECT * FROM " + RecordConstant.TABLE_NAME)
    List<Record> queryAll();

    /**
     * 查询一个数据段内的数据，根据时间大小比较，区间左闭右开
     * : 相当于 Groovy 语言中的 $，起到了模版字符串的作用
     *
     * @param startInclude 时间段的开始时间
     * @param endExclude 时间段的结束时间
     *
     * @return 时间段内的数据
     * */
    @Query("SELECT * FROM " + RecordConstant.TABLE_NAME
        + " WHERE " + RecordConstant.PRIMARY_TIME_COLUMN_ANME + " >= :startInclude"
        + " AND " + RecordConstant.PRIMARY_TIME_COLUMN_ANME + " < :endExclude")
    List<Record> queryByTimeInterval(long startInclude, long endExclude);

    // ---------------更新----------------

    /**
     * @Update 标识这是一个数据库更新操作
     * 更新已有数据，根据主键匹配，返回类型int代表更新的条目数目
     *
     * @param record 带更新的数据
     */
    @Update
    int update(Record record);

    /**
     * 更新已有数据，根据主键匹配，返回类型int代表更新的条目数目
     *
     * @param records 带更新的数据
     */
    @Update
    int updateAll(List<Record> records);
}

// 使用 @Database 标明这是一个数据库对象，并且指明了数据库中的数据表和数据库版本，数据表可以生命不止一张。
// // 采用双重锁的方案实现单例，使用单例是因为数据库对象全局只有一个
@Database(entities = {Record.class}, version = 1)
public abstract class AppDatabase extends RoomDatabase {
    private static volatile AppDatabase singleton;
    /**
     * 创建获取 Dao 的抽象方法，有多少个 Entity 就创建多少个 Dao 方法
     * */
    public abstract RecordDao recordDao();
    
    private AppDatabase(){}
    public static AppDatabase getInstance(Context context) {
        if(singleton == null) {
            synchronized (AppDatabase.class) {
                if(singleton == null) {
                    singleton = Room.databaseBuilder(
                        context.getApplicationContext(),
                        AppDatabase.class,
                        RecordConstant.DATABASE_NAME)
                        // 数据库有变动时，使用这个方法
                        //.addMigrations(MIGRATION_1_2)
                        .build();
                }
            }
        }
        return singleton;
    }

    /**
     * 迁移升级方法示例。
     * 定义用于数据库升级和迁移的对象，版本从1升到2
     * 版本从2升到3，就定义MIGRATION_2_3，依次类推
     */
    public static final Migration MIGRATION_1_2 = new Migration(1, 2) {
        @Override public void migrate(@NonNull SupportSQLiteDatabase database) {
            // 实现数据库变动代码
            //database.execSQL("ALTER TABLE record " +
            // "ADD COLUMN date INTEGER NOT NULL DEFAULT 0");
        }
    };
}

// 数据库操作不能在主线程，所以需要开启异步线程执行
public class DbTaskManager {
    /**
     * 执行异步操作的线程池
     * */
    private ExecutorService executor;
    /**
     * 数据库实例
     * */
    private AppDatabase database;
    /**
     * 上下文环境
     * */
    private Context context;

    public DbTaskManager(Context context, AppDatabase database) {
        // 创建线程数量为 5 的固定线程池
        executor = Executors.newFixedThreadPool(5);
        this.context = context;
        if(database != null) {
            this.database = database;
        } else {
            this.database = AppDatabase.getInstance(context);
        }
    }

    /**
     * 取消所有正在执行的任务，并终结线程池
     * */
    public void cancel() {
        executor.shutdown();
    }

    /**
     * 增删查改操作：增
     * */
    public boolean insert(Record record) {
        return runInsert(record);
    }
    /**
     * 增删查改操作：删
     * */
    public boolean delete(Record record) {
        return runDelete(record);
    }

    /**
     * 增删查改操作：查
     * */
    public List<Record> queryAll() {
        return runQueryAll();
    }
    /**
     * 增删查改操作：改
     * */
    public boolean update(Record record) {
        return runUpdate(record);
    }

    /**
     * 实际的插入操作
     * */
    private boolean runInsert(final Record record) {
        // Future + Callable，异步支持操作，并获取结果
        Callable<Boolean> callable = new Callable<Boolean>() {
            @Override public Boolean call() throws Exception {
                return database.recordDao().insert(record) > -1;
            }
        };

        boolean result;
        try {
            Future<Boolean> task = executor.submit(callable);
            result = task.get();
        } catch (Exception e) {
            result = false;
        }

        return result;
    }

    /**
     * 实际的删除操作
     * */
    private boolean runDelete(final Record record) {
        Callable<Boolean> callable = new Callable<Boolean>() {
            @Override public Boolean call() throws Exception {
                return database.recordDao().delete(record) > 0;
            }
        };

        boolean result;
        try {
            Future<Boolean> task = executor.submit(callable);
            result = task.get();
        } catch (Exception e) {
            result = false;
        }

        return result;
    }

    /**
     * 实际的查询操作，查询所有记录
     * */
    private List<Record> runQueryAll() {
        Callable<List<Record>> callable = new Callable<List<Record>>() {
            @Override public List<Record> call() throws Exception {
                return database.recordDao().queryAll();
            }
        };

        List<Record> result;
        try {
            Future<List<Record>> task = executor.submit(callable);
            result = task.get();
        } catch (Exception e) {
            result = new ArrayList<>(0);
        }

        return result;
    }

    /**
     * 得到某天的所有打卡记录
     *
     * @param instant 可以构建出某个日期的时间戳，是 Java8 中新增的时间 API
     *
     * @return 某天的所有打卡记录的列表
     * */
    public List<Record> querySomedayRecords(Instant instant) {
        // ZonedDateTime 包含着最全的时间信息
        ZonedDateTime zonedDateTime = instant.atZone(ZoneId.of(TimeUtil.ZONE_ID));
        // 获得查询范围的起止事件，单位是秒
        final long startInclude = TimeUtil.getTimeInstant(zonedDateTime);
        // 天数加 1 天
        final long endExclude = TimeUtil.getTimeInstant(zonedDateTime.plus(1, ChronoUnit.DAYS));

        Callable<List<Record>> callable = new Callable<List<Record>>() {
            @Override public List<Record> call() throws Exception {
                if(startInclude == -1 || endExclude == -1) {
                    return null;
                }
                return database.recordDao().queryByTimeInterval(startInclude, endExclude);
            }
        };

        List<Record> result;
        try {
            Future<List<Record>> task = executor.submit(callable);
            result = task.get();
        } catch (Exception e) {
            result = new ArrayList<>(0);
        }

        return result;
    }

    /**
     * 实际的更新操作
     * */
    private boolean runUpdate(final Record record) {
        Callable<Boolean> callable = new Callable<Boolean>() {
            @Override public Boolean call() throws Exception {
                return database.recordDao().update(record) > 0;
            }
        };

        boolean result;
        try {
            Future<Boolean> task = executor.submit(callable);
            result = task.get();
        } catch (Exception e) {
            result = false;
        }

        return result;
    }
}

// 使用 @Entity 的 primaryKeys 属性设计联合主键
@Entity(tableName = RecordConstant.TABLE_NAME,
    primaryKeys = {RecordConstant.PRIMARY_TIME_COLUMN_ANME,
        RecordConstant.SECONDARY_TIME_COLUMN_ANME})
public class Record {
    // 设置主键、自增
    // @PrimaryKey(autoGenerate = true)
    // private long id;
    // 秒 + 纳秒可以唯一标识一条记录
    // @ColumnInfo 设置数据库字段名
    @ColumnInfo(name = RecordConstant.PRIMARY_TIME_COLUMN_ANME)
    private long clickSecond;
    // 设置数据库字段名，副时间
    @ColumnInfo(name = RecordConstant.SECONDARY_TIME_COLUMN_ANME)
    private int nanoInSecond;
}

// 加入我们想要增加年、月、日，代码可能就会变成这样
@Entity(tableName = RecordConstant.TABLE_NAME)
public class Record {
    ....
    @ColumnInfo(name = RecordConstant.YEAR)
    private int year;
    @ColumnInfo(name = RecordConstant.MONTH)
    private int month;
    @ColumnInfo(name = RecordConstant.DAY)
    private int day;
    ...
}

// 定义日期类
public class Date {
    @ColumnInfo(name = RecordConstant.YEAR)
    private int year;
    @ColumnInfo(name = RecordConstant.MONTH)
    private int month;
    @ColumnInfo(name = RecordConstant.DAY)
    private int day;
}

@Entity(tableName = RecordConstant.TABLE_NAME)
public class Record {
    ....
    @ColumnInfo(name = RecordConstant.PRIMARY_TIME_COLUMN_ANME)
    private long clickSecond;
    @ColumnInfo(name = RecordConstant.SECONDARY_TIME_COLUMN_ANME)
    private int nanoInSecond;
    // 包含进日期对象
    @Embedded
    private Date date;
    ...
}

// 定义 insert 方法
@Insert(onConflict = OnConflictStrategy.REPLACE)
long insert(Record record);

// 在 DbTaskManager 中，定义下面的方法，使用了事务。
// 事务的使用有一个标准模版
public boolean createOrUpdateRecord(long id) {
    long newId;
    /**
     * 标准模块
     * database.beginTransaction();
     * try {
     *     database.setTransactionSuccessful();
     * } finally {
     *     database.endTransaction();
     * }
     */
    database.beginTransaction();
    try {
        List<Record> records = database.recordDao().queryAll();
        boolean isIn = false;
        Record record = null;
        for(Record record2 : records) {
            if(record2.getId() == id) {
                record = record2;
                break;
            }
        }
        if(record != null) {
            newId = database.recordDao().update(record);
        } else {
            newId = database.recordDao().insert(record);
        }
        // 设置事务成功，防止回滚或其他异常
        database.setTransactionSuccessful();
    } finally {
        database.endTransaction();
    }
    
    return newId > 0;
}