airport.database.services.users.UsersDaoOnlineImpl.java Source code

Java tutorial

Introduction

Here is the source code for airport.database.services.users.UsersDaoOnlineImpl.java

Source

/*
 * 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;
        }

    }

}