Insert, Update and Delete

Insert

The insert mechanism is the least surprising of all :

Objects that extend KeyedEntity[K] where K is a numeric type
will have their id field assigned their newly created primary key
value (the mechanism for generating keys is specific to each DatabaseAdaptor).

Update

There are two forms of updates :

1. Full

2. Partial :

The SQL will be :

A partial update must have a where clause, otherwise you’ll get a compilation error.
This is to prevent you from updating all rows in a table by accident.

To update all rows use the setAll function :

Delete

Delete is done either by key (when objects extend KeyedEntity[K]), or
with a boolean clause via the table’s deleteWhere method,
example of Table[PlaylistElement].deleteWhere usage :

Batched updates and Inserts

org.squeryl.Table[A] has insert and update methods that take an Iterable[A]. Invoking them does the update in a single roundtrip to the database via JDBC’s batched update functionality.

The advantage of this is obviously making 1 trip to the DB versus N trips (given an iterable with N elements).

Active Record pattern

Another option available for inserting objects is through the use of the
convenience save method. This method allows the entity to be persisted
without the invocation of the insert method of the respective table :

In the same manner, Updates can also be done with the usage of the
Active Record pattern :

It’s important to note that the contents of the Schema object were imported
before the usage of those convenience methods.

While this makes your code look smaller and less cluttered, it may not work
correctly when dealing with some specific corner cases, like a class that has a
common structure and is mapped to two or more different tables.