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.Iterator; import java.util.List; import org.apache.commons.dbutils.DbUtils; 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.IActionDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.ActionCreationDAOException; import com.che.software.testato.domain.dao.jdbc.exception.ActionSearchDAOException; import com.che.software.testato.domain.entity.Action; import com.che.software.testato.domain.entity.Intention; import com.che.software.testato.domain.entity.Section; import com.che.software.testato.domain.entity.search.ActionSearch; /** * JDBC implementation of the DAO interface dedicated to the actions management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IActionDAO. * @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("actionDAO") public class ActionDAO extends AbstractDAO implements IActionDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(ActionDAO.class); /** * Creates a map from an action plan id, a list of intentions and a list of * sections. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param actionPlanId the action plan id to be linked to the created * actions. * @param intentions the intentions to add in the map. * @param sections the sections to add in the map. * @since July, 2011. * @throws ActionCreationDAOException if an error occurs during the * creation. */ @Override public void createMap(int actionPlanId, List<Intention> intentions, List<Section> sections) throws ActionCreationDAOException { LOGGER.debug("createMap(" + actionPlanId + "," + intentions.size() + " intentions," + sections.size() + " sections)."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); List<Integer> createdIntentionsId = new ArrayList<Integer>(); for (Intention intention : intentions) { getQueryRunner().update(connection, "INSERT INTO intention(intention_id, label) VALUES(nextval('intention_seq'), ?) ", new Object[] { intention.getLabel() }); createdIntentionsId.add((Integer) getQueryRunner().query(connection, "SELECT MAX(intention_id)::int AS intentionId FROM intention ", new ScalarHandler("intentionId"))); } List<Intention> createdIntentions = getQueryRunner().query(connection, "SELECT intention_id AS intentionId, label FROM intention WHERE intention_id IN(" + getInClauseFromIntentionsIds(createdIntentionsId) + ") ", new BeanListHandler<Intention>(Intention.class)); LOGGER.debug(createdIntentions.size() + " intentions created with success..."); for (Section section : sections) { boolean source = false, target = false; for (Intention intention : createdIntentions) { if (!source && intention.getLabel().equalsIgnoreCase(section.getSourceIntention())) { section.setSourceIntentionId(intention.getIntentionId()); source = true; } if (!target && intention.getLabel().equalsIgnoreCase(section.getTargetIntention())) { section.setTargetIntentionId(intention.getIntentionId()); target = true; } if (target && source) { break; } } Integer actionId = (Integer) getQueryRunner().query(connection, "SELECT action_id::int AS actionId FROM action WHERE action_plan_id = ? AND source_intention = ? AND target_intention = ? ", new ScalarHandler("actionId"), new Object[] { actionPlanId, section.getSourceIntentionId(), section.getTargetIntentionId() }); if (null == actionId) { LOGGER.debug("Action creation..."); getQueryRunner().update(connection, "INSERT INTO action(action_id, target_intention, source_intention, action_plan_id) VALUES(nextval('action_seq'),?,?,?) ", new Object[] { section.getTargetIntentionId(), section.getSourceIntentionId(), actionPlanId }); actionId = (Integer) getQueryRunner().query(connection, "SELECT action_id::int AS actionId FROM action WHERE action_plan_id = ? AND source_intention = ? AND target_intention = ? ", new ScalarHandler("actionId"), new Object[] { actionPlanId, section.getSourceIntentionId(), section.getTargetIntentionId() }); } getQueryRunner().update(connection, "INSERT INTO item(item_id, label, iteration_max_number) VALUES(nextval('item_seq'), ?, ?) ", new Object[] { section.getStrategyLabel(), section.getMaxIterationNumber() }); Integer createdItemId = (Integer) getQueryRunner().query(connection, "SELECT MAX(item_id)::int AS itemId FROM item ", new ScalarHandler("itemId")); getQueryRunner().update(connection, (section.isExclusive()) ? "INSERT INTO action_exclusive_item(action_id, item_id) VALUES(?,?) " : "INSERT INTO action_inclusive_item(action_id, item_id) VALUES(?,?) ", new Object[] { actionId, createdItemId }); } connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new ActionCreationDAOException(e1); } throw new ActionCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Checks if some action have been setted for a given action plan. In other * words, is able to say if a map has already been setted or not. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param actionPlanId the action plan id. * @return true if the map has already been setted, else false. * @since July, 2011. * @throws ActionSearchDAOException if an error occurs during the search. */ @Override public boolean isActionExistingFromActionPlanId(int actionPlanId) throws ActionSearchDAOException { LOGGER.debug("isActionExistingFromActionPlanId(" + actionPlanId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS( SELECT action_id FROM action WHERE action_plan_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { actionPlanId }); } catch (SQLException e) { throw new ActionSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Action 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 ActionSearchDAOException if an error occurs during the search. */ @Override public List<Action> searchAction(ActionSearch searchBean) throws ActionSearchDAOException { LOGGER.debug("searchAction()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); return getQueryRunner().query(connection, getActionSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Action>(Action.class), params.toArray()); } catch (SQLException e) { throw new ActionSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Recovery of the action 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 getActionSearchQueryFromCriterion(ActionSearch searchBean, List<Object> params) { LOGGER.debug("getActionSearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT action_id AS actionId, action_plan_id AS actionPlanId, target_intention AS targetIntentionId, source_intention AS sourceIntentionId FROM action "); if (null != searchBean && 0 != searchBean.getParentActionPlanId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("action_plan_id = ? "); params.add(searchBean.getParentActionPlanId()); } return sBuilder.toString(); } /** * Building a SQL IN clause from the intentions ids to be able to recover * their properties after their creation. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param intentionsIds the set of intentions ids. * @return the resulting SQL IN clause. * @since July, 2011. */ private String getInClauseFromIntentionsIds(List<Integer> intentionsIds) { LOGGER.debug("getInClauseFromIntentionsIds(" + intentionsIds.size() + " intentions ids)."); StringBuilder sBuilder = new StringBuilder(); Iterator<Integer> it = intentionsIds.iterator(); while (it.hasNext()) { sBuilder.append("'"); sBuilder.append(it.next()); sBuilder.append("'"); if (it.hasNext()) { sBuilder.append(", "); } } return sBuilder.toString(); } }