Extracting Results

Defining and executing queries is one part of the story. The other is mapping query results to Scala types.

Once we can use the as method on a ScalaPreparedStatement to define what's the output type for that statement. By default, this type is Row.

The as method requires an implicit RowMapper available, but we only need to define one for complex types, such as case classes, and these can be derived.

There are three scenarios we have to consider:

• Simple types: When we want to query only one column from the table.
• Tuples: When we want to query multiple columns, but we don't want to create a specialized record for this (ie. case class). Order is important here.
• Case Class: When we want to query multiple columns and each row can be mapped to a case class. Order is not important here.

Only for the last case we have to define an implicit RowMapper, in all other cases Helenus will provide an implicit instance on its own.

Simple Types

For simple types, we can use the as method like this:

val countryHotel =
    "SELECT country FROM hotels WHERE name = ?".toCQL
        .prepare[String]
        .as[String]
// countryHotel: ScalaPreparedStatement1[String, String] = net.nmoncho.helenus.internal.cql.ScalaPreparedStatement1@19d92549

countryHotel.execute("Rotterdam Marriott").nextOption()
// res3: Option[String] = Some(value = "NL")

Tuples

For tuples, we can use the as method like this:

val countryAndCapacityHotel =
    "SELECT country, capacity FROM hotels WHERE name = ?".toCQL
        .prepare[String]
        .as[(String, Int)]
// countryAndCapacityHotel: ScalaPreparedStatement1[String, Tuple2[String, Int]] = net.nmoncho.helenus.internal.cql.ScalaPreparedStatement1@4601b77a

countryAndCapacityHotel.execute("Rotterdam Marriott").nextOption()
// res4: Option[Tuple2[String, Int]] = Some(value = ("NL", 1000))

Notice that the order of the element types tuple in the tuple must match the order of the select column types.

Case Classes

For case classes, we need to make an implicit RowMapper available, and then use the as method like we have seen before:

case class Hotel(name: String, country: String, capacity: Int, occupancy: Int) derives RowMapper

val hotelsByName = "SELECT * FROM hotels WHERE name = ?".toCQL.prepare[String].as[Hotel]
// hotelsByName: ScalaPreparedStatement1[String, Hotel] = net.nmoncho.helenus.internal.cql.ScalaPreparedStatement1@1c1c6173

hotelsByName
    .execute("Rotterdam Marriott")
    .nextOption()
// res5: Option[Hotel] = Some(
//   value = Hotel(
//     name = "Rotterdam Marriott",
//     country = "NL",
//     capacity = 1000,
//     occupancy = 800
//   )
// )

Notice we use derives RowMapper to derive an instance without too much hassle.

Unlike with Tuples, element type order is not important. With case classes the fields are mapped by name.