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.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.IHierarchyDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.HierarchyCreationDAOException; import com.che.software.testato.domain.dao.jdbc.exception.HierarchySearchDAOException; import com.che.software.testato.domain.entity.Hierarchy; import com.che.software.testato.domain.entity.creation.HierarchyCreation; import com.che.software.testato.domain.entity.search.HierarchySearch; /** * JDBC implementation of the DAO interface dedicated to the hierarchies * management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IHierarchyDAO. * @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("hierarchyDAO") public class HierarchyDAO extends AbstractDAO implements IHierarchyDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(HierarchyDAO.class); /** * Creates a hierarchy from a bean of criterions. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param creationBean the bean of criterions. * @since July, 2011. * @throws HierarchyCreationDAOException if an error occurs during the * creation. */ @Override public void createHierarchyFromBean(HierarchyCreation creationBean) throws HierarchyCreationDAOException { LOGGER.debug("createHierarchyFromBean()."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); getQueryRunner().update(connection, "INSERT INTO hirearchy(hierarchy_id, creation_date, last_update_date, user_id, high_level_goal) VALUES(nextval('hierarchy_seq'), NOW(), NOW(), ?, ?) ", new Object[] { creationBean.getUser().getUserId(), creationBean.getHighLevelGoal() }); Integer createdHierarchyId = (Integer) getQueryRunner().query(connection, "SELECT MAX(hierarchy_id)::int AS hierarchy_id FROM hirearchy ", new ScalarHandler("hierarchy_id")); getQueryRunner().update(connection, "INSERT INTO hierarchy_version(version_id, hierarchy_id) VALUES(?, ?) ", new Object[] { creationBean.getVersion().getVersionId(), createdHierarchyId }); getQueryRunner().update(connection, "INSERT INTO action_plan(action_plan_id) VALUES(nextval('action_plan_seq')) "); Integer createdActionPlanId = (Integer) getQueryRunner().query(connection, "SELECT MAX(action_plan_id)::int AS action_plan_id FROM action_plan ", new ScalarHandler("action_plan_id")); getQueryRunner().update(connection, "INSERT INTO hierarchy_action_plan(hierarchy_id, action_plan_id) VALUES(?, ?) ", new Object[] { createdHierarchyId, createdActionPlanId }); connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new HierarchyCreationDAOException(e1); } throw new HierarchyCreationDAOException(e); } finally { if (null != connection) { try { connection.close(); } catch (SQLException e) { throw new HierarchyCreationDAOException(e); } } } } /** * Checks if a hierarchy is already existing for a given version id. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param versionId the version id to check * @return true if a hierarchy is already existing, else false. * @since July, 2011. * @throws HierarchySearchDAOException if an error occurs during the search. */ @Override public boolean isHierarchyExistingFromVersionId(int versionId) throws HierarchySearchDAOException { LOGGER.debug("isHierarchyExistingFromVersionId(" + versionId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS ( SELECT hierarchy_id FROM hierarchy_version WHERE version_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { versionId }); } catch (SQLException e) { throw new HierarchySearchDAOException(e); } finally { if (null != connection) { try { connection.close(); } catch (SQLException e) { throw new HierarchySearchDAOException(e); } } } } /** * Hierarchy 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 HierarchySearchDAOException if an error occurs during the search. */ @Override public List<Hierarchy> searchHierarchy(HierarchySearch searchBean) throws HierarchySearchDAOException { LOGGER.debug("searchHierarchy()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); return getQueryRunner().query(connection, getHierarchySearchQueryFromCriterion(searchBean, params), new BeanListHandler<Hierarchy>(Hierarchy.class), params.toArray()); } catch (SQLException e) { throw new HierarchySearchDAOException(e); } finally { if (null != connection) { try { connection.close(); } catch (SQLException e) { throw new HierarchySearchDAOException(e); } } } } /** * Recovery of the hierarchy 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 getHierarchySearchQueryFromCriterion(HierarchySearch searchBean, List<Object> params) { LOGGER.debug("getHierarchySearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT hierarchy_id AS hierarchyId, creation_date AS creationDate, high_level_goal AS highLevelGoal, action_plan_id AS actionPlanId, last_update_date AS lastUpdateDate, user_id AS responsableUserId, version_id AS versionId FROM hirearchy JOIN hierarchy_version USING(hierarchy_id) JOIN hierarchy_action_plan USING(hierarchy_id) "); if (null != searchBean && null != searchBean.getIterationId()) { sBuilder.append( "JOIN hierarchy_prioritization USING(hierarchy_id) JOIN iteration USING (prioritization_id) "); sBuilder.append(getWhereClauseBegin()); sBuilder.append("iteration_id = ? "); params.add(searchBean.getIterationId()); } if (null != searchBean && searchBean.isPrioritizedExcluded()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append( "NOT EXISTS( SELECT prioritization_id FROM hierarchy_prioritization hp WHERE hp.hierarchy_id = hierarchy_id ) "); } return sBuilder.toString(); } }