CODEWITHSUNDEEP
scalah2slick
TheWaveLad
TheWaveLad

Reputation: 1026

Scala Slick doesn't read or write H2 database

I installed H2 for OSX and I can open the console by opening the jar file. I followed the getting started instructions from Slick and ended up with the following final code:

package com.abc.xyz

import slick.driver.H2Driver.api._
import scala.concurrent.ExecutionContext.Implicits.global

object TestSlick extends App {
  val db = Database.forConfig("h2mem1")
  try {
    // Definition of the SUPPLIERS table
    class Suppliers(tag: Tag) extends Table[(Int, String, String, String, String, String)](tag, "SUPPLIERS") {
      def id = column[Int]("SUP_ID", O.PrimaryKey) // This is the primary key column
      def name = column[String]("SUP_NAME")
      def street = column[String]("STREET")
      def city = column[String]("CITY")
      def state = column[String]("STATE")
      def zip = column[String]("ZIP")
      // Every table needs a * projection with the same type as the table's type parameter
      def * = (id, name, street, city, state, zip)
    }
    val suppliers = TableQuery[Suppliers]

    // Definition of the COFFEES table
    class Coffees(tag: Tag) extends Table[(String, Int, Double, Int, Int)](tag, "COFFEES") {
      def name = column[String]("COF_NAME", O.PrimaryKey)
      def supID = column[Int]("SUP_ID")
      def price = column[Double]("PRICE")
      def sales = column[Int]("SALES")
      def total = column[Int]("TOTAL")
      def * = (name, supID, price, sales, total)
      // A reified foreign key relation that can be navigated to create a join
      def supplier = foreignKey("SUP_FK", supID, suppliers)(_.id)
    }
    val coffees = TableQuery[Coffees]

    val setup = DBIO.seq(
      // Create the tables, including primary and foreign keys
      (suppliers.schema ++ coffees.schema).create,

      // 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"),
      // Equivalent SQL code:
      // insert into SUPPLIERS(SUP_ID, SUP_NAME, STREET, CITY, STATE, ZIP) values (?,?,?,?,?,?)

      // Insert some coffees (using JDBC's batch insert feature, if supported by the DB)
      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)
      )
      // Equivalent SQL code:
      // insert into COFFEES(COF_NAME, SUP_ID, PRICE, SALES, TOTAL) values (?,?,?,?,?)
    )

    val setupFuture = db.run(setup)

    println("Coffees:")
    db.run(coffees.result).map(_.foreach {
      case (name, supID, price, sales, total) =>
        println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
    })
  } finally db.close
}

In the application.conf, I have the following entry:

h2mem1 = {
  url = "jdbc:h2:~/test"
  driver = org.h2.Driver
  connectionPool = disabled
  keepAliveConnection = true
  DB_CLOSE_DELAY=-1
}

The program runs without warnings or errors, but the inserted coffees are not listed. It just shows the string "Coffees:" and exits with exit code 0. Checking with the H2 console I find that the given database is indeed empty, so the insertion of data didn't work.

As there are no warnings or errors I don't know where to start. Is something wrong with the H2 database setup? How can I find out?

Upvotes: 1

Views: 1083

Answers (3)

TheWaveLad
TheWaveLad

Reputation: 1026

Thanks for the others answers. Indeed, one should work with futures to make sure it is completed. However, in my case it turned out to be another problem.

Only after removing the connectionPool=disabled line from the application.conf file, the compiler told me that the driver org.h2.Driver couldn't be found. I had to add 

libraryDependencies += "com.h2database" % "h2" % "1.4.196"

to my sbt file.

Upvotes: 3

Mateusz Kubuszok
Mateusz Kubuszok

Reputation: 27595

Notice that db.run(dbio) returns Future. So what your code does is:

  • run setup Future
  • print without waiting for setup to end
  • run get all results Future without waiting for setup to end
  • close connection without waiting for neither of futures

I am surprised by the lack of errors, but maybe it all accidentally finished just fine without connection terminated in the middle of a query.

But to make it more predictable (and do what you want it to do)

(for {
  _ <- db.run(setup)
  _ = println("Coffees:")
  _ <- db.run(coffees.result).map(_.foreach {
    case (name, supID, price, sales, total) =>
      println("  " + name + "\t" + supID + "\t" + price + "\t" + sales + "\t" + total)
    }
  )
} ()).onComplete { _ => db.close }

Upvotes: 2

Knows Not Much
Knows Not Much

Reputation: 31586

the problem with your code above is that the db.run(setup) returns a future and you can query your table only when that future is completed. If you try to query before the future returned by db.run(setup) has completed, you will not have the data in the database. So change your code to

val query = for {
  r <- coffees
} yield (r.name, r.supID, r.price, r.sales, r.total)
val future = for {
  _ <- db.run(setup)
  r <- db.run(query.result)
} yield r
val rows = Await.result(future, Duration.Inf)
rows.foreach{ row =>
   println(row)
}

In the code above. I execute the query only after the db.run(setup) future has completed.

Also the return value from the query is also a future so I need to add an await call to ensure that all results have been returned and only then try to print the results.

Upvotes: 2

Related Questions