Example usage for org.springframework.jdbc.core JdbcTemplate JdbcTemplate

List of usage examples for org.springframework.jdbc.core JdbcTemplate JdbcTemplate

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.*
  5. JdbcTemplate
  6. JdbcTemplate

Introduction

In this page you can find the example usage for org.springframework.jdbc.core JdbcTemplate JdbcTemplate.

Prototype

public JdbcTemplate(DataSource dataSource)

Source Link

Document

Construct a new JdbcTemplate, given a DataSource to obtain connections from.

Usage

From source file:org.jasig.ssp.util.importer.job.staging.PostgresExternalTableUpsertWriter.java

@Override
public void write(List<? extends RawItem> items) throws Exception {

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    List<String> batchedStatements = new ArrayList<String>();

    String fileName = items.get(0).getResource().getFilename();
    String[] fileNameSplit = fileName.split("\\.");
    String tableName = fileNameSplit[0];

    Object batchStart = stepExecution.getExecutionContext().get("batchStart");
    Object batchStop = stepExecution.getExecutionContext().get("batchStop");

    RawItem item = items.get(0);/*from w  w w . java 2s .  c om*/
    if (currentResource == null) {
        this.orderedHeaders = writeHeader(items.get(0));
        this.currentResource = items.get(0).getResource();
    }
    Resource itemResource = item.getResource();
    if (!(this.currentResource.equals(itemResource))) {
        this.orderedHeaders = writeHeader(item);
        this.currentResource = itemResource;
    }
    StringBuilder updateSql = new StringBuilder();
    updateSql.append(" UPDATE " + tableName + " AS target SET ");
    for (String header : this.orderedHeaders) {
        updateSql.append(header + "=source." + header + ",");
    }
    updateSql.deleteCharAt(updateSql.lastIndexOf(","));
    updateSql.append(" FROM stg_" + tableName + " AS source WHERE ");
    List<String> tableKeys = metadataRepository.getRepository().getColumnMetadataRepository()
            .getTableMetadata(new TableReference(tableName)).getTableKeys();

    // There are a few external tables that don't (yet) have natural keys,
    // in these cases we've enforced the key on the staging table
    // so in cases where the external table does not have any keys, we look
    // towards the corresponding staging table for them
    if (tableKeys.isEmpty()) {
        tableKeys = metadataRepository.getRepository().getColumnMetadataRepository()
                .getTableMetadata(new TableReference("stg_" + tableName)).getTableKeys();
    }
    for (String key : tableKeys) {
        updateSql.append(" target." + key + " = source." + key + " AND ");
    }
    updateSql.append(" source.batch_id >= " + batchStart + " and source.batch_id <= " + batchStop + ";");
    batchedStatements.add(updateSql.toString());
    sayQuery(updateSql);

    StringBuilder insertSql = new StringBuilder();
    insertSql.append(" INSERT INTO " + tableName + "(");
    for (String header : this.orderedHeaders) {
        insertSql.append(header + ",");
    }
    insertSql.setLength(insertSql.length() - 1); // trim comma

    insertSql.append(") SELECT ");
    for (String header : this.orderedHeaders) {
        insertSql.append(" source." + header).append(",");
    }
    insertSql.setLength(insertSql.length() - 1); // trim comma
    insertSql.append(" FROM stg_" + tableName + " AS source ");
    insertSql.append(" LEFT OUTER JOIN " + tableName + " AS target ON ");
    for (String key : tableKeys) {
        insertSql.append(" source." + key + " = target." + key + " AND");
    }
    insertSql.setLength(insertSql.length() - 3); // trim comma
    insertSql.append(" WHERE ");
    for (String key : tableKeys) {
        insertSql.append(" target." + key + " IS NULL AND ");
    }
    insertSql.append(" source.batch_id >= " + batchStart + " and source.batch_id <= " + batchStop + "");

    batchedStatements.add(insertSql.toString());
    sayQuery(insertSql);
    try {
        int[] results = jdbcTemplate.batchUpdate(batchedStatements.toArray(new String[] {}));

        Integer numInsertedUpdated = (Integer) stepExecution.getExecutionContext().get("numInsertedUpdated");
        numInsertedUpdated = numInsertedUpdated == null ? 0 : numInsertedUpdated;
        numInsertedUpdated = numInsertedUpdated + results[0] + results[1];
        stepExecution.getExecutionContext().put("numInsertedUpdated", numInsertedUpdated);
    } catch (Exception e) {
        throw new NotSkippableException(e);
    }
}

From source file:edu.harvard.i2b2.crc.dao.setfinder.QueryResultInstanceSpringDao.java

public QueryResultInstanceSpringDao(DataSource dataSource, DataSourceLookup dataSourceLookup) {
    setDataSource(dataSource);/*w  w  w. ja v  a2  s. com*/
    setDbSchemaName(dataSourceLookup.getFullSchema());
    jdbcTemplate = new JdbcTemplate(dataSource);
    this.dataSourceLookup = dataSourceLookup;

    patientSetMapper = new PatientSetResultRowMapper();
}

From source file:com.dai.dao.TreinoDaoImpl.java

@Override
public void apagarTreino(Integer idTreino) {
    String sql = "delete from treino where idTreino=" + idTreino;
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.update(sql);//w ww.j a v a  2  s .  c  om

}

From source file:edu.harvard.i2b2.crc.dao.setfinder.PatientSetCollectionSpringDao.java

/**
 * Construc with patientset id Initialize hibernate session and creates
 * Query reuslt instance class// w w w .  j  a v  a  2s.  co m
 * 
 * @param patientSetId
 */
public PatientSetCollectionSpringDao(DataSource dataSource, DataSourceLookup dataSourceLookup) {
    setDataSource(dataSource);
    setDbSchemaName(dataSourceLookup.getFullSchema());
    jdbcTemplate = new JdbcTemplate(dataSource);
    this.dataSourceLookup = dataSourceLookup;
    if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
        insert_sql = "insert into " + getDbSchemaName()
                + "qt_patient_set_collection(patient_set_coll_id,result_instance_id,set_index,patient_num) values ("
                + getDbSchemaName() + "QT_SQ_QPR_PCID.nextval,?,?,?)";
    } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)
            || dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
        insert_sql = "insert into " + getDbSchemaName()
                + "qt_patient_set_collection(result_instance_id,set_index,patient_num) values (?,?,?)";
    }
    sqlServerSequenceDao = new SQLServerSequenceDAO(dataSource, dataSourceLookup);
    resultInstance = new QtQueryResultInstance();
    patientSetColl = new QtPatientSetCollection[INITIAL_ARRAY_SIZE];

}

From source file:com.dai.dao.JogadorEquipaAdversariaDaoImpl.java

@Override
public void alteraClubeJEA(int idJEA, int novoidEA) {
    String sql = "UPDATE jogadorEquipaAdversaria set equipaAdversaria_idEquipaAdversaria = ? where idJogadorEquipaAdversaria = "
            + idJEA;/*from  w ww.j  a  v  a2s. c om*/
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    jdbcTemplate.update(sql, new Object[] { novoidEA });

}

From source file:com.company.project.service.dao.UserDao.java

public int insertx(String username, String password) {
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRES_NEW);
    TransactionStatus status = txManager.getTransaction(def);
    int result = 0;
    try {/*from  www .jav  a  2s .  c  o  m*/
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        result = jdbcTemplate.update("insert into users (username, password) values (?, ?)", username,
                password);
        txManager.commit(status); // will not insert if this line is removed
    } catch (Exception e) {
        e.printStackTrace();
        txManager.rollback(status);
    }

    return result;
}

From source file:org.zenoss.zep.dao.impl.DaoCacheImpl.java

public DaoCacheImpl(DataSource ds, NestedTransactionService nestedTransactionService) {
    JdbcTemplate template = new JdbcTemplate(ds);
    this.eventClassCache = new DaoTableStringLruCache(template, nestedTransactionService, "event_class");
    this.eventClassKeyCache = new DaoTableStringLruCache(template, nestedTransactionService, "event_class_key");
    this.monitorCache = new DaoTableStringLruCache(template, nestedTransactionService, "monitor");
    this.agentCache = new DaoTableStringLruCache(template, nestedTransactionService, "agent");
    this.groupCache = new DaoTableStringLruCache(template, nestedTransactionService, "event_group");
    this.eventKeyCache = new DaoTableStringLruCache(template, nestedTransactionService, "event_key");
}

From source file:edu.harvard.i2b2.crc.dao.setfinder.EncounterSetCollectionSpringDao.java

/**
 * Construc with patientset id Initialize hibernate session and creates
 * Query reuslt instance class/* ww  w  .j  av a 2 s .  c  o m*/
 * 
 * @param patientSetId
 */
public EncounterSetCollectionSpringDao(DataSource dataSource, DataSourceLookup dataSourceLookup) {
    setDataSource(dataSource);
    setDbSchemaName(dataSourceLookup.getFullSchema());
    jdbcTemplate = new JdbcTemplate(dataSource);
    this.dataSourceLookup = dataSourceLookup;

    if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
        insert_sql = "insert into " + getDbSchemaName()
                + "qt_patient_enc_collection(patient_enc_coll_id,result_instance_id,set_index,patient_num,encounter_num) values ("
                + getDbSchemaName() + "QT_SQ_QPER_PECID.nextval,?,?,?,?)";
    } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)
            || dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRESQL)) {
        insert_sql = "insert into " + getDbSchemaName()
                + "qt_patient_enc_collection(result_instance_id,set_index,patient_num,encounter_num) values (?,?,?,?)";
    }
    sqlServerSequenceDao = new SQLServerSequenceDAO(dataSource, dataSourceLookup);
    resultInstance = new QtQueryResultInstance();
    patientEncColl = new QtPatientEncCollection[INITIAL_ARRAY_SIZE];

}

From source file:fm.lastify.config.LastifyFmWebappConfig.java

/**
 * Used to configure the in-memory HSQLDB database Remove this method if
 * different datasource is used/*from   www  .  j a  v a  2 s  .  c  o  m*/
 * 
 * @throws IOException
 */
@PostConstruct
public void createDatabaseTable() throws IOException {
    Resource resource = new ClassPathResource(
            "org/springframework/social/connect/jdbc/JdbcUsersConnectionRepository.sql");
    BufferedInputStream is = new BufferedInputStream(resource.getInputStream());
    final char[] buffer = new char[0x10000];
    StringBuilder out = new StringBuilder();
    Reader in = new InputStreamReader(resource.getInputStream(), "UTF-8");
    int read;
    do {
        read = in.read(buffer, 0, buffer.length);
        if (read > 0) {
            out.append(buffer, 0, read);
        }
    } while (read >= 0);

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.execute(out.toString());

}