HorseJdbcDao.java 10.6 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 8 9
import at.ac.tuwien.sepm.assignment.individual.persistence.HorseDao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
10
import org.springframework.beans.factory.annotation.Autowired;
11
import org.springframework.dao.DataAccessException;
12
import org.springframework.dao.DataIntegrityViolationException;
13
import org.springframework.jdbc.core.JdbcTemplate;
14
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
15 16 17 18 19
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
import java.io.IOException;
21
import java.lang.invoke.MethodHandles;
22 23 24
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
25
import java.time.LocalDateTime;
26 27
import java.util.ArrayList;
import java.util.HashMap;
28
import java.util.List;
29
import java.util.Map;
30 31 32 33 34 35 36

@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;
37
    private final FileDao fileDao = new HorseFileDao();
38

39
    @Autowired
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
    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);
    }


57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112
    @Override
    public List<Horse> getAll() throws NotFoundException {
        LOGGER.trace("Get all horses");
        final String sql = "SELECT * FROM " + TABLE_NAME;
        List<Horse> horses = jdbcTemplate.query(sql, new Object[] { }, this::mapRow);

        if(horses.isEmpty()) throw new NotFoundException("No horses found in the database");

        return horses;
    }

    @Override
    public List<Horse> getFiltered(Map<String, String> filters) throws NotFoundException {
        LOGGER.trace("Get all horses with filters " + filters.entrySet());
        final String sql = "SELECT * FROM " + TABLE_NAME + " WHERE UPPER(name) LIKE :name AND UPPER(description) LIKE :description AND race LIKE :race AND score LIKE :score AND birthday <= :birthday";

        // Create a list to hold the results
        List<Horse> horses = new ArrayList<>();

        // Create a map to hold the sql filters with all values set as wildcards
        Map<String, String> queryFilters = new HashMap<>();
        queryFilters.put("name", "%_%");
        queryFilters.put("description", "%_%");
        queryFilters.put("race", "%_%");
        queryFilters.put("score", "%");

        DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        queryFilters.put("birthday", df.format(new java.sql.Date(System.currentTimeMillis())));

        // Go through the supplied filters and find set values
        if(filters.get("name") != null)
            queryFilters.replace("name", '%' + filters.get("name").toUpperCase() + '%');

        if(filters.get("description") != null)
            queryFilters.replace("description", '%' + filters.get("description").toUpperCase() + '%');

        if(filters.get("race") != null)
            queryFilters.replace("race", filters.get("race").toUpperCase());

        if(filters.get("score") != null)
            queryFilters.replace("score", filters.get("score"));

        if(filters.get("birthday") != null)
            queryFilters.replace("birthday", filters.get("birthday"));

        // Create an map sql parameter source for use in the query
        MapSqlParameterSource sqlMap = new MapSqlParameterSource();
        sqlMap.addValues(queryFilters);

        horses = namedParameterJdbcTemplate.query(sql, sqlMap, this::mapRow);

        if(horses.isEmpty()) throw new NotFoundException("No horses found in the database");

        return horses;
    }

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

        try {
119
            // Check if the constraints are violated
120
            this.validateHorse(horse);
121

122 123 124 125 126 127 128 129 130 131
            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);
132

133 134 135 136
                ps.setString(1, horse.getName());
                ps.setString(2, horse.getDescription());
                ps.setInt(3, horse.getScore());
                ps.setDate(4, horse.getBirthday());
137
                ps.setString(5, horse.getRace().toString());  // Convert to string to be able to save in DB
138

139 140
                ps.setString(6, horse.getImagePath());

141
                if(horse.getOwner() == null || horse.getOwner() == 0)
142
                    ps.setNull(7, Types.NULL);
143
                else
144
                    ps.setObject(7, horse.getOwner());
145

146 147
                ps.setObject(8, horse.getCreatedAt());
                ps.setObject(9, horse.getUpdatedAt());
148 149 150 151
                return ps;
            }, keyHolder);

            if (changes == 0)
152
                throw new DataAccessException("Creating horse failed, no rows affected") {};
153 154 155 156 157 158

            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
159
            throw new DataAccessException("Adding new records failed", e) {};
160 161 162
        }
    }

163
    @Override
164
    public Horse updateHorse(Horse horse) throws DataAccessException, IOException {
165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199
        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)
200 201
                throw new DataAccessException("Updating horse failed, no rows affected") {};

202
            horse.setCreatedAt(oldHorse.getCreatedAt());
203 204

            fileDao.delete(oldHorse.getImagePath());
205 206 207 208 209 210

            return horse;

        } catch (DataAccessException e) {
            // We are doing this in order to not change the exception type
            throw new DataAccessException("Updating records failed", e) {};
211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233
        }
    }

    @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) {};
234 235 236 237 238 239 240
        }
    }

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

242 243 244 245 246 247 248
    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"));
249
        horse.setRace(ERace.valueOf(resultSet.getString("race"))); // Convert to Enum for usage in objects
250
        horse.setOwner(resultSet.getLong("owner_id"));
251
        horse.setImagePath(resultSet.getString("image_path"));
252 253
        horse.setCreatedAt(resultSet.getTimestamp("created_at").toLocalDateTime());
        horse.setUpdatedAt(resultSet.getTimestamp("updated_at").toLocalDateTime());
254 255 256
        return horse;
    }
}