How to Connect to MySQL Database from Kotlin using JDBC?

In this tutorial, we will learn how to connect to MySQL Database from Kotlin using JDBC with the help a Kotlin Example Program.

Following is a step by step process explained to connect to MySQL Database from Kotlin using JDBC.

Step 1 : Add MySQL connector for java

MySQL connector for java works for Kotlin as well. Download MySQL connector for java, mysql-connector-java-5.1.42-bin.jar , from [https://dev.mysql.com/downloads/connector/j/5.1.html]. Open IntelliJ IDEA, Click on File in Menu, Click on Project Structure, Click on Libraries on the left panel, and add the jar to Libraries.

Step 2 : Establish a connection to MySQL Server

To establish a connection to MySQL Server

1. Prepare username and password as properties
2. Use Class.forName() to create an instance for JDBC Driver.
3. Use DriverManager.getConnection() to create a connection to the SQL server. The first argument to this function is the URL that specifies the location of MySQL server. The second argument has credentials to login to the server.

val connectionProps = Properties()
connectionProps.put("user", username)
connectionProps.put("password", password)
try {
    Class.forName("com.mysql.jdbc.Driver").newInstance()
    conn = DriverManager.getConnection(
            "jdbc:" + "mysql" + "://" +
                    "127.0.0.1" +
                    ":" + "3306" + "/" +
                    "",
            connectionProps)
} catch (ex: SQLException) {
    // handle any errors
    ex.printStackTrace()
} catch (ex: Exception) {
    // handle any errors
    ex.printStackTrace()
}

Step 3 : Execute MySQL Query to show DATABASES available

var stmt: Statement? = null
var resultset: ResultSet? = null

try {
    stmt = conn!!.createStatement()
    resultset = stmt!!.executeQuery("SHOW DATABASES;")

    if (stmt.execute("SHOW DATABASES;")) {
        resultset = stmt.resultSet
    }

    while (resultset!!.next()) {
        println(resultset.getString("Database"))
    }
} catch (ex: SQLException) {
    // handle any errors
    ex.printStackTrace()
}

Example 1 – Connect to MySQL Database from Kotlin using JDBC

The following program connects to a specific MySQL server and executes ‘SHOW DATABASES;’ query.

example.kt

import java.sql.*
import java.util.Properties
 
/**
 * Program to list databases in MySQL using Kotlin
 */
object MySQLDatabaseExampleKotlin {
 
    internal var conn: Connection? = null
    internal var username = "username" // provide the username
    internal var password = "password" // provide the corresponding password
 
    @JvmStatic fun main(args: Array<String>) {
        // make a connection to MySQL Server
        getConnection()
        // execute the query via connection object
        executeMySQLQuery()
    }
 
    fun executeMySQLQuery() {
        var stmt: Statement? = null
        var resultset: ResultSet? = null
 
        try {
            stmt = conn!!.createStatement()
            resultset = stmt!!.executeQuery("SHOW DATABASES;")
 
            if (stmt.execute("SHOW DATABASES;")) {
                resultset = stmt.resultSet
            }
 
            while (resultset!!.next()) {
                println(resultset.getString("Database"))
            }
        } catch (ex: SQLException) {
            // handle any errors
            ex.printStackTrace()
        } finally {
            // release resources
            if (resultset != null) {
                try {
                    resultset.close()
                } catch (sqlEx: SQLException) {
                }
 
                resultset = null
            }
 
            if (stmt != null) {
                try {
                    stmt.close()
                } catch (sqlEx: SQLException) {
                }
 
                stmt = null
            }
 
            if (conn != null) {
                try {
                    conn!!.close()
                } catch (sqlEx: SQLException) {
                }
 
                conn = null
            }
        }
    }
 
    /**
     * This method makes a connection to MySQL Server
     * In this example, MySQL Server is running in the local host (so 127.0.0.1)
     * at the standard port 3306
     */
    fun getConnection() {
        val connectionProps = Properties()
        connectionProps.put("user", username)
        connectionProps.put("password", password)
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance()
            conn = DriverManager.getConnection(
                    "jdbc:" + "mysql" + "://" +
                            "127.0.0.1" +
                            ":" + "3306" + "/" +
                            "",
                    connectionProps)
        } catch (ex: SQLException) {
            // handle any errors
            ex.printStackTrace()
        } catch (ex: Exception) {
            // handle any errors
            ex.printStackTrace()
        }
    }
}

Output

information_schema
mysql
performance_schema
studentsDB
sys

Conclusion

In this Kotlin Tutorial, we have learnt to connect to MySQL Database from Kotlin using JDBC with the help of Kotlin Example Program.