将查询结果的总和作为单独的数据

Question

Etity公司

@Entity
public class DateFMail {

    @Id
    
    private double balance;

    public DateFMail() {
    }

    public DateFMail(double balance) {this.balance = balance;}

    public DateFMail(DateFMail dateFMail) {
    }

    public double getBalance() { return balance;}

    @Override
    public String toString() {
        return "DateFMail{" +
                "balance=" + balance +
                '}';
    }
}

服务

public interface DateFMailService {
    List<DateFMail> findAll();
}

Impl

@服务公共类DateFMailServiceImpl实现DateFMailService{

@Autowired
private DateFMailRepository mailRepository;

@Override
public List<DateFMail> findAll() {
    return mailRepository.findAll();
}

}

存储库

@Repository公共接口DateFMailRepository扩展JpaRepository

@Query(value = "SELECT SUM(balance) \n" +
        "      FROM agents", nativeQuery = true)
List<DateFMail> findAll();

}

邮件分离器

@Service
public class EmailDos {

    @Autowired
    private JavaMailSender mailSender;

     private DateFMailRepository mailRepository;

    String fileDate1 = new SimpleDateFormat("dd.MM.yyyy").format(new Date());

    LocalDate today = LocalDate.now();
    String fileDate = (today.minusDays(1)).format(DateTimeFormatter.ofPattern("dd MMM"));
    String fileDate2 = (today.minusMonths(1)).format(DateTimeFormatter.ofPattern("MMM"));

    public void sendMailSum(String from, String to, String subject, String body, String fileToAttach) throws SQLException {

            List<DateFMail> list = new ArrayList<>(mailRepository.findAll());
            List<DateFMail> list1 = list.stream()
                    .map(DateFMail::new)
                    .collect(Collectors.toList());
       
        System.out.println("sending email...................");
        System.out.println(list1);
        MimeMessagePreparator preparator = new MimeMessagePreparator() {
            public void prepare(MimeMessage mimeMessage) throws Exception {
                mimeMessage.setFrom(new InternetAddress(from));
                mimeMessage.setRecipient(Message.RecipientType.TO, new InternetAddress(to));
                mimeMessage.setSubject(subject);
                mimeMessage.setText(body);

                FileSystemResource file = new FileSystemResource(new File("C:...xlsx"));
                MimeMessageHelper helper = new MimeMessageHelper(mimeMessage, true);
                helper.setFrom("[e-mail]");
                helper.setTo(InternetAddress.parse("[e-mail]"));  
                helper.setText("Good day!\nIn attachment payments for " + fileDate + " с 12.00-00.00" + "\nAmount for " + fileDate1 + list1);

                helper.addAttachment("...xlsx", file);

                mailSender.send(mimeMessage);
                System.out.println("email Fab was successfully sent.....");
            }
        };

        try {
            mailSender.send(preparator);
        } catch (MailException ex) {
            System.err.println(ex.getMessage());
        }
    }
}

控制器

@Component
public class DateFMailController {

    @Autowired
    private DateFMailService mailService;

    public void saveSum() throws IOException {
        saveExcel(mailService.findAll(), "....xlsx");
    }

    private void saveExcel(List<DateFMail> list, String fileName) throws IOException {

        Workbook workbook = new XSSFWorkbook();
        CreationHelper createHelper = workbook.getCreationHelper();

        Sheet sheet = workbook.createSheet("ECards");
        sheet.autoSizeColumn(0);

        Row header = sheet.createRow(0);

        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        XSSFFont font = ((XSSFWorkbook) workbook).createFont();
        font.setFontName("Arial");
        font.setFontHeightInPoints((short) 10);
        font.setBold(true);
        headerStyle.setFont(font);

        Cell headerCell = header.createCell(0);
        headerCell.setCellValue("Sum");
        headerCell.setCellStyle(headerStyle);

        CellStyle style = workbook.createCellStyle();
        style.setWrapText(true);

        int ix_row=2;
        for (DateFMail dateFMail : list) {
            Row row = sheet.createRow(ix_row);
            Cell cell = row.createCell(0);
            cell.setCellValue(dateFMail.getBalance());
            cell.setCellStyle(style);

            ix_row++;
        }

        FileOutputStream outputStream = new FileOutputStream(fileName);
        workbook.write(outputStream);
        workbook.close();
    }
}

保存运行程序

@Component
public class SaveCardsStartupRunner implements ApplicationRunner {

    @Autowired
    private ECardController eCardController;
    private DateFMailController controller;
  

//    @Autowired
//    private EmailDos emailDos;

    String fileDate1 = new SimpleDateFormat("dd.MM.yyyy").format(new Date());

    LocalDate today = LocalDate.now();
    String fileDate = (today.minusDays(1)).format(DateTimeFormatter.ofPattern("dd MMM"));
    String fileDate2 = (today.minusMonths(1)).format(DateTimeFormatter.ofPattern("MMM"));

    @Override
    public void run(ApplicationArguments args) throws Exception {
        eCardController.saveCards();
        controller.saveSum();
    }
}

我已经纠正了我的问题。我已经在这里粘贴了所有与我的问题相关的代码。对于初学者，我只想将存储库的查询结果输出到控制台。但在我刚刚发布的表单中，我得到了一个NullPointerException错误，并在代码的一部分中指出：controller。saveSum（）；-控制器=空。

Answer 1

创建PaymentService类，该类应包含方法getTotalPayment。将此类注入EmailSend（提示：请将此类名从EmailSend更改为EmailSender，因为类名应为名词）类。然后在PaymentService类中，您应该与数据存储库类交互。从EmailSend类调用此getTotalPayment方法。