问带DataBase的汽车租赁
EN

Code Review用户

提问于 2018-07-16 04:20:19

回答 1查看 1.3K关注 0票数 1

这将是非常有趣的听取关于我的最新项目的建议-汽车租赁，与数据库互动。它没有GUI，因为我以前还没有学过它。这是因为您会发现很多sys.out.print行。它包含了很多类，我会在这里放下来最重要的。在底部，我将把链接到我的github项目。您可以通过创建新的客户端随机数来询问原因。这是因为我不知道如何对用户对象和客户端对象进行配对，所以我的应用程序不可能为用户或工作人员创建帐户。例如，租用汽车时需要客户号码。用户必须输入这个号码。如果他想要填充所有租来的汽车，他必须输入他在租车期间输入的客户号码。这就是为什么每个客户都是独一无二的--它有自己的号码。

数据库类。包含负责添加/删除/更新.我数据库里的数据。方法很长，因为我使用了准备好的语句。我听说了，这是很好的练习。在rentACar、makeCarUnavailable、makeCarAvailable等方法中，我添加了一些功能，负责在不存在car的情况下抛出消息。

public class DataBase {
private Connection connection;
private Statement statement;
private PreparedStatement preparedStatement;
private ResultSet result;

public DataBase() throws SQLException {
    connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/rentalcar?autoReconnect=true&serverTimezone=" + TimeZone.getDefault().getID(), "root", "...");
    statement = connection.createStatement();
}

public void insertNewCustomer(Client client) throws SQLException {
    preparedStatement = connection.prepareStatement("insert into client" + "(namee, surname, street,houseNumber,city,peselNumber,rentDate, clientNumber)" + "values(?,?,?,?,?,?,?,?)");

    preparedStatement.setString(1, client.getName());
    preparedStatement.setString(2, client.getSurname());
    preparedStatement.setString(3, client.getStreet());
    preparedStatement.setInt(4, client.getHouseNumber());
    preparedStatement.setString(5, client.getCity());
    preparedStatement.setLong(6, client.getPeselNumber());
    preparedStatement.setString(7, client.getRentDate());
    preparedStatement.setInt(8, client.getClientNumber());

    preparedStatement.executeUpdate();
}

public void insertNewCar(Car car) throws SQLException {
    preparedStatement = connection.prepareStatement("insert into car" + "(brand, productionYear, engineCapacity,dayPrice,available)" + "values(?,?,?,?,?)");

    preparedStatement.setString(1, car.getBrand());
    preparedStatement.setString(2, car.getProductionYear());
    preparedStatement.setString(3, car.getEngineCapacity());
    preparedStatement.setInt(4, car.getDayPrice());
    preparedStatement.setString(5, car.getAvailable());

    preparedStatement.executeUpdate();
}

public void rentACar(RentingACar rentingACar) throws SQLException {
    int count = 0;
    boolean isAvailable = true;
    {
        preparedStatement = connection.prepareStatement("SELECT COUNT(0) FROM car WHERE available='1' AND brand=?");
        preparedStatement.setString(1, rentingACar.getBrand());
        result = preparedStatement.executeQuery();
    }
    while (result.next()) {
        count = result.getInt(1);
    }
    if (count < 1)
        isAvailable = false;

    if (isAvailable) {
        preparedStatement = connection.prepareStatement("insert into rentcar" + "(brand,namee,surname,rentDate,clientNumber)" + "values(?,?,?,?,?)");
        preparedStatement.setString(1, rentingACar.getBrand());
        preparedStatement.setString(2, rentingACar.getName());
        preparedStatement.setString(3, rentingACar.getSurname());
        preparedStatement.setString(4, rentingACar.getRentDate());
        preparedStatement.setInt(5, rentingACar.getClientNumber());
        preparedStatement.executeUpdate();

        preparedStatement = connection.prepareStatement("update car " + " set available='0'" + " where brand= ? ");
        preparedStatement.setString(1, rentingACar.getBrand());
        preparedStatement.executeUpdate();
        System.out.println("Car was rented!");
    } else {
        System.out.println("There is no " + rentingACar.getBrand() + " in our car or all types of this car are rented!");
    }
}

public void returnACar(Car car) throws SQLException {
    preparedStatement = connection.prepareStatement("DELETE from rentcar WHERE brand=? AND clientNumber=?");
    preparedStatement.setString(1, car.getBrand());
    preparedStatement.setInt(2, car.getClientNumber());
    preparedStatement.executeUpdate();

    preparedStatement = connection.prepareStatement("update car " + " set available='1'" + " where brand=?");
    preparedStatement.setString(1, car.getBrand());
    preparedStatement.executeUpdate();
}

public void makeCarUnavailable(Car car) throws SQLException {
    int count = 0;
    boolean isAvailable = true;
    {
        preparedStatement = connection.prepareStatement("SELECT COUNT(0) FROM car WHERE brand=? AND productionYear=? ");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        result = preparedStatement.executeQuery();
    }
    while (result.next()) {
        count = result.getInt(1);
    }
    if (count < 1)
        isAvailable = false;

    if (isAvailable) {
        preparedStatement = connection.prepareStatement("update car " + " set available='0'" + " where brand=? AND productionYear=?");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        preparedStatement.executeUpdate();
        System.out.println(car.getBrand() + " was made unavailable");
    } else {
        System.out.println("No " + car.getBrand() + " in system!");
    }
}

public void makeCarAvailable(Car car) throws SQLException {
    int count = 0;
    boolean isAvailable = true;
    {
        preparedStatement = connection.prepareStatement("SELECT COUNT(0) FROM car WHERE brand=? AND productionYear=? ");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        result = preparedStatement.executeQuery();
    }
    while (result.next()) {
        count = result.getInt(1);
    }
    if (count < 1)
        isAvailable = false;

    if (isAvailable) {
        preparedStatement = connection.prepareStatement("update car " + " set available='1'" + " where brand=? AND productionYear=?");
        preparedStatement.setString(1, car.getBrand());
        preparedStatement.setString(2, car.getProductionYear());
        preparedStatement.executeUpdate();
        System.out.println(car.getBrand() + " was made unavailable");
    } else {
        System.out.println("No " + car.getBrand() + " in system!");
    }
}

public void populateTableViewCars(Car car) throws SQLException {
    preparedStatement = connection.prepareStatement("SELECT * FROM car WHERE dayPrice > ?");
    preparedStatement.setDouble(1, car.getDayPrice());
    result = preparedStatement.executeQuery();

    while (result.next()) {

        String brand = result.getString("brand");
        String productionYear = result.getString("productionYear");
        String engineCapacity = result.getString("engineCapacity");
        String dayPrice = result.getString("dayPrice");
        String available = result.getString("available");
        System.out.println("----------------------------");
        System.out.printf("Brand:" + brand + "\nEngine Capacity:" + engineCapacity + "\nDayPrice:" + dayPrice + "\nProduction Year:" + productionYear + "\navailable:" + available + "\n");
        System.out.println("----------------------------");
    }
}


public void populateTableRent(Client client) throws SQLException {
    preparedStatement = connection.prepareStatement("SELECT * FROM rentcar WHERE clientNumber=?");
    preparedStatement.setInt(1, client.getClientNumber());
    result = preparedStatement.executeQuery();

    while (result.next()) {

        String brand = result.getString("brand");
        String name = result.getString("namee");
        String surname = result.getString("surname");
        String rentDate = result.getString("rentDate");
        System.out.println("----------------------------");
        System.out.printf("Brand:" + brand + "\nName:" + name + "\nSurname:" + surname + "\nDate of rental:" + rentDate + "\n");
        System.out.println("----------------------------");
    }
}

public void populateTableViewClients() throws SQLException {
    String sql = "SELECT * FROM `client`";
    result = statement.executeQuery(sql);

    while (result.next()) {

        String namee = result.getString("namee");
        String surname = result.getString("surname");
        String street = result.getString("street");
        int houseNumber = result.getInt("houseNumber");
        long peselNumber = result.getLong("peselNumber");
        String rentDate = result.getString("rentDate");
        System.out.println("----------------------------");
        System.out.printf("Name:" + namee + "\nSurname:" + surname + "\nStreet:" + street + "\nNumber of house:" + houseNumber + "\nPesel number:" + peselNumber + "\nDate of rental:" + rentDate + "\n");
        System.out.println("----------------------------");
    }
}
}

我有一个名为DataGetter的包，它包含两个类& WorkerDataGetter和<>ClientDataGetter。它们包含负责获取数据和创建对象的方法。例如，在WorkerDataGetter中，我们可以找到createCar方法，它从用户那里收集数据，并创建将传递给数据库方法的新car对象。

WorkerDataGetter

public class WorkerDataGetter {
private Scanner input = new Scanner(System.in);

public Car createCar() {
    Car car = new Car();

    System.out.print("Brand: ");
    car.setBrand(input.next());
    System.out.print("Day price: ");
    car.setDayPrice(input.nextInt());
    System.out.print("Engine Capcity: ");
    car.setEngineCapacity(input.next());
    System.out.print("Production year: ");
    car.setProductionYear(input.next());
    System.out.print("available: ");
    car.setAvailable(input.next());

    return car;
}

public Car makeCarUnavailable() {
    Car car = new Car();

    System.out.print("Brand: ");
    car.setBrand(input.next());
    System.out.print("production year: ");
    car.setProductionYear(input.next());

    return car;
}

public Car makeCarAavailable() {
    Car car = new Car();

    System.out.print("Brand: ");
    car.setBrand(input.next());
    System.out.print("Production year : ");
    car.setProductionYear(input.next());

    return car;
}
}

ClientDataGetter

public class ClientDataGetter {
private Scanner input = new Scanner(System.in);
private Random rand = new Random();

public Client createClient() {
    Client client = new Client();
    client.setClientNumber(rand.nextInt(999));

    System.out.print("name: ");
    client.setName(input.next());
    System.out.print("surname: ");
    client.setSurname(input.next());
    System.out.print("city: ");
    client.setCity(input.next());
    System.out.print("house number: ");
    client.setHouseNumber(input.nextInt());
    System.out.print("street: ");
    client.setStreet(input.next());
    System.out.print("pesel number: ");
    client.setPeselNumber(input.nextLong());
    System.out.print("rent date: ");
    client.setRentDate(input.next());
    System.out.println("Your client number is: " + client.getClientNumber());

    return client;
}

public RentingACar rentACar() {
    RentingACar rentingACar = new RentingACar();

    System.out.print("Brand: ");
    rentingACar.setBrand(input.next());
    System.out.print("Name: ");
    rentingACar.setName(input.next());
    System.out.print("Surname: ");
    rentingACar.setSurname(input.next());
    System.out.print("Rent Date: ");
    rentingACar.setRentDate(input.next());
    System.out.print("Client number: ");
    rentingACar.setClientNumber(input.nextInt());

    return rentingACar;
}

public Car populateTableViewCars() {
    Car car = new Car();

    System.out.println("Input minimum price per day. If you want to display all cars - input 0.\nMinimum price: ");
    car.setDayPrice(input.nextInt());

    return car;
}

public Client populateTableRent() {
    Client client = new Client();

    System.out.println("Input your client number: ");
    client.setClientNumber(input.nextInt());

    return client;
}

public Car returnACar() {
    Car car = new Car();

    System.out.println("Input brand of car that you want to return: ");
    car.setBrand(input.next());
    System.out.println("Input your client number, otherwise car won't be removed from our DataBase!");
    car.setClientNumber(input.nextInt());

    return car;
}
}

CarRentalOptions类-包含我们可以在App中找到的所有方法。

public class CarRentalOptions {
private DataBase dataBase = new DataBase();

CarRentalOptions() throws SQLException {
}

void createNewCustomer(Client client) throws SQLException {
    dataBase.insertNewCustomer(client);

    System.out.println("Client added successfully!");
}

void createNewCar(Car car) throws SQLException {
    dataBase.insertNewCar(car);

    System.out.println("Car added successfully!");
}

void makeCarUnavailable(Car car) throws SQLException {
    dataBase.makeCarUnavailable(car);
}

void makeCarAvailable(Car car) throws SQLException {
    dataBase.makeCarAvailable(car);
}

void rentACar(RentingACar rentingACar) throws SQLException {
    dataBase.rentACar(rentingACar);
}

void populateTableViewCars(Car car) throws SQLException {
    dataBase.populateTableViewCars(car);
}

void populateTableRent(Client client) throws SQLException {
    dataBase.populateTableRent(client);
}

void populateTableViewClients() throws SQLException {
    dataBase.populateTableViewClients();
}

void returnACar(Car car) throws SQLException {
    dataBase.returnACar(car);
}
}

应用程序的大脑

public class CarRentalEngine {
private int option;
private Scanner input = new Scanner(System.in);
private CarRentalOptions carRentalOptions = new CarRentalOptions();
private ClientDataGetter clientDataGetter = new ClientDataGetter();
private WorkerDataGetter workerDataGetter = new WorkerDataGetter();

CarRentalEngine() throws SQLException {
}

void startCarRental() throws SQLException {
    System.out.println("Who are you?\n1. Customer\n2. Worker");
    try {
        switch (input.nextInt()) {
            case 1:
                executeClientCase();
                break;
            case 2:
                executeWorkerCase();
                break;
        }
    } catch (InputMismatchException e) {
        System.err.println("Your input is wrong!");
    }
}


private void executeOptionsForClient(int option) throws SQLException {
    switch (option) {
        case 1:
            carRentalOptions.rentACar(clientDataGetter.rentACar());
            break;
        case 2:
            carRentalOptions.returnACar(clientDataGetter.returnACar());
            break;
        case 3:
            carRentalOptions.populateTableRent(clientDataGetter.populateTableRent());
            break;
        case 4:
            carRentalOptions.populateTableViewCars(clientDataGetter.populateTableViewCars());
            break;
        case 5:
            break;
    }
}


private void executeOptionsForWorker(int option) throws SQLException {
    switch (option) {
        case 1:
            carRentalOptions.populateTableViewClients();
            break;
        case 2:
            carRentalOptions.populateTableViewCars(clientDataGetter.populateTableViewCars());
            break;
        case 3:
            carRentalOptions.makeCarAvailable(workerDataGetter.makeCarAavailable());
            break;
        case 4:
            carRentalOptions.makeCarUnavailable(workerDataGetter.makeCarUnavailable());
            break;
        case 5:
            carRentalOptions.createNewCar(workerDataGetter.createCar());
        case 6:
            break;
    }
}


private void executeClientCase() throws SQLException {
    System.out.println("1. Have you inputted your data before?\nN/Y: ");
    if (input.next().toUpperCase().equals("N")) {
        carRentalOptions.createNewCustomer(clientDataGetter.createClient());
        System.out.println("Now you have your unique number clinet, use it where it is required!");
    } else {
        do {
            System.out.println("What do you want to do?");
            System.out.println("1. Rent a car\n2. Return a car\n3. Populate rented cars\n4. Populate cars\n5. Quit");
            option = input.nextInt();
            executeOptionsForClient(option);
        }
        while (option != 5);
    }
}

private void executeWorkerCase() throws SQLException {
    do {
        System.out.println("What do you want to do?");
        System.out.println("1. Populate clients\n2. Populate cars\n3. Make car available\n4. Make car unavailable\n5. Insert new car\n6. Quit");
        option = input.nextInt();
        executeOptionsForWorker(option);
    }
    while (option != 6);
}
}

我还有一些类，如Car、Client、RentingACar --它们包含getter和setter。你可以在我的github CarRental/Model上找到它。下面是链接：https://github.com/must1/RentalCar。

谢谢你的建议。

java

1元体验云点播

覆盖建站/短视频等热门场景，提供媒资管理、短视频 SDK、小程序插件和播放器云+端一体化能力，助您抓住视频风口

回答 1

Code Review用户

回答已采纳

发布于 2018-07-16 13:06:28

数据库

在DataBase类中，我使用方法作用域变量，而不是类属性。通常，最好有方法作用域变量，因为您可以控制它们的状态。这条路：

public void rentACar(RentingACar rentingACar) throws SQLException {
    int count = 0;
    boolean isAvailable = true;
    {
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(0) FROM car WHERE available='1' AND brand=?");
        preparedStatement.setString(1, rentingACar.getBrand());
        ResultSet result = preparedStatement.executeQuery();
    }
    while (result.next()) {
        count = result.getInt(1);
    }
    if (count < 1)
        isAvailable = false;

    if (isAvailable) {
        preparedStatement = connection.prepareStatement("insert into rentcar" + "(brand,namee,surname,rentDate,clientNumber)" + "values(?,?,?,?,?)");
        preparedStatement.setString(1, rentingACar.getBrand());
        preparedStatement.setString(2, rentingACar.getName());
        preparedStatement.setString(3, rentingACar.getSurname());
        preparedStatement.setString(4, rentingACar.getRentDate());
        preparedStatement.setInt(5, rentingACar.getClientNumber());
        preparedStatement.executeUpdate();

        preparedStatement = connection.prepareStatement("update car " + " set available='0'" + " where brand= ? ");
        preparedStatement.setString(1, rentingACar.getBrand());
        preparedStatement.executeUpdate();
        System.out.println("Car was rented!");
    } else {
        System.out.println("There is no " + rentingACar.getBrand() + " in our car or all types of this car are rented!");
    }
}

此外，您还可以从DB的角度跟踪两种不同的策略。使用单个连接并保持打开它(打开DB连接非常昂贵)，或者为要执行的每个事务打开一个连接。关于这个问题的有各种各样的风景，通常对于生产环境来说，第二个策略进行得很好，但是下面有一个连接池。

无论如何，考虑到您想要使用单个连接，您需要在使用它们之后关闭PreparedStatement和ResultSet。从Java 7开始，可以做语法中的try：

try (Statement statement = connection.createStatement()) {
    try (ResultSet resultSet = statement.executeQuery("some query")) {
        // Do stuff with the result set.
    }
    try (ResultSet resultSet = statement.executeQuery("some query")) {
        // Do more stuff with the second result set.
    }
}

此外，我个人更喜欢在一些定制的例外情况下结束SqlExceptions。这样，您就可以对DB层进行抽象，但是您将能够向上了解细节。就像这样：

public class DBException extends RuntimeException{
  //Here write a constructor that can be called with any other exception
  public DBException(Throwable ex){
    super(ex);
  }
}

它是一个具有throws关键字的运行时异常D11，但如果需要，您可以向上捕获它。你可以这样使用它：

public void methodThatDoesDBStuff(){
  try{
   //DB stuff
  } catch(SQLException ex /*Add any other exception types here if you want*/){
    throw new DBException(ex);
  }
}

下一点是关于DB事务的。假设在您的rentACar方法中，您获得了正确插入DB中的租房信息，但是更新汽车的查询失败了。您将使DB处于不一致的状态。这就是DB事务来救援的地方。在使用JDBC时，它执行每个操作的事务，但您可以将其配置为禁用自动提交，并在一切正常时执行提交。

Data Getters

我看到这些类的目标是用用户输入填充数据类。但是，由于它们是在主引擎中创建的，并且引擎本身声明了一个从用户输入读取的Scanner对象，因此我将使用该输入并将其作为参数传递给方法：

carRentalOptions.rentACar(clientDataGetter.rentACar(input));

在ClientDataGetter中，删除了它的Scanner：

public RentingACar rentACar(Scanner input) {
    RentingACar rentingACar = new RentingACar();

    System.out.print("Brand: ");
    rentingACar.setBrand(input.next());
    System.out.print("Name: ");
    rentingACar.setName(input.next());
    System.out.print("Surname: ");
    rentingACar.setSurname(input.next());
    System.out.print("Rent Date: ");
    rentingACar.setRentDate(input.next());
    System.out.print("Client number: ");
    rentingACar.setClientNumber(input.nextInt());

    return rentingACar;
}