Example usage for org.springframework.jdbc.datasource DataSourceTransactionManager DataSourceTransactionManager

List of usage examples for org.springframework.jdbc.datasource DataSourceTransactionManager DataSourceTransactionManager

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.datasource.*
  5. DataSourceTransactionManager
  6. DataSourceTransactionManager

Introduction

In this page you can find the example usage for org.springframework.jdbc.datasource DataSourceTransactionManager DataSourceTransactionManager.

Prototype

public DataSourceTransactionManager(DataSource dataSource)

Source Link

Document

Create a new DataSourceTransactionManager instance.

Usage

From source file:dao.DashboardDAO.java

public static ObjectNode getPagedComplianceDatasetsByManagerId(String managerId, String platform, String option,
        int page, int size) {

    DataSourceTransactionManager tm = new DataSourceTransactionManager(getJdbcTemplate().getDataSource());
    TransactionTemplate txTemplate = new TransactionTemplate(tm);

    return txTemplate.execute(new TransactionCallback<ObjectNode>() {

        public ObjectNode doInTransaction(TransactionStatus status) {
            long count;
            List<Map<String, Object>> rows = new ArrayList<>(size);
            if (StringUtils.isNotBlank(option) && !(option.equalsIgnoreCase(ALL_DATASETS))) {
                count = getPagedDashboardDatasets(managerId, GET_DATASETS_WITH_COMPLIANCE_FILTER_PLATFORM,
                        platform, option, page, size, rows);
            } else {
                count = getPagedDashboardDatasets(managerId, GET_ALL_DATASETS_BY_ID_FILTER_PLATFORM, platform,
                        null, page, size, rows);
            }//from   ww  w .j  av a 2 s.  co  m

            List<DashboardDataset> datasets = new ArrayList<>();
            for (Map row : rows) {
                DashboardDataset dashboardDataset = new DashboardDataset();
                dashboardDataset.datasetId = (Long) row.get("dataset_id");
                dashboardDataset.datasetName = (String) row.get("name");
                dashboardDataset.ownerId = (String) row.get("owner_id");
                if (dashboardDataset.datasetId != null && dashboardDataset.datasetId > 0) {
                    dashboardDataset.fields = getJdbcTemplate().queryForList(
                            GET_COMPLIANCE_FIELDS_BY_DATASET_ID, String.class, dashboardDataset.datasetId);
                }
                datasets.add(dashboardDataset);
            }

            ObjectNode resultNode = Json.newObject();
            resultNode.put("status", "ok");
            resultNode.put("count", count);
            resultNode.put("page", page);
            resultNode.put("itemsPerPage", size);
            resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
            resultNode.set("datasets", Json.toJson(datasets));
            return resultNode;
        }
    });
}

From source file:org.ohmage.query.impl.DocumentQueries.java

public void deleteDocument(String documentId) throws DataAccessException {
    // Begin transaction
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Document delete.");

    try {//from   w ww .  j a v  a 2 s  .c  o  m
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        String documentUrl = getDocumentUrl(documentId);

        try {
            getJdbcTemplate().update(SQL_DELETE_DOCUMENT, new Object[] { documentId });
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException(
                    "Error executing SQL '" + SQL_DELETE_DOCUMENT + "' with parameter: " + documentId, e);
        }

        try {
            if (!(new File((new URL(documentUrl)).getFile())).delete()) {
                LOGGER.warn(
                        "The document no longer existed, so the deletion only removed the entry from the database.");
            }
        } catch (MalformedURLException e) {
            LOGGER.warn(
                    "The URL was malformed, meaning that we couldn't have referenced the file anyway. Cannot delete the file.",
                    e);
        } catch (SecurityException e) {
            LOGGER.warn(
                    "Failed to delete the file because the security manager stopped us. Are we attempting to delete a file that isn't part of the heirarchy?",
                    e);
        }

        // Commit transaction.
        try {
            transactionManager.commit(status);
        } catch (TransactionException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while committing the transaction.", e);
        }
    } catch (TransactionException e) {
        throw new DataAccessException("Error while rolling back the transaction.", e);
    }
}

From source file:dao.AdvSearchDAO.java

public static ObjectNode search(JsonNode searchOpt, int page, int size) {
    ObjectNode resultNode = Json.newObject();
    int count = 0;
    List<String> scopeInList = new ArrayList<String>();
    List<String> scopeNotInList = new ArrayList<String>();
    List<String> tableInList = new ArrayList<String>();
    List<String> tableNotInList = new ArrayList<String>();
    List<String> fieldAnyList = new ArrayList<String>();
    List<String> fieldAllList = new ArrayList<String>();
    List<String> fieldNotInList = new ArrayList<String>();
    String fieldAllIDs = "";
    String comments = "";

    if (searchOpt != null && (searchOpt.isContainerNode())) {
        if (searchOpt.has("scope")) {
            JsonNode scopeNode = searchOpt.get("scope");
            if (scopeNode != null && scopeNode.isContainerNode()) {
                if (scopeNode.has("in")) {
                    JsonNode scopeInNode = scopeNode.get("in");
                    if (scopeInNode != null) {
                        String scopeInStr = scopeInNode.asText();
                        if (StringUtils.isNotBlank(scopeInStr)) {
                            String[] scopeInArray = scopeInStr.split(",");
                            if (scopeInArray != null) {
                                for (String value : scopeInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        scopeInList.add(value.trim());
                                    }//from  w  w w . j  a v a  2s . c o m
                                }
                            }
                        }
                    }
                }
                if (scopeNode.has("not")) {
                    JsonNode scopeNotInNode = scopeNode.get("not");
                    if (scopeNotInNode != null) {
                        String scopeNotInStr = scopeNotInNode.asText();
                        if (StringUtils.isNotBlank(scopeNotInStr)) {
                            String[] scopeNotInArray = scopeNotInStr.split(",");
                            if (scopeNotInArray != null) {
                                for (String value : scopeNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        scopeNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (searchOpt.has("table")) {
            JsonNode tableNode = searchOpt.get("table");
            if (tableNode != null && tableNode.isContainerNode()) {
                if (tableNode.has("in")) {
                    JsonNode tableInNode = tableNode.get("in");
                    if (tableInNode != null) {
                        String tableInStr = tableInNode.asText();
                        if (StringUtils.isNotBlank(tableInStr)) {
                            String[] tableInArray = tableInStr.split(",");
                            if (tableInArray != null) {
                                for (String value : tableInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        tableInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (tableNode.has("not")) {
                    JsonNode tableNotInNode = tableNode.get("not");
                    if (tableNotInNode != null) {
                        String tableNotInStr = tableNotInNode.asText();
                        if (StringUtils.isNotBlank(tableNotInStr)) {
                            String[] tableNotInArray = tableNotInStr.split(",");
                            if (tableNotInArray != null) {
                                for (String value : tableNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        tableNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (searchOpt.has("fields")) {
            JsonNode fieldNode = searchOpt.get("fields");
            if (fieldNode != null && fieldNode.isContainerNode()) {
                if (fieldNode.has("any")) {
                    JsonNode fieldAnyNode = fieldNode.get("any");
                    if (fieldAnyNode != null) {
                        String fieldAnyStr = fieldAnyNode.asText();
                        if (StringUtils.isNotBlank(fieldAnyStr)) {
                            String[] fieldAnyArray = fieldAnyStr.split(",");
                            if (fieldAnyArray != null) {
                                for (String value : fieldAnyArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        fieldAnyList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (fieldNode.has("all")) {
                    JsonNode fieldAllNode = fieldNode.get("all");
                    if (fieldAllNode != null) {
                        String fieldAllStr = fieldAllNode.asText();
                        if (StringUtils.isNotBlank(fieldAllStr)) {
                            String[] fieldAllArray = fieldAllStr.split(",");
                            if (fieldAllArray != null) {
                                for (String value : fieldAllArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        fieldAllList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (fieldNode.has("not")) {
                    JsonNode fieldNotInNode = fieldNode.get("not");
                    if (fieldNotInNode != null) {
                        String fieldNotInStr = fieldNotInNode.asText();
                        if (StringUtils.isNotBlank(fieldNotInStr)) {
                            String[] fieldNotInArray = fieldNotInStr.split(",");
                            if (fieldNotInArray != null) {
                                for (String value : fieldNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        fieldNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        String datasetSources = "";
        if (searchOpt.has("sources")) {
            JsonNode sourcesNode = searchOpt.get("sources");
            if (sourcesNode != null) {
                datasetSources = sourcesNode.asText();
            }
        }

        boolean needAndKeyword = false;
        int fieldQueryIndex = 0;
        if (fieldAllList.size() > 0) {
            String fieldAllQuery = "SELECT DISTINCT f1.dataset_id FROM dict_field_detail f1 ";
            String fieldWhereClause = " WHERE ";
            for (String field : fieldAllList) {
                fieldQueryIndex++;
                if (fieldQueryIndex == 1) {
                    fieldWhereClause += "f1.field_name LIKE '%" + field + "%' ";
                } else {
                    fieldAllQuery += "JOIN dict_field_detail f" + fieldQueryIndex + " ON f"
                            + (fieldQueryIndex - 1) + ".dataset_id = f" + fieldQueryIndex + ".dataset_id ";
                    fieldWhereClause += " and f" + fieldQueryIndex + ".field_name LIKE '%" + field + "%' ";

                }
            }
            fieldAllQuery += fieldWhereClause;
            List<Map<String, Object>> rows = getJdbcTemplate().queryForList(fieldAllQuery);
            for (Map row : rows) {

                fieldAllIDs += (Long) row.get("dataset_id") + ",";
            }
            if (fieldAllIDs.length() > 0) {
                fieldAllIDs = fieldAllIDs.substring(0, fieldAllIDs.length() - 1);
            }
            if (StringUtils.isBlank(fieldAllIDs)) {
                fieldAllIDs = Integer.toString(0);

            }
        }

        List<Dataset> pagedDatasets = new ArrayList<Dataset>();
        final JdbcTemplate jdbcTemplate = getJdbcTemplate();
        javax.sql.DataSource ds = jdbcTemplate.getDataSource();
        DataSourceTransactionManager tm = new DataSourceTransactionManager(ds);

        TransactionTemplate txTemplate = new TransactionTemplate(tm);

        ObjectNode result;

        if (searchOpt.has("comments")) {
            JsonNode commentsNode = searchOpt.get("comments");
            if (commentsNode != null) {
                comments = commentsNode.asText();
                if (StringUtils.isNotBlank(comments)) {
                    if (scopeInList.size() == 0 && scopeNotInList.size() == 0 && tableInList.size() == 0
                            && tableNotInList.size() == 0 && fieldAllList.size() == 0
                            && fieldAnyList.size() == 0 && fieldNotInList.size() == 0) {
                        final String commentsQueryStr = SEARCH_DATASETS_BY_COMMENTS_WITH_PAGINATION
                                .replace("$keyword", comments);

                        result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
                            public ObjectNode doInTransaction(TransactionStatus status) {
                                List<Map<String, Object>> rows = null;
                                rows = jdbcTemplate.queryForList(commentsQueryStr, (page - 1) * size, size);

                                for (Map row : rows) {

                                    Dataset ds = new Dataset();
                                    ds.id = (Long) row.get("id");
                                    ds.name = (String) row.get("name");
                                    ds.source = (String) row.get("source");
                                    ds.urn = (String) row.get("urn");
                                    ds.schema = (String) row.get("schema");
                                    pagedDatasets.add(ds);
                                }
                                long count = 0;
                                try {
                                    count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class);
                                } catch (EmptyResultDataAccessException e) {
                                    Logger.error("Exception = " + e.getMessage());
                                }

                                ObjectNode resultNode = Json.newObject();
                                resultNode.put("count", count);
                                resultNode.put("page", page);
                                resultNode.put("itemsPerPage", size);
                                resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
                                resultNode.set("data", Json.toJson(pagedDatasets));

                                return resultNode;
                            }
                        });
                        return result;
                    }
                }
            }
        }

        String query = "";
        if (StringUtils.isNotBlank(comments)) {
            query = "SELECT DISTINCT d.id FROM dict_dataset d";
        } else {
            query = "SELECT SQL_CALC_FOUND_ROWS " + "DISTINCT d.id, d.name, d.schema, d.source, d.urn, "
                    + "FROM_UNIXTIME(d.source_modified_time) as modified FROM dict_dataset d";
        }
        if (fieldAllList.size() > 0 || fieldAnyList.size() > 0 || fieldNotInList.size() > 0) {
            String fieldQuery = "SELECT DISTINCT dataset_id FROM dict_field_detail f WHERE (";
            query += " WHERE d.id IN ( ";
            query += fieldQuery;
            String whereClause = "";
            boolean fieldNeedAndKeyword = false;
            if (fieldAnyList.size() > 0) {
                whereClause = " (";
                int indexForAnyList = 0;
                for (String field : fieldAnyList) {
                    if (indexForAnyList == 0) {
                        whereClause += "f.field_name LIKE '%" + field + "%'";
                    } else {
                        whereClause += " or f.field_name LIKE '%" + field + "%'";
                    }
                    indexForAnyList++;
                }
                whereClause += " ) ";
                fieldNeedAndKeyword = true;
                query += whereClause;
            }
            if (fieldAllList.size() > 0 && StringUtils.isNotBlank(fieldAllIDs)) {
                if (fieldNeedAndKeyword) {
                    whereClause = " and (";
                } else {
                    whereClause = " (";
                }
                whereClause += "f.dataset_id IN (" + fieldAllIDs + ")";
                whereClause += " ) ";
                query += whereClause;
                fieldNeedAndKeyword = true;
            }
            if (fieldNotInList.size() > 0) {
                if (fieldNeedAndKeyword) {
                    whereClause = " and ( f.dataset_id not in (select dataset_id from dict_field_detail where";
                } else {
                    whereClause = " ( f.dataset_id not in (select dataset_id from dict_field_detail where";
                }
                int indexForNotInList = 0;
                for (String field : fieldNotInList) {
                    if (indexForNotInList == 0) {
                        whereClause += " field_name LIKE '%" + field + "%'";
                    } else {
                        whereClause += " or field_name LIKE '%" + field + "%'";
                    }
                    indexForNotInList++;
                }
                whereClause += " )) ";
                query += whereClause;
                fieldNeedAndKeyword = true;
            }
            needAndKeyword = true;
            query += ") )";
        }

        if (scopeInList.size() > 0 || scopeNotInList.size() > 0) {
            if (needAndKeyword) {
                query += " and";
            } else {
                query += " where";
            }
            boolean scopeNeedAndKeyword = false;
            if (scopeInList.size() > 0) {
                query += " d.parent_name in (";
                scopeNeedAndKeyword = true;
                int indexForScopeInList = 0;
                for (String scope : scopeInList) {
                    if (indexForScopeInList == 0) {
                        query += "'" + scope + "'";
                    } else {
                        query += ", '" + scope + "'";
                    }
                    indexForScopeInList++;
                }
                query += ") ";
            }
            if (scopeNotInList.size() > 0) {
                if (scopeNeedAndKeyword) {
                    query += " and d.parent_name not in (";
                } else {
                    query += " d.parent_name not in (";
                }
                int indexForScopeNotInList = 0;
                for (String scope : scopeNotInList) {
                    if (indexForScopeNotInList == 0) {
                        query += "'" + scope + "'";
                    } else {
                        query += ", '" + scope + "'";
                    }
                    indexForScopeNotInList++;
                }
                query += ") ";
            }
            needAndKeyword = true;
        }
        String condition1 = "";
        String condition2 = "";
        String condition3 = "";
        String condition4 = "";

        if (tableInList.size() > 0 || tableNotInList.size() > 0) {
            if (needAndKeyword) {
                query += " and";
            } else {
                query += " where";
            }
            boolean tableNeedAndKeyword = false;
            if (tableInList.size() > 0) {
                query += " (";
                int indexForTableInList = 0;
                for (String table : tableInList) {
                    if (indexForTableInList == 0) {
                        query += "d.name LIKE '%" + table + "%'";
                    } else {
                        condition1 += " or ";
                        condition2 += " or ";
                        condition3 += " or ";
                        condition4 += " or ";
                        query += " or d.name LIKE '%" + table + "%'";
                    }
                    condition1 += "name = '" + table + "'";
                    condition2 += "name LIKE '" + table + "%'";
                    condition3 += "name LIKE '%" + table + "'";
                    condition4 += "name LIKE '%" + table + "%'";
                    indexForTableInList++;
                }
                query += " ) ";
                tableNeedAndKeyword = true;
            }
            if (tableNotInList.size() > 0) {
                if (tableNeedAndKeyword) {
                    query += " and (";
                } else {
                    query += " (";
                }
                int indexForTableNotInList = 0;
                for (String table : tableNotInList) {
                    if (indexForTableNotInList == 0) {
                        query += "d.name NOT LIKE '%" + table + "%'";
                    } else {
                        query += " and d.name NOT LIKE '%" + table + "%'";
                    }
                    indexForTableNotInList++;
                }
                query += " ) ";
            }
            needAndKeyword = true;
        }

        if (StringUtils.isNotBlank(datasetSources)) {
            if (needAndKeyword) {
                query += " and";
            } else {
                query += " WHERE";
            }
            query += " d.source in (";
            String[] dataestSourceArray = datasetSources.split(",");
            for (int i = 0; i < dataestSourceArray.length; i++) {
                query += "'" + dataestSourceArray[i] + "'";
                if (i != (dataestSourceArray.length - 1)) {
                    query += ",";
                }
            }
            query += ")";
        }
        if ((tableInList.size() > 0 || tableNotInList.size() > 0) && StringUtils.isNotBlank(condition1)
                && StringUtils.isNotBlank(condition2) && StringUtils.isNotBlank(condition3)
                && StringUtils.isNotBlank(condition4)) {
            query += ADVSEARCH_RANK_CLAUSE.replace("$condition1", condition1).replace("$condition2", condition2)
                    .replace("$condition3", condition3).replace("$condition4", condition4);
        } else {
            query += " ORDER BY CASE WHEN urn LIKE 'teradata://DWH_%' THEN 2 "
                    + "WHEN urn LIKE 'hdfs://data/tracking/%' THEN 1 "
                    + "WHEN urn LIKE 'teradata://DWH/%' THEN 3 "
                    + "WHEN urn LIKE 'hdfs://data/databases/%' THEN 4 "
                    + "WHEN urn LIKE 'hdfs://data/dervied/%' THEN 5 ELSE 99 end, urn";
        }
        if (StringUtils.isBlank(comments)) {
            query += " LIMIT " + (page - 1) * size + ", " + size;
            final String queryString = query;

            result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
                public ObjectNode doInTransaction(TransactionStatus status) {
                    List<Map<String, Object>> rows = null;
                    rows = jdbcTemplate.queryForList(queryString);

                    for (Map row : rows) {

                        Dataset ds = new Dataset();
                        ds.id = (Long) row.get("id");
                        ds.name = (String) row.get("name");
                        ds.source = (String) row.get("source");
                        ds.urn = (String) row.get("urn");
                        ds.schema = (String) row.get("schema");
                        pagedDatasets.add(ds);
                    }
                    long count = 0;
                    try {
                        count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class);
                    } catch (EmptyResultDataAccessException e) {
                        Logger.error("Exception = " + e.getMessage());
                    }

                    ObjectNode resultNode = Json.newObject();
                    resultNode.put("count", count);
                    resultNode.put("page", page);
                    resultNode.put("itemsPerPage", size);
                    resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
                    resultNode.set("data", Json.toJson(pagedDatasets));

                    return resultNode;
                }
            });
            return result;
        } else {
            String datasetIDStr = "";
            final String queryString = query;

            datasetIDStr = txTemplate.execute(new TransactionCallback<String>() {
                public String doInTransaction(TransactionStatus status) {
                    List<Map<String, Object>> rows = null;
                    rows = jdbcTemplate.queryForList(queryString);
                    String idsString = "";

                    for (Map row : rows) {

                        Long id = (Long) row.get("id");
                        idsString += Long.toString(id) + ",";
                    }
                    if (StringUtils.isNotBlank(idsString)) {
                        idsString = idsString.substring(0, idsString.length() - 1);
                    }
                    return idsString;
                }
            });
            if (StringUtils.isBlank(datasetIDStr)) {
                resultNode.put("count", 0);
                resultNode.put("page", page);
                resultNode.put("itemsPerPage", size);
                resultNode.put("totalPages", 0);
                resultNode.set("data", Json.toJson(""));
                return resultNode;
            }
            final String commentsQueryWithConditionStr = DATASET_BY_COMMENT_PAGINATION_IN_CLAUSE
                    .replace("$keyword", comments).replace("$id_list", datasetIDStr);
            result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
                public ObjectNode doInTransaction(TransactionStatus status) {
                    List<Map<String, Object>> rows = null;
                    rows = jdbcTemplate.queryForList(commentsQueryWithConditionStr, (page - 1) * size, size);

                    for (Map row : rows) {

                        Dataset ds = new Dataset();
                        ds.id = (Long) row.get("id");
                        ds.name = (String) row.get("name");
                        ds.source = (String) row.get("source");
                        ds.urn = (String) row.get("urn");
                        ds.schema = (String) row.get("schema");
                        pagedDatasets.add(ds);
                    }
                    long count = 0;
                    try {
                        count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class);
                    } catch (EmptyResultDataAccessException e) {
                        Logger.error("Exception = " + e.getMessage());
                    }

                    ObjectNode resultNode = Json.newObject();
                    resultNode.put("count", count);
                    resultNode.put("page", page);
                    resultNode.put("itemsPerPage", size);
                    resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
                    resultNode.set("data", Json.toJson(pagedDatasets));

                    return resultNode;
                }
            });
            return result;
        }
    }
    resultNode.put("count", 0);
    resultNode.put("page", page);
    resultNode.put("itemsPerPage", size);
    resultNode.put("totalPages", 0);
    resultNode.set("data", Json.toJson(""));
    return resultNode;
}

From source file:org.ohmage.query.impl.SurveyResponseQueries.java

public void deleteSurveyResponse(final UUID surveyResponseId) throws DataAccessException {

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Deleting a survey response.");

    try {//from w w  w .ja v a  2 s  .c  o m
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        try {
            getJdbcTemplate().update(SQL_DELETE_SURVEY_RESPONSE, new Object[] { surveyResponseId.toString() });
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error executing SQL '" + SQL_DELETE_SURVEY_RESPONSE
                    + "' with parameter: " + surveyResponseId.toString(), e);
        }

        // Commit the transaction.
        try {
            transactionManager.commit(status);
        } catch (TransactionException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while committing the transaction.", e);
        }
    } catch (TransactionException e) {
        throw new DataAccessException("Error while attempting to rollback the transaction.", e);
    }
}

From source file:dao.SearchDAO.java

public static ObjectNode getPagedFlowByKeyword(String category, String keyword, int page, int size) {
    final List<FlowJob> pagedFlows = new ArrayList<FlowJob>();
    final JdbcTemplate jdbcTemplate = getJdbcTemplate();
    javax.sql.DataSource ds = jdbcTemplate.getDataSource();
    DataSourceTransactionManager tm = new DataSourceTransactionManager(ds);

    TransactionTemplate txTemplate = new TransactionTemplate(tm);

    ObjectNode result;/*from  w w  w . j a  v a  2 s . com*/
    result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
        public ObjectNode doInTransaction(TransactionStatus status) {
            String query = SEARCH_FLOW_WITH_PAGINATION.replace("$keyword", keyword);
            List<Map<String, Object>> rows = null;

            rows = jdbcTemplate.queryForList(query, (page - 1) * size, size);
            for (Map row : rows) {

                FlowJob flow = new FlowJob();
                flow.flowId = (Long) row.get(FlowRowMapper.FLOW_ID_COLUMN);
                flow.flowName = (String) row.get(FlowRowMapper.FLOW_NAME_COLUMN);
                flow.flowPath = (String) row.get(FlowRowMapper.FLOW_PATH_COLUMN);
                flow.flowGroup = (String) row.get(FlowRowMapper.FLOW_GROUP_COLUMN);
                flow.appCode = (String) row.get(FlowRowMapper.APP_CODE_COLUMN);
                flow.appId = (Integer) row.get(FlowRowMapper.APP_ID_COLUMN);
                flow.displayName = flow.flowName;
                flow.link = "#/flows/name/" + flow.appCode + "/" + Long.toString(flow.flowId) + "/page/1?urn="
                        + flow.flowGroup;
                flow.path = flow.appCode + "/" + flow.flowPath;
                pagedFlows.add(flow);
            }
            long count = 0;
            try {
                count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class);
            } catch (EmptyResultDataAccessException e) {
                Logger.error("Exception = " + e.getMessage());
            }

            ObjectNode resultNode = Json.newObject();
            resultNode.put("count", count);
            resultNode.put("isFlowJob", true);
            resultNode.put("page", page);
            resultNode.put("category", category);
            resultNode.put("itemsPerPage", size);
            resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
            resultNode.set("data", Json.toJson(pagedFlows));

            return resultNode;
        }
    });

    return result;
}

From source file:dao.SearchDAO.java

public static ObjectNode getPagedJobByKeyword(String category, String keyword, int page, int size) {
    final List<FlowJob> pagedFlowJobs = new ArrayList<FlowJob>();
    final JdbcTemplate jdbcTemplate = getJdbcTemplate();
    javax.sql.DataSource ds = jdbcTemplate.getDataSource();
    DataSourceTransactionManager tm = new DataSourceTransactionManager(ds);

    TransactionTemplate txTemplate = new TransactionTemplate(tm);

    ObjectNode result;//from  w ww  . j  a v a 2  s  .c o  m
    result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
        public ObjectNode doInTransaction(TransactionStatus status) {
            String query = SEARCH_JOB_WITH_PAGINATION.replace("$keyword", keyword);
            List<Map<String, Object>> rows = null;

            rows = jdbcTemplate.queryForList(query, (page - 1) * size, size);
            for (Map row : rows) {

                FlowJob flowJob = new FlowJob();
                flowJob.flowId = (Long) row.get(FlowRowMapper.FLOW_ID_COLUMN);
                flowJob.jobId = (Long) row.get(FlowRowMapper.JOB_ID_COLUMN);
                flowJob.jobName = (String) row.get(FlowRowMapper.JOB_NAME_COLUMN);
                flowJob.jobPath = (String) row.get(FlowRowMapper.JOB_PATH_COLUMN);
                flowJob.jobType = (String) row.get(FlowRowMapper.JOB_TYPE_COLUMN);
                flowJob.flowName = (String) row.get(FlowRowMapper.FLOW_NAME_COLUMN);
                flowJob.flowPath = (String) row.get(FlowRowMapper.FLOW_PATH_COLUMN);
                flowJob.flowGroup = (String) row.get(FlowRowMapper.FLOW_GROUP_COLUMN);
                flowJob.appCode = (String) row.get(FlowRowMapper.APP_CODE_COLUMN);
                flowJob.appId = (Integer) row.get(FlowRowMapper.APP_ID_COLUMN);
                flowJob.displayName = flowJob.jobName;
                flowJob.link = "#/flows/name/" + flowJob.appCode + "/" + Long.toString(flowJob.flowId)
                        + "/page/1?urn=" + flowJob.flowGroup;
                flowJob.path = flowJob.appCode + "/" + flowJob.jobPath;

                pagedFlowJobs.add(flowJob);
            }
            long count = 0;
            try {
                count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class);
            } catch (EmptyResultDataAccessException e) {
                Logger.error("Exception = " + e.getMessage());
            }

            ObjectNode resultNode = Json.newObject();
            resultNode.put("count", count);
            resultNode.put("isFlowJob", true);
            resultNode.put("page", page);
            resultNode.put("category", category);
            resultNode.put("itemsPerPage", size);
            resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
            resultNode.set("data", Json.toJson(pagedFlowJobs));

            return resultNode;
        }
    });

    return result;
}

From source file:dao.SearchDAO.java

public static ObjectNode getPagedCommentsByKeyword(String category, String keyword, int page, int size) {
    List<Dataset> pagedDatasets = new ArrayList<Dataset>();
    final JdbcTemplate jdbcTemplate = getJdbcTemplate();
    javax.sql.DataSource ds = jdbcTemplate.getDataSource();
    DataSourceTransactionManager tm = new DataSourceTransactionManager(ds);

    TransactionTemplate txTemplate = new TransactionTemplate(tm);

    ObjectNode result;/*from   w  w  w  . j  a  v a2 s  .c o  m*/
    result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
        public ObjectNode doInTransaction(TransactionStatus status) {
            List<Map<String, Object>> rows = null;
            String query = SEARCH_DATASET_BY_COMMENTS_WITH_PAGINATION.replace("$keyword", keyword);
            rows = jdbcTemplate.queryForList(query, (page - 1) * size, size);

            for (Map row : rows) {

                Dataset ds = new Dataset();
                ds.id = (long) row.get(DatasetRowMapper.DATASET_ID_COLUMN);
                ds.name = (String) row.get(DatasetRowMapper.DATASET_NAME_COLUMN);
                ds.source = (String) row.get(DatasetRowMapper.DATASET_SOURCE_COLUMN);
                ds.urn = (String) row.get(DatasetRowMapper.DATASET_URN_COLUMN);
                ds.schema = (String) row.get(DatasetRowMapper.DATASET_SCHEMA_COLUMN);
                pagedDatasets.add(ds);
            }
            long count = 0;
            try {
                count = jdbcTemplate.queryForObject("SELECT FOUND_ROWS()", Long.class);
            } catch (EmptyResultDataAccessException e) {
                Logger.error("Exception = " + e.getMessage());
            }

            ObjectNode resultNode = Json.newObject();
            resultNode.put("count", count);
            resultNode.put("page", page);
            resultNode.put("category", category);
            resultNode.put("itemsPerPage", size);
            resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
            resultNode.set("data", Json.toJson(pagedDatasets));

            return resultNode;
        }
    });

    return result;
}

From source file:org.ohmage.query.impl.ClassQueries.java

@Override
public void deleteClass(String classId) throws DataAccessException {
    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Deleting a class.");

    try {//from  w  ww.  ja  va  2 s  .  c  om
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        try {
            getJdbcTemplate().update(SQL_DELETE_CLASS, new Object[] { classId });
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException(
                    "Error while executing SQL '" + SQL_DELETE_CLASS + "' with parameter: " + classId, e);
        }

        // Commit the transaction.
        try {
            transactionManager.commit(status);
        } catch (TransactionException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while committing the transaction.", e);
        }
    } catch (TransactionException e) {
        throw new DataAccessException("Error while attempting to rollback the transaction.", e);
    }
}

From source file:dao.DatasetsDAO.java

public static ObjectNode getPagedDatasetComments(String userName, int id, int page, int size) {
    ObjectNode result = Json.newObject();

    javax.sql.DataSource ds = getJdbcTemplate().getDataSource();
    DataSourceTransactionManager tm = new DataSourceTransactionManager(ds);
    TransactionTemplate txTemplate = new TransactionTemplate(tm);

    result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
        public ObjectNode doInTransaction(TransactionStatus status) {

            List<DatasetComment> pagedComments = getJdbcTemplate().query(GET_COMMENTS_BY_DATASET_ID,
                    new DatasetCommentRowMapper(), id, (page - 1) * size, size);

            long count = 0;
            try {
                count = getJdbcTemplate().queryForObject("SELECT FOUND_ROWS()", Long.class);
            } catch (EmptyResultDataAccessException e) {
                Logger.error("Exception = " + e.getMessage());
            }//from w  ww. j  a  va  2  s. c o  m

            if (pagedComments != null) {
                for (DatasetComment dc : pagedComments) {
                    if (StringUtils.isNotBlank(userName) && userName.equalsIgnoreCase(dc.authorUserName)) {
                        dc.isAuthor = true;
                    }
                }
            }

            ObjectNode resultNode = Json.newObject();
            resultNode.set("comments", Json.toJson(pagedComments));
            resultNode.put("count", count);
            resultNode.put("page", page);
            resultNode.put("itemsPerPage", size);
            resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));

            return resultNode;
        }
    });
    return result;
}

From source file:org.ohmage.query.impl.CampaignQueries.java

public void updateCampaign(String campaignId, String xml, String description,
        Campaign.RunningState runningState, Campaign.PrivacyState privacyState, Collection<String> classesToAdd,
        Collection<String> classesToRemove, Map<String, Set<Campaign.Role>> usersAndRolesToAdd,
        Map<String, Set<Campaign.Role>> usersAndRolesToRemove) throws DataAccessException {

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Updating a campaign.");

    try {/*from  w ww  . j a  v  a2  s  .co m*/
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        // Update the XML if it is present.
        if (xml != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_XML, new Object[] { xml, campaignId });
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_XML + "' with parameters: "
                        + xml + ", " + campaignId, e);
            }
        }

        // Update the description if it is present.
        if (description != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_DESCRIPTION, new Object[] { description, campaignId });
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_DESCRIPTION
                        + "' with parameters: " + description + ", " + campaignId, e);
            }
        }

        // Update the running state if it is present.
        if (runningState != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_RUNNING_STATE,
                        new Object[] { runningState.toString(), campaignId });
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_RUNNING_STATE
                        + "' with parameters: " + runningState + ", " + campaignId, e);
            }
        }

        // Update the privacy state if it is present.
        if (privacyState != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_PRIVACY_STATE,
                        new Object[] { privacyState.toString(), campaignId });
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_PRIVACY_STATE
                        + "' with parameters: " + privacyState + ", " + campaignId, e);
            }
        }

        // Add the specific users with specific roles.
        if (usersAndRolesToAdd != null) {
            for (String username : usersAndRolesToAdd.keySet()) {
                for (Campaign.Role role : usersAndRolesToAdd.get(username)) {
                    try {
                        getJdbcTemplate().update(SQL_INSERT_USER_ROLE_CAMPAIGN,
                                new Object[] { username, campaignId, role.toString() });
                    } catch (org.springframework.dao.DuplicateKeyException e) {
                        // This means that the user already had the role in
                        // the campaign. We can ignore this.
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("Error executing SQL '" + SQL_INSERT_USER_ROLE_CAMPAIGN
                                + "' with parameters: " + username + ", " + campaignId + ", " + role, e);
                    }
                }
            }
        }

        // Remove the specific users and their roles.
        if (usersAndRolesToRemove != null) {
            for (String username : usersAndRolesToRemove.keySet()) {
                for (Campaign.Role role : usersAndRolesToRemove.get(username)) {
                    try {
                        getJdbcTemplate().update(SQL_DELETE_USER_ROLE_CAMPAIGN,
                                new Object[] { username, campaignId, role.toString() });
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("Error executing SQL '" + SQL_DELETE_USER_ROLE_CAMPAIGN
                                + "' with parameters: " + username + ", " + campaignId + ", " + role, e);
                    }
                }
            }
        }

        if (classesToRemove != null) {
            // For all of the classes that are associated with the campaign
            // but are not in the classIds list,
            for (String classId : classesToRemove) {
                // For each of the users in the class, if they are only 
                // associated with the campaign through this class then 
                // remove them.
                List<String> usernames;
                try {
                    usernames = userClassQueries.getUsersInClass(classId);
                } catch (DataAccessException e) {
                    transactionManager.rollback(status);
                    throw e;
                }

                for (String username : usernames) {
                    // If the user is not associated with the campaign 
                    // through any other class, they are removed from the
                    // campaign.
                    int numClasses;
                    try {
                        numClasses = userCampaignClassQueries
                                .getNumberOfClassesThroughWhichUserIsAssociatedWithCampaign(username,
                                        campaignId);
                    } catch (DataAccessException e) {
                        transactionManager.rollback(status);
                        throw e;
                    }
                    if (numClasses == 1) {
                        // Retrieve the default roles that the user was 
                        // given when they joined the class.
                        List<Campaign.Role> roles;
                        try {
                            roles = getJdbcTemplate().query(SQL_GET_USER_DEFAULT_ROLES,
                                    new Object[] { username, campaignId, classId },
                                    new RowMapper<Campaign.Role>() {
                                        @Override
                                        public Campaign.Role mapRow(ResultSet rs, int rowNum)
                                                throws SQLException {
                                            return Campaign.Role.getValue(rs.getString("role"));
                                        }
                                    });
                        } catch (org.springframework.dao.DataAccessException e) {
                            transactionManager.rollback(status);
                            throw new DataAccessException("Error executing SQL '" + SQL_GET_USER_DEFAULT_ROLES
                                    + "' with parameters: " + username + ", " + campaignId + ", " + classId, e);
                        }

                        for (Campaign.Role role : roles) {
                            try {
                                getJdbcTemplate().update(SQL_DELETE_USER_ROLE_CAMPAIGN,
                                        new Object[] { username, campaignId, role.toString() });
                            } catch (org.springframework.dao.DataAccessException e) {
                                transactionManager.rollback(status);
                                throw new DataAccessException("Error executing SQL '"
                                        + SQL_DELETE_USER_ROLE_CAMPAIGN + "' with parameters: " + username
                                        + ", " + campaignId + ", " + role, e);
                            }
                        }
                    }
                }

                // Remove the campaign, class association.
                try {
                    getJdbcTemplate().update(SQL_DELETE_CAMPAIGN_CLASS, new Object[] { campaignId, classId });
                } catch (org.springframework.dao.DataAccessException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException("Error executing SQL '" + SQL_DELETE_CAMPAIGN_CLASS
                            + "' with parameters: " + campaignId + ", " + classId, e);
                }
            }
        }

        if (classesToAdd != null) {
            // For all of the classes that are in the classIds list but not
            // associated with the campaign,
            for (String classId : classesToAdd) {
                associateCampaignAndClass(transactionManager, status, campaignId, classId);
            }
        }

        // Commit the transaction.
        try {
            transactionManager.commit(status);
        } catch (TransactionException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error while committing the transaction.", e);
        }
    } catch (TransactionException e) {
        throw new DataAccessException("Error while attempting to rollback the transaction.", e);
    }
}