英文:
Retrieve JPQL Query as DTO in Spring boot application
问题
以下是您要翻译的内容:
基本上我要做的是运行一个查询,并将结果放在Account
类的对象中。非常重要的是要强调的是,查询在两个未映射为@Entity
的表之间执行了JOIN操作。那么我该如何做呢?
这是我的 FooRepository.java 类
@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
Foo findById(Long id);
@Query(value = "SELECT Q1.ACCOUNT_NAME," +
"Q2.GROUP_NAME " +
"FROM USERS_DEV Q1\n" +
"JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME\n" +
"WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'", nativeQuery = true)
List<Account> getAllAccounts();
那么我该如何修改我的查询以获得所需的结果呢?
这是我的 Account.java 类
public class Account {
String samAccountName;
String groupName;
public Account(String accountName, String groupName) {
this.accountName = accountName;
this.groupName = groupName;
}
public String getAccountName() {
return accountName;
}
public void setAccountName(String accountName) {
this.accountName = accountName;
}
public String getGroupName() {
return groupName;
}
public void setGroupName(String groupName) {
this.groupName = groupName;
}
}
英文:
What I am basically trying to do is to run a query and put the result within the object of the class Account
. What is very important to emphasize is that the query performs a JOIN between 2 tables THAT ARE NOT MAPPED as @Entity
. So how can I do that?
This is my FooRepository.java class
@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
Foo findById(Long id);
@Query(value = "SELECT Q1.ACCOUNT_NAME," +
"Q2.GROUP_NAME " +
"FROM USERS_DEV Q1\n" +
"JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME\n" +
"WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'", nativeQuery = true)
List<Account> getAllAccounts();
So how can I change my query to get the desired result?
And here is my Account.java class
public class Account {
String samAccountName;
String groupName;
public Account(String accountName, String groupName) {
this.accountName = accountName;
this.groupName = groupName;
}
public String getAccountName() {
return accountName;
}
public void setAccountName(String accountName) {
this.accountName = accountName;
}
public String getGroupName() {
return groupName;
}
public void setGroupName(String groupName) {
this.groupName = groupName;
}
}
答案1
得分: 1
你可以使用 EntityManager
和 ResultTransformer
:
entityManager.createNativeQuery(
"SELECT Q1.ACCOUNT_NAME," +
"Q2.GROUP_NAME " +
"FROM USERS_DEV Q1 " +
"JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME " +
"WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'"
)
.unwrap(NativeQuery.class)
.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
return new Account(
(String) tuple[0],
(String) tuple[1]
);
}
@Override
public List transformList(List collection) {
return collection;
}
})
.getResultList();
可能需要进行类型转换。
英文:
You can use EntityManager
and ResultTransformer
:
<!-- language: java -->
entityManager.createNativeQuery(
"SELECT Q1.ACCOUNT_NAME," +
"Q2.GROUP_NAME " +
"FROM USERS_DEV Q1 " +
"JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME " +
"WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'"
)
.unwrap(NativeQuery.class)
.setResultTransformer(new ResultTransformer() {
@Override
public Object transformTuple(Object[] tuple, String[] aliases) {
return new Account(
(String) tuple[0],
(String) tuple[1]
);
}
@Override
public List transformList(List collection) {
return collection;
}
})
.getResultList();
A cast might be needed.
答案2
得分: 1
也许我记错了,但我记得你可以像以下方式(使用HQL)进行操作:
@Query("select new Account(u.name, g.groupName) " +
"from User as u " +
"join u.group as g " +
"where LOWER(u.name) = 'john.pit'")
语法可能有误,但我确信如果你使用HQL,可以在选择语句中使用DTO构造函数。
希望这对你有帮助
英文:
Maybe I'm wrong, but i remember you can do the follow (with HQL):
@Query("select new Account(u.name, g.groupName) " +
"from User as u" +
"join u.group as g" +
"where LOWER(u.name) = 'john.pit'")
Syntax could be wrong, but I'm sure you can use DTO constructor in select statement if you use HQL.
Hope this will helps you
答案3
得分: 0
作为另一种选择,您可以按以下方式使用@NamedNativeQuery
和@SqlResultSetMapping
:
@Entity
@NamedNativeQuery(
name = "findAllAccounts",
query =
"SELECT " +
" Q1.ACCOUNT_NAME AS accountName, " +
" Q2.GROUP_NAME AS groupName " +
"FROM USERS_DEV Q1 " +
"JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME " +
"WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'",
resultSetMapping = "findAllAccountsMapping"
)
@SqlResultSetMapping(
name = "findAllAccountsMapping",
classes = @ConstructorResult(
targetClass = Account.class,
columns = {
@ColumnResult(name="accountName"),
@ColumnResult(name="groupName"),
}
)
)
public class Foo {
// ...
}
以及 FooRepository
:
@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
@Query(name = "findAllAccounts", nativeQuery = true)
List<Account> getAllAccounts();
}
有关更多详细信息,请参阅 Hibernate 的 文档。
英文:
As an alternative you can use @NamedNativeQuery
with @SqlResultSetMapping
in the following way:
@Entity
@NamedNativeQuery(
name = "findAllAccounts",
query =
"SELECT " +
" Q1.ACCOUNT_NAME AS accountName, " +
" Q2.GROUP_NAME AS groupName " +
"FROM USERS_DEV Q1 " +
"JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME " +
"WHERE LOWER(Q1.ACCOUNT_NAME) = 'john.pit'",
resultSetMapping = "findAllAccountsMapping"
)
@SqlResultSetMapping(
name = "findAllAccountsMapping",
classes = @ConstructorResult(
targetClass = Account.class,
columns = {
@ColumnResult(name="accountName"),
@ColumnResult(name="groupName"),
}
)
)
public class Foo {
// ...
}
and FooRepository
:
@Repository
public interface FooRepository extends JpaRepository<Foo, Long> {
@Query(name = "findAllAccounts", nativeQuery = true)
List<Account> getAllAccounts();
}
See hibernate documentation for additional details.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论