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.log4j.Logger; import org.springframework.stereotype.Repository; import com.che.software.testato.domain.dao.IMatrixResultDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.MatrixResultCreationDAOException; import com.che.software.testato.domain.dao.jdbc.exception.MatrixResultSearchDAOException; import com.che.software.testato.domain.dao.jdbc.exception.MatrixResultUpdateDAOException; import com.che.software.testato.domain.entity.MatrixResult; import com.che.software.testato.domain.entity.search.MatrixResultSearch; /** * JDBC implementation of the DAO interface dedicated to the matrix results * management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IMatrixResultDAO. * @since August, 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("matrixResultDAO") public class MatrixResultDAO extends AbstractDAO implements IMatrixResultDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(MatrixResultDAO.class); /** * Creates the matrix results for a given iteration assignment. The related * matrix must be completed before calling this method. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param iterationId the given iteration id. * @since August, 2011. * @throws MatrixResultCreationDAOException if an error occurs during the * creation. */ @Override public void createMatrixResults(int iterationId) throws MatrixResultCreationDAOException { LOGGER.debug("createMatrixResults(" + iterationId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); for (MatrixResult result : getQueryRunner().query(connection, "SELECT first_script AS scriptId, iteration_assignment_id AS iterationAssignmentId, ((SUM((value/( SELECT SUM(value) FROM comparisonMatrixItem WHERE iteration_assignment_id = ? AND second_script = base.second_script GROUP BY second_script ORDER BY second_script ))))/COUNT(*))::numeric(15,2) AS percentage FROM comparisonMatrixItem base WHERE iteration_assignment_id = ? GROUP BY first_script,iteration_assignment_id ORDER BY first_script ", new BeanListHandler<MatrixResult>(MatrixResult.class), new Object[] { iterationId, iterationId })) { getQueryRunner().update(connection, "INSERT INTO iteration_assignment_source_script(script_id, iteration_assignment_id, percentage) VALUES(?, ?, ?) ", new Object[] { result.getScriptId(), result.getIterationAssignmentId(), result.getPercentage() }); } connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new MatrixResultCreationDAOException(e1); } throw new MatrixResultCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Matrix result 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 August, 2011. * @throws MatrixResultSearchDAOException if an error occurs during the * search. */ @Override public List<MatrixResult> searchMatrixResult(MatrixResultSearch searchBean) throws MatrixResultSearchDAOException { LOGGER.debug("searchMatrixResult()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); return getQueryRunner().query(connection, getMatrixResultSearchQueryFromCriterion(searchBean, params), new BeanListHandler<MatrixResult>(MatrixResult.class), params.toArray()); } catch (SQLException e) { throw new MatrixResultSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Updates a matrix result from his object. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param matrixResultToUpdate the matrix result object to update in the * database. * @since August, 2011. * @throws MatrixResultUpdateDAOException if an error occurs during the * update. */ @Override public void updateMatrixResult(MatrixResult matrixResultToUpdate) throws MatrixResultUpdateDAOException { LOGGER.debug("updateMatrixResult(" + matrixResultToUpdate.getIterationAssignmentId() + "," + matrixResultToUpdate.getScriptId() + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); getQueryRunner().update(connection, getMatrixResultUpdateQueryFromCriterion(matrixResultToUpdate, params), params.toArray()); } catch (SQLException e) { throw new MatrixResultUpdateDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Recovery of the matrix result 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 August, 2011. */ private String getMatrixResultSearchQueryFromCriterion(MatrixResultSearch searchBean, List<Object> params) { LOGGER.debug("getMatrixResultSearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT script_id AS scriptId, iteration_assignment_id AS iterationAssignmentId, percentage , label AS scriptLabel FROM iteration_assignment_source_script JOIN script USING(script_id) "); if (null != searchBean && null != searchBean.getIterationAssignmentId() && 0 != searchBean.getIterationAssignmentId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("iteration_assignment_id = ? "); params.add(searchBean.getIterationAssignmentId()); } if (null != searchBean && null != searchBean.getSelected()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("selected = ? "); params.add(searchBean.getSelected()); } sBuilder.append("ORDER BY script_id "); return sBuilder.toString(); } /** * Recovery of the matrix result update query from criterion. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param matrixResultToUpdate the object containing the criterions. * @param params the parameters list corresponding to the built query. * @return the built query. * @since July, 2011. */ private String getMatrixResultUpdateQueryFromCriterion(MatrixResult matrixResultToUpdate, List<Object> params) { LOGGER.debug("getMatrixResultUpdateQueryFromCriterion()."); setSetClauseEnabled(false); StringBuilder sBuilder = new StringBuilder("UPDATE iteration_assignment_source_script "); if (null != matrixResultToUpdate && null != matrixResultToUpdate.getSelected()) { sBuilder.append(getSetClauseBegin()); sBuilder.append("selected = ? "); params.add(matrixResultToUpdate.getSelected()); } sBuilder.append("WHERE script_id = ? AND iteration_assignment_id = ? "); params.add(matrixResultToUpdate.getScriptId()); params.add(matrixResultToUpdate.getIterationAssignmentId()); return sBuilder.toString(); } }