Hướng dẫn về Khung tiếp xúc Kotlin

1. Giới thiệu

Trong hướng dẫn này, chúng ta sẽ xem xét cách truy vấn cơ sở dữ liệu quan hệ bằng Exposed.

Exposed là một thư viện mã nguồn mở (giấy phép Apache) được phát triển bởi JetBrains, cung cấp một API Kotlin thành ngữ cho một số triển khai cơ sở dữ liệu quan hệ đồng thời giải quyết sự khác biệt giữa các nhà cung cấp cơ sở dữ liệu.

Exposed có thể được sử dụng như một DSL cấp cao trên SQL và như một ORM nhẹ (Ánh xạ quan hệ đối tượng). Do đó, chúng tôi sẽ đề cập đến cả hai cách sử dụng trong suốt quá trình hướng dẫn này.

2. Thiết lập khung tiếp xúc

Exposed chưa có trên Maven Central, vì vậy chúng tôi phải sử dụng một kho lưu trữ chuyên dụng:

  exposed exposed //dl.bintray.com/kotlin/exposed  

Sau đó, chúng ta có thể bao gồm thư viện:

 org.jetbrains.exposed exposed 0.10.4 

Ngoài ra, trong các phần sau, chúng tôi sẽ hiển thị các ví dụ sử dụng cơ sở dữ liệu H2 trong bộ nhớ:

 com.h2database h2 1.4.197 

Chúng tôi có thể tìm thấy phiên bản mới nhất của Exposed on Bintray và phiên bản mới nhất của H2 trên Maven Central.

3. Kết nối với Cơ sở dữ liệu

Chúng tôi xác định các kết nối cơ sở dữ liệu với lớp Cơ sở dữ liệu :

Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

Chúng tôi cũng có thể chỉ định người dùngmật khẩu dưới dạng các tham số được đặt tên:

Database.connect( "jdbc:h2:mem:test", driver = "org.h2.Driver", user = "myself", password = "secret")

Lưu ý rằng việc gọi kết nối không thiết lập kết nối với DB ngay lập tức. Nó chỉ lưu các thông số kết nối cho sau này.

3.1. Các thông số bổ sung

Nếu chúng tôi cần cung cấp các tham số kết nối khác, chúng tôi sẽ sử dụng phương thức kết nối quá tải khác cho phép chúng tôi toàn quyền kiểm soát việc thu thập kết nối cơ sở dữ liệu:

Database.connect({ DriverManager.getConnection("jdbc:h2:mem:test;MODE=MySQL") })

Phiên bản kết nối này yêu cầu một tham số đóng. Exposed gọi đóng bất cứ khi nào nó cần một kết nối mới với cơ sở dữ liệu.

3.2. Sử dụng Nguồn dữ liệu

Thay vào đó, nếu chúng tôi kết nối với cơ sở dữ liệu bằng DataSource , như thường xảy ra trong các ứng dụng doanh nghiệp (ví dụ: để hưởng lợi từ việc gộp kết nối), chúng tôi có thể sử dụng quá tải kết nối thích hợp :

Database.connect(datasource)

4. Mở giao dịch

Mọi hoạt động cơ sở dữ liệu trong Exposed cần một giao dịch hoạt động.

Các giao dịch phương pháp có một đóng cửa và gọi nó với một giao dịch tích cực:

transaction { //Do cool stuff }

Các giao dịch trở lại bất cứ điều gì trở về đóng cửa. Sau đó, Exposed tự động đóng giao dịch khi quá trình thực hiện khối kết thúc.

4.1. Cam kết và khôi phục

Khi khối giao dịch trở lại thành công, Exposed sẽ thực hiện giao dịch. Thay vào đó, khi quá trình đóng thoát ra bằng cách ném ra một ngoại lệ, khung công tác sẽ quay trở lại giao dịch.

Chúng tôi cũng có thể cam kết hoặc khôi phục giao dịch theo cách thủ công. Việc đóng mà chúng tôi cung cấp cho giao dịch thực sự là một phiên bản của lớp Giao dịch nhờ phép thuật Kotlin.

Do đó, chúng tôi có một phương thức cam kếtkhôi phục :

transaction { //Do some stuff commit() //Do other stuff }

4.2. Báo cáo ghi nhật ký

Khi tìm hiểu khuôn khổ hoặc gỡ lỗi, chúng tôi có thể thấy hữu ích khi kiểm tra các câu lệnh và truy vấn SQL mà Exposed gửi đến cơ sở dữ liệu.

Chúng tôi có thể dễ dàng thêm trình ghi như vậy vào giao dịch đang hoạt động:

transaction { addLogger(StdOutSqlLogger) //Do stuff }

5. Xác định bảng

Thông thường, trong Exposed, chúng tôi không làm việc với các chuỗi và tên SQL thô. Thay vào đó, chúng tôi xác định bảng, cột, khóa, mối quan hệ, v.v., bằng cách sử dụng DSL cấp cao.

Chúng tôi đại diện cho mỗi bảng với một thể hiện của lớp Table :

object StarWarsFilms : Table()

Exposed tự động tính toán tên của bảng từ tên lớp, nhưng chúng tôi cũng có thể cung cấp một tên rõ ràng:

object StarWarsFilms : Table("STAR_WARS_FILMS")

5.1. Cột

Một bảng là vô nghĩa nếu không có cột. Chúng tôi xác định các cột là thuộc tính của lớp bảng của chúng tôi:

object StarWarsFilms : Table() { val id = integer("id").autoIncrement().primaryKey() val sequelId = integer("sequel_id").uniqueIndex() val name = varchar("name", 50) val director = varchar("director", 50) }

We've omitted the types for brevity, as Kotlin can infer them for us. Anyway, each column is of type Column and it has a name, a type and possibly type parameters.

5.2. Primary Keys

As we can see from the example in the previous section, we can easily define indexes and primary keys with a fluent API.

However, for the common case of a table with an integer primary key, Exposed provides classes IntIdTable and LongIdTable that define the key for us:

object StarWarsFilms : IntIdTable() { val sequelId = integer("sequel_id").uniqueIndex() val name = varchar("name", 50) val director = varchar("director", 50) }

There's also a UUIDTable; furthermore, we can define our own variants by subclassing IdTable.

5.3. Foreign Keys

Foreign keys are easy to introduce. We also benefit from static typing because we always refer to properties known at compile time.

Suppose we want to track the names of the actors playing in each movie:

object Players : Table() { val sequelId = integer("sequel_id") .uniqueIndex() .references(StarWarsFilms.sequelId) val name = varchar("name", 50) }

To avoid having to spell the type of the column (in this case, integer) when it can be derived from the referenced column, we can use the reference method as a shorthand:

val sequelId = reference("sequel_id", StarWarsFilms.sequelId).uniqueIndex()

If the reference is to the primary key, we can omit the column's name:

val filmId = reference("film_id", StarWarsFilms)

5.4. Creating Tables

We can create the tables as defined above programmatically:

transaction { SchemaUtils.create(StarWarsFilms, Players) //Do stuff }

The tables are only created if they don't already exist. However, there's no support for database migrations.

6. Queries

Once we've defined some table classes as we've shown in the previous sections, we can issue queries to the database by using the extension functions provided by the framework.

6.1. Select All

To extract data from the database, we use Query objects built from table classes. The simplest query is one that returns all the rows of a given table:

val query = StarWarsFilms.selectAll()

A query is an Iterable, so it supports forEach:

query.forEach { assertTrue { it[StarWarsFilms.sequelId] >= 7 } }

The closure parameter, implicitly called it in the example above, is an instance of the ResultRow class. We can see it as a map keyed by column.

6.2. Selecting a Subset of Columns

We can also select a subset of the table's columns, i.e., perform a projection, using the slice method:

StarWarsFilms.slice(StarWarsFilms.name, StarWarsFilms.director).selectAll() .forEach { assertTrue { it[StarWarsFilms.name].startsWith("The") } }

We use slice to apply a function to a column, too:

StarWarsFilms.slice(StarWarsFilms.name.countDistinct())

Often, when using aggregate functions such as count and avg, we'll need a group by clause in the query. We'll talk about the group by in section 6.5.

6.3. Filtering With Where Expressions

Exposed contains a dedicated DSL for where expressions, which are used to filter queries and other types of statements. This is a mini-language based on the column properties we've encountered earlier and a series of boolean operators.

This is a where expression:

{ (StarWarsFilms.director like "J.J.%") and (StarWarsFilms.sequelId eq 7) }

Its type is complex; it's a subclass of SqlExpressionBuilder, which defines operators such as like, eq, and. As we can see, it is a sequence of comparisons combined together with and and or operators.

We can pass such an expression to the select method, which again returns a query:

val select = StarWarsFilms.select { ... } assertEquals(1, select.count())

Thanks to type inference, we don't need to spell out the complex type of the where expression when it's directly passed to the select method as in the above example.

Since where expressions are Kotlin objects, there are no special provisions for query parameters. We simply use variables:

val sequelNo = 7 StarWarsFilms.select { StarWarsFilms.sequelId >= sequelNo }

6.4. Advanced Filtering

The Query objects returned by select and its variants have a number of methods that we can use to refine the query.

For example, we might want to exclude duplicate rows:

query.withDistinct(true).forEach { ... }

Or we might want to only return a subset of the rows, for example when paginating the results for the UI:

query.limit(20, offset = 40).forEach { ... }

These methods return a new Query, so we can easily chain them.

6.5. OrderBy and GroupBy

The Query.orderBy method accepts a list of columns mapped to a SortOrder value indicating if sorting should be ascending or descending:

query.orderBy(StarWarsFilms.name to SortOrder.ASC)

While the grouping by one or more columns, useful in particular when using aggregate functions (see section 6.2.), is achieved using the groupBy method:

StarWarsFilms .slice(StarWarsFilms.sequelId.count(), StarWarsFilms.director) .selectAll() .groupBy(StarWarsFilms.director)

6.6. Joins

Joins are arguably one of the selling points of relational databases. In the most simple of cases, when we have a foreign key and no join conditions, we can use one of the built-in join operators:

(StarWarsFilms innerJoin Players).selectAll()

Here we've shown innerJoin, but we also have left, right and cross join available with the same principle.

Then, we can add join conditions with a where expression; for example, if there isn't a foreign key and we must perform the join explicitly:

(StarWarsFilms innerJoin Players) .select { StarWarsFilms.sequelId eq Players.sequelId }

In the general case, the full form of a join is the following:

val complexJoin = Join( StarWarsFilms, Players, onColumn = StarWarsFilms.sequelId, otherColumn = Players.sequelId, joinType = JoinType.INNER, additionalConstraint = { StarWarsFilms.sequelId eq 8 }) complexJoin.selectAll()

6.7. Aliasing

Thanks to the mapping of column names to properties, we don't need any aliasing in a typical join, even when the columns happen to have the same name:

(StarWarsFilms innerJoin Players) .selectAll() .forEach { assertEquals(it[StarWarsFilms.sequelId], it[Players.sequelId]) }

In fact, in the above example, StarWarsFilms.sequelId and Players.sequelId are different columns.

However, when the same table appears more than once in a query, we might want to give it an alias. For that we use the alias function:

val sequel = StarWarsFilms.alias("sequel")

We can then use the alias a bit like a table:

Join(StarWarsFilms, sequel, additionalConstraint = { sequel[StarWarsFilms.sequelId] eq StarWarsFilms.sequelId + 1 }).selectAll().forEach { assertEquals( it[sequel[StarWarsFilms.sequelId]], it[StarWarsFilms.sequelId] + 1) }

In the above example, we can see that the sequel alias is a table participating in a join. When we want to access one of its columns, we use the aliased table's column as a key:

sequel[StarWarsFilms.sequelId]

7. Statements

Now that we've seen how to query the database, let's look at how to perform DML statements.

7.1. Inserting Data

To insert data, we call one of the variants of the insert function. All variants take a closure:

StarWarsFilms.insert { it[name] = "The Last Jedi" it[sequelId] = 8 it[director] = "Rian Johnson" }

There are two notable objects involved in the closure above:

  • this (the closure itself) is an instance of the StarWarsFilms class; that's why we can access the columns, which are properties, by their unqualified name
  • it (the closure parameter) is an InsertStatement; it is a map-like structure with a slot for each column to insert

7.2. Extracting Auto-Increment Column Values

When we have an insert statement with auto-generated columns (typically auto-increment or sequences), we may want to obtain the generated values.

In the typical case, we only have one generated value and we call insertAndGetId:

val id = StarWarsFilms.insertAndGetId { it[name] = "The Last Jedi" it[sequelId] = 8 it[director] = "Rian Johnson" } assertEquals(1, id.value)

If we have more than one generated value, we can read them by name:

val insert = StarWarsFilms.insert { it[name] = "The Force Awakens" it[sequelId] = 7 it[director] = "J.J. Abrams" } assertEquals(2, insert[StarWarsFilms.id]?.value)

7.3. Updating Data

We can now use what we have learned about queries and insertions to update existing data in the database. Indeed, a simple update looks like a combination of a select with an insert:

StarWarsFilms.update ({ StarWarsFilms.sequelId eq 8 }) { it[name] = "Episode VIII – The Last Jedi" }

We can see the use of a where expression combined with an UpdateStatement closure. In fact, UpdateStatement and InsertStatement share most of the API and logic through a common superclass, UpdateBuilder, which provides the ability to set the value of a column using idiomatic square brackets.

When we need to update a column by computing a new value from the old value, we leverage the SqlExpressionBuilder:

StarWarsFilms.update ({ StarWarsFilms.sequelId eq 8 }) { with(SqlExpressionBuilder) { it.update(StarWarsFilms.sequelId, StarWarsFilms.sequelId + 1) } }

This is an object that provides infix operators (like plus, minus and so on) that we can use to build an update instruction.

7.4. Deleting Data

Finally, we can delete data with the deleteWhere method:

StarWarsFilms.deleteWhere ({ StarWarsFilms.sequelId eq 8 })

8. The DAO API, a Lightweight ORM

So far, we've used Exposed to directly map from operations on Kotlin objects to SQL queries and statements. Each method invocation like insert, update, select and so on results in a SQL string being immediately sent to the database.

However, Exposed also has a higher-level DAO API that constitutes a simple ORM. Let's now dive into that.

8.1. Entities

In the previous sections, we've used classes to represent database tables and to express operations over them, using static methods.

Moving a step further, we can define entities based on those table classes, where each instance of an entity represents a database row:

class StarWarsFilm(id: EntityID) : Entity(id) { companion object : EntityClass(StarWarsFilms) var sequelId by StarWarsFilms.sequelId var name by StarWarsFilms.name var director by StarWarsFilms.director }

Let's now analyze the above definition piece by piece.

In the first line, we can see that an entity is a class extending Entity. It has an ID with a specific type, in this case, Int.

class StarWarsFilm(id: EntityID) : Entity(id) {

Then, we encounter a companion object definition. The companion object represents the entity class, that is, the static metadata defining the entity and the operations we can perform on it.

Furthermore, in the declaration of the companion object, we connect the entity, StarWarsFilm – singular, as it represents a single row to the table, StarWarsFilms – plural, because it represents the collection of all the rows.

companion object : EntityClass(StarWarsFilms)

Finally, we have the properties, implemented as property delegates to the corresponding table columns.

var sequelId by StarWarsFilms.sequelId var name by StarWarsFilms.name var director by StarWarsFilms.director

Note that previously we declared the columns with val because they're immutable metadata. Now, instead, we're declaring the entity properties with var, because they're mutable slots in a database row.

8.2. Inserting Data

To insert a row in a table, we simply create a new instance of our entity class using the static factory method new in a transaction:

val theLastJedi = StarWarsFilm.new { name = "The Last Jedi" sequelId = 8 director = "Rian Johnson" }

Note that operations against the database are performed lazily; they're only issued when the warm cache is flushed. For comparison, Hibernate calls the warm cache a session.

This happens automatically when required; e.g., the first time we read the generated identifier, Exposed silently executes the insert statement:

assertEquals(1, theLastJedi.id.value) //Reading the ID causes a flush

Compare this behavior with the insert method from section 7.1., which immediately issues a statement against the database. Here, we're working at a higher level of abstraction.

8.3. Updating and Deleting Objects

To update a row, we simply assign to its properties:

theLastJedi.name = "Episode VIII – The Last Jedi"

While to delete an object we call delete on it:

theLastJedi.delete()

As with new, the update and operations are performed lazily.

Updates and deletions can only be performed on a previously loaded object. There is no API for massive updates and deletions. Instead, we have to use the lower-level API that we've seen in section 7. Still, the two APIs can be used together in the same transaction.

8.4. Querying

With the DAO API, we can perform three types of queries.

To load all the objects without conditions we use the static method all:

val movies = StarWarsFilm.all()

To load a single object by ID we call findById:

val theLastJedi = StarWarsFilm.findById(1)

If there's no object with that ID, findById returns null.

Finally, in the general case, we use find with a where expression:

val movies = StarWarsFilm.find { StarWarsFilms.sequelId eq 8 }

8.5. Many-to-One Associations

Just as joins are an important feature of relational databases, the mapping of joins to references is an important aspect of an ORM. So, let's see what Exposed has to offer.

Suppose we want to track the rating of each movie by users. First, we define two additional tables:

object Users: IntIdTable() { val name = varchar("name", 50) } object UserRatings: IntIdTable() { val value = long("value") val film = reference("film", StarWarsFilms) val user = reference("user", Users) }

Then, we'll write the corresponding entities. Let's omit the User entity, which is trivial, and move straight to the UserRating class:

class UserRating(id: EntityID): IntEntity(id) { companion object : IntEntityClass(UserRatings) var value by UserRatings.value var film by StarWarsFilm referencedOn UserRatings.film var user by User referencedOn UserRatings.user }

In particular, note the referencedOn infix method call on properties that represent associations. The pattern is the following: a var declaration, by the referenced entity, referencedOn the referencing column.

Properties declared this way behave like regular properties, but their value is the associated object:

val someUser = User.new { name = "Some User" } val rating = UserRating.new { value = 9 user = someUser film = theLastJedi } assertEquals(theLastJedi, rating.film)

8.6. Optional Associations

The associations we've seen in the previous section are mandatory, that is, we must always specify a value.

If we want an optional association, we must first declare the column as nullable in the table:

val user = reference("user", Users).nullable()

Then, we'll use optionalReferencedOn instead of referencedOn in the entity:

var user by User optionalReferencedOn UserRatings.user

That way, the user property will be nullable.

8.7. One-to-Many Associations

We might also want to map the opposite side of the association. A rating is about a film, that's what we model in the database with a foreign key; consequently, a film has a number of ratings.

To map a film's ratings, we simply add a property to the “one” side of the association, that is, the film entity in our example:

class StarWarsFilm(id: EntityID) : Entity(id) { //Other properties elided val ratings by UserRating referrersOn UserRatings.film }

The pattern is similar to that of many-to-one relationships, but it uses referrersOn. The property thus defined is an Iterable, so we can traverse it with forEach:

theLastJedi.ratings.forEach { ... }

Note that, unlike regular properties, we've defined ratings with val. Indeed, the property is immutable, we can only read it.

The value of the property has no API for mutation as well. So, to add a new rating, we must create it with a reference to the film:

UserRating.new { value = 8 user = someUser film = theLastJedi }

Then, the film's ratings list will contain the newly added rating.

8.8. Many-to-Many Associations

In some cases, we might need a many-to-many association. Let's say we want to add a reference an Actors table to the StarWarsFilm class:

object Actors: IntIdTable() { val firstname = varchar("firstname", 50) val lastname = varchar("lastname", 50) } class Actor(id: EntityID): IntEntity(id) { companion object : IntEntityClass(Actors) var firstname by Actors.firstname var lastname by Actors.lastname }

Having defined the table and the entity, we need another table to represent the association:

object StarWarsFilmActors : Table() { val starWarsFilm = reference("starWarsFilm", StarWarsFilms).primaryKey(0) val actor = reference("actor", Actors).primaryKey(1) }

The table has two columns that are both foreign keys and that also make up a composite primary key.

Finally, we can connect the association table with the StarWarsFilm entity:

class StarWarsFilm(id: EntityID) : IntEntity(id) { companion object : IntEntityClass(StarWarsFilms) //Other properties elided var actors by Actor via StarWarsFilmActors }

Tại thời điểm viết bài này, không thể tạo một thực thể có số nhận dạng đã tạo và đưa nó vào một liên kết nhiều-nhiều trong cùng một giao dịch.

Trên thực tế, chúng tôi phải sử dụng nhiều giao dịch:

//First, create the film val film = transaction { StarWarsFilm.new { name = "The Last Jedi" sequelId = 8 director = "Rian Johnson"r } } //Then, create the actor val actor = transaction { Actor.new { firstname = "Daisy" lastname = "Ridley" } } //Finally, link the two together transaction { film.actors = SizedCollection(listOf(actor)) }

Ở đây, chúng tôi đã sử dụng ba giao dịch khác nhau để thuận tiện. Tuy nhiên, chỉ cần hai cái là đủ.

9. Kết luận

Trong bài viết này, chúng tôi đã đưa ra một cái nhìn tổng quan kỹ lưỡng về khung Exposed cho Kotlin. Để biết thêm thông tin và ví dụ, hãy xem wiki Tiếp xúc.

Việc triển khai tất cả các ví dụ và đoạn mã này có thể được tìm thấy trong dự án GitHub.