Parsers

Parsers – Retrieving Data

Defining a Parser

Parsers are created by passing an anonymous function that takes a row object and returns some value to the RowParser's apply method. Within that function, values can be extracted from the row object. Here's an example:

import com.lucidchart.open.relate.RowParser

case class Pokemon(
  name: String,
  level: Short,
  trainerId: Option[Long]
)

val pokemonParser = RowParser { row =>
  Pokemon(
    row.string("name"),
    row.short("level"),
    row.longOption("trainer_id")
  )
}

In this example, the created parser takes the value from the name column of the row as a string, the value of level as a short, and the value from the trainer_id column as a long option to instantiate a Pokemon object. The row object has numerous methods to extract data from the row with the desired data type.

Some of the methods that can be called on the row object are prepended with the word "strict." These methods are faster than their non-strict counterparts, but do not do type checking, and do not handle null values in the database.

The function passed to RowParser can return any value, so it doesn't necessarily need to be an instance of a case class. A Tuple, Seq, etc. would work equally well.

Using a Parser

Applying a parser to a query only requires specifying the desired collection type to return. The following is an example using the parser created in the previous section:

SQL("""
  SELECT *
  FROM professor_oaks_pokemon
  WHERE pokemon_type={type}
""").on { implicit query =>
  string("type", "starter")
}.asList(pokemonParser)(connection)

This example would return a List of Pokemon. The parser can also be passed to the asSingle, asSingleOption, asSet, asSeq, asIterable, and asList methods to produce the respective collections.

Parsers that return a Tuple of size 2 can also be passed to the asMap method to get a Map. Here's an example of its use (using the case class from the previous example):

val nameToPokemonParser  = RowParser { row =>
  val pokemon = Pokemon(
    row.string("name"),
    row.short("level"),
    row.longOption("trainer_id")
  )
  (pokemon.name, pokemon)
}

SQL("""
  SELECT *
  FROM professor_oaks_pokemon
  WHERE pokemon_type={type}
""").on { implicit query =>
  string("type", "starter")
}.asMap(nameToPokemonParser)(connection)

Single Column Parsers

Sometimes a query retrieves only one column. Convenience methods are defined in RowParser for creating single column row parsers in these occasions. Below is an example of their use:

SQL("""
  SELECT id
  FROM trainers
  WHERE name="Red"
""").asList(RowParser.long("id"))(connection)

The RowParser object also contains definitions for bigInt, date, int, and string.

Single Value Parsers

In other cases, only one value is desired as the result of a query. For these scenarios, Relate provides a scalar method with which the desired type of the returned single value can be defined. The return value is wrapped as an Option. An example of its use is as follows:

SQL("""
  SELECT hp
  FROM pokemon
  WHERE name="Squirtle"
""").asScalarOption[Int](connection)

There is also a non-option version of this method, asScalar[A].

Retrieving Auto Increment Values on Insert

There also exist methods to retrieve the auto-incremented ids of inserted records. Given a table where the primary key was a bigint, here's an example:

val id = SQL("""
  INSERT INTO badges(name)
  VALUES ("Boulder Badge")
""").executeInsertLong()

Ids can also be retrieved as integers with executeInsertInt. There also exist plural versions of these two methods: executeInsertInts and executeInsertLongs. Finally, in the case that the id is not an integer or bigint, use executeInsertSingle or executeInsertCollection, both of which take RowParsers capable of parsing your ids as their parameters.