OwnerJdbcDao.java 9.2 KB
Newer Older
1 2
package at.ac.tuwien.sepm.assignment.individual.persistence.impl;

3
import at.ac.tuwien.sepm.assignment.individual.entity.Horse;
4 5 6
import at.ac.tuwien.sepm.assignment.individual.entity.Owner;
import at.ac.tuwien.sepm.assignment.individual.exception.NotFoundException;
import at.ac.tuwien.sepm.assignment.individual.persistence.OwnerDao;
7

8
import java.lang.invoke.MethodHandles;
9
import java.sql.PreparedStatement;
10 11
import java.sql.ResultSet;
import java.sql.SQLException;
12
import java.time.LocalDateTime;
13
import java.util.ArrayList;
14
import java.util.HashMap;
15
import java.util.List;
16 17
import java.util.Map;

18 19 20
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
21 22
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataIntegrityViolationException;
23 24 25
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
26
import org.springframework.jdbc.core.JdbcTemplate;
27
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
28
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
29 30
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
31 32 33 34 35 36
import org.springframework.stereotype.Repository;

@Repository
public class OwnerJdbcDao implements OwnerDao {

    private static final String TABLE_NAME = "Owner";
37
    private static final String HORSE_TABLE_NAME = "Horse";
38 39 40 41 42 43 44 45 46 47 48 49
    private static final Logger LOGGER = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass());
    private final JdbcTemplate jdbcTemplate;
    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    public OwnerJdbcDao(JdbcTemplate jdbcTemplate, NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    @Override
    public Owner findOneById(Long id) {
50
        LOGGER.debug("Get owner with id {}", id);
51 52 53 54 55 56 57 58
        final String sql = "SELECT * FROM " + TABLE_NAME + " WHERE id=?";
        List<Owner> owners = jdbcTemplate.query(sql, new Object[] { id }, this::mapRow);

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

        return owners.get(0);
    }

59 60
    @Override
    public List<Owner> getAll() throws NotFoundException {
61
        LOGGER.debug("Get all owners");
62 63 64 65 66 67 68 69 70 71
        final String sql = "SELECT * FROM " + TABLE_NAME;
        List<Owner> owners = jdbcTemplate.query(sql, new Object[] { }, this::mapRow);

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

        return owners;
    }

    @Override
    public List<Owner> getFiltered(Map<String, String> filters) throws NotFoundException {
72
        LOGGER.debug("Get all owners with filters " + filters.entrySet());
73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
        final String sql = "SELECT * FROM " + TABLE_NAME + " WHERE UPPER(name) LIKE :name";

        // Create a list to hold the results
        List<Owner> owners = 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", "%_%");

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

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

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

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

        return owners;
    }

97 98
    @Override
    public List<Horse> getOwnedHorses(Long id) throws NotFoundException {
99
        LOGGER.debug("Get all horses for owner with id " + id);
100 101 102 103 104 105 106 107 108
        final String sql = "SELECT * FROM " + HORSE_TABLE_NAME + " WHERE owner_id=?";

        List<Horse> horses = jdbcTemplate.query(sql, new Object[] {id}, BeanPropertyRowMapper.newInstance(Horse.class));

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

        return horses;
    }

109 110
    @Override
    public Owner addOwner(Owner owner) {
111
        LOGGER.debug("Add owner {}", owner);
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
        final String sql = "INSERT INTO " + TABLE_NAME + "(name, created_at, updated_at) VALUES(?,?,?)";

        try {
            // Check if the constraints are violated
            this.validateOwner(owner);

            LocalDateTime currentTime = LocalDateTime.now();

            owner.setCreatedAt(currentTime);
            owner.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);

                ps.setString(1, owner.getName());
                ps.setObject(2, owner.getCreatedAt());
                ps.setObject(3, owner.getUpdatedAt());
                return ps;
            }, keyHolder);

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

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

            return owner;

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

148 149
    @Override
    public Owner updateOwner(Owner owner) throws DataAccessException {
150
        LOGGER.debug("Update owner {}", owner.toString());
151 152 153 154
        String sql = "UPDATE " + TABLE_NAME + " SET name=?, updated_at=? WHERE id=?";

        try {
            if(owner.getId() == null || owner.getId() == 0)
155
                throw new DataIntegrityViolationException("Owner Id missing or 0");
156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185

            this.validateOwner(owner);

            Owner oldOwner = findOneById(owner.getId());

            LocalDateTime currentTime = LocalDateTime.now();

            owner.setUpdatedAt(currentTime);

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

                ps.setString(1, owner.getName());
                ps.setObject(2, owner.getUpdatedAt());
                ps.setObject(3, owner.getId());
                return ps;
            });

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

            owner.setCreatedAt(oldOwner.getCreatedAt());

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

186 187 188
    @Override
    public void deleteOwner(Long id) throws DataAccessException, NotFoundException {
        Owner ownerToDelete = this.findOneById(id);
189
        LOGGER.debug("Delete owner with id {}", id);
190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210
        final String sql = "DELETE FROM " + TABLE_NAME + " WHERE id=?";

        if (ownerOwnsHorses(id))
            throw new DataIntegrityViolationException("Deleting owner failed, owner has horses assigned");

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

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

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

211 212 213 214
    private void validateOwner(Owner owner) throws DataIntegrityViolationException {
        if(owner.getName() == null || owner.getName().isEmpty())
            throw new DataIntegrityViolationException("Required parameters for owner missing");
    }
215

216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231
    private boolean ownerOwnsHorses(Long ownerId) {
        final String sql = "SELECT * FROM " + HORSE_TABLE_NAME + " WHERE owner_id=?";

        try {
            jdbcTemplate.queryForObject(sql, new Object[] {ownerId}, BeanPropertyRowMapper.newInstance(Horse.class));
        } catch(EmptyResultDataAccessException e) {
            // If empty, return false
            return false;
        } catch (IncorrectResultSizeDataAccessException e) {
            // If incorrect size above 0, return true
            return true;
        }

        return true;
    }

232 233 234 235 236 237 238 239 240 241
    private Owner mapRow(ResultSet resultSet, int i) throws SQLException {
        final Owner owner = new Owner();
        owner.setId(resultSet.getLong("id"));
        owner.setName(resultSet.getString("name"));
        owner.setCreatedAt(resultSet.getTimestamp("created_at").toLocalDateTime());
        owner.setUpdatedAt(resultSet.getTimestamp("updated_at").toLocalDateTime());
        return owner;
    }

}