Example usage for java.sql Statement execute

List of usage examples for java.sql Statement execute

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. execute

Introduction

In this page you can find the example usage for java.sql Statement execute.

Prototype

boolean execute(String sql) throws SQLException;

Source Link

Document

Executes the given SQL statement, which may return multiple results.

Usage

From source file:com.alibaba.wasp.jdbc.TestPreparedStatement.java

public void testLobTempFiles() throws SQLException {
    Statement stat = conn.createStatement();
    stat.execute("CREATE TABLE TEST(ID INT PRIMARY KEY, DATA CLOB)");
    PreparedStatement prep = conn.prepareStatement("INSERT INTO TEST VALUES(?, ?)");
    for (int i = 0; i < 5; i++) {
        prep.setInt(1, i);//from   ww w .ja va 2s . c  o m
        if (i % 2 == 0) {
            prep.setCharacterStream(2, new StringReader(getString(i)), -1);
        }
        prep.execute();
    }
    ResultSet rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
    int check = 0;
    for (int i = 0; i < 5; i++) {
        assertTrue(rs.next());
        if (i % 2 == 0) {
            check = i;
        }
        assertEquals(getString(check), rs.getString(2));
    }
    assertFalse(rs.next());
    stat.execute("DELETE FROM TEST");
    for (int i = 0; i < 3; i++) {
        prep.setInt(1, i);
        prep.setCharacterStream(2, new StringReader(getString(i)), -1);
        prep.addBatch();
    }
    prep.executeBatch();
    rs = stat.executeQuery("SELECT * FROM TEST ORDER BY ID");
    for (int i = 0; i < 3; i++) {
        assertTrue(rs.next());
        assertEquals(getString(i), rs.getString(2));
    }
    assertFalse(rs.next());
    stat.execute("DROP TABLE TEST");
}

From source file:org.apache.hive.jdbc.TestActivePassiveHA.java

@Test(timeout = 60000)
public void testNoAuthZLlapClusterInfo() throws Exception {
    String instanceId1 = UUID.randomUUID().toString();
    miniHS2_1.start(getConfOverlay(instanceId1));
    Connection hs2Conn = getConnection(miniHS2_1.getJdbcURL(), "user1");
    boolean caughtException = false;
    Statement stmt = hs2Conn.createStatement();
    try {//from  w  w  w . j a  va 2 s  .  co  m
        stmt.execute("set hive.llap.daemon.service.hosts=@localhost");
        stmt.execute("llap cluster -info");
    } catch (SQLException e) {
        caughtException = true;
    } finally {
        stmt.close();
        hs2Conn.close();
    }
    assertEquals(false, caughtException);
}

From source file:org.opendatakit.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 stringBuilder = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;/* w  w w  .  ja v a  2  s.c  o m*/

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

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

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

private void openConnectionAndRunQuery(String jdbcUrl) throws Exception {
    hs2Conn = getConnection(jdbcUrl, System.getProperty("user.name"));
    String tableName = "testTab1";
    Statement stmt = hs2Conn.createStatement();
    // create table
    stmt.execute("DROP TABLE IF EXISTS " + tableName);
    stmt.execute("CREATE TABLE " + tableName
            + " (under_col INT COMMENT 'the under column', value STRING) COMMENT ' test table'");
    // load data//from  ww  w.ja va 2 s . co  m
    stmt.execute("load data local inpath '" + kvDataFilePath.toString() + "' into table " + tableName);
    ResultSet res = stmt.executeQuery("SELECT * FROM " + tableName);
    assertTrue(res.next());
    assertEquals("val_238", res.getString(2));
    res.close();
    stmt.close();
}

From source file:fr.jetoile.hadoopunit.integrationtest.IntegrationBootstrapTest.java

@Test
@Ignore//w w  w  .  j  ava 2s. c om
public void hiveServer2ShouldStart() throws InterruptedException, ClassNotFoundException, SQLException {

    //        assertThat(Utils.available("127.0.0.1", 20103)).isFalse();

    // Load the Hive JDBC driver
    LOGGER.info("HIVE: Loading the Hive JDBC Driver");
    Class.forName("org.apache.hive.jdbc.HiveDriver");

    //
    // Create an ORC table and describe it
    //
    // Get the connection
    Connection con = DriverManager
            .getConnection(
                    "jdbc:hive2://" + configuration.getString(HadoopUnitConfig.HIVE_SERVER2_HOSTNAME_KEY) + ":"
                            + configuration.getInt(HadoopUnitConfig.HIVE_SERVER2_PORT_KEY) + "/"
                            + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY),
                    "user", "pass");

    // Create the DB
    Statement stmt;
    try {
        String createDbDdl = "CREATE DATABASE IF NOT EXISTS "
                + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY);
        stmt = con.createStatement();
        LOGGER.info("HIVE: Running Create Database Statement: {}", createDbDdl);
        stmt.execute(createDbDdl);
    } catch (Exception e) {
        e.printStackTrace();
    }

    // Drop the table incase it still exists
    String dropDdl = "DROP TABLE " + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY) + "."
            + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY);
    stmt = con.createStatement();
    LOGGER.info("HIVE: Running Drop Table Statement: {}", dropDdl);
    stmt.execute(dropDdl);

    // Create the ORC table
    String createDdl = "CREATE TABLE IF NOT EXISTS "
            + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY) + "."
            + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY) + " (id INT, msg STRING) "
            + "PARTITIONED BY (dt STRING) " + "CLUSTERED BY (id) INTO 16 BUCKETS "
            + "STORED AS ORC tblproperties(\"orc.compress\"=\"NONE\")";
    stmt = con.createStatement();
    LOGGER.info("HIVE: Running Create Table Statement: {}", createDdl);
    stmt.execute(createDdl);

    // Issue a describe on the new table and display the output
    LOGGER.info("HIVE: Validating Table was Created: ");
    ResultSet resultSet = stmt.executeQuery(
            "DESCRIBE FORMATTED " + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY));
    int count = 0;
    while (resultSet.next()) {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
            System.out.print(resultSet.getString(i));
        }
        System.out.println();
        count++;
    }
    assertEquals(33, count);

    // Drop the table
    dropDdl = "DROP TABLE " + configuration.getString(HadoopUnitConfig.HIVE_TEST_DATABASE_NAME_KEY) + "."
            + configuration.getString(HadoopUnitConfig.HIVE_TEST_TABLE_NAME_KEY);
    stmt = con.createStatement();
    LOGGER.info("HIVE: Running Drop Table Statement: {}", dropDdl);
    stmt.execute(dropDdl);
}

From source file:com.generalbioinformatics.rdf.VirtuosoConnection.java

/** wrapper to execute a JDBC statement with tracing */
private boolean execute(Statement st, String sql) throws SQLException {
    log.trace(sql);/*  w w w .j a v a2 s.  c o  m*/
    return st.execute(sql);
}

From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java

/**
 * Test a simple resultset. This technically also demonstrates the upsert functionality
 * of Cassandra.//from w w w . j a va2s.  c  o  m
 */
@Test
public void testSimpleResultSet() throws Exception {
    Statement stmt = con.createStatement();

    // Create the target Column family
    String createCF = "CREATE COLUMNFAMILY t59 (k int PRIMARY KEY," + "c text " + ") ;";

    stmt.execute(createCF);
    stmt.close();
    con.close();

    // open it up again to see the new CF
    con = DriverManager
            .getConnection(String.format("jdbc:cassandra://%s:%d/%s?%s", HOST, PORT, KEYSPACE, OPTIONS));

    PreparedStatement statement = con.prepareStatement("update t59 set c=? where k=123",
            ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    statement.setString(1, "hello");
    statement.executeUpdate();

    ResultSet result = statement.executeQuery("SELECT * FROM t59;");

    System.out.println(resultToDisplay(result, 59, null));

}

From source file:com.nextep.designer.dbgm.services.impl.DerbyStorageService.java

@Override
public IStorageHandle createDataSetStorage(final IDataSet set, final boolean mirrored) {
    String name = set.getTable().getName();
    if (set.getReference() == null || set.getReference().isVolatile()) {
        // Building storage name
        final long time = System.currentTimeMillis();
        final int seed = (int) (Math.random() * 10);
        name = "NEXTEP_" + time + "_" + seed; //$NON-NLS-1$ //$NON-NLS-2$
    }/*from  ww  w. j av  a2  s  .  c  om*/

    // Creating table
    Connection conn = null;
    Statement stmt = null;
    try {
        conn = getLocalConnection();
        stmt = conn.createStatement();
        // First dropping any previous table storage
        final String dropTableStmt = "DROP TABLE " + escape(name); //$NON-NLS-1$
        try {
            stmt.execute(dropTableStmt);
        } catch (SQLException e) {
            // Ignoring, it will happen most of the times
        }
        // Creating local derby table
        final String createTableStmt = buildCreateDatasetTableStatement(name, set, mirrored);
        stmt.execute(createTableStmt);
        // Indexing local table
        final String indexTableStmt = buildCreateIndexStatement(name, set);
        stmt.execute(indexTableStmt);
        // Initializing storage handle
        final StorageHandle handle = new StorageHandle(name, set);
        final String insertStmt = buildInsertDatalineStatement(set, handle, mirrored);
        final String selectStmt = buildSelectDatalineStatement(set, handle, mirrored);
        // Configuring insert and select statements
        handle.setInsertStatement(insertStmt);
        handle.setSelectStatement(selectStmt);
        handle.setDisplayedColumnsCount(set.getColumnsRef().size());
        set.setStorageHandle(handle);
        return handle;
    } catch (SQLException e) {
        throw new ErrorException(
                DBGMMessages.getString("service.derbyStorage.createLocalDbFailed") + e.getMessage(), e); //$NON-NLS-1$
    } finally {
        safeClose(null, stmt, conn);
    }
}

From source file:com.micromux.cassandra.jdbc.JdbcRegressionTest.java

/**
 * Confirm that the connection can accept a different consistency level.
 *///from  ww  w. j  a va2  s .c om
@Test
public void testConsistencyLevel() throws Exception {
    Statement stmt = con.createStatement();

    // Create the target Column family
    String createCF = "CREATE COLUMNFAMILY t71 (k int PRIMARY KEY," + "c text " + ") ;";

    stmt.execute(createCF);
    stmt.close();
    con.close();

    // open it up again to see the new CF
    con = DriverManager.getConnection(String.format("jdbc:cassandra://%s:%d/%s?%s&consistency=%s", HOST, PORT,
            KEYSPACE, OPTIONS, CONSISTENCY_QUORUM));

    // at this point defaultConsistencyLevel should be set the QUORUM in the connection
    stmt = con.createStatement();

    ConsistencyLevel cl = statementExtras(stmt).getConsistencyLevel();
    assertTrue(ConsistencyLevel.QUORUM == cl);

    System.out.println();
    System.out.println("Test Issue #71");
    System.out.println("--------------");
    System.out.println("statement.consistencyLevel = " + cl);

}

From source file:com.atolcd.alfresco.repo.patch.SchemaUpgradeScriptPatch.java

/**
 * Execute the given SQL statement, absorbing exceptions that we expect
 * during schema creation or upgrade.//w  w  w  . j  ava  2s. com
 * 
 * @param fetchColumnName
 *            the name of the column value to return
 */
private Object executeStatement(Connection connection, String sql, String fetchColumnName, boolean optional,
        int line, File file) throws Exception {
    StringBuilder executedStatements = executedStatementsThreadLocal.get();
    if (executedStatements == null) {
        throw new IllegalArgumentException("The executedStatementsThreadLocal must be populated");
    }

    Statement stmt = connection.createStatement();
    Object ret = null;
    try {
        if (logger.isDebugEnabled()) {
            LogUtil.debug(logger, MSG_EXECUTING_STATEMENT, sql);
        }
        boolean haveResults = stmt.execute(sql);
        // Record the statement
        executedStatements.append(sql).append(";\n\n");
        if (haveResults && fetchColumnName != null) {
            ResultSet rs = stmt.getResultSet();
            if (rs.next()) {
                // Get the result value
                ret = rs.getObject(fetchColumnName);
            }
        }
    } catch (SQLException e) {
        if (optional) {
            // it was marked as optional, so we just ignore it
            LogUtil.debug(logger, MSG_OPTIONAL_STATEMENT_FAILED, sql, e.getMessage(), file.getAbsolutePath(),
                    line);
        } else {
            LogUtil.error(logger, ERR_STATEMENT_FAILED, sql, e.getMessage(), file.getAbsolutePath(), line);
            throw e;
        }
    } finally {
        try {
            stmt.close();
        } catch (Throwable e) {
        }
    }
    return ret;
}