使用 UUID 作为主键与 H2 内存中数据库的 Slick 一起使用时,我遇到了以下错误。我已经尝试调试了几天,但没有运气:
Data conversion error converting "00000000-0000-0000-0000-000000000001"
下面是可以重现此问题的代码:
import java.util.UUID
import slick.driver.H2Driver.api._
import scala.concurrent.Await
import scala.concurrent.duration.Duration
object Main {
class Tasks(tag: Tag) extends Table[(String, Option[UUID])](tag, "tasks") {
def name: Rep[String] = column[String]("name")
def id = column[UUID]("id", O.SqlType("UUID"), O.PrimaryKey, O.AutoInc)
def * = (name, id ?)
}
def main(args: Array[String]) {
val db = Database.forURL("jdbc:h2:mem:test;MODE=PostgreSQL", driver = "org.h2.Driver")
try {
val tasks = TableQuery[Tasks]
println("Create table: " + tasks.schema.create.statements.mkString("|"))
val setupAction: DBIO[Unit] = DBIO.seq(
tasks.schema.create,
tasks.+=("foo", None)
)
Await.result(db.run(setupAction), Duration.Inf)
} finally db.close
}
}
任何人都可以在这里提供一些帮助吗?
---更新---
表创建查询如下所示:
create table "tasks" (
"name" VARCHAR NOT NULL,
"id" UUID GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY
);
全栈跟踪如下所示:
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Data conversion error converting "00000000-0000-0000-0000-000000000001"; SQL statement:
insert into "tasks" ("name") values (?) [22018-187]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
at org.h2.message.DbException.get(DbException.java:168)
at org.h2.value.Value.convertTo(Value.java:902)
at org.h2.value.Value.getLong(Value.java:443)
at org.h2.table.Column.updateSequenceIfRequired(Column.java:339)
at org.h2.table.Column.validateConvertUpdateSequence(Column.java:331)
at org.h2.table.Table.validateConvertUpdateSequence(Table.java:737)
at org.h2.command.dml.Insert.insertRows(Insert.java:151)
at org.h2.command.dml.Insert.update(Insert.java:114)
at org.h2.command.CommandContainer.update(CommandContainer.java:78)
at org.h2.command.Command.executeUpdate(Command.java:254)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:157)
at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:143)
at slick.driver.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction$$anonfun$run$7.apply(JdbcActionComponent.scala:507)
at slick.driver.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction$$anonfun$run$7.apply(JdbcActionComponent.scala:504)
at slick.jdbc.JdbcBackend$SessionDef$class.withPreparedStatement(JdbcBackend.scala:347)
at slick.jdbc.JdbcBackend$BaseSession.withPreparedStatement(JdbcBackend.scala:407)
at slick.driver.JdbcActionComponent$InsertActionComposerImpl.preparedInsert(JdbcActionComponent.scala:498)
at slick.driver.JdbcActionComponent$InsertActionComposerImpl$SingleInsertAction.run(JdbcActionComponent.scala:504)
at slick.driver.JdbcActionComponent$SimpleJdbcDriverAction.run(JdbcActionComponent.scala:32)
at slick.driver.JdbcActionComponent$SimpleJdbcDriverAction.run(JdbcActionComponent.scala:29)
at slick.dbio.DBIOAction$$anon$4$$anonfun$run$3.apply(DBIOAction.scala:214)
at slick.dbio.DBIOAction$$anon$4$$anonfun$run$3.apply(DBIOAction.scala:214)
at scala.collection.Iterator$class.foreach(Iterator.scala:727)
at scala.collection.AbstractIterator.foreach(Iterator.scala:1157)
at scala.collection.IterableLike$class.foreach(IterableLike.scala:72)
at scala.collection.AbstractIterable.foreach(Iterable.scala:54)
at slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:214)
at slick.dbio.DBIOAction$$anon$4.run(DBIOAction.scala:212)
at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.liftedTree1$1(DatabaseComponent.scala:237)
at slick.backend.DatabaseComponent$DatabaseDef$$anon$2.run(DatabaseComponent.scala:237)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NumberFormatException: For input string: "00000000-0000-0000-0000-000000000001"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Long.parseLong(Long.java:589)
at java.lang.Long.parseLong(Long.java:631)
at org.h2.value.Value.convertTo(Value.java:854)
... 31 more
你有一个奇怪的"create table"语句,其序列类型为 UUID。没有数据库支持这一点。例如,您需要使用:
create table tasks (
name VARCHAR NOT NULL,
id UUID DEFAULT RANDOM_UUID() NOT NULL PRIMARY KEY
);
insert into tasks(name) values('Hello');
select * from tasks;
但是随机生成的主键很慢,我建议回到常规序列。
附言这里问了同样的问题:https://github.com/h2database/h2database/issues/303
除了托马斯的回答,另一种选择是将类型从uuid
更改为begint
:
create table "tasks" (
"name" VARCHAR NOT NULL,
"id" BIGINT GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL PRIMARY KEY
);
当表由 Slick/Hibernate/等生成时,您可能无法编写自己的 SQL。更改类型可能是可行的替代方案。