- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
大家好,我正在尝试从我的数据库中检索一些数据,但是我得到了
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'subject_id' in 'where clause'
如果我检索同一个表的其他列,它会起作用。我正在使用 spring 和 hibernate
这是我的 Controller 代码
@RequestMapping(value = "/getAnyQuestion", method = RequestMethod.GET)
public @ResponseBody
String getAnyQuestion(
@RequestParam(value = "subId", required = true) Long subId,
@RequestParam(value = "questionType", required = true) String questionType) {
Question questionList = questionService.getAnyQuestion(subId,questionType);
if (questionList != null) {
questionId = questionList.getId();
return questionList.getQuestionText();
}
return "";
}
这是我的服务
public Question getAnyQuestion(Long subId,String questionType) {
String query = "subject_id = " + subId +" and "+ "questionType='" + questionType +"' and 1=1 order by rand()";
List<Question> questionList = hibernateTemplate.findByCriteria(DetachedCriteria.forClass(Question.class).add(Restrictions.sqlRestriction(query)),0,1);
if (questionList.isEmpty()) {
return null;
}
return questionList.get(0);
}
这是我的域名
@Entity
@Table(name = "问题")公开课问题{
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(length = 50)
private String questionType;
@Column(length = 5000)
private String questionText;
@ManyToOne
private Syllabus syllabus;
@ManyToOne
private Subject subject;
@ManyToOne
private Roll roll;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getQuestionText() {
return questionText;
}
public void setQuestionText(String questionText) {
this.questionText = questionText;
}
public String getQuestionType() {
return questionType;
}
public void setQuestionType(String questionType) {
this.questionType = questionType;
}
public Syllabus getSyllabus() {
return syllabus;
}
public void setSyllabus(Syllabus syllabus) {
this.syllabus = syllabus;
}
public Subject getSubject() {
return subject;
}
public void setSubject(Subject subject) {
this.subject = subject;
}
public Roll getRoll() {
return roll;
}
public void setRoll(Roll roll) {
this.roll = roll;
}
完整的 StackTrace 是
21296 [http-bio-8080-exec-10] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1052, SQLState: 23000
21296 [http-bio-8080-exec-10] ERROR org.hibernate.util.JDBCExceptionReporter - Column 'subject_id' in where clause is ambiguous
Jun 26, 2013 7:30:12 PM org.apache.catalina.core.StandardWrapperValve invoke
SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/Trainning] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: Hibernate operation: could not execute query; SQL [select this_.id as id3_4_, this_.questionText as question2_3_4_, this_.questionType as question3_3_4_, this_.roll_id as roll4_3_4_, this_.subject_id as subject5_3_4_, this_.syllabus_id as syllabus6_3_4_, roll2_.id as id4_0_, roll2_.contact as contact4_0_, roll2_.rollno as rollno4_0_, roll2_.studentName as studentN4_4_0_, subject3_.id as id0_1_, subject3_.course as course0_1_, subject3_.sem as sem0_1_, subject3_.subjectName as subjectN4_0_1_, subject3_.syllabuspath as syllabus5_0_1_, syllabus4_.id as id6_2_, syllabus4_.chapterName as chapterN2_6_2_, syllabus4_.subject_id as subject3_6_2_, subject5_.id as id0_3_, subject5_.course as course0_3_, subject5_.sem as sem0_3_, subject5_.subjectName as subjectN4_0_3_, subject5_.syllabuspath as syllabus5_0_3_ from question this_ left outer join roll roll2_ on this_.roll_id=roll2_.id left outer join subject subject3_ on this_.subject_id=subject3_.id left outer join syllabus syllabus4_ on this_.syllabus_id=syllabus4_.id left outer join subject subject5_ on syllabus4_.subject_id=subject5_.id where subject_id = 1 and questionType='University' and 1=1 order by rand()]; Column 'subject_id' in where clause is ambiguous; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'subject_id' in where clause is ambiguous] with root cause
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'subject_id' in where clause is ambiguous
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1041)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3566)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3498)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2568)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2113)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2275)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:118)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1597)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:306)
at org.springframework.orm.hibernate3.HibernateTemplate$35.doInHibernate(HibernateTemplate.java:966)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:366)
at org.springframework.orm.hibernate3.HibernateTemplate.findByCriteria(HibernateTemplate.java:956)
at org.Trainning.service.question.QuestionServiceImpl.getAnyQuestion(QuestionServiceImpl.java:91)
at org.Trainning.controller.activities.ActivityController.getAnyQuestion(ActivityController.java:749)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:647)
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:603)
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:950)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:859)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:883)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:781)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:224)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:169)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:928)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:987)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:539)
at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:300)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:722)
我做错了什么请帮助我,如果我检索教学大纲数据但给出上述主题错误,它工作正常
这是 hibernate 生成的查询:
Hibernate: select this_.id as id3_4_, this_.questionText as question2_3_4_, this_.questionType as question3_3_4_, this_.roll_id as roll4_3_4_, this_.subject_id as subject5_3_4_, this_.syllabus_id as syllabus6_3_4_, roll2_.id as id4_0_, roll2_.contact as contact4_0_, roll2_.rollno as rollno4_0_, roll2_.studentName as studentN4_4_0_, subject3_.id as id0_1_, subject3_.course as course0_1_, subject3_.sem as sem0_1_, subject3_.subjectName as subjectN4_0_1_, subject3_.syllabuspath as syllabus5_0_1_, syllabus4_.id as id6_2_, syllabus4_.chapterName as chapterN2_6_2_, syllabus4_.subject_id as subject3_6_2_, subject5_.id as id0_3_, subject5_.course as course0_3_, subject5_.sem as sem0_3_, subject5_.subjectName as subjectN4_0_3_, subject5_.syllabuspath as syllabus5_0_3_ from question this_ left outer join roll roll2_ on this_.roll_id=roll2_.id left outer join subject subject3_ on this_.subject_id=subject3_.id left outer join syllabus syllabus4_ on this_.syllabus_id=syllabus4_.id left outer join subject subject5_ on syllabus4_.subject_id=subject5_.id where subject_id = 1 and questionType='University' and 1=1 order by rand() limit ?
根据 Yak 的建议,这里是我的主题域
@Entity
@Table(name = "subject")
public class Subject {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@Column(length=80)
private Double course;
@Column(length=60)
private String subjectName;
@Column(length=10)
private Double sem;
@Column(length=300)
private String syllabuspath;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Double getCourse() {
return course;
}
public void setCourse(Double course) {
this.course = course;
}
public Double getSem() {
return sem;
}
public void setSem(Double sem) {
this.sem = sem;
}
public String getSubjectName() {
return subjectName;
}
public void setSubjectName(String subjectName) {
this.subjectName = subjectName;
}
public String getSyllabuspath() {
return syllabuspath;
}
public void setSyllabuspath(String syllabuspath) {
this.syllabuspath = syllabuspath;
}
}
这是desc给我的
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| questionText | varchar(5000) | YES | | NULL | |
| questionType | varchar(50) | YES | | NULL | |
| roll_id | bigint(20) | YES | MUL | NULL | |
| subject_id | bigint(20) | YES | MUL | NULL | |
| syllabus_id | bigint(20) | YES | MUL | NULL | |
+--------------+---------------+------+-----+---------+----------------+
最佳答案
消除 WHERE
子句中的 column_id
歧义:
where subject_id = 1 and questionType='University' and 1=1 order by rand() limit ?
我猜是这行代码引起的:
String query = "subject_id = " + subId +" and "+ "questionType='" + questionType +"' and 1=1 order by rand()";
应该是这样的
String query = "this_.subject_id = " + subId +" and "+ "this_.questionType='" + questionType +"' and 1=1 order by rand()";
关于mysql - SQL 错误 : 1054, SQLState:42S22,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/17322378/
env file:环境文件: APP_ENV=localAPP_DEBUG=trueAPP_KEY= ...........DB_HOST=srv3.linuxisrael.co.ilDB_D
很难说出这里问的是什么。这个问题是含糊的、模糊的、不完整的、过于宽泛的或修辞性的,无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开它,visit the help center 。 已关
我在这里有一个 plpgsql 函数来指示在 ANALYZE 期间是否引发了任何警告: CREATE OR REPLACE FUNCTION analyzeWarning() RETURNS inte
我创建了一个过程,如果输入等于 0,该过程将抛出 SQLException 并显示错误消息“dwa”。 CREATE PROCEDURE enter_the_dragon(test INT) BEGI
我在尝试通过 8443 门户连接到我的服务器时收到此错误: ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] No such file
在插入投票之前,我需要检查每个用户的声誉数。因此,为了这个目的,我有一个像这样的TRIGGER: CREATE TRIGGER check_reputation BEFORE INSERT ON vo
Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error oraccess violation: 1064 You have
MYSQL 在处理通过别名调用的表的子查询时触发 SQLSTATE[42S02] 错误 “更新 call_log AS c1 INNER JOIN call_log AS c2 ON c1.id =
我在 PostgreSQL (mixbest) 中有一个包含 73018 行的表。我要选择的字段是: sample integer m integer, pciv double prec
我刚刚收到此错误...请帮我解决这个错误! www.sp-power.com Error in file: "/home/sppower6/public_html/app/code/core/Mage
例如,我有产品[香蕉]和产品[橙色],我希望这两个产品在数据库中使用相同的图片。但是,当我尝试添加与第一个产品具有相同图片的第二个产品时,我收到此错误: SQLSTATE[23000]: Integr
已关闭。此问题不符合Stack Overflow guidelines 。目前不接受答案。 已关闭 7 年前。 编辑问题以包含 desired behavior, a specific problem
Closed. This question is off-topic。它当前不接受答案。
运行时: DELIMITER $$ CREATE TRIGGER tr_test BEFORE UPDATE ON test FOR EACH ROW BEGIN
我的代码大部分已经运行起来了。我似乎不断地一遍又一遍地犯同样的错误。我认为这是因为我在错误的地方放了逗号,但我不知道。谁能帮我看一下这段代码吗? setAttribute(PDO::ATTR_ERRM
我正在尝试获取我的 yii 应用程序的时间范围。 这是我的模型: public function timeRange($attribute, $params) { $criteria
prepare($sql); foreach($parameters as $name=>$value){ $query->bindValue($name,$value);
我有一个前触发器,它可以防止数据更新到表中。为此,我使用了“SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'This operation is not allo
您好,我在 SQL 查询中遇到错误,无法弄清楚问题出在哪里。这是到目前为止在 Barmar 的帮助下的查询。 $query = "SELECT c.*, count(s.curso_id) as c
我构建了 restful API 及其工作,但是当我尝试将参数传递给链接时,下面显示错误,尽管当我打印参数时结果是正确的! 详细信息 Type: PDOException Code: 42000 Me
我是一名优秀的程序员,十分优秀!