scala 在 PostgreSQL 和 Slick 中使用自动递增字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13199198/
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
Using Auto Incrementing fields with PostgreSQL and Slick
提问by Hyman
How does one insert records into PostgreSQL using AutoInc keys with Slick mapped tables? If I use and Option for the id in my case class and set it to None, then PostgreSQL will complain on insert that the field cannot be null. This works for H2, but not for PostgreSQL:
如何使用带有 Slick 映射表的 AutoInc 键将记录插入 PostgreSQL？如果我在我的案例类中使用和 Option 作为 id 并将其设置为 None，那么 PostgreSQL 将在插入时抱怨该字段不能为空。这适用于 H2，但不适用于 PostgreSQL：
//import scala.slick.driver.H2Driver.simple._
//import scala.slick.driver.BasicProfile.SimpleQL.Table
import scala.slick.driver.PostgresDriver.simple._
import Database.threadLocalSession
object TestMappedTable extends App{
case class User(id: Option[Int], first: String, last: String)
object Users extends Table[User]("users") {
def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
def first = column[String]("first")
def last = column[String]("last")
def * = id.? ~ first ~ last <> (User, User.unapply _)
def ins1 = first ~ last returning id
val findByID = createFinderBy(_.id)
def autoInc = id.? ~ first ~ last <> (User, User.unapply _) returning id
}
// implicit val session = Database.forURL("jdbc:h2:mem:test1", driver = "org.h2.Driver").createSession()
implicit val session = Database.forURL("jdbc:postgresql:test:slicktest",
driver="org.postgresql.Driver",
user="postgres",
password="xxx")
session.withTransaction{
Users.ddl.create
// insert data
print(Users.insert(User(None, "Hyman", "Green" )))
print(Users.insert(User(None, "Joe", "Blue" )))
print(Users.insert(User(None, "John", "Purple" )))
val u = Users.insert(User(None, "Jim", "Yellow" ))
// println(u.id.get)
print(Users.autoInc.insert(User(None, "Johnathan", "Seagul" )))
}
session.withTransaction{
val queryUsers = for {
user <- Users
} yield (user.id, user.first)
println(queryUsers.list)
Users.where(_.id between(1, 2)).foreach(println)
println("ID 3 -> " + Users.findByID.first(3))
}
}
Using the above with H2 succeeds, but if I comment it out and change to PostgreSQL, then I get:
将上述与 H2 一起使用成功，但是如果我将其注释掉并更改为 PostgreSQL，那么我会得到：
[error] (run-main) org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint
org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint
采纳答案by Bruno
This is working here:
这是在这里工作：
object Application extends Table[(Long, String)]("application") {
def idlApplication = column[Long]("idlapplication", O.PrimaryKey, O.AutoInc)
def appName = column[String]("appname")
def * = idlApplication ~ appName
def autoInc = appName returning idlApplication
}
var id = Application.autoInc.insert("App1")
This is how my SQL looks:
这是我的 SQL 的样子：
CREATE TABLE application
(idlapplication BIGSERIAL PRIMARY KEY,
appName VARCHAR(500));
Update:
更新：
The specific problem with regard to a mapped table with User (as in the question) can be solved as follows:
关于带有 User 的映射表的特定问题（如问题中所述）可以按如下方式解决：
def forInsert = first ~ last <>
({ (f, l) => User(None, f, l) }, { u:User => Some((u.first, u.last)) })
This is from the test cases in the Slick git repository.
这是来自Slick git 存储库中的测试用例。
回答by Leonidas A
I tackled this problem in an different way. Since I expect my Userobjects to always have an id in my application logic and the only point where one would not have it is during the insertion to the database, I use an auxiliary NewUsercase class which doesn't have an id.
我以不同的方式解决了这个问题。因为我希望我的User对象在我的应用程序逻辑中总是有一个 id 并且唯一没有它的点是在插入数据库期间，我使用了一个NewUser没有 id的辅助case 类。
case class User(id: Int, first: String, last: String)
case class NewUser(first: String, last: String)
object Users extends Table[User]("users") {
def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
def first = column[String]("first")
def last = column[String]("last")
def * = id ~ first ~ last <> (User, User.unapply _)
def autoInc = first ~ last <> (NewUser, NewUser.unapply _) returning id
}
val id = Users.autoInc.insert(NewUser("John", "Doe"))
Again, Usermaps 1:1 to the database entry/row while NewUsercould be replaced by a tuple if you wanted to avoid having the extra case class, since it is only used as a data container for the insertinvocation.
同样，如果您想避免使用额外的 case 类，可以User将 1:1 映射到数据库条目/行，而NewUser可以用元组替换，因为它仅用作insert调用的数据容器。
EDIT:If you want more safety (with somewhat increased verbosity) you can make use of a trait for the case classes like so:
编辑：如果你想要更多的安全性（稍微增加冗长），你可以像这样使用案例类的特征：
trait UserT {
def first: String
def last: String
}
case class User(id: Int, first: String, last: String) extends UserT
case class NewUser(first: String, last: String) extends UserT
// ... the rest remains intact
In this case you would apply your model changes to the trait first (including any mixins you might need), and optionally add default values to the NewUser.
在这种情况下，您将首先将模型更改应用于特征（包括您可能需要的任何混合），并可选择将默认值添加到NewUser.
Author's opinion: I still prefer the no-trait solution as it is more compact and changes to the model are a matter of copy-pasting the Userparams and then removing the id(auto-inc primary key), both in case class declaration and in table projections.
作者的意见：我仍然更喜欢 no-trait 解决方案，因为它更紧凑，对模型的更改是复制粘贴User参数然后删除id(auto-inc 主键) 的问题，无论是在 case 类声明中还是在表中预测。
回答by alexx
We're using a slightly different approach. Instead of creating a further projection, we request the next id for a table, copy it into the case class and use the default projection '*' for inserting the table entry.
我们使用了一种稍微不同的方法。我们没有创建进一步的投影，而是请求表的下一个 id，将其复制到案例类中，并使用默认投影“*”插入表条目。
For postgres it looks like this:
对于 postgres，它看起来像这样：
Let your Table-Objects implement this trait
让你的表对象实现这个特性
trait TableWithId { this: Table[_] =>
/**
* can be overriden if the plural of tablename is irregular
**/
val idColName: String = s"${tableName.dropRight(1)}_id"
def id = column[Int](s"${idColName}", O.PrimaryKey, O.AutoInc)
def getNextId = (Q[Int] + s"""select nextval('"${tableName}_${idColName}_seq"')""").first
}
All your entity case classes need a method like this (should also be defined in a trait):
你所有的实体案例类都需要一个这样的方法（也应该在特征中定义）：
case class Entity (...) {
def withId(newId: Id): Entity = this.copy(id = Some(newId)
}
New entities can now be inserted this way:
现在可以通过这种方式插入新实体：
object Entities extends Table[Entity]("entities") with TableWithId {
override val idColName: String = "entity_id"
...
def save(entity: Entity) = this insert entity.withId(getNextId)
}
The code is still not DRY, because you need to define the withId method for each table. Furthermore you have to request the next id before you insert an entity which might lead to performance impacts, but shouldn't be notable unless you insert thousands of entries at a time.
代码还是不DRY，因为需要为每个表定义withId方法。此外，您必须在插入可能导致性能影响的实体之前请求下一个 id，但除非您一次插入数千个条目，否则不应引起注意。
The main advantage is that there is no need for a second projection what makes the code less error prone, in particular for tables having many columns.
主要优点是不需要第二个投影，这使得代码不易出错，特别是对于具有许多列的表。
回答by James
Another trick is making the id of the case class a var
另一个技巧是将案例类的 id 设为 var
case class Entity(var id: Long)
To insert an instance, create it like below
Entity(null.asInstanceOf[Long])
要插入一个实例，像下面这样创建它
Entity(null.asInstanceOf[Long])
I've tested that it works.
我已经测试过它有效。
回答by Abhishek Jangalwa
The simplest solution was to use the SERIAL type like this:
最简单的解决方案是像这样使用 SERIAL 类型：
def id = column[Long]("id", SqlType("SERIAL"), O.PrimaryKey, O.AutoInc)
def id = column[Long]("id", SqlType("SERIAL"), O.PrimaryKey, O.AutoInc)
Here's a more concrete block:
这是一个更具体的块：
// A case class to be used as table map
case class CaseTable( id: Long = 0L, dataType: String, strBlob: String)
// Class for our Table
class MyTable(tag: Tag) extends Table[CaseTable](tag, "mytable") {
// Define the columns
def dataType = column[String]("datatype")
def strBlob = column[String]("strblob")
// Auto Increment the id primary key column
def id = column[Long]("id", SqlType("SERIAL"), O.PrimaryKey, O.AutoInc)
// the * projection (e.g. select * ...) auto-transforms the tupled column values
def * = (id, dataType, strBlob) <> (CaseTable.tupled, CaseTable.unapply _)
}
// Insert and get auto incremented primary key
def insertData(dataType: String, strBlob: String, id: Long = 0L): Long = {
// DB Connection
val db = Database.forURL(jdbcUrl, pgUser, pgPassword, driver = driverClass)
// Variable to run queries on our table
val myTable = TableQuery[MyTable]
val insert = try {
// Form the query
val query = myTable returning myTable.map(_.id) += CaseTable(id, dataType, strBlob)
// Execute it and wait for result
val autoId = Await.result(db.run(query), maxWaitMins)
// Return ID
autoId
}
catch {
case e: Exception => {
logger.error("Error in inserting using Slick: ", e.getMessage)
e.printStackTrace()
-1L
}
}
insert
}
回答by Alex Klibisz
The solution I've found is to use SqlType("Serial")in the column definition. I haven't tested it extensively yet, but it seems to work so far.
我找到的解决方案是SqlType("Serial")在列定义中使用。我还没有对其进行广泛的测试，但到目前为止它似乎有效。
So instead of
所以代替
def id: Rep[PK[SomeTable]] = column[PK[SomeTable]]("id", O.PrimaryKey, O.AutoInc)
You should do:
你应该做：
def id: Rep[PK[SomeTable]] = column[PK[SomeTable]]("id", SqlType("SERIAL"), O.PrimaryKey, O.AutoInc)
Where PKis defined like the example in the "Essential Slick" book:
WherePK的定义类似于“Essential Slick”一书中的示例：
final case class PK[A](value: Long = 0L) extends AnyVal with MappedTo[Long]
回答by Rene Cejas Bolecek
I've faced the same problem trying to make the computer-database sample from play-slick-3.0 when I changed the db to Postgres. What solved the problem was to change the id column (primary key) type to SERIAL in the evolution file /conf/evolutions/default/1.sql (originally was in BIGINT). Take a look at https://groups.google.com/forum/?fromgroups=#%21topic/scalaquery/OEOF8HNzn2U
for the whole discussion.
Cheers,
ReneX
当我将数据库更改为 Postgres 时，我在尝试从 play-slick-3.0 制作计算机数据库示例时遇到了同样的问题。解决问题的方法是将evolution文件/conf/evolutions/default/1.sql中的id列（主键）类型改为SERIAL（原来是BIGINT）。查看https://groups.google.com/forum/?fromgroups=#%21topic/scalaquery/OEOF8HNzn2U
以了解整个讨论。干杯，雷内克斯
