Select * or Equivalent in Scala Query

问题:

I've got this ScalaQuery model in Playframework 2

object User {
    implicit object UserFormat extends Format[User] {
    def reads(json: JsValue) : User = User(
        None,
        (json \ "firstName").as[String],
        (json \ "lastName").as[String],
        (json \ "email").as[String]
    )

    def writes(user: User) : JsValue = JsObject(Seq(
        "firstName" -> JsString(user.firstName),
        "lastName" -> JsString(user.lastName),
        "email" -> JsString(user.email))
    )
    }
}

object UsersTable extends Table[User]("users") {
    def id = column[Long]("USER_ID", O.PrimaryKey, O.AutoInc)
    def fName = column[String]("USER_FIRSTNAME", O.NotNull)
    def lName= column[String]("USER_LASTNAME", O.NotNull)
    def email = column[String]("USER_EMAIL", O.NotNull)

    def user_email_idx = index("user_email_idx", email, unique = true)

    def * =  id.? ~ fName ~ lName ~ email <> (User.apply _, User.unapply _)
    def forInsert = fName ~ lName ~ email <> ({ (f, l, e) => User(None, f, l, e) }, { u:User => Some((u.firstName, u.lastName, u.email)) })
}

I'd like to perform a Select * and return all the rows in UsersTable. Is it possible to do this using the UsersTable projection? I've seen some examples that look like this

        UsersTable.where(_.fName startsWith "H").list 

to select rows that fit a criteria. How do I do this without passing in any of that?

Thanks!

回答1:

All you should need to do is this:

val query = for(r <- MyTable) yield r
val results = query.list

The first line creates the actual query object that represents something like select * from MyTable, and the second line actual invokes that and loads the results into memory.

The important implicits you might want to know about are tableToQuery, which lets the MyTable have for-comprehensions on it; and queryToQueryInvoker, which pimps the query so that it has methods like list.

Alternatively you should be able to do something like

val query: Query[User] = MyTable

which should use the tableToQuery implicit to satisfy the type requirement.

回答2:

I think this is what you are looking for: https://github.com/szeiger/scalaquery-examples/blob/master/src/main/scala/org/scalaquery/examples/FirstExample.scala#L73

See also http://scalaquery.org/doc/api/scalaquery-0.9.4/#org.scalaquery.ql.Query for all the methods of the Query class.

You can also use for-comprehensions to query your tables:

for {
  record <- MyTable if record.column0 === "foo"
} yield record.column1 ~ record.column2

回答3:

I've done it for now with val userQuery = UsersTable.where(_.email =!= ""). But I'm sure this isn't the best answer. So keeping this open.