sql

A ScalikeJdbc SQL SELECT example that maps results to a list of case class instances

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

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

Here’s another 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; it shows how to use the WrappedResultSet as shown. Here’s the code:

A simple ScalikeJdbc SQL SELECT example

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

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

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.

Play Framework: An Anorm ‘singleOpt’ query example alvin April 13, 2018 - 6:35pm

It’s embarrassing how long it took to get this to work, but if you ever need to select a single row from a database using Play Framework (version 2.6), Anorm, and singleOpt, I hope this example is helpful, and will save you time and frustration: