HorseJdbcDao.java 8.22 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
import at.ac.tuwien.sepm.assignment.individual.exception.NotFoundException;
6
import at.ac.tuwien.sepm.assignment.individual.persistence.FileDao;
7
import at.ac.tuwien.sepm.assignment.individual.persistence.HorseDao;
8
import at.ac.tuwien.sepm.assignment.individual.util.ValidationException;
9 10 11
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
12
import org.springframework.dao.DataIntegrityViolationException;
13 14 15 16 17 18 19
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;

20 21
import javax.xml.crypto.Data;
import java.io.IOException;
22 23 24 25 26 27 28 29 30 31 32 33 34 35
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;
36
    private final FileDao fileDao = new HorseFileDao();
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57

    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());
58
        String sql = "INSERT INTO " + TABLE_NAME + "(name, description, score, birthday, race, image_path, owner_id, created_at, updated_at) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
59 60

        try {
61
            // Check if the constraints are violated
62
            this.validateHorse(horse);
63

64 65 66 67 68 69 70 71 72 73
            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);
74

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

81 82
                ps.setString(6, horse.getImagePath());

83
                if(horse.getOwner() == null || horse.getOwner() == 0)
84
                    ps.setNull(7, Types.NULL);
85
                else
86
                    ps.setObject(7, horse.getOwner());
87

88 89
                ps.setObject(8, horse.getCreatedAt());
                ps.setObject(9, horse.getUpdatedAt());
90 91 92 93
                return ps;
            }, keyHolder);

            if (changes == 0)
94
                throw new DataAccessException("Creating horse failed, no rows affected") {};
95 96 97 98 99 100

            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
101 102
            throw new DataAccessException("Adding new records failed", e) {
            };
103 104 105
        }
    }

106
    @Override
107
    public Horse updateHorse(Horse horse) throws DataAccessException, IOException {
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
        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)
143 144 145 146 147
                throw new DataAccessException("Updating horse failed, no rows affected") {};

            horse.setUpdatedAt(oldHorse.getUpdatedAt());

            fileDao.delete(oldHorse.getImagePath());
148 149 150 151 152 153

            return horse;

        } catch (DataAccessException e) {
            // We are doing this in order to not change the exception type
            throw new DataAccessException("Updating records failed", e) {};
154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176
        }
    }

    @Override
    public void deleteHorse(Long id) throws DataAccessException, NotFoundException, IOException {
        Horse horseToDelete = this.findOneById(id);
        LOGGER.trace("Delete horse with id {}", id);
        final String sql = "DELETE FROM " + TABLE_NAME + " WHERE id=?";

        try {
            int changes = jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sql);
                ps.setLong(1, id);
                return ps;
            });

            if (changes == 0)
                throw new DataAccessException("Deleting horse failed, no rows affected") {};

            fileDao.delete(horseToDelete.getImagePath());
        } catch(DataAccessException e){
            // We are doing this in order to not change the exception type
            throw new DataAccessException("Deleting records failed", e) {};
177 178 179 180 181 182 183
        }
    }

    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");
    }
184

185 186 187 188 189 190 191
    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"));
192
        horse.setRace(ERace.valueOf(resultSet.getString("race"))); // Convert to Enum for usage in objects
193
        horse.setOwner(resultSet.getLong("owner_id"));
194
        horse.setImagePath(resultSet.getString("image_path"));
195 196
        horse.setCreatedAt(resultSet.getTimestamp("created_at").toLocalDateTime());
        horse.setUpdatedAt(resultSet.getTimestamp("updated_at").toLocalDateTime());
197 198 199
        return horse;
    }
}