package org.geekhub.database;
import org.geekhub.objects.Actor;
import org.geekhub.objects.Movie;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class MovieDAO {
private SimpleJdbcInsert jdbcInsert;
private JdbcTemplate jdbcTemplate;
public void setDataSource(DataSource dataSource) {
this.jdbcInsert = new SimpleJdbcInsert(dataSource).withTableName("movie");
this.jdbcTemplate = new JdbcTemplate(dataSource);
}
public void insert(final Movie movie) {
Map<String, Object> map = new HashMap<String, Object>() {
{
put("name", movie.getName());
put("year", movie.getYear());
}
};
jdbcInsert.execute(map);
}
public List<Movie> queryMovies() {
String sql = "SELECT * FROM movie";
String moviesSql = "SELECT * FROM actor INNER JOIN movies_actors ON actor.id = movies_actors.actor_id WHERE " +
"movies_actors.movie_id = ?";
List<Movie> movieList = jdbcTemplate.query(sql, new RowMapper<Movie>() {
@Override
public Movie mapRow(ResultSet resultSet, int i) throws SQLException {
return new Movie(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
}
});
for (final Movie movie : movieList) {
List<Actor> actorList = jdbcTemplate.query(moviesSql, new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement preparedStatement) throws SQLException {
preparedStatement.setString(1, String.valueOf(movie.getId()));
}
}, new RowMapper<Actor>() {
@Override
public Actor mapRow(ResultSet resultSet, int i) throws SQLException {
return new Actor(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3),
resultSet.getDate(4));
}
}
);
movie.setActorList(actorList);
}
return movieList;
}
public Movie getById(int id) {
String sql = "SELECT * FROM movie WHERE movie.id = ?";
return jdbcTemplate.queryForObject(sql, new Object[]{id}, new BeanPropertyRowMapper<Movie>(Movie.class));
}
public void update(Movie movie) {
String sql = "UPDATE movie SET name = ?, year = ? WHERE id = ?";
jdbcTemplate.update(sql, movie.getName(), movie.getYear(), movie.getId());
}
public void delete(int id) {
String sql = "DELETE FROM movie WHERE id = ?";
String deleteMoviesSql = "DELETE FROM movies_actors WHERE movie_id = ?";
jdbcTemplate.update(deleteMoviesSql, id);
jdbcTemplate.update(sql, id);
}
}