HorseJdbcDao.java 7.27 KB
Newer Older
1 2 3
package at.ac.tuwien.sepm.assignment.individual.persistence.impl;

import at.ac.tuwien.sepm.assignment.individual.entity.Horse;
4
import at.ac.tuwien.sepm.assignment.individual.enums.ERace;
5 6
import at.ac.tuwien.sepm.assignment.individual.exception.NotFoundException;
import at.ac.tuwien.sepm.assignment.individual.persistence.HorseDao;
7
import at.ac.tuwien.sepm.assignment.individual.util.ValidationException;
8 9 10
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
11
import org.springframework.dao.DataIntegrityViolationException;
12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53
import org.springframework.dao.DataRetrievalFailureException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.lang.invoke.MethodHandles;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.time.LocalDateTime;
import java.util.List;

@Repository
public class HorseJdbcDao implements HorseDao {
    private static final String TABLE_NAME = "horse";
    private static final Logger LOGGER = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
    private final JdbcTemplate jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public HorseJdbcDao(JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Override
    public Horse findOneById(Long id) {
        LOGGER.trace("Get horse with id {}", id);
        final String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id=?";
        List<Horse> horses = jdbcTemplate.query(sql, new Object[] { id }, this::mapRow);

        if (horses.isEmpty()) throw new NotFoundException("Could not find horse with id " + id);

        return horses.get(0);
    }


    @Override
    public Horse addHorse(Horse horse) throws DataAccessException {
        LOGGER.trace("Add horse {}", horse.toString());
54
        String sql = "INSERT INTO " + TABLE_NAME + "(name, description, score, birthday, race, image_path, owner_id, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
55 56

        try {
57
            // Check if the constraints are violated
58
            this.validateHorse(horse);
59

60 61 62 63 64 65 66 67 68 69
            LocalDateTime currentTime = LocalDateTime.now();

            horse.setCreatedAt(currentTime);
            horse.setUpdatedAt(currentTime);

            // Create a key holder to get the key of the new record
            KeyHolder keyHolder = new GeneratedKeyHolder();

            int changes = jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
70

71 72 73 74
                ps.setString(1, horse.getName());
                ps.setString(2, horse.getDescription());
                ps.setInt(3, horse.getScore());
                ps.setDate(4, horse.getBirthday());
75
                ps.setString(5, horse.getRace().toString());  // Convert to string to be able to save in DB
76

77 78
                ps.setString(6, horse.getImagePath());

79
                if(horse.getOwner() == null || horse.getOwner() == 0)
80
                    ps.setNull(7, Types.NULL);
81
                else
82
                    ps.setObject(7, horse.getOwner());
83

84 85
                ps.setObject(8, horse.getCreatedAt());
                ps.setObject(9, horse.getUpdatedAt());
86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102
                return ps;
            }, keyHolder);

            if (changes == 0)
                throw new NotFoundException("Creating horse failed, no rows affected");

            horse.setId(((Number)keyHolder.getKeys().get("id")).longValue());
            return horse;

        } catch (DataAccessException e) {
            // We are doing this in order to not change the exception type
            throw new DataAccessException("Adding new records failed", e) {};
        } catch(NotFoundException e){
            throw new DataRetrievalFailureException("No new records added", e);
        }
    }

103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156
    @Override
    public Horse updateHorse(Horse horse) {
        LOGGER.trace("Update horse {}", horse.toString());
        String sql = "UPDATE " + TABLE_NAME + " SET name=?, description=?, score=?, birthday=?, race=?, image_path=?, owner_id=?, updated_at=? WHERE id=?";

        try {
            this.validateHorse(horse);

            if(horse.getId() == null || horse.getId() == 0)
                throw new DataIntegrityViolationException("Horse Id missing or 0");

            Horse oldHorse = findOneById(horse.getId());
            LocalDateTime currentTime = LocalDateTime.now();

            horse.setUpdatedAt(currentTime);

            int changes = jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sql);

                ps.setString(1, horse.getName());
                ps.setString(2, horse.getDescription());
                ps.setInt(3, horse.getScore());
                ps.setDate(4, horse.getBirthday());
                ps.setString(5, horse.getRace().toString());  // Convert to string to be able to save in DB
                ps.setString(6, horse.getImagePath());

                if(horse.getOwner() == null || horse.getOwner() == 0)
                    ps.setNull(7, Types.NULL);
                else
                    ps.setObject(7, horse.getOwner());

                ps.setObject(8, horse.getUpdatedAt());
                ps.setObject(9, horse.getId());
                return ps;
            });

            if (changes == 0)
                throw new NotFoundException("Updating horse failed, no rows affected");

            horse.setCreatedAt(oldHorse.getCreatedAt());
            return horse;

        } catch (DataAccessException e) {
            // We are doing this in order to not change the exception type
            throw new DataAccessException("Updating records failed", e) {};
        } catch(NotFoundException e){
            throw new DataRetrievalFailureException("No new records updated", e);
        }
    }

    private void validateHorse(Horse horse) throws DataIntegrityViolationException {
        if(horse.getName() == null || horse.getScore() == 0 || horse.getBirthday() == null || horse.getRace() == null || horse.getImagePath() == null)
            throw new DataIntegrityViolationException("Required parameters for horse missing");
    }
157

158 159 160 161 162 163 164
    private Horse mapRow(ResultSet resultSet, int i) throws SQLException {
        final Horse horse = new Horse();
        horse.setId(resultSet.getLong("id"));
        horse.setName(resultSet.getString("name"));
        horse.setDescription(resultSet.getString("description"));
        horse.setScore(resultSet.getShort("score"));
        horse.setBirthday(resultSet.getDate("birthday"));
165
        horse.setRace(ERace.valueOf(resultSet.getString("race"))); // Convert to Enum for usage in objects
166
        horse.setOwner(resultSet.getLong("owner_id"));
167
        horse.setImagePath(resultSet.getString("image_path"));
168 169
        horse.setCreatedAt(resultSet.getTimestamp("created_at").toLocalDateTime());
        horse.setUpdatedAt(resultSet.getTimestamp("updated_at").toLocalDateTime());
170 171 172
        return horse;
    }
}