scala 如果 Slick 3.0.0 中不存在则插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30706193/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Insert if not exists in Slick 3.0.0
提问by Ixx
I'm trying to insert if not exists, I found this postfor 1.0.1, 2.0.
如果不存在，我正在尝试插入，我发现这篇文章适用于 1.0.1、2.0。
I found snippet using transactionally in the docs of 3.0.0
我在3.0.0 的文档中发现了使用事务的片段
val a = (for {
ns <- coffees.filter(_.name.startsWith("ESPRESSO")).map(_.name).result
_ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally
val f: Future[Unit] = db.run(a)
I'm struggling to write the logic from insert if not exists with this structure. I'm new to Slick and have little experience with Scala. This is my attempt to do insert if not exists outside the transaction...
如果此结构不存在，我正在努力从插入中编写逻辑。我是 Slick 的新手，对 Scala 的经验很少。这是我尝试插入如果不存在于事务之外...
val result: Future[Boolean] = db.run(products.filter(_.name==="foo").exists.result)
result.map { exists =>
if (!exists) {
products += Product(
None,
productName,
productPrice
)
}
}
But how do I put this in the transactionally block? This is the furthest I can go:
但是我如何把它放在事务块中呢？这是我能走的最远的地方：
val a = (for {
exists <- products.filter(_.name==="foo").exists.result
//???
// _ <- DBIO.seq(ns.map(n => coffees.filter(_.name === n).delete): _*)
} yield ()).transactionally
Thanks in advance
提前致谢
采纳答案by Ixx
This is the version I came up with:
这是我想出的版本：
val a = (
products.filter(_.name==="foo").exists.result.flatMap { exists =>
if (!exists) {
products += Product(
None,
productName,
productPrice
)
} else {
DBIO.successful(None) // no-op
}
}
).transactionally
It's is a bit lacking though, for example it would be useful to return the inserted or existing object.
但是它有点缺乏，例如返回插入的或现有的对象会很有用。
For completeness, here the table definition:
为了完整起见，这里的表定义：
case class DBProduct(id: Int, uuid: String, name: String, price: BigDecimal)
class Products(tag: Tag) extends Table[DBProduct](tag, "product") {
def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // This is the primary key column
def uuid = column[String]("uuid")
def name = column[String]("name")
def price = column[BigDecimal]("price", O.SqlType("decimal(10, 4)"))
def * = (id, uuid, name, price) <> (DBProduct.tupled, DBProduct.unapply)
}
val products = TableQuery[Products]
I'm using a mapped table, the solution works also for tuples, with minor changes.
我正在使用映射表，该解决方案也适用于元组，只需稍作改动。
Note also that it's not necessary to define the id as optional, according to the documentationit's ignored in insert operations:
另请注意，根据插入操作中忽略的文档，没有必要将 id 定义为可选：
When you include an AutoInc column in an insert operation, it is silently ignored, so that the database can generate the proper value
当您在插入操作中包含 AutoInc 列时，它会被静默忽略，以便数据库可以生成正确的值
And here the method:
这里的方法：
def insertIfNotExists(productInput: ProductInput): Future[DBProduct] = {
val productAction = (
products.filter(_.uuid===productInput.uuid).result.headOption.flatMap {
case Some(product) =>
mylog("product was there: " + product)
DBIO.successful(product)
case None =>
mylog("inserting product")
val productId =
(products returning products.map(_.id)) += DBProduct(
0,
productInput.uuid,
productInput.name,
productInput.price
)
val product = productId.map { id => DBProduct(
id,
productInput.uuid,
productInput.name,
productInput.price
)
}
product
}
).transactionally
db.run(productAction)
}
(Thanks Matthew Pocock from Google group thread, for orienting me to this solution).
（感谢来自Google group thread 的Matthew Pocock为我介绍了这个解决方案）。
回答by dwickern
It is possible to use a single insert ... if not existsquery. This avoids multiple database round-trips and race conditions (transactions may not be enough depending on isolation level).
可以使用单个insert ... if not exists查询。这避免了多次数据库往返和竞争条件（根据隔离级别，事务可能不够）。
def insertIfNotExists(name: String) = users.forceInsertQuery {
val exists = (for (u <- users if u.name === name.bind) yield u).exists
val insert = (name.bind, None) <> (User.apply _ tupled, User.unapply)
for (u <- Query(insert) if !exists) yield u
}
Await.result(db.run(DBIO.seq(
// create the schema
users.schema.create,
users += User("Bob"),
users += User("Bob"),
insertIfNotExists("Bob"),
insertIfNotExists("Fred"),
insertIfNotExists("Fred"),
// print the users (select * from USERS)
users.result.map(println)
)), Duration.Inf)
Output:
输出：
Vector(User(Bob,Some(1)), User(Bob,Some(2)), User(Fred,Some(3)))
Generated SQL:
生成的 SQL：
insert into "USERS" ("NAME","ID") select ?, null where not exists(select x2."NAME", x2."ID" from "USERS" x2 where x2."NAME" = ?)
回答by Aleksandr Vinokurov
I've run into the solution that looks more complete. Section 3.1.7 More Control over Insertsof the Essential Slickbook has the example.
我遇到了看起来更完整的解决方案。Essential Slick书的第 3.1.7 节对插入的更多控制有示例。
At the end you get smth like:
最后你会得到类似的东西：
val entity = UserEntity(UUID.random, "jay", "jay@localhost")
val exists =
users
.filter(
u =>
u.name === entity.name.bind
&& u.email === entity.email.bind
)
.exists
val selectExpression = Query(
(
entity.id.bind,
entity.name.bind,
entity.email.bind
)
).filterNot(_ => exists)
val action = usersDecisions
.map(u => (u.id, u.name, u.email))
.forceInsertQuery(selectExpression)
exec(action)
// res17: Int = 1
exec(action)
// res18: Int = 0
回答by mduf
according to the slick 3.0 manual insert query section (http://slick.typesafe.com/doc/3.0.0/queries.html), the inserted values can be returned with id as below:
根据 slick 3.0 手动插入查询部分（http://slick.typesafe.com/doc/3.0.0/queries.html），插入的值可以使用 id 返回，如下所示：
def insertIfNotExists(productInput: ProductInput): Future[DBProduct] = {
val productAction = (
products.filter(_.uuid===productInput.uuid).result.headOption.flatMap {
case Some(product) =>
mylog("product was there: " + product)
DBIO.successful(product)
case None =>
mylog("inserting product")
(products returning products.map(_.id)
into ((prod,id) => prod.copy(id=id))) += DBProduct(
0,
productInput.uuid,
productInput.name,
productInput.price
)
}
).transactionally
db.run(productAction)
}
