如何让 Grid 显示关联式 SQL 查询的资料 - day22

承前例，接着要完成选取学期後 grid 动态查询更新

vok-orm 资料加载

前端显示多笔资料内容像是list、recordset时，一般会使用table(html)、grid这一类元件来显示。通常这类表格会提供排序和过滤功能，别忘了我们写的是网页程序，资料是由後端提供给前端显示，在提供资料给前端显示时，一次资料量不宜过多，通常以页为单位提供资料。

vok-ork 提供的 Data Loaders能达成上述所提到的 sorting、filtering、lazy-loading等功能。vok-ork 提供两种易於使用的data loaders EntityDataLoader和SqlDataLoader。

• 前面范例里显示学生资料即是EntityDataLoader

    setDataLoader(Student.dataLoader)

• 在接下来的例子中，将会使用到SqlDataLoader

Grid 资料来源

• List

预设情况下，Grid 绑定一个item list，使用setItems()绑定资料。例:

val students = listOf<Student>(Student(name = "A"), 
                               Student(name = "B"), 
                               Student(name = "C"))
    :
    :
grid.setItems(students)

• DataLoader

例:

data class Student(
    override var id: Long? = null,
    var name: String? = null,
    var birthday: LocalDate? = null,
    var created: Date? = null,
    var gender: Gender? = null,
    var height: Double? = null,
    var weight: Double? = null,
    var photo: String? = null,
    var student_id: String? = null
) : KEntity<Long> {
    companion object : Dao<Student, Long>(Student::class.java)
}

    grid{
        setDataLoader(Student.dataLoader)
    }

• DataProvider

官方文件中对於 Interface DataProvider说明如下

A common interface for fetching data from a backend. The DataProvider interface is used by listing components implementing HasDataProvider or HasFilterableDataProvider. The listing component will provide a Query object with request information, and the data provider uses this information to return a stream containing requested beans.

Vaadin comes with a ready-made solution for in-memory data, known as ListDataProvider which can be created using static create methods in this interface. For custom backends such as SQL, EntityManager, REST APIs or SpringData, use a BackEndDataProvider or its subclass.

如前所述，在网页程序里，前端资料是由後端提供给前端显示，grid 动态更改资料来源，不能直接将 List 指定给setItems()，必需使用ListDataProvider。接下来的范例中，我们将会使用ListDataProvider。

查询符合条件成绩资料，并排序

1. 设定 Bean

此为查询结果要映射的Bean类，要取得的栏位除了学生成绩，还有平均、学生姓名。这个bean类资料要从後端传到前端，所以必需可序列化，请记得加上 Serializable

data class StudentGrade(
    var description: String? = null,
    var english: Double? = null,
    var math: Double? = null,
    var mandarin: Double? = null,
    var pe: Double? = null,
    var student_id: Long? = null,
    var name: String? = null,
    var avg: Double? = null
) : Serializable

2. 取得学生成绩

vok-orm 所提供的 SqlDataLoader所提供的SQL中有几个比较特别的{{WHERE}}、{{ORDER}}、{{PAGING}}，都是预留空间。当指定条件时会进行替代。

companion object {
    val sql = "select grade.*,round((grade.english + grade.math + grade.mandarin + grade.pe)/4,2) as avg, student.name from grade, student where grade.student_id = student.id {{WHERE}} order by 1=1{{ORDER}} {{PAGING}}"
        
    fun getListDataProvider(semester: String?): ListDataProvider<StudentGrade> {
        val filter = buildFilter<StudentGrade> { 
            "grade.description = :semester"("semester" to semester) 
        }
        val provider = SqlDataLoader(DaoOfAny(StudentGrade::class.java), sql)
        val result: List<StudentGrade> = 
            provider.fetch(filter, sortBy = listOf("avg".desc))
        
        return DataProvider.ofCollection(result)
    }
}

在此例中，若semester = "第 1 学期"，最终 sql 为

select grade.*,round((grade.english + grade.math + grade.mandarin + grade.pe)/4,2) as avg, student.name from grade, student where grade.student_id = student.id and grade.description = `第 1 学期` order by 1=1 , avg desc

对於SQL语法熟悉的开发者而言，应该非常乐见 SqlDataLoader

3. 设定 Grid 显示栏位

    grid = grid {
        isExpand = true
        setSizeFull()
        dataProvider = StudentGrade.getListDataProvider(null)
        addColumn(StudentGrade::name).setHeader("姓名")
        addColumn(StudentGrade::description).setHeader("学期")
        addColumn(StudentGrade::english).setHeader("英文")
        addColumn(StudentGrade::mandarin).setHeader("国文")
        addColumn(StudentGrade::math).setHeader("数学")
        addColumn(StudentGrade::pe).setHeader("体育")
        addColumn(StudentGrade::avg).setHeader("平均")
    }

4. 选取学期

使用 valueChangeListener 来倾听选项是否改变，若有异动则更新 semester

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

5. 送出查询、更新Grid

    button("QUERY") {
        onLeftClick {
            grid.dataProvider = StudentGrade.getListDataProvider(semester)
        }
    }

上述程序之测试资料为 10000 笔学生资料，每位学生5个学期成绩