Examples with SimpleJdbcInsert org.springframework.jdbc.core.simple.SimpleJdbcInsert used on opensource projects

Example 1 with SimpleJdbcInsert

use of org.springframework.jdbc.core.simple.SimpleJdbcInsert in project goci by EBISPOT.

the class V2_2_0_030__Migrate_ancestry_countries method migrate.

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    IdAndStringRowHandler countryOfOriginHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_COUNTRY_OF_ORIGIN, countryOfOriginHandler);
    final Map<Long, String> ancestryIdToCountryOfOrigin = countryOfOriginHandler.getIdToStringMap();
    IdAndStringRowHandler countryOfRecruitmentHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_COUNTRY_OF_RECRUITMENT, countryOfRecruitmentHandler);
    final Map<Long, String> ancestryIdToCountryOfRecruitment = countryOfRecruitmentHandler.getIdToStringMap();
    IdAndStringRowHandler countryNameHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_COUNTRY_NAMES, countryNameHandler);
    final Map<Long, String> countryIdToCountry = countryNameHandler.getIdToStringMap();
    final Map<String, Long> countries = new HashMap<>();
    for (Long id : countryIdToCountry.keySet()) {
        String c = countryIdToCountry.get(id);
        countries.put(c, id);
    }
    SimpleJdbcInsert insertAncestryCountryOfOrigin = new SimpleJdbcInsert(jdbcTemplate).withTableName("ANCESTRY_COUNTRY_OF_ORIGIN").usingColumns("ANCESTRY_ID", "COUNTRY_ID");
    for (Long ancestryId : ancestryIdToCountryOfOrigin.keySet()) {
        String ancestry = ancestryIdToCountryOfOrigin.get(ancestryId);
        if (ancestry != null) {
            List<String> coos = new ArrayList<>();
            for (String co : countries.keySet()) {
                if (ancestry.contains(co)) {
                    coos.add(co);
                }
            }
            for (String c : coos) {
                Long countryId = countries.get(c);
                Map<String, Object> ancestryCountryOfOriginArgs = new HashMap<>();
                ancestryCountryOfOriginArgs.put("ANCESTRY_ID", ancestryId);
                ancestryCountryOfOriginArgs.put("COUNTRY_ID", countryId);
                insertAncestryCountryOfOrigin.execute(ancestryCountryOfOriginArgs);
            }
        }
    }
    SimpleJdbcInsert insertAncestryCountryOfRecruitment = new SimpleJdbcInsert(jdbcTemplate).withTableName("ANCESTRY_COUNTRY_RECRUITMENT").usingColumns("ANCESTRY_ID", "COUNTRY_ID");
    for (Long ancestryId : ancestryIdToCountryOfRecruitment.keySet()) {
        String ancestry = ancestryIdToCountryOfRecruitment.get(ancestryId);
        if (ancestry != null) {
            List<String> cors = new ArrayList<>();
            for (String cor : countries.keySet()) {
                if (ancestry.contains(cor)) {
                    cors.add(cor);
                }
            }
            for (String c : cors) {
                Long countryId = countries.get(c);
                Map<String, Object> ancestryCountryOfRecruitmentArgs = new HashMap<>();
                ancestryCountryOfRecruitmentArgs.put("ANCESTRY_ID", ancestryId);
                ancestryCountryOfRecruitmentArgs.put("COUNTRY_ID", countryId);
                insertAncestryCountryOfRecruitment.execute(ancestryCountryOfRecruitmentArgs);
            }
        }
    }
}

Example 2 with SimpleJdbcInsert

use of org.springframework.jdbc.core.simple.SimpleJdbcInsert in project goci by EBISPOT.

the class V1_9_9_011__Association_locus_links_for_haplotypes 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, List<Long>> associationIdToSnpIds = new HashMap<>();
    final Map<Long, List<String>> associationIdToRiskAlleleNames = new HashMap<>();
    final Map<Long, String> associationIdToMigratedDescription = 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<>();
        }
        associationIdToMigratedDescription.put(associationID, riskAlleleStr);
        String genesStr = resultSet.getString(3);
        if (genesStr != null) {
            geneNames = split(genesStr.trim());
        } else {
            geneNames = new ArrayList<>();
        }
        String snpsStr = resultSet.getString(4);
        if (snpsStr != null) {
            rsIds = split(snpsStr.trim());
        } else {
            rsIds = new ArrayList<>();
        }
        // 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");
        Iterator<String> rsIdIterator = rsIds.iterator();
        Iterator<String> riskAlleleIterator = riskAlleles.iterator();
        if (rsIds.size() == riskAlleles.size()) {
            while (rsIdIterator.hasNext()) {
                String rsId = rsIdIterator.next().trim();
                String riskAllele = riskAlleleIterator.next().trim();
                boolean foundSnp = false;
                for (long snpID : snpIdToRsIdMap.keySet()) {
                    if (snpIdToRsIdMap.get(snpID).equals(rsId)) {
                        if (!associationIdToSnpIds.containsKey(associationID)) {
                            associationIdToSnpIds.put(associationID, new ArrayList<>());
                            associationIdToRiskAlleleNames.put(associationID, new ArrayList<>());
                        }
                        if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                            // add the new associated snp and risk allele
                            associationIdToSnpIds.get(associationID).add(snpID);
                            associationIdToRiskAlleleNames.get(associationID).add(riskAllele);
                        }
                        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);
                    insertSnp.execute(snpArgs);
                    long snpID = insertSnp.executeAndReturnKey(snpArgs).longValue();
                    snpIdToRsIdMap.put(snpID, rsId);
                    if (!associationIdToSnpIds.containsKey(associationID)) {
                        associationIdToSnpIds.put(associationID, new ArrayList<>());
                    }
                    if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                        // add the new associated gene
                        associationIdToSnpIds.get(associationID).add(snpID);
                    }
                }
            }
        } else {
            getLog().warn("Mismatched number of snps and risk alleles for " + "association " + associationID + " " + "(snp string = " + snpsStr + " and " + "risk allele string = " + riskAlleleStr + ").  " + "Inferring risk alleles from SNP");
            while (rsIdIterator.hasNext()) {
                String rsId = rsIdIterator.next().trim();
                String riskAllele = rsId + "-?";
                for (String nextRiskAllele : riskAlleles) {
                    if (nextRiskAllele.contains(rsId)) {
                        // overwrite with actual value
                        riskAllele = nextRiskAllele;
                        break;
                    }
                }
                boolean foundSnp = false;
                for (long snpID : snpIdToRsIdMap.keySet()) {
                    if (snpIdToRsIdMap.get(snpID).equals(rsId)) {
                        if (!associationIdToSnpIds.containsKey(associationID)) {
                            associationIdToSnpIds.put(associationID, new ArrayList<>());
                            associationIdToRiskAlleleNames.put(associationID, new ArrayList<>());
                        }
                        if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                            // add the new associated snp and risk allele
                            associationIdToSnpIds.get(associationID).add(snpID);
                            associationIdToRiskAlleleNames.get(associationID).add(riskAllele);
                        }
                        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);
                    insertSnp.execute(snpArgs);
                    long snpID = insertSnp.executeAndReturnKey(snpArgs).longValue();
                    snpIdToRsIdMap.put(snpID, rsId);
                    if (!associationIdToSnpIds.containsKey(associationID)) {
                        associationIdToSnpIds.put(associationID, new ArrayList<>());
                        associationIdToRiskAlleleNames.put(associationID, new ArrayList<>());
                    }
                    if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                        // add the new associated gene
                        associationIdToSnpIds.get(associationID).add(snpID);
                        associationIdToRiskAlleleNames.get(associationID).add(riskAllele);
                    }
                }
            }
        }
        return null;
    });
    SimpleJdbcInsert insertLocus = new SimpleJdbcInsert(jdbcTemplate).withTableName("LOCUS").usingColumns("HAPLOTYPE_SNP_COUNT", "DESCRIPTION", "MIGRATED_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 : associationIdToSnpIds.keySet()) {
        // get snp/risk allele pairs
        List<Long> snps = associationIdToSnpIds.get(associationID);
        List<String> riskAlleles = associationIdToRiskAlleleNames.get(associationID);
        if (snps.size() != riskAlleles.size()) {
            throw new RuntimeException("Mismatched SNP ID/Risk Allele name pairs for " + "association " + associationID + " (" + snps + ", " + riskAlleles + ")");
        } else {
            // create a single LOCUS and get the locus ID
            Map<String, Object> locusArgs = new HashMap<>();
            locusArgs.put("HAPLOTYPE_SNP_COUNT", snps.size());
            locusArgs.put("DESCRIPTION", String.valueOf(snps.size()) + " SNP haplotype");
            locusArgs.put("MIGRATED_DESCRIPTION", associationIdToMigratedDescription.get(associationID));
            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);
            Iterator<Long> snpIterator = snps.iterator();
            Iterator<String> riskAlleleIterator = riskAlleles.iterator();
            while (riskAlleleIterator.hasNext()) {
                Long snpID = snpIterator.next();
                String riskAlleleName = riskAlleleIterator.next();
                // now create a single RISK_ALLELE and get the risk allele ID
                Map<String, Object> riskAlleleArgs = new HashMap<>();
                riskAlleleArgs.put("RISK_ALLELE_NAME", riskAlleleName);
                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
                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 3 with SimpleJdbcInsert

use of org.springframework.jdbc.core.simple.SimpleJdbcInsert in project goci by EBISPOT.

the class V1_9_9_012__Association_locus_links_for_interaction_studies 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, List<Long>> associationIdToSnpIds = new HashMap<>();
    final Map<Long, List<String>> associationIdToRiskAlleleNames = 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(), "x", ":");
        } 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);
        if (snpsStr != null) {
            rsIds = split(snpsStr.trim(), "x", ":");
        } else {
            rsIds = new ArrayList<>();
        }
        // 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");
        Iterator<String> rsIdIterator = rsIds.iterator();
        Iterator<String> riskAlleleIterator = riskAlleles.iterator();
        if (rsIds.size() == riskAlleles.size()) {
            while (rsIdIterator.hasNext()) {
                String rsId = rsIdIterator.next().trim();
                String riskAllele = riskAlleleIterator.next().trim();
                boolean foundSnp = false;
                for (long snpID : snpIdToRsIdMap.keySet()) {
                    if (snpIdToRsIdMap.get(snpID).equals(rsId)) {
                        if (!associationIdToSnpIds.containsKey(associationID)) {
                            associationIdToSnpIds.put(associationID, new ArrayList<>());
                            associationIdToRiskAlleleNames.put(associationID, new ArrayList<>());
                        }
                        if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                            // add the new associated snp and risk allele
                            associationIdToSnpIds.get(associationID).add(snpID);
                            associationIdToRiskAlleleNames.get(associationID).add(riskAllele);
                        }
                        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);
                    insertSnp.execute(snpArgs);
                    long snpID = insertSnp.executeAndReturnKey(snpArgs).longValue();
                    snpIdToRsIdMap.put(snpID, rsId);
                    if (!associationIdToSnpIds.containsKey(associationID)) {
                        associationIdToSnpIds.put(associationID, new ArrayList<>());
                        associationIdToRiskAlleleNames.put(associationID, new ArrayList<>());
                    }
                    if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                        // add the new associated gene
                        associationIdToSnpIds.get(associationID).add(snpID);
                        associationIdToRiskAlleleNames.get(associationID).add(riskAllele);
                    }
                }
            }
        } else {
            getLog().warn("Mismatched number of snps and risk alleles for " + "association " + associationID + " " + "(snp string = " + snpsStr + " and " + "risk allele string = " + riskAlleleStr + ").  " + "Inferring risk alleles from SNP");
            while (rsIdIterator.hasNext()) {
                String rsId = rsIdIterator.next().trim();
                String riskAllele = rsId + "-?";
                for (String nextRiskAllele : riskAlleles) {
                    if (nextRiskAllele.contains(rsId)) {
                        // overwrite with actual value
                        riskAllele = nextRiskAllele;
                        break;
                    }
                }
                boolean foundSnp = false;
                for (long snpID : snpIdToRsIdMap.keySet()) {
                    if (snpIdToRsIdMap.get(snpID).equals(rsId)) {
                        if (!associationIdToSnpIds.containsKey(associationID)) {
                            associationIdToSnpIds.put(associationID, new ArrayList<>());
                            associationIdToRiskAlleleNames.put(associationID, new ArrayList<>());
                        }
                        if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                            // add the new associated snp and risk allele
                            associationIdToSnpIds.get(associationID).add(snpID);
                            associationIdToRiskAlleleNames.get(associationID).add(riskAllele);
                        }
                        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);
                    insertSnp.execute(snpArgs);
                    long snpID = insertSnp.executeAndReturnKey(snpArgs).longValue();
                    snpIdToRsIdMap.put(snpID, rsId);
                    if (!associationIdToSnpIds.containsKey(associationID)) {
                        associationIdToSnpIds.put(associationID, new ArrayList<>());
                        associationIdToRiskAlleleNames.put(associationID, new ArrayList<>());
                    }
                    if (!associationIdToSnpIds.get(associationID).contains(snpID)) {
                        // add the new associated gene
                        associationIdToSnpIds.get(associationID).add(snpID);
                        associationIdToRiskAlleleNames.get(associationID).add(riskAllele);
                    }
                }
            }
        }
        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 : associationIdToSnpIds.keySet()) {
        // get snp/risk allele pairs
        List<Long> snps = associationIdToSnpIds.get(associationID);
        List<String> riskAlleles = associationIdToRiskAlleleNames.get(associationID);
        if (snps.size() != riskAlleles.size()) {
            throw new RuntimeException("Mismatched SNP ID/Risk Allele name pairs for " + "association " + associationID + " (" + snps + ", " + riskAlleles + ")");
        } else {
            // iterate over each SNP and risk allele
            Iterator<Long> snpIterator = snps.iterator();
            Iterator<String> riskAlleleIterator = riskAlleles.iterator();
            while (riskAlleleIterator.hasNext()) {
                // create multiple LOCI, one per SNP/risk allele pair, and get the locus ID
                Map<String, Object> locusArgs = new HashMap<>();
                locusArgs.put("HAPLOTYPE_SNP_COUNT", null);
                String description = snps.size() == 2 ? "SNP x SNP interaction" : "SNP x SNP x SNP interaction";
                locusArgs.put("DESCRIPTION", description);
                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);
                Long snpID = snpIterator.next();
                String riskAlleleName = riskAlleleIterator.next();
                // now create a single RISK_ALLELE and get the risk allele ID
                Map<String, Object> riskAlleleArgs = new HashMap<>();
                riskAlleleArgs.put("RISK_ALLELE_NAME", riskAlleleName);
                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
                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 4 with SimpleJdbcInsert

use of org.springframework.jdbc.core.simple.SimpleJdbcInsert in project goci by EBISPOT.

the class V1_9_9_039__Proxy_snp_cleanup method migrate.

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    final Map<String, List<ProxySNP>> descriptionToProxyElements = new HashMap<>();
    final List<Long> riskAlleleIdsForRemoval = new ArrayList<>();
    final Map<Long, List<Long>> riskAlleleIdToProxySNPids = new HashMap<>();
    jdbcTemplate.query(SELECT_DATA_FOR_UPDATE, (resultSet, i) -> {
        long locus_id = resultSet.getLong(1);
        long risk_allele_id = resultSet.getLong(2);
        long snp_id = resultSet.getLong(3);
        String rs_id = resultSet.getString(4).trim();
        String migrated_description = resultSet.getString(5).trim();
        //create a map that links a migrated description to all the entries with that description
        if (!descriptionToProxyElements.containsKey(migrated_description)) {
            descriptionToProxyElements.put(migrated_description, new ArrayList<>());
        }
        descriptionToProxyElements.get(migrated_description).add(new ProxySNP(locus_id, risk_allele_id, snp_id, rs_id));
        return null;
    });
    Set<String> migratedDescriptions = descriptionToProxyElements.keySet();
    /*find the primary SNP (rsID same as rsID in description) and make it the risk allele, and make the
        other SNPs for this association proxy SNPs*/
    for (String description : migratedDescriptions) {
        List<ProxySNP> snps = descriptionToProxyElements.get(description);
        List<Long> snpIds = new ArrayList<>();
        long risk_allele_id = 0;
        for (ProxySNP snp : snps) {
            if (description.contains(snp.getRs_id())) {
                risk_allele_id = snp.getRisk_allele_id();
            } else {
                snpIds.add(snp.getSnp_id());
                riskAlleleIdsForRemoval.add(snp.getRisk_allele_id());
            }
        }
        if (risk_allele_id != 0) {
            riskAlleleIdToProxySNPids.put(risk_allele_id, snpIds);
        }
    }
    //delete proxy SNP entries from RISK_ALLELE, LOCUS_RISK_ALLELE and RISK_ALLELE_SNP using RISK_ALLELE_ID
    for (long risk_allele_id : riskAlleleIdsForRemoval) {
        jdbcTemplate.update(DELETE_FROM_RISK_ALLELE_SNP, risk_allele_id);
        jdbcTemplate.update(DELETE_FROM_LOCUS_RISK_ALLELE, risk_allele_id);
        jdbcTemplate.update(DELETE_FROM_RISK_ALLELE, risk_allele_id);
    }
    //insert new risk allele/SNP tuples into the RISK_ALLELE_PROXY_SNP
    SimpleJdbcInsert insertRiskAlleleProxySNP = new SimpleJdbcInsert(jdbcTemplate).withTableName("RISK_ALLELE_PROXY_SNP").usingColumns("RISK_ALLELE_ID", "SNP_ID");
    Set<Long> riskAlleles = riskAlleleIdToProxySNPids.keySet();
    for (long ra : riskAlleles) {
        List<Long> snps = riskAlleleIdToProxySNPids.get(ra);
        for (long snp : snps) {
            Map<String, Long> riskAlleleProxySNPargs = new HashMap<>();
            riskAlleleProxySNPargs.put("RISK_ALLELE_ID", ra);
            riskAlleleProxySNPargs.put("SNP_ID", snp);
            insertRiskAlleleProxySNP.execute(riskAlleleProxySNPargs);
        }
        //remove the haplotype count and new description for the primary risk allele in LOCUS
        jdbcTemplate.update(UPDATE_PROXY_SNP_MAIN_LOCUS, ra);
    }
}

Example 5 with SimpleJdbcInsert

use of org.springframework.jdbc.core.simple.SimpleJdbcInsert in project goci by EBISPOT.

the class V2_0_1_005__Populate_locations_and_snp_location method migrate.

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    // Go through list of potential locations
    jdbcTemplate.query(SELECT_LOCATION_DETAILS, (resultSet, i) -> {
        Long snpId = resultSet.getLong(1);
        String chromosomeName = resultSet.getString(2);
        String chromosomePosition = resultSet.getString(3);
        Long regionId = resultSet.getLong(4);
        // Insert into LOCATION table
        SimpleJdbcInsert insertLocation = new SimpleJdbcInsert(jdbcTemplate).withTableName("LOCATION").usingColumns("CHROMOSOME_NAME", "CHROMOSOME_POSITION", "REGION_ID").usingGeneratedKeyColumns("ID");
        Map<String, Object> locationArgs = new HashMap<>();
        locationArgs.put("CHROMOSOME_NAME", chromosomeName);
        locationArgs.put("CHROMOSOME_POSITION", chromosomePosition);
        locationArgs.put("REGION_ID", regionId);
        Number locationId = insertLocation.executeAndReturnKey(locationArgs);
        // Insert into SNP_LOCATION table
        SimpleJdbcInsert insertSnpLocation = new SimpleJdbcInsert(jdbcTemplate).withTableName("SNP_LOCATION").usingColumns("SNP_ID", "LOCATION_ID");
        Map<String, Object> snpLocationArgs = new HashMap<>();
        snpLocationArgs.put("SNP_ID", snpId);
        snpLocationArgs.put("LOCATION_ID", locationId.longValue());
        insertSnpLocation.execute(snpLocationArgs);
        return null;
    });
}