HorseJdbcDao.java 10.4 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.exception.PersistenceException;
7
import at.ac.tuwien.sepm.assignment.individual.persistence.FileDao;
8 9 10
import at.ac.tuwien.sepm.assignment.individual.persistence.HorseDao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
11
import org.springframework.beans.factory.annotation.Autowired;
12
import org.springframework.dao.DataAccessException;
13
import org.springframework.dao.DataIntegrityViolationException;
14
import org.springframework.jdbc.core.JdbcTemplate;
15
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
16 17 18 19 20
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

21
import java.io.IOException;
22
import java.lang.invoke.MethodHandles;
23 24 25
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
26
import java.time.LocalDateTime;
27 28
import java.util.ArrayList;
import java.util.HashMap;
29
import java.util.List;
30
import java.util.Map;
31 32 33 34 35 36 37

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

40
    @Autowired
41 42 43 44 45 46 47
    public HorseJdbcDao(JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Override
    public Horse findOneById(Long id) {
48
        LOGGER.debug("Get horse with id {}", id);
49 50 51 52 53 54 55 56 57
        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);
    }


58 59
    @Override
    public List<Horse> getAll() throws NotFoundException {
60
        LOGGER.debug("Get all horses");
61 62 63 64 65 66 67 68 69 70
        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 {
71
        LOGGER.debug("Get all horses with filters " + filters.entrySet());
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 113
        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;
    }

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

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

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

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

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

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

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

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

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

        } catch (DataAccessException e) {
159
            throw new PersistenceException("Adding new records failed", e);
160 161 162
        }
    }

163
    @Override
164
    public Horse updateHorse(Horse horse) throws DataAccessException, IOException {
165
        LOGGER.debug("Update horse {}", horse.toString());
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181
        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);

182
                ps.setString(1, horse.getName().trim());
183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199
                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
                throw new PersistenceException("Updating horse failed, no rows affected");
201

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

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

            return horse;

        } catch (DataAccessException e) {
209
            throw new PersistenceException("Updating records failed", e);
210 211 212 213 214 215
        }
    }

    @Override
    public void deleteHorse(Long id) throws DataAccessException, NotFoundException, IOException {
        Horse horseToDelete = this.findOneById(id);
216
        LOGGER.debug("Delete horse with id {}", id);
217 218 219 220 221 222 223 224 225 226
        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)
227
                throw new PersistenceException("Deleting horse failed, no rows affected");
228 229 230

            fileDao.delete(horseToDelete.getImagePath());
        } catch(DataAccessException e){
231
            throw new PersistenceException("Deleting records failed", e);
232 233 234 235 236 237 238
        }
    }

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

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