select

A ScalikeJdbc SQL SELECT example that maps results to a list of case class instances alvin May 15, 2018 - 6:40pm

Here’s another ScalikeJdbc example. The main benefits of this example are:

  • It shows how to perform a SQL SELECT with ScalikeJdbc
  • How to map the ResultSet values to create a series of objects from the ResultSet
  • How to connect to a database with ScalikeJdbc using a JDBC style of connecting (as opposed to putting the JDBC parameters in a configuration file)

Here’s the ScalikeJdbc example code:

A ScalikeJdbc SQL UPDATE syntax example alvin May 15, 2018 - 6:36pm

Here’s an example of how to write a SQL UPDATE query with ScalikeJdbc:

def updateUrlClickGeoInfo(urlClickId: Long, gi: GeoInfo): Int = NamedDB('kbhr) localTx { implicit session =>
    sql"""
    update url_clicks
    set city = ${gi.city}, state = ${gi.state}, country = ${gi.country}
    where id = ${urlClickId}
    """.update.apply()
}

(As a note to self, this code comes from my PopulateKbhrGeo1 app.)

A ScalikeJdbc SQL SELECT example with WrappedResultSet alvin May 15, 2018 - 6:29pm

Here’s another Scala ScalikeJdbc SQL SELECT example. A few unique things about this example is that it shows the JDBC-style parameters; it shows how to use a case class and the “service” style approach; and it shows how to use the WrappedResultSet as shown. Here’s the code:

A ScalikeJdbc SQL SELECT example with JDBC-style parameters alvin May 15, 2018 - 6:25pm

Here’s another ScalikeJdbc SQL SELECT example. If I remember right, this one doesn’t rely on a configuration file, it just uses the JDBC parameters shown:

A ScalikeJdbc SQL SELECT “service” example alvin May 15, 2018 - 6:17pm

Here’s another ScalikeJdbc SQL SELECT query example. In this example I use the concept of a “service,” which I probably originally got from the ScalikeJdbc website:

A simple ScalikeJdbc SQL SELECT example alvin May 15, 2018 - 6:06pm

I don’t have time to write about ScalikeJdbc right now, so I’ll just say that it’s been working well for me. Here’s one of the first test examples I created, a simple SQL SELECT query that prints some output from a database table:

A SQL select group by, order by, and count query alvin May 13, 2018 - 12:39pm

As a note to self, this is how I wrote a “group by” and “order by” SQL query that gives me the number of times each country code occurs in a database table named url_clicks:

select country, count(1) as the_count
from url_clicks
where url_id=6
and country != ''
group by country
order by the_count desc

Results of this query look like this:

US, 50
CA, 30
GB, 15

That tells me that 50 people in the US clicked on the link with the id=6, 30 people from CA clicked on the same link, and 15 people from GB clicked on that link as well.

How to get the count of detail rows in a master/detail SQL SELECT query and subquery alvin April 28, 2018 - 4:03pm

As a brief note to self, I just used this SQL SELECT query to show both (a) master/summary information about each URL row in the urls database table, and also (b) detail information in the form of the number of rows in the url_clicks database table for each row in the urls table:

Play Framework: Anorm SQL query syntax and examples alvin April 18, 2018 - 2:35pm
Table of Contents1 - Anorm2 - More background ...3 - A note about my coding style4 - SQL SELECT queries5 - A brief intermission6 - SQL INSERT with primary key (auto increment)7 - SQL INSERT with no primary key8 - SQL UPDATE9 - SQL DELETE10 - Complete example methods11 - A complete class12 - More information

In general the online Play Framework documentation is excellent, but one area where I needed more help was in seeing more examples of the Anorm syntax. To that end, here are some Anorm query examples, taken from a Play Framework application I worked on recently. But first, a little background about Anorm.

Creating a drop-down list in Play Framework 2.6 alvin April 11, 2018 - 10:37am

As a brief note to self before I delete this code, this is how you create a drop-down list in Play Framework 2.6:

@* adding 'size to helper.select creates a select/option area (single or multi-select) *@
@helper.select(
    form("category"),
    categories,
    'id -> "category",
    '_help -> "Select one, any one"
)

In that code, categories is passed into the Play template like this: