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

/**
 * Copy s3 files to redshift table.//from w w w.j  av  a 2  s.  c  o m
 *
 * @throws Exception the exception
 */
private void copyS3FilesToRedshiftTable() throws Exception {
    GetSessionTokenRequest getSessionTokenRequest = new GetSessionTokenRequest();
    GetSessionTokenResult getSessionTokenResult = stsClient.getSessionToken(getSessionTokenRequest);
    Credentials credentialsToken = getSessionTokenResult.getCredentials();
    String jdbcRedshiftUrl = properties.getProperty("jdbcRedshiftUrl");
    String jdbcRedshiftDriverClass = properties.getProperty("jdbcRedshiftDriverClass");
    String jdbcRedshiftLogin = properties.getProperty("jdbcRedshiftLogin");
    String jdbcRedshiftPassword = properties.getProperty("jdbcRedshiftPassword");

    Class.forName(jdbcRedshiftDriverClass);
    Connection con = null;
    Statement statement = null;

    try {
        String tableName = properties.getProperty("tableNameFred").trim();
        con = DriverManager.getConnection(jdbcRedshiftUrl, jdbcRedshiftLogin, jdbcRedshiftPassword);
        statement = con.createStatement();
        createDatabase(statement); // just in case...
        // Drop/Create table (more efficient than deleting all of the rows)
        dropTable(statement, tableName);
        statement.execute(createTableStatement(tableName));

        long beforeCopy = System.currentTimeMillis();
        String s3SourceBucketPrefix = "s3://" + awsBucketName + "/" + awsBucketTsvPrefix + "/";
        String s3Copy = "copy " + tableName + " from '" + s3SourceBucketPrefix + "' "
                + "CREDENTIALS 'aws_access_key_id=" + credentialsToken.getAccessKeyId().replace("\\", "\\\\")
                + ";" + "aws_secret_access_key=" + credentialsToken.getSecretAccessKey().replace("\\", "\\\\")
                + ";" + "token=" + credentialsToken.getSessionToken().replace("\\", "\\\\") + "' "
                + "delimiter '\\t' gzip";
        statement.executeUpdate(s3Copy);

    } catch (Exception e) {
        log.error(e);
        throw e;
    } finally {
        try {
            if (statement != null && !statement.isClosed())
                statement.close();
        } catch (Exception e) {
            log.warn("Exception closing statement: " + e.getMessage());
        }

        try {
            if (con != null && !con.isClosed())
                con.close();
        } catch (Exception e) {
            log.warn("Exception closing connection: " + e.getMessage());
        }
    }
}

From source file:com.frameworkset.commons.dbcp2.PoolableConnectionFactory.java

protected void initializeConnection(Connection conn) throws SQLException {
    Collection<String> sqls = _connectionInitSqls;
    if (conn.isClosed()) {
        throw new SQLException("initializeConnection: connection closed");
    }/*from  www .j a  va2 s.  com*/
    if (null != sqls) {
        Statement stmt = null;
        SQLException localThrowable2 = null;
        try {
            stmt = conn.createStatement();
            for (String sql : sqls) {
                if (sql == null) {
                    throw new NullPointerException("null connectionInitSqls element");
                }
                stmt.execute(sql);
            }
        } catch (SQLException localThrowable1) {
            localThrowable2 = localThrowable1;
            throw localThrowable1;
        } catch (Throwable localThrowable1) {

            SQLException localThrowable1_ = new SQLException(localThrowable1);
            localThrowable2 = localThrowable1_;
            throw localThrowable1_;
        } finally {
            if (stmt != null) {
                if (localThrowable2 != null) {
                    try {
                        stmt.close();
                    } catch (Throwable x2) {
                        localThrowable2.addSuppressed(x2);
                    }
                } else {
                    stmt.close();
                }
            }
        }
    }
}

From source file:com.zotoh.core.db.JDBCPool.java

/**
 * @param conn/*from  w  w w  .j a  v  a2 s  .  co  m*/
 * @return
 */
public boolean isBadConnection(Connection conn) {

    DBVendor v = getVendor();
    Statement stmt = null;
    String sql = null;

    if (conn != null)
        try {

            if (ORACLE.equals(v)) {
                sql = "select count(*) from user_tables";
            }
            if (SQLSERVER.equals(v)) {
                sql = "select count(*) from sysusers";
            }
            if (DB2.equals(v)) {
                sql = "select count(*) from sysibm.systables";
            }

            if (sql != null) {
                stmt = conn.createStatement();
                stmt.execute(sql);
            }
        } catch (Exception e) {
            return true;
        } finally {
            DBUte.safeClose(stmt);
        }

    return false;
}

From source file:com.taobao.datax.plugins.writer.oraclejdbcwriter.OracleJdbcWriter.java

@Override
public int post(PluginParam param) {
    if (StringUtils.isBlank(this.post))
        return PluginStatus.SUCCESS.value();

    Statement stmt = null;
    try {/*  w  w w . j  a  va  2s.  com*/
        this.connection = DBSource.getConnection(this.sourceUniqKey);

        stmt = this.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

        for (String subSql : this.post.split(";")) {
            this.logger.info(String.format("Excute prepare sql %s .", subSql));
            stmt.execute(subSql);
        }

        return PluginStatus.SUCCESS.value();
    } catch (Exception e) {
        e.printStackTrace();
        throw new DataExchangeException(e.getCause());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
            if (null != this.connection) {
                this.connection.close();
                this.connection = null;
            }
        } catch (Exception e2) {
        }

    }
}

From source file:com.taobao.datax.plugins.writer.oraclejdbcwriter.OracleJdbcWriter.java

@Override
public int prepare(PluginParam param) {
    this.setParam(param);

    DBSource.register(this.sourceUniqKey, this.genProperties());

    if (StringUtils.isBlank(this.pre))
        return PluginStatus.SUCCESS.value();

    Statement stmt = null;
    try {//from www . ja va  2s.c o  m
        this.connection = DBSource.getConnection(this.sourceUniqKey);

        stmt = this.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

        for (String subSql : this.pre.split(";")) {
            this.logger.info(String.format("Excute prepare sql %s .", subSql));
            stmt.execute(subSql);
        }
        this.connection.commit();
        return PluginStatus.SUCCESS.value();
    } catch (Exception e) {
        throw new DataExchangeException(e.getCause());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
            if (null != this.connection) {
                this.connection.close();
                this.connection = null;
            }
        } catch (SQLException e) {
        }
    }
}

From source file:de.codecentric.multitool.db.DBUnitLibrary.java

/**
 * Liest einen Einzelwert aus einer Tabellenabfrage
 * //from   w  w  w  .j ava2s .  c o m
 * Beispiel: | ${value} = | Read Single Value From Table | ACSD_FULL |
 * MYTABLE | STATUS | id = 5 |
 */
public String readSingleValueFromTable(String dsName, String table, String column, String whereStatment)
        throws IllegalArgumentException, SQLException {
    Statement statement = null;
    ResultSet rs = null;
    try {
        statement = getConnection(dsName).createStatement();
        String sql = "SELECT " + column + " FROM " + table
                + (StringUtils.isNotEmpty(whereStatment) ? " WHERE " + whereStatment : "");
        statement.execute(sql);

        rs = statement.getResultSet();
        if (rs.next()) {
            if (rs.getObject(1) == null)
                return null;
            else
                return rs.getObject(1).toString();
        }
        throw new NoSuchElementException(sql);

    } finally {
        if (rs != null) {
            rs.close();
        }
        if (statement != null) {
            statement.close();
        }
    }
}

From source file:com.opengamma.util.db.management.AbstractDbManagement.java

@Override
public void executeSql(String catalog, String schema, String sql) {
    ArrayList<String> sqlStatements = new ArrayList<String>();
    boolean inDollarQuote = false;
    boolean inComment = false;
    StringBuilder stmtBuilder = new StringBuilder();
    for (int currentIdx = 0; currentIdx < sql.length(); currentIdx++) {
        char currentChar = sql.charAt(currentIdx);
        char nextChar = currentIdx + 1 < sql.length() ? sql.charAt(currentIdx + 1) : 0;
        if (inDollarQuote) {
            // Add everything verbatim until the end-of-quote $$
            if (currentChar == '$' && nextChar == '$') {
                inDollarQuote = false;//  w w w .ja  v a  2  s.c o  m
            }
            stmtBuilder.append(currentChar);
            continue;
        }
        boolean isLineEnd = currentChar == '\r' || currentChar == '\n';
        if (currentChar == '\r' && nextChar == '\n') {
            currentIdx++;
        }
        if (inComment) {
            // Ignore everything until the next new line
            if (isLineEnd) {
                inComment = false;
            }
            continue;
        }
        if (isLineEnd) {
            stmtBuilder.append(" ");
            continue;
        }
        if (currentChar == ';') {
            String currentStmt = stmtBuilder.toString().trim();
            if (!currentStmt.isEmpty()) {
                sqlStatements.add(currentStmt);
            }
            stmtBuilder = new StringBuilder();
            continue;
        }
        if (currentChar == '-' && nextChar == '-') {
            inComment = true;
            continue;
        }
        if (currentChar == '$' && nextChar == '$') {
            inDollarQuote = true;
        }
        stmtBuilder.append(currentChar);
    }
    String currentStmt = stmtBuilder.toString().trim();
    if (!currentStmt.isEmpty()) {
        sqlStatements.add(currentStmt);
    }

    Connection conn = null;
    try {
        conn = connect(catalog);
        setActiveSchema(conn, schema);

        Statement statement = conn.createStatement();
        for (String sqlStatement : sqlStatements) {
            try {
                statement.execute(sqlStatement);
            } catch (SQLException e) {
                throw new OpenGammaRuntimeException(
                        "Failed to execute statement (" + getDbHost() + ") " + sqlStatement, e);
            }
        }
        statement.close();

    } catch (SQLException e) {
        throw new OpenGammaRuntimeException("Failed to execute statement", e);
    } finally {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
        }
    }
}

From source file:com.taobao.datax.plugins.writer.mysqlwriter.MysqlWriter.java

@Override
public int prepare(PluginParam param) {
    this.setParam(param);

    DBSource.register(this.sourceUniqKey, this.genProperties());

    if (StringUtils.isBlank(this.pre))
        return PluginStatus.SUCCESS.value();

    Statement stmt = null;
    try {//from   w  w w  .  j  a  v a  2 s.c  o  m
        this.connection = DBSource.getConnection(this.sourceUniqKey);

        stmt = this.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

        for (String subSql : this.pre.split(";")) {
            this.logger.info(String.format("Excute prepare sql %s .", subSql));
            stmt.execute(subSql);
        }

        return PluginStatus.SUCCESS.value();
    } catch (Exception e) {
        throw new DataExchangeException(e.getCause());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
            if (null != this.connection) {
                this.connection.close();
                this.connection = null;
            }
        } catch (SQLException e) {
        }
    }
}

From source file:atg.tools.dynunit.test.util.DBUtils.java

public void shutdown() throws SQLException {
    if (!connection.isClosed()) {
        Statement st = connection.createStatement();

        // db writes out to files and performs clean shuts down
        // otherwise there will be an unclean shutdown
        // when program ends
        if (connection.getMetaData().getDatabaseProductName().startsWith("HSQL")) {
            st.execute("SHUTDOWN");
        }//w w w . j  a va  2 s  . co  m
        connection.close(); // if there are no other open connection
    }
}

From source file:com.taobao.datax.plugins.writer.mysqlwriter.MysqlWriter.java

@Override
public int post(PluginParam param) {
    if (StringUtils.isBlank(this.post))
        return PluginStatus.SUCCESS.value();

    /*/* ww w  .  j  a va2  s .c o m*/
     * add by bazhen.csy if (null == this.connection) { throw new
     * DataExchangeException(String.format(
     * "MysqlWriter connect %s failed in post work .", this.host)); }
     */

    Statement stmt = null;
    try {
        this.connection = DBSource.getConnection(this.sourceUniqKey);

        stmt = this.connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);

        for (String subSql : this.post.split(";")) {
            this.logger.info(String.format("Excute prepare sql %s .", subSql));
            stmt.execute(subSql);
        }

        return PluginStatus.SUCCESS.value();
    } catch (Exception e) {
        throw new DataExchangeException(e.getCause());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
            if (null != this.connection) {
                this.connection.close();
                this.connection = null;
            }
        } catch (Exception e2) {
        }

    }
}