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

Java tutorial

Introduction

Here is the source code for edu.harvard.i2b2.crc.dao.setfinder.QueryInstanceSpringDao.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.CallableStatement;
import java.sql.Connection;
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.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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.I2B2Exception;
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.QtQueryMaster;
import edu.harvard.i2b2.crc.datavo.db.QtQueryStatusType;
import edu.harvard.i2b2.crc.dao.DataSourceLookupDAOFactory;

/**
 * Class to handle persistance operation of Query instance i.e. each run of
 * query is called query instance $Id: QueryInstanceSpringDao.java,v 1.4
 * 2008/04/08 19:38:24 rk903 Exp $
 * 
 * @author rkuttan
 * @see QtQueryInstance
 */
public class QueryInstanceSpringDao extends CRCDAO implements IQueryInstanceDao {

    JdbcTemplate jdbcTemplate = null;
    SaveQueryInstance saveQueryInstance = null;
    QtQueryInstanceRowMapper queryInstanceMapper = null;
    private DataSourceLookup dataSourceLookup = null;

    /** log **/
    protected final Log log = LogFactory.getLog(getClass());

    public QueryInstanceSpringDao(DataSource dataSource, DataSourceLookup dataSourceLookup) {
        setDataSource(dataSource);
        setDbSchemaName(dataSourceLookup.getFullSchema());
        jdbcTemplate = new JdbcTemplate(dataSource);
        this.dataSourceLookup = dataSourceLookup;
        queryInstanceMapper = new QtQueryInstanceRowMapper();
    }

    /**
     * Function to create query instance
     * 
     * @param queryMasterId
     * @param userId
     * @param groupId
     * @param batchMode
     * @param statusId
     * @return query instance id
     */
    public String createQueryInstance(String queryMasterId, String userId, String groupId, String batchMode,
            int statusId) {
        QtQueryInstance queryInstance = new QtQueryInstance();
        queryInstance.setUserId(userId);
        queryInstance.setGroupId(groupId);
        queryInstance.setBatchMode(batchMode);
        queryInstance.setDeleteFlag("N");

        QtQueryMaster queryMaster = new QtQueryMaster();
        queryMaster.setQueryMasterId(queryMasterId);
        queryInstance.setQtQueryMaster(queryMaster);

        QtQueryStatusType statusType = new QtQueryStatusType();
        statusType.setStatusTypeId(statusId);
        queryInstance.setQtQueryStatusType(statusType);

        Date startDate = new Date(System.currentTimeMillis());
        queryInstance.setStartDate(startDate);
        saveQueryInstance = new SaveQueryInstance(getDataSource(), getDbSchemaName(), dataSourceLookup);
        saveQueryInstance.save(queryInstance);

        return queryInstance.getQueryInstanceId();
    }

    /**
     * Returns list of query instance for the given master id
     * 
     * @param queryMasterId
     * @return List<QtQueryInstance>
     */
    @SuppressWarnings("unchecked")
    public List<QtQueryInstance> getQueryInstanceByMasterId(String queryMasterId) {
        String sql = "select *  from " + getDbSchemaName() + "qt_query_instance where query_master_id = ?";
        List<QtQueryInstance> queryInstanceList = jdbcTemplate.query(sql,
                new Object[] { Integer.parseInt(queryMasterId) }, queryInstanceMapper);
        return queryInstanceList;
    }

    /**
     * Find query instance by id
     * 
     * @param queryInstanceId
     * @return QtQueryInstance
     */
    public QtQueryInstance getQueryInstanceByInstanceId(String queryInstanceId) {
        String sql = "select *  from " + getDbSchemaName() + "qt_query_instance  where query_instance_id =?";

        QtQueryInstance queryInstance = (QtQueryInstance) jdbcTemplate.queryForObject(sql,
                new Object[] { Integer.parseInt(queryInstanceId) }, queryInstanceMapper);

        return queryInstance;
    }

    /**
     * Update query instance
     * 
     * @param queryInstance
     * @return QtQueryInstance
     * @throws I2B2DAOException 
     */
    public QtQueryInstance update(QtQueryInstance queryInstance, boolean appendMessageFlag)
            throws I2B2DAOException {

        Integer statusTypeId = (queryInstance.getQtQueryStatusType() != null)
                ? queryInstance.getQtQueryStatusType().getStatusTypeId()
                : null;
        String messageUpdate = "";
        if (appendMessageFlag) {
            String concatOperator = "";
            if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
                concatOperator = "||";
                messageUpdate = " MESSAGE = nvl(MESSAGE,'') " + concatOperator + " ? ";
            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)
                    || dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
                //concatOperator = "+";
                // messageUpdate = " MESSAGE = isnull(Cast(MESSAGE as nvarchar(4000)),'') "
                //      + concatOperator + " ? ";
                // Cast(notes as nvarchar(4000))

                //update message field
                updateMessage(queryInstance.getQueryInstanceId(), queryInstance.getMessage(), true);

                if (queryInstance.getEndDate() != null) {
                    //update rest of the fields
                    String sql = "UPDATE " + getDbSchemaName()
                            + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,END_DATE = ? ,STATUS_TYPE_ID = ? "
                            + " where query_instance_id = ? ";

                    jdbcTemplate.update(sql,
                            new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(),
                                    queryInstance.getBatchMode(), queryInstance.getEndDate(), statusTypeId,
                                    Integer.parseInt(queryInstance.getQueryInstanceId()) });
                } else {
                    //update rest of the fields
                    String sql = "UPDATE " + getDbSchemaName()
                            + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,STATUS_TYPE_ID = ? "
                            + " where query_instance_id = ? ";

                    jdbcTemplate.update(sql,
                            new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(),
                                    queryInstance.getBatchMode(), statusTypeId,
                                    Integer.parseInt(queryInstance.getQueryInstanceId()) });
                }
                return queryInstance;
            }

        } else {
            messageUpdate = " MESSAGE = ?";
        }

        if (queryInstance.getEndDate() != null) {
            String sql = "UPDATE " + getDbSchemaName()
                    + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,END_DATE = ? ,STATUS_TYPE_ID = ?, "
                    + messageUpdate + " where query_instance_id = ? ";

            jdbcTemplate.update(sql,
                    new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(),
                            queryInstance.getBatchMode(), queryInstance.getEndDate(), statusTypeId,
                            (queryInstance.getMessage() == null) ? "" : queryInstance.getMessage(),
                            queryInstance.getQueryInstanceId() });
        } else {
            String sql = "UPDATE " + getDbSchemaName()
                    + "QT_QUERY_INSTANCE set USER_ID = ?, GROUP_ID = ?,BATCH_MODE = ?,STATUS_TYPE_ID = ?, "
                    + messageUpdate + " where query_instance_id = ? ";

            jdbcTemplate.update(sql,
                    new Object[] { queryInstance.getUserId(), queryInstance.getGroupId(),
                            queryInstance.getBatchMode(), statusTypeId,
                            (queryInstance.getMessage() == null) ? "" : queryInstance.getMessage(),
                            Integer.parseInt(queryInstance.getQueryInstanceId()) });
        }
        return queryInstance;
    }

    /**
     * Update query instance message
     * 
     * @param queryInstanceId
     * @param message
     * @param appendMessageFlag
     * @return 
     */
    public void updateMessage(String queryInstanceId, String message, boolean appendMessageFlag)
            throws I2B2DAOException {

        String messageUpdate = "";
        if (appendMessageFlag) {
            String concatOperator = "";
            if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
                concatOperator = "||";
                messageUpdate = " MESSAGE = nvl(MESSAGE,'') " + concatOperator + " ? ";
            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
                concatOperator = "||";
                messageUpdate = " MESSAGE = ? ";

            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
                // Cast(notes as nvarchar(4000))
                //messageUpdate = " message.write (?, NULL, 0) ";

                Connection conn = null;
                try {
                    conn = getDataSource().getConnection();
                    CallableStatement callStmt = conn
                            .prepareCall("{call " + getDbSchemaName() + "UPDATE_QUERYINSTANCE_MESSAGE(?,?,?)}");
                    callStmt.setString(1, message);
                    callStmt.setString(2, queryInstanceId);
                    callStmt.registerOutParameter(3, java.sql.Types.VARCHAR);
                    // callStmt.setString(2, tempPatientMappingTableName);
                    callStmt.execute();
                    this.getSQLServerProcedureError(dataSourceLookup.getServerType(), callStmt, 3);
                } catch (SQLException sqlEx) {
                    sqlEx.printStackTrace();
                    throw new I2B2DAOException("SQLException occured" + sqlEx.getMessage(), sqlEx);
                } catch (Exception ex) {
                    ex.printStackTrace();
                    throw new I2B2DAOException("Exception occured" + ex.getMessage(), ex);
                } finally {
                    if (conn != null) {
                        try {
                            conn.close();
                        } catch (SQLException sqlEx) {
                            sqlEx.printStackTrace();
                            log.error("Error while closing connection", sqlEx);
                        }
                    }
                }
                return;
                //////

            }

        } else {
            messageUpdate = " MESSAGE = ?";
        }
        String sql = "UPDATE " + getDbSchemaName() + "QT_QUERY_INSTANCE set " + messageUpdate
                + " where query_instance_id = ? ";
        jdbcTemplate.update(sql, new Object[] {

                (message == null) ? "" : message, Integer.parseInt(queryInstanceId) });

    }

    private static class SaveQueryInstance extends SqlUpdate {

        private String INSERT_ORACLE = "";
        private String INSERT_SQLSERVER = "";
        private String SEQUENCE_ORACLE = "";
        private String SEQUENCE_POSTGRESQL = "";
        private String INSERT_POSTGRESQL = "";

        private DataSourceLookup dataSourceLookup = null;

        public SaveQueryInstance(DataSource dataSource, String dbSchemaName, DataSourceLookup dataSourceLookup) {
            super();
            this.dataSourceLookup = dataSourceLookup;
            // sqlServerSequenceDao = new
            // SQLServerSequenceDAO(dataSource,dataSourceLookup) ;
            setDataSource(dataSource);
            if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
                INSERT_ORACLE = "INSERT INTO " + dbSchemaName + "QT_QUERY_INSTANCE "
                        + "(QUERY_INSTANCE_ID, QUERY_MASTER_ID, USER_ID, GROUP_ID,BATCH_MODE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) "
                        + "VALUES (?,?,?,?,?,?,?,?,?)";
                setSql(INSERT_ORACLE);
                SEQUENCE_ORACLE = "select " + dbSchemaName + "QT_SQ_QI_QIID.nextval from dual";
                declareParameter(new SqlParameter(Types.INTEGER));

            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
                INSERT_SQLSERVER = "INSERT INTO " + dbSchemaName + "QT_QUERY_INSTANCE "
                        + "( QUERY_MASTER_ID, USER_ID, GROUP_ID,BATCH_MODE,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_INSTANCE "
                        + "(QUERY_INSTANCE_ID, QUERY_MASTER_ID, USER_ID, GROUP_ID,BATCH_MODE,START_DATE,END_DATE,STATUS_TYPE_ID,DELETE_FLAG) "
                        + "VALUES (?,?,?,?,?,?,?,?,?)";
                setSql(INSERT_POSTGRESQL);
                SEQUENCE_POSTGRESQL = "select " // + dbSchemaName
                        + "nextval('qt_query_instance_query_instance_id_seq') ";
                declareParameter(new SqlParameter(Types.INTEGER));

            }

            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.VARCHAR));
            declareParameter(new SqlParameter(Types.VARCHAR));
            declareParameter(new SqlParameter(Types.VARCHAR));
            declareParameter(new SqlParameter(Types.TIMESTAMP));
            declareParameter(new SqlParameter(Types.TIMESTAMP));
            declareParameter(new SqlParameter(Types.INTEGER));
            declareParameter(new SqlParameter(Types.VARCHAR));
            compile();
        }

        public void save(QtQueryInstance queryInstance) {
            JdbcTemplate jdbc = getJdbcTemplate();
            int queryInstanceId = 0;
            Object[] object = null;
            if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
                object = new Object[] {

                        queryInstance.getQtQueryMaster().getQueryMasterId(), queryInstance.getUserId(),
                        queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getStartDate(),
                        queryInstance.getEndDate(), queryInstance.getQtQueryStatusType().getStatusTypeId(),
                        queryInstance.getDeleteFlag() };

            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
                queryInstanceId = jdbc.queryForInt(SEQUENCE_ORACLE);
                queryInstance.setQueryInstanceId(String.valueOf(queryInstanceId));
                object = new Object[] { queryInstance.getQueryInstanceId(),
                        queryInstance.getQtQueryMaster().getQueryMasterId(), queryInstance.getUserId(),
                        queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getStartDate(),
                        queryInstance.getEndDate(), queryInstance.getQtQueryStatusType().getStatusTypeId(),
                        queryInstance.getDeleteFlag() };
            } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
                queryInstanceId = jdbc.queryForInt(SEQUENCE_POSTGRESQL);
                queryInstance.setQueryInstanceId(String.valueOf(queryInstanceId));
                object = new Object[] { queryInstance.getQueryInstanceId(),
                        queryInstance.getQtQueryMaster().getQueryMasterId(), queryInstance.getUserId(),
                        queryInstance.getGroupId(), queryInstance.getBatchMode(), queryInstance.getStartDate(),
                        queryInstance.getEndDate(), queryInstance.getQtQueryStatusType().getStatusTypeId(),
                        queryInstance.getDeleteFlag() };
            }

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

                queryInstance.setQueryInstanceId(String.valueOf(queryInstanceIdentityId));

            }
        }
    }

    private class QtQueryInstanceRowMapper implements RowMapper {
        QueryStatusTypeSpringDao statusTypeDao = new QueryStatusTypeSpringDao(dataSource, dataSourceLookup);

        public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            QtQueryInstance queryInstance = new QtQueryInstance();
            queryInstance.setQueryInstanceId(rs.getString("QUERY_INSTANCE_ID"));
            QtQueryMaster queryMaster = new QtQueryMaster();
            queryMaster.setQueryMasterId(rs.getString("QUERY_MASTER_ID"));
            queryInstance.setQtQueryMaster(queryMaster);
            queryInstance.setUserId(rs.getString("USER_ID"));
            queryInstance.setGroupId(rs.getString("GROUP_ID"));
            queryInstance.setBatchMode(rs.getString("BATCH_MODE"));
            queryInstance.setStartDate(rs.getTimestamp("START_DATE"));
            queryInstance.setEndDate(rs.getTimestamp("END_DATE"));
            queryInstance.setMessage(rs.getString("MESSAGE"));
            int statusTypeId = rs.getInt("STATUS_TYPE_ID");
            queryInstance.setQtQueryStatusType(statusTypeDao.getQueryStatusTypeById(statusTypeId));
            queryInstance.setDeleteFlag(rs.getString("DELETE_FLAG"));
            return queryInstance;
        }
    }

    private void getSQLServerProcedureError(String serverType, CallableStatement callStmt, int outParamIndex)
            throws SQLException, I2B2Exception {

        if (serverType.equalsIgnoreCase(DataSourceLookupDAOFactory.SQLSERVER)) {
            String errorMsg = callStmt.getString(outParamIndex);
            if (errorMsg != null) {
                log.debug("error codde" + errorMsg);
                throw new I2B2Exception("Error from stored procedure [" + errorMsg + "]");
            }
        }
    }

}