database

A database query for implementing paging in a Play Framework application

As a quick note to self, one way to implement paging in a Play Framework application is to use the limit and offset arguments in a MySQL query. This way you can show X number of objects on one web page, then give the user Next and Previous links so they can scroll backwards and forwards through the dataset.

To implement paging with a MySQL query, use the limit and offset query parameters, as shown in this example:

This website is a little one-man operation. If you found this information helpful, I’d appreciate it if you would share it.

Play Framework: An Anorm ‘singleOpt’ query example

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:

This website is a little one-man operation. If you found this information helpful, I’d appreciate it if you would share it.

SQL select, group by, order by, and count (all in one)

Don’t tell anyone, but my SQL skills are pretty average these days, at best, mostly because I haven’t had to do anything hard in a while. But just now I was happy to write this little SQL SELECT query that does a GROUP BY, an ORDER BY, and a COUNT, yielding the results shown in the image:

select nid, count(nid) from term_node
where tid in (3,1,11,10,9,8,7)
group by nid
order by count(nid) DESC

I’m going to use this query — or one very similar to it — to get a list of nodes (nid) that have the most tag ids (tid) from the list of tid in the query. In theory, the nodes (blog posts) that have the most tags in common should be the most related to each other. So, in my Scrupal6 replacement for Drupal 6, this query is a way to get “related” content for a given blog post. (The tid list shown comes from node id 4, so I need to also exclude nid=4 from the results. I also need to add a limit clause to the query.)

If you ever need to do a group by, order by, and count in one SQL query, I hope this example is helpful.

Scala: A ScalikeJdbc example (including SQL select and update statements)

If you want to see a relatively simple ScalikeJdbc example that performs SQL SELECT and UPDATE commands, I hope this example is helpful.

The background for this example goes like this:

This website is a little one-man operation. If you found this information helpful, I’d appreciate it if you would share it.

Scala exception handling (try/catch/finally and more)

Scala FAQ: How do I handle exceptions in Scala?

Solution: Basic exception handling in Scala is handled with its try/catch/finally syntax. (See the end of this article for links to tutorials on advanced exception handling in Scala.)

Here’s an example of Scala’s try/catch/finally syntax:

This website is a little one-man operation. If you found this information helpful, I’d appreciate it if you would share it.

Facebook switched from InnoDB to MyRocks and cut their storage usage in half alvin September 26, 2017 - 8:28pm

Facebook engineers shared this post about how they switched from InnoDB to MyRocks and successfully cut their storage usage in half.

Android Room, database I/O, and Java 8 threads

I just started working with the Android Room database persistence library, and since you’re not supposed to run things like database queries on the main thread (the UI thread), I was looking at other ways to run them.

In general, you probably won’t want to run database queries using a Thread, but just to see how Room works, I wrote this Java Thread code, and confirmed that it works as expected:

This website is a little one-man operation. If you found this information helpful, I’d appreciate it if you would share it.