OwnerJdbcDao.java 9.04 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
import at.ac.tuwien.sepm.assignment.individual.entity.Owner;
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.OwnerDao;
8

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

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

@Repository
public class OwnerJdbcDao implements OwnerDao {

    private static final String TABLE_NAME = "Owner";
38
    private static final String HORSE_TABLE_NAME = "Horse";
39 40 41 42 43 44 45 46 47 48 49 50
    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) {
51
        LOGGER.debug("Get owner with id {}", id);
52 53 54 55 56 57 58 59
        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);
    }

60 61
    @Override
    public List<Owner> getAll() throws NotFoundException {
62
        LOGGER.debug("Get all owners");
63 64 65 66 67 68 69 70 71 72
        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 {
73
        LOGGER.debug("Get all owners with filters " + filters.entrySet());
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
        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;
    }

98 99
    @Override
    public List<Horse> getOwnedHorses(Long id) throws NotFoundException {
100
        LOGGER.debug("Get all horses for owner with id " + id);
101 102 103 104 105 106 107 108 109
        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;
    }

110 111
    @Override
    public Owner addOwner(Owner owner) {
112
        LOGGER.debug("Add owner {}", owner);
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136
        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)
137
                throw new PersistenceException("Creating owner failed, no rows affected");
138 139 140 141 142 143

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

            return owner;

        } catch (DataAccessException e) {
144
            throw new PersistenceException("Adding new records failed", e);
145 146 147
        }
    }

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

            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)
175
                throw new PersistenceException("Updating owner failed, no rows affected");
176 177 178 179 180

            owner.setCreatedAt(oldOwner.getCreatedAt());

            return owner;
        } catch(DataAccessException e) {
181
            throw new PersistenceException("Updating records failed", e);
182 183 184
        }
    }

185 186 187
    @Override
    public void deleteOwner(Long id) throws DataAccessException, NotFoundException {
        Owner ownerToDelete = this.findOneById(id);
188
        LOGGER.debug("Delete owner with id {}", id);
189 190 191 192 193 194 195 196 197 198 199 200 201
        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)
202
                throw new PersistenceException("Deleting owner failed, no rows affected");
203 204

        } catch(DataAccessException e){
205
            throw new PersistenceException("Deleting records failed", e);
206 207 208
        }
    }

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

214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229
    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;
    }

230 231 232 233 234 235 236 237 238 239
    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;
    }

}