Here’s a github repo which will track this project

–

Last time we got our Postgres DB ready to serve up data. Now let’s go ahead and connect to it.

Since the goal of this series is to learn the fundamentals, I decided not to go with a 3rd party ORM tool that provides sugary syntax for connecting to a persistence layer. I am more interested in getting my hands dirty and learning how things really work. So, I decided to import only the basic Java SQL and Postgres tools and roll the rest myself.

The result is far from perfect…I basically hacked my way to an MVP solution. But I had a lot of fun and learned a lot.

One big takeaway is that working with the ResultSet class that gets returned from a SQL query can be very confusing. The API was not clear to me and I had a hell of a time mappings its highly imperative and mutable nature to the more functional nature I strive for in Scala code.

Here’s the MVP implementation

The idea of this RepositoryService is that a service can call it and pass in just the name of the resource (aka the sql table) and the RepositoryService will figure out the column names and pass back a list of maps that represent the table.

Basically, turn this request:

greetingService.getOne(id)

into this SQL:

id | language |     content      |        create_date         
----+----------+------------------+----------------------------
 3 | French   | Bonjour le monde | 2017-04-08 15:27:30.168371

into this Scala Map:

Map(id -> 3, language -> French, content -> Bonjour le monde, create_date -> 2017-04-09 02:32:57.760932)

then into a Scala case class for type safety

Greeting(3,French,Bonjour le monde,2017-04-09 02:32:57.760932)

and then into json for outward transmission:

{
  "id": 3,
  "language": "French",
  "content": "Bonjour le monde",
  "create_date": "2017-04-09 02:32:57.760932"
}

There’s a lot of stuff in the PR, from refactoring the Servlet to be named after the Greeting resource it represents to creating a Service layer and the above-mentioned Repository layer. Further improvement ideas:

  • database interactions need to return Option[Type] for null handling
  • add the rest of the CRUD operations
  • find a more Scala way of mapping the sql ResultSet into a Scala collection (look into the Stream class)
  • add Postgres dependency as a injected variable
  • clean up the way PG connections are established (execute in the ScalatraBootstrap class and inject into the RepositoryService class)

–

work covered in this post