Tip:
Highlight text to annotate it
X
Today I will tell you about a lightweight ORM library called SQueryl
Since I've improperly imported PDF files, you can see funny signs in the corner of the slides.
Probably, almost everyone is familiar with problems caused by using ORM. It is believed that ORM is anti-pattern because of certain issues:
one of them is productivity; second - you mostly don’t understand when and how it requests database.
One of the problems occurs when we start writing requests, for complex requests we have to use built-in query language.
Here you can see one of ORM’s default requests where you can calculate where runtime exception can appear.
I’ve counted six places where it can occur, we can write bad request or enter incorrect parameter number, due to the fact that JBC starts with one (1) - everyone is always confused, wrong "cast", etc.
At some point, I think in 2009, JPA 2 appeared and it gave us opportunity to make safely typed queries, but you have to sacrifice readability because of them.
Can anyone tell me what this request do?
SQueryl library was written in order to solve these problems. It is a lightweight ORM (has minimum features on start-up) which provides language for querying similar to SQueryl, in this case all requests are completely secured, checked with Compile Time and syntax become brief.
Let’s get back to our request. This is JPA 2 version, and this is SQueryl version. Basically everything is clear in here. As you can see, it’s default Scala code and those who prefer SQL syntax can use it with default words: From, Select, Where, etc.
Now we will get through the basics of SQueryl. There is nothing complicated.
Initialization
Initialization - is similar to JBC initialization: we load driver, declare factory sessions (session factory - this is a simple function that returns the session) Here's a rather naive implementation, which will create a Connection on every request.
It’s better to use ConnectionPool (DBCP) in significant project or use BoneCP, as we do in one of our projects.
Transactions
Everything is simple here; all you need to do within a transaction is executed in block “Transaction”.
Or it’s simply a Transaction - transaction is started every time we enter this block and being committed when we leave this block or inTransaction, which makes it only if we are not in another transaction.
It means that you can put a couple of blocks to each other while inTransaction will not open another transaction.
Description of the scheme
Generally, there are two modes in SQueryl to operate with types. PrimitiveTypeMode - where fields are Scalas’ primitive types - Int, String, Long, etc., and Custom Type Mode, which I’ll explain later.
I use a Primitive Type Mode and I'll tell you about it. Here we can see that it’s our default Scalas’ class, it has a field with default values, types, using annotations you can specify some additional parameters - length, column name in the database, etc.
Either you can use Keys classes, for example I prefer using Keys class with unchangeable fields, so I always know what's going on and in what state my object is. You need to use Copy method - default class for Keys to change any field in order to get a new object.
Here we expand KeyedEntity – it’s not necessary to do it, it just tells us that object has a Primary Key with ID name, which allows you to use shorter forms of syntax.
Custom Type Mode is used instead of default classes of the fields where we can add validation, meta data, etc.
Scheme
Object is usually made in Singleton, extends scheme class. We announce tables here - saying that here we have «Users» table – this is User type table, announce additional options for the fields, such as Auto-Increment, uniqueness, etc.
Here we describe connection between tables – you will see this a bit later. Scheme has several useful methods, including Create, Drop and PrintDel, which are used to display types that are used to generate.
As we can see, the whole scheme is made in Scala, checks Compiles and all changes in the scheme, so that scheme is Up-to-Date with SQL project. Of course, if you start developing project from a scratch.
Let’s switch to default operations. This is Insert. Here we simply create an object, insert an object and then we get it back, or paste a list of objects. It's simple.
Select
As we have already seen, select supports several types of syntax. Standard SQL Like syntax - Select, Where and Order By can be used. We use basic names of fields and use SQueryl operators.
Here we use tilde in order for Scala not to be confused with Scalas’ operator “> =”, with SQueryl operator. Actually, tilde structure can be changed to DT – they are replaceable.
When we expand Key Identity, we get «Users» table, a useful method Look Up, which returns an Option with ID.
Update
Update has two forms: full and partial. Full Update - we take an object insert it into Update method, which is being found and replaced ID. Since all fields are unchangeable, we have to use Copy in order to change value of a field, which is default for all Keys classes.
Partial Update is also SQL syntax, standardly we do Update and choose what fields needs to be changed; here we use assignment operator ": =" and default condition Where.
Number of updated records returns as well as in JBC. And again, "+" can be used instead of "~ +".
Delete
Everything is clear with removal, you can delete either using ID or condition. Number of deleted records returns.
Until now everything was simple, let’s move on to more complicated things, though they would not be too difficult!
One of the most interesting features in SQueryl is composite queries (Composite Select), which reduce code duplication to minimum. It means, if you have a query that is used in many different forms, you can fill it once and use it later.
Here we insert request using Select and then we use it instead of a table to make it another Select.
Since Select in SQL return object in Query, this object can either be used as a collection, when we start fetching (sampling from the database take place), or we can use it to produce other Query, in case of not fetching.
Select inside Select can be used as well as in SQL. Using condition Where.
Query object can be used to select unique values, pagination and selection using keyword Far Update in SQL, to update selected records.
Aggregation
Aggregation is the same as in SQL, but word «Compute» is used instead of the word «Select» in order to improve security, which means: here we use Select and here we use Compute - they are mutually exclusive.
Object returns that can be iterated ("measures") - it can led us to Maped, where Key will be a group and Compute will be value.
Join
As well as in SQL Join can be just a selection of more than one table with indication of condition Where;
In first case we can make Join of different types, in this case Left Outer Join, in this case second table «Avatars» inside this function will be an Option, so in this case we use Map.
Since SQueryl is trying to be lightweight as ORM, there is no advanced features of subsidiary collections, but still there are some features.
We can declare the One-to-Many or Many-to-Many relation between tables with the help of following syntax.
Relation is divided into two types: Stateless - it's roughly the same as Scala Immutable collection.
In fact it’s the same Query, it’s selected from the database every time you apply to them. In fact this is Shopper syntax for Predefine Query. They can be used as a collection, being iterated, etc.
Stateful Relation is same as Mutable collection - they keep their status in memory and you can iterate with their help. Also they have more operators and methods to change them.
SQueryl has drawbacks as well as any other library. I'm using it for a month and have confronted a few drawbacks.
First, what you can see inside is a magic, without compiling and without plug-ins, therefore everything is complicated: fields of objects in circuits are not led to field of object, but to meta data, column, database, etc.
Here is the method that describes equality between two values, I had to use it when I tried to tear a code and compile it in one method. There is no support for Union class at least so far.
Sometimes DSL manages to trick itself. For example, we have an interesting SQueryl syntax for dynamic Query, when some kind of terms may be in the form of Option; using question mark we can say that if Option is filled in, we will use it and if it’s not filled in we’ll throw it out.
It is very comfortable, when you have many “And” comparisons, when they are used or not used. As for String - it works, but it doesn’t work for Boolean because they interact with Shopper syntax for simplified Boolean conditions, which can be done without using '='.
I tried to avoid this to be understood by compiler, I had to convert this expression to a form, where the first line - double Not, so that the compiler understood that it is Boolean.
In general, there is no other drawbacks, but if we assume that this is lightweight ORM, anyways you need to write something yourself, but syntax turns to be very brief.