how to set the findAll() on springboot to only return one column of it's foreign key's tab-CodePudding

I'm working on a database with multiple relations, how can I return only the table I need and some of its columns instead of what findAll() returns which is the table the related table and tables related to that one and so on.

What I'm getting is something along the lines of:

{
    "id":1 ,
    "title": "foo",
    "writer": {
       "id":6,
       "name": "name",
       "group": {
         "id":3,
         "name":"group"
       }
    }
}

how can i get something like this:

{
  "id":1 ,
  "title": "foo",
  "writer": {
    "name": "name",
   }
}

Should I use jpql? Otherwise, what's the best approach.

CodePudding user response：

One possibility would be relying on JPQL for this. Assuming that your entity class is called Article it would be something in the line of:

@Query("select article.id, article.title from Article article")
List<Article> getAllArticlesOptimized();

You can read more about this at https://www.appsdeveloperblog.com/specific-columns-jpa-native-query/.

Another possibility would be Projections so that you can selectively retrieve partial views of your entities. You would declare an interface that exposes accessor methods for the properties to be read:

interface ArticleSummary {

  Long getId();
  String getTitle();
  WriterSummary getWriter();

  interface WriterSummary {
    String getName();
  }
}

And you would use it as the return type in your repository:

interface ArticleRepository extends Repository<Article, Long> {
  List<ArticleSummary> findAll();
}

CodePudding user response：

You can use native-query and ResultTransformer:


    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Optional<ArticleDTO> getArticle(final long id) {
        final Session sess = entityManager.unwrap(Session.class);
        return sess.createNativeQuery(
            "select a.id, a.title, w.name from Article a left outer join Writer w on a.writer_id = w.id where a.id = :id")
            .setParameter("id", id)
            .addScalar("id", LongType.INSTANCE)
            .addScalar("name", StringType.INSTANCE)
            .addScalar("title", StringType.INSTANCE)
            .setResultTransformer(new ArticleResultTransformer())
            .uniqueResultOptional();
    }

    public static class ArticleResultTransformer extends BasicTransformerAdapter {
        @Override
        public Object transformTuple(final Object[] tuple, final String[] aliases) {
            final Optional<String> writerName = Optional.ofNullable((String) tuple[2]);
            final WriterDTO writer = writerName.map(name -> new WriterDTO(name)).orElse(null);

            return new ArticleDTO((Long) tuple[0], (String) tuple[1], writer);
        }
    }