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.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.IElementDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.ElementCreationDAOException; import com.che.software.testato.domain.dao.jdbc.exception.ElementSearchDAOException; import com.che.software.testato.domain.entity.Element; import com.che.software.testato.domain.entity.creation.ElementCreation; import com.che.software.testato.domain.entity.creation.TransitionCreation; import com.che.software.testato.domain.entity.search.ElementSearch; import com.che.software.testato.domain.enumeration.creation.ElementCreationTypes; /** * JDBC implementation of the DAO interface dedicated to the elements * management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IElementDAO. * @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("elementDAO") public class ElementDAO extends AbstractDAO implements IElementDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(ElementDAO.class); /** * Creates a procedural diagram from a testCaseId, a set of elements and a * set of transitions. If activities have been reused or not to create this * diagram. * * @param testCaseId the test case id. * @param elements the set of elements. * @param transitions the set of transitions. * @throws ElementCreationDAOException if an error occurs during the * creation. */ @Override public void createDiagram(int testCaseId, List<ElementCreation> elements, List<TransitionCreation> transitions) throws ElementCreationDAOException { LOGGER.debug("createDiagram(" + testCaseId + ", " + elements.size() + " elements, " + transitions.size() + " transitions)."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); for (ElementCreation element : elements) { Integer activityId = null, pointId = null; if (element.getType().equals(ElementCreationTypes.ACTIVITY)) { activityId = (Integer) getQueryRunner().query(connection, "SELECT activity_id::int AS activityId FROM activity WHERE label = ? ", new ScalarHandler("activityId"), new Object[] { element.getLabel() }); if (null == activityId) { getQueryRunner().update(connection, "INSERT INTO activity(activity_id, global_description, label) VALUES(nextval('activity_id_seq'), NULL, ?) ", new Object[] { element.getLabel() }); activityId = (Integer) getQueryRunner().query(connection, "SELECT activity_id::int AS activityId FROM activity WHERE label = ? ", new ScalarHandler("activityId"), new Object[] { element.getLabel() }); } } else { getQueryRunner().update(connection, "INSERT INTO point(point_id, point_type, label) VALUES(nextval('point_id_seq'), ?, ?) ", new Object[] { element.getType().name(), element.getLabel() }); pointId = (Integer) getQueryRunner().query(connection, "SELECT MAX(point_id)::int AS pointId FROM point ", new ScalarHandler("pointId")); } getQueryRunner().update(connection, "INSERT INTO element(element_id, point_id, activity_id, test_case_id) VALUES(nextval('element_id_seq')," + ((null != activityId) ? "NULL" : "?") + "," + ((null != pointId) ? "NULL" : "?") + ",?) ", (null != activityId) ? new Object[] { activityId, testCaseId } : new Object[] { pointId, testCaseId }); } List<Element> createdElements = getQueryRunner().query(connection, "SELECT element_id AS elementId, point_id AS pointId, activity_id AS activityId, test_case_id AS testCaseId, COALESCE(activity.label, point.label) AS label FROM element LEFT JOIN activity USING(activity_id) LEFT JOIN point USING(point_id) WHERE test_case_id = ? ", new BeanListHandler<Element>(Element.class), new Object[] { testCaseId }); for (TransitionCreation transition : transitions) { boolean source = false, target = false; for (Element element : createdElements) { if (element.getLabel().equalsIgnoreCase(transition.getSource())) { transition.setSourceId(element.getElementId()); source = true; } if (element.getLabel().equalsIgnoreCase(transition.getTarget())) { transition.setTargetId(element.getElementId()); target = true; } if (source && target) { break; } } getQueryRunner().update(connection, "INSERT INTO transition(transition_id, target_element, source_element, test_case_id, label) VALUES(nextval('transition_id_seq'), ?, ?, ?, ?) ", new Object[] { transition.getTargetId(), transition.getSourceId(), testCaseId, (null != transition.getLabel()) ? transition.getLabel() : "" }); } connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new ElementCreationDAOException(e1); } throw new ElementCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Checks if some element have been setted for a given case test. In other * words, is able to say if a procedural diagram has already been setted or * not. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param caseTestId the case test id. * @return true if the procedural diagram has already been setted, else * false. * @since July, 2011. * @throws ElementSearchDAOException if an error occurs during the search. */ @Override public boolean isElementExistingFromCaseTestId(int testCaseId) throws ElementSearchDAOException { LOGGER.debug("isElementExistingFromCaseTestId(" + testCaseId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS ( SELECT element_id FROM element WHERE test_case_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { testCaseId }); } catch (SQLException e) { throw new ElementSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Element 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 ElementSearchDAOException if an error occurs during the search. */ @Override public List<Element> searchElement(ElementSearch searchBean) throws ElementSearchDAOException { LOGGER.debug("searchElement()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); return getQueryRunner().query(connection, getElementSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Element>(Element.class), params.toArray()); } catch (SQLException e) { throw new ElementSearchDAOException(e); } finally { DbUtils.closeQuietly(connection); } } /** * Recovery of the element 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 getElementSearchQueryFromCriterion(ElementSearch searchBean, List<Object> params) { LOGGER.debug("getElementSearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT element_id AS elementId, point_id AS pointId, activity_id AS activityId, test_case_id AS testCaseId, COALESCE(activity.label, point.label) AS label, point_type AS pointTypeString FROM element LEFT JOIN activity USING(activity_id) LEFT JOIN point USING(point_id) "); if (null != searchBean && 0 != searchBean.getParentVariantId()) { sBuilder.append("JOIN variant_element USING(element_id) "); } if (null != searchBean && 0 != searchBean.getParentTestCaseId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("test_case_id = ? "); params.add(searchBean.getParentTestCaseId()); } if (null != searchBean && 0 != searchBean.getParentVariantId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("variant_id = ? "); params.add(searchBean.getParentVariantId()); } return sBuilder.toString(); } }