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.IItemDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.ItemSearchDAOException; import com.che.software.testato.domain.dao.jdbc.exception.ItemUpdateDAOException; import com.che.software.testato.domain.entity.Item; import com.che.software.testato.domain.entity.search.ItemSearch; /** * JDBC implementation of the DAO interface dedicated to the projects * management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IItemDAO. * @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("itemDAO") public class ItemDAO extends AbstractDAO implements IItemDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(ItemDAO.class); /** * Update an item to operationalize it. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param itemId the item id. * @return the created test case id. * @since July, 2011. * @throws ItemUpdateDAOException if an error occurs during the update. */ @Override public int operationalizeItem(int itemId) throws ItemUpdateDAOException { LOGGER.debug("operationalizeItem(" + itemId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); getQueryRunner().update(connection, "INSERT INTO test_case(test_case_id) VALUES(nextval('test_case_id_seq')) "); Integer createdTestCase = (Integer) getQueryRunner().query(connection, "SELECT MAX(test_case_id)::int AS testCaseId FROM test_case ", new ScalarHandler("testCaseId")); getQueryRunner().update(connection, "UPDATE item SET test_case_id = ? WHERE item_id = ? ", new Object[] { createdTestCase, itemId }); connection.commit(); return createdTestCase; } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new ItemUpdateDAOException(e); } throw new ItemUpdateDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Update an item to refine it. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param itemId the item id. * @return the created action plan id. * @since July, 2011. * @throws ItemUpdateDAOException if an error occurs during the update. */ @Override public int refineItem(int itemId) throws ItemUpdateDAOException { LOGGER.debug("refineItem(" + itemId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); getQueryRunner().update(connection, "INSERT INTO action_plan(action_plan_id) VALUES(nextval('action_plan_seq')) "); Integer createdActionPlan = (Integer) getQueryRunner().query(connection, "SELECT MAX(action_plan_id)::int AS actionPlanId FROM action_plan ", new ScalarHandler("actionPlanId")); getQueryRunner().update(connection, "UPDATE item SET action_plan_id = ? WHERE item_id = ? ", new Object[] { createdActionPlan, itemId }); connection.commit(); return createdActionPlan; } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new ItemUpdateDAOException(e1); } throw new ItemUpdateDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Item 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 ItemSearchDAOException if an error occurs during the search. */ @Override public List<Item> searchItem(ItemSearch searchBean) throws ItemSearchDAOException { LOGGER.debug("searchItem()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); return getQueryRunner().query(connection, getItemSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Item>(Item.class), params.toArray()); } catch (SQLException e) { throw new ItemSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Recovery of the item 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 getItemSearchQueryFromCriterion(ItemSearch searchBean, List<Object> params) { LOGGER.debug("getItemSearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT item_id AS itemId, test_case_id AS testCaseId, i.action_plan_id AS actionPlanId, i.label, iteration_max_number AS maxIterationNumber, EXISTS (SELECT action_id FROM action_exclusive_item ex WHERE ex.item_id = i.item_id) AS exclusive "); if (null != searchBean && 0 != searchBean.getParentActionPlanId()) { sBuilder.append( ", inso.label AS sourceIntentionLabel, inta.label AS targetIntentionLabel, inso.intention_id AS sourceIntentionId, inta.intention_id AS targetIntentionId "); } sBuilder.append("FROM item i "); if (null != searchBean && 0 != searchBean.getParentActionPlanId()) { sBuilder.append( "LEFT JOIN action_inclusive_item ai USING(item_id) LEFT JOIN action_exclusive_item ei USING(item_id) LEFT JOIN action aai ON(aai.action_id = ai.action_id) LEFT JOIN action aei ON(aei.action_id = ei.action_id) JOIN intention inso ON(inso.intention_id = COALESCE(aai.source_intention, aei.source_intention)) JOIN intention inta ON(inta.intention_id = COALESCE(aai.target_intention, aei.target_intention)) "); sBuilder.append(getWhereClauseBegin()); sBuilder.append("COALESCE(aai.action_plan_id, aei.action_plan_id) = ? "); params.add(searchBean.getParentActionPlanId()); } if (null != searchBean && 0 != searchBean.getParentAction()) { sBuilder.append( "LEFT JOIN action_inclusive_item aii USING(item_id) LEFT JOIN action_exclusive_item aei USING(item_id) "); sBuilder.append(getWhereClauseBegin()); sBuilder.append("COALESCE(aii.action_id, aei.action_id) = ? "); params.add(searchBean.getParentAction()); } sBuilder.append("ORDER BY i.label "); return sBuilder.toString(); } }