Mixing
Need help translating a MYSQL DB Sql Query into a Jpql query
0
I have 3 entities - usergroup, user, usergroupmemberships. usergroupmemberships has a 1 to 1 to both user and usergroup. im trying to get a users groups with his id
The following Query works in my DB:
SELECT *
FROM UserGroup g
JOIN UserGroupMembership ON
UserGroupMembership.usergroup_id = g.id
WHERE UserGroupMembership.user_id = 868
Wildfly:
the same as a Java REST POST that uses a query from my entitymanager doesnt with a nullpointerexception
SELECT g
FROM UserGroup g
JOIN UserGroupMembership
ON UserGroupMembership.usergroup = g.id
WHERE UserGroupMembership.user = :id
How do i write this correctly?
Tried different ways to write the query and tried writing user_id
im on wildfly and java ee
public List<UserGroup> findUserGroupbyUser(long id) {
return em.createQuery("SELECT g FROM UserGroup g JOIN
UserGroupMembership ON UserGroupMembership.usergroup = g WHERE
UserGroupMembership.user.id = :id", UserGroup.class).setParameter("id",
id).getResultList();
}
@Entity
public class UserGroupMembership {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@OneToOne(cascade = CascadeType.ALL)
private UserGroup usergroup;
@OneToOne(cascade = CascadeType.ALL)
private User user;
expected result is 1 entry
java mysql sql wildfly jpql
edited Jan 2 at 21:43
Xiaoyu Lu
564618
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
add a comment |
0
I have 3 entities - usergroup, user, usergroupmemberships. usergroupmemberships has a 1 to 1 to both user and usergroup. im trying to get a users groups with his id
The following Query works in my DB:
SELECT *
FROM UserGroup g
JOIN UserGroupMembership ON
UserGroupMembership.usergroup_id = g.id
WHERE UserGroupMembership.user_id = 868
Wildfly:
the same as a Java REST POST that uses a query from my entitymanager doesnt with a nullpointerexception
SELECT g
FROM UserGroup g
JOIN UserGroupMembership
ON UserGroupMembership.usergroup = g.id
WHERE UserGroupMembership.user = :id
How do i write this correctly?
Tried different ways to write the query and tried writing user_id
im on wildfly and java ee
public List<UserGroup> findUserGroupbyUser(long id) {
return em.createQuery("SELECT g FROM UserGroup g JOIN
UserGroupMembership ON UserGroupMembership.usergroup = g WHERE
UserGroupMembership.user.id = :id", UserGroup.class).setParameter("id",
id).getResultList();
}
@Entity
public class UserGroupMembership {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@OneToOne(cascade = CascadeType.ALL)
private UserGroup usergroup;
@OneToOne(cascade = CascadeType.ALL)
private User user;
expected result is 1 entry
java mysql sql wildfly jpql
edited Jan 2 at 21:43
Xiaoyu Lu
564618
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
Here's the documentation on HQL/JPQL. It has a section on joins. docs.jboss.org/hibernate/orm/current/userguide/html_single/…. Don't try random things. Learn the language by reading the documentation. It's much more effective that way. BTW, Your OneToOne should be ManyToOne, since I guess that there are more than one user per group, and that a user can belong to several groups.
– JB Nizet
Jan 2 at 21:34
should it really be a ManyToOne? Im using this help class to evade having to use FetchType.Eager or a huge join fetch statement as wildflys hibernate configuration seems really tricky there to me to not endlessly load new resources in a loop. i would just have a table with a list of objects with 1 user and 1 usergroup and out of this i could filter my result?
– Christopher Gusenbauer
Jan 2 at 21:59
The Doku has a similar example that does it this way: return em.createQuery("SELECT g FROM UserGroup g JOIN UserGroupMembership ugm WHERE ugm.user = :id", UserGroup.class).setParameter("id", em.find(User.class, id)).getResultList(); returns the error Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where usergroupm1_.user_id=868' at line 1
– Christopher Gusenbauer
Jan 2 at 22:08
add a comment |
0
0
0
I have 3 entities - usergroup, user, usergroupmemberships. usergroupmemberships has a 1 to 1 to both user and usergroup. im trying to get a users groups with his id
The following Query works in my DB:
SELECT *
FROM UserGroup g
JOIN UserGroupMembership ON
UserGroupMembership.usergroup_id = g.id
WHERE UserGroupMembership.user_id = 868
Wildfly:
the same as a Java REST POST that uses a query from my entitymanager doesnt with a nullpointerexception
SELECT g
FROM UserGroup g
JOIN UserGroupMembership
ON UserGroupMembership.usergroup = g.id
WHERE UserGroupMembership.user = :id
How do i write this correctly?
Tried different ways to write the query and tried writing user_id
im on wildfly and java ee
public List<UserGroup> findUserGroupbyUser(long id) {
return em.createQuery("SELECT g FROM UserGroup g JOIN
UserGroupMembership ON UserGroupMembership.usergroup = g WHERE
UserGroupMembership.user.id = :id", UserGroup.class).setParameter("id",
id).getResultList();
}
@Entity
public class UserGroupMembership {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@OneToOne(cascade = CascadeType.ALL)
private UserGroup usergroup;
@OneToOne(cascade = CascadeType.ALL)
private User user;
expected result is 1 entry
java mysql sql wildfly jpql
edited Jan 2 at 21:43
Xiaoyu Lu
564618
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
I have 3 entities - usergroup, user, usergroupmemberships. usergroupmemberships has a 1 to 1 to both user and usergroup. im trying to get a users groups with his id
The following Query works in my DB:
SELECT *
FROM UserGroup g
JOIN UserGroupMembership ON
UserGroupMembership.usergroup_id = g.id
WHERE UserGroupMembership.user_id = 868
Wildfly:
the same as a Java REST POST that uses a query from my entitymanager doesnt with a nullpointerexception
SELECT g
FROM UserGroup g
JOIN UserGroupMembership
ON UserGroupMembership.usergroup = g.id
WHERE UserGroupMembership.user = :id
How do i write this correctly?
Tried different ways to write the query and tried writing user_id
im on wildfly and java ee
public List<UserGroup> findUserGroupbyUser(long id) {
return em.createQuery("SELECT g FROM UserGroup g JOIN
UserGroupMembership ON UserGroupMembership.usergroup = g WHERE
UserGroupMembership.user.id = :id", UserGroup.class).setParameter("id",
id).getResultList();
}
@Entity
public class UserGroupMembership {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;
@OneToOne(cascade = CascadeType.ALL)
private UserGroup usergroup;
@OneToOne(cascade = CascadeType.ALL)
private User user;
expected result is 1 entry
java mysql sql wildfly jpql
java mysql sql wildfly jpql
edited Jan 2 at 21:43
Xiaoyu Lu
564618
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
edited Jan 2 at 21:43
Xiaoyu Lu
564618
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
edited Jan 2 at 21:43
Xiaoyu Lu
564618
edited Jan 2 at 21:43
Xiaoyu Lu
564618
edited Jan 2 at 21:43
Xiaoyu Lu
564618
564618
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
asked Jan 2 at 21:25
Christopher GusenbauerChristopher Gusenbauer
43
43
Here's the documentation on HQL/JPQL. It has a section on joins. docs.jboss.org/hibernate/orm/current/userguide/html_single/…. Don't try random things. Learn the language by reading the documentation. It's much more effective that way. BTW, Your OneToOne should be ManyToOne, since I guess that there are more than one user per group, and that a user can belong to several groups.
– JB Nizet
Jan 2 at 21:34
should it really be a ManyToOne? Im using this help class to evade having to use FetchType.Eager or a huge join fetch statement as wildflys hibernate configuration seems really tricky there to me to not endlessly load new resources in a loop. i would just have a table with a list of objects with 1 user and 1 usergroup and out of this i could filter my result?
– Christopher Gusenbauer
Jan 2 at 21:59
The Doku has a similar example that does it this way: return em.createQuery("SELECT g FROM UserGroup g JOIN UserGroupMembership ugm WHERE ugm.user = :id", UserGroup.class).setParameter("id", em.find(User.class, id)).getResultList(); returns the error Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where usergroupm1_.user_id=868' at line 1
– Christopher Gusenbauer
Jan 2 at 22:08
add a comment |
Here's the documentation on HQL/JPQL. It has a section on joins. docs.jboss.org/hibernate/orm/current/userguide/html_single/…. Don't try random things. Learn the language by reading the documentation. It's much more effective that way. BTW, Your OneToOne should be ManyToOne, since I guess that there are more than one user per group, and that a user can belong to several groups.
– JB Nizet
Jan 2 at 21:34
should it really be a ManyToOne? Im using this help class to evade having to use FetchType.Eager or a huge join fetch statement as wildflys hibernate configuration seems really tricky there to me to not endlessly load new resources in a loop. i would just have a table with a list of objects with 1 user and 1 usergroup and out of this i could filter my result?
– Christopher Gusenbauer
Jan 2 at 21:59
The Doku has a similar example that does it this way: return em.createQuery("SELECT g FROM UserGroup g JOIN UserGroupMembership ugm WHERE ugm.user = :id", UserGroup.class).setParameter("id", em.find(User.class, id)).getResultList(); returns the error Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where usergroupm1_.user_id=868' at line 1
– Christopher Gusenbauer
Jan 2 at 22:08
Here's the documentation on HQL/JPQL. It has a section on joins. docs.jboss.org/hibernate/orm/current/userguide/html_single/…. Don't try random things. Learn the language by reading the documentation. It's much more effective that way. BTW, Your OneToOne should be ManyToOne, since I guess that there are more than one user per group, and that a user can belong to several groups.
– JB Nizet
Jan 2 at 21:34
Here's the documentation on HQL/JPQL. It has a section on joins. docs.jboss.org/hibernate/orm/current/userguide/html_single/…. Don't try random things. Learn the language by reading the documentation. It's much more effective that way. BTW, Your OneToOne should be ManyToOne, since I guess that there are more than one user per group, and that a user can belong to several groups.
– JB Nizet
Jan 2 at 21:34
should it really be a ManyToOne? Im using this help class to evade having to use FetchType.Eager or a huge join fetch statement as wildflys hibernate configuration seems really tricky there to me to not endlessly load new resources in a loop. i would just have a table with a list of objects with 1 user and 1 usergroup and out of this i could filter my result?
– Christopher Gusenbauer
Jan 2 at 21:59
should it really be a ManyToOne? Im using this help class to evade having to use FetchType.Eager or a huge join fetch statement as wildflys hibernate configuration seems really tricky there to me to not endlessly load new resources in a loop. i would just have a table with a list of objects with 1 user and 1 usergroup and out of this i could filter my result?
– Christopher Gusenbauer
Jan 2 at 21:59
The Doku has a similar example that does it this way: return em.createQuery("SELECT g FROM UserGroup g JOIN UserGroupMembership ugm WHERE ugm.user = :id", UserGroup.class).setParameter("id", em.find(User.class, id)).getResultList(); returns the error Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where usergroupm1_.user_id=868' at line 1
– Christopher Gusenbauer
Jan 2 at 22:08
The Doku has a similar example that does it this way: return em.createQuery("SELECT g FROM UserGroup g JOIN UserGroupMembership ugm WHERE ugm.user = :id", UserGroup.class).setParameter("id", em.find(User.class, id)).getResultList(); returns the error Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where usergroupm1_.user_id=868' at line 1
– Christopher Gusenbauer
Jan 2 at 22:08
add a comment |
0
active
oldest
votes
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013375%2fneed-help-translating-a-mysql-db-sql-query-into-a-jpql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54013375%2fneed-help-translating-a-mysql-db-sql-query-into-a-jpql-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Here's the documentation on HQL/JPQL. It has a section on joins. docs.jboss.org/hibernate/orm/current/userguide/html_single/…. Don't try random things. Learn the language by reading the documentation. It's much more effective that way. BTW, Your OneToOne should be ManyToOne, since I guess that there are more than one user per group, and that a user can belong to several groups.
– JB Nizet
Jan 2 at 21:34
should it really be a ManyToOne? Im using this help class to evade having to use FetchType.Eager or a huge join fetch statement as wildflys hibernate configuration seems really tricky there to me to not endlessly load new resources in a loop. i would just have a table with a list of objects with 1 user and 1 usergroup and out of this i could filter my result?
– Christopher Gusenbauer
Jan 2 at 21:59
The Doku has a similar example that does it this way: return em.createQuery("SELECT g FROM UserGroup g JOIN UserGroupMembership ugm WHERE ugm.user = :id", UserGroup.class).setParameter("id", em.find(User.class, id)).getResultList(); returns the error Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where usergroupm1_.user_id=868' at line 1
– Christopher Gusenbauer
Jan 2 at 22:08