StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Error: 1054, SQLState: 42S22
primarykey
Id
17322378
data
AcceptedAnswerId
17323166
AnswerCount
2
ClosedDate
CommentCount
7
CommunityOwnedDate
CreationDate
2013-06-26T14:05:58.627
FavoriteCount
0
LastActivityDate
2017-01-11T19:08:37.237
LastEditDate
2015-02-19T20:56:13.187
LastEditorUserId
3037819
OwnerUserId
2078291
ParentId
0
PostTypeId
1
Score
2
ViewCount
14170
LastEditorDisplayName
text
Body
Hello Everyone I'm trying to retrive some data from my database but I'm getting <pre><code>com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'subject_id' in 'where clause' </code></pre> It works if I retrive some other column of same table. I'm using spring and hibernate Here is my controller code <pre><code>@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 ""; } </code></pre> Here is my Service <pre><code> 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); } </code></pre> and here is my domain <pre><code>@Entity </code></pre> @Table(name = "question") public class Question { <pre><code>@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; } </code></pre> } Ful StackTrace is <pre><code> 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) </code></pre> What I'm doing wrong please help me it works fine if i retrive syllabus data but give above error for subject Here is the query generated by hibernate: <pre><code>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 ? </code></pre> As suggested by Yak here is my subject domain <pre><code> @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; } } </code></pre> This is what desc gave me <pre><code>| 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 | | +--------------+---------------+------+-----+---------+----------------+ </code></pre>
Tags
<mysql><hibernate><spring-mvc>
Title
SQL Error: 1054, SQLState: 42S22
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USsvjn
UserOwnerUserId
1. USPatty31
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POSQL Error: 1054, SQLState: 42S22
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.