CODEWITHSUNDEEP
javaunit-testingjdbcmockingkotlin
Ivar Reukers
Ivar Reukers

Reputation: 7719

Kotlin/Java - Testing Database-data provided REST API

I'm trying to test a connection to my Database and trying to configure a Mock Database. I only have no clue how to. How is it possible with any test framework to mock a database and test my method (shown below) getUserById?

Since this is using raw jdbc, how can I overwrite this getConnection() method to be using the mock database instead of the real one? Or isn't this the way to go?

I haven't tested a database connection/output of API ever, so I don't know where to start.

This is my jdbc class:

import java.sql.Connection
import java.sql.DriverManager
import java.sql.ResultSet
import java.sql.Statement
import java.util.ArrayList
import java.util.Properties
import bye.domain.*

class jdbcTrial {
    val url: String = "jdbc:postgresql://196.21.2.12:5432/events"
    //val props: Properties = Properties();

    val DB_DRIVER = "org.postgresql.Driver";


    // used for getting the comments
    fun getUserById(id: Int): User {
        val query = "select * from user where id = ${id};";
        return getSingleUser(query)
    }

    /*

        Singles

    */

    fun getSingleUser(query: String): User {
        val conn = this.getConnection()
        var user = User()
        val statement: Statement = conn.createStatement()
        val rs: ResultSet = statement.executeQuery(query)
        while (rs.next()) {
            user = convertToUser(rs)
        }
        return user
    }

    /*

        Converting

    */
    fun convertToUser(rs: ResultSet): User {
        val id = rs.getInt("id")
        val uname = rs.getString("username")
        val type = rs.getString("usertype")
        return User(id, uname, type)
    }


    fun getConnection(): Connection {

        Class.forName(DB_DRIVER).newInstance()
        val conn: Connection = DriverManager.getConnection(url, "username", "password")

        return conn
    }
}

Upvotes: 0

Views: 2399

Answers (1)

piotrek
piotrek

Reputation: 14550

in the real world you can see at least the following ways:

  1. you don't test it. code is simple, db tests may be expensive, slow etc
  2. manual tests on 1 common remotely installed instance. pretty useless as it doesn't allow you (or makes it really difficult) to experiment with changing schema, deleteting tables, data etc
  3. for tests use in-memory db (h2, hsql etc). it lets you start pretty easy because it starts and stops db on demand. the problems are: you will still have to clear db between transaction (if you do transactional tests. you can use frameworks like dbUnit or have full control and do it manually) and in fact you don't test your db. you test some other db
  4. use the same db provider as production db. this are best tests but require a bit of thinking on setting upthe infrastructure: you have to start your db for tests (automated docker? manual startup? permanently installed local/remote db?), you have to prepare db before each test (schema, tables, initial data, clearing existing data, resetting sequences etc) and close it after tests.

i recommend the last one using:

  1. before all tests setup that tries to connect to existing db and if there is no db listening then it starts docker with that db
  2. flyway that creates schema and db structure
  3. tests setup that clears all data, reset sequences etc before each test
  4. each test inserts data that will be needed during that test
  5. jvm shutdown hook that stops docker if it was started at the beginning

Upvotes: 1

Related Questions