我很难使用PostgreSQL 9.1让"hasMany"在Grails 2.0.1中工作。我有两张桌子:
CREATE TABLE "_QUESTIONS"
(
"QUESTION_ID" bigint NOT NULL,
"TEXT" text,
CONSTRAINT "PK" PRIMARY KEY ("QUESTION_ID" )
)
WITH (
OIDS=FALSE
);
ALTER TABLE "_QUESTIONS"
OWNER TO postgres;
CREATE TABLE "_ANSWERS"
(
"ANSWER_ID" bigint NOT NULL,
"TEXT" text,
"QUESTION_ID" bigint,
CONSTRAINT "PK1" PRIMARY KEY ("ANSWER_ID" ),
CONSTRAINT "FK" FOREIGN KEY ("QUESTION_ID")
REFERENCES "_QUESTIONS" ("QUESTION_ID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE "_ANSWERS"
OWNER TO postgres;
和两个域类:
class Question {
String text
String toString(){
text
}
static constraints = {
}
static hasMany = [answers: Answer]
static mapping = {
table '`_QUESTIONS`'
version false
id generator: 'identity'
id column: '`QUESTION_ID`'
text column: '`TEXT`'
}
}
class Answer {
String text
Question question
String toString(){
text
}
static constraints = {
}
static belongsTo = [question : Question]
static mapping = {
table '`_ANSWERS`'
version false
id generator: 'identity'
id column: '`ANSWER_ID`'
text column: '`TEXT`'
question column: '`QUESTION_ID`'
}
}
我已经为它们生成了视图和控制器,当我试图浏览一个特定的问题时,我会得到以下错误:
URI:/hasManyTest/question/show/1
Class:org.postgresql.util.PSQLException
Message:ERROR: column answers0_.question_id does not exist Position: 8
带有堆栈跟踪:
Line | Method
->> 8 | runWorker in grails-appviewsquestionshow.gsp
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Caused by SQLGrammarException: could not initialize a collection: [hasmanytest.Question.answers#1]
->> 26 | doCall in C__Users_root_IdeaProjects_hasManyTest_grails_app_views_question_show_gsp$_run_closure2
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 55 | run in C__Users_root_IdeaProjects_hasManyTest_grails_app_views_question_show_gsp
| 1110 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 603 | run in java.util.concurrent.ThreadPoolExecutor$Worker
^ 722 | run . . . in java.lang.Thread
Caused by PSQLException: ERROR: column answers0_.question_id does not exist
Position: 8
->> 2103 | receiveErrorResponse in org.postgresql.core.v3.QueryExecutorImpl
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
| 1836 | processResults in ''
| 257 | execute . in ''
| 512 | execute in org.postgresql.jdbc2.AbstractJdbc2Statement
| 388 | executeWithFlags in ''
| 273 | executeQuery in ''
| 96 | executeQuery in org.apache.commons.dbcp.DelegatingPreparedStatement
| 26 | doCall in C__Users_root_IdeaProjects_hasManyTest_grails_app_views_question_show_gsp$_run_closure2
| 55 | run . . . in C__Users_root_IdeaProjects_hasManyTest_grails_app_views_question_show_gsp
| 1110 | runWorker in java.util.concurrent.ThreadPoolExecutor
| 603 | run . . . in java.util.concurrent.ThreadPoolExecutor$Worker
^ 722 | run in java.lang.Thread
在过去的几天里,我做了很多体操,但似乎没有什么帮助,当我取消协会时,一切都很好。我是不是错过了一些显而易见的东西?
问题似乎是Grails/GORM映射所做的假设与用于创建表的SQL之间不匹配。
如果在上面的SQL中省略了表和列名周围的引号,则表和列将不区分大小写。从…起http://wiki.postgresql.org/wiki/Things_to_find_out_about_when_moving_from_MySQL_to_PostgreSQL:
PostgreSQL中的数据库、表、字段和列名是独立于大小写的,除非您在它们的名称周围使用双引号创建它们,在这种情况下,它们区分大小写。在MySQL中,表名可以区分大小写,也可以不区分大小写。
从等式中去掉Grails,如果您使用以下方法创建表:
CREATE TABLE "_QUESTIONS"
(
"QUESTION_ID" bigint NOT NULL,
"TEXT" text,
CONSTRAINT "PK" PRIMARY KEY ("QUESTION_ID" )
)
WITH (
OIDS=FALSE
);
然后:
test=> select * from _questions;
ERROR: relation "_questions" does not exist
LINE 1: select * from _questions
^
test=> select * from _QUESTIONS;
ERROR: relation "_questions" does not exist
LINE 1: select * from _QUESTIONS;
^
test=> select * from "_QUESTIONS";
QUESTION_ID | TEXT
-------------+------
(0 rows)
但是,如果您创建的表没有在表和列名周围加引号:
CREATE TABLE _QUESTIONS
(
QUESTION_ID bigint NOT NULL,
TEXT text,
CONSTRAINT PK PRIMARY KEY (QUESTION_ID)
)
WITH (
OIDS=FALSE
);
然后:
test=> select * from _questions;
question_id | text
-------------+------
(0 rows)
test=> select * from _QUESTIONS;
question_id | text
-------------+------
(0 rows)
test=> select * from "_QUESTIONS";
ERROR: relation "_QUESTIONS" does not exist
LINE 1: select * from "_QUESTIONS";
^
我已经设法解决了这个问题。事实证明,ORM试图通过其小写名称访问关联列,更改名称解决了这个问题。