External MySQL database in Kotlin

Question

I'm making a Kotlin application which should have an external MySQL database. How can I set it up to get desired results?

The application should get a result from External MySQL database as a string. I already tried to use this link but It seems that there is something wrong.

I use this code:

class DBConnection {
    internal var conn: Connection? = null
    internal var username = "some username" 
    internal var password = "some password" 

    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
            }
        }
    }

    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" + "://" +
                        "remotemysql.com" +
                        ":" + "3306" + "/" +
                        "",
                connectionProps)
        } catch (ex: SQLException) {
            // handle any errors
            ex.printStackTrace()
        } catch (ex: Exception) {
            // handle any errors
            ex.printStackTrace()
        }
    }
}

And in a main file I use this:

val submit: Button = findViewById(R.id.submitBtn)
submit.setOnClickListener{
                val dbConnection: DBConnection
                dbConnection = DBConnection()
                dbConnection.getConnection()
                dbConnection.executeMySQLQuery()

        }

For now I just need to get a result from the database. When I used the mentioned code I got this error

E/AndroidRuntime: FATAL EXCEPTION: main
    Process: sk.letsdream, PID: 18521
    kotlin.KotlinNullPointerException
        at sk.letsdream.dbMethods.DBConnection.executeMySQLQuery(DBConnection.kt:19)
        at sk.letsdream.DochadzkaActivity$onCreate$2.onClick(DochadzkaActivity.kt:86)
        at android.view.View.performClick(View.java:7125)
        at android.view.View.performClickInternal(View.java:7102)
        at android.view.View.access$3400(View.java:801)
        at android.view.View$PerformClick.run(View.java:27301)
        at android.os.Handler.handleCallback(Handler.java:883)
        at android.os.Handler.dispatchMessage(Handler.java:100)
        at android.os.Looper.loop(Looper.java:214)
        at android.app.ActivityThread.main(ActivityThread.java:7319)
        at java.lang.reflect.Method.invoke(Native Method)
        at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
        at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:934)

EDIT: After adding mysql-connector-java-8.0.16 it gives me an error that min. SDK should be 26. But after changing SDK to 26 it gives me this type error:

java.lang.BootstrapMethodError: Exception from call site #39 bootstrap method
        at com.mysql.cj.jdbc.AbandonedConnectionCleanupThread.<clinit>(AbandonedConnectionCleanupThread.java:58)

Where 58. line is:

Class.forName("com.mysql.jdbc.Driver").newInstance()

When we had older version of mysql connector mysql-connector-java-5.1.47 it threw this error:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.

on this line of code:

var conn = DriverManager.getConnection("jdbc:mysql://remotemysql.com:3306/My_DB_Name", connectionProps)

Solution

Android does not support MySQL out of the box. The "normal" way to access your database would be to put a Restful server in front of it and use the HTTPS protocol to connect to the Restful front end.

For Access the MySQL in you're application. You can try with Restful API. With Restful API you can proceed with Same action you want to do with MySQL. For interact with API Retrofit is better library.

One of local database SQLite. But i won't recommend to use it.

Or You can go with Firebase CloudStore. Better and easy NoSQL database.

If you have any query Just comment it down.

Comments

[Apuna12]

Finally someone! Thanks for the answer. I'll try some of your advices. After that I'll accept your answer.

[Ashish]

if you're looking for any help just comment it down

[Apuna12]

Ok I tried to make a "middle" component in php and it seems that it works. I used okhttp to make this. O made a function in pho which makes a request to my MySQL database and send it to Kotlin via HTTP request. I think that this is an easiest way to do it. Thanks for the help :)