How to send plain SQL queries (and retrieve results) using scala slick 3
Solution 1
I realized I was missing a Seq in the return type:
def getData(TableName: String): Future[Seq[(Int,Double,String)]] = {
db.run(sql"""SELECT * FROM $TableName """.as[(Int, Double, String)])
}
Solution 2
You can use sql"..."
with any String
content using #$tableName
:
db.run(sql"SELECT * FROM #$tableName".as[(Int, Double, String)])
Please remember: don't ever take the tableName
as a user input - otherwise, there is a great risk for SQL injection. The normal $value
solves these problems for you.
Read Slick manual (http://slick.typesafe.com/doc/3.0.0/sql.html#splicing-literal-values)
... sometimes you need to splice literal values directly into the statement, for example to abstract over table names ... You can use #$ instead of $ in all interpolators for this purpose ...
Kyle Balkissoon
Updated on June 17, 2022Comments
-
Kyle Balkissoon almost 2 years
I am trying to make a class that has methods that can send and get data to an SQLite db using plain sql queries. This unfortunately does not work. I do not want to use the withSession implicit parts.
import slick.driver.SQLiteDriver.api._ import slick.lifted.TableQuery import slick.jdbc.JdbcBackend.Database; import scala.concurrent.ExecutionContext.Implicits.global import scala.concurrent._ import ExecutionContext.Implicits.global class DBops { val db = Database.forURL("jdbc:sqlite:S:/testing/testdb.sql",driver = "org.sqlite.JDBC") def getData(TableName: String):Future[(Int,Double,String)]={ db.run(sql"""select * from $TableName """.as[(Int,Double,String)]) } }
The following error is thrown:
type mismatch; found : slick.profile.SqlStreamingAction[Vector[(Int, Double, String)],(Int, Double, String),slick.dbio.Effect] required: slick.dbio.DBIOAction[(Int, Double, String),slick.dbio.NoStream,Nothing] DBops.scala
-
Kyle Balkissoon over 8 yearsWon't be taking it as a user input this is backend only, Is there any way in slick to NOT have to predefine the column types?
-
Matias Saarinen over 8 yearsYou mean the
sql"select...".as[(String, Int)]
? Since you're programming Scala it is very important that you determine the types beforehand - that's type safety. Basically you can use tuples with any "primitive types" or you can create your ownimplicit
GetResult
. Read more: slick.typesafe.com/doc/3.0.0/sql.html#result-sets Plus: I can't see any reason why you could not create aGetResult
which would create a list by just callingrs.next
over the result set but I could not find any reference for that.