Java tutorial
/* * Copyright 1998-2012 Linux.org.ru * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. */ package ru.org.linux.poll; import com.google.common.collect.ImmutableList; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowCallbackHandler; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.support.rowset.SqlRowSet; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import ru.org.linux.user.User; import javax.sql.DataSource; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; @Repository public class PollDao { private static final String queryPoolIdByTopicId = "SELECT polls.id FROM polls,topics WHERE topics.id=? AND polls.topic=topics.id"; private static final String queryCurrentPollId = "SELECT polls.id FROM polls,topics WHERE topics.id=polls.topic AND topics.moderate = 't' AND topics.deleted = 'f' AND topics.commitdate = (select max(commitdate) from topics where groupid=19387 AND moderate AND NOT deleted)"; private static final String queryPool = "SELECT topic, multiselect FROM polls WHERE id=?"; private static final String queryPollVariantsOrderById = "SELECT id, label, votes FROM polls_variants WHERE vote=? ORDER BY id"; private static final String queryPollVariantsOrderByVotes = "SELECT id, label, votes FROM polls_variants WHERE vote=? ORDER BY votes DESC, id"; private static final String queryPollUserVote = "select count(vote) from vote_users where userid=? and variant_id=?"; private static final String queryCountVotesUser = "SELECT count(vote) FROM vote_users WHERE vote=? AND userid=?"; private static final String queryCountVotesPool = "SELECT count(DISTINCT userid) FROM vote_users WHERE vote=?"; private static final String queryCountVotes = "SELECT sum(votes) as s FROM polls_variants WHERE vote=?"; private static final String updateVote = "UPDATE polls_variants SET votes=votes+1 WHERE id=? AND vote=?"; private static final String insertVoteUser = "INSERT INTO vote_users VALUES(?, ?, ?)"; private static final String insertPoll = "INSERT INTO polls (id, multiselect, topic) values (?,?,?)"; private static final String deletePoll1 = "DELETE FROM vote_users WHERE vote = ?"; private static final String deletePoll2 = "DELETE FROM polls WHERE id = ?"; private static final String deletePoll3 = "DELETE FROM polls_variants WHERE vote = ?"; private static final String queryNextPollId = "select nextval('vote_id') as voteid"; private static final String insertNewVariant = "INSERT INTO polls_variants (id, vote, label) values (nextval('votes_id'), ?, ?)"; private static final String updateVariant = "UPDATE polls_variants SET label=? WHERE id=?"; private static final String deleteVariant = "DELETE FROM polls_variants WHERE id=?"; private static final String updateMultiselect = "UPDATE polls SET multiselect=? WHERE id=?"; private JdbcTemplate jdbcTemplate; @Autowired public void setDataSource(DataSource dataSource) { jdbcTemplate = new JdbcTemplate(dataSource); } /** * ?? ?? ??. * ? ? id * * @param pollId ?? * @return ?? ?? */ private List<PollVariant> getVoteDTO(int pollId) { return jdbcTemplate.query(queryPollVariantsOrderById, new RowMapper<PollVariant>() { @Override public PollVariant mapRow(ResultSet rs, int rowNum) throws SQLException { return new PollVariant(rs.getInt("id"), rs.getString("label")); } }, pollId); } /** * - ? ?. * * @param poll ?? * @return - ? */ public int getCountUsers(Poll poll) { return jdbcTemplate.queryForInt(queryCountVotesPool, poll.getId()); } /** * - ? ?. * * @param pollId ?? * @return - ? ? (? ? ???" */ public int getVotersCount(Integer pollId) { return jdbcTemplate.queryForInt(queryCountVotes, pollId); } /** * ??, ? user ? ? ?, * ? , ?. * * @param pollId ?? * @param votes ? * @param user ? * @throws BadVoteException ? */ @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED) public void updateVotes(int pollId, int[] votes, User user) throws BadVoteException { if (jdbcTemplate.queryForInt(queryCountVotesUser, pollId, user.getId()) == 0) { for (int vote : votes) { if (jdbcTemplate.update(updateVote, vote, pollId) == 0) { throw new BadVoteException(); } jdbcTemplate.update(insertVoteUser, pollId, user.getId(), vote); } } } /** * ? * @return id ?? */ public int getCurrentPollId() { try { return jdbcTemplate.queryForInt(queryCurrentPollId); } catch (EmptyResultDataAccessException exception) { return 0; } } /** * ?. * * @return * @throws PollNotFoundException ? ? ?? */ public Poll getCurrentPoll() throws PollNotFoundException { return getPoll(getCurrentPollId()); } /** * ? . * * @param pollId ?? * @return ? * @throws PollNotFoundException ? ? ?? */ public Poll getPoll(final int pollId) throws PollNotFoundException { int currentPollId = getCurrentPollId(); SqlRowSet rs = jdbcTemplate.queryForRowSet(queryPool, pollId); if (!rs.next()) { throw new PollNotFoundException(); } return new Poll(pollId, rs.getInt("topic"), rs.getBoolean("multiselect"), pollId == currentPollId, getVoteDTO(pollId)); } /** * ? . * * @param topicId ?? * @return ?? * @throws PollNotFoundException ? ? ?? */ public Poll getPollByTopicId(int topicId) throws PollNotFoundException { try { return getPoll(jdbcTemplate.queryForInt(queryPoolIdByTopicId, topicId)); } catch (EmptyResultDataAccessException exception) { throw new PollNotFoundException(); } } /** * ? ? ? * * @param poll ? * @return ? ?? ? */ public ImmutableList<PollVariantResult> getPollVariants(Poll poll) { return getPollVariants(poll, Poll.ORDER_ID, null); } /** * ? ? ? * * @param poll ?? * @param order ? ? Poll.ORDER_ID Poll.ORDER_VOTES * @param user ? ? * @return ? ?? ? */ public ImmutableList<PollVariantResult> getPollVariants(Poll poll, int order, final User user) { final List<PollVariantResult> variants = new ArrayList<>(); String query; switch (order) { case Poll.ORDER_ID: query = queryPollVariantsOrderById; break; case Poll.ORDER_VOTES: query = queryPollVariantsOrderByVotes; break; default: throw new RuntimeException("Oops!? order=" + order); } jdbcTemplate.query(query, new RowCallbackHandler() { @Override public void processRow(ResultSet resultSet) throws SQLException { int id = resultSet.getInt("id"); String label = resultSet.getString("label"); int votes = resultSet.getInt("votes"); boolean voted = false; if (user != null && jdbcTemplate.queryForInt(queryPollUserVote, user.getId(), resultSet.getInt("id")) != 0) { voted = true; } variants.add(new PollVariantResult(id, label, votes, voted)); } }, poll.getId()); return ImmutableList.copyOf(variants); } /** * ?. * * @param pollList - ? * @param multiSelect - true ? ? ? * @param msgid - . */ // call in @Transactional public void createPoll(List<String> pollList, boolean multiSelect, int msgid) { final int voteid = getNextPollId(); jdbcTemplate.update(insertPoll, voteid, multiSelect, msgid); try { final Poll poll = getPoll(voteid); for (String variant : pollList) { if (variant.trim().isEmpty()) { continue; } addNewVariant(poll, variant); } } catch (PollNotFoundException e) { throw new RuntimeException(e); } } /** * ?? * * @return ?? */ private int getNextPollId() { return jdbcTemplate.queryForInt(queryNextPollId); } /** * ?. * * @param poll ?? */ @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED) public void deletePoll(Poll poll) { jdbcTemplate.update(deletePoll1, poll.getId()); jdbcTemplate.update(deletePoll2, poll.getId()); jdbcTemplate.update(deletePoll3, poll.getId()); } /** * ?. * * @param poll ?? * @param label - */ public void addNewVariant(Poll poll, String label) { jdbcTemplate.update(insertNewVariant, poll.getId(), label); } /** * ??. * * @param var ?? * @param label ? */ public void updateVariant(PollVariant var, String label) { if (var.getLabel().equals(label)) { return; } jdbcTemplate.update(updateVariant, label, var.getId()); } /** * ?? * * @param variant ?? */ public void removeVariant(PollVariant variant) { jdbcTemplate.update(deleteVariant, variant.getId()); } /** * ? ? * ALERT: Transactional , ? Transactional * @param poll ? * @param multiselect */ public void updateMultiselect(Poll poll, boolean multiselect) { jdbcTemplate.update(updateMultiselect, multiselect, poll.getId()); } }