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.IScriptDAO; import com.che.software.testato.domain.dao.jdbc.adao.AbstractDAO; import com.che.software.testato.domain.dao.jdbc.exception.ScriptCreationDAOException; import com.che.software.testato.domain.dao.jdbc.exception.ScriptSearchDAOException; import com.che.software.testato.domain.dao.jdbc.exception.ScriptUpdateDAOException; import com.che.software.testato.domain.entity.MapArrow; import com.che.software.testato.domain.entity.Script; import com.che.software.testato.domain.entity.Variant; import com.che.software.testato.domain.entity.creation.ScriptCreation; import com.che.software.testato.domain.entity.search.ScriptSearch; /** * JDBC implementation of the DAO interface dedicated to the scripts management. * * @author Clement HELIOU (clement.heliou@che-software.com). * @copyright Che Software. * @license GNU General Public License. * @see AbstractDAO, IScriptDAO. * @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("scriptDAO") public class ScriptDAO extends AbstractDAO implements IScriptDAO { /** * Constants. */ private static final Logger LOGGER = Logger.getLogger(ScriptDAO.class); /** * Creates the scripts for a given hierarchy. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param hierarchyId the hierarchy id. * @param scripts the scripts to create. * @since July, 2011. * @throws ScriptCreationDAOException if an error occurs during the * creation. */ @Override public void createScriptsFromHierarchy(int hierarchyId, List<ScriptCreation> scripts) throws ScriptCreationDAOException { LOGGER.debug("createScriptsFromHierarchy(" + hierarchyId + "," + scripts.size() + " scripts)."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); for (ScriptCreation script : scripts) { getQueryRunner().update(connection, "INSERT INTO script(script_id, hierarchy_id, label, depth) VALUES(nextval('script_seq'),?,'', ?) ", new Object[] { hierarchyId, 1 }); Integer createdScript = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_id)::int AS scriptId FROM script ", new ScalarHandler("scriptId")); createItems(connection, script.getScriptArrows(), createdScript, 1, hierarchyId); } connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new ScriptCreationDAOException(e1); } throw new ScriptCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Checks if some script have been elicited for a given hierarchy. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param hierarchyId the hierarchy id. * @return true if the hierarchy has already been elicitate, else false. * @since July, 2011. * @throws ScriptSearchDAOException if an error occurs during the search. */ @Override public boolean isScriptExistingFromHierarchyId(int hierarchyId) throws ScriptSearchDAOException { LOGGER.debug("isScriptExistingFromHierarchyId(" + hierarchyId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); return (Boolean) getQueryRunner().query(connection, "SELECT EXISTS ( SELECT script_id FROM script WHERE hierarchy_id = ? ) AS result ", new ScalarHandler("result"), new Object[] { hierarchyId }); } catch (SQLException e) { throw new ScriptSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Recursive method. Creates the items for a given script. Then, for each * item, makes a recursive call to create their children items. The stop * condition is reached when variants are discovered in an item. * * @param connection the connection to use. * @param items the items to create. * @param scriptId the parent script. Will be the same for all items. * @param depth the level of the items to create. Default is one and it's * incremented at each iteration. * @param hierarchyId the related hierarchy id. * @throws SQLException if an SQLException occurs during the creations. */ private void createItems(Connection connection, List<MapArrow> items, int scriptId, int depth, int hierarchyId) throws SQLException { LOGGER.debug("createItems(" + items.size() + " items," + scriptId + "," + depth + ")."); for (int i = 1; i <= items.size(); i++) { MapArrow item = items.get(i - 1); getQueryRunner().update(connection, "INSERT INTO script_item(script_item_id, script_id, \"ORDER\", label) VALUES(nextval('script_item_seq'),?,?,?) ", new Object[] { scriptId, i, "[" + item.getSourceLabel() + "," + item.getLabel() + "," + item.getTargetLabel() + "]" }); Integer createdItem = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_item_id)::int AS scriptItemId FROM script_item ", new ScalarHandler("scriptItemId")); if (null != item.getScripts() && !item.getScripts().isEmpty()) { for (ScriptCreation itemScripts : item.getScripts()) { getQueryRunner().update(connection, "INSERT INTO script(script_id, hierarchy_id, label, depth, parent_script_item) VALUES(nextval('script_seq'), ?, '', ?, ?) ", new Object[] { hierarchyId, (depth + 1), createdItem }); Integer createdScript = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_id)::int AS scriptId FROM script ", new ScalarHandler("scriptId")); createItems(connection, itemScripts.getScriptArrows(), createdScript, (depth + 1), hierarchyId); } } else { // Stop condition. for (int j = 1; j <= item.getVariants().size(); j++) { getQueryRunner().update(connection, "INSERT INTO script(script_id, hierarchy_id, label, depth, parent_script_item) VALUES(nextval('script_seq'), ?, '', ?, ?) ", new Object[] { hierarchyId, (depth + 1), createdItem }); Integer createdScript = (Integer) getQueryRunner().query(connection, "SELECT MAX(script_id)::int AS scriptId FROM script ", new ScalarHandler("scriptId")); Variant variant = item.getVariants().get(j - 1); getQueryRunner().update(connection, "INSERT INTO script_item(script_item_id, script_id, \"ORDER\", label) VALUES(nextval('script_item_seq'),?,?,?) ", new Object[] { createdScript, j, variant.getLabel() }); } } } } /** * Script 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 ScriptSearchDAOException if an error occurs during the search. */ @Override public List<Script> searchScript(ScriptSearch searchBean) throws ScriptSearchDAOException { LOGGER.debug("searchScript()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); return getQueryRunner().query(connection, getScriptSearchQueryFromCriterion(searchBean, params), new BeanListHandler<Script>(Script.class), params.toArray()); } catch (SQLException e) { throw new ScriptSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Updates a script from his object. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param scriptToUpdate the script object to update in the database. * @since August, 2011. * @throws ScriptUpdateDAOException if an error occurs during the update. */ @Override public void updateScript(Script scriptToUpdate) throws ScriptUpdateDAOException { LOGGER.debug("updateScript()."); Connection connection = null; try { connection = getDataSource().getConnection(); List<Object> params = new ArrayList<Object>(); getQueryRunner().update(connection, getScriptUpdateQueryFromCriterion(scriptToUpdate, params), params.toArray()); } catch (SQLException e) { throw new ScriptUpdateDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } } /** * Recovery of the script 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 getScriptSearchQueryFromCriterion(ScriptSearch searchBean, List<Object> params) { LOGGER.debug("getScriptSearchQueryFromCriterion()."); setWhereClauseEnabled(false); StringBuilder sBuilder = new StringBuilder( "SELECT DISTINCT(script_id) AS scriptId, hierarchy_id AS parentHierarchyId, label, depth, parent_script_item AS parentScriptItem FROM script "); if (null != searchBean && null != searchBean.getIterationId()) { sBuilder.append( "JOIN comparisonMatrixItem ON(first_script = script_id) JOIN iteration_assignment USING(iteration_assignment_id) "); } if (null != searchBean && null != searchBean.getSelected()) { sBuilder.append("JOIN iteration_assignment_source_script USING(script_id) "); } if (null != searchBean && null != searchBean.getParentHierarchyId() && 0 != searchBean.getParentHierarchyId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("hierarchy_id = ? "); params.add(searchBean.getParentHierarchyId()); } if (null != searchBean && null != searchBean.getDepth() && 0 != searchBean.getDepth()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("depth = ? "); params.add(searchBean.getDepth()); } if (null != searchBean && null != searchBean.getParentScriptItem() && 0 != searchBean.getParentScriptItem()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("parent_script_item = ? "); params.add(searchBean.getParentScriptItem()); } if (null != searchBean && null != searchBean.getParentScriptItem() && 0 == searchBean.getParentScriptItem()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("parent_script_item IS NULL "); } if (null != searchBean && null != searchBean.getIterationId()) { sBuilder.append(getWhereClauseBegin()); sBuilder.append("iteration_id = ? "); params.add(searchBean.getIterationId()); } 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 script update query from criterion. * * @author Clement HELIOU (clement.heliou@che-software.com). * @param scriptToUpdate the object containing the criterions. * @param params the parameters list corresponding to the built query. * @return the built query. * @since July, 2011. */ private String getScriptUpdateQueryFromCriterion(Script scriptToUpdate, List<Object> params) { LOGGER.debug("getScriptUpdateQueryFromCriterion()."); setSetClauseEnabled(false); StringBuilder sBuilder = new StringBuilder("UPDATE script "); if (null != scriptToUpdate && null != scriptToUpdate.getLabel()) { sBuilder.append(getSetClauseBegin()); sBuilder.append("label = ? "); params.add(scriptToUpdate.getLabel()); } sBuilder.append("WHERE script_id = ? "); params.add(scriptToUpdate.getScriptId()); return sBuilder.toString(); } }