Database connection with Ktor(Kotlin)

In this blog, I will talk to you about a new framework in Ktor(Kotlin). if you don't know about Ktor you can watch my previous blog about Ktor-Click Here
In KTOR, You can connect with the database through Exposed library of Kotlin. So for the making of connection through the MySql database, you have to create a database.

1. I have created data as below SQL query.

	CREATE DATABASE IF NOT EXISTS mytestdb;
	USE mytestdb;
	create table user(id int,name varchar(50),email varchar(50));
	INSERT INTO `user` (`uid`, `name`, `email`) VALUES ('1', 'anuj', 'anuj@gmail.com')
	INSERT INTO `user` (`uid`, `name`, `email`) VALUES ('2', 'anuj', 'anuj@gmail.com')

view raw mytestdb.sql hosted with ❤ by GitHub

After that, you have to create an empty KTOR project

 Now you can now try to connect through DB with help of Kotlin and expose library.
first of all, you have to add some line in build.gradle file to add expose and MySQL connector etc in your project inside dependency-

	compile "org.jetbrains.kotlinx:kotlinx-html-jvm:0.6.4"
	compile 'org.jetbrains.exposed:exposed:0.8.5'
	compile group: 'mysql', name: 'mysql-connector-java', version: '6.0.6'
	compile group: 'org.slf4j', name: 'slf4j-simple', version: '1.7.25'
	compile group: 'com.google.code.gson', name: 'gson', version: '2.8.1'
	testCompile group: 'junit', name: 'junit', version: '4.12'

view raw build.gradle hosted with ❤ by GitHub

After that add these lines in the repository-

	maven { url 'https://kotlin.bintray.com/ktor' }
	maven { url "https://dl.bintray.com/kotlin/kotlinx" }
	maven { url "https://dl.bintray.com/kotlin/exposed" }

view raw build.gradle hosted with ❤ by GitHub

After that my final build.gradle file looks like below-

	buildscript {
	repositories {
	jcenter()
	}
	dependencies {
	classpath "org.jetbrains.kotlin:kotlin-gradle-plugin:$kotlin_version"
	}
	}
	apply plugin: 'kotlin'
	apply plugin: 'application'
	group 'com.example'
	version '0.0.1'
	mainClassName = "io.ktor.server.netty.EngineMain"
	sourceSets {
	main.kotlin.srcDirs = main.java.srcDirs = ['src']
	test.kotlin.srcDirs = test.java.srcDirs = ['test']
	main.resources.srcDirs = ['resources']
	test.resources.srcDirs = ['testresources']
	}
	repositories {
	mavenLocal()
	jcenter()
	maven { url 'https://kotlin.bintray.com/ktor' }
	maven { url "https://dl.bintray.com/kotlin/kotlinx" }
	maven { url "https://dl.bintray.com/kotlin/exposed" }
	}
	dependencies {
	compile "org.jetbrains.kotlin:kotlin-stdlib-jdk8:$kotlin_version"
	compile "io.ktor:ktor-server-netty:$ktor_version"
	compile "ch.qos.logback:logback-classic:$logback_version"
	testCompile "io.ktor:ktor-server-tests:$ktor_version"
	compile "org.jetbrains.kotlinx:kotlinx-html-jvm:0.6.4"
	compile 'org.jetbrains.exposed:exposed:0.8.5'
	compile group: 'mysql', name: 'mysql-connector-java', version: '6.0.6'
	compile group: 'org.slf4j', name: 'slf4j-simple', version: '1.7.25'
	compile group: 'com.google.code.gson', name: 'gson', version: '2.8.1'
	testCompile group: 'junit', name: 'junit', version: '4.12'
	}

view raw build.gradle hosted with ❤ by GitHub

Then you can make an object of data table like below-

	object User : Table("user") {
	val id = integer("id")
	val name = varchar("name", length = 50)
	val email = varchar("email",length=50)
	}
	data class Users(val id: Int, val name: String, val email: String)

view raw Application.kt hosted with ❤ by GitHub

if you are from PHP MySQL background you can think what the fuck going on here but it's not as easy as in PHP.
In this blog, I will tell you about how to make a connection in JDBC driver the same as we do in java.
I am not using any external library like the connectionPool library in Spring framework of java.
Add below lines for connection here root is my database username and web is password-

	fun initDB() {
	val url = "jdbc:mysql://root:web@localhost:3306/mytestdb?useUnicode=true&serverTimezone=UTC"
	val driver = "com.mysql.cj.jdbc.Driver"
	Database.connect(url, driver)
	}

view raw Application.kt hosted with ❤ by GitHub

now add below fun to fetch data In this I have written a DSL query and fetch records in form of ArrayList after that I converted it in JSON with GSON library -

	fun getTopuserData(): String {
	var json: String = ""
	transaction {
	val res = User.selectAll().orderBy(User.id, false).limit(5)
	val c = ArrayList<Users>()
	for (f in res) {
	c.add(Users(id = f[User.id], name = f[User.name], email = f[User.email]))
	}
	json = Gson().toJson(c);
	}
	return json
	}

view raw Application.kt hosted with ❤ by GitHub

after that create main fun to push data by HTTP protocol by the netty app engine.

	fun main(args: Array<String>) {
	initDB()
	embeddedServer(Netty, 8080) {
	routing {
	get("/") {
	call.respondText(getTopuserData(), ContentType.Text.Plain)
	}
	}
	}.start(wait = true)
	}

view raw Application.kt hosted with ❤ by GitHub

after doing all of this my final Application.kt look like below-

	package com.example
	import com.google.gson.Gson
	import io.ktor.application.*
	import io.ktor.http.ContentType
	import io.ktor.response.*
	import io.ktor.request.*
	import io.ktor.routing.get
	import io.ktor.routing.routing
	import io.ktor.server.engine.embeddedServer
	import io.ktor.server.netty.Netty
	import org.jetbrains.exposed.sql.Database
	import org.jetbrains.exposed.sql.Table
	import org.jetbrains.exposed.sql.selectAll
	import org.jetbrains.exposed.sql.transactions.transaction
	object User : Table("user") {
	val id = integer("id")
	val name = varchar("name", length = 50)
	val email = varchar("email",length=50)
	}
	data class Users(val id: Int, val name: String, val email: String)
	fun initDB() {
	val url = "jdbc:mysql://root:web@localhost:3306/mytestdb?useUnicode=true&serverTimezone=UTC"
	val driver = "com.mysql.cj.jdbc.Driver"
	Database.connect(url, driver)
	}
	fun getTopuserData(): String {
	var json: String = ""
	transaction {
	val res = User.selectAll().orderBy(User.id, false).limit(5)
	val c = ArrayList<Users>()
	for (f in res) {
	c.add(Users(id = f[User.id], name = f[User.name], email = f[User.email]))
	}
	json = Gson().toJson(c);
	}
	return json
	}
	fun main(args: Array<String>) {
	initDB()
	embeddedServer(Netty, 8080) {
	routing {
	get("/") {
	call.respondText(getTopuserData(), ContentType.Text.Plain)
	}
	}
	}.start(wait = true)
	}

view raw Application.kt hosted with ❤ by GitHub

Now you can make a connection with DB with the help of Ktor in Kotlin I will try to make a few more blogs in Kotlin Ktor to explore more to this framework and Language.
If you like this blog you can follow our blog for more awesome content.
To know more about how to create API in Ktor- ClickHere

                                           project code-CLICK HERE