java - 字段列表中的列不明确

Question

表预订:

我想向客人提供他必须支付的金额，并且我有一个程序

CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateTotal`(IN ID int ,OUT total float)
BEGIN
SELECT (DATEDIFF(reservation.endDate, reservation.startDate) * room.price)
INTO total
FROM (select * from guest Where guestID = ID) As R , Room, Reservation
WHERE Room.roomNumber = Reservation.roomNumber AND Reservation.guestID =
R.guestID;
END

System.out.println("Enter your ID guest:");
int gID = keyboard.nextInt();
rs = stmt.executeQuery("Select guestID from reservation where guestID = "+gID+";");
if(rs.next())
   {
   stmt.executeQuery("call calculateTotal(" + gID + ", @result);");
   rs = stmt.executeQuery("Select firstName, lastName, roomNumber, class, price, startDate, endDate,  @result as TotalPrice From (Guest join (Reservation join Room on (Reservation.roomNumber = Room.roomNumber)) on Reservation.guestID = Guest.guestID ) where reservation.guestID = "+gID+";");
   while(rs.next())
       System.out.println("Name: "+ rs.getString("firstName")+" "+ rs.getString("lastName")+ "  Room Number:"+ rs.getInt("roomNumber") + "  Class:"+ rs.getString("class")+ "  Price:"+ rs.getFloat("price")+ rs.getDate("startDate")+"  EndDate: "+ rs.getDate("endDate")+ "  Total Price: "+ rs.getFloat("TotalPrice") + "р.");
   }

结束我有一个错误

java.sql.SQLIntegrityConstraintViolationException: Column 'roomNumber' in field list is ambiguous
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:117)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
    at com.jdbc.Main.main(Main.java:172)

172 raw 是 rs = stmt.executeQuery("选择名字，姓氏...

可能会以某种方式改进程序和代码吗？

Answer 1

正如 @Abra 和 @Eric Brandt 指出的那样，异常(exception)情况是您正在查询多个表，并且您需要在字段前添加表名或别名，以防止引用不明确的列(例如列在不同的表中具有相同的名称)

既然您希望改进代码，我会提出一些建议。

正如@Strawberry 所建议的，如果最终目标只是查询预订，则不需要该过程。虽然您可能打算扩展该过程的功能，但恕我直言，通常最好避免使用它们来进行可以在普通 SQL 中完成的简单计算、聚合等。

在使用用户输入时将字符串连接在一起形成 SQL 可能很危险。在这种情况下是安全的，因为 keyboard.nextInt()可以防止 SQL 注入(inject)攻击，但您可能会考虑使用准备好的语句来获得额外的保护级别，准备好的语句还可以具有其他优点。例如，某些数据库会缓存运行计划，这在多次运行更复杂的查询时可能很重要。

您可能总是希望在连接中使用“on”子句。否则您将得到交叉连接，这可能不是您想要的。

一旦您超越了简单的示例，在 Java 代码中嵌入 SQL 可能会变得乏味，将其外部化为资源可能非常有用，并且可以设置 SQL 模板，或者使用变体来处理不同数据库之间的怪癖(例如， MySQL、Oracle、Postgres 等)。

关于自然键与代理键，或者 lowerCamel 列与 lower Snake case 列，何时使用或避免存储过程等，有很多观点，但不是尝试详尽的“最佳实践” “列表，无论如何，这只是我的意见，这是一个与原始示例相差不远的替代示例(即忽略 ORM 等)

import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class Hotel {

    private static Connection connection;
    private static ResultSet rs;
    private static Scanner keyboard;
    private static Statement stmt;

    public static void main(String[] args) throws Exception {
        if (args.length != 3) {
            // Note: Don't pass passwords on the command line in real life.
            System.err.println("Usage: java Hotel <dbUrl> <dbUser> <dbPwd>");
            return;
        }
        // Initial set up
        keyboard = new Scanner(System.in);
        connection = DriverManager.getConnection(args[0], args[1], args[2]);
        stmt = connection.createStatement();
        // Do it the original way (from the post).
        // orig();
        // Do it a slightly different way.
        anotherWay();
    }

    public static void orig() throws Exception {
        System.out.println("(orig) Enter your ID guest:");
        int gID = keyboard.nextInt();
        rs = stmt.executeQuery("Select guestID from Reservation where guestId = " + gID + ";");
        if (rs.next()) {
            stmt.executeQuery("call calculateTotal(" + gID + ", @result);");
            rs = stmt.executeQuery(
                    "Select firstName, lastName, Reservation.roomId as roomNumber, class, price, startDate, endDate,  @result as TotalPrice From (Guest join (Reservation join Room on (Reservation.roomId = Room.id)) on Reservation.guestID = Guest.id ) where Reservation.guestID = "
                            + gID + ";");
            while (rs.next()) 
                System.out.println("  Name: " + rs.getString("firstName") + " " + rs.getString("lastName")
                + "\n  Room Number: " + rs.getInt("roomNumber") + "\n  Class: " + rs.getString("class")
                + "\n  Price: " + String.format("%.2f", rs.getFloat("price")) + "\n  Start Date: "
                + rs.getDate("startDate") + "\n  EndDate: " + rs.getDate("endDate") + "\n  Total Price: "
                + String.format("%.2f", rs.getFloat("TotalPrice")));

        }
    }

    public static void anotherWay() throws Exception {
        System.out.println("(anotherWay) Enter your guest ID:");
        int guestId = keyboard.nextInt();
        // Get the SQL as a String.  Normally this would be standardized into
        // a utility, so it wouldn't be so messy.
        String sql = new String(
                Files.readAllBytes(Paths.get(Hotel.class.getResource("select_reservation.sql").toURI())));
        // Prepare the statement
        PreparedStatement ps = connection.prepareStatement(sql);

        // Set the parameter
        ps.setInt(1, guestId);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            printResult(rs);
        }
        rs.close();
        ps.close();
    }

    public static void printResult(ResultSet rs) throws Exception {
        System.out.println(
                "  Name: " + rs.getString("firstName") + " " + rs.getString("lastName") 
                + "\n  Room Number: "   + rs.getInt("roomNumber") 
                + "\n  Class: " + rs.getString("class") 
                + "\n  Price: " + String.format("%.2f", rs.getFloat("price")) 
                + "\n  Start Date: " + rs.getDate("startDate")
                + "\n  EndDate: " + rs.getDate("endDate") 
                + "\n  Room Charge: " + String.format("%.2f", rs.getFloat("roomCharge"))
                + "\n  Extra: " + String.format("%.2f", rs.getFloat("extra"))
                + "\n  Total Price: " + String.format("%.2f", rs.getFloat("totalPrice"))
                );
    }
}

像这样运行它(现实生活中不要在命令行上使用密码)...

java Hotel <url> <user> <pwd>

访客 1 的输出应如下所示...

(anotherWay) Enter your guest ID:
1
  Name: Alice Smith
  Room Number: 1
  Class: premium
  Price: 100.00
  Start Date: 2020-05-15
  EndDate: 2020-05-22
  Room Charge: 700.00
  Extra: 350.00
  Total Price: 1050.00

这个是给客人 2 的...

(anotherWay) Enter your guest ID:
2
  Name: Bob Jones
  Room Number: 2
  Class: base
  Price: 75.00
  Start Date: 2020-05-15
  EndDate: 2020-05-22
  Room Charge: 525.00
  Extra: 0.00
  Total Price: 525.00 

这是外部化的 SQL...

select
    Guest.firstName,
    Guest.lastName,
    Room.id as roomNumber,
    Room.class,
    Room.price,
    Reservation.startDate,
    Reservation.endDate,
    (DATEDIFF(Reservation.endDate, Reservation.startDate) * Room.price) as roomCharge,
    IFNULL(extra.charges,0) as extra,
    ((DATEDIFF(Reservation.endDate, Reservation.startDate) * Room.price)
        + IFNULL(extra.charges,0)) as totalPrice
from    
    Reservation
    inner join Guest on Reservation.guestId = Guest.id
    inner join Room on Reservation.roomId = Room.id
    left join ( -- Use subquery to calculate extra charges.
        select  -- Could be more efficient by specifying key.
            guestId,
            sum(price) as charges
        from
            RoomService
            inner join Service on RoomService.serviceId = Service.id
        group by
            guestId) extra on extra.guestId = Guest.id
where
    Reservation.guestId = ?;

如果您想尝试一下，这里有完整的 MySQL 架构...

-- Make it idempotent
drop database if exists hotel;
create database hotel;

-- Create the tables (using lowerCamel cols)
CREATE TABLE hotel.Guest (
    id int AUTO_INCREMENT ,
    firstName varchar(40),
    lastName varchar (40),
PRIMARY KEY (id)
);
CREATE TABLE hotel.Room (
    id int AUTO_INCREMENT ,
    class varchar(40),
    price decimal(13,2),
PRIMARY KEY (id)
);
CREATE TABLE hotel.Reservation (
    id int AUTO_INCREMENT ,
    guestId int,
    roomId int,
    startDate date,
    endDate date,
PRIMARY KEY (id)
);

CREATE TABLE hotel.Service (
    id int AUTO_INCREMENT ,
    name varchar(40),
    price decimal(13,2),
PRIMARY KEY (id)
);

CREATE TABLE hotel.RoomService (
    id int AUTO_INCREMENT ,
    guestId int,
    roomId int,
    serviceId int,
PRIMARY KEY (id)
);

INSERT INTO hotel.Guest (id,firstName,lastName) VALUES (1,'Alice','Smith');
INSERT INTO hotel.Guest (id,firstName,lastName) VALUES (2,'Bob','Jones');
INSERT INTO hotel.Guest (id,firstName,lastName) VALUES (3,'Mallory','Adams');

INSERT INTO hotel.Room (id,class,price) VALUES (1,'premium',100.00);
INSERT INTO hotel.Room (id,class,price) VALUES (2,'base',75.00);
INSERT INTO hotel.Room (id,class,price) VALUES (3,'budget',50.00);

INSERT INTO hotel.Reservation (id,guestId,roomId,startDate,endDate) VALUES (1,1,1,'2020-05-15','2020-05-22');
INSERT INTO hotel.Reservation (id,guestId,roomId,startDate,endDate) VALUES (2,2,2,'2020-05-15','2020-05-22');

INSERT INTO hotel.Service (id,name,price) VALUES (1,'WIFI',100.00);
INSERT INTO hotel.Service (id,name,price) VALUES (2,'Safe',100.00);
INSERT INTO hotel.Service (id,name,price) VALUES (3,'Washing clothes',450.00);
INSERT INTO hotel.Service (id,name,price) VALUES (4,'Food delivery',250.00);

INSERT INTO hotel.RoomService (id,guestId,roomId,serviceId) VALUES (1,1,1,1);
INSERT INTO hotel.RoomService (id,guestId,roomId,serviceId) VALUES (2,1,1,4);

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE hotel.CalculateTotal(IN ID int ,OUT total float)
BEGIN
    SELECT 
        (DATEDIFF(Reservation.endDate, Reservation.startDate) * Room.price)
    INTO 
        total
    FROM 
        (select * from Guest Where id = ID) As R , 
        Room, 
        Reservation
WHERE 
    Room.id = Reservation.roomId 
    AND Reservation.guestId = R.id;
END //

DELIMITER ;