o.h.engine.jdbc.spi.SqlExceptionHelper:错误:列cliententi0_name不

Question

报告

@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 ppn" +
"INNER JOIN user AS au ON au.id = pp.creator_idn" +
"INNER JOIN services AS ss ON ss.id = pp.service_idn" +
"WHERE pp.created_dt >= '2021-09-28'n" +
"AND ss.name = 'Faberlic' AND pp.status = 'SUCCESS'", nativeQuery = true)
List<Client> getAllByRegDate();
}

Integface

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中；name"；我指定一个现有的表-"；支付"；。所有数据都来自三个表(正如我在Query中所写的那样)。但是当程序运行时，出现一个错误；name"；列不存在。此列在另一个表中，我正在从中获取数据。不知道我该怎么办。

Answer 1

这更像是对这个问题和您在这里提出的问题的回答。我想，你的Client接口被用作投影，然后被转换为ClientDto(为什么？投影已经是DTO了)，你就有了自己的实体，这让事情变得过于复杂。

不执行此操作，只需使用JdbcTemplate和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.amountn" +
"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 ppn" +
"INNER JOIN user AS au ON au.id = pp.creator_idn" +
"INNER JOIN services AS ss ON ss.id = pp.service_idn" +
"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);
}