Example usage for java.sql Statement getConnection

List of usage examples for java.sql Statement getConnection

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

Introduction

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

Prototype

Connection getConnection() throws SQLException;

Source Link

Document

Retrieves the Connection object that produced this Statement object.

Usage

From source file:org.kawanfw.sql.servlet.sql.ResultSetWriter.java

/**
 * Returns true if engine is terradata// www .j a  v  a  2  s .c  om
 * 
 * @param resultSet
 *            the result set in use
 * @returns true if engine is terradata
 * @throws SQLException
 */
private String getDatabaseProductName(ResultSet resultSet) throws SQLException {

    Statement statement = resultSet.getStatement();

    // happens on Metadata requests, we don' care about the result:
    if (statement == null) {
        return "unknown";
    } else {
        Connection connection = statement.getConnection();
        return new SqlUtil(connection).getDatabaseProductName();
    }
}

From source file:cc.tooyoung.common.db.JdbcTemplate.java

public int[] batchUpdate(final String[] sql) throws DataAccessException {
    Assert.notEmpty(sql, "SQL array must not be empty");
    if (ApiLogger.isTraceEnabled()) {
        ApiLogger.trace(new StringBuilder(128).append("Executing SQL batch update of ").append(sql.length)
                .append(" statements"));
    }// w w w  .  j  a  v  a  2 s.c o  m

    class BatchUpdateStatementCallback implements StatementCallback, SqlProvider {
        private String currSql;

        public Object doInStatement(Statement stmt) throws SQLException, DataAccessException {
            int[] rowsAffected = new int[sql.length];
            if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
                for (int i = 0; i < sql.length; i++) {
                    this.currSql = sql[i];
                    stmt.addBatch(sql[i]);
                }
                rowsAffected = stmt.executeBatch();
            } else {
                for (int i = 0; i < sql.length; i++) {
                    this.currSql = sql[i];
                    if (!stmt.execute(sql[i])) {
                        rowsAffected[i] = stmt.getUpdateCount();
                    } else {
                        throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]);
                    }
                }
            }
            return rowsAffected;
        }

        public String getSql() {
            return currSql;
        }
    }
    return (int[]) execute(new BatchUpdateStatementCallback(), true);
}

From source file:com.hangum.tadpole.rdb.core.editors.main.composite.ResultSetComposite.java

/**
 * select? ./*from   w  ww . ja v a 2s .  c  o  m*/
 * 
 * @param strSQL
 */
private ResultSet _runSQLSelect(final Statement statement, final String strSQL) throws Exception {

    Future<ResultSet> queryFuture = execServiceQuery.submit(new Callable<ResultSet>() {
        @Override
        public ResultSet call() throws SQLException {

            // ???  PL/SQL  dbms_output    ? .
            if (DBGroupDefine.ORACLE_GROUP == getUserDB().getDBGroup()) {
                try {
                    dbmsOutput = new OracleDbmsOutputUtil(statement.getConnection());
                    dbmsOutput.enable(1000000);
                    statement.execute(strSQL);
                    dbmsOutput.show();
                    tadpole_system_message = dbmsOutput.getOutput();
                } finally {
                    try {
                        if (dbmsOutput != null)
                            dbmsOutput.close();
                    } catch (SQLException e) {
                    }
                }
            } else {
                statement.execute(strSQL);
            }

            return statement.getResultSet();
        }
    });

    /* SELECT ALRM_DATE ? select??  ?? ?  ? ? ?.
     * Caused by: java.lang.NullPointerException
       at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:973)
     */
    return queryFuture.get();
}

From source file:lib.JdbcTemplate.java

@Override
public int[] batchUpdate(final String... sql) throws DataAccessException {
    Assert.notEmpty(sql, "SQL array must not be empty");
    if (logger.isDebugEnabled()) {
        logger.debug("Executing SQL batch update of " + sql.length + " statements");
    }//from w ww  .  ja v a2 s .c o m

    class BatchUpdateStatementCallback implements StatementCallback<int[]>, SqlProvider {

        private String currSql;

        @Override
        public int[] doInStatement(Statement stmt) throws SQLException, DataAccessException {
            int[] rowsAffected = new int[sql.length];
            if (JdbcUtils.supportsBatchUpdates(stmt.getConnection())) {
                for (String sqlStmt : sql) {
                    this.currSql = appendSql(this.currSql, sqlStmt);
                    stmt.addBatch(sqlStmt);
                }
                try {
                    rowsAffected = stmt.executeBatch();
                } catch (BatchUpdateException ex) {
                    String batchExceptionSql = null;
                    for (int i = 0; i < ex.getUpdateCounts().length; i++) {
                        if (ex.getUpdateCounts()[i] == Statement.EXECUTE_FAILED) {
                            batchExceptionSql = appendSql(batchExceptionSql, sql[i]);
                        }
                    }
                    if (StringUtils.hasLength(batchExceptionSql)) {
                        this.currSql = batchExceptionSql;
                    }
                    throw ex;
                }
            } else {
                for (int i = 0; i < sql.length; i++) {
                    this.currSql = sql[i];
                    if (!stmt.execute(sql[i])) {
                        rowsAffected[i] = stmt.getUpdateCount();
                    } else {
                        throw new InvalidDataAccessApiUsageException("Invalid batch SQL statement: " + sql[i]);
                    }
                }
            }
            return rowsAffected;
        }

        private String appendSql(String sql, String statement) {
            return (StringUtils.isEmpty(sql) ? statement : sql + "; " + statement);
        }

        @Override
        public String getSql() {
            return this.currSql;
        }
    }

    return execute(new BatchUpdateStatementCallback());
}

From source file:edu.ku.brc.specify.conversion.BasicSQLUtils.java

/**
 * Deletes all the records from a table/*from   w ww  .  j  av  a  2  s.  c  o  m*/
 * @param connection connection to the DB
 * @param tableName the name of the table
 * @return the return value from the SQL update statement (or -1 on an exception)
 */
public static int deleteAllRecordsFromTable(final Connection connection, final String tableName,
        final SERVERTYPE currentServerType) {
    try {
        if (doesTableExist(connection, tableName)) {
            Integer count = getCount(connection, "SELECT COUNT(*) FROM " + tableName);
            if (count == null || count == 0) {
                return 0;
            }

            Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            if (currentServerType != SERVERTYPE.MS_SQLServer) {
                removeForeignKeyConstraints(stmt.getConnection(), currentServerType);
            }
            int retVal = exeUpdateCmd(stmt, "delete from " + tableName);
            stmt.clearBatch();
            stmt.close();

            log.info("Deleted " + count + " records from " + tableName);

            return retVal;

        }
    } catch (SQLException ex) {
        edu.ku.brc.af.core.UsageTracker.incrSQLUsageCount();
        edu.ku.brc.exceptions.ExceptionTracker.getInstance().capture(BasicSQLUtils.class, ex);
        log.error(ex);
        ex.printStackTrace();

    }
    return 0;
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testBatchUpdate() throws Exception {
    final String[] sql = { "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
            "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2" };

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    Statement mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.getConnection();
    ctrlStatement.setReturnValue(mockConnection);
    mockStatement.addBatch(sql[0]);/*  w  w  w  . j a v a2  s  .co  m*/
    ctrlStatement.setVoidCallable();
    mockStatement.addBatch(sql[1]);
    ctrlStatement.setVoidCallable();
    mockStatement.executeBatch();
    ctrlStatement.setReturnValue(new int[] { 1, 1 });
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(true);

    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    ctrlStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource, false);

    int[] actualRowsAffected = template.batchUpdate(sql);
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);

    ctrlStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testBatchUpdateWithNoBatchSupportAndSelect() throws Exception {
    final String[] sql = { "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
            "SELECT * FROM NOSUCHTABLE" };

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    Statement mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.getConnection();
    ctrlStatement.setReturnValue(mockConnection);
    mockStatement.execute(sql[0]);/*from   w  w w . j  av a  2  s  .  co m*/
    ctrlStatement.setReturnValue(false);
    mockStatement.getUpdateCount();
    ctrlStatement.setReturnValue(1);
    mockStatement.execute(sql[1]);
    ctrlStatement.setReturnValue(true);
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(false);

    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    ctrlStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource, false);

    try {
        template.batchUpdate(sql);
        fail("Shouldn't have executed batch statement with a select");
    } catch (DataAccessException ex) {
        // pass
        assertTrue("Check exception type", ex.getClass() == InvalidDataAccessApiUsageException.class);
    }

    ctrlStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testBatchUpdateWithNoBatchSupport() throws Exception {
    final String[] sql = { "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 1",
            "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = 2" };

    MockControl ctrlStatement = MockControl.createControl(Statement.class);
    Statement mockStatement = (Statement) ctrlStatement.getMock();
    mockStatement.getConnection();
    ctrlStatement.setReturnValue(mockConnection);
    mockStatement.execute(sql[0]);//from   w  w  w.  jav a  2 s.  c  o  m
    ctrlStatement.setReturnValue(false);
    mockStatement.getUpdateCount();
    ctrlStatement.setReturnValue(1);
    mockStatement.execute(sql[1]);
    ctrlStatement.setReturnValue(false);
    mockStatement.getUpdateCount();
    ctrlStatement.setReturnValue(1);
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    MockControl ctrlDatabaseMetaData = MockControl.createControl(DatabaseMetaData.class);
    DatabaseMetaData mockDatabaseMetaData = (DatabaseMetaData) ctrlDatabaseMetaData.getMock();
    mockDatabaseMetaData.getDatabaseProductName();
    ctrlDatabaseMetaData.setReturnValue("MySQL");
    mockDatabaseMetaData.supportsBatchUpdates();
    ctrlDatabaseMetaData.setReturnValue(false);

    mockConnection.getMetaData();
    ctrlConnection.setReturnValue(mockDatabaseMetaData, 2);
    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);

    ctrlStatement.replay();
    ctrlDatabaseMetaData.replay();
    replay();

    JdbcTemplate template = new JdbcTemplate(mockDataSource, false);

    int[] actualRowsAffected = template.batchUpdate(sql);
    assertTrue("executed 2 updates", actualRowsAffected.length == 2);

    ctrlStatement.verify();
    ctrlDatabaseMetaData.verify();
}

From source file:org.kepler.objectmanager.library.LibItem.java

/**
 * Delete this populated LibItem from the Library_Index table. This will
 * also delete all the children and update the LFT and RGT indexes of all
 * the other items in the table.//from w  ww.  java  2s  .  com
 * 
 * @param stmt
 * @throws SQLException
 */
public void delete(Statement stmt) throws SQLException {

    // double check the LFT and RGT values
    String query = "SELECT LFT,RGT FROM " + LibIndex.LIBRARY_INDEX_TABLE_NAME + " WHERE LIID = " + getLiid();
    if (isDebugging)
        log.debug(query);
    ResultSet rs = null;
    try {
        rs = stmt.executeQuery(query);
        if (rs == null)
            throw new SQLException("Query Failed: " + query);
        if (rs.next()) {
            int l = rs.getInt(1);
            int r = rs.getInt(2);
            if (l != getLeft())
                log.warn("Left value wasn't set before delete " + getLiid());
            if (r != getRight())
                log.warn("Right value wasn't set before delete " + getLiid());
            setLeft(l);
            setRight(r);
        }
    } finally {
        if (rs != null) {
            rs.close();
        }
    }

    // The number of rows to be deleted
    int delCount = getRight() - getLeft();

    String delete = "delete from " + LibIndex.LIBRARY_INDEX_TABLE_NAME + " WHERE LFT >= " + getLeft()
            + " AND RGT <= " + getRight();

    String updateLeft = "UPDATE " + LibIndex.LIBRARY_INDEX_TABLE_NAME + " SET LFT = LFT - " + (delCount + 1)
            + " WHERE LFT >= " + getLeft();
    String updateRight = "UPDATE " + LibIndex.LIBRARY_INDEX_TABLE_NAME + " SET RGT = RGT - " + (delCount + 1)
            + " WHERE RGT >= " + getLeft();

    if (isDebugging) {
        log.debug("\n" + delete + "\n" + updateLeft + "\n" + updateRight);
    }

    stmt.executeUpdate(updateLeft);
    stmt.executeUpdate(updateRight);
    stmt.executeUpdate(delete);
    stmt.getConnection().commit();

}

From source file:herddb.cli.HerdDBCLI.java

@SuppressFBWarnings("SQL_PREPARED_STATEMENT_GENERATED_FROM_NONCONSTANT_STRING")
private static void executeStatementInSqlFile(String query, final Statement statement,
        SqlFileStatus sqlFileStatus) throws SQLException, ScriptException {
    query = query.trim();//from ww  w. j  a v a2s.co  m

    if (query.isEmpty() || query.startsWith("--")) {
        return;
    }
    String formattedQuery = query.toLowerCase();
    if (formattedQuery.endsWith(";")) {
        // mysqldump
        formattedQuery = formattedQuery.substring(0, formattedQuery.length() - 1);
    }
    if (formattedQuery.equals("exit") || formattedQuery.equals("quit")) {
        throw new SQLException("explicit END of script with '" + formattedQuery + "'");
    }
    if (sqlFileStatus.frommysqldump
            && (formattedQuery.startsWith("lock tables") || formattedQuery.startsWith("unlock tables"))) {
        // mysqldump
        return;
    }
    Boolean setAutoCommit = null;
    if (formattedQuery.startsWith("autocommit=")) {
        String value = "";
        if (formattedQuery.split("=").length > 1) {
            value = formattedQuery.split("=")[1];
        }
        switch (value) {
        case "true":
            setAutoCommit = true;
            break;
        case "false":
            setAutoCommit = false;
            break;
        default:
            System.out.println("No valid value for autocommit. Only true and false allowed.");
            return;
        }
    }
    if (sqlFileStatus.verbose) {
        System.out.println("Executing query:" + query);
    }
    try {
        if (setAutoCommit != null) {
            statement.getConnection().setAutoCommit(setAutoCommit);
            System.out.println("Set autocommit=" + setAutoCommit + " executed.");
            return;
        }
        if (formattedQuery.equals("commit")) {
            sqlFileStatus.flushAndCommit(statement.getConnection());

            System.out.println("Commit executed.");
            return;
        }
        if (formattedQuery.equals("rollback")) {
            sqlFileStatus.rollback(statement.getConnection());
            statement.getConnection().rollback();
            System.out.println("Rollback executed.");
            return;
        }

        QueryWithParameters rewritten = null;
        if (sqlFileStatus.rewritestatements) {
            rewritten = rewriteQuery(query, sqlFileStatus.tableSpaceMapper, sqlFileStatus.frommysqldump);
        }
        if (rewritten != null) {
            if (rewritten.schema != null) {
                HerdDBConnection connection = statement.getConnection().unwrap(HerdDBConnection.class);
                if (connection != null && !connection.getSchema().equalsIgnoreCase(rewritten.schema)) {
                    sqlFileStatus.flushAndCommit(connection);
                    commitAndChangeSchema(connection, rewritten.schema);
                }
            }
            PreparedStatement ps = sqlFileStatus.prepareStatement(statement.getConnection(), rewritten.query);
            int i = 1;
            for (Object o : rewritten.jdbcParameters) {
                ps.setObject(i++, o);
            }
            ps.addBatch();
        } else {
            PreparedStatement ps = sqlFileStatus.prepareStatement(statement.getConnection(), query);
            ps.addBatch();
        }
        sqlFileStatus.countPendingOp();
    } catch (SQLException err) {
        if (sqlFileStatus.ignoreerrors) {
            println("ERROR:" + err);
            return;
        } else {
            throw err;
        }
    }
}