The 'slick' library for scala ticks a lot of these boxes.
It allows you to write type-safe, idiomatic scala code to query your database, transform results into proper objects while avoiding the weight of an ORM.
For a large org, Slick also adds to the already significant Scala learning curve for Java devs. Scala itself is good and pretty, but when the first task you give someone new is to write a CRUD-like rest service using slick on one end, and spray on the other, they don't get to spend any time learning actual scala, and instead domain specific languages that are hard to reason about without serious Scala experience. No, this is not speculation: We are going through this problem at work right now, with dozens of developers learning Scala, and only a few of us that really started with significant experience.
If I have to use slick, I use the few direct SQL bits. From my perspective, is the one saving grace of the library.
What library would you use instead of slick?