db.tk

package core

import org.sqlite.SQLiteConfig
import java.sql.Connection
import java.sql.DriverManager
import java.sql.SQLException
import java.sql.Statement


class DB {

    private val tableName = "tableTeste"
    private val primaryKey = "id"

    private val JDBC_URL = "jdbc:sqlite:C:\\testes\\db_teste.db"
    private lateinit var con: Connection ///DriverManager.getConnection("jdbc:sqlite:C:\\testes\\db_teste.db")
    private lateinit var stmt: Statement;

    val fields = mutableMapOf<String, Any>()

    fun addField(fieldName: String, fieldValue: String){
        fields.put(fieldName, fieldValue)
    }
    fun addField(fieldName: String, fieldValue: Int){
        fields.put(fieldName, fieldValue)
    }
    fun getField(fieldName: String): Any? {
        return fields.get(fieldName)
    }


    constructor(){
        openConnection()
    }

    protected fun finalize() {
        closeConnection()
    }



    private fun openConnection() {
        //if (con==null) {
            val config = SQLiteConfig()
            config.enforceForeignKeys(true)
            con = DriverManager.getConnection(JDBC_URL, config.toProperties())

            stmt = con.createStatement()
        ///}
    }

    fun closeConnection() {
        con.close()
    }


    private fun runQuery(sqlString: String){

        try {
            stmt.executeUpdate(sqlString)
        } catch (e: SQLException) {
            println("Erro ao executar a query : " + e.message)
        }

    }


    fun create() {
        runQuery("CREATE TABLE IF NOT EXISTS " + tableName + " (id INTEGER, title TEXT, PRIMARY KEY(id))")
    }

    fun has(id: Int): Boolean {
        return hasRecord("id", id);
    }
    fun has(title: String): Boolean {
        return hasRecord("title", title);
    }

    private fun hasRecord(fieldName: String, value: Any): Boolean{
        //println(fieldName + " - " + value)
        val qry = stmt.executeQuery("SELECT * FROM " + tableName + " WHERE $fieldName = '$value'")
        val exists = qry?.next()
        qry?.close()

        return exists ?: false
    }


    fun insert(){

        var fString = arrayListOf<String>()
        var vString = arrayListOf<Any>()

        fields.forEach{
            fString.add(it.key)
            vString.add(it.value)
        }

        runQuery("INSERT INTO " + tableName + "(" + fString.joinToString(",") + ") VALUES('" + vString.joinToString("','") + "')")

     }

    fun update(){

        if (getField(primaryKey)==null){
            println("A chave primaria não foi informada")
            return;
        }

        var fString = arrayListOf<String>()
        fields.forEach{
            if (it.key!=primaryKey) {
                fString.add(it.key + "='" + it.value + "'")
            }
        }

        runQuery("UPDATE " + tableName + " SET " + fString.joinToString(", ") + " WHERE " + primaryKey + "=" + getField(primaryKey))

    }

    fun delete(){
        if (getField(primaryKey)==null){
            println("A chave primaria não foi informada")
            return;
        }
        runQuery("DELETE FROM " + tableName + " WHERE " + primaryKey + "=" + getField(primaryKey))
    }

}