Caused by: java.sql.SQLException: Column 'id' not found
17,423
Your query doesn't return a field named id
. It has fields named aId, qId, and tId
.
You need to use the correct column names in your entities. For example, in your Test entity, you declared a column named id. Except your query doesn't return a column named id, it returns a column named tId. See below for an example of what needs to be changed.
public class Test {
@tId
@Column(name = "tId")
@GeneratedValue(strategy = GenerationType.AUTO)
private long tId;
....
Comments
-
quento almost 2 years
I want to get some fields and then set it to my Test.entity. My SQL query:
query = "SELECT t.id as tId, t.test_name, t.duration, q.id as qId, " + "q.question as question, q.is_multichoice as is_multichoice, " + "q.is_open as is_open, a.id as aId, a.answer_text as answer_text FROM result r " + "JOIN test t ON r.test_id = t.id " + "JOIN user u ON r.user_id = u.id " + "JOIN question q ON t.id = q.test_id JOIN answer a ON q.id = a.question_id " + "WHERE t.id = :testId AND u.id = :userId AND r.permission = :permissionId " + "AND q.archived = false AND a.archived = false", resultClass = com.bionic.entities.Test.class)
Test Entity:
public class Test { @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.AUTO) private long id; @Column(name = "duration", nullable = false) private int duration; @Column(name = "test_name", nullable = false, unique = true) private String testName; @Column(name = "archived", nullable = false) private boolean archived; @OneToMany(mappedBy = "test", fetch = FetchType.EAGER) private Set<Question> questions; @ManyToMany(mappedBy = "tests") private Set<User> users;
Question Entity:
public class Question { @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.AUTO) private long id; @Column(name = "is_multichoice", nullable = false) private boolean isMultichoice; @Column(name = "is_open", nullable = false) private boolean isOpen; @Column(name = "picture") private String picture; @Column(name = "question") private String question; @ManyToOne @JoinColumn(name = "test_id", nullable = false) private Test test; @Column(name = "archived", nullable = false) private boolean isArchived; @OneToMany(mappedBy = "question", fetch = FetchType.EAGER) private Set<Answer> answers;
Answer Entity:
public class Answer { @Id @Column(name = "id") @GeneratedValue(strategy = GenerationType.AUTO) private long id; @Column(name = "answer_text", nullable = false) private String answerText; @Column(name = "mark", nullable = false) private int mark; @ManyToOne @JoinColumn(name = "question_id") private Question question; @Column(name = "picture") private String picture; @Column(name = "archived", nullable = false) private boolean isArchived;
However, after executing this query i am getting exeption :
Caused by: java.sql.SQLException: Column 'id' not found.
DAO.class:
public Test getCurrentTest(long id, long testId, long permissionId) { Query query = em.createNamedQuery("getCurrentTestById"); query.setParameter("userId", id); query.setParameter("testId", testId); query.setParameter("permissionId", permissionId); return (Test) query.getSingleResult(); }
What am i doing wrong?
-
quento over 8 yearshow could i map it to my id filed in entity?
-
quento over 8 yearsI mean that i have 3 fields called id (test, question, aswer), but i can't get by SQL query 3 fields with same name "id"
-
Rabbit over 8 yearsI understand that. And that's why you gave those 3 fields an alias. Thereby changing the name of the fields so they are unique. But that also means your code doesn't see a field named id. You need to reference the field by its new alias.
-
quento over 8 yearsshould i use SqlResultSetMappings for that or rename fields in each entity (this variant i don't like) if i should use SqlResultSetMappings, how could i set fields for answers and questions as they are subenteties
-
Rabbit over 8 yearsYou need to use the correct column names in your entities. For example, in your Test entity, you declared a column named
id
. Except your query doesn't return a column namedid
, it returns a column namedtId
. -
quento over 8 yearsHow can i set tId to test id?
-
Rabbit over 8 yearsI don't know what you mean by that. Your references are ambiguous. I edited the post with more info, hopefully that will clear some things up for you.
-
quento over 8 yearsah, you mean i should call them in db like that