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.IVariantDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.VariantCreationDAOException; import com.che.software.testato.domain.dao.jdbc.exception.VariantSearchDAOException; import com.che.software.testato.domain.dao.jdbc.exception.VariantUpdateDAOException; import com.che.software.testato.domain.entity.Element; import com.che.software.testato.domain.entity.ProceduralArrow; import com.che.software.testato.domain.entity.Variant; import com.che.software.testato.domain.entity.creation.VariantCreation; import com.che.software.testato.domain.entity.search.VariantSearch; import com.che.software.testato.domain.enumeration.VariantTypes; /** * JDBC implementation of the DAO interface dedicated to the variants * management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IVariantDAO. * @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("variantDAO") public class VariantDAO extends AbstractDAO implements IVariantDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(VariantDAO.class); /** * Checks if variants are already existing or not for a given test case. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param testCaseId the test case id. * @return true if variants are already existing, else false. * @since July, 2011. * @throws VariantSearchDAOException if an error occurs during the search. */ @Override public boolean areVariantsExistingFromTestCaseId(int testCaseId) throws VariantSearchDAOException { LOGGER.debug("areVariantsExistingFromTestCaseId(" + testCaseId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS ( SELECT variant_id FROM variant WHERE test_case_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { testCaseId }); } catch (SQLException e) { throw new VariantSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Creates variants for a given test case. Is called after the variants * generation. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param testCaseId the test case id. * @param variants the list of generated variants. * @since July, 2011. * @throws VariantCreationDAOException if an error occurs during the * creation. */ @Override public void createVariantsFromTestCaseId(int testCaseId, List<VariantCreation> variants) throws VariantCreationDAOException { LOGGER.debug("createVariantsFromTestCaseId(" + testCaseId + "," + variants.size() + " variants)"); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); for (VariantCreation variant : variants) { getQueryRunner().update(connection, "INSERT INTO variant(variant_id, variant_type, test_case_id, label, quantitative_criterion) VALUES(nextval('variant_id_seq'),?,?,NULL,NULL) ", new Object[] { variant.getVariantType().name(), testCaseId }); Integer createdVariantId = (Integer) getQueryRunner().query(connection, "SELECT MAX(variant_id)::int AS result FROM variant ", new ScalarHandler("result")); for (Element element : variant.getElements()) { getQueryRunner().update(connection, "INSERT INTO variant_element(element_id, variant_id) VALUES(?,?) ", new Object[] { element.getElementId(), createdVariantId }); } for (ProceduralArrow transition : variant.getTransitions()) { getQueryRunner().update(connection, "INSERT INTO variant_transition(transition_id, variant_id) VALUES(?,?) ", new Object[] { transition.getTransitionId(), createdVariantId }); } } connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new VariantCreationDAOException(e1); } throw new VariantCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Variant 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 VariantSearchDAOException if an error occurs during the search. */ @Override public List<Variant> searchVariant(VariantSearch searchBean) throws VariantSearchDAOException { LOGGER.debug("searchVariant()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); List<Variant> variants = getQueryRunner().query(connection, getVariantSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Variant>(Variant.class), params.toArray()); for (Variant variant : variants) { variant.setVariantType(VariantTypes.valueOf((String) getQueryRunner().query(connection, "SELECT variant_type AS variantTypeAsString FROM variant WHERE variant_id = ? ", new ScalarHandler("variantTypeAsString"), new Object[] { variant.getVariantId() }))); } return variants; } catch (SQLException e) { throw new VariantSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Updates a variant from his object. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param variantToUpdate the variant object to update in the database. * @since July, 2011. * @throws VariantUpdateDAOException if an error occurs during the update. */ @Override public void updateVariant(Variant variantToUpdate) throws VariantUpdateDAOException { LOGGER.debug("updateVariant(" + variantToUpdate.getVariantId() + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); getQueryRunner().update(connection, getVariantUpdateQueryFromCriterion(variantToUpdate, params), params.toArray()); } catch (SQLException e) { throw new VariantUpdateDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Recovery of the variant 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 getVariantSearchQueryFromCriterion(VariantSearch searchBean, List<Object> params) { LOGGER.debug("getVariantSearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT variant_id AS variantId, test_case_id AS testCaseId, label, quantitative_criterion AS quantitativeCriterion FROM variant "); if (null != searchBean && 0 != searchBean.getParentTestCaseId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("test_case_id = ? "); params.add(searchBean.getParentTestCaseId()); } sBuilder.append("ORDER BY variant_id "); return sBuilder.toString(); } /** * Recovery of the variant update query from criterion. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param variantToUpdate the object containing the criterions. * @param params the parameters list corresponding to the built query. * @return the built query. * @since July, 2011. */ private String getVariantUpdateQueryFromCriterion(Variant variantToUpdate, List<Object> params) { LOGGER.debug("getVariantUpdateQueryFromCriterion()."); setSetClauseEnabled(false); StringBuilder sBuilder = new StringBuilder("UPDATE variant "); if (null != variantToUpdate && null != variantToUpdate.getLabel() && !"".equals(variantToUpdate.getLabel())) { sBuilder.append(getSetClauseBegin()); sBuilder.append("label = ? "); params.add(variantToUpdate.getLabel()); } if (null != variantToUpdate && null != variantToUpdate.getQuantitativeCriterion() && !"".equals(variantToUpdate.getQuantitativeCriterion())) { sBuilder.append(getSetClauseBegin()); sBuilder.append("quantitative_criterion = ? "); params.add(variantToUpdate.getQuantitativeCriterion()); } sBuilder.append("WHERE variant_id = ? "); params.add(variantToUpdate.getVariantId()); return sBuilder.toString(); } }