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.micromux.cassandra.jdbc.CollectionsTest.java

/**
 * @throws java.lang.Exception//ww  w . j  a  va  2  s  .c o  m
 */
@BeforeClass
public static void setUpBeforeClass() throws Exception {
    // configure OPTIONS
    if (!StringUtils.isEmpty(TRUST_STORE)) {
        OPTIONS = String.format("trustStore=%s&trustPass=%s", URLEncoder.encode(TRUST_STORE), TRUST_PASS);
    }

    Class.forName("com.micromux.cassandra.jdbc.CassandraDriver");
    String URL = String.format("jdbc:cassandra://%s:%d/%s?%s&version=%s", HOST, PORT, SYSTEM, OPTIONS, CQLV3);

    con = DriverManager.getConnection(URL);

    if (LOG.isDebugEnabled())
        LOG.debug("URL         = '{}'", URL);

    Statement stmt = con.createStatement();

    // Use Keyspace
    String useKS = String.format("USE %s;", KEYSPACE);

    // Drop Keyspace
    String dropKS = String.format("DROP KEYSPACE %s;", KEYSPACE);

    try {
        stmt.execute(dropKS);
    } catch (Exception e) {
        /* Exception on DROP is OK */}

    // Create KeySpace
    String createKS = String.format(
            "CREATE KEYSPACE %s WITH replication = { 'class' : 'SimpleStrategy',  'replication_factor' : 1  };",
            KEYSPACE);
    //        String createKS = String.format("CREATE KEYSPACE %s WITH strategy_class = SimpleStrategy AND strategy_options:replication_factor = 1;",KEYSPACE);
    if (LOG.isDebugEnabled())
        LOG.debug("createKS    = '{}'", createKS);

    stmt = con.createStatement();
    stmt.execute("USE " + SYSTEM);
    stmt.execute(createKS);
    stmt.execute(useKS);

    // Create the target Table (CF)
    String createTable = "CREATE TABLE testcollection (" + " k int PRIMARY KEY," + " L list<bigint>,"
            + " M map<double, boolean>, M2 map<text, timestamp>, S set<text>" + ") ;";
    if (LOG.isDebugEnabled())
        LOG.debug("createTable = '{}'", createTable);

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

    // open it up again to see the new TABLE
    URL = String.format("jdbc:cassandra://%s:%d/%s?%s&version=%s", HOST, PORT, KEYSPACE, OPTIONS, CQLV3);
    con = DriverManager.getConnection(URL);
    if (LOG.isDebugEnabled())
        LOG.debug("URL         = '{}'", URL);

    Statement statement = con.createStatement();

    String insert = "INSERT INTO testcollection (k,L) VALUES( 1,[1, 3, 12345]);";
    statement.executeUpdate(insert);
    String update1 = "UPDATE testcollection SET S = {'red', 'white', 'blue'} WHERE k = 1;";
    String update2 = "UPDATE testcollection SET M = {2.0: true, 4.0: false, 6.0 : true} WHERE k = 1;";
    statement.executeUpdate(update1);
    statement.executeUpdate(update2);

    if (LOG.isDebugEnabled())
        LOG.debug("Unit Test: 'CollectionsTest' initialization complete.\n\n");
}

From source file:com.redhat.rhn.common.db.datasource.test.AdvDataSourceTest.java

protected static void oneTimeSetup() throws Exception {
    Session session = null;/*from w w  w. j  a  v  a2s  .  c  om*/
    Connection c = null;
    Statement stmt = null;
    try {
        session = HibernateFactory.getSession();
        c = session.connection();
        stmt = c.createStatement();
        stmt.executeQuery("select 1 from adv_datasource");
    } catch (SQLException e) {
        // Couldn't select 1, so the table didn't exist, create it
        stmt.execute("create table adv_datasource " + "( " + "  foobar VarChar2(32),"
                + "  test_column VarChar2(25)," + "  pin    number, " + "  id     number"
                + "         constraint adv_datasource_pk primary key" + ")");
        stmt.execute("insert into adv_datasource(foobar, id) " + "values ('Blarg', 1)");
        c.commit();
    } finally {
        HibernateHelper.cleanupDB(stmt);
    }
}

From source file:com.vertica.hadoop.VerticaOutputFormat.java

public static void checkOutputSpecs(VerticaConfiguration vtconfig) throws IOException {
    Relation vTable = new Relation(vtconfig.getOutputTableName());
    if (vTable.isNull())
        throw new IOException("Vertica output requires a table name defined by "
                + VerticaConfiguration.OUTPUT_TABLE_NAME_PROP);
    String[] def = vtconfig.getOutputTableDef();
    boolean dropTable = vtconfig.getDropTable();

    Statement stmt = null;
    try {//from   www .ja  va 2 s.co  m
        Connection conn = vtconfig.getConnection(true);
        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet rs = dbmd.getTables(null, vTable.getSchema(), vTable.getTable(), null);
        boolean tableExists = rs.next();

        stmt = conn.createStatement();

        if (tableExists && dropTable) {
            stmt = conn.createStatement();
            stmt.execute("TRUNCATE TABLE " + vTable.getQualifiedName().toString());
        }

        // create table if it doesn't exist
        if (!tableExists) {
            if (def == null)
                throw new RuntimeException("Table " + vTable.getQualifiedName().toString()
                        + " does not exist and no table definition provided");
            if (!vTable.isDefaultSchema()) {
                stmt.execute("CREATE SCHEMA IF NOT EXISTS " + vTable.getSchema());
            }
            StringBuffer tabledef = new StringBuffer("CREATE TABLE ")
                    .append(vTable.getQualifiedName().toString()).append(" (");
            for (String column : def)
                tabledef.append(column).append(",");
            tabledef.replace(tabledef.length() - 1, tabledef.length(), ")");
            stmt.execute(tabledef.toString());
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        if (stmt != null)
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
    }
}

From source file:com.vertica.hivestoragehandler.VerticaOutputFormat.java

public static void checkOutputSpecs(VerticaConfiguration vtconfig) throws IOException {
    VerticaRelation vTable = new VerticaRelation(vtconfig.getOutputTableName());
    if (vTable.isNull())
        throw new IOException("Vertica output requires a table name defined by "
                + VerticaConfiguration.OUTPUT_TABLE_NAME_PROP);
    String[] def = vtconfig.getOutputTableDef();
    boolean dropTable = vtconfig.getDropTable();

    Statement stmt = null;
    try {/*ww w  .  j av  a  2  s.  com*/
        Connection conn = vtconfig.getConnection(true);
        DatabaseMetaData dbmd = conn.getMetaData();
        ResultSet rs = dbmd.getTables(null, vTable.getSchema(), vTable.getTable(), null);
        boolean tableExists = rs.next();

        stmt = conn.createStatement();

        if (tableExists && dropTable) {
            stmt = conn.createStatement();
            stmt.execute("TRUNCATE TABLE " + vTable.getQualifiedName().toString());
        }

        // create table if it doesn't exist
        if (!tableExists) {
            if (def == null)
                throw new RuntimeException("Table " + vTable.getQualifiedName().toString()
                        + " does not exist and no table definition provided");
            if (!vTable.isDefaultSchema()) {
                stmt.execute("CREATE SCHEMA IF NOT EXISTS " + vTable.getSchema());
            }
            StringBuffer tabledef = new StringBuffer("CREATE TABLE ")
                    .append(vTable.getQualifiedName().toString()).append(" (");
            for (String column : def)
                tabledef.append(column).append(",");
            tabledef.replace(tabledef.length() - 1, tabledef.length(), ")");
            stmt.execute(tabledef.toString());
        }
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        if (stmt != null)
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
    }
}

From source file:com.quest.orahive.HiveJdbcClient.java

private static void initializeOracleSession(Connection connection, OraHiveOptions opts) {

    String sql = "";
    try {/*from  w  w  w .j ava  2s  .  co m*/
        sql = "begin \n" + "  dbms_application_info.set_module(module_name => '%s', action_name => '%s'); \n"
                + "end;";

        sql = String.format(sql, Constants.ORAHIVE_PRODUCT_NAME, getOracleTableName(opts));

        Statement statement = connection.createStatement();
        statement.execute(sql);
        statement.close();
    } catch (Exception ex) {
        LOG.error(String.format("An error occurred while attempting to execute "
                + "the following Oracle session-initialization statement:" + "\n%s" + "\nError:" + "\n%s", sql,
                ex.getMessage()));
    }
}

From source file:com.hangum.tadpole.engine.sql.util.QueryUtils.java

/**
 * execute query// w  ww.  j  a v a 2  s .  c  om
 * 
 * @param userDB
 * @param strQuery
 * @param intStartCnt
 * @param intSelectLimitCnt
 * @return
 * @throws Exception
 */
public static QueryExecuteResultDTO executeQuery(final UserDBDAO userDB, String strSQL, final int intStartCnt,
        final int intSelectLimitCnt) throws Exception {
    ResultSet resultSet = null;
    java.sql.Connection javaConn = null;
    Statement statement = null;

    strSQL = SQLUtil.makeExecutableSQL(userDB, strSQL);
    try {
        SqlMapClient client = TadpoleSQLManager.getInstance(userDB);
        javaConn = client.getDataSource().getConnection();
        statement = javaConn.createStatement();

        if (intStartCnt == 0) {
            statement.execute(strSQL);
            resultSet = statement.getResultSet();
        } else {
            strSQL = PartQueryUtil.makeSelect(userDB, strSQL, intStartCnt, intSelectLimitCnt);

            if (logger.isDebugEnabled())
                logger.debug("part sql called : " + strSQL);
            statement.execute(strSQL);
            resultSet = statement.getResultSet();
        }
        return new QueryExecuteResultDTO(userDB, false, resultSet, intSelectLimitCnt, intStartCnt);

    } catch (Exception e) {
        logger.error("execute query", e);
        throw e;
    } finally {
        if (statement != null)
            statement.close();
        if (resultSet != null)
            resultSet.close();
        if (javaConn != null)
            javaConn.close();
    }

}

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

@BeforeClass
public static void setUpBeforeClass() throws Exception {

    // configure OPTIONS
    if (!StringUtils.isEmpty(TRUST_STORE)) {
        OPTIONS = String.format("trustStore=%s&trustPass=%s", URLEncoder.encode(TRUST_STORE), TRUST_PASS);
    }/* ww w.ja  va  2  s .c  o m*/

    Class.forName("com.micromux.cassandra.jdbc.CassandraDriver");
    String URL = String.format("jdbc:cassandra://%s:%d/%s?%s", HOST, PORT, "system", OPTIONS);
    System.out.println("Connection URL = '" + URL + "'");

    con = DriverManager.getConnection(URL);
    Statement stmt = con.createStatement();

    // Drop Keyspace
    String dropKS = String.format("DROP KEYSPACE \"%s\";", KEYSPACE);

    try {
        stmt.execute(dropKS);
    } catch (Exception e) {
        /* Exception on DROP is OK */}

    // Create KeySpace
    String createKS = String.format(
            "CREATE KEYSPACE \"%s\" WITH replication = {'class': 'SimpleStrategy', 'replication_factor': 1};",
            KEYSPACE);
    System.out.println("createKS = '" + createKS + "'");
    stmt = con.createStatement();
    stmt.execute("USE system;");
    stmt.execute(createKS);

    // Use Keyspace
    String useKS = String.format("USE \"%s\";", KEYSPACE);
    stmt.execute(useKS);

    // Create the target Column family
    String createCF = "CREATE COLUMNFAMILY " + TABLE + " (keyname text PRIMARY KEY," + " bValue boolean,"
            + " iValue int" + ");";
    stmt.execute(createCF);

    //create an index
    stmt.execute("CREATE INDEX ON " + TABLE + " (iValue)");

    String createCF2 = "CREATE COLUMNFAMILY " + TYPETABLE + " ( " + " id uuid PRIMARY KEY, "
            + " blobValue blob," + " blobSetValue set<blob>," + " dataMapValue map<text,blob>,"
            + ") WITH comment = 'datatype TABLE in the Keyspace'" + ";";
    stmt.execute(createCF2);

    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));
    System.out.println(con);

}

From source file:com.mirth.connect.server.util.DatabaseUtil.java

public static void executeScript(List<String> script, boolean ignoreErrors) throws Exception {
    SqlSessionManager sqlSessionManger = SqlConfig.getSqlSessionManager();

    Connection conn = null;/*w  w  w . ja v a2s . c o  m*/
    ResultSet resultSet = null;
    Statement statement = null;

    try {
        sqlSessionManger.startManagedSession();
        conn = sqlSessionManger.getConnection();
        /*
         * Set auto commit to false or an exception will be thrown when trying to rollback
         */
        conn.setAutoCommit(false);
        statement = conn.createStatement();

        for (String statementString : script) {
            statementString = statementString.trim();
            if (statementString.length() > 0) {
                try {
                    statement.execute(statementString);
                    conn.commit();
                } catch (SQLException se) {
                    if (!ignoreErrors) {
                        throw se;
                    } else {
                        logger.error("Error was encountered and ignored while executing statement: "
                                + statementString, se);
                        conn.rollback();
                    }
                }
            }
        }

    } catch (Exception e) {
        throw new Exception(e);
    } finally {
        DbUtils.closeQuietly(statement);
        DbUtils.closeQuietly(resultSet);
        DbUtils.closeQuietly(conn);
        sqlSessionManger.close();
    }
}

From source file:com.datatorrent.contrib.hive.HiveMockTest.java

public static void hiveInitializeMapDatabase(HiveStore hiveStore) throws SQLException {
    hiveStore.connect();/*  www. j a va 2  s  .c o  m*/
    Statement stmt = hiveStore.getConnection().createStatement();
    // show tables
    String sql = "show tables";

    LOG.debug(sql);
    ResultSet res = stmt.executeQuery(sql);
    if (res.next()) {
        LOG.debug(res.getString(1));
    }

    stmt.execute("DROP TABLE " + tablemap);

    stmt.execute("CREATE TABLE IF NOT EXISTS " + tablemap
            + " (col1 map<string,int>) PARTITIONED BY(dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'  \n"
            + "MAP KEYS TERMINATED BY '" + delimiterMap + "' \n" + "STORED AS TEXTFILE ");
    hiveStore.disconnect();
}

From source file:com.datatorrent.contrib.hive.HiveMockTest.java

public static void hiveInitializePOJODatabase(HiveStore hiveStore) throws SQLException {
    hiveStore.connect();//from  w ww  . j a  v  a  2 s  .  co m
    Statement stmt = hiveStore.getConnection().createStatement();
    // show tables
    String sql = "show tables";

    LOG.debug(sql);
    ResultSet res = stmt.executeQuery(sql);
    if (res.next()) {
        LOG.debug("tables are {}", res.getString(1));
    }
    stmt.execute("DROP TABLE " + tablepojo);

    stmt.execute("CREATE TABLE " + tablepojo + " (col1 int) PARTITIONED BY(dt STRING) ROW FORMAT DELIMITED "
            + "FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' \n" + "STORED AS TEXTFILE ");
    hiveStore.disconnect();
}