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.users; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.util.ArrayList; import java.util.Date; import java.util.GregorianCalendar; import java.util.List; import org.apache.log4j.Logger; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.EmptyResultDataAccessException; 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 UsersDaoOnlineImpl implements UsersOnlineDao { private final static Logger LOG = Logger.getLogger(UsersDaoOnlineImpl.class); @Autowired private NamedParameterJdbcTemplate jdbcTemplate; private final static String SQL_QUERY_ADD_USER = "INSERT INTO users_online (user_id, last_visit, id_session) " + "VALUES ((SELECT user_id FROM users WHERE login = ?), ?, ?)"; private final static String SQL_QUERY_EXIST_USER = "SELECT COUNT(*)" + "FROM users_online " + "WHERE id_session = :id"; private final static String SQL_QUERY_EXIST_USERS = "SELECT COUNT(*) " + "FROM users_online"; private final static String SQL_QUERY_DELETE_ALL_USERS_ONLINE = "DELETE FROM users_online"; private final static String SQL_QUERY_DELETE_CURRENT_USER = "DELETE FROM users_online\n" + " WHERE id_session = :id"; private final static String SQL_QUERY_UPDATE_LAS_VISIT = "UPDATE users_online\n" + " SET last_visit=:last_visit" + " WHERE id_session=:id_session"; private final static String SQL_QUERY_DELETE_SOUR_USER = "DELETE from users_online " + "WHERE EXTRACT(EPOCH FROM AGE(:difference, last_visit)) > :limit"; private final static String SQL_QUERY_SELECT_SOUR_USER = "SELECT u.login, uo.id_session FROM users_online uo, users u " + "WHERE u.user_id = uo.user_id AND EXTRACT(EPOCH FROM AGE(:difference, last_visit)) > :limit"; private final static String SQL_QUERY_SELECT_ALL = "SELECT u.login FROM users_online uo, users u " + "WHERE u.user_id = uo.user_id"; private final static String SQL_QUERY_SELECT_USER_ONLINE_SECOND = "SELECT EXTRACT(EPOCH FROM AGE(:difference, last_visit)) from users_online " + "WHERE id_session = :id"; public UsersDaoOnlineImpl() { } @Override public void deleteAllUser() { jdbcTemplate.getJdbcOperations().update(SQL_QUERY_DELETE_ALL_USERS_ONLINE); if (LOG.isInfoEnabled()) { LOG.info("delete all users online"); } } @Override public void addUser(User user) { try { jdbcTemplate.getJdbcOperations().update(SQL_QUERY_ADD_USER, user.getLogin(), new Timestamp(new GregorianCalendar().getTimeInMillis()), user.getId()); } catch (Throwable e) { //? ? ? ?? SPRING JDBC TEMPLATE } if (LOG.isInfoEnabled()) { LOG.info("add user online. User : " + user); } } @Override public void updateTimer(User user) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("last_visit", new Timestamp(new Date().getTime())); parameterSource.addValue("id_session", user.getId()); jdbcTemplate.update(SQL_QUERY_UPDATE_LAS_VISIT, parameterSource); if (LOG.isInfoEnabled()) { LOG.info("update time of user. User : " + user); } } @Override public void deleteUser(User user) { SqlParameterSource parameterUser = new BeanPropertySqlParameterSource(user); jdbcTemplate.update(SQL_QUERY_DELETE_CURRENT_USER, parameterUser); if (LOG.isInfoEnabled()) { LOG.info("delete user from online. User : " + user); } } @Override public boolean isThere(User user) { SqlParameterSource parameterUser = new BeanPropertySqlParameterSource(user); int countDispatcher = jdbcTemplate.queryForObject(SQL_QUERY_EXIST_USER, parameterUser, Double.class) .intValue(); boolean result = countDispatcher > 0; if (LOG.isInfoEnabled()) { if (result) { LOG.info("User is exist. User : " + user); } else { LOG.info("User isn't exist. User : " + user); } } return result; } @Override public void deleteSourUser(Date timeDifference, int secondsLimit) { MapSqlParameterSource parametrQuery = new MapSqlParameterSource(); parametrQuery.addValue("difference", new Timestamp(timeDifference.getTime())); parametrQuery.addValue("limit", secondsLimit); jdbcTemplate.update(SQL_QUERY_DELETE_SOUR_USER, parametrQuery); if (LOG.isInfoEnabled()) { LOG.info("delete user which not online. Date : " + timeDifference + ". SecondsLimit : " + secondsLimit); } } @Override public boolean areThere() { boolean result = jdbcTemplate.getJdbcOperations().queryForObject(SQL_QUERY_EXIST_USERS, Integer.class) > 0; if (LOG.isInfoEnabled()) { if (result) { LOG.info("Users are exist"); } else { LOG.info("Users aren't exist"); } } return result; } @Override public List<User> getUsersSour(Date timeDifference, int secondsLimit) { MapSqlParameterSource parametrQuery = new MapSqlParameterSource(); parametrQuery.addValue("difference", new Timestamp(timeDifference.getTime())); parametrQuery.addValue("limit", secondsLimit); List<User> resultUsers; try { resultUsers = jdbcTemplate.query(SQL_QUERY_SELECT_SOUR_USER, parametrQuery, new UserSourMapper()); } catch (EmptyResultDataAccessException e) { if (LOG.isInfoEnabled()) { LOG.info("Users aren't exist which not online"); } return new ArrayList<>(); } if (LOG.isInfoEnabled()) { LOG.info("get users which not online. Users not online: " + resultUsers); } return resultUsers; } @Override public List<User> getUsersOnline() { List<User> resultUsers; try { resultUsers = jdbcTemplate.getJdbcOperations().query(SQL_QUERY_SELECT_ALL, new UserAllMapper()); } catch (EmptyResultDataAccessException e) { if (LOG.isInfoEnabled()) { LOG.info("Users online aren't exist."); } return new ArrayList<>(); } if (LOG.isInfoEnabled()) { LOG.info("Users online are exist. Result users : " + resultUsers); } return resultUsers; } private static class UserSourMapper implements RowMapper<User> { private static final String ARGUMENT_LOGIN = "login"; private static final String ARGUMENT_ID_SESSION = "id_session"; @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setLogin(rs.getString(ARGUMENT_LOGIN)); user.setId(rs.getString(ARGUMENT_ID_SESSION)); return user; } } private static class UserAllMapper implements RowMapper<User> { private static final String ARGUMENT_LOGIN = "login"; @Override public User mapRow(ResultSet rs, int rowNum) throws SQLException { User user = new User(); user.setLogin(rs.getString(ARGUMENT_LOGIN)); return user; } } }