Dialects & Native SQL
It’s known that there is a uniform standard for SQL language, but beyond the standard, many databases still have their special features. The core module of Ktorm (ktorm-core) only provides support for standard SQL, if we want to use some special features of a database, we need to support dialects.
Enable Dialects
In Ktorm, SqlDialect
interface is the abstraction of dialects. Ktorm supports many dialects now, each of them is published as a separated module independent of ktorm-core, and they all provide their own implementation of SqlDialect
.
Database Name | Module Name | SqlDialect Implementation |
---|---|---|
MySQL | ktorm-support-mysql | org.ktorm.support.mysql.MySqlDialect |
PostgreSQL | ktorm-support-postgresql | org.ktorm.support.postgresql.PostgreSqlDialect |
Oracle | ktorm-support-oracle | org.ktorm.support.oracle.OracleDialect |
SqlServer | ktorm-support-sqlserver | org.ktorm.support.sqlserver.SqlServerDialect |
SQLite | ktorm-support-sqlite | org.ktorm.support.sqlite.SQLiteDialect |
Now let’s take MySQL’s on duplicate key update
feature as an example, learning how to enable dialects in Ktorm.
This feature can determine if there is a conflict while records are being inserted into databases, and automatically performs updates if any conflict exists, which is not supported by standard SQL. To use this feature, we need to add the dependency of ktorm-support-mysql to our projects. If we are using Maven:
1 | <dependency> |
Or Gradle:
1 | compile "org.ktorm:ktorm-support-mysql:${ktorm.version}" |
Having the dependency, we also need to modify the calling of the Database.connect
function, this function is used to create Database
objects. We need to specify its dialect
parameter, telling Ktorm which SqlDialect
implementation should be used.
1 | val database = Database.connect( |
Since version 2.4, Ktorm’s dialect modules start following the convention of JDK
ServiceLoader
SPI, so we don’t need to specify thedialect
parameter explicitly anymore while creatingDatabase
instances. Ktorm auto detects one for us from the classpath. We just need to insure the dialect module exists in the dependencies.
Now we have enabled MySQL’s dialect implementation and all of its features are available. Try to call the insertOrUpdate
function:
1 | database.insertOrUpdate(Employees) { |
Generated SQL:
1 | insert into t_employee (id, name, job, salary, hire_date, department_id) values (?, ?, ?, ?, ?, ?) |
Perfect!
Built-in Dialects’ Features
Now, let’s talk about Ktorm’s built-in dialects’ features.
Here is a list of features provided by module ktorm-support-mysql:
- Support paginations via
limit
function, translating paging expressions into MySQL’slimit ?, ?
statement. - Add
bulkInsert
function for bulk insertion, different frombatchInsert
in the core module, it uses MySQL’s bulk insertion syntax and the performance is much better. - Add
insertOrUpdate
function for data “upsert”, based on MySQL’s feature ofon duplicate key update
. - Add
naturalJoin
function for natural joining, based onnatural join
keyword. - Add
jsonContains
function to determine if the specific item exists in a json array, based on thejson_contains
function in MySQL. - Add
jsonExtract
function to obtain fields in a json, that’s the->
grammar in MySQL, based onjson_extract
function. - Add
match
andagainst
functions for fulltext search, based on MySQL’smatch ... against ...
syntax. - Add other functions such as
rand
,ifnull
,greatest
,least
,dateDiff
,replace
, etc, supporting the corresponding functions in MySQL.
ktorm-support-postgresql provides:
- Support paginations via
limit
function, translating paging expressions into PostgreSQL’slimit ? offset ?
statement. - Add
insertOrUpdate
function for data “upsert”, based on PostgreSQL’son conflict (key) do update set
syntax. - Add
ilike
operator for string matchings ignoring cases, based on PostgreSQL’silike
keyword. - Add
hstore
data type and a series of operators for it, such as->
,||
,?
,?&
,?|
and so on.
ktorm-support-oracle provides:
- Support paginations via
limit
function, translating paging expressions into Oracle’s paging SQL usingrownum
.
ktorm-support-sqlserver provides:
- Support paginations via
limit
function, translating paging expressions into SqlServer’s paging SQL usingtop
androw_number() over(...)
. - Support
datetimeoffset
data type.
ktorm-support-sqlite provides:
- Support paginations via
limit
function, translating paging expressions into SQLite’slimit ?, ?
statement.
Ktorm always claims that we are supporting many dialects, but actually, the support for databases other than MySQL is really not enough. I’m so sorry about that, my time and energy are really limited, so I have to lower the precedence of supporting other databases.
Fortunately, the standard SQL supported by the core module is enough for most scenarios, so there is little influence on our business before the dialects are completed.
Ktorm’s design is open, it’s easy to add features to it, and we have learned how to write our own extensions in the former sections. So we can also implement dialects by ourselves if it’s really needed. Welcome to fork the repository and send your pull requests to me, I’m glad to check and merge your code. Looking forward to your contributions!
Native SQL
In some rare situations, we have to face some special businesses that Ktorm may not be able to support now, such as some complex queries (eg. correlated subqueries), special features of a dialect (eg. SQL Server’s cross apply), or DDL that operates the table schemas.
To solve the problem, Ktorm provides a way for us to execute native SQLs directly. We need to obtain a database connection via database.useConnection
first, then perform our operations by writing some code with JDBC. Here is an example:
1 | val names = database.useConnection { conn -> |
At first glance, there are only boilerplate JDBC code in the example, but actually, it’s also benefited from some convenient functions of Ktorm:
useConnection
function is used to obtain or create connections. If the current thread has opened a transaction, then this transaction’s connection will be passed to the closure. Otherwise, Ktorm will pass a new-created connection to the closure and auto close it after it’s not useful anymore. Ktorm also uses this function to obtain connections to execute generated SQLs. So, by callinguseConnection
, we can share the transactions or connection pools with Ktorm’s internal SQLs.asIterable
function is used to wrapResultSet
instances asIterable
, then we can iterate the result sets by for-each loops, or process them via extension functions of Kotlin standard lib, such asmap
,flatMap
, etc.
Note: Although Ktorm provides supports for native SQLs, we don’t recommend you to use it, because it violates the design philosophy of Ktorm. Once native SQL is used, we will lose the benefits of the strong typed DSL, so please ensure whether it’s really necessary to do that. In general, most complex SQLs can be converted to equivalent simple joining queries, and most special keywords and SQL functions can also be implemented by writing some extensions with Ktorm.