vok-orm 自订sql 查询 / db connection - day21

目标

延续前例
查询指定学期成绩，并且按平均进行排序，如下图

本范例资料表

create TABLE Student(
  id bigint auto_increment PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  birthday DATE,
  created TIMESTAMP,
  gender VARCHAR(20) NOT NULL,
  height DOUBLE NOT NULL,
  weight DOUBLE NOT NULL,
  student_id VARCHAR(20),
  photo VARCHAR(100)
);

create TABLE Grade(
 id bigint auto_increment PRIMARY KEY,
 mandarin DOUBLE NOT NULL,
 description VARCHAR(50) NOT NULL,
 english DOUBLE NOT NULL,
 math DOUBLE NOT NULL,
 pe DOUBLE NOT NULL,
 student_id bigint not null REFERENCES Student(id)
)

Vaadin 的 SQL 查询

资料库连线

前述范例中 VoK 能够将资料表映射到实体类，因为在Bootstrap.kt已经设定好资料库连线，在此使用Hikari-CP管理JDB资料库连接池 (connection pool)。

    val cfg = HikariConfig().apply {
        driverClassName = Driver::class.java.name
        jdbcUrl = "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1"
        username = "sa"
        password = ""
    }
    VaadinOnKotlin.dataSource = HikariDataSource(cfg)

设定好 jdbc url 并 setDataSource 初始化 connection pool後，只要使用db{}即可取得资料库连线，如下所示:

db {
    handle.createQuery("select distinct description from Grade")
        .mapTo(String::class.java)
        .list()
}

删除/新增/修改

vok-orm 提供了可直接执行SQL的弹性作法，一般而言，单一表单CRUD较倾向使用KEntity所提供的save()、create()、delete()、validate()等方法，当上述方法皆无法达成目的时，即会采用此法。

范例 :

db {
    handle.createQuery("create TABLE CATEGORY (id bigint auto_increment PRIMARY KEY, name varchar(200) NOT NULL );").executeUpdate()
}

db {
    (0..100).forEach { Category(name = "cat $it").save() }
}

db {
    handle.createUpdate("delete from Category where id = :id")
        .bind("id", id)
        .execute()
}

查询已上传成绩之学期

查学期

成绩资料表 Grade 为实体映射类(Entity)，可直接使用db{} 从connection pool取得 connection。
在Grade加入一静态方法getSemester()，请开启Grade.kt

data class Grade(
    override var id: Long? = null,
    var student_id: Long? = null,
    var description: String? = null,
    var english: Double? = null,
    var math: Double? = null,
    var mandarin:Double? = null,
    var pe: Double? = null
): KEntity<Long> {
    companion object: Dao<Grade, Long>(Grade::class.java){
        fun getSemester(): List<String> = db {
            handle.createQuery("select distinct description from Grade")
                .mapTo(String::class.java)
                .list()
        }
    }
    val student: Student?
        get() = student_id?.let { Student.getById(student_id!!)} ?: null
}

因仅查询description栏位，使用mapTo()方法将结果映射为字串型态List

ComboBox 显示查询结果

    comboBox<String>("学期"){
        setItems(Grade.getSemester())
        addValueChangeListener {
            semester = value
        }
    }