0

I am using micronaut in backend and I have a table called transactions in postgres which has about 11 columns in it. I want to make a query to select only 4 columns from it.

We also did a look at this stack overflow link but it does not help in micronaut. Official Docs also does not provide precise answers for the same. We did google lot of stuffs about this and finally solved by doing this:

  1. Created a DTO record for the same

MonthlyTransactionDTO.java

package com.example.transactions.dto;

import java.util.Date;

public record MonthlyTransactionDTO(Long id, Long userId, Date dateOfTransaction, double amount) {
}
  1. Customised the query in repository class

transactionsRepository.java

@Repository
public interface TransactionsRepository extends CrudRepository<Transaction, Long> {
  @Query(value ="select new com.example.transactions.dto.MonthlyTransactionDTO(T.id, T.userId, T.dateOfTransaction, T.amount) from transactions T")
  List<MonthlyTransactionDTO> findMonthlyTransactionsDetails(Long userId);
}

My question is: Is there any other nice way in which we can achieve to select specific columns or a way where I can write my custom SQL query in a clean way?

TBH, I really don't like this kind of approach to select specific columns from Database. Coz everytime when I change my package name I have to change this com.example.transactions.dto. manually coz intelliJ does not auto detect it.

Would be very helpful if you could give me any leads or anything for the same problem. Thanks in advance :)

saw303
  • 8,051
  • 7
  • 50
  • 90
ajinzrathod
  • 925
  • 10
  • 28
  • Why do you need the `@Query` annotation? (see https://micronaut-projects.github.io/micronaut-data/3.3.0/guide/#dto for examples) – Jeff Scott Brown Apr 22 '22 at 13:05
  • @JeffScottBrown I don't get it how should I implement this. And what if I have another situation where I have to select only 3 columns.. I am new to micronaut. So .. – ajinzrathod Apr 22 '22 at 13:10
  • "And what if I have another situation where I have to select only 3 columns." - Is your goal to have a single method that supports all of your situations? – Jeff Scott Brown Apr 22 '22 at 13:38
  • It would be good if single method can do that. But even if it does not, its okay for now – ajinzrathod Apr 22 '22 at 13:40
  • I believe that regardless of whether or not you are using `@Query` you will need separate methods if you have situations that require selecting different subsets of columns. – Jeff Scott Brown Apr 22 '22 at 13:41
  • How can I achieve this? – ajinzrathod Apr 22 '22 at 13:53
  • "How can I achieve this?" - If you mean "How to select only only specific columns in micronaut query", you can have a repository method which returns a DTO and the properties in the DTO dictate which columns are selected from the table(s). – Jeff Scott Brown Apr 22 '22 at 13:58
  • How can I "dictate which columns are selected from the table(s)". If its possible can you please post answer to how to actually code this – ajinzrathod Apr 22 '22 at 14:16
  • "How can I "dictate which columns are selected from the table(s)". The columns selected from the database correspond to properties in the DTO. If you want the column selected, add a corresponding property to the DTO. If you do not want a column selected, do not include a corresponding property in the DTO. I think there are good examples at https://micronaut-projects.github.io/micronaut-data/3.3.0/guide/#dto. That page includes "Micronaut Data will optimize the query to only select the necessary properties from the database.". – Jeff Scott Brown Apr 22 '22 at 15:48

0 Answers0