I am new to spring boot and i am creating a spring boot app to generate csv files from data fetched from database .I'm using h2 database for it and want to get selective columns from my entity-id,amount

entity class:

package com.reports.entities;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity(name="reportDetails")
@Table(name = "reports")
public class Report {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name="id")
    private long id;
    @Column(name="name")
    private String name;
    @Column(name="email")
    private String email;
    @Column(name="amount")
    private int amount;
    
    public Report() {
        super();
    }
    public Report(int id, String name, String email, int amount) {
        super();
        this.id = id;
        this.name = name;
        this.email = email;
        this.amount = amount;
    }
    public long getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
    public int getAmount() {
        return amount;
    }
    public void setAmount(int amount) {
        this.amount = amount;
    }
    
    
    }

main class:

package com.reports;

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

import com.reports.entities.Report;
import com.reports.repository.ReportsRepository;

@SpringBootApplication
public class ExportCsvApplication implements CommandLineRunner {
    @Autowired
    ReportsRepository reportsRepository;

    public static void main(String[] args) {
        SpringApplication.run(ExportCsvApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        List<Report> reports = new ArrayList<>();

        // create dummy employees
        reports.add(new Report(1,"roy","[email protected]",2500));
        reports.add(new Report(2,"joy","[email protected]",2500));
        reports.add(new Report(3,"soy","[email protected]",2500));
        reports.add(new Report(4,"moy","[email protected]",2500));
        reports.add(new Report(5,"noy","[email protected]",2500));
        
        reportsRepository.saveAll(reports);
    }

}

repository :

package com.reports.repository;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import com.reports.entities.IReport;
import com.reports.entities.Report;

@Repository("reportsRepository")
public interface ReportsRepository extends JpaRepository<Report,Long>{

}

Service class:

package com.reports.services;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.reports.entities.IReport;
import com.reports.entities.Report;
import com.reports.repository.ReportsRepository;
@Transactional
@Service
public class ReportsService {
    @Autowired
   ReportsRepository reportsRepository;

    public List<Report> fetchAll() {
        return (List<Report>) reportsRepository.findAll();
       

    }

    
}

Controller:

package com.reports.controllers;

import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import com.opencsv.CSVWriter;
import com.opencsv.bean.StatefulBeanToCsv;
import com.opencsv.bean.StatefulBeanToCsvBuilder;
import com.reports.entities.Report;
import com.reports.services.ReportsService;

@RestController
public class ReportsController {

    @Autowired
    ReportsService reportsService;
    @GetMapping("/export-report")
    public void exportCSV(HttpServletResponse response) throws Exception {

        // set file name and content type
        String filename = "details.csv";

        response.setContentType("text/csv");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, 
                   "attachment; filename=\""   filename   "\"");

        // create a csv writer
        StatefulBeanToCsv<Report> writer = new StatefulBeanToCsvBuilder<Report>(response.getWriter()).withQuotechar(CSVWriter.NO_QUOTE_CHARACTER).withSeparator(CSVWriter.DEFAULT_SEPARATOR).withOrderedResults(false).build();

        // write all employees to csv file
        writer.write(reportsService.fetchAll());

    }
    
    
    
   
}

I want to know what would be the best method to incorporate for it i tried query but faced errors.Please let me know how do i get this done

CodePudding user response：

This is more of being related to the opencsv library than Spring or Spring Boot itself.

When constructing the StatefullBeanToCsvBuilder, you should use the withIgonreField builder property to instruct the final StatefullBeanToCsv on which fields to ignore for which types.

Say you want only the id and amount fields out of your Report entity, you can achieve this as follows:

@RestController
public class ReportsController {

    @Autowired
    ReportsService reportsService;

    @GetMapping("/export-report")
    public void exportCSV(HttpServletResponse response) throws Exception {

        // set file name and content type
        String filename = "details.csv";
        response.setContentType("text/csv");
        response.setHeader(HttpHeaders.CONTENT_DISPOSITION, 
                   "attachment; filename=\""   filename   "\"");

        // Configure the CSV writer builder
        StatefulBeanToCsvBuilder<Report> builder = new StatefulBeanToCsvBuilder<Report>(response.getWriter()).withQuotechar(CSVWriter.NO_QUOTE_CHARACTER).withSeparator(CSVWriter.DEFAULT_SEPARATOR).withOrderedResults(false);

        // Ignore any field except the `id` and `amount` ones
        Arrays.stream(Report.class.getDeclaredFields())
                .filter(field -> !("id".equals(field.getName()) || "amount".equals(field.getName())))
                .forEach(field -> builder.withIgnoreField(Report.class, field));

        // create a csv writer
        StatefulBeanToCsv<Report> writer = builder.build();

        // write all employees to csv file
        writer.write(reportsService.fetchAll());

    }  
}