Examples with DataIntegrityViolationException org.springframework.dao.DataIntegrityViolationException used on opensource projects

Example 21 with DataIntegrityViolationException

use of org.springframework.dao.DataIntegrityViolationException in project goci by EBISPOT.

the class V1_9_9_016__Association_locus_links_for_unusual_single_snps method migrate.

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    // get all genes
    IdAndStringRowHandler geneHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_GENES, geneHandler);
    final Map<Long, String> geneIdToNameMap = geneHandler.getIdToStringMap();
    // get all snps
    IdAndStringRowHandler snpHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_SNPS, snpHandler);
    final Map<Long, String> snpIdToRsIdMap = snpHandler.getIdToStringMap();
    // get all associations and link to gene id
    final Map<Long, Set<Long>> associationIdToGeneIds = new HashMap<>();
    final Map<Long, Long> associationIdToSnpId = new HashMap<>();
    final Map<Long, String> associationIdToRiskAlleleName = new HashMap<>();
    jdbcTemplate.query(SELECT_ASSOCIATIONS_AND_SNPS, (resultSet, i) -> {
        long associationID = resultSet.getLong(1);
        List<String> riskAlleles;
        List<String> geneNames;
        List<String> rsIds;
        String riskAlleleStr = resultSet.getString(2);
        if (riskAlleleStr != null) {
            riskAlleles = split(resultSet.getString(2).trim());
        } else {
            riskAlleles = new ArrayList<>();
        }
        String genesStr = resultSet.getString(3);
        if (genesStr != null) {
            geneNames = split(genesStr.trim());
        } else {
            geneNames = new ArrayList<>();
        }
        String snpsStr = resultSet.getString(4);
        rsIds = new ArrayList<>();
        if (snpsStr != null) {
            rsIds.add(snpsStr.trim());
        }
        // in case we need to add new genes
        SimpleJdbcInsert insertGene = new SimpleJdbcInsert(jdbcTemplate).withTableName("GENE").usingColumns("GENE_NAME").usingGeneratedKeyColumns("ID");
        for (String geneName : geneNames) {
            boolean found = false;
            for (long geneID : geneIdToNameMap.keySet()) {
                if (geneIdToNameMap.get(geneID).equals(geneName)) {
                    if (!associationIdToGeneIds.containsKey(associationID)) {
                        associationIdToGeneIds.put(associationID, new HashSet<>());
                    }
                    if (!associationIdToGeneIds.get(associationID).contains(geneID)) {
                        // add the new associated gene
                        associationIdToGeneIds.get(associationID).add(geneID);
                    }
                    found = true;
                    // we break here to handle duplicate entries in the gene table of the database
                    break;
                }
            }
            if (!found) {
                // the GENE with the GENE_NAME in GWASSTUDIESSNP doesn't exist in GWASGENE,
                // so create a new GENE entry
                Map<String, Object> geneArgs = new HashMap<>();
                geneArgs.put("GENE_NAME", geneName);
                long geneID = insertGene.executeAndReturnKey(geneArgs).longValue();
                geneIdToNameMap.put(geneID, geneName);
                if (!associationIdToGeneIds.containsKey(associationID)) {
                    associationIdToGeneIds.put(associationID, new HashSet<>());
                }
                if (!associationIdToGeneIds.get(associationID).contains(geneID)) {
                    // add the new associated gene
                    associationIdToGeneIds.get(associationID).add(geneID);
                }
            }
        }
        // in case we need to add new SNPs
        SimpleJdbcInsert insertSnp = new SimpleJdbcInsert(jdbcTemplate).withTableName("SINGLE_NUCLEOTIDE_POLYMORPHISM").usingColumns("RS_ID").usingGeneratedKeyColumns("ID");
        for (String rsId : rsIds) {
            boolean foundSnp = false;
            for (long snpID : snpIdToRsIdMap.keySet()) {
                if (snpIdToRsIdMap.get(snpID).equals(rsId)) {
                    if (associationIdToSnpId.containsKey(associationID)) {
                        // check for equality of SNP names
                        String rsExisting = snpIdToRsIdMap.get(associationIdToSnpId.get(associationID));
                        String rsNew = snpIdToRsIdMap.get(snpID);
                        if (!rsExisting.equals(rsNew)) {
                            // can't safely ignore, this isn't simply duplicate entries in SNP table
                            throw new RuntimeException("Can't link association '" + associationID + "' to single SNP - " + "more than one connected rsID (" + "existing = " + associationIdToSnpId.get(associationID) + ", " + "new = " + snpID + ")");
                        }
                    } else {
                        if (riskAlleles.size() > 1) {
                            throw new RuntimeException("Single SNP with multiple risk alleles for SNP - " + snpID + " (risk alleles = " + riskAlleles + ")");
                        } else {
                            if (!riskAlleles.isEmpty()) {
                                associationIdToSnpId.put(associationID, snpID);
                                associationIdToRiskAlleleName.put(associationID, riskAlleles.iterator().next());
                            }
                        }
                    }
                    foundSnp = true;
                    // we break here to handle duplicate entries in the snp table of the database
                    break;
                }
            }
            if (!foundSnp) {
                // the SNP with the RS_ID in GWASSTUDIESSNP doesn't exist in GWASSNP,
                // so create a new SINGLE_NUCLEOTIDE_POLYMORPHISM entry
                Map<String, Object> snpArgs = new HashMap<>();
                snpArgs.put("RS_ID", rsId);
                long snpID = insertSnp.executeAndReturnKey(snpArgs).longValue();
                snpIdToRsIdMap.put(snpID, rsId);
                associationIdToSnpId.put(associationID, snpID);
            }
        }
        return null;
    });
    SimpleJdbcInsert insertLocus = new SimpleJdbcInsert(jdbcTemplate).withTableName("LOCUS").usingColumns("HAPLOTYPE_SNP_COUNT", "DESCRIPTION").usingGeneratedKeyColumns("ID");
    SimpleJdbcInsert insertAssociationLocus = new SimpleJdbcInsert(jdbcTemplate).withTableName("ASSOCIATION_LOCUS").usingColumns("ASSOCIATION_ID", "LOCUS_ID");
    SimpleJdbcInsert insertRiskAllele = new SimpleJdbcInsert(jdbcTemplate).withTableName("RISK_ALLELE").usingColumns("RISK_ALLELE_NAME").usingGeneratedKeyColumns("ID");
    SimpleJdbcInsert insertLocusRiskAllele = new SimpleJdbcInsert(jdbcTemplate).withTableName("LOCUS_RISK_ALLELE").usingColumns("LOCUS_ID", "RISK_ALLELE_ID");
    SimpleJdbcInsert insertRiskAlleleSnp = new SimpleJdbcInsert(jdbcTemplate).withTableName("RISK_ALLELE_SNP").usingColumns("RISK_ALLELE_ID", "SNP_ID");
    SimpleJdbcInsert insertAuthorReportedGene = new SimpleJdbcInsert(jdbcTemplate).withTableName("AUTHOR_REPORTED_GENE").usingColumns("LOCUS_ID", "REPORTED_GENE_ID");
    for (Long associationID : associationIdToSnpId.keySet()) {
        // create a single LOCUS and get the locus ID
        Map<String, Object> locusArgs = new HashMap<>();
        locusArgs.put("HAPLOTYPE_SNP_COUNT", null);
        locusArgs.put("DESCRIPTION", "Single variant");
        Number locusID = insertLocus.executeAndReturnKey(locusArgs);
        // now create the ASSOCIATION_LOCUS link
        Map<String, Object> associationLocusArgs = new HashMap<>();
        associationLocusArgs.put("ASSOCIATION_ID", associationID);
        associationLocusArgs.put("LOCUS_ID", locusID);
        insertAssociationLocus.execute(associationLocusArgs);
        // now create a single RISK_ALLELE and get the risk allele ID
        Map<String, Object> riskAlleleArgs = new HashMap<>();
        riskAlleleArgs.put("RISK_ALLELE_NAME", associationIdToRiskAlleleName.get(associationID));
        Number riskAlleleID = insertRiskAllele.executeAndReturnKey(riskAlleleArgs);
        // now create the LOCUS_RISK_ALLELE link
        Map<String, Object> locusRiskAlleleArgs = new HashMap<>();
        locusRiskAlleleArgs.put("LOCUS_ID", locusID.longValue());
        locusRiskAlleleArgs.put("RISK_ALLELE_ID", riskAlleleID.longValue());
        insertLocusRiskAllele.execute(locusRiskAlleleArgs);
        // now create the RISK_ALLELE_SNP link
        Long snpID = associationIdToSnpId.get(associationID);
        try {
            Map<String, Object> riskAlleleSnpArgs = new HashMap<>();
            riskAlleleSnpArgs.put("RISK_ALLELE_ID", riskAlleleID.longValue());
            riskAlleleSnpArgs.put("SNP_ID", snpID);
            insertRiskAlleleSnp.execute(riskAlleleSnpArgs);
        } catch (DataIntegrityViolationException e) {
            throw new RuntimeException("Failed to insert link between snp = " + snpID + " and risk allele = " + riskAlleleID, e);
        }
        // finally create the AUTHOR_REPORTED_GENE link
        if (associationIdToGeneIds.containsKey(associationID)) {
            for (Long geneID : associationIdToGeneIds.get(associationID)) {
                try {
                    Map<String, Object> authorReportedGeneArgs = new HashMap<>();
                    authorReportedGeneArgs.put("LOCUS_ID", locusID.longValue());
                    authorReportedGeneArgs.put("REPORTED_GENE_ID", geneID);
                    insertAuthorReportedGene.execute(authorReportedGeneArgs);
                } catch (DataIntegrityViolationException e) {
                    throw new RuntimeException("Failed to insert link between locus = " + locusID + " and reported gene  = " + geneID, e);
                }
            }
        }
    }
}

Example 22 with DataIntegrityViolationException

use of org.springframework.dao.DataIntegrityViolationException in project goci by EBISPOT.

the class V1_9_9_037__Risk_alllele_locus_links method migrate.

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    // Maps to store relevant information
    final Map<Long, String> riskAlleleIdToRiskAlleleName = new HashMap<>();
    final Map<Long, Long> riskAlleleIdToSnpId = new HashMap<>();
    final Map<Long, Set<Long>> riskAlleleIdToLociIds = new HashMap<>();
    jdbcTemplate.query(SELECT_DATA_FOR_UPDATE, (resultSet, i) -> {
        String riskAlleleName = resultSet.getString(1);
        long riskAlleleId = resultSet.getLong(2);
        long locusId = resultSet.getLong(3);
        long snpId = resultSet.getLong(4);
        riskAlleleIdToRiskAlleleName.put(riskAlleleId, riskAlleleName);
        riskAlleleIdToSnpId.put(riskAlleleId, snpId);
        // Create map of risk allele to linked loci
        if (riskAlleleIdToLociIds.containsKey(riskAlleleId)) {
            riskAlleleIdToLociIds.get(riskAlleleId).add(locusId);
        } else // First time we see a risk allele don't store the locus ID
        // as this locus will keep the old risk allele id linked
        // to it
        {
            riskAlleleIdToLociIds.put(riskAlleleId, new HashSet<>());
        }
        return null;
    });
    // Insert statements
    SimpleJdbcInsert insertRiskAllele = new SimpleJdbcInsert(jdbcTemplate).withTableName("RISK_ALLELE").usingColumns("RISK_ALLELE_NAME").usingGeneratedKeyColumns("ID");
    SimpleJdbcInsert insertRiskAlleleSnp = new SimpleJdbcInsert(jdbcTemplate).withTableName("RISK_ALLELE_SNP").usingColumns("RISK_ALLELE_ID", "SNP_ID");
    // For each locus create a new risk allele with same name and snp as old ID
    for (long oldRiskAlleleId : riskAlleleIdToLociIds.keySet()) {
        Set<Long> lociIds = riskAlleleIdToLociIds.get(oldRiskAlleleId);
        // For each locus
        for (long locusId : lociIds) {
            // Get the risk allele name using the old ID
            String riskAlleleName = riskAlleleIdToRiskAlleleName.get(oldRiskAlleleId);
            //Create new risk allele
            Map<String, Object> riskAlleleArgs = new HashMap<>();
            riskAlleleArgs.put("RISK_ALLELE_NAME", riskAlleleName);
            Number riskAlleleID = insertRiskAllele.executeAndReturnKey(riskAlleleArgs);
            // Update LOCUS_RISK_ALLELE table
            jdbcTemplate.update(UPDATE_LOCUS_RISK_ALLELE, riskAlleleID.longValue(), locusId, oldRiskAlleleId);
            // Insert new risk allele details into RISK_ALLELE_SNP
            long snpId = riskAlleleIdToSnpId.get(oldRiskAlleleId);
            try {
                Map<String, Object> riskAlleleSnpArgs = new HashMap<>();
                riskAlleleSnpArgs.put("RISK_ALLELE_ID", riskAlleleID.longValue());
                riskAlleleSnpArgs.put("SNP_ID", snpId);
                insertRiskAlleleSnp.execute(riskAlleleSnpArgs);
            } catch (DataIntegrityViolationException e) {
                throw new RuntimeException("Failed to insert link between risk allele = " + riskAlleleID + " and snp  = " + snpId, e);
            }
        }
    }
}