Java tutorial
package com.che.software.testato.domain.dao.jdbc.impl; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.commons.dbutils.DbUtils; import org.apache.commons.dbutils.handlers.ArrayListHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import org.apache.commons.dbutils.handlers.ScalarHandler; import org.apache.log4j.Logger; import org.springframework.stereotype.Repository; import com.che.software.testato.domain.dao.IUserDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.UserCreationDAOException; import com.che.software.testato.domain.dao.jdbc.exception.UserSearchDAOException; import com.che.software.testato.domain.entity.User; import com.che.software.testato.domain.entity.creation.UserCreation; import com.che.software.testato.domain.entity.search.UserSearch; import com.che.software.testato.domain.enumeration.Role; /** * JDBC implementation of the DAO interface dedicated to the users management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IUserDAO. * @since July, 2011. * * This file is part of Testato. * * Testato is free software: you can redistribute it and/or modify it * under the terms of the GNU General Public License as published by the * Free Software Foundation, either version 3 of the License, or (at your * option) any later version. * * Testato is distributed in the hope that it will be useful, but WITHOUT * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License * for more details. * * You should have received a copy of the GNU General Public License * along with Testato. If not, see <http://www.gnu.org/licenses/>. * * Testato's logo is a creation of Arrioch * (http://arrioch.deviantart.com/) and it's distributed under the terms * of the Creative Commons License. */ @Repository("userDAO") public class UserDAO extends AbstractDAO implements IUserDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(UserDAO.class); /** * Creates an user from his properties bean. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param userToCreateBean the properties to use to create the user. * @throws UserCreationDAOException if an error occurs during the creation. */ @Override public void createUserFromBean(UserCreation userToCreateBean) throws UserCreationDAOException { LOGGER.debug("createUserFromBean(" + userToCreateBean.getLogin() + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); getQueryRunner().update(connection, "INSERT INTO \"user\"(user_id, \"login\", \"password\", \"name\", last_name, creation_date, job) VALUES(nextval('user_seq'), ?, md5(?), ?, ?, NOW(), ?) ", new Object[] { userToCreateBean.getLogin(), userToCreateBean.getPassword(), userToCreateBean.getName().toUpperCase(), userToCreateBean.getLastName(), userToCreateBean.getJob() }); Integer createdUserID = (Integer) getQueryRunner().query(connection, "SELECT user_id::int FROM \"user\" WHERE \"login\" = ? ", new ScalarHandler("user_id"), new Object[] { userToCreateBean.getLogin() }); getQueryRunner().update(connection, "INSERT INTO user_role(user_id, role) VALUES(?, 'ROLE_USER') ", new Object[] { createdUserID }); if (userToCreateBean.isAdministrator()) { getQueryRunner().update(connection, "INSERT INTO user_role(user_id, role) VALUES(?, 'ROLE_ADMIN') ", new Object[] { createdUserID }); } if (userToCreateBean.isResponsable()) { getQueryRunner().update(connection, "INSERT INTO user_role(user_id, role) VALUES(?, 'ROLE_RESP') ", new Object[] { createdUserID }); } getQueryRunner().update(connection, "INSERT INTO user_service(user_id, service_id, hired_date) VALUES(?, ?, NOW()) ", new Object[] { createdUserID, userToCreateBean.getServiceId() }); connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new UserCreationDAOException(e1); } throw new UserCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Reponsable user search. * * @author Clement HELIOU (clement.heliou@che-software.com). * @return the resulting object list. * @since July, 2011. * @throws UserSearchDAOException if an error occurs during the search. */ @Override public List<User> searchResponsableUser() throws UserSearchDAOException { LOGGER.debug("searchResponsableUser()."); List<User> responsableUsers = new ArrayList<User>(); for (User user : searchUsers(new UserSearch())) { if (user.isResponsable()) { responsableUsers.add(user); } } return responsableUsers; } /** * User search from an id. * * @param userId the user id. * @return the resulting object. * @throws UserSearchDAOException if an error occurs during the search. */ @Override public User searchUserFromId(int userId) throws UserSearchDAOException { LOGGER.debug("searchUserFromId(" + userId + ")."); return searchUsers(new UserSearch(userId)).get(0); } /** * User search from a bean of criterions. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param searchBean the criterions to use for the search. * @return the resulting object list. * @since July, 2011. * @throws UserSearchDAOException if an error occurs during the search. */ @Override public List<User> searchUsers(UserSearch searchBean) throws UserSearchDAOException { LOGGER.debug("searchUsers()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); List<User> result = getQueryRunner().query(connection, getUserSearchQueryFromCriterion(searchBean, params), new BeanListHandler<User>(User.class), params.toArray()); if (null != result) { // Adding the users roles. for (User user : result) { List<Role> roles = new ArrayList<Role>(); for (Object[] role : getQueryRunner().query(connection, getUserRoleSearchQuery(), new ArrayListHandler(), new Object[] { user.getUserId() })) { roles.add(Role.valueOf(role[0].toString().toUpperCase())); } user.setRoles(roles); } } return result; } catch (SQLException e) { throw new UserSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Recovery of the user roles search query. * * @author Clement HELIOU (clement.heliou@che-software.com). * @return the built query. * @since July, 2011. */ private String getUserRoleSearchQuery() { LOGGER.debug("getUserRoleSearchQuery"); return "SELECT role FROM user_role WHERE user_id = ? "; } /** * Recovery of the user search query from criterion. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param searchBean the object containing the criterions. * @param params the parameters list corresponding to the built query. * @return the built query. * @since July, 2011. */ private String getUserSearchQueryFromCriterion(UserSearch searchBean, List<Object> params) { LOGGER.debug("getUserSearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT user_id AS userId, login, password, name, last_name AS lastName, creation_date AS creationDate, job FROM \"user\" "); if (null != searchBean && 0 != searchBean.getUserId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("user_id = ? "); params.add(searchBean.getUserId()); } if (null != searchBean && null != searchBean.getLogin() && !"".equals(searchBean.getLogin())) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("login = ? "); params.add(searchBean.getLogin()); } return sBuilder.toString(); } }