Slick basic query

Basic queries

Now that we're ready for the development environment, let's look at Slick's basic query methods, we're going to query the Album table in Chinook, and let's look at the definition of album in the code that was automatically generated earlier:

/** Entity class storing rows of table Album
   *  @param albumid Database column AlbumId PrimaryKey
   *  @param title Database column Title
   *  @param artistid Database column ArtistId  */
case class AlbumRow(albumid: Int, title: String, artistid: Int)
    /** GetResult implicit for fetching AlbumRow objects using plain SQL queries */
    implicit def GetResultAlbumRow(implicit e0: GR[Int], e1: GR[String]): GR[AlbumRow] = GR{
    prs => import prs._
    AlbumRow.tupled((<<[Int], <<[String], <<[Int]))
}
/** Table description of table Album. Objects of this class serve as prototypes for rows in queries. */
class Album(tag: Tag) extends Table[AlbumRow](tag, "Album") {
    ...
    /** Database column AlbumId PrimaryKey */
    val albumid: Column[Int] = column[Int]("AlbumId", O.PrimaryKey)
    /** Database column Title  */
    val title: Column[String] = column[String]("Title")
    /** Database column ArtistId  */
    val artistid: Column[Int] = column[Int]("ArtistId")

    /** Foreign key referencing Artist (database name FK_AlbumArtistId) */
    lazy val artistFk = foreignKey("FK_AlbumArtistId", artistid, Artist)
        (r => r.artistid, onUpdate=ForeignKeyAction.NoAction, onDelete=ForeignKeyAction.NoAction)
}
    /** Collection-like TableQuery object for table Album */
    lazy val Album = new TableQuery(tag => new Album(tag))

As you can see for each table in the database, we define a case class that represents a row of tables, such as AlbumRow, a Table class, such as Album, and a lazy variable, Album, which can be used directly to query the data table as a collection class object. Let's look at the basic usage of queries:

We create an Example.scala file in the same directory as com/guidebee/slick/example

package com.guidebee.slick.example

import scala.slick.driver.MySQLDriver.simple._
import com.guidebee.slick.example.Tables._
// The main application
object Example extends App {

    Database.forURL("jdbc:mysql://127.0.0.1/Chinook",
        driver = "com.mysql.jdbc.Driver",
        user="user",
        password="password").withSession {
            implicit session =>
            // <- write queries here
            Album foreach { case AlbumRow(albumId,title,artistId) =>
            println(" " + albumId + ":" + title + ":" + artistId)
            }
        }

}

Note: Modify the correct username and password.

Where the code is

    Database.forURL("jdbc:mysql://127.0.0.1/Chinook",
        driver = "com.mysql.jdbc.Driver",
        user="user",
        password="password").withSession {
          implicit session =>
          // <- write queries here

           }
        }

Used to connect to the database and create a Session object where all database-related queries can be implemented, here we print out all the records in album:

Album is a collection object, so we can use the methods supported by Scala collection objects to filter and compare, for example:

val q1= for (a <- Album;if a.albumid<10)
     yield (a.albumid,a.title,a.artistid)
q1 foreach println

Show the first 9 records: (1, For Those Of To Rock We Salute You, 1) (2, Balls to the Wall, 2) (3, Restless and Wild, 2) (4, Let There Be Rock, 1) (5, Big Ones, 3) (6, Jagged Little Pill, 4) (7, Facelift, 5) (8, Warner 25). Anos,6)(9,Plays Metallica By Four Cellos,7)

val q1= for (a <- Album;if a.albumid<10)
    yield a.albumid.asColumnOf[String] ++ LiteralColumn(":") ++ a.title
q1 foreach println

1:For Those About To Rock We Salute You2:Balls to the Wall3:Restless and Wild4:Let There Be Rock5:Big Ones6:Jagged Little Pill7:Facelift8:Warner 25 Anos9:Plays Metallica By Four Cellos

Let's take a look at the case of multiple tables Join: let's look at using SQL statements directly

select album.AlbumId,album.Title,artist.Name from album
INNER JOIN artist 
ON album.ArtistId=artist.ArtistId
WHERE album.AlbumId<10

So how do you implement it with the Scala statement, which is the case with multiple collection object Joins:

val q3 = for {
    a <- Album if a.albumid < 10
    t <- Artist if a.artistid===t.artistid 
}  yield (a.albumid,a.title,t.name)
q3 foreach println

Note that the comparison operator is , and we can also query directly with a foreign key, which is defined in Tables.scala, class Album

val q2 = for {
    a <- Album if a.albumid < 10
    t <- a.artistFk
} yield (a.albumid,a.title,t.name)
q2 foreach println

Both methods output the following results:

(1,For Those About To Rock We Salute You,Some(AC/DC))
(2,Balls to the Wall,Some(Accept))
(3,Restless and Wild,Some(Accept))
(4,Let There Be Rock,Some(AC/DC))
(5,Big Ones,Some(Aerosmith))
(6,Jagged Little Pill,Some(Alanis Morissette))
(7,Facelift,Some(Alice In Chains))
(8,Warner 25 Anos,Some(Antônio Carlos Jobim))
(9,Plays Metallica By Four Cellos,Some(Apocalyptica))