Slick 2.x examples with MariaDB

CaseClassMapping.scala

package slick.lifted

import scala.slick.driver.MySQLDriver.simple._
import slick._

object CaseClassMapping extends App {

  // the base query for the Users table
  val users = TableQuery[Users]

  val db = forURL()
  db.withSession { implicit session =>
    
     val conn=db.createConnection()
    
    val stmt = conn.createStatement();
    stmt.executeUpdate("DROP TABLE IF Exists USERS")    
    stmt.close();
    conn.close();
    
    // create the schema
    users.ddl.create
    
    // insert two User instances
    users += User("John Doe")
    users += User("Fred Smith")
    
    // print the users (select * from USERS)
    println(users.list)
  }
  
}

case class User(name: String, id: Option[Int] = None)

class Users(tag: Tag) extends Table[User](tag, "USERS") {
  // Auto Increment the id primary key column
  def id = column[Int]("ID", O.PrimaryKey, O.AutoInc)
  // The name can't be null
  def name = column[String]("NAME", O.NotNull)
  // the * projection (e.g. select * ...) auto-transforms the tupled
  // column values to / from a User
  def * = (name, id.?) <> (User.tupled, User.unapply)
}

HelloSlick.scala

package slick.lifted

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.jdbc.meta.MTable
import slick._

// The main application
object HelloSlick extends App {

  def createIfNotExists(tables: TableQuery[_ <: Table[_]]*)(implicit session: Session) {
   tables foreach {table => if(MTable.getTables(table.baseTableRow.tableName).list.isEmpty) table.ddl.create}
 }
  
  // The query interface for the Suppliers table
  val suppliers: TableQuery[Suppliers] = TableQuery[Suppliers]

  // the query interface for the Coffees table
  val coffees: TableQuery[Coffees] = TableQuery[Coffees]
  
  // Create a connection (called a "session") to an in-memory H2 database
  val db = forURL
  db.withSession { implicit session =>

    val conn=db.createConnection()
    
    val stmt = conn.createStatement();
    stmt.executeUpdate("SET FOREIGN_KEY_CHECKS=0")
    stmt.executeUpdate("DROP TABLE IF Exists Suppliers")
        stmt.executeUpdate("DROP TABLE IF Exists Coffees")
    stmt.executeUpdate("SET FOREIGN_KEY_CHECKS=1")
    stmt.close();
    conn.close();
    
    // Create the schema by combining the DDLs for the Suppliers and Coffees
    // tables using the query interfaces
    
    createIfNotExists(suppliers,coffees)
  
    /* Create / Insert */
  
    // Insert some suppliers
    suppliers += (101, "Acme, Inc.", "99 Market Street", "Groundsville", "CA", "95199")
    suppliers += ( 49, "Superior Coffee", "1 Party Place", "Mendocino", "CA", "95460")
    suppliers += (150, "The High Ground", "100 Coffee Lane", "Meadows", "CA", "93966")

    // Insert some coffees (using JDBC's batch insert feature)
    val coffeesInsertResult: Option[Int] = coffees ++= Seq (
      ("Colombian",         101, 7.99, 0, 0),
      ("French_Roast",       49, 8.99, 0, 0),
      ("Espresso",          150, 9.99, 0, 0),
      ("Colombian_Decaf",   101, 8.99, 0, 0),
      ("French_Roast_Decaf", 49, 9.99, 0, 0)
    )
  
    val allSuppliers: List[(Int, String, String, String, String, String)] =
      suppliers.list

    // Print the number of rows inserted
    coffeesInsertResult foreach { numRows =>
      println(s"Inserted $numRows rows into the Coffees table")
    }
   
    /* Read / Query / Select */
  
    // Print the SQL for the Coffees query
    println("Generated SQL for base Coffees query:\n" + coffees.selectStatement)

    // Query the Coffees table using a foreach and print each row
    coffees foreach { case (name, supID, price, sales, total) =>
      println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
    }

    /* Filtering / Where */

    // Construct a query where the price of Coffees is > 9.0
    val filterQuery: Query[Coffees, (String, Int, Double, Int, Int), Seq] =
      coffees.filter(_.price > 9.0)

    println("Generated SQL for filter query:\n" + filterQuery.selectStatement)

    // Execute the query
    println(filterQuery.list)
  

    /* Update */
  
    // Construct an update query with the sales column being the one to update
    val updateQuery: Query[Column[Int], Int, Seq] = coffees.map(_.sales)

    // Print the SQL for the Coffees update query
    println("Generated SQL for Coffees update:\n" + updateQuery.updateStatement)
  
    // Perform the update
    val numUpdatedRows = updateQuery.update(1)
  
    println(s"Updated $numUpdatedRows rows")


    /* Delete */

    // Construct a delete query that deletes coffees with a price less than 8.0
    val deleteQuery: Query[Coffees,(String, Int, Double, Int, Int), Seq] =
      coffees.filter(_.price < 8.0)

    // Print the SQL for the Coffees delete query
    println("Generated SQL for Coffees delete:\n" + deleteQuery.deleteStatement)

    // Perform the delete
    val numDeletedRows = deleteQuery.delete

    println(s"Deleted $numDeletedRows rows")
  
  
    /* Selecting Specific Columns */
  
    // Construct a new coffees query that just selects the name
    val justNameQuery: Query[Column[String], String, Seq] = coffees.map(_.name)
  
    println("Generated SQL for query returning just the name:\n" +
      justNameQuery.selectStatement)
  
    // Execute the query
    println(justNameQuery.list)
  
  
    /* Sorting / Order By */
  
    val sortByPriceQuery: Query[Coffees, (String, Int, Double, Int, Int), Seq] =
      coffees.sortBy(_.price)
  
    println("Generated SQL for query sorted by price:\n" +
      sortByPriceQuery.selectStatement)
  
    // Execute the query
    println(sortByPriceQuery.list)
  
  
    /* Query Composition */
  
    val composedQuery: Query[Column[String], String, Seq] =
      coffees.sortBy(_.name).take(3).filter(_.price > 9.0).map(_.name)
  
    println("Generated SQL for composed query:\n" +
      composedQuery.selectStatement)
  
    // Execute the composed query
    println(composedQuery.list)
  
  
    /* Joins */
  
    // Join the tables using the relationship defined in the Coffees table
    val joinQuery: Query[(Column[String], Column[String]), (String, String), Seq] = for {
      c <- coffees if c.price > 9.0
      s <- c.supplier
    } yield (c.name, s.name)

    println("Generated SQL for the join query:\n" + joinQuery.selectStatement)

    // Print the rows which contain the coffee name and the supplier name
    println(joinQuery.list)
    
    
    /* Computed Values */
    
    // Create a new computed column that calculates the max price
    val maxPriceColumn: Column[Option[Double]] = coffees.map(_.price).max
    
    println("Generated SQL for max price column:\n" + maxPriceColumn.selectStatement)
    
    // Execute the computed value query
    println(maxPriceColumn.run)
    
    
    /* Manual SQL / String Interpolation */

    // Required import for the sql interpolator
    import scala.slick.jdbc.StaticQuery.interpolation
  
    // A value to insert into the statement
    val state = "CA"
  
    // Construct a SQL statement manually with an interpolated value
    val plainQuery = sql"select SUP_NAME from SUPPLIERS where STATE = $state".as[String]
    
    println("Generated SQL for plain query:\n" + plainQuery.getStatement)
    
    // Execute the query
    println(plainQuery.list)
    
  }
}

InvokerMethods.scala

package slick.lifted

import scala.slick.driver.MySQLDriver.simple._
import slick._

// Demonstrates various ways of reading data from an Invoker.
object InvokerMethods extends App {

  // A simple dictionary table with keys and values
  class Dict(tag: Tag) extends Table[(Int, String)](tag, "INT_DICT") {
    def key = column[Int]("MYKEY", O.PrimaryKey)
    def value = column[String]("MYVALUE")
    def * = (key, value)
  }
  val dict = TableQuery[Dict]

  val db =forURL()
  db.withSession { implicit session =>

    val conn=db.createConnection()
    
    val stmt = conn.createStatement();
    stmt.executeUpdate("DROP TABLE IF Exists INT_DICT")
    stmt.close();
    conn.close();
    
    // Create the dictionary table and insert some data
    dict.ddl.create
    dict ++= Seq(1 -> "a", 2 -> "b", 3 -> "c", 4 -> "d", 5 -> "e")

    // Define a pre-compiled parameterized query for reading all key/value
    // pairs up to a given key.
    val upTo = Compiled { k: Column[Int] =>
      dict.filter(_.key <= k).sortBy(_.key)
    }

    println("List of k/v pairs up to 3 with .list")
    println("- " + upTo(3).list)

    println("IndexedSeq of k/v pairs up to 3 with .buildColl")
    println("- " + upTo(3).buildColl[IndexedSeq])

    println("Set of k/v pairs up to 3 with .buildColl")
    println("- " + upTo(3).buildColl[Set])

    println("Array of k/v pairs up to 3 with .buildColl")
    println("- " + upTo(3).buildColl[Array])

    println("All keys in an unboxed Array[Int]")
    val allKeys = dict.map(_.key)
    println("  " + allKeys.buildColl[Array])

    println("Stream k/v pairs up to 3 via an Iterator")
    val it = upTo(3).iterator
    try {
      it.foreach { case (k, v) => println(s"- $k -> $v") }
    } finally {
      // Make sure to close the Iterator in case of an error. (It is
      // automatically closed when all data has been read.)
      it.close
    }

    println("Only get the first result, failing if there is none")
    println("- " + upTo(3).first)

    println("Get the first result as an Option, or None")
    println("- " + upTo(3).firstOption)

    println("Map of k/v pairs up to 3 with .toMap")
    println("- " + upTo(3).toMap)

    println("Combine the k/v pairs up to 3 with .foldLeft")
    println("- " + upTo(3).foldLeft("") { case (z, (k, v)) => s"$z[$k -> $v] " })
  }
}

package.scala

package object slick {
 val USERNAME="root"
 val PASSWORD="mypassword"
 val URL="jdbc:mysql://localhost:3306/test"
 val DRIVER="org.mariadb.jdbc.Driver"
 
import scala.slick.driver.MySQLDriver.simple._ 
 
 def forURL()=
   Database.forURL(URL, user=USERNAME,password=PASSWORD,driver = DRIVER)
}

SimpleExampleClass.scala

package slick.direct

import scala.slick.driver.MySQLDriver
import scala.slick.driver.MySQLDriver.simple.{Session, Database}
import scala.slick.direct._
import scala.slick.direct.AnnotationMapper._
import scala.slick.jdbc.StaticQuery.interpolation

import slick._

/** A case class which is mapped to a database table */
@table("COFFEES")
case class Coffee(
  @column("COF_NAME") name: String,
  @column("PRICE") price: Double
)

/** Example queries using the experimental Direct Embedding API */
class SimpleExampleClass {

  // Convenience functions
  def query[T](q: QueryableValue[T])(implicit session: Session): T =
    backend.result(q, session)
  def query[T](q: Queryable[T])(implicit session: Session): Vector[T] =
    backend.result(q, session)

  // Database queries specified using direct embedding
  val coffees = Queryable[Coffee]
  val priceAbove3 = coffees.filter(_.price > 3.0).map(_.name)
  val samePrice = for {
    c1 <- coffees
    c2 <- coffees if c1.price == c2.price
  } yield (c1.name, c2.name)
  
  // some dummy data
  val coffees_data = Vector(
    ("Colombian",          2),
    ("French_Roast",       2),
    ("Espresso",           5),
    ("Colombian_Decaf",    4),
    ("French_Roast_Decaf", 5)
  )

  // Direct embedding backend (AnnotationMapper evaluates the @table
  // and @column annotations. Use custom mapper for other mappings)
  val backend = new SlickBackend(MySQLDriver, AnnotationMapper)

    val db = forURL()
  db withSession { implicit session =>
    
     val conn=db.createConnection()
    
    val stmt = conn.createStatement();    
    stmt.executeUpdate("DROP TABLE IF Exists Coffees")    
    stmt.close();
    conn.close();

    // Insert data using the Plain SQL API
    // (currently not supported by direct embedding)
    sqlu"create table COFFEES(COF_NAME varchar(255), PRICE DOUBLE)".execute
    coffees_data.foreach { case (name, sales) =>
      sqlu"insert into COFFEES values ($name, $sales)".execute
    }

    // Execute the Direct Embedding queries
    Seq(
      coffees,
      coffees.filter(_.price > 3.0).map(_.name), // inline query
      priceAbove3,
      samePrice
    ).foreach( q => println(query(q)) )

    println(query(priceAbove3.length))
  }
}

object SimpleExample extends SimpleExampleClass with App {
  // The Direct embedding is currently not supported in singleton
  // objects, so we use it in a class and extend that class here.
}

Tables.scala

package slick.lifted

import scala.slick.driver.MySQLDriver.simple._
import scala.slick.lifted.ForeignKeyQuery
import scala.slick.lifted.ProvenShape

// A Suppliers table with 6 columns: id, name, street, city, state, zip
class Suppliers(tag: Tag)
  extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {

  // This is the primary key column:
  def id: Column[Int] = column[Int]("SUP_ID", O.PrimaryKey)
  def name: Column[String] = column[String]("SUP_NAME")
  def street: Column[String] = column[String]("STREET")
  def city: Column[String] = column[String]("CITY")
  def state: Column[String] = column[String]("STATE")
  def zip: Column[String] = column[String]("ZIP")
  
  // Every table needs a * projection with the same type as the table's type parameter
  def * : ProvenShape[(Int, String, String, String, String, String)] =
    (id, name, street, city, state, zip)
}

// A Coffees table with 5 columns: name, supplier id, price, sales, total
class Coffees(tag: Tag)
  extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {

  def name: Column[String] = column[String]("COF_NAME", O.PrimaryKey)
  def supID: Column[Int] = column[Int]("SUP_ID")
  def price: Column[Double] = column[Double]("PRICE")
  def sales: Column[Int] = column[Int]("SALES")
  def total: Column[Int] = column[Int]("TOTAL")
  
  def * : ProvenShape[(String, Int, Double, Int, Int)] =
    (name, supID, price, sales, total)
  
  // A reified foreign key relation that can be navigated to create a join
  def supplier: ForeignKeyQuery[Suppliers, (Int, String, String, String, String, String)] = 
    foreignKey("SUP_FK", supID, TableQuery[Suppliers])(_.id)
}