HorseJdbcDao.java 4.15 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 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
package at.ac.tuwien.sepm.assignment.individual.persistence.impl;

import at.ac.tuwien.sepm.assignment.individual.entity.Horse;
import at.ac.tuwien.sepm.assignment.individual.exception.NotFoundException;
import at.ac.tuwien.sepm.assignment.individual.persistence.HorseDao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
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;

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;

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

        try {
            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);
                ps.setString(1, horse.getName());
                ps.setString(2, horse.getDescription());
                ps.setInt(3, horse.getScore());
                ps.setDate(4, horse.getBirthday());

                if(horse.getOwner() == 0)
                    ps.setNull(5, Types.NULL);
                else
                    ps.setObject(5, horse.getOwner());

                ps.setObject(6, horse.getCreatedAt());
                ps.setObject(7, horse.getUpdatedAt());
                return ps;
            }, keyHolder);

            if (changes == 0)
                throw new NotFoundException("Creating horse failed, no rows affected");

            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
            throw new DataAccessException("Adding new records failed", e) {};
        } catch(NotFoundException e){
            throw new DataRetrievalFailureException("No new records added", e);
        }
    }

    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"));
        horse.setOwner(resultSet.getLong("owner_id"));

        return horse;
    }
}