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.DatasetsDAO.java

public static ObjectNode getPagedDatasetColumnComments(String userName, int datasetId, int columnId, 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) {

            ObjectNode resultNode = Json.newObject();
            long count = 0;
            int start = (page - 1) * size;
            int end = start + size;
            List<DatasetColumnComment> pagedComments = new ArrayList<DatasetColumnComment>();
            List<Map<String, Object>> rows = null;

            rows = getJdbcTemplate().queryForList(GET_COLUMN_COMMENTS_BY_DATASETID_AND_COLUMNID, datasetId,
                    columnId, start, end);
            for (Map row : rows) {
                Long id = (Long) row.get("id");
                String author = (String) row.get("author");
                String authorEmail = (String) row.get("authorEmail");
                String authorUsername = (String) row.get("authorUsername");
                String text = (String) row.get("text");
                String created = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss")
                        .format((Timestamp) row.get("created"));
                String modified = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss")
                        .format((Timestamp) row.get("modified"));
                Long columnId = (Long) row.get("field_id");
                boolean isDefault = (Boolean) row.get("is_default");

                DatasetColumnComment datasetColumnComment = new DatasetColumnComment();
                datasetColumnComment.id = id;
                datasetColumnComment.author = author;
                datasetColumnComment.authorEmail = authorEmail;
                datasetColumnComment.authorUsername = authorUsername;
                datasetColumnComment.text = text;
                datasetColumnComment.created = created;
                datasetColumnComment.modified = modified;
                datasetColumnComment.columnId = columnId;
                datasetColumnComment.isDefault = isDefault;
                pagedComments.add(datasetColumnComment);
            }// w  ww  .j  av a  2 s  . com

            try {
                count = getJdbcTemplate().queryForObject("SELECT FOUND_ROWS()", Long.class);
            } catch (EmptyResultDataAccessException e) {
                Logger.error("Exception = " + e.getMessage());
            }

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

            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:dao.AdvSearchDAO.java

public static ObjectNode searchFlows(JsonNode searchOpt, int page, int size) {
    ObjectNode resultNode = Json.newObject();
    int count = 0;
    List<String> appcodeInList = new ArrayList<String>();
    List<String> appcodeNotInList = new ArrayList<String>();
    List<String> flowInList = new ArrayList<String>();
    List<String> flowNotInList = new ArrayList<String>();
    List<String> jobInList = new ArrayList<String>();
    List<String> jobNotInList = new ArrayList<String>();

    if (searchOpt != null && (searchOpt.isContainerNode())) {
        if (searchOpt.has("appcode")) {
            JsonNode appcodeNode = searchOpt.get("appcode");
            if (appcodeNode != null && appcodeNode.isContainerNode()) {
                if (appcodeNode.has("in")) {
                    JsonNode appcodeInNode = appcodeNode.get("in");
                    if (appcodeInNode != null) {
                        String appcodeInStr = appcodeInNode.asText();
                        if (StringUtils.isNotBlank(appcodeInStr)) {
                            String[] appcodeInArray = appcodeInStr.split(",");
                            if (appcodeInArray != null) {
                                for (String value : appcodeInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        appcodeInList.add(value.trim());
                                    }//from   w  w w .  ja v  a  2 s  . co  m
                                }
                            }
                        }
                    }
                }
                if (appcodeNode.has("not")) {
                    JsonNode appcodeNotInNode = appcodeNode.get("not");
                    if (appcodeNotInNode != null) {
                        String appcodeNotInStr = appcodeNotInNode.asText();
                        if (StringUtils.isNotBlank(appcodeNotInStr)) {
                            String[] appcodeNotInArray = appcodeNotInStr.split(",");
                            if (appcodeNotInArray != null) {
                                for (String value : appcodeNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        appcodeNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (searchOpt.has("flow")) {
            JsonNode flowNode = searchOpt.get("flow");
            if (flowNode != null && flowNode.isContainerNode()) {
                if (flowNode.has("in")) {
                    JsonNode flowInNode = flowNode.get("in");
                    if (flowInNode != null) {
                        String flowInStr = flowInNode.asText();
                        if (StringUtils.isNotBlank(flowInStr)) {
                            String[] flowInArray = flowInStr.split(",");
                            if (flowInArray != null) {
                                for (String value : flowInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        flowInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (flowNode.has("not")) {
                    JsonNode flowNotInNode = flowNode.get("not");
                    if (flowNotInNode != null) {
                        String flowNotInStr = flowNotInNode.asText();
                        if (StringUtils.isNotBlank(flowNotInStr)) {
                            String[] flowNotInArray = flowNotInStr.split(",");
                            if (flowNotInArray != null) {
                                for (String value : flowNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        flowNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (searchOpt.has("job")) {
            JsonNode jobNode = searchOpt.get("job");
            if (jobNode != null && jobNode.isContainerNode()) {
                if (jobNode.has("in")) {
                    JsonNode jobInNode = jobNode.get("in");
                    if (jobInNode != null) {
                        String jobInStr = jobInNode.asText();
                        if (StringUtils.isNotBlank(jobInStr)) {
                            String[] jobInArray = jobInStr.split(",");
                            if (jobInArray != null) {
                                for (String value : jobInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        jobInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (jobNode.has("not")) {
                    JsonNode jobNotInNode = jobNode.get("not");
                    if (jobNotInNode != null) {
                        String jobNotInStr = jobNotInNode.asText();
                        if (StringUtils.isNotBlank(jobNotInStr)) {
                            String[] jobNotInArray = jobNotInStr.split(",");
                            if (jobNotInArray != null) {
                                for (String value : jobNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        jobNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        boolean needAndKeyword = false;

        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;
        String query = null;
        if (jobInList.size() > 0 || jobNotInList.size() > 0) {
            query = ADV_SEARCH_JOB;
        } else {
            query = ADV_SEARCH_FLOW;
        }

        if (appcodeInList.size() > 0 || appcodeNotInList.size() > 0) {
            boolean appcodeNeedAndKeyword = false;
            if (appcodeInList.size() > 0) {
                int indexForAppcodeInList = 0;
                for (String appcode : appcodeInList) {
                    if (indexForAppcodeInList == 0) {
                        query += "WHERE a.app_code in ('" + appcode + "'";
                    } else {
                        query += ", '" + appcode + "'";
                    }
                    indexForAppcodeInList++;
                }
                query += ") ";
                appcodeNeedAndKeyword = true;
            }
            if (appcodeNotInList.size() > 0) {
                if (appcodeNeedAndKeyword) {
                    query += " AND ";
                } else {
                    query += " WHERE ";
                }
                int indexForAppcodeNotInList = 0;
                for (String appcode : appcodeNotInList) {
                    if (indexForAppcodeNotInList == 0) {
                        query += "a.app_code not in ('" + appcode + "'";
                    } else {
                        query += ", '" + appcode + "'";
                    }
                    indexForAppcodeNotInList++;
                }
                query += ") ";
            }
            needAndKeyword = true;
        }

        if (flowInList.size() > 0 || flowNotInList.size() > 0) {
            if (needAndKeyword) {
                query += " AND ";
            } else {
                query += " WHERE ";
            }
            boolean flowNeedAndKeyword = false;
            if (flowInList.size() > 0) {
                query += "( ";
                int indexForFlowInList = 0;
                for (String flow : flowInList) {
                    if (indexForFlowInList == 0) {
                        query += "f.flow_name LIKE '%" + flow + "%'";
                    } else {
                        query += " or f.flow_name LIKE '%" + flow + "%'";
                    }
                    indexForFlowInList++;
                }
                query += ") ";
                flowNeedAndKeyword = true;
            }
            if (flowNotInList.size() > 0) {
                if (flowNeedAndKeyword) {
                    query += " AND ";
                }
                query += "( ";
                int indexForFlowNotInList = 0;
                for (String flow : flowNotInList) {
                    if (indexForFlowNotInList == 0) {
                        query += "f.flow_name NOT LIKE '%" + flow + "%'";
                    } else {
                        query += " and f.flow_name NOT LIKE '%" + flow + "%'";
                    }
                    indexForFlowNotInList++;
                }
                query += ") ";
            }
            needAndKeyword = true;
        }

        if (jobInList.size() > 0 || jobNotInList.size() > 0) {
            if (needAndKeyword) {
                query += " AND ";
            } else {
                query += " WHERE ";
            }
            query += "( ";
            boolean jobNeedAndKeyword = false;
            if (jobInList.size() > 0) {
                query += "( ";
                int indexForJobInList = 0;
                for (String job : jobInList) {
                    if (indexForJobInList == 0) {
                        query += "j.job_name LIKE '%" + job + "%'";
                    } else {
                        query += " or j.job_name LIKE '%" + job + "%'";
                    }
                    indexForJobInList++;
                }
                query += ") ";
                jobNeedAndKeyword = true;
            }
            if (jobNotInList.size() > 0) {
                if (jobNeedAndKeyword) {
                    query += " AND ";
                }
                query += "( ";
                int indexForJobNotInList = 0;
                for (String job : jobNotInList) {
                    if (indexForJobNotInList == 0) {
                        query += "j.job_name NOT LIKE '%" + job + "%'";
                    } else {
                        query += " and j.job_name NOT LIKE '%" + job + "%'";
                    }
                    indexForJobNotInList++;
                }
                query += ") ";
            }
            query += " ) ";
        }

        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) {

                    FlowJob flow = new FlowJob();
                    flow.appCode = (String) row.get("app_code");
                    flow.flowName = (String) row.get("flow_name");
                    flow.flowPath = (String) row.get("flow_path");
                    flow.flowGroup = (String) row.get("flow_group");
                    flow.jobName = (String) row.get("job_name");
                    flow.jobPath = (String) row.get("job_path");
                    flow.flowId = (Long) row.get("flow_id");
                    if (StringUtils.isNotBlank(flow.jobName)) {
                        flow.displayName = flow.jobName;
                    } else {
                        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("page", page);
                resultNode.put("isFlowJob", true);
                resultNode.put("itemsPerPage", size);
                resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
                resultNode.set("data", Json.toJson(pagedFlows));

                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.UserQueries.java

@Override
public void updateUser(final String username, final String emailAddress, final Boolean admin,
        final Boolean enabled, final Boolean newAccount, final Boolean campaignCreationPrivilege,
        final Boolean classCreationPrivilege, final Boolean userSetupPrivilege, final String firstName,
        final String lastName, final String organization, final String personalId,
        final boolean deletePersonalInfo) throws DataAccessException {

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Updating a user's privileges and information.");

    try {/*from w ww .j a  va 2  s  .  com*/
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        if (emailAddress != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_EMAIL_ADDRESS, emailAddress, username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing the following SQL '" + SQL_UPDATE_EMAIL_ADDRESS
                        + "' with parameters: " + emailAddress + ", " + username, e);
            }
        }

        // Update the admin value if it's not null.
        if (admin != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_ADMIN, admin, username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing the following SQL '" + SQL_UPDATE_ADMIN
                        + "' with parameters: " + admin + ", " + username, e);
            }
        }

        // Update the enabled value if it's not null.
        if (enabled != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_ENABLED, enabled, username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing the following SQL '" + SQL_UPDATE_ENABLED
                        + "' with parameters: " + enabled + ", " + username, e);
            }
        }

        // Update the new account value if it's not null.
        if (newAccount != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_NEW_ACCOUNT, newAccount, username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing the following SQL '" + SQL_UPDATE_NEW_ACCOUNT
                        + "' with parameters: " + newAccount + ", " + username, e);
            }
        }

        // Update the campaign creation privilege value if it's not null.
        if (campaignCreationPrivilege != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_CAMPAIGN_CREATION_PRIVILEGE, campaignCreationPrivilege,
                        username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException(
                        "Error executing the following SQL '" + SQL_UPDATE_CAMPAIGN_CREATION_PRIVILEGE
                                + "' with parameters: " + campaignCreationPrivilege + ", " + username,
                        e);
            }
        }

        // Update the class creation privilege value if it's not null.
        if (classCreationPrivilege != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_CLASS_CREATION_PRIVILEGE, classCreationPrivilege, username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException(
                        "Error executing the following SQL '" + SQL_UPDATE_CLASS_CREATION_PRIVILEGE
                                + "' with parameters: " + classCreationPrivilege + ", " + username,
                        e);
            }
        }

        // Update the user setup privilege value if it's not null.
        if (userSetupPrivilege != null) {
            try {
                getJdbcTemplate().update(SQL_UPDATE_USER_SETUP_PRIVILEGE, userSetupPrivilege, username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException(
                        "Error executing the following SQL '" + SQL_UPDATE_USER_SETUP_PRIVILEGE
                                + "' with parameters: " + userSetupPrivilege + ", " + username,
                        e);
            }
        }

        // If we are deleting the user's personal information, then we 
        // won't add new or update existing personal information.
        if (deletePersonalInfo) {
            try {
                getJdbcTemplate().update(SQL_DELETE_USER_PERSONAL, new Object[] { username });
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException(
                        "Error executing SQL '" + SQL_DELETE_USER_PERSONAL + "' with parameter: " + username,
                        e);
            }
        } else {
            if (userHasPersonalInfo(username)) {
                if (firstName != null) {
                    try {
                        getJdbcTemplate().update(SQL_UPDATE_FIRST_NAME, firstName, username);
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_FIRST_NAME
                                + "' with parameters: " + firstName + ", " + username, e);
                    }
                }

                if (lastName != null) {
                    try {
                        getJdbcTemplate().update(SQL_UPDATE_LAST_NAME, lastName, username);
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_LAST_NAME
                                + "' with parameters: " + lastName + ", " + username, e);
                    }
                }

                if (organization != null) {
                    try {
                        getJdbcTemplate().update(SQL_UPDATE_ORGANIZATION, organization, username);
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_ORGANIZATION
                                + "' with parameters: " + organization + ", " + username, e);
                    }
                }

                if (personalId != null) {
                    try {
                        getJdbcTemplate().update(SQL_UPDATE_PERSONAL_ID, personalId, username);
                    } catch (org.springframework.dao.DataAccessException e) {
                        transactionManager.rollback(status);
                        throw new DataAccessException("Error executing SQL '" + SQL_UPDATE_PERSONAL_ID
                                + "' with parameters: " + personalId + ", " + username, e);
                    }
                }
            } else if ((firstName != null) && (lastName != null) && (organization != null)
                    && (personalId != null)) {

                try {
                    getJdbcTemplate().update(SQL_INSERT_USER_PERSONAL, username, firstName, lastName,
                            organization, personalId);
                } catch (org.springframework.dao.DataAccessException e) {
                    transactionManager.rollback(status);
                    throw new DataAccessException("Error executing SQL '" + SQL_INSERT_USER_PERSONAL
                            + "' with parameters: " + username + ", " + firstName + ", " + lastName + ", "
                            + organization + ", " + personalId, 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:org.ohmage.query.impl.CampaignQueries.java

public void deleteCampaign(String campaignId) throws DataAccessException {
    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Deleting a campaign.");

    try {/*  w ww .java2s. co m*/
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        try {
            getJdbcTemplate().update(SQL_DELETE_CAMPAIGN, campaignId);
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException(
                    "Error executing SQL '" + SQL_DELETE_CAMPAIGN + "' with parameter: " + campaignId, 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:org.ohmage.query.impl.UserQueries.java

/**
 * Updates a user's password.//from w w  w . j  a  va  2s.co  m
 * 
 * @param username The username of the user to be updated.
 * 
 * @param hashedPassword The new, hashed password for the user.
 */
public void updateUserPassword(String username, String hashedPassword, boolean setNewAccount)
        throws DataAccessException {
    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Updating a user's password.");

    try {
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        // Update the password.
        try {
            getJdbcTemplate().update(SQL_UPDATE_PASSWORD, hashedPassword, username);
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error executing the following SQL '" + SQL_UPDATE_PASSWORD
                    + "' with parameters: " + hashedPassword + ", " + username, e);
        }

        // Ensure that this user is no longer a new user.
        try {
            getJdbcTemplate().update(SQL_UPDATE_NEW_ACCOUNT, setNewAccount, username);
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error executing the following SQL '" + SQL_UPDATE_NEW_ACCOUNT
                    + "' with parameters: " + setNewAccount + ", " + username, 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:org.ohmage.query.impl.UserQueries.java

public void activateUser(final String registrationId) throws DataAccessException {

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Activating a user's account.");

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

        // Make the account not disabled.
        try {
            getJdbcTemplate().update(SQL_UPDATE_ENABLED_FROM_REGISTRATION_ID,
                    new Object[] { true, registrationId });
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException(
                    "Error executing the following SQL '" + SQL_UPDATE_ENABLED_FROM_REGISTRATION_ID
                            + "' with parameters: " + true + ", " + registrationId,
                    e);
        }

        // Update the accepted timestamp in the registration table.
        try {
            getJdbcTemplate().update(SQL_UPDATE_ACCEPTED_TIMESTAMP,
                    new Object[] { (new Date()).getTime(), registrationId });
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException("Error executing the following SQL '"
                    + SQL_UPDATE_ENABLED_FROM_REGISTRATION_ID + "' with parameter: " + registrationId, 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.AdvSearchDAO.java

public static ObjectNode searchMetrics(JsonNode searchOpt, int page, int size) {
    ObjectNode resultNode = Json.newObject();
    int count = 0;
    List<String> dashboardInList = new ArrayList<String>();
    List<String> dashboardNotInList = new ArrayList<String>();
    List<String> groupInList = new ArrayList<String>();
    List<String> groupNotInList = new ArrayList<String>();
    List<String> categoryInList = new ArrayList<String>();
    List<String> categoryNotInList = new ArrayList<String>();
    List<String> metricInList = new ArrayList<String>();
    List<String> metricNotInList = new ArrayList<String>();

    if (searchOpt != null && (searchOpt.isContainerNode())) {
        if (searchOpt.has("dashboard")) {
            JsonNode dashboardNode = searchOpt.get("dashboard");
            if (dashboardNode != null && dashboardNode.isContainerNode()) {
                if (dashboardNode.has("in")) {
                    JsonNode dashboardInNode = dashboardNode.get("in");
                    if (dashboardInNode != null) {
                        String dashboardInStr = dashboardInNode.asText();
                        if (StringUtils.isNotBlank(dashboardInStr)) {
                            String[] dashboardInArray = dashboardInStr.split(",");
                            if (dashboardInArray != null) {
                                for (String value : dashboardInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        dashboardInList.add(value.trim());
                                    }//  www .ja  v  a  2  s . c  o  m
                                }
                            }
                        }
                    }
                }
                if (dashboardNode.has("not")) {
                    JsonNode dashboardNotInNode = dashboardNode.get("not");
                    if (dashboardNotInNode != null) {
                        String dashboardNotInStr = dashboardNotInNode.asText();
                        if (StringUtils.isNotBlank(dashboardNotInStr)) {
                            String[] dashboardNotInArray = dashboardNotInStr.split(",");
                            if (dashboardNotInArray != null) {
                                for (String value : dashboardNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        dashboardNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (searchOpt.has("group")) {
            JsonNode groupNode = searchOpt.get("group");
            if (groupNode != null && groupNode.isContainerNode()) {
                if (groupNode.has("in")) {
                    JsonNode groupInNode = groupNode.get("in");
                    if (groupInNode != null) {
                        String groupInStr = groupInNode.asText();
                        if (StringUtils.isNotBlank(groupInStr)) {
                            String[] groupInArray = groupInStr.split(",");
                            if (groupInArray != null) {
                                for (String value : groupInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        groupInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (groupNode.has("not")) {
                    JsonNode groupNotInNode = groupNode.get("not");
                    if (groupNotInNode != null) {
                        String groupNotInStr = groupNotInNode.asText();
                        if (StringUtils.isNotBlank(groupNotInStr)) {
                            String[] groupNotInArray = groupNotInStr.split(",");
                            if (groupNotInArray != null) {
                                for (String value : groupNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        groupNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (searchOpt.has("cat")) {
            JsonNode categoryNode = searchOpt.get("cat");
            if (categoryNode != null && categoryNode.isContainerNode()) {
                if (categoryNode.has("in")) {
                    JsonNode categoryInNode = categoryNode.get("in");
                    if (categoryInNode != null) {
                        String categoryInStr = categoryInNode.asText();
                        if (StringUtils.isNotBlank(categoryInStr)) {
                            String[] categoryInArray = categoryInStr.split(",");
                            if (categoryInArray != null) {
                                for (String value : categoryInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        categoryInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (categoryNode.has("not")) {
                    JsonNode categoryNotInNode = categoryNode.get("not");
                    if (categoryNotInNode != null) {
                        String categoryNotInStr = categoryNotInNode.asText();
                        if (StringUtils.isNotBlank(categoryNotInStr)) {
                            String[] categoryNotInArray = categoryNotInStr.split(",");
                            if (categoryNotInArray != null) {
                                for (String value : categoryNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        categoryNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        if (searchOpt.has("metric")) {
            JsonNode metricNode = searchOpt.get("metric");
            if (metricNode != null && metricNode.isContainerNode()) {
                if (metricNode.has("in")) {
                    JsonNode metricInNode = metricNode.get("in");
                    if (metricInNode != null) {
                        String metricInStr = metricInNode.asText();
                        if (StringUtils.isNotBlank(metricInStr)) {
                            String[] metricInArray = metricInStr.split(",");
                            if (metricInArray != null) {
                                for (String value : metricInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        metricInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
                if (metricNode.has("not")) {
                    JsonNode metricNotInNode = metricNode.get("not");
                    if (metricNotInNode != null) {
                        String metricNotInStr = metricNotInNode.asText();
                        if (StringUtils.isNotBlank(metricNotInStr)) {
                            String[] metricNotInArray = metricNotInStr.split(",");
                            if (metricNotInArray != null) {
                                for (String value : metricNotInArray) {
                                    if (StringUtils.isNotBlank(value)) {
                                        metricNotInList.add(value.trim());
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }

        boolean needAndKeyword = false;

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

        TransactionTemplate txTemplate = new TransactionTemplate(tm);

        ObjectNode result;
        String query = ADV_SEARCH_METRIC;

        if (dashboardInList.size() > 0 || dashboardNotInList.size() > 0) {
            boolean dashboardNeedAndKeyword = false;
            if (dashboardInList.size() > 0) {
                int indexForDashboardInList = 0;
                for (String dashboard : dashboardInList) {
                    if (indexForDashboardInList == 0) {
                        query += "WHERE dashboard_name in ('" + dashboard + "'";
                    } else {
                        query += ", '" + dashboard + "'";
                    }
                    indexForDashboardInList++;
                }
                query += ") ";
                dashboardNeedAndKeyword = true;
            }
            if (dashboardNotInList.size() > 0) {
                if (dashboardNeedAndKeyword) {
                    query += " AND ";
                } else {
                    query += " WHERE ";
                }
                int indexForDashboardNotInList = 0;
                for (String dashboard : dashboardNotInList) {
                    if (indexForDashboardNotInList == 0) {
                        query += "dashboard_name not in ('" + dashboard + "'";
                    } else {
                        query += ", '" + dashboard + "'";
                    }
                    indexForDashboardNotInList++;
                }
                query += ") ";
            }
            needAndKeyword = true;
        }

        if (groupInList.size() > 0 || groupNotInList.size() > 0) {
            if (needAndKeyword) {
                query += " AND ";
            } else {
                query += " WHERE ";
            }
            query += "( ";
            boolean groupNeedAndKeyword = false;
            if (groupInList.size() > 0) {
                query += "( ";
                int indexForGroupInList = 0;
                for (String group : groupInList) {
                    if (indexForGroupInList == 0) {
                        query += "metric_group LIKE '%" + group + "%'";
                    } else {
                        query += " or metric_group LIKE '%" + group + "%'";
                    }
                    indexForGroupInList++;
                }
                query += ") ";
                groupNeedAndKeyword = true;
            }
            if (groupNotInList.size() > 0) {
                if (groupNeedAndKeyword) {
                    query += " AND ";
                }
                query += "( ";
                int indexForGroupNotInList = 0;
                for (String group : groupNotInList) {
                    if (indexForGroupNotInList == 0) {
                        query += "metric_group NOT LIKE '%" + group + "%'";
                    } else {
                        query += " and metric_group NOT LIKE '%" + group + "%'";
                    }
                    indexForGroupNotInList++;
                }
                query += ") ";
            }
            query += ") ";
            needAndKeyword = true;
        }

        if (categoryInList.size() > 0 || categoryNotInList.size() > 0) {
            if (needAndKeyword) {
                query += " AND ";
            } else {
                query += " WHERE ";
            }
            query += "( ";
            boolean categoryNeedAndKeyword = false;
            if (categoryInList.size() > 0) {
                int indexForCategoryInList = 0;
                query += "( ";
                for (String category : categoryInList) {
                    if (indexForCategoryInList == 0) {
                        query += "metric_category LIKE '%" + category + "%'";
                    } else {
                        query += " or metric_category LIKE '%" + category + "%'";
                    }
                    indexForCategoryInList++;
                }
                query += ") ";
                categoryNeedAndKeyword = true;
            }
            if (categoryNotInList.size() > 0) {
                if (categoryNeedAndKeyword) {
                    query += " AND ";
                }
                query += "( ";
                int indexForCategoryNotInList = 0;
                for (String category : categoryNotInList) {
                    if (indexForCategoryNotInList == 0) {
                        query += "metric_category NOT LIKE '%" + category + "%'";
                    } else {
                        query += " and metric_category NOT LIKE '%" + category + "%'";
                    }
                    indexForCategoryNotInList++;
                }
                query += ") ";
            }
            query += ") ";
            needAndKeyword = true;
        }

        if (metricInList.size() > 0 || metricNotInList.size() > 0) {
            if (needAndKeyword) {
                query += " AND ";
            } else {
                query += " WHERE ";
            }
            query += "( ";
            boolean metricNeedAndKeyword = false;
            if (metricInList.size() > 0) {
                int indexForMetricInList = 0;
                query += " ( ";
                for (String metric : metricInList) {
                    if (indexForMetricInList == 0) {
                        query += "metric_name LIKE '%" + metric + "%'";
                    } else {
                        query += " or metric_name LIKE '%" + metric + "%'";
                    }
                    indexForMetricInList++;
                }
                query += ") ";
                metricNeedAndKeyword = true;
            }
            if (metricNotInList.size() > 0) {
                if (metricNeedAndKeyword) {
                    query += " AND ";
                }
                query += "( ";
                int indexForMetricNotInList = 0;
                for (String metric : metricNotInList) {
                    if (indexForMetricNotInList == 0) {
                        query += "metric_name NOT LIKE '%" + metric + "%'";
                    } else {
                        query += " and metric_name NOT LIKE '%" + metric + "%'";
                    }
                    indexForMetricNotInList++;
                }
                query += ") ";
            }
            query += " )";
        }

        query += " LIMIT " + (page - 1) * size + ", " + size;
        final String queryString = query;

        result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
            public ObjectNode doInTransaction(TransactionStatus status) {
                List<Metric> pagedMetrics = jdbcTemplate.query(queryString, new MetricRowMapper());

                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("isMetrics", true);
                resultNode.put("itemsPerPage", size);
                resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
                resultNode.set("data", Json.toJson(pagedMetrics));

                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.UserQueries.java

public void deleteExpiredRegistration(final long duration) throws DataAccessException {

    String sql = "DELETE u, ur " + "FROM user u, user_registration ur " + "WHERE u.id = ur.user_id "
            + "AND accepted_timestamp IS null " + "AND request_timestamp < ?";

    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Activating a user's account.");

    try {//www .j  av  a  2s .co m
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        long earliestTime = (new Date()).getTime() - duration;

        try {
            getJdbcTemplate().update(sql, earliestTime);
        } catch (org.springframework.dao.DataAccessException e) {
            transactionManager.rollback(status);
            throw new DataAccessException(
                    "Error while executing SQL '" + sql + "' with parameter: " + earliestTime, 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:org.ohmage.query.impl.UserQueries.java

/**
 * Deletes all of the users in a Collection.
 * //from w  w w . ja  va2  s .co m
 * @param usernames A Collection of usernames for the users to delete.
 */
public void deleteUsers(Collection<String> usernames) throws DataAccessException {
    // Create the transaction.
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("Deleting a user.");

    try {
        // Begin the transaction.
        PlatformTransactionManager transactionManager = new DataSourceTransactionManager(getDataSource());
        TransactionStatus status = transactionManager.getTransaction(def);

        // Delete the users.
        for (String username : usernames) {
            try {
                getJdbcTemplate().update(SQL_DELETE_USER, username);
            } catch (org.springframework.dao.DataAccessException e) {
                transactionManager.rollback(status);
                throw new DataAccessException("Error executing the following SQL '" + SQL_DELETE_USER
                        + "' with parameters: " + username, 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:org.apache.ctakes.ytex.uima.DBCollectionReader.java

protected void initDB(String dbDriver, String dbURL) throws ResourceInitializationException {
    if (dbURL != null && dbURL.length() > 0) {
        try {//from  w ww .j a va2  s  .  c  o m

            if (dbDriver == null || dbDriver.length() == 0) {
                dbDriver = ApplicationContextHolder.getYtexProperties().getProperty("db.driver");
            }
            dataSource = new SimpleDriverDataSource((Driver) Class.forName(dbDriver).newInstance(), dbURL);
            txTemplate = new TransactionTemplate(new DataSourceTransactionManager(dataSource));
        } catch (InstantiationException e) {
            throw new ResourceInitializationException(e);
        } catch (IllegalAccessException e) {
            throw new ResourceInitializationException(e);
        } catch (ClassNotFoundException e) {
            throw new ResourceInitializationException(e);
        }
    } else {
        txTemplate = (TransactionTemplate) ApplicationContextHolder.getApplicationContext()
                .getBean("txTemplate");
        dataSource = (DataSource) ApplicationContextHolder.getApplicationContext()
                .getBean("collectionReaderDataSource");
    }
    simpleJdbcTemplate = new SimpleJdbcTemplate(dataSource);
    namedJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
}