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

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

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

Introduction

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

Prototype

@Override
    public void execute(final String sql) throws DataAccessException

Source Link

Usage

From source file:org.madsonic.service.SecurityService.java

/**
 * After instantiation, check if this is an old Subsonic instance, still
 * storing passwords in clear-text. If so, update to salted hashing.
 *//*from  www  .  j  a va  2s . c om*/
public void afterPropertiesSet() throws Exception {
    JdbcTemplate template = userDao.getJdbcTemplate();

    if (template.queryForInt("select count(*) from version where version = 160") == 0) {
        LOG.info("Updating database schema to version 160. (securing user passwords)");
        template.execute("insert into version values (160)");

        for (User user : getAllUsers()) {
            setSecurePassword(user);
            updateUser(user);
        }
    }
}

From source file:org.mskcc.cbio.importer.io.internal.DatabaseUtilsImpl.java

/**
 * Creates a database with the given name.
 *
 * @param dataSourceFactoryBean DataSourceFactoryBean
 * @param databaseName String/*from   w  w  w . j a va 2s  .c  o m*/
 * @param dropDatabase boolean
 * @return boolean
 */
private boolean createDatabase(DataSourceFactoryBean dataSourceFactoryBean, String databaseName,
        boolean dropDatabase) {

    boolean toReturn = true;

    // create simple JdbcTemplate if necessary
    DataSource dataSource = dataSourceFactoryBean.getDataSource("");
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    try {
        // drop if desired
        if (dropDatabase) {
            jdbcTemplate.execute("DROP DATABASE IF EXISTS " + databaseName);
        }
        // create
        jdbcTemplate.execute("CREATE DATABASE " + databaseName);
        if (LOG.isInfoEnabled()) {
            LOG.info("createDatabase(): " + databaseName + " successfully created.");
        }
    } catch (DataAccessException e) {
        LOG.error(e);
        toReturn = false;
    }

    // outta here
    return toReturn;
}

From source file:org.ojbc.util.camel.processor.audit.SQLLoggingProcessorTest.java

@Before
public void setup() throws Exception {
    assertNotNull(sqlLoggingProcessor);/* ww w  . j  a  v a2  s  .c o m*/
    JdbcTemplate t = sqlLoggingProcessor.getJdbcTemplate();
    assertNotNull(t);
    t.execute("delete from AuditLog");
    camelContext = new DefaultCamelContext();
    camelContext.setName(getClass().getName() + " CamelContext");
}

From source file:org.opendatakit.common.persistence.engine.mysql.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    final String uri = entity.getUri();
    StringBuilder b = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;//from   www .j  ava2 s .  c  o m
    // String tableName= TaskLockTable.TABLE_NAME;

    b.append("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("START TRANSACTION WITH CONSISTENT SNAPSHOT");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("SET @present := NOW(),");
    b.append(" @uriUser := '");
    b.append(user.getUriUser().replaceAll("'", "''"));
    b.append("',");
    b.append(" @uriLock := '");
    b.append(uri.replaceAll("'", "''"));
    b.append("',");
    b.append(" @formId := '");
    b.append(entity.getFormId().replaceAll("'", "''"));
    b.append("',");
    b.append(" @taskType := '");
    b.append(entity.getTaskType().replaceAll("'", "''"));
    b.append("',");
    b.append(" @lifetimeMicroseconds := ");
    b.append(1000L * l);
    stmts.add(b.toString());
    b.setLength(0);
    b.append("LOCK TABLES ");
    b.append(tableName);
    b.append(" WRITE ");
    stmts.add(b.toString());
    b.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record
        b.append("REPLACE INTO ");
        b.append(tableName);
        b.append(" (");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
        }
        first = true;
        b.append(") VALUES ( ");
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("@present");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append("@uriUser");
            } else if (f.equals(entity.formId)) {
                b.append("@formId");
            } else if (f.equals(entity.taskType)) {
                b.append("@taskType");
            } else if (f.equals(entity.primaryKey)) {
                b.append("@uriLock");
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(" DATE_ADD(CAST(@present AS DATETIME), INTERVAL @lifetimeMicroseconds MICROSECOND)");
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(")");
        stmts.add(b.toString());
        b.setLength(0);
    } else {
        // update existing record
        b.append("UPDATE ");
        b.append(tableName);
        b.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
            b.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("@present");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append("@uriUser");
            } else if (f.equals(entity.formId)) {
                b.append("@formId");
            } else if (f.equals(entity.taskType)) {
                b.append("@taskType");
            } else if (f.equals(entity.primaryKey)) {
                b.append("@uriLock");
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(" DATE_ADD(CAST(@present AS DATETIME), INTERVAL @lifetimeMicroseconds MICROSECOND)");
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(" WHERE ");
        b.append(K_BQ);
        b.append(entity.primaryKey.getName());
        b.append(K_BQ);
        b.append(" = ");
        b.append("@uriLock");
        stmts.add(b.toString());
        b.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ");
    b.append(tableName);
    b.append(" WHERE ");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" <= CAST(@present AS DATETIME)");
    stmts.add(b.toString());
    b.setLength(0);
    // determine the time of the oldest lock for this resource and task type...
    // do this by querying for the minimum expiration time
    // BUT, first, set the minimum time to the present time in case there are no locks.
    b.append("SET @minExpiration = @present");
    stmts.add(b.toString());
    b.setLength(0);
    dam.recordQueryUsage(TaskLockTable.TABLE_NAME, 1);
    b.append("SELECT @minExpiration := MIN(");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(") FROM ");
    b.append(tableName);
    b.append(" WHERE ");
    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");
    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType");
    stmts.add(b.toString());
    b.setLength(0);
    // determine if there are two or more records matching
    // the minimum expiration time.  If there are, we must
    // release the locks and retry, as we cannot determine
    // which one is first.
    b.append("SET @uriCount = 0");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("SELECT @uriCount := COUNT(");
    b.append(K_BQ);
    b.append(entity.primaryKey.getName());
    b.append(K_BQ);
    b.append(") FROM ");
    b.append(tableName);
    b.append(" WHERE ");

    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");

    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType AND ");

    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" = CAST(@minExpiration AS DATETIME)");
    stmts.add(b.toString());
    b.setLength(0);
    // delete all locks except the oldest one for this resource and task type...
    // or, if we have two or more old locks, release ours
    // whatever lock exists identifies the owner of the resource.
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ");
    b.append(tableName);
    b.append(" WHERE ");

    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");

    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType AND ( ");

    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" > CAST(@minExpiration AS DATETIME)");
    b.append(" OR ( @uriCount > 1 AND ");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" = CAST(@minExpiration AS DATETIME)");
    b.append(" AND ");
    b.append(K_BQ);
    b.append(entity.primaryKey.getName());
    b.append(K_BQ);
    b.append(" = @uriLock ) )");
    stmts.add(b.toString());
    b.setLength(0);
    // and within the transaction, see if the primary key of the winning lock is ours...
    b.append("SELECT ");
    b.append(K_BQ);
    b.append(entity.primaryKey.getName());
    b.append(K_BQ);
    b.append(" FROM ");
    b.append(tableName);
    b.append(" WHERE ");
    b.append(K_BQ);
    b.append(entity.formId.getName());
    b.append(K_BQ);
    b.append(" = @formId AND ");

    b.append(K_BQ);
    b.append(entity.taskType.getName());
    b.append(K_BQ);
    b.append(" = @taskType AND ");
    b.append(K_BQ);
    b.append(entity.expirationDateTime.getName());
    b.append(K_BQ);
    b.append(" = CAST(@minExpiration AS DATETIME)");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("UNLOCK TABLES");
    stmts.add(b.toString());
    b.setLength(0);
    b.append("COMMIT");
    stmts.add(b.toString());
    b.setLength(0);

    TaskLockTable relation;
    try {
        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (ODKDatastoreException e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }

    boolean success = false;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        Object o = jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean success = false;
                boolean oldValue = conn.getAutoCommit();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    String lastResult = null;
                    for (String s : stmts) {
                        Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
                                ResultSet.CONCUR_READ_ONLY, ResultSet.CLOSE_CURSORS_AT_COMMIT);
                        if (s.startsWith("SELECT")) {
                            ResultSet rs = stmt.executeQuery(s);
                            if (rs.first()) {
                                lastResult = rs.getString(1);
                            }
                            rs.close();
                        } else {
                            stmt.executeUpdate(s);
                        }
                        stmt.close();
                    }
                    conn.commit();
                    success = uri.equals(lastResult);
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                } finally {
                    if (!success) {
                        Statement stmt = conn.createStatement();
                        LogFactory.getLog(TaskLockImpl.class).info("UNLOCK TABLES");
                        stmt.execute("UNLOCK TABLES");
                        conn.commit();
                    }
                }
                conn.setAutoCommit(oldValue);
                return success ? uri : null;
            }

        });

        success = o != null && uri.equals((String) o);

    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    if (success) {
        return (TaskLockTable) datastore.getEntity(relation, uri, user);
    } else {
        throw new ODKEntityNotFoundException();
    }
}

From source file:org.opendatakit.common.persistence.engine.pgres.DatastoreImpl.java

/**
 * Relation manipulation APIs//from w w  w.j  a va  2  s  .c o m
 */
@Override
public void assertRelation(CommonFieldsBase relation, User user) throws ODKDatastoreException {
    JdbcTemplate jc = getJdbcConnection();
    TransactionStatus status = null;
    try {
        DefaultTransactionDefinition paramTransactionDefinition = new DefaultTransactionDefinition();

        // do serializable read on the information schema...
        paramTransactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_SERIALIZABLE);
        paramTransactionDefinition.setReadOnly(true);
        status = tm.getTransaction(paramTransactionDefinition);

        // see if relation already is defined and update it with dimensions...
        if (updateRelation(jc, relation, null)) {
            // it exists -- we're done!
            tm.commit(status);
            status = null;
            return;
        } else {
            tm.commit(status);
            // Try a new transaction to create the table
            paramTransactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_SERIALIZABLE);
            paramTransactionDefinition.setReadOnly(false);
            status = tm.getTransaction(paramTransactionDefinition);

            // need to create the table...
            StringBuilder b = new StringBuilder();
            b.append(K_CREATE_TABLE);
            b.append(K_BQ);
            b.append(relation.getSchemaName());
            b.append(K_BQ);
            b.append(".");
            b.append(K_BQ);
            b.append(relation.getTableName());
            b.append(K_BQ);
            b.append(K_OPEN_PAREN);
            boolean firstTime = true;
            for (DataField f : relation.getFieldList()) {
                if (!firstTime) {
                    b.append(K_CS);
                }
                firstTime = false;
                b.append(K_BQ);
                b.append(f.getName());
                b.append(K_BQ);
                DataField.DataType type = f.getDataType();
                switch (type) {
                case BINARY:
                    b.append(" BYTEA");
                    break;
                case LONG_STRING:
                    b.append(" TEXT");// b.append(" CHARACTER SET utf8");
                    break;
                case STRING:
                    b.append(" VARCHAR(");
                    Long len = f.getMaxCharLen();
                    if (len == null) {
                        len = PersistConsts.DEFAULT_MAX_STRING_LENGTH;
                    }
                    b.append(len.toString());
                    b.append(K_CLOSE_PAREN);
                    // b.append(" CHARACTER SET utf8");
                    break;
                case BOOLEAN:
                    b.append(" BOOLEAN");
                    break;
                case INTEGER:
                    Integer int_digits = f.getNumericPrecision();
                    if (int_digits == null) {
                        int_digits = DEFAULT_INT_NUMERIC_PRECISION;
                    }
                    Integer isSerial = f.getNumericScale();
                    // logger.warn("********************");
                    // logger.warn(int_digits + " ---" + isSerial);
                    if (int_digits.compareTo(9) > 0) {
                        if (isSerial != null && isSerial == 1) {
                            b.append(" SERIAL");
                            continue;
                            //    logger.warn("^^^^^^^^^^^^^^^^");
                        } else {
                            b.append(" BIGINT");
                        }
                    } else {
                        b.append(" INTEGER");
                    }
                    break;
                case DECIMAL:
                    Integer dbl_digits = f.getNumericPrecision();
                    Integer dbl_fract = f.getNumericScale();
                    if (dbl_digits == null) {
                        dbl_digits = DEFAULT_DBL_NUMERIC_PRECISION;
                    }
                    if (dbl_fract == null) {
                        dbl_fract = DEFAULT_DBL_NUMERIC_SCALE;
                    }
                    b.append(" DECIMAL(");
                    b.append(dbl_digits.toString());
                    b.append(K_CS);
                    b.append(dbl_fract.toString());
                    b.append(K_CLOSE_PAREN);
                    break;
                case DATETIME:
                    b.append(" TIMESTAMP WITHOUT TIME ZONE");
                    break;
                case URI:
                    b.append(" VARCHAR(");
                    len = f.getMaxCharLen();
                    if (len == null) {
                        len = PersistConsts.URI_STRING_LEN;
                    }
                    b.append(len.toString());
                    b.append(")");// b.append(" CHARACTER SET utf8");
                    break;
                }

                if (f == relation.primaryKey) {
                    b.append(" UNIQUE ");
                }
                if (f.getNullable()) {
                    b.append(" NULL ");
                } else {
                    b.append(" NOT NULL ");
                }
            }
            b.append(K_CLOSE_PAREN);

            String createTableStmt = b.toString();
            LogFactory.getLog(DatastoreImpl.class).info("Attempting: " + createTableStmt);

            jc.execute(createTableStmt);
            LogFactory.getLog(DatastoreImpl.class)
                    .info("create table success (before updateRelation): " + relation.getTableName());

            String idx;
            // create other indicies
            for (DataField f : relation.getFieldList()) {
                if ((f.getIndexable() != IndexType.NONE) && (f != relation.primaryKey)) {
                    idx = relation.getTableName() + "_" + shortPrefix(f.getName());
                    createIndex(jc, relation, idx, f);
                }
            }

            // and update the relation with actual dimensions...
            updateRelation(jc, relation, createTableStmt);
            tm.commit(status);
        }
    } catch (Exception e) {
        if (status != null) {
            tm.rollback(status);
        }
        throw new ODKDatastoreException(e);
    }
}

From source file:org.opendatakit.common.persistence.engine.pgres.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    String uri = entity.getUri();

    StringBuilder b = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;//from  ww w  . j av a2  s . c o  m

    b.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'");
    String uriUserInline = b.toString();
    b.setLength(0);
    b.append("'").append(uri.replaceAll("'", "''")).append("'");
    String uriLockInline = b.toString();
    b.setLength(0);
    b.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'");
    String formIdInline = b.toString();
    b.setLength(0);
    b.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'");
    String taskTypeInline = b.toString();
    b.setLength(0);
    b.append("interval '").append(l).append(" milliseconds'");
    String lifetimeIntervalMilliseconds = b.toString();
    b.setLength(0);

    b.append("LOCK TABLE ").append(tableName).append(" IN ACCESS EXCLUSIVE MODE");
    stmts.add(b.toString());
    b.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record (prospective lock)
        b.append("INSERT INTO ");
        b.append(tableName);
        b.append(" (");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
        }
        first = true;
        b.append(") VALUES ( ");
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("NOW()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                b.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                b.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                b.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(" NOW() + ");
                b.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(")");
        stmts.add(b.toString());
        b.setLength(0);
    } else {
        // update existing record (prospective lock)
        b.append("UPDATE ");
        b.append(tableName);
        b.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
            b.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("NOW()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                b.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                b.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                b.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(" NOW() + ");
                b.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(" WHERE ");
        b.append(K_BQ);
        b.append(entity.primaryKey.getName());
        b.append(K_BQ);
        b.append(" = ");
        b.append(uriLockInline);
        stmts.add(b.toString());
        b.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ").append(tableName).append(" WHERE ");
    b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= NOW()");
    stmts.add(b.toString());
    b.setLength(0);
    // delete prospective locks which are not the oldest for that resource and
    // task type
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ").append(tableName).append(" WHERE ");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    b.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(")");
    stmts.add(b.toString());
    b.setLength(0);
    // delete our entry if it collides with another entry with exactly 
    // this time.
    b.append("DELETE FROM ").append(tableName).append(" WHERE ");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ").append(uriLockInline)
            .append(" AND ");
    b.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(")");
    stmts.add(b.toString());
    b.setLength(0);
    // assert: only the lock that holds the resource for that task type appears
    // in the task lock table
    TaskLockTable relation;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean oldAutoCommitValue = conn.getAutoCommit();
                int oldTransactionValue = conn.getTransactionIsolation();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    Statement stmt = conn.createStatement();
                    for (String s : stmts) {
                        // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s);
                        stmt.execute(s);
                    }
                    conn.commit();
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                }
                conn.setTransactionIsolation(oldTransactionValue);
                conn.setAutoCommit(oldAutoCommitValue);
                return null;
            }

        });

        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user);
}

From source file:org.opendatakit.common.persistence.engine.sqlserver.DatastoreImpl.java

/**
 * Relation manipulation APIs/*from w w  w  .ja  v a 2 s. c o  m*/
 */
@Override
public void assertRelation(CommonFieldsBase relation, User user) throws ODKDatastoreException {
    JdbcTemplate jc = getJdbcConnection();
    TransactionStatus status = null;
    try {
        DefaultTransactionDefinition paramTransactionDefinition = new DefaultTransactionDefinition();

        // do serializable read on the information schema...
        paramTransactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_SERIALIZABLE);
        paramTransactionDefinition.setReadOnly(true);
        status = tm.getTransaction(paramTransactionDefinition);

        // see if relation already is defined and update it with dimensions...
        if (updateRelation(jc, relation, null)) {
            // it exists -- we're done!
            tm.commit(status);
            status = null;
            return;
        } else {
            tm.commit(status);
            // Try a new transaction to create the table
            paramTransactionDefinition.setIsolationLevel(DefaultTransactionDefinition.ISOLATION_SERIALIZABLE);
            paramTransactionDefinition.setReadOnly(false);
            status = tm.getTransaction(paramTransactionDefinition);

            // total number of columns must be less than MAX_BIND_PARAMS
            int countColumns = 0;
            // need to create the table...
            StringBuilder b = new StringBuilder();
            b.append(K_CREATE_TABLE);
            b.append(K_BQ);
            b.append(relation.getSchemaName());
            b.append(K_BQ);
            b.append(".");
            b.append(K_BQ);
            b.append(relation.getTableName());
            b.append(K_BQ);
            b.append(K_OPEN_PAREN);
            boolean firstTime = true;
            for (DataField f : relation.getFieldList()) {
                if (!firstTime) {
                    b.append(K_CS);
                }
                ++countColumns;
                firstTime = false;
                String nullClause;
                if (f.getNullable()) {
                    nullClause = K_NULL;
                } else {
                    nullClause = K_NOT_NULL;
                }

                b.append(K_BQ);
                b.append(f.getName());
                b.append(K_BQ);
                DataField.DataType type = f.getDataType();
                switch (type) {
                case BINARY:
                    b.append(" varbinary(max)").append(nullClause);
                    break;
                case LONG_STRING:
                    b.append(" nvarchar(max)").append(nullClause);
                    break;
                case STRING:
                    b.append(" nvarchar(");
                    Long len = f.getMaxCharLen();
                    if (len == null) {
                        len = PersistConsts.DEFAULT_MAX_STRING_LENGTH;
                    }
                    if (len > MAX_IN_ROW_NVARCHAR) {
                        // store value out-of-row
                        b.append("max");
                    } else {
                        b.append(len.toString());
                    }
                    b.append(K_CLOSE_PAREN).append(nullClause);
                    break;
                case BOOLEAN:
                    b.append(" bit").append(nullClause);
                    break;
                case INTEGER:
                    Integer int_digits = f.getNumericPrecision();
                    if (int_digits == null) {
                        int_digits = DEFAULT_INT_NUMERIC_PRECISION;
                    }

                    if (int_digits.compareTo(9) > 0) {
                        b.append(" bigint").append(nullClause);
                    } else {
                        b.append(" integer").append(nullClause);
                    }
                    break;
                case DECIMAL:
                    if (f == relation.primaryKey) {
                        throw new IllegalStateException("cannot use decimal columns as primary keys");
                    }

                    if (f.isDoublePrecision()) {
                        b.append(" float(53) ").append(nullClause);
                    } else {
                        Integer dbl_digits = f.getNumericPrecision();
                        Integer dbl_fract = f.getNumericScale();
                        if (dbl_digits == null) {
                            dbl_digits = DEFAULT_DBL_NUMERIC_PRECISION;
                        }
                        if (dbl_fract == null) {
                            dbl_fract = DEFAULT_DBL_NUMERIC_SCALE;
                        }
                        b.append(" decimal(");
                        b.append(dbl_digits.toString());
                        b.append(K_CS);
                        b.append(dbl_fract.toString());
                        b.append(K_CLOSE_PAREN).append(nullClause);
                    }
                    break;
                case DATETIME:
                    b.append(" datetime2(7)").append(nullClause);
                    break;
                case URI:
                    b.append(" nvarchar(");
                    len = f.getMaxCharLen();
                    if (len == null) {
                        len = PersistConsts.URI_STRING_LEN;
                    }
                    b.append(len.toString());
                    b.append(")").append(nullClause);
                    break;
                }

                if (f == relation.primaryKey) {
                    b.append(" PRIMARY KEY NONCLUSTERED ");
                }
            }
            b.append(K_CLOSE_PAREN);

            if (countColumns > MAX_BIND_PARAMS) {
                throw new IllegalArgumentException("Table size exceeds bind parameter limit");
            }

            String createTableStmt = b.toString();
            LogFactory.getLog(DatastoreImpl.class).info("Attempting: " + createTableStmt);

            jc.execute(createTableStmt);
            LogFactory.getLog(DatastoreImpl.class)
                    .info("create table success (before updateRelation): " + relation.getTableName());

            boolean alreadyClustered = false;
            String idx;
            // create other indicies
            for (DataField f : relation.getFieldList()) {
                if ((f.getIndexable() != IndexType.NONE) && (f != relation.primaryKey)) {
                    idx = relation.getTableName() + "_" + shortPrefix(f.getName());
                    alreadyClustered = createIndex(jc, relation, idx, f, alreadyClustered);
                }
            }

            // and update the relation with actual dimensions...
            updateRelation(jc, relation, createTableStmt);
            tm.commit(status);
        }
    } catch (Exception e) {
        if (status != null) {
            tm.rollback(status);
        }
        throw new ODKDatastoreException(e);
    }
}

From source file:org.opendatakit.common.persistence.engine.sqlserver.DatastoreImpl.java

private boolean createIndex(JdbcTemplate jc, CommonFieldsBase tbl, String idxName, DataField field,
        boolean alreadyClustered) {
    StringBuilder b = new StringBuilder();

    if (field.getDataType() == DataType.DECIMAL) {
        // don't allow this. It will conflict with our handling of special values.
        throw new IllegalStateException("Cannot index decimal fields");
    }// www.j  a va2s .c  om
    // the options are non-clustered or clustered. 
    // there can only be one clustered index per table. 
    if (field.getIndexable() == IndexType.HASH || alreadyClustered) {
        b.append(K_CREATE_NONCLUSTERED_INDEX);
    } else {
        b.append(K_CREATE_CLUSTERED_INDEX);
        alreadyClustered = true;
    }
    b.append(K_BQ);
    b.append(idxName);
    b.append(K_BQ);
    b.append(K_ON);
    b.append(K_BQ);
    b.append(tbl.getSchemaName());
    b.append(K_BQ);
    b.append(".");
    b.append(K_BQ);
    b.append(tbl.getTableName());
    b.append(K_BQ);
    b.append(" (");
    b.append(K_BQ);
    b.append(field.getName());
    b.append(K_BQ);
    b.append(" )");

    jc.execute(b.toString());
    return alreadyClustered;
}

From source file:org.opendatakit.common.persistence.engine.sqlserver.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    String uri = entity.getUri();

    StringBuilder b = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;/*from  w  w  w . j av  a2s .  c  o  m*/

    b.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'");
    String uriUserInline = b.toString();
    b.setLength(0);
    b.append("'").append(uri.replaceAll("'", "''")).append("'");
    String uriLockInline = b.toString();
    b.setLength(0);
    b.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'");
    String formIdInline = b.toString();
    b.setLength(0);
    b.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'");
    String taskTypeInline = b.toString();
    b.setLength(0);
    b.append("DATEADD(ms,").append(l).append(",SYSUTCDATETIME())");
    String nowPlusLifetimeIntervalMilliseconds = b.toString();
    b.setLength(0);

    b.append("SELECT  COUNT(1) FROM ").append(tableName).append(" WITH (TABLOCKX, HOLDLOCK)");
    stmts.add(b.toString());
    b.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record (prospective lock)
        b.append("INSERT INTO ");
        b.append(tableName);
        b.append(" (");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
        }
        first = true;
        b.append(") VALUES ( ");
        for (DataField f : entity.getFieldList()) {
            if (!first) {
                b.append(",");
            }
            first = false;
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("SYSUTCDATETIME()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                b.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                b.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                b.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(nowPlusLifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(")");
        stmts.add(b.toString());
        b.setLength(0);
    } else {
        // update existing record (prospective lock)
        b.append("UPDATE ");
        b.append(tableName);
        b.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                b.append(",");
            }
            first = false;
            b.append(K_BQ);
            b.append(f.getName());
            b.append(K_BQ);
            b.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                b.append("SYSUTCDATETIME()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                b.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                b.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                b.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                b.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                b.append(nowPlusLifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        b.append(" WHERE ");
        b.append(K_BQ);
        b.append(entity.primaryKey.getName());
        b.append(K_BQ);
        b.append(" = ");
        b.append(uriLockInline);
        stmts.add(b.toString());
        b.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ").append(tableName).append(" WHERE ");
    b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= SYSUTCDATETIME()");
    stmts.add(b.toString());
    b.setLength(0);
    // delete prospective locks which are not the oldest for that resource and
    // task type
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    b.append("DELETE FROM ").append(tableName).append(" WHERE ");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    b.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(")");
    stmts.add(b.toString());
    b.setLength(0);
    // delete our entry if it collides with another entry with exactly 
    // this time.
    b.append("DELETE FROM ").append(tableName).append(" WHERE ");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(" AND ");
    b.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ").append(uriLockInline)
            .append(" AND ");
    b.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    b.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    b.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    b.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ").append(taskTypeInline)
            .append(")");
    stmts.add(b.toString());
    b.setLength(0);
    // assert: only the lock that holds the resource for that task type appears
    // in the task lock table
    TaskLockTable relation;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean oldAutoCommitValue = conn.getAutoCommit();
                int oldTransactionValue = conn.getTransactionIsolation();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    Statement stmt = conn.createStatement();
                    for (String s : stmts) {
                        // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s);
                        stmt.execute(s);
                    }
                    conn.commit();
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                }
                conn.setTransactionIsolation(oldTransactionValue);
                conn.setAutoCommit(oldAutoCommitValue);
                return null;
            }

        });

        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user);
}

From source file:org.projectforge.database.DatabaseUpdateDao.java

/**
 * Without access checking.//from   w  w  w . j a v  a  2  s .c om
 * @see #fixDBHistoryEntries()
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public int internalFixDBHistoryEntries() {
    log.info("Fix all broken history entries (if exist).");
    final int counter[] = new int[1];
    counter[0] = 0;
    final JdbcTemplate jdbc = new JdbcTemplate(dataSource);
    try {
        String sql = " from t_history_property_delta where old_value like 'org.projectforge.%' or new_value like 'org.projectforge.%'";
        jdbc.query("select id, old_value, new_value, property_type" + sql, new ResultSetExtractor() {
            @Override
            public Object extractData(final ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next() == true) {
                    final int id = rs.getInt("ID");
                    final String oldValue = rs.getString("OLD_VALUE");
                    final String newValue = rs.getString("NEW_VALUE");
                    final Serializable oldId = getObjectId(oldValue);
                    final Serializable newId = getObjectId(newValue);
                    final String propertyType = rs.getString("PROPERTY_TYPE");
                    final int pos = propertyType.indexOf("_$$_javassist_");
                    final String newPropertyType;
                    if (pos > 0) {
                        newPropertyType = propertyType.substring(0, pos);
                    } else {
                        newPropertyType = null;
                    }
                    if (oldId == null && newId == null) {
                        continue;
                    }
                    final StringBuffer buf = new StringBuffer();
                    boolean first = true;
                    buf.append("update t_history_property_delta set ");
                    if (oldId != null) {
                        buf.append("OLD_VALUE='").append(oldId).append("'");
                        first = false;
                    }
                    if (newId != null) {
                        if (first == false) {
                            buf.append(", ");
                        } else {
                            first = false;
                        }
                        buf.append("NEW_VALUE='").append(newId).append("'");
                    }
                    if (newPropertyType != null) {
                        if (first == false) {
                            buf.append(", ");
                        } else {
                            first = false;
                        }
                        buf.append("PROPERTY_TYPE='").append(newPropertyType).append("'");
                    }
                    buf.append(" where ID=").append(id);
                    final String sql = buf.toString();
                    log.info(sql);
                    jdbc.execute(sql);
                    counter[0]++;
                }
                return null;
            }
        });
        int no = jdbc.queryForInt("select count(*)" + sql);
        if (no > 0) {
            log.warn("" + no + " of data base history entries aren't fixed.");
        }
        sql = " from t_history_property_delta where property_type like '%_$$_javassist_%'";
        jdbc.query("select id, property_type" + sql, new ResultSetExtractor() {
            @Override
            public Object extractData(final ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next() == true) {
                    final int id = rs.getInt("ID");
                    final String propertyType = rs.getString("PROPERTY_TYPE");
                    final int pos = propertyType.indexOf("_$$_javassist_");
                    if (pos < 0) {
                        log.error("Oups, should not occur.");
                        continue;
                    }
                    final String newPropertyType = propertyType.substring(0, pos);
                    final String sql = "update t_history_property_delta set PROPERTY_TYPE='" + newPropertyType
                            + "' where id=" + id;
                    log.info(sql);
                    jdbc.execute(sql);
                    counter[0]++;
                }
                return null;
            }
        });
        no = jdbc.queryForInt("select count(*)" + sql);
        if (no > 0) {
            log.warn("" + no + " of data base history entries aren't fixed.");
        }
    } catch (final Exception ex) {
        log.error(ex.getMessage(), ex);
        return 0;
    }
    return counter[0];
}