val records = jdbcRoutine.queryForSeq(
  "select * from users where id = ?",
  new RowHandler[User] {
    override def handle(resultSet: WrappedResultSet): User = {
      User(
        id = resultSet.getScalaLong("id"),
        optionalName = resultSet.getStringOpt("name") // Option[] instead of null
      )
    }
  },
  Some(1) //parameters of Option[] are handled
)

"com.github.chenjianjx.sjr" %% "scala-jdbc-routine" % "0.9.1"

"com.github.chenjianjx.sjr" % "scala-jdbc-routine" % "0.9.1" cross CrossVersion.for3Use2_13

implicit val connection: Connection = howeverYouGetYourConnection()
val jdbcRoutine = new JdbcRoutine

jdbcRoutine.execute("CREATE TABLE users (id LONG PRIMARY KEY, name VARCHAR(255))")

jdbcRoutine.update("INSERT INTO users (id, name) VALUES (?, ?)", 1, "Alice")
jdbcRoutine.update("INSERT INTO users (id, name) VALUES (?, ?)", 2, None)

val users = jdbcRoutine.queryForSeq("select * from users", new RowHandler[User] {
  override def handle(resultSet: WrappedResultSet): User = {
    User(id = resultSet.getScalaLong("id"),
      optioalName = resultSet.getStringOpt("name"))
  }
})

val aliceOpt = jdbcRoutine.queryForSingle("select * from users", new RowHandler[User] {
  override def handle(resultSet: WrappedResultSet): User = {
    User(id = resultSet.getScalaLong("id"),
      optioalName = resultSet.getStringOpt("name"))
  }
})

jdbcRoutine.batchUpdate("INSERT INTO users (id, name) VALUES (?, ?)", 
  Seq(3, "Chris"), 
  Seq(4, "Dave")
)

private class MySqlGeneratedKeysHandler extends GeneratedKeysHandler[Long] {
  override def handle(resultSet: WrappedResultSet): Option[Long] = if (resultSet.next()) {
    Some(resultSet.getScalaLong(1))
  } else {
    None
  }
}

val key = jdbcRoutine.updateAndGetGeneratedKeys[Long](
  "insert into some_table(int_value) values(?)",
  generatedKeysHandler,
  123
)

private class OracleGeneratedKeysHandler extends GeneratedKeysHandler[Long] {
  override def handle(resultSet: WrappedResultSet): Option[Long] =
    if (resultSet.next()) {
      Some(resultSet.getScalaLong(1))
    } else {
      None
    }
}

val key = jdbcRoutine.updateAndGetGeneratedKeysFromReturnedColumns[Long](
  "insert into some_table(id, int_value) values(auto_key_seq.NEXTVAL, ?)",
  Array("id"),
  new OracleGeneratedKeysHandler, 123
)

jdbcRoutine.callToUpdate("CALL insert_sp (?, ?, ?, ?, ?, ?)",
  InParam(1.0F),
  InParam(Some(2.0F)),
  InOutParam(Types.DECIMAL, 100.0F),
  InOutParam(Types.DECIMAL, Some(200.0F)),
  OutParam(Types.FLOAT),
  OutParam(Types.FLOAT)
)

val records = jdbcRoutine.callForSeq("CALL query_sp ()", someRowHandler)

class PgSetBytesAsBlobParam(bytes: Array[Byte]) extends PreparedStatementSetterParam {
  override def doSet(stmt: PreparedStatement, index: Int): Unit = {
    stmt.setBlob(index, new ByteArrayInputStream(bytes), bytes.length.toLong)
  }
}

jdbcRoutine.update(insertSql, someId, new PgSetBytesAsBlobParam(someBytes))

implicit val connection: Connection = howeverYouGetYourConnection()
transaction {
  jdbcRoutine.update(insertSql, foo1, bar1)
  jdbcRoutine.update(insertSql, foo2, bar2)
}

val vanillaResultSet = stmt.executeQuery()
val resultSet = new WrappedResultSet(vanillaResultSet)
while (resultSet.next()) {
  println(resultSet.getScalaLong("id"))
  println(resultSet.getStringOpt("name"))
}