org.generationcp.middleware.dao.gdms.QtlDetailsDAO.java Source code

Java tutorial

Introduction

Here is the source code for org.generationcp.middleware.dao.gdms.QtlDetailsDAO.java

Source

/*******************************************************************************
 * Copyright (c) 2012, All Rights Reserved.
 *
 * Generation Challenge Programme (GCP)
 *
 *
 * This software is licensed for use under the terms of the GNU General Public License (http://bit.ly/8Ztv8M) and the provisions of Part F
 * of the Generation Challenge Programme Amended Consortium Agreement (http://bit.ly/KQX1nL)
 *
 *******************************************************************************/

package org.generationcp.middleware.dao.gdms;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.generationcp.middleware.dao.GenericDAO;
import org.generationcp.middleware.exceptions.MiddlewareQueryException;
import org.generationcp.middleware.pojos.gdms.QtlDataElement;
import org.generationcp.middleware.pojos.gdms.QtlDetails;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;

/**
 * DAO class for {@link QtlDetails}.
 *
 * @author Joyce Avestro
 *
 */
@SuppressWarnings("unchecked")
public class QtlDetailsDAO extends GenericDAO<QtlDetails, Integer> {

    public static final String GET_MARKER_IDS_BY_QTL = "SELECT marker_id "
            + "FROM gdms_markers_onmap, gdms_qtl_details, gdms_qtl "
            + "WHERE gdms_markers_onmap.map_id = gdms_qtl_details.map_id "
            + "        AND gdms_qtl_details.qtl_id = gdms_qtl.qtl_id " + "        AND gdms_qtl.qtl_name = :qtlName "
            + "        AND gdms_markers_onmap.linkage_group = :chromosome "
            + "        AND gdms_markers_onmap.start_position between :min AND :max ";

    public static final String COUNT_MARKER_IDS_BY_QTL = "SELECT COUNT(marker_id)  "
            + "FROM gdms_markers_onmap, gdms_qtl_details, gdms_qtl "
            + "WHERE gdms_markers_onmap.map_id = gdms_qtl_details.map_id "
            + "        AND gdms_qtl_details.qtl_id = gdms_qtl.qtl_id " + "        AND gdms_qtl.qtl_name = :qtlName "
            + "        AND gdms_markers_onmap.linkage_group = :chromosome "
            + "        AND gdms_markers_onmap.start_position between :min AND :max ";

    public static final String GET_MAP_IDS_BY_QTL = "SELECT DISTINCT map_id " + "FROM gdms_qtl_details "
            + "WHERE qtl_id = (SELECT qtl_id FROM gdms_qtl WHERE qtl_name = :qtlName) ";

    public static final String COUNT_MAP_IDS_BY_QTL = "SELECT COUNT(DISTINCT map_id) " + "FROM gdms_qtl_details "
            + "WHERE qtl_id = (SELECT qtl_id FROM gdms_qtl WHERE qtl_name = :qtlName) ";

    public static final String GET_MARKER_IDS_BY_QTL_AND_MAP_ID = "SELECT DISTINCT gm.marker_id "
            + "FROM gdms_marker gm " + "    INNER JOIN gdms_markers_onmap gmo ON gm.marker_id = gmo.marker_id "
            + "    INNER JOIN gdms_qtl_details gqd ON gqd.map_id = gmo.map_id "
            + "    INNER JOIN gdms_qtl gq ON gq.qtl_id = gqd.qtl_id " + "    WHERE gmo.linkage_group = :chromosome "
            + "         AND gmo.start_position BETWEEN :min AND :max " + "         AND gmo.map_id = :mapId "
            + "         AND gq.qtl_name like :qtlName ";

    public static final String COUNT_MARKER_IDS_BY_QTL_AND_MAP_ID = "SELECT COUNT(DISTINCT gm.marker_id) "
            + "FROM gdms_marker gm " + "    INNER JOIN gdms_markers_onmap gmo ON gm.marker_id = gmo.marker_id "
            + "    INNER JOIN gdms_qtl_details gqd ON gqd.map_id = gmo.map_id "
            + "    INNER JOIN gdms_qtl gq ON gq.qtl_id = gqd.qtl_id " + "    WHERE gmo.linkage_group = :chromosome "
            + "         AND gmo.start_position BETWEEN :min AND :max " + "         AND gmo.map_id = :mapId "
            + "         AND gq.qtl_name like :qtlName ";

    public static final String GET_MAP_ID_BY_QTL = "SELECT DISTINCT map_id " + "FROM gdms_qtl_details "
            + "WHERE qtl_id = (SELECT qtl_id FROM gdms_qtl WHERE qtl_name = :qtlName) ";;

    public static final String GET_QTL_TRAITS_BY_DATASET_ID = "SELECT DISTINCT gqd.tid " + "FROM gdms_qtl gq  "
            + "INNER JOIN gdms_qtl_details gqd  " + "ON gq.qtl_id = gqd.qtl_id  "
            + "WHERE gq.dataset_id = :datasetId  ";

    public static final String COUNT_QTL_TRAITS_BY_DATASET_ID = "SELECT COUNT(DISTINCT gqd.tid) "
            + "FROM gdms_qtl gq  " + "INNER JOIN gdms_qtl_details gqd  " + "ON gq.qtl_id = gqd.qtl_id  "
            + "WHERE gq.dataset_id = :datasetId  ";

    public static final String GET_QTL_DATA_BY_QTL_TRAITS = "SELECT CONCAT(gq.qtl_name,'') "
            + "      , gqd.linkage_group " + "      , gqd.position " + "      , gqd.min_position "
            + "      , gqd.max_position " + "      , gqd.tid " + "      , CONCAT(gqd.experiment,'') "
            + "      , gqd.left_flanking_marker " + "      , gqd.right_flanking_marker " + "      , gqd.effect "
            + "      , gqd.score_value " + "      , gqd.r_square " + "FROM gdms_qtl_details gqd "
            + "INNER JOIN gdms_qtl gq " + "ON gqd.qtl_id = gq.qtl_id " + "AND gqd.tid " + "IN (:qtlTraits) ";

    public static final String COUNT_QTL_DATA_BY_QTL_TRAITS = "SELECT COUNT(*) " + "FROM gdms_qtl_details gqd "
            + "INNER JOIN gdms_qtl gq " + "ON gqd.qtl_id = gq.qtl_id " + "AND gqd.tid " + "IN (:qtlTraits) ";

    public List<Integer> getMarkerIdsByQtl(String qtlName, String chromosome, float min, float max, int start,
            int numOfRows) throws MiddlewareQueryException {
        try {
            SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.GET_MARKER_IDS_BY_QTL);
            query.setParameter("qtlName", qtlName);
            query.setParameter("chromosome", chromosome);
            query.setParameter("min", min);
            query.setParameter("max", max);
            query.setFirstResult(start);
            query.setMaxResults(numOfRows);
            return query.list();

        } catch (HibernateException e) {
            this.logAndThrowException("Error with getMarkerIDsByQtl() query from QtlDetails: " + e.getMessage(), e);
        }
        return new ArrayList<Integer>();
    }

    public long countMarkerIdsByQtl(String qtlName, String chromosome, float min, float max)
            throws MiddlewareQueryException {
        try {
            SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.COUNT_MARKER_IDS_BY_QTL);
            query.setParameter("qtlName", qtlName);
            query.setParameter("chromosome", chromosome);
            query.setParameter("min", min);
            query.setParameter("max", max);
            BigInteger result = (BigInteger) query.uniqueResult();
            if (result != null) {
                return result.longValue();
            }

        } catch (HibernateException e) {
            this.logAndThrowException("Error with countMarkerIdsByQtl() query from QtlDetails: " + e.getMessage(),
                    e);
        }
        return 0;

    }

    public Integer getMapIdByQtlName(String qtlName) throws MiddlewareQueryException {
        try {
            SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.GET_MAP_ID_BY_QTL);
            query.setParameter("qtlName", qtlName);
            return (Integer) query.uniqueResult();

        } catch (HibernateException e) {
            this.logAndThrowException("Error with getMapIdByQtlName() query from QtlDetails: " + e.getMessage(), e);
        }
        return 0;
    }

    public List<Integer> getMapIdsByQtlName(String qtlName, int start, int numOfRows)
            throws MiddlewareQueryException {
        try {
            SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.GET_MAP_IDS_BY_QTL);
            query.setParameter("qtlName", qtlName);
            query.setFirstResult(start);
            query.setMaxResults(numOfRows);
            return query.list();

        } catch (HibernateException e) {
            this.logAndThrowException("Error with getMapIdsByQtlName() query from QtlDetails: " + e.getMessage(),
                    e);
        }
        return new ArrayList<Integer>();
    }

    public long countMapIdsByQtlName(String qtlName) throws MiddlewareQueryException {
        try {
            SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.COUNT_MAP_IDS_BY_QTL);
            query.setParameter("qtlName", qtlName);
            BigInteger result = (BigInteger) query.uniqueResult();
            if (result != null) {
                return result.longValue();
            }
        } catch (HibernateException e) {
            this.logAndThrowException("Error with countMapIdsByQtlName() query from QtlDetails: " + e.getMessage(),
                    e);
        }
        return 0;
    }

    public List<Integer> getMarkerIdsByQtl(Integer mapId, String qtlName, String chromosome, int min, int max,
            int start, int numOfRows) throws MiddlewareQueryException {
        try {
            SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.GET_MARKER_IDS_BY_QTL_AND_MAP_ID);
            query.setParameter("qtlName", qtlName);
            query.setParameter("chromosome", chromosome);
            query.setParameter("min", min);
            query.setParameter("max", max);
            query.setParameter("mapId", mapId);
            return query.list();
        } catch (HibernateException e) {
            this.logAndThrowException("Error with getMarkerIDsByQtl() query from QtlDetails: " + e.getMessage(), e);
        }
        return new ArrayList<Integer>();
    }

    public long countMarkerIdsByQtl(Integer mapId, String qtlName, String chromosome, int min, int max)
            throws MiddlewareQueryException {
        try {
            SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.COUNT_MARKER_IDS_BY_QTL_AND_MAP_ID);
            query.setParameter("qtlName", qtlName);
            query.setParameter("chromosome", chromosome);
            query.setParameter("min", min);
            query.setParameter("max", max);
            query.setParameter("mapId", mapId);
            BigInteger result = (BigInteger) query.uniqueResult();
            if (result != null) {
                return result.longValue();
            }

        } catch (HibernateException e) {
            this.logAndThrowException("Error with countMarkerIdsByQtl() query from QtlDetails: " + e.getMessage(),
                    e);
        }
        return 0;
    }

    public List<Integer> getQtlTraitsByDatasetId(Integer datasetId, int start, int numOfRows)
            throws MiddlewareQueryException {
        try {
            if (datasetId != null) {
                SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.GET_QTL_TRAITS_BY_DATASET_ID);
                query.setParameter("datasetId", datasetId);
                query.setFirstResult(start);
                query.setMaxResults(numOfRows);
                return query.list();
            }
        } catch (HibernateException e) {
            this.logAndThrowException(
                    "Error with getQtlTraitsByDatasetId() query from QtlDetails: " + e.getMessage(), e);
        }
        return new ArrayList<Integer>();
    }

    public long countQtlTraitsByDatasetId(Integer datasetId) throws MiddlewareQueryException {
        try {
            if (datasetId != null) {
                SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.COUNT_QTL_TRAITS_BY_DATASET_ID);
                query.setParameter("datasetId", datasetId);
                BigInteger result = (BigInteger) query.uniqueResult();
                if (result != null) {
                    return result.longValue();
                }
            }
        } catch (HibernateException e) {
            this.logAndThrowException(
                    "Error with countQtlTraitsByDatasetId() query from QtlDetails: " + e.getMessage(), e);
        }
        return 0;
    }

    public List<QtlDataElement> getQtlDataByQtlTraits(List<Integer> qtlTraits, int start, int numOfRows)
            throws MiddlewareQueryException {
        List<QtlDataElement> toReturn = new ArrayList<QtlDataElement>();

        try {
            if (qtlTraits != null && !qtlTraits.isEmpty()) {
                SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.GET_QTL_DATA_BY_QTL_TRAITS);
                query.setParameterList("qtlTraits", qtlTraits);
                query.setFirstResult(start);
                query.setMaxResults(numOfRows);

                @SuppressWarnings("rawtypes")
                List results = query.list();

                for (Object o : results) {
                    Object[] result = (Object[]) o;
                    if (result != null) {
                        // Get the fields for QtlDataElement
                        String qtlName = (String) result[0];
                        String linkageGroup = (String) result[1];
                        Float position = (Float) result[2];
                        Float minPosition = (Float) result[3];
                        Float maxPosition = (Float) result[4];
                        Integer traitId = (Integer) result[5];
                        String experiment = (String) result[6];
                        String leftFlankingMarker = (String) result[7];
                        String rightFlankingMarker = (String) result[8];
                        Float effect = (Float) result[9];
                        Float scoreValue = (Float) result[10];
                        Float rSquare = (Float) result[11];

                        QtlDataElement qtlData = new QtlDataElement(qtlName, linkageGroup, position, minPosition,
                                maxPosition, traitId, experiment, leftFlankingMarker, rightFlankingMarker, effect,
                                scoreValue, rSquare);
                        toReturn.add(qtlData);
                    }
                }
            }
        } catch (HibernateException e) {
            this.logAndThrowException("Error with getQtlDataByQtlTraits() query from QtlDetails: " + e.getMessage(),
                    e);
        }
        return toReturn;
    }

    public long countQtlDataByQtlTraits(List<Integer> qtlTraits) throws MiddlewareQueryException {
        try {
            if (qtlTraits != null && !qtlTraits.isEmpty()) {
                SQLQuery query = this.getSession().createSQLQuery(QtlDetailsDAO.COUNT_QTL_DATA_BY_QTL_TRAITS);
                query.setParameterList("qtlTraits", qtlTraits);
                BigInteger result = (BigInteger) query.uniqueResult();
                if (result != null) {
                    return result.longValue();
                }
            }
        } catch (HibernateException e) {
            this.logAndThrowException(
                    "Error with countQtlDataByQtlTraits() query from QtlDetails: " + e.getMessage(), e);
        }
        return 0;
    }

    public void deleteByQtlIds(List<Integer> qtlIds) throws MiddlewareQueryException {
        try {
            // Please note we are manually flushing because non hibernate based deletes and updates causes the Hibernate session to get out of synch with
            // underlying database. Thus flushing to force Hibernate to synchronize with the underlying database before the delete
            // statement
            this.getSession().flush();

            SQLQuery statement = this.getSession().createSQLQuery(
                    "DELETE FROM gdms_qtl_details WHERE qtl_id IN (" + StringUtils.join(qtlIds, ",") + ")");
            statement.executeUpdate();

        } catch (HibernateException e) {
            this.logAndThrowException("Error in deleteByQtlIds=" + qtlIds + " in QtlDetailsDAO: " + e.getMessage(),
                    e);
        }
    }

    public List<QtlDetails> getQtlDetailsByMapId(Integer mapId) throws MiddlewareQueryException {
        try {
            Criteria criteria = this.getSession().createCriteria(this.getPersistentClass());
            criteria.add(Restrictions.eq("mapId", mapId));

            return criteria.list();

        } catch (HibernateException e) {
            this.logAndThrowException(
                    "Error in getQtlDetailsByMapId=" + mapId + " in QtlDetailsDAO: " + e.getMessage(), e);
        }
        return new ArrayList<QtlDetails>();
    }

    public List<QtlDetails> getQtlDetailsByQtlIds(List<Integer> qtlIds) throws MiddlewareQueryException {
        try {
            Criteria criteria = this.getSession().createCriteria(this.getPersistentClass());
            criteria.add(Restrictions.in("qtlId", qtlIds));

            return criteria.list();

        } catch (HibernateException e) {
            this.logAndThrowException(
                    "Error in getQtlDetailsByQtlId=" + qtlIds + " in QtlDetailsDAO: " + e.getMessage(), e);
        }
        return new ArrayList<QtlDetails>();
    }

    public long countQtlDetailsByMapId(Integer mapId) throws MiddlewareQueryException {
        try {
            Criteria criteria = this.getSession().createCriteria(this.getPersistentClass());
            criteria.add(Restrictions.eq("mapId", mapId));
            criteria.setProjection(Projections.rowCount());

            return (Long) criteria.uniqueResult();

        } catch (HibernateException e) {
            this.logAndThrowException(
                    "Error in countQtlDetailsByMapId=" + mapId + " in QtlDetailsDAO: " + e.getMessage(), e);
        }
        return 0;
    }
}