Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package airport.database.services.statistics; import airport.database.dispatcher.airplane.FlightDaoImpl; import airport.database.services.users.User; import java.sql.ResultSet; import java.sql.SQLException; import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.stereotype.Repository; /** * * @author mcdoker */ @Repository public class StatisticsDaoImpl implements StatisticsDao { private final static Logger LOG = Logger.getLogger(StatisticsDaoImpl.class); @Autowired private NamedParameterJdbcTemplate jdbcTemplate; private final static String SQL_QUERY_SELECT_ALL = "SELECT sum(amount_flew_plane) as amount_flew_plane, sum(amount_taken_off_plane) as amount_taken_off_plane,\n" + " sum(amount_ready_taken_off_plane) as amount_ready_taken_off_plane, sum(amount_lended_plane) as amount_lended_plane,\n" + " sum(amount_send_message) as amount_send_message, sum(amount_entry) as amount_entry, \n" + " sum(amount_seconds_online) as amount_seconds_online\n" + " FROM statistics_user"; private final static String SQL_QUERY_SELECT_FOR_USER = "SELECT amount_flew_plane, amount_taken_off_plane, amount_ready_taken_off_plane, \n" + " amount_lended_plane, amount_send_message, amount_entry, amount_seconds_online\n" + " FROM statistics_user WHERE user_id = (SELECT user_id FROM users_online WHERE id_session = :id)"; private final static String SQL_QUERY_INC_AMOUNT_FLEW_PLANE = "UPDATE statistics_user\n" + " SET amount_flew_plane= amount_flew_plane + 1" + " WHERE user_id = ANY (SELECT user_id FROM users_online)"; private final static String SQL_QUERY_INC_AMOUNT_TAKEN_OFF_PLANE = "UPDATE statistics_user\n" + " SET amount_taken_off_plane= amount_taken_off_plane + 1" + " WHERE user_id = (SELECT user_id FROM users_online WHERE id_session = :id)"; private final static String SQL_QUERY_INC_AMOUNT_READY_TAKEN_OFF_PLANE = "UPDATE statistics_user\n" + " SET amount_ready_taken_off_plane= amount_ready_taken_off_plane + 1" + " WHERE user_id = ANY (SELECT user_id FROM users_online)"; private final static String SQL_QUERY_INC_AMOUNT_LENDED_PLANE = "UPDATE statistics_user\n" + " SET amount_lended_plane= amount_lended_plane + 1" + " WHERE user_id = (SELECT user_id FROM users_online WHERE id_session = :id)"; private final static String SQL_QUERY_INC_AMOUNT_SEND_MESSAGE = "UPDATE statistics_user\n" + " SET amount_send_message=amount_send_message + 1 " + " WHERE user_id = (SELECT user_id FROM users_online WHERE id_session = :id)"; private final static String SQL_QUERY_INC_AMOUNT_ENTRY = "UPDATE statistics_user\n" + " SET amount_entry=amount_entry+1" + " WHERE user_id = (SELECT user_id FROM users_online WHERE id_session = :id)"; private final static String SQL_QUERY_INC_SECONDS_ONLINE = "UPDATE statistics_user\n" + " SET amount_seconds_online= :amountSeconds" + " WHERE user_id = (SELECT user_id FROM users_online WHERE id_session = :id)"; @Override public Statistics getStatisticsUser(User user) { SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(user); return jdbcTemplate.queryForObject(SQL_QUERY_SELECT_FOR_USER, parameterSource, new StatisticsRowMapper()); } @Override public Statistics getStatisticsAll() { return jdbcTemplate.getJdbcOperations().queryForObject(SQL_QUERY_SELECT_ALL, new StatisticsRowMapper()); } @Override public void incAmountFlewPlane() { jdbcTemplate.getJdbcOperations().update(SQL_QUERY_INC_AMOUNT_FLEW_PLANE); } @Override public void incAmountTakenOffPlane(User user) { SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(user); jdbcTemplate.update(SQL_QUERY_INC_AMOUNT_TAKEN_OFF_PLANE, parameterSource); } @Override public void incAmountReadyTakenOffPlane() { jdbcTemplate.getJdbcOperations().update(SQL_QUERY_INC_AMOUNT_READY_TAKEN_OFF_PLANE); } @Override public void incAmoubtLendedPlane(User user) { SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(user); jdbcTemplate.update(SQL_QUERY_INC_AMOUNT_LENDED_PLANE, parameterSource); } @Override public void incAmountSendMessage(User user) { SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(user); jdbcTemplate.update(SQL_QUERY_INC_AMOUNT_SEND_MESSAGE, parameterSource); } @Override public void incAmountSecondsOnline(User user, int amountSeconds) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("amountSeconds", amountSeconds); parameterSource.addValue("id", user.getId()); jdbcTemplate.update(SQL_QUERY_INC_SECONDS_ONLINE, parameterSource); } @Override public void incAmountEntry(User user) { SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(user); jdbcTemplate.update(SQL_QUERY_INC_AMOUNT_ENTRY, parameterSource); } private static class StatisticsRowMapper implements RowMapper<Statistics> { private final static String ARGUMENT_AMOUNT_LENDED_PLANE = "amount_lended_plane"; private final static String ARGUMENT_AMOUNT_ENTRY = "amount_entry"; private final static String ARGUMENT_AMOUNT_FLEW_PLANE = "amount_flew_plane"; private final static String ARGUMENT_AMOUNT_READY_TAKE_OFF_PLANE = "amount_ready_taken_off_plane"; private final static String ARGUMENT_AMOUNT_SECONDS_ONLINE = "amount_seconds_online"; private final static String ARGUMENT_AMOUNT_SEND_MESSAGE = "amount_send_message"; private final static String ARGUMENT_AMOUNT_TAKEN_OFF_PLANE = "amount_taken_off_plane"; @Override public Statistics mapRow(ResultSet rs, int rowNum) throws SQLException { Statistics statistics = new Statistics(); statistics.setAmountLendedPlane(rs.getInt(ARGUMENT_AMOUNT_LENDED_PLANE)); statistics.setAmountEntry(rs.getInt(ARGUMENT_AMOUNT_ENTRY)); statistics.setAmountFlewPlane(rs.getInt(ARGUMENT_AMOUNT_FLEW_PLANE)); statistics.setAmountReadyTakenOffPlane(rs.getInt(ARGUMENT_AMOUNT_READY_TAKE_OFF_PLANE)); statistics.setAmountSecondsOnline(rs.getInt(ARGUMENT_AMOUNT_SECONDS_ONLINE)); statistics.setAmountSendMessage(rs.getInt(ARGUMENT_AMOUNT_SEND_MESSAGE)); statistics.setAmountTakenOffPlane(rs.getInt(ARGUMENT_AMOUNT_TAKEN_OFF_PLANE)); return statistics; } } }