repository jdbc

SqlInventoryRepositoryImpl.java

package org.jazzteam.krivonos.fitness.jdbc;

import com.google.common.collect.Lists;

import lombok.extern.slf4j.Slf4j;
import org.jazzteam.fitnesclub.model.Inventory;
import org.jazzteam.fitnesclub.model.InventoryType;
import org.jazzteam.fitnesclub.repository.addictional.InventoryRepository;
import org.jazzteam.krivonos.fitness.jdbc.util.ConnectionUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.function.Predicate;

@Slf4j
public class SqlInventoryRepositoryImpl implements InventoryRepository {


    @Override
    public Inventory findById(Long id) {
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(true);
            try (PreparedStatement statement = connection.prepareStatement(
                    "select id, type, amount from fitness_db.inventories where id = ?")) {

                statement.setLong(1, id);

                try (ResultSet resultSet = statement.executeQuery()) {
                    if (resultSet.next()) {
                        return getInventory(resultSet);
                    }
                }
            }
        } catch (SQLException throwables) {
            log.error(throwables.getMessage());
        }
        return null;
    }


    @Override
    public Inventory save(Inventory inventory) {
        if (findById(inventory.getId()) != null) {
            return update(inventory);
        }
        return saveNew(inventory);
    }


    @Override
    public void delete(Inventory inventory) {
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(false);
            try (PreparedStatement statement = connection.prepareStatement(
                    "delete from fitness_db.inventories where id = ?",
                    Statement.RETURN_GENERATED_KEYS)) {
                statement.setLong(1, inventory.getId());
                statement.execute();
                connection.commit();
            }
        } catch (SQLException throwables) {
            log.error(throwables.getMessage());
        }
    }

    @Override
    public List<Inventory> findAll() {
        List<Inventory> inventories = new ArrayList<>();
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(true);
            try (PreparedStatement statement = connection.prepareStatement(
                    "select id, type, amount from fitness_db.inventories")) {

                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {
                        Inventory inventory = getInventory(resultSet);
                        inventories.add(inventory);
                    }
                }
                return inventories;
            }
        } catch (SQLException throwables) {
            log.error(throwables.getMessage());
        }
        return inventories;
    }

    @Override
    public List<Inventory> findAll(Predicate<Inventory> predicate) {

        List<Inventory> inventories = new ArrayList<>();
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(true);

            try (PreparedStatement statement = connection.prepareStatement(
                    "select id, type, amount from fitness_db.inventories ")) {
                try (ResultSet resultSet = statement.executeQuery()) {

                    while (resultSet.next()) {
                        Inventory inventory = getInventory(resultSet);
                        if (predicate.test(inventory)) {
                            inventories.add(inventory);
                        }
                    }
                }
            }
        } catch (SQLException throwables) {
            log.error(throwables.getMessage());
        }
        return inventories;
    }

    @Override
    public List<Inventory> findByInventoryType(InventoryType inventoryType) {

        List<Inventory> inventories = new ArrayList<>();
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(true);
            try (PreparedStatement statement = connection.prepareStatement(
                    "select id, type, amount from fitness_db.inventories where type = ?")) {
                statement.setLong(1, inventoryType.getId());
                try (ResultSet resultSet = statement.executeQuery()) {
                    while (resultSet.next()) {
                        Inventory inventory = getInventory(resultSet);
                        inventories.add(inventory);
                    }
                }
            }
        } catch (SQLException throwables) {
            log.error(throwables.getMessage());
        }
        return inventories;
    }

    public InventoryType findTypeById(Long id) {
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(true);
            try (PreparedStatement statement = connection.prepareStatement(
                    "select id, type_name  from fitness_db.inventory_types where id = ? ")) {
                statement.setLong(1, id);
                try (ResultSet resultSet = statement.executeQuery()) {
                    if (resultSet.next()) {
                        return new InventoryType(id,
                                resultSet.getString("type_name"),
                                Lists.newArrayList(),
                                Lists.newArrayList());
                    }
                }
            }
        } catch (SQLException throwables) {
            log.error(throwables.getMessage());
        }
        return null;
    }

    private Inventory getInventory(ResultSet resultSet) throws SQLException {
        return new Inventory(
                resultSet.getLong("id"),
                findTypeById(resultSet.getLong("type")),
                resultSet.getInt("amount"),
                Lists.newArrayList());
    }

    private Inventory saveNew(Inventory inventory) {
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(true);
            try (PreparedStatement statement = connection
                    .prepareStatement(
                            "insert into fitness_db.inventories (type, amount) values (?, ?)",
                            Statement.RETURN_GENERATED_KEYS)) {
                statement.setLong(1, inventory.getInventoryType().getId());
                statement.setInt(2, inventory.getAmount());
                int affectedRows = statement.executeUpdate();

                if (affectedRows == 0) {
                    throw new IllegalArgumentException("Failed save inventory");
                }
                try (ResultSet generated = statement.getGeneratedKeys()) {
                    if (generated.next()) {
                        inventory.setId(generated.getLong(1));
                    } else {
                        connection.rollback();
                        throw new IllegalArgumentException("No id obtained");
                    }
                }
                return findById(inventory.getId());
            }
        } catch (SQLException throwables) {
            log.error("{}", throwables);
            throw new IllegalStateException(throwables.getSQLState());
        }
    }

    private Inventory update(Inventory inventory) {
        try (Connection connection = ConnectionUtil.getConnection()) {
            connection.setAutoCommit(true);
            try (PreparedStatement statement = connection
                    .prepareStatement(
                            "update fitness_db.inventories set amount = ?, type = ? where id = ?",
                            Statement.RETURN_GENERATED_KEYS)) {
                statement.setLong(3, inventory.getInventoryType().getId());
                statement.setInt(1, inventory.getAmount());
                statement.setInt(2, inventory.getAmount());

                int affectedRows = statement.executeUpdate();

                if (affectedRows == 0) {
                    throw new IllegalArgumentException("Failed save inventory");
                }
                return findById(inventory.getId());
            }
        } catch (SQLException throwables) {
            log.error("{}", throwables);
            throw new IllegalStateException(throwables.getSQLState());
        }
    }
}

1. code style: чаму месцамі па 2 радкі паміж метадамі?
2. назва класа пачынаецца з Sql - чаму?
3. line 25 - павінна быць у канстанце (як і ў іншых месцах таксама)
4. метад findById (а, гэта ў кожным метадзе) - try{try{try}} - непрыгожа. Да і ўсё роўна catch толькі адзін. Я бы пакінуў толькі try (Connection connection = ConnectionUtil.getConnection()) , а астатняе без try with resources. UPD зрабі як сказаў Кірыл
5. метад findAll - у любым выпадку вяртаеш адзін і той жа ліст. Магчыма, лепш гэта проста перанесці ў finally (+ findByInventoryType)
6. line 146 - дзіўная бібліятэка, чаму б не выкарыстоўваць проста new ArrayList або Collections.emptyList()? (мб і норм)
7. lines 202-204 - а чо ў сэнсе, тут усё правільна?
8. connection - ці трэба іх зачыняць? Зрабі правільна