java - 严重: There is no column named: "PAYMENTDATE "

Question

我使用 NetBeans 来制作我的 Java 项目。在我的数据库表或 DTO 中，它们都有一个名为“paymentDate ”的列。我不明白为什么它会发送此错误。奇怪的是，当我将“PAYMENTDATE”更改为“DATE”时，系统会向我发送另一个错误:严重:没有名为“CLIENTORDERID ”的列。

客户订单代码:

package dbase;
import dto.ClientDTO;
import dto.clientOrderDTO;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

public class ClientOrder {

    private static final String PERSON_ORDER = "SELECT clientName, orderName, paymentDate AS Date " 
                                          +  "FROM Client JOIN clientOrder ON Client.id = clientOrder.clientId " 
                                          +  "WHERE Client.id = ? ";

    public ArrayList<clientOrderDTO> findCurrentClientOrderForClient(ClientDTO client) throws SQLException{ 
        ArrayList<clientOrderDTO> list = new ArrayList<>();
        Connection con = DB_Manager.getConnection();
        try{ 
            PreparedStatement stmt = con.prepareStatement(PERSON_ORDER);
            stmt.setInt(1, client.getId());
            ResultSet rs = stmt.executeQuery();

            while(rs.next()){

                Calendar paymentDate = Calendar.getInstance(); 
                paymentDate.setTime(rs.getDate("PAYMENTDATE"));

                clientOrderDTO order = new clientOrderDTO(    
                                rs.getInt("CLIENTORDERID"),
                                client,
                                rs.getString("ORDERNAME"),
                                paymentDate
                );

                list.add(order);
             }           
            con.close();  
        }   
        catch (NullPointerException npe)
        {
            System.err.println("No connection available");
        }
        catch (SQLException sqle)
        {
            System.err.println(sqle.getMessage());
        }
            return list;
    }
}

创建表

CREATE TABLE Client(
    id integer not null primary key generated always as identity (start with 1, increment by 1),
    clientName varchar(20) not null,
    username varchar(10) not null unique,
    pwd varchar(50) not null
);



CREATE TABLE clientOrder(
    id integer not null primary key generated always as identity (start with 1, increment by 1),
    clientId integer not null,
    orderName varchar(20) not null,
    paymentDate Date not null,
    constraint client_FK foreign key(clientId) references Client(id)
);

DTO:

package dto;

import java.io.Serializable;

public class ClientDTO implements Serializable
{
    private final int id;
    private final String name;
    private final String username;
    private final String password;

    public ClientDTO(int id, String name, String username, String password){
        this.id = id;
        this.name = name;
        this.username = username;
        this.password = password;
    }

    public int getId(){
        return id;
    }

    public String getName(){
        return name;
    }

    public String getUsername(){
        return username;
    }

    public boolean passwordMatches(String pwd){
        return password.equals(pwd);
    }


}

package dto;

import java.io.Serializable;
import java.util.Calendar;

import javax.inject.Named;

@Named(value = "clientOrder")

public class clientOrderDTO implements Serializable{
    private final int id;
    private final ClientDTO client;
    private final String orderName;
    private Calendar paymentDate;

    public clientOrderDTO(int id, ClientDTO client, String orderName,Calendar paymentDate){
        this.id = id;
        this.client = client;
        this.orderName = orderName;
        this.paymentDate = paymentDate;
    }

    public int getId(){
        return id;
    }

    public ClientDTO getClient(){
        return client;
    }

    public String getOrderName(){
        return orderName;
    }

    public Calendar getPaymentDate(){
        return paymentDate;
    }

    public void setPatmentDate(Calendar paymentDate){
        this.paymentDate = paymentDate;
    }

}

Answer 1

在您的查询中，您已将 paymentDate 列别名为 Date :

paymentDate AS Date

因此您应该使用的列名称是Date。
此外，SELECT 列表中也没有名称或别名 CLIENTORDERID 的列。如果您想使用列的值，则该列必须出现在 SELECT 列表中，以便查询返回它。