List of usage examples for org.apache.commons.dbutils.handlers ScalarHandler ScalarHandler
public ScalarHandler(String columnName)
From source file:azkaban.db.DatabaseTransOperatorImpl.java
/** * The ID that was generated is maintained in Mysql server on a per-connection basis. * This means that the value returned by the function to a given client is * the first AUTO_INCREMENT value generated for most recent statement * * This value cannot be affected by other callers, even if they generate * AUTO_INCREMENT values of their own.//from ww w . ja v a 2 s . c om * @return last insertion ID * */ @Override public long getLastInsertId() throws SQLException { // A default connection: autocommit = true. long num = -1; try { num = ((Number) queryRunner.query(conn, "SELECT LAST_INSERT_ID();", new ScalarHandler<>(1))) .longValue(); } catch (SQLException ex) { logger.error("can not get last insertion ID"); throw ex; } return num; }
From source file:de.unibremen.informatik.tdki.combo.data.JdbcTemplate.java
public int queryForInt(String query) { try {/*w ww. ja va 2s. c om*/ return qRunner.query(connection, query, new ScalarHandler<Integer>(1)); } catch (SQLException ex) { throw new RuntimeException(ex); } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.ItemDAO.java
/** * Update an item to operationalize it.//w w w . j av a2 s . c o m * * @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); } } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.PrioritizationDAO.java
/** * Creates the prioritization related to the given hierarchy. * /*from ww w.j a v a 2 s.com*/ * @author Clement HELIOU (clement.heliou@che-software.com). * @param hierarchyId the given hierarchy id. * @since July, 2011. * @throws PrioritizationCreationDAOException if an error occurs during the * creation. */ @Override public void createHierarchyPrioritization(int hierarchyId) throws PrioritizationCreationDAOException { LOGGER.debug("createHierarchyPrioritization(" + hierarchyId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); getQueryRunner().update(connection, "INSERT INTO prioritization(prioritization_id) VALUES(nextval('prioritization_seq')) "); Integer createdPrioritization = (Integer) getQueryRunner().query(connection, "SELECT MAX(prioritization_id)::int AS prioritizationId FROM prioritization ", new ScalarHandler("prioritizationId")); getQueryRunner().update(connection, "INSERT INTO hierarchy_prioritization(hierarchy_id, prioritization_id) VALUES(?,?) ", new Object[] { hierarchyId, createdPrioritization }); connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new PrioritizationCreationDAOException(e); } throw new PrioritizationCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.MatrixItemDAO.java
/** * Checks if a comparison matrix is ready to be prioritize. In other words, * checks if a matrix item exists without value for a given matrix (i.e. a * given iteration assignment).//from ww w .java 2s. c om * * @author Clement HELIOU (clement.heliou@che-software.com). * @param iterationAssignmentId the given iteration assignment id. * @return true if the matrix is complete, else false. * @since August, 2011. * @throws MatrixItemSearchDAOException if an error occurs during the * search. */ @Override public boolean isComparisonMatrixComplete(int iterationAssignmentId) throws MatrixItemSearchDAOException { LOGGER.debug("isComparisonMatrixComplete(" + iterationAssignmentId + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); return (Boolean) getQueryRunner().query(connection, "SELECT NOT EXISTS( SELECT comparison_matrix_item FROM comparisonMatrixItem WHERE iteration_assignment_id = ? AND value IS NULL ) AS result ", new ScalarHandler("result"), new Object[] { iterationAssignmentId }); } catch (SQLException e) { throw new MatrixItemSearchDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.HierarchyDAO.java
/** * Creates a hierarchy from a bean of criterions. * /*from ww w . ja v a 2 s . c o m*/ * @author Clement HELIOU (clement.heliou@che-software.com). * @param creationBean the bean of criterions. * @since July, 2011. * @throws HierarchyCreationDAOException if an error occurs during the * creation. */ @Override public void createHierarchyFromBean(HierarchyCreation creationBean) throws HierarchyCreationDAOException { LOGGER.debug("createHierarchyFromBean()."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); getQueryRunner().update(connection, "INSERT INTO hirearchy(hierarchy_id, creation_date, last_update_date, user_id, high_level_goal) VALUES(nextval('hierarchy_seq'), NOW(), NOW(), ?, ?) ", new Object[] { creationBean.getUser().getUserId(), creationBean.getHighLevelGoal() }); Integer createdHierarchyId = (Integer) getQueryRunner().query(connection, "SELECT MAX(hierarchy_id)::int AS hierarchy_id FROM hirearchy ", new ScalarHandler("hierarchy_id")); getQueryRunner().update(connection, "INSERT INTO hierarchy_version(version_id, hierarchy_id) VALUES(?, ?) ", new Object[] { creationBean.getVersion().getVersionId(), createdHierarchyId }); getQueryRunner().update(connection, "INSERT INTO action_plan(action_plan_id) VALUES(nextval('action_plan_seq')) "); Integer createdActionPlanId = (Integer) getQueryRunner().query(connection, "SELECT MAX(action_plan_id)::int AS action_plan_id FROM action_plan ", new ScalarHandler("action_plan_id")); getQueryRunner().update(connection, "INSERT INTO hierarchy_action_plan(hierarchy_id, action_plan_id) VALUES(?, ?) ", new Object[] { createdHierarchyId, createdActionPlanId }); connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new HierarchyCreationDAOException(e1); } throw new HierarchyCreationDAOException(e); } finally { if (null != connection) { try { connection.close(); } catch (SQLException e) { throw new HierarchyCreationDAOException(e); } } } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.VariantDAO.java
/** * Checks if variants are already existing or not for a given test case. * //from w ww. j a va2s . c om * @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); } } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.UserDAO.java
/** * Creates an user from his properties bean. * //from w w w . j ava 2s.c om * @author Clement HELIOU (clement.heliou@che-software.com). * @param userToCreateBean the properties to use to create the user. * @throws UserCreationDAOException if an error occurs during the creation. */ @Override public void createUserFromBean(UserCreation userToCreateBean) throws UserCreationDAOException { LOGGER.debug("createUserFromBean(" + userToCreateBean.getLogin() + ")."); Connection connection = null; try { connection = getDataSource().getConnection(); connection.setAutoCommit(false); getQueryRunner().update(connection, "INSERT INTO \"user\"(user_id, \"login\", \"password\", \"name\", last_name, creation_date, job) VALUES(nextval('user_seq'), ?, md5(?), ?, ?, NOW(), ?) ", new Object[] { userToCreateBean.getLogin(), userToCreateBean.getPassword(), userToCreateBean.getName().toUpperCase(), userToCreateBean.getLastName(), userToCreateBean.getJob() }); Integer createdUserID = (Integer) getQueryRunner().query(connection, "SELECT user_id::int FROM \"user\" WHERE \"login\" = ? ", new ScalarHandler("user_id"), new Object[] { userToCreateBean.getLogin() }); getQueryRunner().update(connection, "INSERT INTO user_role(user_id, role) VALUES(?, 'ROLE_USER') ", new Object[] { createdUserID }); if (userToCreateBean.isAdministrator()) { getQueryRunner().update(connection, "INSERT INTO user_role(user_id, role) VALUES(?, 'ROLE_ADMIN') ", new Object[] { createdUserID }); } if (userToCreateBean.isResponsable()) { getQueryRunner().update(connection, "INSERT INTO user_role(user_id, role) VALUES(?, 'ROLE_RESP') ", new Object[] { createdUserID }); } getQueryRunner().update(connection, "INSERT INTO user_service(user_id, service_id, hired_date) VALUES(?, ?, NOW()) ", new Object[] { createdUserID, userToCreateBean.getServiceId() }); connection.commit(); } catch (SQLException e) { try { connection.rollback(); } catch (SQLException e1) { throw new UserCreationDAOException(e1); } throw new UserCreationDAOException(e); } finally { if (null != connection) { DbUtils.closeQuietly(connection); } } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.ScriptDAO.java
/** * Creates the scripts for a given hierarchy. * /*from w ww.ja v a 2 s.c o m*/ * @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); } } }
From source file:com.che.software.testato.domain.dao.jdbc.impl.ElementDAO.java
/** * 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.//w w w . j av a 2s . c o m * * @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); } } }