edu.harvard.i2b2.crc.dao.setfinder.QueryResultInstanceSpringDao.java Source code

Java tutorial

Introduction

Here is the source code for edu.harvard.i2b2.crc.dao.setfinder.QueryResultInstanceSpringDao.java

Source

/*
 * Copyright (c) 2006-2007 Massachusetts General Hospital 
 * All rights reserved. This program and the accompanying materials 
 * are made available under the terms of the i2b2 Software License v1.0 
 * which accompanies this distribution. 
 * 
 * Contributors: 
 *     Rajesh Kuttan
 */
package edu.harvard.i2b2.crc.dao.setfinder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.SqlUpdate;

import edu.harvard.i2b2.common.exception.I2B2DAOException;
import edu.harvard.i2b2.common.exception.StackTraceUtil;
import edu.harvard.i2b2.common.util.db.JDBCUtil;
import edu.harvard.i2b2.crc.dao.CRCDAO;
import edu.harvard.i2b2.crc.dao.DAOFactoryHelper;
import edu.harvard.i2b2.crc.datavo.db.DataSourceLookup;
import edu.harvard.i2b2.crc.datavo.db.QtQueryInstance;
import edu.harvard.i2b2.crc.datavo.db.QtQueryResultInstance;
import edu.harvard.i2b2.crc.datavo.db.QtQueryResultType;
import edu.harvard.i2b2.crc.datavo.db.QtQueryStatusType;
import edu.harvard.i2b2.crc.datavo.db.StatusEnum;

/**
 * This is class handles persistance of result instance and its update operation
 * $Id: QueryResultInstanceSpringDao.java,v 1.14 2010/07/22 18:54:51 rk903 Exp $
 * 
 * @author rkuttan
 */
public class QueryResultInstanceSpringDao extends CRCDAO implements IQueryResultInstanceDao {

    JdbcTemplate jdbcTemplate = null;
    SavePatientSetResult savePatientSetResult = null;
    PatientSetResultRowMapper patientSetMapper = null;
    DataSourceLookup dataSourceLookup = null;

    public QueryResultInstanceSpringDao(DataSource dataSource, DataSourceLookup dataSourceLookup) {
        setDataSource(dataSource);
        setDbSchemaName(dataSourceLookup.getFullSchema());
        jdbcTemplate = new JdbcTemplate(dataSource);
        this.dataSourceLookup = dataSourceLookup;

        patientSetMapper = new PatientSetResultRowMapper();
    }

    /**
     * Function to create result instance for given query instance id. The
     * result instance status is set to running. Use updatePatientSet function
     * to change the status to completed or error
     * 
     * @param queryInstanceId
     * @return
     */
    public String createPatientSet(String queryInstanceId, String resultName) throws I2B2DAOException {
        QtQueryResultInstance resultInstance = new QtQueryResultInstance();
        resultInstance.setDeleteFlag("N");

        QueryResultTypeSpringDao resultTypeDao = new QueryResultTypeSpringDao(dataSource, dataSourceLookup);
        List<QtQueryResultType> resultType = resultTypeDao.getQueryResultTypeByName(resultName);
        if (resultType.size() < 1) {
            throw new I2B2DAOException(" Result type  [" + resultName + "] not found");
        }
        resultInstance.setQtQueryResultType(resultType.get(0));
        resultInstance.setDescription(resultType.get(0).getDescription());
        QtQueryInstance queryInstance = new QtQueryInstance();
        queryInstance.setQueryInstanceId(queryInstanceId);
        resultInstance.setQtQueryInstance(queryInstance);

        QtQueryStatusType queryStatusType = new QtQueryStatusType();
        queryStatusType.setStatusTypeId(StatusEnum.QUEUED.ordinal());
        resultInstance.setQtQueryStatusType(queryStatusType);

        Date startDate = new Date(System.currentTimeMillis());
        resultInstance.setStartDate(startDate);
        savePatientSetResult = new SavePatientSetResult(getDataSource(), getDbSchemaName(), dataSourceLookup);
        savePatientSetResult.save(resultInstance);

        return resultInstance.getResultInstanceId();
    }

    /**
     * Function used to update result instance Particularly its status and size
     * 
     * @param resultInstanceId
     * @param statusTypeId
     * @param setSize
     */
    public void updatePatientSet(String resultInstanceId, int statusTypeId, int setSize) {
        updatePatientSet(resultInstanceId, statusTypeId, "", setSize, 0, "");
    }

    /**
     * Function used to update result instance Particularly its status and size
     * 
     * @param resultInstanceId
     * @param statusTypeId
     * @param setSize
     */
    public void updatePatientSet(String resultInstanceId, int statusTypeId, String message, int setSize,
            int realSetSize, String obsMethod) {

        Date endDate = new Date(System.currentTimeMillis());
        String sql = "update " + getDbSchemaName()
                + "qt_query_result_instance set set_size = ?, real_set_size = ? , obfusc_method = ?, status_type_id =?, end_date = ?, message = ?  where result_instance_id = ?";
        jdbcTemplate.update(sql,
                new Object[] { setSize, realSetSize, obsMethod, statusTypeId, endDate, message, resultInstanceId },
                new int[] { Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.INTEGER, Types.TIMESTAMP,
                        Types.VARCHAR, Types.INTEGER });
    }

    /**
     * Function used to update result instance description
     * 
     * @param resultInstanceId
     * @param description
     */
    public void updateResultInstanceDescription(String resultInstanceId, String description) {
        String sql = "update " + getDbSchemaName()
                + "qt_query_result_instance set description = ?  where result_instance_id = ?";
        jdbcTemplate.update(sql, new Object[] { description, Integer.parseInt(resultInstanceId) },
                new int[] { Types.VARCHAR, Types.INTEGER });
    }

    /**
     * Return list of query result instance by query instance id
     * 
     * @param queryInstanceId
     * @return List<QtQueryResultInstance>
     */
    @SuppressWarnings("unchecked")
    public List<QtQueryResultInstance> getResultInstanceList(String queryInstanceId) {
        String sql = "select *  from " + getDbSchemaName()
                + "qt_query_result_instance where query_instance_id = ? ";
        List<QtQueryResultInstance> queryResultInstanceList = jdbcTemplate.query(sql,
                new Object[] { Integer.parseInt(queryInstanceId) }, patientSetMapper);
        return queryResultInstanceList;
    }

    /**
     * Return list of query result instance by query result id
     * 
     * @param queryResultId
     * @return QtQueryResultInstance
     */
    @SuppressWarnings("unchecked")
    public QtQueryResultInstance getResultInstanceById(String queryResultId) throws I2B2DAOException {
        String sql = "select *  from " + getDbSchemaName()
                + "qt_query_result_instance where result_instance_id = ? ";
        List<QtQueryResultInstance> queryResultInstanceList = jdbcTemplate.query(sql,
                new Object[] { Integer.parseInt(queryResultId) }, patientSetMapper);
        if (queryResultInstanceList.size() > 0) {
            return queryResultInstanceList.get(0);
        } else {
            throw new I2B2DAOException("Query result id " + queryResultId + " not found");
        }

    }

    /**
     * Return list of query result instance by query instance id and result name
     * 
     * @param queryInstanceId
     * @param resultName
     * @return QtQueryResultInstance
     */
    @SuppressWarnings("unchecked")
    public QtQueryResultInstance getResultInstanceByQueryInstanceIdAndName(String queryInstanceId,
            String resultName) {
        String sql = "select *  from " + getDbSchemaName() + "qt_query_result_instance ri, " + getDbSchemaName()
                + "qt_query_result_type rt where ri.query_instance_id = ? and ri.result_type_id = rt.result_type_id and rt.name=?";
        QtQueryResultInstance queryResultInstanceList = (QtQueryResultInstance) jdbcTemplate.queryForObject(sql,
                new Object[] { Integer.parseInt(queryInstanceId), resultName }, patientSetMapper);
        return queryResultInstanceList;
    }

    /**
     * Return a list of query result instance with waiting status
     * 
     * @param queueName
     * @param maxListSize
     * @return
     */
    public List<QtQueryResultInstance> getUnfinishedInstanceByQueue(String queueName, int maxListSize) {
        List<QtQueryResultInstance> resultInstanceList = null;
        int waitStatus = 1;
        String sql = "select * from " + getDbSchemaName() + "qt_query_result_instance ri, " + getDbSchemaName()
                + "qt_query_result_type rt where status_type_id = ? and queue_name = ? and ri.result_type_id = rt.result_type_id order by start_date";
        resultInstanceList = jdbcTemplate.query(sql, new Object[] { waitStatus, queueName }, patientSetMapper);
        return resultInstanceList;
    }

    /**
     * Get result instance count by set size
     * 
     * @param userId
     * @param compareDays
     * @param setSize
     * @param totalCount
     * @return
     * @throws I2B2DAOException
     */
    public int getResultInstanceCountBySetSize(String userId, int compareDays, int resultTypeId, int setSize,
            int totalCount) throws I2B2DAOException {
        // int betweenDayValue = compareDays / 2;
        int startBetweenDayValue = compareDays * -1;
        int returnSetSize = 0;
        String queryCountSql = "";

        if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
            queryCountSql = " select count(r1.result_instance_id) result_count,r1.real_set_size " + " from "
                    + this.getDbSchemaName() + "qt_query_result_instance r1 inner join " + this.getDbSchemaName()
                    + "qt_query_result_instance r2 on " + " r1.real_set_size = r2.real_set_size, "
                    + this.getDbSchemaName() + "qt_query_instance qi " + " where "
                    + "  r1.start_date between sysdate- " + compareDays + " and sysdate   "
                    + " and r2.start_date between sysdate- " + compareDays + "  and sysdate "
                    + " and r1.result_type_id = ?" + " and r2.result_type_id = ? " + " and  qi.user_id = ? "
                    + " and qi.query_instance_id = r1.query_instance_id "
                    + " and qi.query_instance_id = r2.query_instance_id " + " and r1.real_set_size = ? "
                    + " group by r1.real_set_size " + " having count(r1.result_instance_id) > ? ";
        } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
            queryCountSql = " select count(r1.result_instance_id) result_count,r1.real_set_size " + " from "
                    + this.getDbSchemaName() + "qt_query_result_instance r1 inner join " + this.getDbSchemaName()
                    + "qt_query_result_instance r2 on " + " r1.real_set_size = r2.real_set_size, "
                    + this.getDbSchemaName() + "qt_query_instance qi " + " where "
                    + "  r1.start_date between LOCALTIMESTAMP - INTERVAL '" + compareDays
                    + " days' and LOCALTIMESTAMP " + " and r2.start_date between LOCALTIMESTAMP - INTERVAL '"
                    + compareDays + " days' and LOCALTIMESTAMP " + " and r1.result_type_id = ?"
                    + " and r2.result_type_id = ? " + " and  qi.user_id = ? "
                    + " and qi.query_instance_id = r1.query_instance_id "
                    + " and qi.query_instance_id = r2.query_instance_id " + " and r1.real_set_size = ? "
                    + " group by r1.real_set_size " + " having count(r1.result_instance_id) > ? ";
        } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
            queryCountSql = " select count(r1.result_instance_id) result_count,r1.real_set_size " + " from "
                    + this.getDbSchemaName() + "qt_query_result_instance r1 inner join " + this.getDbSchemaName()
                    + "qt_query_result_instance r2 on " + " r1.real_set_size = r2.real_set_size, "
                    + this.getDbSchemaName() + "qt_query_instance qi " + " where "
                    + " r1.start_date between DATEADD ( day , " + startBetweenDayValue
                    + ", getDate())  and DATEADD ( day , " + "1" + ", getDate()) "
                    + " and r2.start_date between DATEADD ( day , " + startBetweenDayValue
                    + ", getDate()) and DATEADD ( day , " + "1" + ", getDate()) " + " and r1.result_type_id = ? "
                    + " and r2.result_type_id = ? " + " and  qi.user_id = ? "
                    + " and qi.query_instance_id = r1.query_instance_id "
                    + " and qi.query_instance_id = r2.query_instance_id " + " and r1.real_set_size = ? "
                    + " group by r1.real_set_size " + " having count(r1.result_instance_id) > ? ";
        }

        Connection conn = null;
        PreparedStatement preparedStmt = null;
        try {
            conn = dataSource.getConnection();

            log.debug("Executing sql [" + queryCountSql + "]");
            preparedStmt = conn.prepareStatement(queryCountSql);
            preparedStmt.setInt(1, resultTypeId);
            preparedStmt.setInt(2, resultTypeId);
            preparedStmt.setString(3, userId);
            preparedStmt.setInt(4, setSize);
            preparedStmt.setInt(5, totalCount);

            ResultSet resultSet = preparedStmt.executeQuery();
            if (resultSet.next()) {
                returnSetSize = resultSet.getInt("result_count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new I2B2DAOException(
                    "Error while calculating query count by set size" + StackTraceUtil.getStackTrace(e));
        } finally {
            try {
                JDBCUtil.closeJdbcResource(null, preparedStmt, conn);
            } catch (SQLException e) {

                e.printStackTrace();
            }

        }
        return returnSetSize;

    }

    private static class SavePatientSetResult extends SqlUpdate {

        private String INSERT_ORACLE = "";
        private String INSERT_SQLSERVER = "";
        private String SEQUENCE_ORACLE = "";
        private String SEQUENCE_POSTGRESQL = "";
        private String INSERT_POSTGRESQL = "";
        DataSourceLookup dataSourceLookup = null;

        public SavePatientSetResult(DataSource dataSource, String dbSchemaName, DataSourceLookup dataSourceLookup) {
            super();
            setDataSource(dataSource);
            if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
                INSERT_ORACLE = "INSERT INTO " + dbSchemaName + "QT_QUERY_RESULT_INSTANCE "
                        + "(RESULT_INSTANCE_ID, QUERY_INSTANCE_ID, RESULT_TYPE_ID, SET_SIZE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) "
                        + "VALUES (?,?,?,?,?,?,?,?)";
                setSql(INSERT_ORACLE);
                SEQUENCE_ORACLE = "select " + dbSchemaName + "QT_SQ_QRI_QRIID.nextval from dual";
                declareParameter(new SqlParameter(Types.INTEGER));

            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
                INSERT_SQLSERVER = "INSERT INTO " + dbSchemaName + "QT_QUERY_RESULT_INSTANCE "
                        + "( QUERY_INSTANCE_ID, RESULT_TYPE_ID, SET_SIZE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) "
                        + "VALUES (?,?,?,?,?,?,?)";
                setSql(INSERT_SQLSERVER);
            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
                INSERT_POSTGRESQL = "INSERT INTO " + dbSchemaName + "QT_QUERY_RESULT_INSTANCE "
                        + "(RESULT_INSTANCE_ID, QUERY_INSTANCE_ID, RESULT_TYPE_ID, SET_SIZE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) "
                        + "VALUES (?,?,?,?,?,?,?,?)";
                setSql(INSERT_POSTGRESQL);
                SEQUENCE_POSTGRESQL = "select " //+ dbSchemaName
                        + "nextval('qt_query_result_instance_result_instance_id_seq') ";
                declareParameter(new SqlParameter(Types.INTEGER));

            }

            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.TIMESTAMP));
            declareParameter(new SqlParameter(Types.TIMESTAMP));
            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.VARCHAR));
            this.dataSourceLookup = dataSourceLookup;

            compile();
        }

        public void save(QtQueryResultInstance resultInstance) {
            JdbcTemplate jdbc = getJdbcTemplate();
            int resultInstanceId = 0;
            Object[] object = null;
            if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {

                object = new Object[] { resultInstance.getQtQueryInstance().getQueryInstanceId(),

                        resultInstance.getQtQueryResultType().getResultTypeId(), resultInstance.getSetSize(),
                        resultInstance.getStartDate(), resultInstance.getEndDate(),
                        resultInstance.getQtQueryStatusType().getStatusTypeId(), resultInstance.getDeleteFlag()

                };
            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
                resultInstanceId = jdbc.queryForInt(SEQUENCE_ORACLE);
                resultInstance.setResultInstanceId(String.valueOf(resultInstanceId));
                object = new Object[] { resultInstance.getResultInstanceId(),
                        resultInstance.getQtQueryInstance().getQueryInstanceId(),
                        resultInstance.getQtQueryResultType().getResultTypeId(), resultInstance.getSetSize(),
                        resultInstance.getStartDate(), resultInstance.getEndDate(),
                        resultInstance.getQtQueryStatusType().getStatusTypeId(), resultInstance.getDeleteFlag()

                };
            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
                resultInstanceId = jdbc.queryForInt(SEQUENCE_POSTGRESQL);
                resultInstance.setResultInstanceId(String.valueOf(resultInstanceId));
                object = new Object[] { resultInstance.getResultInstanceId(),
                        resultInstance.getQtQueryInstance().getQueryInstanceId(),
                        resultInstance.getQtQueryResultType().getResultTypeId(), resultInstance.getSetSize(),
                        resultInstance.getStartDate(), resultInstance.getEndDate(),
                        resultInstance.getQtQueryStatusType().getStatusTypeId(), resultInstance.getDeleteFlag()

                };
            }

            update(object);
            if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
                int resultInstanceIdentityId = jdbc.queryForInt("SELECT @@IDENTITY");

                resultInstance.setResultInstanceId(String.valueOf(resultInstanceIdentityId));

            }

        }
    }

    private class PatientSetResultRowMapper implements RowMapper {
        QueryStatusTypeSpringDao statusTypeDao = new QueryStatusTypeSpringDao(dataSource, dataSourceLookup);
        QueryResultTypeSpringDao resultTypeDao = new QueryResultTypeSpringDao(dataSource, dataSourceLookup);

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            QtQueryResultInstance resultInstance = new QtQueryResultInstance();
            resultInstance.setResultInstanceId(rs.getString("RESULT_INSTANCE_ID"));

            QtQueryInstance queryInstance = new QtQueryInstance();
            queryInstance.setQueryInstanceId(rs.getString("QUERY_INSTANCE_ID"));
            resultInstance.setQtQueryInstance(queryInstance);
            resultInstance.setDescription(rs.getString("DESCRIPTION"));

            int resultTypeId = rs.getInt("RESULT_TYPE_ID");
            resultInstance.setQtQueryResultType(resultTypeDao.getQueryResultTypeById(resultTypeId));
            resultInstance.setSetSize(rs.getInt("SET_SIZE"));
            resultInstance.setRealSetSize(rs.getInt("REAL_SET_SIZE"));
            resultInstance.setObfuscateMethod(rs.getString("OBFUSC_METHOD"));
            resultInstance.setStartDate(rs.getTimestamp("START_DATE"));
            resultInstance.setEndDate(rs.getTimestamp("END_DATE"));
            resultInstance.setMessage(rs.getString("MESSAGE"));
            // QtQueryStatusType queryStatusType = new QtQueryStatusType();
            int statusTypeId = rs.getInt("STATUS_TYPE_ID");
            resultInstance.setQtQueryStatusType(statusTypeDao.getQueryStatusTypeById(statusTypeId));
            // resultInstance.setQtQueryStatusType(queryStatusType);
            resultInstance.setDeleteFlag(rs.getString("DELETE_FLAG"));
            return resultInstance;
        }
    }
}