检索在Spring Boot应用中将JPQL查询作为DTO返回。

huangapple go评论78阅读模式
英文:

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&lt;Foo, Long&gt; {

  Foo findById(Long id);

@Query(value = &quot;SELECT Q1.ACCOUNT_NAME,&quot; +
          &quot;Q2.GROUP_NAME  &quot; +
          &quot;FROM USERS_DEV Q1\n&quot; +
          &quot;JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME\n&quot; +
          &quot;WHERE LOWER(Q1.ACCOUNT_NAME) = &#39;john.pit&#39;&quot;, nativeQuery = true)
  List&lt;Account&gt; 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

你可以使用 EntityManagerResultTransformer

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(
    &quot;SELECT Q1.ACCOUNT_NAME,&quot; +
    &quot;Q2.GROUP_NAME  &quot; +
    &quot;FROM USERS_DEV Q1 &quot; +
    &quot;JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME &quot; +
    &quot;WHERE LOWER(Q1.ACCOUNT_NAME) = &#39;john.pit&#39;&quot;
)
			.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构造函数。

希望这对你有帮助 检索在Spring Boot应用中将JPQL查询作为DTO返回。

英文:

Maybe I'm wrong, but i remember you can do the follow (with HQL):

@Query(&quot;select new Account(u.name, g.groupName) &quot; +
          &quot;from User as u&quot; +
          &quot;join u.group as g&quot; +
          &quot;where LOWER(u.name) = &#39;john.pit&#39;&quot;)

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 检索在Spring Boot应用中将JPQL查询作为DTO返回。

答案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 = &quot;findAllAccounts&quot;,
  query = 
     &quot;SELECT &quot; +
     &quot;  Q1.ACCOUNT_NAME AS accountName, &quot; +
     &quot;  Q2.GROUP_NAME AS groupName &quot; +
     &quot;FROM USERS_DEV Q1 &quot; +
     &quot;JOIN USERS_GROUPS Q2 ON Q1.ACCOUNT_NAME = Q2.ACCOUNT_NAME &quot; + 
     &quot;WHERE LOWER(Q1.ACCOUNT_NAME) = &#39;john.pit&#39;&quot;,
  resultSetMapping = &quot;findAllAccountsMapping&quot;
)
@SqlResultSetMapping(
   name = &quot;findAllAccountsMapping&quot;,
   classes = @ConstructorResult(
      targetClass = Account.class,
      columns = {
         @ColumnResult(name=&quot;accountName&quot;),
         @ColumnResult(name=&quot;groupName&quot;),
      }
   )
)
public class Foo {
   // ...
}

and FooRepository:

@Repository
public interface FooRepository extends JpaRepository&lt;Foo, Long&gt; {

  @Query(name = &quot;findAllAccounts&quot;, nativeQuery = true)
  List&lt;Account&gt; getAllAccounts();
}

See hibernate documentation for additional details.

huangapple
  • 本文由 发表于 2020年10月6日 20:06:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/64225477.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定