CODEWITHSUNDEEP
postgresqlscalaplayframeworksbtslick
Kaviranga
Kaviranga

Reputation: 666

How to use select statement using slick?

I wanted to query data from example table like below using slick

   | id  | username | password   |           
   +-----+----------+------------+
   |  1  | admin    | admin@123  |
   |  2  | user     | user@123   |

The query is

    SELECT password FROM users WHERE username = 'user';

I have read many slick examples on stack overflow but they are more complex queries

I want a simple query as above using slick .

Upvotes: 0

Views: 1221

Answers (3)

Sangeeta
Sangeeta

Reputation: 491

Using slick you can write your implementation like below:

trait UsertDAO {
  this: DbComponent =>

  import driver.api._
    case class User(id: Int, username: String, password: String)

  val userTableQuery = TableQuery[UserTable]

    class UserTable(tag: Tag) extends Table[User](tag, "user") {
        val id = column[String]("id", O.SqlType("NUMBER"))
        val username = column[String]("username", O.SqlType("VARCHAR(200)"))
        val password = column[String]("password", O.SqlType("VARCHAR(200)"))

        def pk: PrimaryKey = primaryKey("pk", id)

        def * : ProvenShape[CjTable] =
          (id, username, password) <> (User.tupled, User.unapply)
      }

}

The above class defines your table structure. Later you can define your method like to run the sql query. : 

  def getPassword(userName: String): Future[Option[String]] = {
    db.run{
      userTableQuery.filter(_.username === userName).map(_.password).to[List].result.headOption
    }
  }

If you still face any issues, Try looking at the CRUD application i made: https://github.com/SangeetaGulia/Student-CRUD-Slick/blob/master/Student-Slick-Project.zip

Upvotes: 1

Mateusz Kubuszok
Mateusz Kubuszok

Reputation: 27535

You can just use plain SQL query for such cases:

sql"""SELECT password FROM users WHERE username = 'user'""".as[String]

This is less portable than building queries, but let you write any arbitrary query and run it without the risk of SQL injection. Though if you are using only such queries then you don't have a reason to use Slick in the first place - you could rather use Quill or Doobie (or both) or ScalikeJDBC or jOOQ.

Upvotes: 1

ashishtomer
ashishtomer

Reputation: 316

First define a class that represents your SQL table

class UsersTable(tag: Tag) extends Table[User](tag, "users") {

  def email: Rep[String] = column[String]("email", O.PrimaryKey)
  def password: Rep[String] = column[String]("password")
  def registered: Rep[Boolean] = column[Boolean]("registered")
  def firstName: Rep[Option[String]] = column[Option[String]]("first_name")
  def lastName: Rep[Option[String]] = column[Option[String]]("last_name")
  def username: Rep[Option[String]] = column[Option[String]]("username", O.Unique)
  def contact: Rep[Option[String]] = column[Option[String]]("contact")

  override def * = (email, password, registered, firstName, lastName, username, contact) <> (User.tupled, User.unapply)
}

Then create a DAO for your table which has the method to select 'one' row. Like this:

class UsersDao @Inject()(protected val dbConfigProvider: DatabaseConfigProvider)
                        (implicit executionContext: ExecutionContext) extends HasDatabaseConfigProvider[JdbcProfile]  {
  import profile.api._
  private val Users = TableQuery[UsersTable]

  def selectOne(email: String): Future[Option[User]] = db.run(Users.filter(_.email === email).result.headOption)

}

Now in your service class read the record like this

class AuthService @Inject()(usersDao: UsersDao) {
  def someFunction(.. some_params ..) {
    usersDao.selectOne(someParama.email).flatMap {
      case Some(user: User) => //Do something if user exists
      case None => //Do something else if user doesn't exist
    }
  }
}

In case you're interested in exploring a play project written in Scala, have a look at this project https://github.com/ashishtomer/fastscraping-web

Upvotes: 0

Related Questions