3
\$\begingroup\$

Project structure without code:
project structure without code

Packages:

packages

Classes in packages:

first part
second part

In ConfigConstants I store path to folder, where I save pictures. Also in WebConstants I store URL of Servlet.
Here is mySQL script for creating db:

drop database meme_exchanger;
CREATE SCHEMA `meme_exchanger` DEFAULT CHARACTER SET utf8 ;

use meme_exchanger;

create table users(
    id int auto_increment primary key,
    name varchar(30) not null unique,
    password varchar(20) not null,
    access_level_id int references access_levels.id,
    creation_date date not null
);

create table users_banned(
    id int auto_increment primary key,
    user_id int references users.id,
    _from date not null,
    _to date not null,
    removed boolean default false,
    removed_date date default null
);

create table access_levels(
    id int auto_increment primary key,
    name varchar(20) not null
);

create table posts(
    id int auto_increment primary key,
    title varchar(100),
    user_id int references users.id,
    publish_date date not null,
    picture_name varchar(50) not null
);

create table posts_banned(
    id int auto_increment primary key,
    post_id int references posts.id,
    _from date not null,
    _to date not null,
    removed boolean default false,
    removed_date date default null
);

create table post_likes(
    id int auto_increment primary key,
    post_id int references posts.id,
    user_id int references users.id,
    date date not null
);

create table post_comments(
    id int auto_increment primary key,
    post_id int references posts.id,
    user_id int references users.id,
    text varchar(500) not null,
    date date not null
);

create table post_comments_banned(
    id int auto_increment primary key,
    comment_id int references post_comments.id,
    _from date not null,
    _to date not null,
    removed boolean default false,
    removed_date date default null
);


create table user_ip_adresses(
    id int auto_increment primary key,
    user_id int references users.id,
    name varchar(15)
);

create table user_id_adresses_banned(
    id int auto_increment primary key,
    ip_adress_id int references user_ip_addresses.id,
    _from date not null,
    _to date not null,
    removed boolean default false,
    removed_date date default null
);

and creating admin account:

insert into access_levels
(name)
VALUES
    ('CommonUser'),
    ('Admin');
    
insert into users
(name, password, access_level_id, creation_date)
VALUES
    ('root', '123321a', 2, curdate());

To see code on GitHub.

It is my first project on Spring MVC(used Tomcat 7.0) and I think I did many design and coding mistakes. Help me to find them, please.

I'll leave here some important files from my project:

AdminToolsController:

@Controller
public class AdminToolsController {
    
    @SuppressWarnings("deprecation")
    Date toForeverDate = new Date(3000, 1, 1); 
    
    @Autowired
    WebUtils webUtils;
    @Autowired
    IUserDAO userDAO;
    @Autowired
    IPostDAO postDAO;
    @Autowired
    MessageSource messageSource;
    
    @RequestMapping(value = {
            "/" + adminToolsMenuURL + "/" + adminToolsMenuUsersBanURL, 
            "/" + adminToolsMenuURL + "/" + adminToolsMenuPostsBanURL,
            "/" + adminToolsMenuURL + "/" + adminToolsMenuPostCommentsBanURL,
            "/" + adminToolsMenuURL + "/" + adminToolsMenuIPAddressesBanURL}, 
            method = RequestMethod.GET)
    public Object entitiesBanManagement(
            HttpServletRequest request,
            Locale locale) {
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null ||
            (redirectTo = webUtils.adminCheck(request, messageSource, locale)) != null ||
            (redirectTo = webUtils.userBanCheck(request, messageSource, locale)) != null ||
            (redirectTo = webUtils.ipAddressBanCheck(request, messageSource, locale)) != null) {
            return redirectTo;
        }       
        
        
        if(request.getServletPath().endsWith(adminToolsMenuUsersBanURL)) {
            request.setAttribute(entitiesBanManagementPageTypeOfEntityParam, TypesOfEntity.USERS);
            request.setAttribute(typeOfIdentificatorParamName, TypesOfIdentificator.LOGIN);
            request.setAttribute(entitiesBanManagementPageTitleAttrName, messageSource.getMessage("UsersBanManagement", null, locale));
            request.setAttribute(entitiesBanManagementPageIdentificatorNameAttrName, messageSource.getMessage("Login", null, locale));
        } else if(request.getServletPath().endsWith(adminToolsMenuPostsBanURL)) {
            request.setAttribute(entitiesBanManagementPageTypeOfEntityParam, TypesOfEntity.POSTS);
            request.setAttribute(typeOfIdentificatorParamName, TypesOfIdentificator.ID);
            request.setAttribute(entitiesBanManagementPageTitleAttrName, messageSource.getMessage("PostsBanManagement", null, locale));
            request.setAttribute(entitiesBanManagementPageIdentificatorNameAttrName, messageSource.getMessage("PostID", null, locale));
        } else if(request.getServletPath().endsWith(adminToolsMenuPostCommentsBanURL)) {
            request.setAttribute(entitiesBanManagementPageTypeOfEntityParam, TypesOfEntity.POST_COMMENTS);
            request.setAttribute(typeOfIdentificatorParamName, TypesOfIdentificator.ID);
            request.setAttribute(entitiesBanManagementPageTitleAttrName, messageSource.getMessage("PostCommentsBanManagement", null, locale));
            request.setAttribute(entitiesBanManagementPageIdentificatorNameAttrName, messageSource.getMessage("CommentID", null, locale));
        } else if(request.getServletPath().endsWith(adminToolsMenuIPAddressesBanURL)){
            request.setAttribute(entitiesBanManagementPageTypeOfEntityParam, TypesOfEntity.IP_ADDRESSES);
            request.setAttribute(typeOfIdentificatorParamName, TypesOfIdentificator.ID);
            request.setAttribute(entitiesBanManagementPageTitleAttrName, messageSource.getMessage("IPAddressesBanManagement", null, locale));
            request.setAttribute(entitiesBanManagementPageIdentificatorNameAttrName, messageSource.getMessage("IPAddrID", null, locale)); // TODO: show user ip addresses before
        } else {
            throw new IllegalStateException("Wrong URL");
        }
        return entitiesBanManagementPageName;
    }
    
    @RequestMapping(value = "/" + adminToolsMenuURL + "/" + banURL, method = RequestMethod.POST)
    public Object ban(
            HttpServletRequest request,
            Locale locale,
            @RequestParam(identificatorParamName) String identificator,
            @RequestParam(typeOfIdentificatorParamName) String typeOfIdentificator,
            @RequestParam(entitiesBanManagementPageToDateParam) String toDateString,
            @RequestParam(value = entitiesBanManagementPageForeverCheckboxParam, required = false) String foreverCheckbox,
            @RequestParam(entitiesBanManagementPageTypeOfEntityParam) String typeOfEntity) throws ParseException {
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null ||
                (redirectTo = webUtils.adminCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.userBanCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.ipAddressBanCheck(request, messageSource, locale)) != null) {
            return redirectTo;
        }
        
        String banMenuURL = null;
        IBanner banner = null;
        IExistanceChecker existanceChecker = null;
        TypesOfEntity enumTypeOfEnity = null;
        TypesOfIdentificator enumTypeOfIdentificator = null;
        if(TypesOfEntity.contains(typeOfEntity)) {
            enumTypeOfEnity = TypesOfEntity.getByString(typeOfEntity);
            banner = chooseBanner(enumTypeOfEnity);
            banMenuURL = chooseBanMenuURL(enumTypeOfEnity);
        } else {
            return illegalEntityTypeRedirect(typeOfEntity, request, locale);
        }
        if(TypesOfIdentificator.contains(typeOfIdentificator)) {
            enumTypeOfIdentificator = TypesOfIdentificator.getByString(typeOfIdentificator);
            existanceChecker = chooseEntityExistanceChecker(enumTypeOfEnity, enumTypeOfIdentificator);
        } else {
            return illegalIdentificatorTypeRedirect(typeOfIdentificator, request, locale);
        }
        try {
            if(!existanceChecker.exists(identificator)) {
                return entityDoesntExistRedirect(banMenuURL, identificator, request, locale);
            }
            
            Date now = new Date();
            boolean forever = webUtils.getHTMLCheckboxValue(foreverCheckbox);
            if(forever) {
                banner.ban(now, toForeverDate, identificator);
            } else {
                try {
                    Date toDate = htmlDateFormater.parse(toDateString);
                    if(now.after(toDate)) {
                        return wrongToDateRedirect(banMenuURL, now, toDate, request, locale); // TODO: not generic message?
                    }
                    banner.ban(now, toDate, identificator);
                } catch(ParseException ex) {
                    return maybeDidntSetDateRedirect(banMenuURL, identificator, request, locale);
                }
            }
            return new RedirectView("/" + adminToolsMenuURL + "/" + banMenuURL, true);
        } catch(IllegalArgumentException ex) {
            return illegalIdentificatorRedirect(banMenuURL, identificator, request, locale);
        }
        
        
    }

    @RequestMapping(value =  "/" + adminToolsMenuURL + "/" + getBansURL, method = RequestMethod.GET)
    public Object getBansUsingJSGet(
            HttpServletRequest request,
            Locale locale,
            @RequestParam(countFromParamName) int from,
            @RequestParam(countToParamName) int to,
            @RequestParam(bansFirstIDFromFirstSetParamName) Integer idOfFirstBan,
            @RequestParam(identificatorParamName) String identificator,
            @RequestParam(typeOfIdentificatorParamName) String typeOfIdentificator,
            @RequestParam(entitiesBanManagementPageTypeOfEntityParam) String typeOfEntity) {
        String loginFromCookies = webUtils.getLoginFromCookies(request);
        if(webUtils.isLoggedUsingCookies(request) != LoginStatus.LOGGED) {
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("NotLogonMsg", new Object[] {loginFromCookies}, locale));
            return failedPageName;
        }
        if(!userDAO.isAdmin(loginFromCookies)) {
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("YouAreNotAdmin", null, locale));
            return failedPageName;
        }
        if(userDAO.isBannedUser(loginFromCookies)) {
            DateInterval banDateInterval = userDAO.getLastUserBanDateInterval(loginFromCookies);
            Date fromDate = null, toDate = null;
            if(banDateInterval != null) {
                fromDate = banDateInterval.getFromDate();
                toDate = banDateInterval.getToDate();
            }
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("UserWithLoginBannedMsg", new Object[] {loginFromCookies, fromDate, toDate},  locale));
            return failedPageName;
        }
        if(userDAO.isBannedUserIPAddressByLogin(webUtils.getLoginFromCookies(request))) {
            DateInterval banDateInterval = userDAO.getLastUserIPAddressBanDateInterval(loginFromCookies);
            IPAddress ipAddress = userDAO.getIPAddressByLogin(loginFromCookies);
            Date fromDate = null, toDate = null;
            if(banDateInterval != null) {
                fromDate = banDateInterval.getFromDate();
                toDate = banDateInterval.getToDate();
            }
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("IPAddressBannedMsg", new Object[] {ipAddress == null ? null : ipAddress.getName(), fromDate, toDate},  locale));
            return failedPageName;
        }
        
        
        IBansGetter bansGetter = null;
        IExistanceChecker banExistanceChecker = null;
        TypesOfEntity enumTypeOfEnity = null;
        TypesOfIdentificator enumTypeOfIdentificator = null;
        if(TypesOfEntity.contains(typeOfEntity)) {
            enumTypeOfEnity = TypesOfEntity.getByString(typeOfEntity);
            bansGetter = chooseBansGetter(enumTypeOfEnity);
        } else {
            return illegalEntityTypeRedirect(typeOfEntity, request, locale);
        }
        if(TypesOfIdentificator.contains(typeOfIdentificator)) {
            enumTypeOfIdentificator = TypesOfIdentificator.getByString(typeOfIdentificator);
            banExistanceChecker = chooseEntityExistanceChecker(enumTypeOfEnity, enumTypeOfIdentificator);
        } else {
            return illegalIdentificatorTypeRedirect(typeOfIdentificator, request, locale);
        }
        
        try {
            if(!banExistanceChecker.exists(identificator)) {
                request.getSession().setAttribute(
                        failedPageErrorMessageAttrName, 
                        messageSource.getMessage("EntityDoesntExistsMsg", new Object[] {identificator}, locale)); 
                return failedPageName;
            }
            
            boolean isFirstSet = idOfFirstBan == null;
            int countOfNewBeforeFirst = 0;
            if(!isFirstSet) {
                countOfNewBeforeFirst = bansGetter.getBansCountBefore(idOfFirstBan);
            }
            System.out.println(identificator + " " + from + " " + to + " " + countOfNewBeforeFirst);
            List<Ban> bans = bansGetter.getBans(identificator, from + countOfNewBeforeFirst, to + countOfNewBeforeFirst);
            request.setAttribute(getBansPageBansAttrName, bans);
            request.setAttribute(getBansPageWhenSwitchedURLAttrName, changeBanStateURL);
            request.setAttribute(entitiesBanManagementPageTypeOfEntityParam, enumTypeOfEnity);
            request.setAttribute(isFirstSetAttrName, isFirstSet);
            return getBansPage;
        } catch(IllegalArgumentException ex) {
            request.getSession().setAttribute(
                    failedPageErrorMessageAttrName,
                    messageSource.getMessage(
                            "IllegalIdentificatorMsg", 
                            new Object[] {identificator}, 
                            locale));
            return failedPageName;
        }
    }

    @RequestMapping(value = "/" + changeBanStateURL, method=RequestMethod.POST)
    public Object changeBanState(
            HttpServletRequest request,
            Locale locale,
            @RequestParam(value=getBansPageDoActiveParamName,required=false) String doActive,
            @RequestParam(identificatorParamName) String banIdentificator,
            @RequestParam(entitiesBanManagementPageTypeOfEntityParam) String typeOfEntity) {
        System.out.println(banIdentificator);
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null ||
                (redirectTo = webUtils.adminCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.userBanCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.ipAddressBanCheck(request, messageSource, locale)) != null) {
            return redirectTo;
        }
        
        String banMenuURL = null;
        IBanStateChanger banStateChanger = null;
        IExistanceChecker banExistanceChecker = null;
        if(TypesOfEntity.contains(typeOfEntity)) {
            TypesOfEntity enumTypeOfEnity = TypesOfEntity.getByString(typeOfEntity);
            banMenuURL = chooseBanMenuURL(enumTypeOfEnity);
            banExistanceChecker = chooseEntityBanExistanceChecker(enumTypeOfEnity, TypesOfIdentificator.ID);
            banStateChanger = chooseBanStateChanger(enumTypeOfEnity, TypesOfIdentificator.ID);
        } else {
            return illegalEntityTypeRedirect(typeOfEntity, request, locale);
        }
        
        try {
            if(!banExistanceChecker.exists(banIdentificator)) {
                return entityDoesntExistRedirect(banMenuURL, banIdentificator, request, locale);
            }
            boolean isActive = doActive == null ? true : false;
            int changedCount = banStateChanger.changeBanState(isActive, banIdentificator);
            if(changedCount != 1) {
                System.out.println("Changed " + changedCount + " bans instead of 1");
            }
            return new RedirectView("/" + adminToolsMenuURL + "/" + banMenuURL, true);
        } catch(IllegalArgumentException ex) {
            return illegalIdentificatorRedirect(banMenuURL, banIdentificator, request, locale);
        }
    }
    
    @RequestMapping(value = "/" + adminToolsMenuURL + "/" + getIPAddressByUserURL, method=RequestMethod.POST)
    public Object getIPAddress(
            HttpServletRequest request,
            Locale locale,
            @RequestParam(typeOfIdentificatorParamName) String typeOfIdentificator,
            @RequestParam(identificatorParamName) String identificator) {
        String loginFromCookies = webUtils.getLoginFromCookies(request);
        if(webUtils.isLoggedUsingCookies(request) != LoginStatus.LOGGED) {
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("NotLogonMsg", new Object[] {loginFromCookies}, locale));
            return failedPageName;
        }
        if(!userDAO.isAdmin(loginFromCookies)) {
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("YouAreNotAdmin", null, locale));
            return failedPageName;
        }
        if(userDAO.isBannedUser(loginFromCookies)) {
            DateInterval banDateInterval = userDAO.getLastUserBanDateInterval(loginFromCookies);
            Date from = null, to = null;
            if(banDateInterval != null) {
                from = banDateInterval.getFromDate();
                to = banDateInterval.getToDate();
            }
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("UserWithLoginBannedMsg", new Object[] {loginFromCookies, from, to},  locale));
            return failedPageName;
        }
        if(userDAO.isBannedUserIPAddressByLogin(webUtils.getLoginFromCookies(request))) {
            DateInterval banDateInterval = userDAO.getLastUserIPAddressBanDateInterval(loginFromCookies);
            IPAddress ipAddress = userDAO.getIPAddressByLogin(loginFromCookies);
            Date from = null, to = null;
            if(banDateInterval != null) {
                from = banDateInterval.getFromDate();
                to = banDateInterval.getToDate();
            }
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("IPAddressBannedMsg", new Object[] {ipAddress == null ? null : ipAddress.getName(), from, to},  locale));
            return failedPageName;
        }
        TypesOfIdentificator enumTypeOfIdentificator = null;
        IIPAddressGetter ipAddressGetter = null;
        if(TypesOfIdentificator.contains(typeOfIdentificator)) {
            enumTypeOfIdentificator = TypesOfIdentificator.getByString(typeOfIdentificator);
            ipAddressGetter = chooseByUserIPAddressGetter(enumTypeOfIdentificator);
        } else {
            request.getSession().setAttribute(
                    failedPageErrorMessageAttrName,
                    messageSource.getMessage(
                            "IllegalIdentificatorTypeMsg", 
                            new Object[] {
                                    typeOfIdentificator, 
                                    CommonUtils.getEnumeration(TypesOfIdentificator.getTypesOfIdentificatorList(), ", ")}, 
                            locale));
            return failedPageName;
        }
        
        try {
            request.setAttribute(getIPAddressPageIPAddressAttrName, ipAddressGetter.get(identificator));
            return getIPAddressPageName;
        } catch(IllegalArgumentException ex) {
            request.getSession().setAttribute(
                    failedPageErrorMessageAttrName,
                    messageSource.getMessage(
                            "IllegalIdentificatorMsg", 
                            new Object[] {identificator}, 
                            locale));
            return failedPageName;
        }
        
    }
    
    private IIPAddressGetter chooseByUserIPAddressGetter(TypesOfIdentificator enumTypeOfIdentificator) {
        switch(enumTypeOfIdentificator) {
        case LOGIN:
            return userDAO::getIPAddressByLogin;
        default:
            throw new IllegalStateException();
        }
        
    }

    private IExistanceChecker chooseEntityExistanceChecker(TypesOfEntity typeOfEntity, TypesOfIdentificator typeOfIdentificator) {
        switch(typeOfEntity) {
        case USERS:
            switch(typeOfIdentificator) {
            case LOGIN:
                return userDAO::existsUserByLogin;
            case ID:
                return (String identificator) -> userDAO.existsUserById(Integer.parseInt(identificator));
            default:
                throw new IllegalStateException("Illegal type of entity to ban");
            }
            
        case POSTS:
            return (String identificator) -> postDAO.existsPost(Integer.parseInt(identificator));
        case POST_COMMENTS:
            return (String identificator) -> postDAO.existsComment(Integer.parseInt(identificator));
        case IP_ADDRESSES:
            return (String identificator) -> userDAO.existsIPAddressById(Integer.parseInt(identificator));
        default:
            throw new IllegalStateException("illegal type of entity to ban");
        }
    }

    private String chooseBanMenuURL(TypesOfEntity typeOfEntity) {
        switch(typeOfEntity) {
        case USERS:
            return adminToolsMenuUsersBanURL;
        case POSTS:
            return adminToolsMenuPostsBanURL;
        case POST_COMMENTS:
            return adminToolsMenuPostCommentsBanURL;
        case IP_ADDRESSES:
            return adminToolsMenuIPAddressesBanURL;
        default:
            throw new IllegalStateException("illegal type of entity to ban");
        }
    }
    
    private IBansGetter chooseBansGetter(TypesOfEntity typeOfEntity) {
        switch(typeOfEntity) {
        case USERS:
            return new IBansGetter() {
                @Override
                public List<Ban> getBans(String identificator, int from, int to) {
                    return userDAO.getUserBans(identificator, from, to);
                }
                @Override
                public int getBansCountBefore(int id) throws DataAccessException {
                    return userDAO.getUserBansCountBefore(id);
                }
            };
        case POSTS:
            return new IBansGetter() {
                @Override
                public List<Ban> getBans(String identificator, int from, int to) {
                    return postDAO.getPostBans(Integer.parseInt(identificator), from, to);
                }
                @Override
                public int getBansCountBefore(int id) throws DataAccessException {
                    return postDAO.getPostBansCountBefore(id);
                }
            };
        case POST_COMMENTS:
            return new IBansGetter() {
                @Override
                public List<Ban> getBans(String identificator, int from, int to) {
                    return postDAO.getCommentBans(Integer.parseInt(identificator), from, to);
                }
                @Override
                public int getBansCountBefore(int id) throws DataAccessException {
                    return postDAO.getCommentBansCountBefore(id);
                }
            };
        case IP_ADDRESSES:
            return new IBansGetter() {
                @Override
                public List<Ban> getBans(String identificator, int from, int to) {
                    return userDAO.getIPAddressBans(Integer.parseInt(identificator), from, to);
                }
                @Override
                public int getBansCountBefore(int id) throws DataAccessException {
                    return userDAO.getIPAddressBansCountBefore(id);
                }
            };
        default:
            throw new IllegalStateException("Illegal type of entity to ban");
        }
    }

    private IBanner chooseBanner(TypesOfEntity typeOfEntity) throws ParseException {
        switch(typeOfEntity) {
        case USERS:
            return userDAO::banUser;
        case POSTS:
            return (Date from, Date to, String identificator) -> postDAO.banPost(from, to, Integer.parseInt(identificator));
        case POST_COMMENTS:
            return (Date from, Date to, String identificator) -> postDAO.banComment(from, to, Integer.parseInt(identificator));
        case IP_ADDRESSES:
            return (Date from, Date to, String identificator) -> userDAO.banIPAddress(from, to, Integer.parseInt(identificator));
        default:
            throw new IllegalStateException("illegal type of entity to ban");
        }
    }
    
    private IExistanceChecker chooseEntityBanExistanceChecker(TypesOfEntity typeOfEntity, TypesOfIdentificator typeOfIdentificator) {
        switch(typeOfEntity) {
        case USERS:
            return (String identificator) -> userDAO.existsUserBanById(Integer.parseInt(identificator));
        case POSTS:
            return (String identificator) -> postDAO.hasPostBan(Integer.parseInt(identificator));
        case POST_COMMENTS:
            return (String identificator) -> postDAO.hasCommentBan(Integer.parseInt(identificator));
        case IP_ADDRESSES:
            return (String identificator) -> userDAO.existsIPAddressBanById(Integer.parseInt(identificator));
        default:
            throw new IllegalStateException("illegal type of entity to ban");
        }
    }
    
    private IBanStateChanger chooseBanStateChanger(TypesOfEntity typeOfEntity, TypesOfIdentificator typeOfIdentificator) {
        switch(typeOfEntity) {
        case USERS:
            return (boolean isActive, String identificator) -> userDAO.changeUserBanStateById(isActive, Integer.parseInt(identificator));
        case POSTS:
            return (boolean isActive, String identificator) -> postDAO.changePostBanState(isActive, Integer.parseInt(identificator));
        case POST_COMMENTS:
            return (boolean isActive, String identificator) -> postDAO.changeCommentBanState(isActive, Integer.parseInt(identificator));
        case IP_ADDRESSES:
            return (boolean isActive, String identificator) -> userDAO.changeIPAddressBanStateById(isActive, Integer.parseInt(identificator));
        default:
            throw new IllegalStateException("illegal type of entity to ban");
        }
    }

    private RedirectView maybeDidntSetDateRedirect(
            String adminToolURL,
            String login,
            HttpServletRequest request,
            Locale locale) {
        request.getSession().setAttribute(entitiesBanManagementPageMaybeDidntSetDateMsgAttrName, messageSource.getMessage("MaybeDidntSetDateMsg", null, locale));
        return new RedirectView("/" + adminToolsMenuURL + "/" + adminToolURL, true);
    }

    private RedirectView wrongToDateRedirect(
            String adminToolURL,
            Date now,
            Date toDate,
            HttpServletRequest request,
            Locale locale) {
        request.getSession().setAttribute(entitiesBanManagementPageWrongToDateErrorMsgAttrName, messageSource.getMessage("WrongToDateErrorMsg", new Object[] {now, dbDateFormater.format(toDate)}, locale));
        return new RedirectView("/" + adminToolsMenuURL + "/" + adminToolURL, true);
    }

    private RedirectView entityDoesntExistRedirect(
            String toolURL, 
            String identificator,
            HttpServletRequest request, 
            Locale locale) {
        request.getSession().setAttribute(entitiesBanManagementPageEntityDoesntExistMsgAttrName, messageSource.getMessage("EntityDoesntExistsMsg", new Object[] {identificator}, locale)); 
        return new RedirectView("/" + adminToolsMenuURL + "/" + toolURL, true);
    }
    
    private RedirectView illegalIdentificatorRedirect(
            String toolURL,
            String identificator,
            HttpServletRequest request, 
            Locale locale) {
        request.getSession().setAttribute(
                adminToolsMenuPageIllegalEntityTypeMsgAttrName, // TODO: think about creating name-spaces - EntitiesBanManagement.* class f.e.
                messageSource.getMessage(
                        "IllegalIdentificatorMsg", 
                        new Object[] {identificator}, 
                        locale));
        return new RedirectView("/" + adminToolsMenuURL + "/" + toolURL, true);
    }
    
    private RedirectView illegalIdentificatorTypeRedirect(
            String typeOfIdentificator, 
            HttpServletRequest request, 
            Locale locale) {
        request.getSession().setAttribute(
                adminToolsMenuPageIllegalIdentificatorTypeMsgAttrName,
                messageSource.getMessage(
                        "IllegalIdentificatorTypeMsg", 
                        new Object[] {
                                typeOfIdentificator, 
                                CommonUtils.getEnumeration(TypesOfIdentificator.getTypesOfIdentificatorList(), ", ")}, 
                        locale));
        return new RedirectView("/" + adminToolsMenuURL, true);
    }
    
    private RedirectView illegalEntityTypeRedirect(
            String typeOfEntity,
            HttpServletRequest request, 
            Locale locale) {
        request.getSession().setAttribute(
                adminToolsMenuPageIllegalEntityTypeMsgAttrName,
                messageSource.getMessage(
                        "IllegalEntityTypeMsg", 
                        new Object[] {
                                typeOfEntity, 
                                CommonUtils.getEnumeration(TypesOfEntity.getTypesOfEntityList(), ", ")}, 
                        locale));
        return new RedirectView("/" + adminToolsMenuURL, true);
    }
}

DefaultController:

@Controller
public class DefaultController {
    
    @Autowired
    WebUtils webUtils;
    @Autowired
    IUserDAO userDAO;
    @Autowired
    IPostDAO postDAO;
    @Autowired
    IFileChecker fileChecker;
    @Autowired
    IPostTitleChecker postTitleChecker;
    @Autowired
    MessageSource messageSource;
    
    @RequestMapping(value="/" + mainURL, method = RequestMethod.GET)
    public Object main(HttpServletRequest request, Locale locale) {
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null ||
                (redirectTo = webUtils.userBanCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.ipAddressBanCheck(request, messageSource, locale)) != null) {
            return redirectTo;
        }
        request.setAttribute(isAdminAttrName, userDAO.isAdmin(webUtils.getLoginFromCookies(request)));
        return mainPageName;
    }
    
    @RequestMapping(value="/" + createPostURL, method = RequestMethod.GET)
    public Object createPost(HttpServletRequest request, Locale locale) {
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null || 
                (redirectTo = webUtils.userBanCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.ipAddressBanCheck(request, messageSource, locale)) != null) {
            return redirectTo;
        }
        int countOfPostsToday = postDAO.countOfPostsToday(webUtils.getLoginFromCookies(request));
        if(countOfPostsToday >= MAX_POSTS_COUNT_PER_DAY) { 
            if(request.getSession().getAttribute(createPostPageTooManyPostsPerDayMsgAttrName) == null) { // if didn't redirected from createPost_postForm with this error
                request.getSession().setAttribute(
                        createPostPageTooManyPostsPerDayMsgAttrName, 
                        messageSource.getMessage(
                                "TooManyPostsPerDayMsg", 
                                new Object[] {webUtils.getLoginFromCookies(request), MAX_POSTS_COUNT_PER_DAY},
                        locale));
            }
            
        }
        return createPostPageName;
    }
    
    @RequestMapping(value="/" + createPostURL, method = RequestMethod.POST)
    public Object createPost_postForm(
            HttpServletRequest request,
            @RequestParam(createPostPageTitleParamName) String title,
            @RequestParam(createPostPageFileParamName) CommonsMultipartFile file, 
            Locale locale) throws IOException {
        HttpSession session = request.getSession();
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null ||
                (redirectTo = webUtils.userBanCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.ipAddressBanCheck(request, messageSource, locale)) != null) {
            return redirectTo;
        }
        List<String> errors;
        boolean hasErrors = false;
        if(!(errors = fileChecker.getErrorsBeforeGetting(file, locale)).isEmpty()) {
            hasErrors = true;
            errors.forEach(System.out::println);
            session.setAttribute(createPostPageFileBeforeInsertErrorsAttrName, errors);
        }
        if(!(errors = postTitleChecker.getErrors(title, locale)).isEmpty()) {
            hasErrors = true;
            errors.forEach(System.out::println);
            session.setAttribute(createPostPageTitleErrorsAttrName, errors);
        }
        int countOfPostsToday = postDAO.countOfPostsToday(webUtils.getLoginFromCookies(request));
        if(countOfPostsToday >= MAX_POSTS_COUNT_PER_DAY) {
            hasErrors = true;
            session.setAttribute(
                    createPostPageTooManyPostsPerDayMsgAttrName, 
                    messageSource.getMessage(
                            "TooManyPostsPerDayMsg", 
                            new Object[] {webUtils.getLoginFromCookies(request), MAX_POSTS_COUNT_PER_DAY},
                    locale));
        }
        if(hasErrors) {
            return new RedirectView("/" + createPostURL, true);
        }
        String insertedFileName = postDAO.insertFile(file, locale);
        if(!(errors = fileChecker.getErrorsAfterGetting(insertedFileName, locale)).isEmpty()) {
            errors.forEach(System.out::println);
            session.setAttribute(createPostPageFileAfterInsertErrorsAttrName, errors);
            return new RedirectView("/" + createPostURL, true);
        }
        System.out.println(insertedFileName);
        postDAO.insertRecord(webUtils.getLoginFromCookies(request), title, insertedFileName);
        return new RedirectView("/" + createPostURL, true);
    }
    
    @RequestMapping(value="/" + feedURL, method = RequestMethod.GET)
    public Object feed(HttpServletRequest request) {
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null) {
            return redirectTo;
        }
        return feedPageName;
    }
    
    @RequestMapping(value="/" + getFeedURL, method = RequestMethod.GET)
    public Object getFeed(HttpServletRequest request,
            @RequestParam(countFromParamName) int from,
            @RequestParam(countToParamName) int to,
            @RequestParam(postsFirstIDFromFirstSetParamName) Integer idOfFirst) throws IOException {
        if(webUtils.loggedCheck(request) != null) {
            return null; // TODO: write smth in answer?
        }
        boolean isFirstSet = idOfFirst == null;
        int countOfNewBeforeFirst = 0;
        if(!isFirstSet) {
            countOfNewBeforeFirst = postDAO.getCountPostsBefore(idOfFirst);
        }
        List<FeedPostWithBanInfo> feedPostsWithBanInfo = postDAO.getFreshPostsWithBiggestBanInfo(from + countOfNewBeforeFirst, to + countOfNewBeforeFirst);
        feedPostsWithBanInfo.forEach(System.out::println);
        request.setAttribute(feedPostsWithBanInfoAttrName, feedPostsWithBanInfo);
        request.setAttribute(isFirstSetAttrName, isFirstSet);
        String loginFromCookies = webUtils.getLoginFromCookies(request);
        request.setAttribute(getFeedPageCanLeaveCommentsAttrName, canLeaveComments(loginFromCookies));
        request.setAttribute(loginAttrName, loginFromCookies);
        return getFeedPageName;
    }
    
    @RequestMapping(value="/" + createCommentURL, method = RequestMethod.POST)
    @ResponseBody
    public Object createComment(
            HttpServletRequest request,
            @RequestParam(getFeedTextOfCommentParamName) String text,
            @RequestParam(postIDParamName) int postID) {
        String loginFromCookies = webUtils.getLoginFromCookies(request);
        if(webUtils.isLoggedUsingCookies(request) != LoginStatus.LOGGED ||
                userDAO.isBannedUser(loginFromCookies)  ||
                userDAO.isBannedUserIPAddressByLogin(loginFromCookies)) {
            return "error - banned or not logged"; // TODO: to return smth?
        }
        
        if(text.length() > MAX_COMMENT_LENGTH || text.length() < MIN_COMMENT_LENGTH) {
            return "error - comment has illegal length - > " + MAX_COMMENT_LENGTH + " or < " + MIN_COMMENT_LENGTH;
        }
        if(!postDAO.existsPost(postID) || postDAO.isPostBanned(postID)) {
            return "error - post doesn't exist or banned";
        }
        if(postDAO.getCommentsCountPerDay(loginFromCookies) > MAX_COMMENTS_COUNT_PER_DAY) {
            return "too many comments per day";
        }
        
        int countCreated = postDAO.createComment(postID, loginFromCookies, text);
        if(countCreated == 0) {
            return "not created";
        }
        return "ok";
    }
    
    @RequestMapping(value="/" + imgsURL, method = RequestMethod.GET)
    public void getImg(@RequestParam(imgNameParamName) String name, HttpServletResponse response, HttpServletRequest request, Locale locale) throws IOException {
        if(webUtils.loggedCheck(request) != null ||
                webUtils.userBanCheck(request, messageSource, locale) != null) {
            return; // TODO: to return smth?
        }
        File file = new File(picturesDirectiory + File.separatorChar + name);
        if(!file.exists()) {
            System.out.println(name);
            response.sendError(404, messageSource.getMessage("ImgNotFoundMsg", new Object[] {name}, locale));
            return;
        }
        
        String postfix = name.split("\\.")[1];
        if(!PossibleIMGsSuffixes.contains(postfix.toLowerCase())) {
            response.sendError(500, messageSource.getMessage("InvalidFileTypeOnServerMsg", new Object[] {name}, locale));
        }
        response.setContentType("image/" + postfix);
        InputStream in = new FileInputStream(file);
        IOUtils.copy(in, response.getOutputStream());
    }
    
    @RequestMapping(value = "/" + getCommentsURL, method = RequestMethod.GET)
    public Object getComments(HttpServletRequest request,
            Locale locale,
            @RequestParam(countFromParamName) int from,
            @RequestParam(countToParamName) int to,
            @RequestParam(commentsFirstIDFromFirstSetParamName) Integer idOfFirstComment,
            @RequestParam(postIDParamName) int postID) {
        String loginFromCookies = webUtils.getLoginFromCookies(request);
        if(webUtils.isLoggedUsingCookies(request) != LoginStatus.LOGGED) {
            request.getSession().setAttribute(failedPageErrorMessageAttrName, messageSource.getMessage("NotLogonMsg", new Object[] {loginFromCookies}, locale));
            return failedPageName;
        }
        
        
        boolean isFirstSet = idOfFirstComment == null;
        
        int countOfNewBeforeFirst = 0;
        if(!isFirstSet) {
            countOfNewBeforeFirst = postDAO.getCommentsCountUnderPostBefore(idOfFirstComment);
        }
        List<PostCommentWithBanInfo> comments = postDAO.getFreshCommentsWithBiggestBanInfo(postID, from + countOfNewBeforeFirst, to + countOfNewBeforeFirst);
        request.setAttribute(getCommentsPageCommentsAttrName, comments);
        request.setAttribute(isFirstSetAttrName, isFirstSet);
        request.setAttribute(postIDAttrName, postID);
        System.out.println(from + " " + to + " " + countOfNewBeforeFirst);
        return getCommentsPageName;
    }
    
    @RequestMapping(value="/" + adminToolsMenuURL, method = RequestMethod.GET)
    public Object adminToolsMenu(HttpServletRequest request, Locale locale) {
        RedirectView redirectTo;
        if((redirectTo = webUtils.loggedCheck(request)) != null ||
                (redirectTo = webUtils.adminCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.userBanCheck(request, messageSource, locale)) != null ||
                (redirectTo = webUtils.ipAddressBanCheck(request, messageSource, locale)) != null) {
            return redirectTo;
        }
        return adminToolsMenuPageName;
    }
    
    @RequestMapping(value = "/" + failedURL)
    public Object failed(HttpServletRequest request) {
        return failedPageName;
    }
    
    boolean canLeaveComments(String login) {
        return !userDAO.isBannedUser(login) && !userDAO.isBannedUserIPAddressByLogin(login);
    }
}

EnterController:

@Controller
@EnableTransactionManagement
public class EnterController {
    
    @Autowired
    WebUtils webUtils;
    @Autowired
    IUserDAO userDAO;
    @Autowired
    IUserChecker userChecker;
    @Autowired
    MessageSource messageSource;
    
    @RequestMapping(value="/" + rootURL, method = RequestMethod.GET)
    public Object root(HttpServletRequest request) {
        if(webUtils.isLoggedUsingCookies(request) != LoginStatus.LOGGED) { // to do ban checks
            return rootPageName;
        }
        return new RedirectView(mainURL, true);
    }
    
    @RequestMapping(value="/" + loginURL, method = RequestMethod.GET)
    public Object login(HttpServletRequest request) {
        return loginPageName;
    }
    
    @RequestMapping(value="/" + loginURL, method = RequestMethod.POST)
    public Object login_loginForm(
            HttpServletRequest request,
            HttpSession model,
            @RequestParam(loginPageLoginParamName) String login, 
            @RequestParam(loginPagePasswordParamName) String password, 
            HttpServletResponse response,
            Locale locale) {
        if(userDAO.isWrong(login, password)) {
            model.setAttribute(
                    loginPageWrongLoginOrPasswordMsgAttrName, 
                    messageSource.getMessage("WrongLoginOrPasswordMsg", null, locale));
            return loginPageName;
        }
        RedirectView redirectTo;
        if((redirectTo = webUtils.userBanCheck(login, request, messageSource, locale)) != null || 
                (redirectTo = webUtils.ipAddressBanCheck(login, request, messageSource, locale)) != null) {
            return redirectTo;
        }
        webUtils.putUserIntoCookies(response, login, password, secondsToSaveCookie);
        return new RedirectView(mainURL, true);
    }
    
    @RequestMapping(value="/" + registrationURL, method = RequestMethod.GET)
    public Object registration(HttpServletRequest request) {
        return registrationPageName;
    }
    
    @Transactional(propagation = Propagation.REQUIRES_NEW, isolation = Isolation.SERIALIZABLE)
    @RequestMapping(value="/" + registrationURL, method = RequestMethod.POST)
    public Object registration_registationForm(
            @RequestParam(registrationPageLoginParamName) String login, 
            @RequestParam(registrationPagePasswordParamName) String password, 
            HttpServletResponse response,
            HttpServletRequest request,
            Locale locale) {
        if(userDAO.existsUserByLogin(login)) {
            request.setAttribute(
                    registrationPageLoginAlreadyExistsMsgAttrName, 
                    messageSource.getMessage("LoginAlreadyExistsMsg", null, locale));
            return registrationPageName;
        }
        List<String> errors;
        if(!(errors = userChecker.getLoginErrors(login, locale)).isEmpty()) {
            errors.forEach(System.out::println);
            request.setAttribute(registrationPageLoginErrorsAttrName, errors);
            return registrationPageName;
        }
        if(!(errors = userChecker.getPasswordErrors(password, locale)).isEmpty()) {
            errors.forEach(System.out::println);
            request.setAttribute(registrationPagePasswordErrorsAttrName, errors);
            return registrationPageName;
        }
        String ipAddress = request.getRemoteAddr();
        if(userDAO.getCountOfAccountsOfAddr(ipAddress) > MAX_ACCOUNT_COUNT_PER_ADDR) {
            request.setAttribute(
                    registrationPageTooManyAccountsMsgAttrName, 
                    messageSource.getMessage("TooManyAccountsMsg", new Object[] {MAX_ACCOUNT_COUNT_PER_ADDR}, locale));
            return registrationPageName;
        }
        
        webUtils.putUserIntoCookies(response, login, password, secondsToSaveCookie);
        
        userDAO.insertUser(login, password);
        userDAO.insertIPAddress(login, ipAddress);
        return new RedirectView(mainURL, true);
    }
    
    @RequestMapping(value="/" + logoutURL, method = RequestMethod.GET)
    public RedirectView logout(HttpServletResponse response) {
        webUtils.deleteCookies(new String[] {loginCookieName, passwordCookieName}, response);
        return new RedirectView(rootURL, true);
    }
}

MySQLPostDAO:

@Component
public class MySQLPostDAO implements IPostDAO {

    JdbcTemplate jdbcTemplate;
    @Autowired
    MessageSource messageSource;
    
    @Autowired
    public MySQLPostDAO(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }
    
    @Override
    public String insertFile(CommonsMultipartFile file, Locale locale) throws IOException, IllegalStateException, EmptyFileException { // TODO: think about it. It doesn't have any relation to MySQL(!)PostDAO
        if(!file.isEmpty()) {
            File fileToUpload = null;
            String[] splittedByDots = file.getOriginalFilename().split("\\.");
            String fileName = CommonUtils.connectTo(splittedByDots, splittedByDots.length - 1);
            String fileExtension = "." + splittedByDots[splittedByDots.length - 1].toLowerCase(); // SHOULD BE SAVED IN LOWER CASE!
            boolean foundPlace = false;
            for(int i = 0; i < 100; i++) {
                if(!(fileToUpload = new File(picturesDirectiory + File.separatorChar + fileName + Integer.toString(i) + fileExtension)).exists()) {
                    foundPlace = true;
                    break;
                }
            }
            if(!foundPlace) {
                throw new IllegalStateException(messageSource.getMessage("TooManyFilesWithNameMsg", new Object[] {MAX_FILES_WITH_SAME_NAME_COUNT}, locale));
            }
            
            fileToUpload.createNewFile();
            BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(fileToUpload));
            out.write(file.getBytes());
            out.close();
            
            return fileToUpload.getName();
        }
        throw new EmptyFileException(messageSource.getMessage("EmptyFileMsg", new Object[] {file.getOriginalFilename()}, locale));
    }

    @Override
    public void insertRecord(String login, String title, String filePath) throws DataAccessException {
        String query = MessageFormat.format(
                "INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES (?, (SELECT {5} FROM {6} WHERE {7} = ?), curdate(), ?);", 
                postsTableName, 
                postsTableTitleFieldName, 
                postsTableUserIDFieldName,
                postsTablePublishDateFieldName,
                postsTablePictureNameFieldName,
                usersTableIDFieldName,
                usersTableName,
                usersTableLoginFieldName); // TODO: to remember method with names, not numbers
        jdbcTemplate.update(query.toString(), new Object[] {title, login, filePath});
    }

    @Override
    public List<FeedPost> getFreshPosts(int countGot, int countToGet) throws DataAccessException{
        String query = MessageFormat.format(
                "SELECT {1}, {2}, (SELECT {3} FROM {4} WHERE {5} = {0}.{6}), {7}, {8} FROM {0} ORDER BY {7} DESC, {1} DESC LIMIT ?, ?",
                postsTableName,
                postsTableIDFieldName,
                postsTableTitleFieldName,
                
                usersTableLoginFieldName,
                usersTableName,
                usersTableIDFieldName,
                postsTableUserIDFieldName,
                
                postsTablePublishDateFieldName,
                postsTablePictureNameFieldName);
        try {
            return jdbcTemplate.query(query, new Object[] {countGot, countToGet}, new PostMapper());
        } catch(EmptyResultDataAccessException ex) {
            return new ArrayList<FeedPost>();
        }
    }

    @Override
    public List<FeedPostWithBanInfo> getFreshPostsWithBiggestBanInfo(int countGot, int countToGet) throws DataAccessException {
        String query = MessageFormat.format(
                //TODO: bad getting 
                "SELECT {0}.{3}, {0}.{9}, (SELECT {11} FROM {12} WHERE {13} = {0}.{10}) as {11}, {0}.{1}, {14}, ifnull({2}.{5}, true) as {5}, {2}.{6}, {2}.{7} "
                + "FROM (SELECT * FROM {0} ORDER BY {1} DESC, {3} DESC LIMIT ?, ?) as {0} "
                + "LEFT JOIN (SELECT {5}, {6}, {7}, {4} FROM {2} as pb1 WHERE {8} = "
                + "(SELECT {8} FROM {2} as pb2 WHERE {6} <= curdate() AND {7} >= curdate() AND pb1.{4} = pb2.{4} AND {7} = "
                + "(SELECT MAX({7}) FROM {2} as pb3 WHERE {6} <= curdate() AND {7} >= curdate() AND pb3.{4} = pb2.{4}) LIMIT 1)) as {2} "
                + "ON {2}.{4} = {0}.{3} ORDER BY {0}.{1} DESC, {0}.{3} DESC",
                postsTableName,
                postsTablePublishDateFieldName,
                postsBannedTableName,
                postsTableIDFieldName,
                postsBannedTablePostIDFieldName,
                postsBannedTableRemovedFieldName,
                postsBannedTableFromDateFieldName,
                postsBannedTableToDateFieldName,
                postsBannedTableIDFieldName,
                
                postsTableTitleFieldName,
                postsTableUserIDFieldName,
                usersTableLoginFieldName,
                usersTableName,
                usersTableIDFieldName,
                postsTablePictureNameFieldName);
        try {
            return jdbcTemplate.query(query, new Object[] {countGot, countToGet}, new PostWithBanInfoMapper());
        } catch(EmptyResultDataAccessException ex) {
            return new ArrayList<FeedPostWithBanInfo>();
        }
    }

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public int changePostBanState(boolean isActive, int id) throws DataAccessException {
        String query = MessageFormat.format(
                "UPDATE {0} SET {1} = " + (isActive ? "true" : "false") + ", {2} = " + (isActive ? "curdate()" : "null") + " WHERE {3} = ?",
                postsBannedTableName,
                postsBannedTableRemovedFieldName,
                postsBannedTableRemovedDateFieldName,
                postsBannedTableIDFieldName);
        return jdbcTemplate.update(query, new Object[] {id});
    }
    
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public int changeCommentBanState(boolean isActive, int id) throws DataAccessException {
        String query = MessageFormat.format(
                "UPDATE {0} SET {1} = " + (isActive ? "true" : "false") + ", {2} = " + (isActive ? "curdate()" : "null") + " WHERE {3} = ?",
                postCommentsBannedTableName,
                postCommentsBannedTableRemovedFieldName,
                postCommentsBannedTableRemovedDateFieldName,
                postCommentsBannedTableIDFieldName);
        return jdbcTemplate.update(query, new Object[] {id});
    }

    @Override
    public boolean existsPost(int id) throws DataAccessException, IllegalStateException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = ?", 
                postsTableName, 
                postsTableIDFieldName);
        
        int accountsCount = jdbcTemplate.queryForObject(query, new Object[] {id}, Integer.class);
        if(accountsCount == 1) {
            return true;
        }
        if(accountsCount == 0) {
            return false;
        }
        throw new IllegalStateException("DB unique id error"); // don't need to be localized
    }
    
    @Override
    public boolean existsComment(int id) throws DataAccessException, IllegalStateException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = ?", 
                postCommentsTableName, 
                postCommentsTableIDFieldName);
        
        int accountsCount = jdbcTemplate.queryForObject(query, new Object[] {id}, Integer.class);
        if(accountsCount == 1) {
            return true;
        }
        if(accountsCount == 0) {
            return false;
        }
        throw new IllegalStateException("DB unique id error"); // don't need to be localized
    }

    @Override
    public List<Ban> getPostBans(int id, int from, int to) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT {0}, {1}, {2}, {3}, {4} FROM {5} WHERE {6} = ? ORDER BY {1} DESC, {0} DESC LIMIT ?, ?",
                postsBannedTableIDFieldName,
                postsBannedTableFromDateFieldName,
                postsBannedTableToDateFieldName,
                postsBannedTableRemovedFieldName,
                postsBannedTableRemovedDateFieldName,
                postsBannedTableName,
                postsBannedTablePostIDFieldName);
        try {
            return jdbcTemplate.query(query, new Object[] {id, from, to} , new BanMapperForPost());
        } catch(EmptyResultDataAccessException ex) {
            return new ArrayList<Ban>();
        }
    }
    
    @Override
    public List<Ban> getCommentBans(int id, int from, int to) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT {0}, {1}, {2}, {3}, {4} FROM {5} WHERE {6} = ? ORDER BY {1} DESC, {0} DESC LIMIT ?, ?",
                postCommentsBannedTableIDFieldName,
                postCommentsBannedTableFromDateFieldName,
                postCommentsBannedTableToDateFieldName,
                postCommentsBannedTableRemovedFieldName,
                postCommentsBannedTableRemovedDateFieldName,
                postCommentsBannedTableName,
                postCommentsBannedTableCommentIDFieldName);
        try {
            return jdbcTemplate.query(query, new Object[] {id, from, to} , new BanMapperForPost());
        } catch(EmptyResultDataAccessException ex) {
            return new ArrayList<Ban>();
        }
    }



    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public int banPost(Date from, Date to, int id) throws DataAccessException {
        String query = MessageFormat.format(
                "INSERT INTO {0} ({1}, {2}, {3}) VALUES (?, ?, ?)",
                postsBannedTableName,
                postsBannedTablePostIDFieldName,
                postsBannedTableFromDateFieldName,
                postsBannedTableToDateFieldName);
        int countBanned = jdbcTemplate.update(query, new Object[] {id, dbDateFormater.format(from), dbDateFormater.format(to)});
        return countBanned;
    }
    
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public int banComment(Date from, Date to, int id) throws DataAccessException {
        String query = MessageFormat.format(
                "INSERT INTO {0} ({1}, {2}, {3}) VALUES (?, ?, ?)",
                postCommentsBannedTableName,
                postCommentsBannedTableCommentIDFieldName,
                postCommentsBannedTableFromDateFieldName,
                postCommentsBannedTableToDateFieldName);
        int countBanned = jdbcTemplate.update(query, new Object[] {id, dbDateFormater.format(from), dbDateFormater.format(to)});
        return countBanned;
    }

    @Override
    public boolean hasPostBan(int id) throws DataAccessException{
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = ?",
                postsBannedTableName,
                postsBannedTableIDFieldName);
        int bansCount = jdbcTemplate.queryForObject(query, new Object[] {id}, Integer.class);
        if(bansCount > 0) {
            return true;
        }
        return false;
    }
    
    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public boolean hasCommentBan(int id) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = ?",
                postCommentsBannedTableName,
                postCommentsBannedTableIDFieldName);
        int bansCount = jdbcTemplate.queryForObject(query, new Object[] {id}, Integer.class);
        if(bansCount > 0) {
            return true;
        }
        return false;
    }
    
    @Override
    public boolean isPostBanned(int id) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = ? AND {2} = false",
                postsBannedTableName,
                postsBannedTablePostIDFieldName,
                postsBannedTableRemovedFieldName);
        return 0 < jdbcTemplate.queryForObject(query, new Object[] {id}, Integer.class) ;
    }

    @Override
    public int countOfPostsToday(String login) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = curdate() AND {2} = (SELECT {3} FROM {4} WHERE {5} = ?)",
                postsTableName,
                postsTablePublishDateFieldName,
                postsTableUserIDFieldName,
                usersTableIDFieldName,
                usersTableName,
                usersTableLoginFieldName);
        return jdbcTemplate.queryForObject(query, new Object[] {login}, Integer.class);
    }

    @Override
    public int getCountPostsBefore(int id) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} > ?", // id should indicate order of publish!
                postsTableName,
                postsBannedTableIDFieldName);
        return jdbcTemplate.queryForObject(query, new Object[] {id}, Integer.class);
    }

    @Override
    public int getPostBansCountBefore(int banId) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} > ?",
                postsBannedTableName,
                postsBannedTableIDFieldName);
        return jdbcTemplate.queryForObject(query, new Object[] {banId}, Integer.class);
    }

    @Override
    public int getCommentBansCountBefore(int banId) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} > ?",
                postCommentsBannedTableName,
                postCommentsBannedTableIDFieldName);
        return jdbcTemplate.queryForObject(query, new Object[] {banId}, Integer.class);
    }

    @Override
    public int getCommentsCountUnderPostBefore(int idOfComment) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = (SELECT {1} FROM {0} WHERE {2} = ?) AND {1} > ?",
                postCommentsTableName,
                postCommentsTablePostIDFieldName,
                postCommentsTableIDFieldName);
        return jdbcTemplate.queryForObject(query, new Object[] {idOfComment, idOfComment}, Integer.class);
    }

    @Override
    public int getPostIDByCommentID(int id) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT {0} FROM {1} WHERE {2} = ?",
                postCommentsTablePostIDFieldName,
                postCommentsTableName,
                postCommentsTableIDFieldName);
        return jdbcTemplate.queryForObject(query, new Object[] {id}, Integer.class);
    }

    @Override
    public List<PostCommentWithBanInfo> getFreshCommentsWithBiggestBanInfo(int postID, int countGot, int countToGet) throws DataAccessException {
        String query = MessageFormat.format(
                //TODO: bad getting
                "SELECT pc1.{1}, pc1.{2}, (SELECT {8} FROM {6} WHERE {7} = pc1.{3}) as {8}, pc1.{4}, "
                + "ifnull(cb1.{10}, true) as {10}, cb1.{11}, cb1.{12} "
                + "FROM (SELECT {1}, {2}, {3}, {4} FROM {0} WHERE {5} = ? ORDER BY {4}, {1} LIMIT ?, ?) as pc1 "
                + "LEFT JOIN (SELECT {10}, {11}, {12}, {13} FROM {9} as cb1 WHERE {11} <= curdate() AND {12} >= curdate() "
                + "AND {12} = (SELECT MAX({12}) FROM {9} as cb2 WHERE {11} <= curdate() AND {12} >= curdate() AND cb1.{13} = cb2.{13}) LIMIT 1) as cb1 "
                + "ON pc1.{1} = cb1.{13} ORDER BY pc1.{4}, pc1.{1}",
                postCommentsTableName, // 0
                postCommentsTableIDFieldName, // 1
                postCommentsTableTextFieldName, // 2
                postCommentsTableUserIDFieldName, // 3
                postCommentsTableDateFieldName, // 4
                postCommentsTablePostIDFieldName, // 5
                
                usersTableName, // 6
                usersTableIDFieldName, // 7
                usersTableLoginFieldName, // 8
                
                postCommentsBannedTableName, // 9
                postCommentsBannedTableRemovedFieldName, // 10
                postCommentsBannedTableFromDateFieldName, // 11
                postCommentsBannedTableToDateFieldName, // 12
                postCommentsBannedTableCommentIDFieldName // 13
                );
        
        try {
            return jdbcTemplate.query(query, new Object[] {postID, countGot, countToGet}, new PostCommentWithBanInfoMapper());
        } catch(EmptyResultDataAccessException ex) {
            return new ArrayList<PostCommentWithBanInfo>();
        }
    }

    @Override
    public int createComment(int postID, String login, String text) throws DataAccessException {
        String query = MessageFormat.format(
                "INSERT INTO {0} ({1}, {2}, {3}, {4}) VALUES (?, (SELECT {5} FROM {6} WHERE {7} = ?), ?, curdate())", 
                postCommentsTableName,
                postCommentsTablePostIDFieldName,
                postCommentsTableUserIDFieldName,
                postCommentsTableTextFieldName,
                postCommentsTableDateFieldName,
                
                usersTableIDFieldName,
                usersTableName,
                usersTableLoginFieldName);
        return jdbcTemplate.update(query, new Object[] {postID, login, text});
    }

    @Override
    public int getCommentsCountPerDay(String login) throws DataAccessException {
        String query = MessageFormat.format(
                "SELECT COUNT(*) FROM {0} WHERE {1} = curdate() AND {2} = (SELECT {3} FROM {4} WHERE {5} = ?)",
                postCommentsTableName,
                postCommentsTableDateFieldName,
                postCommentsTableUserIDFieldName,
                
                usersTableIDFieldName,
                usersTableName,
                usersTableLoginFieldName); 
        return  jdbcTemplate.queryForObject(query, new Object[] {login}, Integer.class);
    }

}
\$\endgroup\$
3
+75
\$\begingroup\$

A comment on the DDL

Despite this using MySQL (which would never be my first choice), the schema seems pretty sane; good job. This surprised me:

removed_date date default null

but then, reading the documentation, MySQL does a nonsensical thing by default and uses the "zero" value for a date as its default; so what you've done is correct.

For these two columns:

_from date not null,
_to date not null,

If you didn't want an underscore, you can instead use quote escaping.

For columns like these:

removed boolean default false,

you should consider making them not null.

User table

insert into users
(name, password, access_level_id, creation_date)
VALUES
    ('root', '123321a', 2, curdate());

First of all, you should be able to make the current date the default for creation_date so that you don't actually need to specify it in the insert. Second and certainly the most concerning thing I see in all of this code is what appears to be a plain-text password. Never, ever, ever, ever put a plaintext password in a database. I implore to you have a careful read through https://stackoverflow.com/questions/7270526/how-do-you-securely-store-a-users-password-and-salt-in-mysql , or similar articles, noting that some suggest MD5 which should be avoided due to cryptographic weakness. It is crucial to hash and salt passwords.

\$\endgroup\$
6
  • \$\begingroup\$ Do you have any remarks about queries(MySQLPostDAO, MySQLUserDAO)? \$\endgroup\$ – Miron Jun 22 '20 at 11:14
  • 1
    \$\begingroup\$ No, because they aren't included in the body of the question. If you want them to be reviewed they should be included in-line rather than linked to a repository. \$\endgroup\$ – Reinderien Jun 22 '20 at 14:16
  • \$\begingroup\$ I cannot include all my code in question, because my code has too many symbols. I tried. But I noticed a limitation when I finished including DAOs package. I cannot include all code physically. \$\endgroup\$ – Miron Jun 23 '20 at 5:38
  • \$\begingroup\$ I ll try to include only important files, but i think, that it will be bad without others \$\endgroup\$ – Miron Jun 23 '20 at 5:49
  • \$\begingroup\$ Files have been added to the question on request. \$\endgroup\$ – Mast Jun 23 '20 at 7:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.