将存储库中的查询请求保存到EXCEL文件

Question

@Repository
public interface UserRepository extends JpaRepository<User, Integer> {
    @Modifying
    @Transactional
    @Query(value = "SELECT pp.id, pp.created_dt, au.username, ss.name, pp.amount,\n" +
            "REPLACE(pp.status, 'SUCCESS', 'Success') AS status, pp.account,\n" +
            "pp.external_id, COALESCE(pp.external_status, null, 'Indefined') AS external_status\n" +
            "FROM payments AS pp\n" +
            "INNER JOIN auth_user AS au ON au.id = pp.creator_id\n" +
            "INNER JOIN services AS ss ON ss.id = pp.service_id\n" +
            "WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date\n" +
            "AND ss.name = 'SomeName' AND pp.status = 'SUCCESS'", nativeQuery = true)
    List<User> findAll();
}

Answer 1

我将研究apache poi库：https://www.codejava.net/coding/how-to-write-excel-files-in-java-using-apache-poi

您可以使用以下maven依赖项来使用这些库：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>3.17</version>
    </dependency>

您可以使用时态文件存储数据并将其作为报告发送，只需记住之后删除该文件。