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

Example 6 with SimpleJdbcInsert

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

the class V2_0_1_016__Populate_gene_entrez_gene_table method migrate.

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    // Query for gene IDs and its linked entrezGeneId
    jdbcTemplate.query(SELECT_GENE_ID_AND_ENTREZ_ID, (resultSet, i) -> {
        Long geneId = resultSet.getLong(1);
        String entrezIdinGeneTable = resultSet.getString(2);
        // Find ID of entrez gene in ENTREZ_GENE table linked to that ID
        Long idInEntrezGeneTable = jdbcTemplate.queryForObject(SELECT_ENTREZ_GENE_ID, Long.class, entrezIdinGeneTable);
        // Insert into new table
        SimpleJdbcInsert insertGeneEntrezGene = new SimpleJdbcInsert(jdbcTemplate).withTableName("GENE_ENTREZ_GENE").usingColumns("GENE_ID", "ENTREZ_GENE_ID");
        Map<String, Object> insertArgs = new HashMap<>();
        insertArgs.put("GENE_ID", geneId);
        insertArgs.put("ENTREZ_GENE_ID", idInEntrezGeneTable);
        insertGeneEntrezGene.execute(insertArgs);
        return null;
    });
}

Example 7 with SimpleJdbcInsert

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

the class V2_1_1_003__Split_platform_array_info method migrate.

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    IdAndStringRowHandler platformHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_PLATFORM, platformHandler);
    final Map<Long, String> studyIdToPlatform = platformHandler.getIdToStringMap();
    IdAndStringRowHandler manHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_MANUFACTURERS, manHandler);
    final Map<Long, String> manIdToMan = manHandler.getIdToStringMap();
    final Map<String, Long> manufacturers = new HashMap<>();
    for (Long id : manIdToMan.keySet()) {
        String man = manIdToMan.get(id);
        manufacturers.put(man, id);
    }
    List<String> qualifiers = new ArrayList<>();
    qualifiers.add("up to");
    qualifiers.add("at least");
    qualifiers.add("~");
    qualifiers.add(">");
    SimpleJdbcInsert insertStudyPlatform = new SimpleJdbcInsert(jdbcTemplate).withTableName("STUDY_PLATFORM").usingColumns("STUDY_ID", "PLATFORM_ID");
    for (Long studyId : studyIdToPlatform.keySet()) {
        String platform = studyIdToPlatform.get(studyId);
        if (platform != null) {
            List<String> manufacturer = new ArrayList<>();
            int imputed = 0;
            int pooled = 0;
            Integer snpCount = null;
            List<String> qualifier = new ArrayList<>();
            String qual = null;
            String studyDesignComment = null;
            if (platform.equals("NR")) {
                studyDesignComment = platform;
            } else {
                for (String man : manufacturers.keySet()) {
                    if (platform.contains(man)) {
                        manufacturer.add(man);
                    }
                }
                if (manufacturer.size() == 0) {
                    studyDesignComment = platform;
                }
                if (platform.contains("imputed")) {
                    imputed = 1;
                }
                if (platform.contains("pooled")) {
                    pooled = 1;
                }
                if (platform.contains("SNP") || platform.contains("unsure") || platform.contains("UNSURE") || platform.contains("CNV") || platform.contains("aplotype")) {
                    studyDesignComment = platform;
                } else {
                    if (platform.contains("[") && platform.indexOf("[") == platform.lastIndexOf("[")) {
                        int start = platform.indexOf("[") + 1;
                        int finish = platform.indexOf("]");
                        String count = platform.substring(start, finish).trim();
                        if (!count.equals("NR")) {
                            for (String q : qualifiers) {
                                if (count.contains(q)) {
                                    qualifier.add(q);
                                    count = count.replace(q, "").trim();
                                }
                            }
                            if (count.contains("million")) {
                                count = count.replace("million", "").trim();
                                if (count.contains(",")) {
                                    count = count.replace(",", "").trim();
                                }
                                double c = Double.parseDouble(count);
                                snpCount = (int) (c * 1000000);
                            } else if (count.contains(",") || count.contains(".")) {
                                count = count.replace(",", "").trim();
                                count = count.replace(".", "").trim();
                                snpCount = Integer.parseInt(count);
                            } else if (!count.equals("")) {
                                snpCount = Integer.parseInt(count);
                            } else {
                                studyDesignComment = platform;
                            }
                        }
                    } else {
                        studyDesignComment = platform;
                    }
                }
            }
            if (qualifier.size() > 1) {
                studyDesignComment = platform;
            } else if (qualifier.size() == 1) {
                qual = qualifier.get(0);
            }
            jdbcTemplate.update(UPDATE_STUDY_POOLED, pooled, snpCount, qual, imputed, studyDesignComment, studyId);
            for (String man : manufacturer) {
                Long pId = manufacturers.get(man);
                Map<String, Object> studyPlatformArgs = new HashMap<>();
                studyPlatformArgs.put("STUDY_ID", studyId);
                studyPlatformArgs.put("PLATFORM_ID", pId);
                insertStudyPlatform.execute(studyPlatformArgs);
            }
        }
    }
}

Example 8 with SimpleJdbcInsert

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

the class V2_2_0_028__Migrate_ancestral_groups method migrate.

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    IdAndStringRowHandler ancestralGroupHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_ANCESTRAL_GROUP, ancestralGroupHandler);
    final Map<Long, String> ancestryIdToAncestralGroup = ancestralGroupHandler.getIdToStringMap();
    IdAndStringRowHandler ancestralGroupNameHandler = new IdAndStringRowHandler();
    jdbcTemplate.query(SELECT_ANCESTRAL_GROUP_NAMES, ancestralGroupNameHandler);
    final Map<Long, String> agIdToAg = ancestralGroupNameHandler.getIdToStringMap();
    final Map<String, Long> ancestral_groups = new HashMap<>();
    for (Long id : agIdToAg.keySet()) {
        String ag = agIdToAg.get(id);
        ancestral_groups.put(ag, id);
    }
    SimpleJdbcInsert insertStudyAncestry = new SimpleJdbcInsert(jdbcTemplate).withTableName("ANCESTRY_ANCESTRAL_GROUP").usingColumns("ANCESTRY_ID", "ANCESTRAL_GROUP_ID");
    for (Long ancestryId : ancestryIdToAncestralGroup.keySet()) {
        String ancestry = ancestryIdToAncestralGroup.get(ancestryId);
        if (ancestry != null) {
            List<String> ags = new ArrayList<>();
            for (String ag : ancestral_groups.keySet()) {
                if (ancestry.contains(ag)) {
                    ags.add(ag);
                }
            }
            for (String ag : ags) {
                Long aId = ancestral_groups.get(ag);
                Map<String, Object> ancestryAncestralGroupArgs = new HashMap<>();
                ancestryAncestralGroupArgs.put("ANCESTRY_ID", ancestryId);
                ancestryAncestralGroupArgs.put("ANCESTRAL_GROUP_ID", aId);
                insertStudyAncestry.execute(ancestryAncestralGroupArgs);
            }
        }
    }
}

Example 9 with SimpleJdbcInsert

use of org.springframework.jdbc.core.simple.SimpleJdbcInsert in project sakuli by ConSol.

the class DaoTestCaseImpl method saveTestCaseResult.

@Override
public void saveTestCaseResult(final TestCase testCase) {
    LOGGER.info("Save results for test case \"" + testCase.getId() + "\"");
    //create a map for the sql parameters
    MapSqlParameterSource tcParameters = new MapSqlParameterSource();
    tcParameters.addValue("sakuli_suites_id", testSuite.getDbPrimaryKey());
    tcParameters.addValue("caseID", testCase.getId());
    tcParameters.addValue("result", testCase.getState().getErrorCode());
    tcParameters.addValue("result_desc", testCase.getState());
    tcParameters.addValue("name", testCase.getName());
    tcParameters.addValue("guid", testSuite.getGuid());
    tcParameters.addValue("start", testCase.getStartDateAsUnixTimestamp());
    tcParameters.addValue("stop", testCase.getStopDateAsUnixTimestamp());
    int warningTime = testCase.getWarningTime();
    tcParameters.addValue("warning", (warningTime != 0) ? warningTime : null);
    int criticalTime = testCase.getCriticalTime();
    tcParameters.addValue("critical", (criticalTime != 0) ? criticalTime : null);
    tcParameters.addValue("browser", testSuite.getBrowserInfo());
    tcParameters.addValue("lastpage", testCase.getLastURL());
    //try to save the screenshot
    tcParameters.addValue("screenshot", getScreenshotAsSqlLobValue(testCase), Types.BLOB);
    tcParameters.addValue("duration", testCase.getDuration());
    tcParameters.addValue("msg", testCase.getExceptionMessages(true));
    //generate the sql-statement
    SimpleJdbcInsert insertTCResults = new SimpleJdbcInsert(getDataSource()).withTableName("sakuli_cases").usingGeneratedKeyColumns("id");
    LOGGER.debug("write the following values to 'sakuli_cases': " + tcParameters.getValues() + " => now execute ....");
    int dbPrimaryKey = insertTCResults.executeAndReturnKey(tcParameters).intValue();
    LOGGER.info("test case '" + testCase.getId() + "' has been written to 'sahi_cases' with  primaryKey=" + dbPrimaryKey);
    testCase.setDbPrimaryKey(dbPrimaryKey);
}

Example 10 with SimpleJdbcInsert

use of org.springframework.jdbc.core.simple.SimpleJdbcInsert in project sakuli by ConSol.

the class DaoTestCaseStepImpl method saveTestCaseSteps.

@Override
public void saveTestCaseSteps(SortedSet<TestCaseStep> steps, int primaryKeyOfTestCase) {
    for (TestCaseStep step : steps) {
        LOGGER.info("============== save STEP \"" + step.getName() + "\" ==============");
        MapSqlParameterSource stepParameters = new MapSqlParameterSource();
        stepParameters.addValue("sakuli_cases_id", primaryKeyOfTestCase);
        stepParameters.addValue("result", step.getState().getErrorCode());
        stepParameters.addValue("result_desc", step.getState());
        stepParameters.addValue("name", step.getName());
        stepParameters.addValue("start", step.getStartDateAsUnixTimestamp());
        stepParameters.addValue("stop", step.getStopDateAsUnixTimestamp());
        int warningTime = step.getWarningTime();
        stepParameters.addValue("warning", (warningTime != 0) ? warningTime : null);
        stepParameters.addValue("duration", step.getDuration());
        LOGGER.debug("write the following values to 'sakuli_steps': " + stepParameters.getValues() + "\n now execute ....");
        //generate the sql-statement
        SimpleJdbcInsert insertStepResults = new SimpleJdbcInsert(getDataSource()).withTableName("sakuli_steps").usingGeneratedKeyColumns("id");
        //execute the sql-statement and save the primary key
        int dbPrimaryKey = insertStepResults.executeAndReturnKey(stepParameters).intValue();
        LOGGER.info("test case step '" + step.getName() + "' has been written to 'sakuli_steps' with  primaryKey=" + dbPrimaryKey);
        step.setDbPrimaryKey(dbPrimaryKey);
    }
}