src/main/java/ru.machine.service/DAO/RepairDAO.java - master - syunya/Course2026

src/main/java/ru.machine.service/DAO/RepairDAO.java
147 строк4 KB
syunya
Update: RepairDAO.java, RepairView.java
25 июн 2026, 19:03
25 июн 2026, 19:039a0872a
package ru.machine.service.dao;

import ru.machine.service.model.*;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class RepairDAO {

    private final Connection connection;

    public RepairDAO() throws SQLException {
        connection = DatabaseConnection.getInstance().getConnection();
    }

    // CREATE
    public void addRepair(Repair repair, List<WorkType> works) throws SQLException {

        String sql = """
                INSERT INTO repairs
                (client_id, machine_id, start_date, end_date, total_cost)
                VALUES (?, ?, ?, ?, ?)
                """;

        PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

        ps.setInt(1, repair.getClientId());
        ps.setInt(2, repair.getMachineId());
        ps.setDate(3, Date.valueOf(repair.getStartDate()));
        ps.setDate(4, Date.valueOf(repair.getEndDate()));
        ps.setBigDecimal(5, repair.getTotalCost());

        int affected = ps.executeUpdate();

        if (affected == 0) {
            throw new SQLException("Repair insert failed");
        }

        ResultSet rs = ps.getGeneratedKeys();

        if (!rs.next()) {
            throw new SQLException("No generated ID");
        }

        int repairId = rs.getInt(1);

        String sqlWork = "INSERT INTO repair_work_types (repair_id, work_type_id) VALUES (?, ?)";

        PreparedStatement psWork = connection.prepareStatement(sqlWork);

        for (WorkType w : works) {
            psWork.setInt(1, repairId);
            psWork.setInt(2, w.getId());
            psWork.addBatch();
        }

        psWork.executeBatch();
    }

    //READ LIST
    public List<RepairView> getAllView() throws SQLException {

        String sql =
                """
                SELECT r.id,
                       c.full_name AS client_name,
                       m.inventory_number AS machine_name,
                       r.start_date,
                       r.end_date,
                       r.total_cost
                FROM repairs r
                JOIN clients c ON r.client_id = c.id
                JOIN machines m ON r.machine_id = m.id
                ORDER BY r.id DESC
                """;

        List<RepairView> list = new ArrayList<>();

        ResultSet rs = connection.createStatement().executeQuery(sql);

        while (rs.next()) {
            list.add(new RepairView(
                    rs.getInt("id"),
                    rs.getString("client_name"),
                    rs.getString("machine_name"),
                    rs.getDate("start_date").toLocalDate(),
                    rs.getDate("end_date").toLocalDate(),
                    rs.getBigDecimal("total_cost")
            ));
        }

        return list;
    }

    //DELETE
    public void deleteRepair(int id) throws SQLException {

        PreparedStatement ps =
                connection.prepareStatement("DELETE FROM repairs WHERE id=?");

        ps.setInt(1, id);
        ps.executeUpdate();
    }

    //FILTER
    public List<RepairView> getByDateRange(LocalDate from, LocalDate to) throws SQLException {

        String sql =
                """
                SELECT r.id,
                       c.full_name AS client_name,
                       m.inventory_number AS machine_name,
                       r.start_date,
                       r.end_date,
                       r.total_cost
                FROM repairs r
                JOIN clients c ON r.client_id = c.id
                JOIN machines m ON r.machine_id = m.id
                WHERE r.start_date BETWEEN ? AND ?
                ORDER BY r.id DESC
                """;

        PreparedStatement ps = connection.prepareStatement(sql);

        ps.setDate(1, Date.valueOf(from));
        ps.setDate(2, Date.valueOf(to));

        ResultSet rs = ps.executeQuery();

        List<RepairView> list = new ArrayList<>();

        while (rs.next()) {
            list.add(new RepairView(
                    rs.getInt("id"),
                    rs.getString("client_name"),
                    rs.getString("machine_name"),
                    rs.getDate("start_date").toLocalDate(),
                    rs.getDate("end_date").toLocalDate(),
                    rs.getBigDecimal("total_cost")
            ));
        }

        return list;
    }
}