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.ipcglobal.fredimportaws.TsvsToRedshift.java

/**
 * Creates the database./*w w  w .  j av a2 s  .co  m*/
 *
 * @param statement the statement
 * @throws Exception the exception
 */
private void createDatabase(Statement statement) throws Exception {
    // Create the database, just in case it doesn't already exist
    String databaseNameFred = properties.getProperty("databaseNameFred").trim();
    try {
        statement.execute("CREATE DATABASE " + databaseNameFred);
    } catch (PSQLException pe) {
        if (!"42P04".equals(pe.getSQLState()))
            throw pe; // 42P04 is "database already exists"
    }
}

From source file:com.streamsets.pipeline.stage.lib.hive.HiveQueryExecutor.java

public void executeAlterTableAddPartitionQuery(String qualifiedTableName,
        LinkedHashMap<String, String> partitionNameValueMap, Map<String, HiveType> partitionTypeMap,
        String partitionPath) throws StageException {
    String sql = buildPartitionAdditionQuery(qualifiedTableName, partitionNameValueMap, partitionTypeMap,
            partitionPath);/*from  www  .  j  ava  2 s . c  o m*/
    LOG.debug("Executing SQL:", sql);
    Statement statement = null;
    try (Connection con = DriverManager.getConnection(jdbcUrl)) {
        statement = con.createStatement();
        statement.execute(sql);
    } catch (SQLException e) {
        LOG.error("SQL Exception happened when adding partition", e);
        throw new StageException(Errors.HIVE_20, sql, e.getMessage());
    } finally {
        closeStatement(statement);
    }
}

From source file:com.nextep.datadesigner.sqlgen.impl.generator.DataSetGenerator.java

private ISQLScript buildScript(IDataSet set, IDataSet valuesSet, DeltaType type) {
    final ISQLScript script = CorePlugin.getTypedObjectFactory().create(ISQLScript.class);
    script.setScriptType(ScriptType.DATA);
    script.setName(type.name().toLowerCase() + "." + set.getName()); //$NON-NLS-1$
    final StringBuilder buf = new StringBuilder(2000);
    final ISQLParser parser = SQLGenPlugin.getService(IGenerationService.class).getCurrentSQLParser();
    // final IDataService dataService = DbgmPlugin.getService(IDataService.class);
    final IStorageService storageService = DbgmPlugin.getService(IStorageService.class);
    final IStorageHandle handle = valuesSet.getStorageHandle();
    if (handle != null) {
        Connection conn = null;/*w  w w  .j a v a  2s.  co  m*/
        Statement stmt = null;
        ResultSet rset = null;
        try {
            conn = storageService.getLocalConnection();
            stmt = conn.createStatement();
            stmt.execute(handle.getSelectStatement());
            rset = stmt.getResultSet();
            final ResultSetMetaData md = rset.getMetaData();
            while (rset.next()) {
                final List<Object> values = new LinkedList<Object>();
                for (int i = 1; i <= md.getColumnCount(); i++) {
                    values.add(rset.getObject(i));
                }
                switch (type) {
                case INSERT:
                    buf.append(buildInsert(parser, set, values));
                    break;
                case UPDATE:
                    buf.append(buildUpdate(parser, set, values));
                    break;
                case DELETE:
                    buf.append(buildDelete(parser, set, values));
                    break;
                }
            }
        } catch (SQLException e) {
            throw new ErrorException("Data generation problem: " + e.getMessage(), e);
        } finally {
            safeClose(rset, stmt, conn);
        }
    }
    if (buf.length() == 0) {
        return null;
    } else {
        script.appendSQL(buf.toString());
        return script;
    }
}

From source file:com.streamsets.pipeline.stage.lib.hive.HiveQueryExecutor.java

public void executeCreateTableQuery(String qualifiedTableName, LinkedHashMap<String, HiveType> columnTypeMap,
        LinkedHashMap<String, HiveType> partitionTypeMap, boolean useAsAvro, String schemaLocation,
        boolean isInternal) throws StageException {
    Utils.checkArgument((useAsAvro || schemaLocation != null),
            "Invalid configuration for table creation in use As Avro");

    String sql = useAsAvro//from www.java 2 s  . c  om
            ? buildCreateTableQueryNew(qualifiedTableName, columnTypeMap, partitionTypeMap, isInternal)
            : buildCreateTableQueryOld(qualifiedTableName, columnTypeMap, partitionTypeMap, schemaLocation,
                    isInternal);

    LOG.debug("Executing SQL:", sql);
    Statement statement = null;
    try (Connection con = DriverManager.getConnection(jdbcUrl)) {
        statement = con.createStatement();
        statement.execute(sql);
    } catch (SQLException e) {
        LOG.error("SQL Exception happened when creating table", e);
        throw new StageException(Errors.HIVE_20, sql, e.getMessage());
    } finally {
        closeStatement(statement);
    }
}

From source file:gda.jython.scriptcontroller.logging.LoggingScriptController.java

private void createTable() throws SQLException {
    if (tableExists()) {
        return;/*from ww w. j av  a 2 s  .  co m*/
    }

    // make SQL to create table
    String createString = "CREATE TABLE " + tableName + " ( " + PK_COLUMNNAME + " VARCHAR(50) PRIMARY KEY,";
    createString += SK_COLUMNNAME + " VARCHAR(80),";

    for (String columnName : columnGetters.values()) {
        columnName = columnName.replace(" ", "_");
        createString += columnName + " VARCHAR(150),";
    }
    createString += DATE_ADDED_COL_NAME + " TIMESTAMP," + DATE_UPDATED_COL_NAME + " TIMESTAMP)";

    // run the SQL
    Statement s = conn.createStatement();
    s.execute(createString);
    s.close();

}

From source file:com.redhat.lightblue.rest.crud.ITCaseCrudResourceRDBMSTest.java

@Test
public void testSelect()
        throws IOException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException,
        InstantiationException, IllegalAccessException, URISyntaxException, JSONException {
    try {//from w w w.ja v a  2  s.  c o m
        Context initCtx = new InitialContext();
        DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource");
        Connection conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(
                "CREATE TABLE Country ( name varchar(255), iso2code varchar(255), iso3code varchar(255) );");
        stmt.execute("INSERT INTO Country (name,iso2code,iso3code) VALUES ('a','CA','c');");
        stmt.close();
        conn.close();

        Assert.assertNotNull("CrudResource was not injected by the container", cutCrudResource);
        RestConfiguration.setDatasources(new DataSourcesConfiguration(
                JsonUtils.json(readConfigFile(RestConfiguration.DATASOURCE_FILENAME))));
        RestConfiguration.setFactory(new LightblueFactory(RestConfiguration.getDatasources()));

        String expectedCreated = readFile("expectedCreated.json");
        String metadata = readFile("metadata.json");
        EntityMetadata em = RestConfiguration.getFactory().getJSONParser()
                .parseEntityMetadata(JsonUtils.json(metadata));
        RestConfiguration.getFactory().getMetadata().createNewMetadata(em);
        EntityMetadata em2 = RestConfiguration.getFactory().getMetadata().getEntityMetadata("country", "1.0.0");
        String resultCreated = RestConfiguration.getFactory().getJSONParser().convert(em2).toString();
        JSONAssert.assertEquals(expectedCreated, resultCreated, false);

        String expectedFound = readFile("expectedFound.json");
        String resultFound = cutCrudResource.find("country", "1.0.0", readFile("resultFound.json")).getEntity()
                .toString();
        // TODO / NOTE we can change the result format if needed, now it return an array of arrays
        //System.err.println("!!!!!!!!!!!!!!!!!" + resultFound);
        JSONAssert.assertEquals(expectedFound, resultFound, false);
    } catch (NamingException | SQLException ex) {
        throw new IllegalStateException(ex);
    }
    mongo.dropDatabase(DB_NAME);
}

From source file:com.redhat.lightblue.rest.crud.ITCaseCrudResourceRDBMSTest.java

@Test
public void testSelectAll()
        throws IOException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException,
        InstantiationException, IllegalAccessException, URISyntaxException, JSONException {
    try {/*from   w  ww.  java2s . c om*/
        Context initCtx = new InitialContext();
        DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource");
        Connection conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(
                "CREATE TABLE Country ( name varchar(255), iso2code varchar(255), iso3code varchar(255) );");
        stmt.execute("INSERT INTO Country (name,iso2code,iso3code) VALUES ('a','CA','c');");
        stmt.close();
        conn.close();

        Assert.assertNotNull("CrudResource was not injected by the container", cutCrudResource);
        RestConfiguration.setDatasources(new DataSourcesConfiguration(
                JsonUtils.json(readConfigFile(RestConfiguration.DATASOURCE_FILENAME))));
        RestConfiguration.setFactory(new LightblueFactory(RestConfiguration.getDatasources()));

        String expectedCreated = readFile("expectedCreated.json");
        String metadata = readFile("metadata.json");
        EntityMetadata em = RestConfiguration.getFactory().getJSONParser()
                .parseEntityMetadata(JsonUtils.json(metadata));
        RestConfiguration.getFactory().getMetadata().createNewMetadata(em);
        EntityMetadata em2 = RestConfiguration.getFactory().getMetadata().getEntityMetadata("country", "1.0.0");
        String resultCreated = RestConfiguration.getFactory().getJSONParser().convert(em2).toString();
        JSONAssert.assertEquals(expectedCreated, resultCreated, false);

        String expectedFound = readFile("expectedFoundAll.json");
        String resultFound = cutCrudResource.find("country", "1.0.0", readFile("resultFoundAll.json"))
                .getEntity().toString();
        // TODO / NOTE we can change the result format if needed, now it return an array of arrays
        //System.err.println("!!!!!!!!!!!!!!!!!" + resultFound);
        JSONAssert.assertEquals(expectedFound, resultFound, false);
    } catch (NamingException | SQLException ex) {
        throw new IllegalStateException(ex);
    }
    mongo.dropDatabase(DB_NAME);
}

From source file:com.redhat.lightblue.rest.crud.ITCaseCrudResourceRDBMSTest.java

@Test
public void testDelete()
        throws IOException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException,
        InstantiationException, IllegalAccessException, URISyntaxException, JSONException {
    try {// w  w w  .  j ava2  s . c  o  m
        Context initCtx = new InitialContext();
        DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource");
        Connection conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(
                "CREATE TABLE Country ( name varchar(255), iso2code varchar(255), iso3code varchar(255) );");
        stmt.execute("INSERT INTO Country (name,iso2code,iso3code) VALUES ('a','CA','c');");
        stmt.close();
        conn.close();

        Assert.assertNotNull("CrudResource was not injected by the container", cutCrudResource);
        RestConfiguration.setDatasources(new DataSourcesConfiguration(
                JsonUtils.json(readConfigFile(RestConfiguration.DATASOURCE_FILENAME))));
        RestConfiguration.setFactory(new LightblueFactory(RestConfiguration.getDatasources()));

        String expectedCreated = readFile("expectedCreated.json");
        String metadata = readFile("metadata.json").replaceAll("YYZ",
                " DELETE FROM Country WHERE ISO2CODE=:ISO2CODE;");
        EntityMetadata em = RestConfiguration.getFactory().getJSONParser()
                .parseEntityMetadata(JsonUtils.json(metadata));
        RestConfiguration.getFactory().getMetadata().createNewMetadata(em);
        EntityMetadata em2 = RestConfiguration.getFactory().getMetadata().getEntityMetadata("country", "1.0.0");
        String resultCreated = RestConfiguration.getFactory().getJSONParser().convert(em2).toString();
        JSONAssert.assertEquals(expectedCreated, resultCreated, false);

        String expectedDeleted = readFile("expectedDeleted.json");
        String resultDeleted = cutCrudResource.delete("country", "1.0.0", readFile("resultDeleted.json"))
                .getEntity().toString();
        //System.err.println("!!!!!!!!!!!!!!!!!" + resultDeleted);

        ds = (DataSource) initCtx.lookup("java:/mydatasource");
        conn = ds.getConnection();
        stmt = conn.createStatement();
        stmt.execute("SELECT * FROM Country;");
        ResultSet resultSet = stmt.getResultSet();

        Assert.assertEquals(false, resultSet.next());

        JSONAssert.assertEquals(expectedDeleted, resultDeleted, false);
    } catch (NamingException | SQLException ex) {
        throw new IllegalStateException(ex);
    }
    mongo.dropDatabase(DB_NAME);
}

From source file:com.redhat.lightblue.rest.crud.ITCaseCrudResourceRDBMSTest.java

@Test
public void testInsert()
        throws IOException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException,
        InstantiationException, IllegalAccessException, URISyntaxException, JSONException {
    try {//ww  w . j  a  va  2s. c  o  m
        Context initCtx = new InitialContext();
        DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource");
        Connection conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(
                "CREATE TABLE Country ( name varchar(255), iso2code varchar(255), iso3code varchar(255) );");
        stmt.close();
        conn.close();

        Assert.assertNotNull("CrudResource was not injected by the container", cutCrudResource);
        RestConfiguration.setDatasources(new DataSourcesConfiguration(
                JsonUtils.json(readConfigFile(RestConfiguration.DATASOURCE_FILENAME))));
        RestConfiguration.setFactory(new LightblueFactory(RestConfiguration.getDatasources()));

        String expectedCreated = readFile("expectedCreated.json");
        String metadata = readFile("metadata.json").replaceAll("XXY",
                "INSERT INTO Country (NAME,ISO2CODE,ISO3CODE) VALUES (:name,:iso2code,:iso3code);");
        EntityMetadata em = RestConfiguration.getFactory().getJSONParser()
                .parseEntityMetadata(JsonUtils.json(metadata));
        RestConfiguration.getFactory().getMetadata().createNewMetadata(em);
        EntityMetadata em2 = RestConfiguration.getFactory().getMetadata().getEntityMetadata("country", "1.0.0");
        String resultCreated = RestConfiguration.getFactory().getJSONParser().convert(em2).toString();
        JSONAssert.assertEquals(expectedCreated, resultCreated, false);

        String expectedInserted = readFile("expectedInserted.json");
        String resultInserted = cutCrudResource.insert("country", "1.0.0", readFile("resultInserted.json"))
                .getEntity().toString();
        System.err.println("!!!!!!!!!!!!!!!!!" + resultInserted);

        ds = (DataSource) initCtx.lookup("java:/mydatasource");
        conn = ds.getConnection();
        stmt = conn.createStatement();
        stmt.execute("SELECT * FROM Country;");
        ResultSet resultSet = stmt.getResultSet();
        resultSet.next();
        Assert.assertEquals("Canad", resultSet.getString("name"));
        Assert.assertEquals("CA", resultSet.getString("iso2code"));
        Assert.assertEquals("CAN", resultSet.getString("iso3code"));

        JSONAssert.assertEquals(expectedInserted, resultInserted, false);
    } catch (NamingException ex) {
        throw new IllegalStateException(ex);
    } catch (SQLException ex) {
        throw new IllegalStateException(ex);
    }
}

From source file:com.redhat.lightblue.rest.crud.ITCaseCrudResourceRDBMSTest.java

@Test
public void testUpdate()
        throws IOException, ClassNotFoundException, NoSuchMethodException, InvocationTargetException,
        InstantiationException, IllegalAccessException, URISyntaxException, JSONException {
    try {//from   www .  ja v a 2s  .co m
        Context initCtx = new InitialContext();
        DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource");
        Connection conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute(
                "CREATE TABLE Country ( name varchar(255), iso2code varchar(255), iso3code varchar(255) );");
        stmt.execute("INSERT INTO Country (name,iso2code,iso3code) VALUES ('a','CA','c');");
        stmt.close();
        conn.close();

        Assert.assertNotNull("CrudResource was not injected by the container", cutCrudResource);
        RestConfiguration.setDatasources(new DataSourcesConfiguration(
                JsonUtils.json(readConfigFile(RestConfiguration.DATASOURCE_FILENAME))));
        RestConfiguration.setFactory(new LightblueFactory(RestConfiguration.getDatasources()));

        String expectedCreated = readFile("expectedCreated.json");
        String metadata = readFile("metadata.json").replaceAll("ZZY",
                " UPDATE Country SET NAME=:name  WHERE ISO2CODE=:ISO2CODE;");
        EntityMetadata em = RestConfiguration.getFactory().getJSONParser()
                .parseEntityMetadata(JsonUtils.json(metadata));
        RestConfiguration.getFactory().getMetadata().createNewMetadata(em);
        EntityMetadata em2 = RestConfiguration.getFactory().getMetadata().getEntityMetadata("country", "1.0.0");
        String resultCreated = RestConfiguration.getFactory().getJSONParser().convert(em2).toString();
        JSONAssert.assertEquals(expectedCreated, resultCreated, false);

        String expectedUpdated = readFile("expectedUpdated.json");
        String resultUpdated = cutCrudResource.update("country", "1.0.0", readFile("resultUpdated.json"))
                .getEntity().toString();
        System.err.println("!!!!!!!!!!!!!!!!!" + resultUpdated);

        ds = (DataSource) initCtx.lookup("java:/mydatasource");
        conn = ds.getConnection();
        stmt = conn.createStatement();
        stmt.execute("SELECT * FROM Country;");
        ResultSet resultSet = stmt.getResultSet();
        resultSet.next();
        Assert.assertEquals("Canada", resultSet.getString("name"));
        Assert.assertEquals("CA", resultSet.getString("iso2code"));
        Assert.assertEquals("c", resultSet.getString("iso3code"));

        JSONAssert.assertEquals(expectedUpdated, resultUpdated, false);
    } catch (NamingException | SQLException ex) {
        throw new IllegalStateException(ex);
    }
    mongo.dropDatabase(DB_NAME);
}