Recommendations for working with SQL?

1

Hi,

It’s been a while, but I’m happy to be using some Kotlin again. First thing I’m trying to do is query some relational DBs. Any recommendations on ways to do this, beyond strait JDBC? In Python, I can do something like:

  for id, name, dt, foo in query("select id, name, dt, foo from table t join … "):

But in JDBC that becomes much for verbose with many lines like:

  val id = rs.getInt(1)
  val name = rs.getString(2)
  val foo = rs.getTimestamp(3)

… or similar for setting input query parameters. I like the types, but I’d like to cut down the boilerplate. I’m thinking I could declare a data class for the rows and maybe write a method like query that uses reflection to call all the ResultSet.getFoo(n) methods for me.  Maybe something like that exists already?

Rob

2

Hi Robert,

If you don’t mind bleeding edge then my kwery library might be of interest: https://github.com/andrewoma/kwery

Cheers
Andrew

3

Thanks Andrew, I'll check that out.

~Rob

4

You could this one too

https://github.com/MarioAriasC/KotlinPrimavera/wiki/JDBC

Is primarily for Spring, but the extension functions on JDBC classes will work without Spring

As an example, you could change this:

JdbcPet pet = new JdbcPet();
pet.setId(rs.getInt("id"));
pet.setName(rs.getString("name"));
Date birthDate = rs.getDate("birth_date");
pet.setBirthDate(new DateTime(birthDate));
pet.setTypeId(rs.getInt("type_id"));
pet.setOwnerId(rs.getInt("owner_id"));
return pet;

To this 

return rs.extract {
  val pet = JdbcPet()
  pet.id = int["id"]
  pet.name = string["name"]
  pet.birthDate = DateTime(date["birth_date"])
  pet.typeId = int["type_id"]
  pet.ownerId = int["owner_id"]
  pet
}