I am currently programming an application, that connnects to a mysql database and reads, inserts and changes data in it. I am coding in Kotlin and use the JDBC Driver. My problem is, that for every read or transaction I want to do, I need another method. For example : SelectEverythingFromTable1(), SelectEverythingFromTable2(), SelectEverythingFromTable3(), InsertIntoTable1(), InsertIntoTable2(), InsertIntoTable3()... I am currently doing it this way because of course every Table has different columns and I am loading the data into different classes.
Here For example two of my current methods, which are basically the same, but load from another table:
fun loadAllPatients () : List<Patient> {
var stmt: Statement? = null
var resultset: ResultSet? = null
val patients = mutableListOf<Patient>()
try {
stmt = conn!!.createStatement()
resultset = stmt!!.executeQuery("SELECT * FROM patient;")
if (stmt.execute("SELECT * FROM patient;")) {
resultset = stmt.resultSet
}
while (resultset!!.next()) {
patients.add(Patient(resultset.getInt("patientID"),resultset.getString("first_name"),resultset.getString("last_name"),resultset.getDate("birthday")))
}
} 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) {
println("error")
}
stmt = null
}
if (conn != null) {
try {
conn!!.close()
} catch (sqlEx: SQLException) {
println("error")
}
conn = null
}
}
return patients
}
fun loadAllEntries () : List<Entry> {
var stmt: Statement? = null
var resultset: ResultSet? = null
val entries = mutableListOf<Entry>()
try {
stmt = conn!!.createStatement()
resultset = stmt!!.executeQuery("SELECT * FROM entry;")
if (stmt.execute("SELECT * FROM entry;")) {
resultset = stmt.resultSet
}
while (resultset!!.next()) {
entries.add(
Entry(resultset.getInt("entryID"),resultset.getInt("patientID"),resultset.getInt("therapyID"),resultset.getInt("status"),resultset.getString("comment"),
resultset.getInt("creator"),resultset.getInt("employee"),resultset.getDate("creation_date"),resultset.getDate("start_date"),resultset.getDate("end_date")))
}
} 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) {
println("error")
}
stmt = null
}
if (conn != null) {
try {
conn!!.close()
} catch (sqlEx: SQLException) {
println("error")
}
conn = null
}
}
return entries
}
Is there any way I can do this more efficient, or am I doing this the completely wrong way? I am very new to working with databases, but I am not finding much information about this topic in the internet, thanks for your help.
