Inserting database table rows with Slick 1.0.1
This tutorial shows you how to execute database inserts in a Slick application by building on the previous previous tutorials.
Inserting data with Slick turns out not to be as complex as querying, but there’s still more than one way to do it.
Summary
Perform the following tasks to complete this tutorial, and learn how to insert data.
-
Insert an instance of a mapped case class
-
Get the inserted database table row’s primary key
-
Insert using a tuple instead of a case class
-
Insert multiple rows using JDBC batch insert
Source code: https://github.com/lunatech-labs/play-slick-examples.
Insert an instance of a mapped case class
The most common way to insert data is probably to use mapped case classes, such as the ones in the previous tutorials. Let’s start with the table mapping from Mapping Slick query results to case classes, and paste this into a Play/Slick application’s Scala console (see Using the Scala console with Play and Slick). First, add the case class:
scala> import org.joda.money.Money
import org.joda.money.Money
scala> case class Cocktail(id: Option[Long], name: String, price: Money)
defined class Cocktail
Now add the database table mapping, including the mapRow
and
unMapRow
functions that map between the *
projection and a
Cocktail
case class instance, and the money
function that creates a
Joda Money instance.
// models.database.Cocktails
import scala.slick.driver.MySQLDriver.simple._
import org.joda.money.{CurrencyUnit, Money}
import java.math.RoundingMode
class Cocktails extends Table[Cocktail]("COCKTAIL") {
def id = column[Long]("ID", O.AutoInc)
def name = column[String]("NAME")
def priceCurrency = column[String]("PRICE_CURRENCY")
def priceAmount = column[BigDecimal]("PRICE_AMOUNT", O.DBType("DECIMAL(13,3)"))
def * = id.? ~ name ~ priceCurrency ~ priceAmount <> (mapRow _, unMapRow _)
private def mapRow(id: Option[Long], name: String, currency: String, amount: BigDecimal) = {
Cocktail(id, name, money(currency, amount))
}
private def unMapRow(cocktail: Cocktail) = {
val currency = cocktail.price.getCurrencyUnit.getCode
val amount: BigDecimal = cocktail.price.getAmount
val tuple = (cocktail.id, cocktail.name, currency, amount)
Some(tuple)
}
private def money(currencyCode: String, amount: BigDecimal): Money = {
val currency = CurrencyUnit.of(currencyCode)
Money.of(currency, amount.bigDecimal, RoundingMode.DOWN)
}
}
You can now see that Slick can generate an SQL insert
statement from
the table mapping the same way that it generates select
statements.
scala> (new Cocktails).insertStatement
res: String = INSERT INTO `COCKTAIL` (`ID`,`NAME`,`PRICE_CURRENCY`,`PRICE_AMOUNT`) VALUES (?,?,?,?)
To actually perform the insert, Slick provides an insert
method on the
table object, which maps the case class to the table’s default project,
populates the bind variables and executes the insert statement. You can
add this to your model layer, along with the case class and finder
methods:
// models.Cocktail
package models
import models.database.Cocktails
import play.api.Play.current
import play.api.db.slick.Config.driver.simple._
import play.api.db.slick.DB
import scala.slick.session.Session
import org.joda.money.Money
case class Cocktail(id: Option[Long], name: String, price: Money)
object Cocktail {
val table = new Cocktails
def insert(cocktail: Cocktail): Unit = DB.withSession { implicit session: Session =>
table.insert(cocktail)
}
// Finder methods…
}
Note that table.insert(cocktail)
returns the number of rows inserted,
in this case, but this is not especially useful which is why you don’t
return anything - the function’s return type is Unit
. In any case, an
insert
method like this is almost all you need.
Get the inserted database table row’s primary key
In a web application, you might add cocktails to the database as a result of filling in a form on a web page. After inserting a cocktail, it would be typical to redirect the browser to a web page that shows the new cocktail’s details.
Since our cocktail names are not necessarily unique, the easiest way to
do this is to use the database primary key in the URL,
e.g. /cocktail/42
. However, if you use the code above, you won’t have
this primary key - the Cocktail
object’s id
property.
To make the ID available, add another projection to the table definition:
// models.database.Cocktails
def forInsert = * returning id
Now you can change the model layer method to use this projection and return the ID:
// models.Cocktail
def insert(cocktail: Cocktail): Long = DB.withSession { implicit session: Session =>
table.forInsert.insert(cocktail)
}
If you call this from the web application’s controller, you can now get the cocktail ID and use it to construct the URL for the HTTP redirect
Insert using a tuple instead of a case class
You don’t always need to use a mapped table, in which case you insert tuples instead of case class instances. Suppose you extend our application to record cocktails that are similar to each other, by adding a link table:
// models.database.Similarities
package models.database
import play.api.db.slick.Config.driver.simple._
class Similarities extends Table[(Long, Long)]("SIMILARITY") {
def firstId = column[Long]("FIRST_COCKTAIL_ID")
def secondId = column[Long]("SECOND_COCKTAIL_ID")
def * = firstId ~ secondId
def pk = primaryKey("pk_myTable2", firstId ~ secondId)
}
You could make a case class for a similarity', to insert, but this is
simple enough to handle two cocktail IDs directly. In your model, pass
the column values as arguments to the `insert
function:
// models.Cocktail
def link(firstId: Long, secondId: Long): Unit = DB.withSession { implicit session: Session =>
(new Similarities).insert(firstId, secondId)
}
If there were more columns, then you would pass more arguments to
insert
.
Insert multiple rows using JDBC batch insert
The final variation is to insert multiple rows. You probably don’t need
this as much, because it is less common to insert multiple rows to the
same table in one operation. In our example, perhaps you want to make
the links bi-directional. To do this, pass two tuples to the table’s
insertAll
function - one for each link:
// models.Cocktail
def link(firstId: Long, secondId: Long): Unit = DB.withSession { implicit session: Session =>
(new Similarities).insertAll(firstId -> secondId, secondId -> firstId)
}
Like the insertAll
function accepts a variable number of arguments,
like insert
, but this time each one is a tuple that represents a whole
row instead of a single value. This uses JDBC batch insert, which
probably performs better than separate inserts if you are inserting a
lot of data.
Next steps
Now you can insert data, you can:
-
define join queries
-
define aggregate and group-by queries
-
update and delete data.