时间戳

首页 > 解决方案 > ohengine.jdbc.spi.SqlExceptionHelper: 错误: 列 cliententi0_.name 不存在

java - ohengine.jdbc.spi.SqlExceptionHelper: 错误: 列 cliententi0_.name 不存在

问题描述

存储库

@Repository
public interface ClientRepository extends JpaRepository<ClientEntity, Long> {

    @Modifying
    @Transactional
    @Query(value = "SELECT pp.id, TO_CHAR(pp.created_dt::date, 'dd.mm.yyyy')\n" +
               "AS 'Data', CAST(pp.created_dt AS time(0)) AS 'Time', au.username AS 'UserName',\n" +
               "ss.name AS 'Service', pp.amount AS 'Amount',\n" +
               "REPLACE(pp.status, 'SUCCESS', 'Success') AS 'Payment_status', pp.account AS 'Account',\n" +
               "pp.external_id AS 'Idn', COALESCE(pp.external_status, null, 'DN')\n" +
               "AS 'Stat'\n" +
               "FROM payments AS pp\n" +
               "INNER JOIN 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 >= '2021-09-28'\n" +
               "AND ss.name = 'Faberlic' AND pp.status = 'SUCCESS'", nativeQuery = true)
    List<Client> getAllByRegDate();
}

接口

public interface Client {

    Long getId();
    @JsonFormat(shape = JsonFormat.Shape.STRING)
    LocalDate getCreated_dt();
    String getUsername();
    String getName();
    int getAmount();
    String getStatus();
    String getAccount();
    String getExternal_id();
    String getExternal_status();
}

DTO

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString

public class ClientDto {
    private Long id;
    @JsonFormat(shape = JsonFormat.Shape.STRING)
    private LocalDate created_dt;
    private String username;
    private String name;
    private int amount;
    private String status;
    private String account;
    private String external_id;
    private String external_status;

    public ClientDto(Client client) {
        this.id = client.getId();
        /...
        /...
        this.external_status = client.getExternal_status();
    }

    public ClientDto(ClientDto clientDto) {
        this.id = clientDto.getId();
        
        /...

        this.external_status = clientDto.getExternal_status();
    }

    public ClientDto(ClientEntity clientEntity) {
    }

    @Override
    public String toString() {
        return "" + id + "|" + created_dt + "|" + username + "|" + name +
                "|" + amount + "|" + status + "|" + account + "|" + external_id + "|" + external_status;
    }
}

实体

@Getter
@NoArgsConstructor
@AllArgsConstructor
@Immutable
@Entity
@Table(name = "payments", schema = "public")
public class ClientEntity {

    @Id
    private Long id;

    @Column(name = "created_dt")
    private LocalDate created_dt;

    @Column(name = "username")
    private String username;

    @Column(name = "name")
    private String name;

    @Column(name = "amount")
    private int amount;

    @Column(name = "status")
    private String status;

    @Column(name = "account")
    private String account;

    @Column(name = "external_id")
    private String external_id;

    @Column(name = "external_status")
    private String external_status;
}

我正在尝试将数据保存到 csv 文件。我从一个数据库、三个表中获取数据。在“名称”中的实体 @Table 中，我指定了现有表之一 - “付款”。所有数据都取自三个表（正如我在 Query 中所写的那样）。但是当程序运行时，会出现“名称”列不存在的错误。此列位于我从中获取数据的另一个表中。无法弄清楚我应该做什么。

标签： javaspringpostgresqlcsv

解决方案

这更多地是对这个问题和你在这里提出的问题的回答，结合起来。Client恕我直言，您使用具有用作投影的接口的结构使事情变得过于复杂，然后将其转换为ClientDto（为什么？投影已经是DTO）并且您拥有实体。

而不是这样做，只需使用 aJdbcTemplate将RowCallbackHandler行写入 CSV。这将使用更少的内存，更快（因为您不会每行创建多个对象然后将其丢弃，并且您没有内存中的所有行）。

import java.io.FileWriter;
import java.sql.ResultSet;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

@Component
public class SchedulerService {

    private static final String QUERY = "SELECT pp.id, pp.created_dt au.username, ss.name, pp.amount\n" +
        "REPLACE(pp.status, 'SUCCESS', 'Success'), pp.account,\n" +
        "pp.external_id AS 'Idn', COALESCE(pp.external_status, null, 'DN') AS 'Stat'\n" +
        "FROM payments AS pp\n" +
        "INNER JOIN 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 >= '2021-09-28'\n" +
        "AND ss.name = 'Faberlic' AND pp.status = 'SUCCESS'";

    private static final DateTimeFormatter date_format = DateTimeFormatter.ofPattern("dd.MM.yyyy");
    private static final DateTimeFormatter time_format = DateTimeFormatter.ofPattern("HH:mm:ss");

    private final JdbcTemplate jdbc;

    public SchedulerService(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    @Scheduled(fixedRate = 5000)
    public void downloadBlockedClients() {
        String filename = "select.csv";

        try (FileWriter writer = new FileWriter(filename)) {
            writer.append("id|date|time|username|name|amount|status|account|external_id|external_status").append('\n');
            this.jdbc.query(QUERY, (ResultSet rs) -> writeLine(writer, rs));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void writeLine(FileWriter writer, ResultSet rs) {
        try {
            LocalDateTime ldt = rs.getTimestamp("created_dt").toLocalDateTime();
            writer.append(String.valueOf(rs.getLong("id")));
            writer.append('|');
            writer.append(ldt.format(date_format));
            writer.append('|');
            writer.append(ldt.format(time_format));
            writer.append('|');
            writer.append(rs.getString("username"));
            writer.append('|');
            writer.append(rs.getString("name"));
            writer.append('|');
            writer.append(String.valueOf(rs.getBigDecimal("amount")));
            writer.append('|');
            writer.append(rs.getString("status"));
            writer.append('|');
            writer.append(rs.getString("account"));
            writer.append('|');
            writer.append(rs.getString("idn"));
            writer.append('|');
            writer.append(rs.getString("stat"));
            writer.append('\n');
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }
}

这些方面的东西将使您的资源更有效（保存复制，在内存中复制结果）并且应该更快。您可以将行处理移至一个方法，以便您的 lambda 变得更具可读性。

注意：我假设您使用的是 Spring Boot，并且 `JdbcTemplate 是开箱即用的。如果没有，您需要在 JPA 配置旁边配置一个。

@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
  return new JdbcTemplate(dataSource);
}

推荐阅读