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.ibm.soatf.component.database.StatementExecutor.java

/**
 * Runs an SQL script from the file specified by the <code>inputScriptFile</code> parameter
 * //from  ww w.  j  av a 2  s  .c om
 * @param conn SQL connection on which you want to run this script
 * @param file script file
 * @throws StatementExecutorException if SQL or IO exception occurs
 */
public void runScript(Connection conn, File file) throws DatabaseComponentException {
    OperationResult cor = OperationResult.getInstance();
    String inputScriptFilePath = "";
    String inputScriptRelativePath = "";
    Statement stmt = null;
    try {
        ProgressMonitor.increment("Loading SQL script...");
        inputScriptFilePath = file.getAbsolutePath();
        inputScriptRelativePath = FileSystem.getRelativePath(file);
        String sql = FileUtils.readFileToString(file);
        if (sql.endsWith(";"))
            sql = sql.substring(0, sql.length() - 1);
        String msg = "Successfuly loaded script [FILE: %s]";
        logger.debug(String.format(msg, inputScriptFilePath));
        cor.addMsg(msg, "<a href='file://" + inputScriptFilePath + "'>" + inputScriptFilePath + "</a>",
                inputScriptRelativePath);

        conn.setAutoCommit(false);
        stmt = conn.createStatement();
        ProgressMonitor.increment("Executing SQL script...");
        boolean hasResults = stmt.execute(sql);
        conn.commit();
        int updateCount = -1;
        if (!hasResults) {
            updateCount = stmt.getUpdateCount();
        }
        msg = "Script run successful, update count: " + updateCount;
        logger.debug(msg);
        cor.addMsg(msg);
        final String logMsg = "Record has been inserted into source database '" + conn.getMetaData().getURL()
                + "'.\n" + "Insert statement executed:\n%s";
        cor.addMsg(logMsg, sql, "[FILE: " + FileSystem.getRelativePath(file) + "]");
        cor.markSuccessful();
    } catch (IOException ex) {
        final String msg = "Failed to open statement [FILE: %s].";
        cor.addMsg(msg, "<a href='file://" + inputScriptFilePath + "'>" + inputScriptFilePath + "</a>",
                inputScriptRelativePath);
        throw new DatabaseComponentException(String.format(msg, inputScriptFilePath), ex);
    } catch (SQLException ex) {
        final String msg = String.format("Failed to execute INSERT statement: %s",
                Utils.getSQLExceptionMessage(ex));
        cor.addMsg(msg);
        throw new DatabaseComponentException(msg, ex);
    } finally {
        DatabaseComponent.closeStatement(stmt);
    }
}

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

@Test
public void testObjectTimestamp() throws Exception {
    Statement stmt = con.createStatement();
    java.util.Date now = new java.util.Date();

    // Create the target Column family
    //String createCF = "CREATE COLUMNFAMILY t74 (id BIGINT PRIMARY KEY, col1 TIMESTAMP)";        
    String createCF = "CREATE COLUMNFAMILY t74 (id BIGINT PRIMARY KEY, col1 TIMESTAMP)";

    stmt.execute(createCF);
    stmt.close();//from  w w  w.  j av  a 2 s . c  o m
    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));

    Statement statement = con.createStatement();

    String insert = "INSERT INTO t74 (id, col1) VALUES (?, ?);";

    PreparedStatement pstatement = con.prepareStatement(insert);
    pstatement.setLong(1, 1L);
    pstatement.setObject(2, new Timestamp(now.getTime()), Types.TIMESTAMP);
    pstatement.execute();

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

    assertTrue(result.next());
    assertEquals(1L, result.getLong(1));

    // try reading Timestamp directly
    Timestamp stamp = result.getTimestamp(2);
    assertEquals(now, stamp);

    // try reading Timestamp as an object
    stamp = result.getObject(2, Timestamp.class);
    assertEquals(now, stamp);

    System.out.println(resultToDisplay(result, 74, "current date"));

}

From source file:architecture.common.spring.jdbc.core.ExtendedJdbcTemplate.java

protected Object runScript(Connection conn, boolean stopOnError, Reader reader)
        throws SQLException, IOException {

    StringBuffer command = null;// www .  ja va  2  s.c  om
    List<Object> list = new ArrayList<Object>();
    try {
        LineNumberReader lineReader = new LineNumberReader(reader);
        String line = null;
        while ((line = lineReader.readLine()) != null) {
            if (command == null) {
                command = new StringBuffer();
            }
            String trimmedLine = line.trim();
            if (trimmedLine.startsWith("--")) {
                if (logger.isDebugEnabled())
                    logger.debug(trimmedLine);
            } else if (trimmedLine.length() < 1 || trimmedLine.startsWith("//")) {
                // Do nothing
            } else if (trimmedLine.length() < 1 || trimmedLine.startsWith("--")) {
                // Do nothing
            } else if (trimmedLine.endsWith(";")) {
                command.append(line.substring(0, line.lastIndexOf(";")));
                command.append(" ");

                Statement statement = conn.createStatement();
                if (logger.isDebugEnabled()) {
                    logger.debug("Executing SQL script command [" + command + "]");
                }

                boolean hasResults = false;
                if (stopOnError) {
                    hasResults = statement.execute(command.toString());
                } else {
                    try {
                        statement.execute(command.toString());
                    } catch (SQLException e) {
                        if (logger.isDebugEnabled())
                            logger.error("Error executing: " + command, e);
                        throw e;
                    }
                }
                ResultSet rs = statement.getResultSet();
                if (hasResults && rs != null) {
                    RowMapperResultSetExtractor<Map<String, Object>> rse = new RowMapperResultSetExtractor<Map<String, Object>>(
                            getColumnMapRowMapper());
                    List<Map<String, Object>> rows = rse.extractData(rs);
                    list.add(rows);
                }
                command = null;
            } else {
                command.append(line);
                command.append(" ");
            }
        }

        return list;
    } catch (SQLException e) {
        logger.error("Error executing: " + command, e);
        throw e;
    } catch (IOException e) {
        logger.error("Error executing: " + command, e);
        throw e;
    }
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testSystemPrivileges() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();
    stmt.execute("CREATE USER \"jsyang\" IDENTIFIED BY '0009'");
    stmt.close();//w  w  w .j a  v a 2  s .  c  om
    conn.close();

    conn = getConnection("jsyang", "0009");
    stmt = conn.createStatement();

    try {
        stmt.execute("ALTER SYSTEM ADD DATASOURCE \"" + dataMemDb.name + "\" CONNECT TO '"
                + dataMemDb.connectionString + "' USING '" + MemoryDatabase.DRIVER_NAME + "'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("CREATE USER \"kskim\" IDENTIFIED BY 'vp'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("ALTER USER \"jsyang\" IDENTIFIED BY 'jsyang' REPLACE '0009'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("DROP USER \"jsyang\"");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("GRANT CREATE USER TO \"jsyang\"");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("REVOKE CREATE USER FROM \"octopus\"");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("COMMENT ON TABLE \"" + dataMemDb.name + "\".\"__DEFAULT\".\"employee\" IS 'test'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    try {
        stmt.execute("SET DATACATEGORY ON COLUMN \"" + dataMemDb.name
                + "\".\"__DEFAULT\".\"employee\".\"name\" IS 'category'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    stmt.close();
    conn.close();

    conn = getConnection("octopus", "bitnine");
    stmt = conn.createStatement();

    stmt.execute("GRANT ALL PRIVILEGES TO \"jsyang\"");
    String query = "REVOKE ALTER SYSTEM, " + "SELECT ANY TABLE, " + "ALTER USER, DROP USER, " + "COMMENT ANY, "
            + "GRANT ANY OBJECT PRIVILEGE, GRANT ANY PRIVILEGE " + "FROM \"jsyang\"";
    stmt.execute(query);

    stmt.close();
    conn.close();

    conn = getConnection("jsyang", "0009");
    stmt = conn.createStatement();

    stmt.execute("CREATE USER \"kskim\" IDENTIFIED BY 'vp'");

    try {
        stmt.execute("DROP USER \"kskim\"");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }

    stmt.close();
    conn.close();

    conn = getConnection("octopus", "bitnine");
    stmt = conn.createStatement();
    stmt.execute("REVOKE CREATE USER FROM \"jsyang\"");
    stmt.close();
    conn.close();

    conn = getConnection("jsyang", "0009");
    stmt = conn.createStatement();
    try {
        stmt.execute("CREATE USER \"bitnine\" IDENTIFIED BY 'password'");
    } catch (SQLException e) {
        System.out.println("expected exception - " + e.getMessage());
    }
    stmt.close();
    conn.close();

    conn = getConnection("octopus", "bitnine");
    stmt = conn.createStatement();
    stmt.execute("DROP USER \"kskim\"");
    stmt.execute("DROP USER \"jsyang\"");
    stmt.close();
    conn.close();
}

From source file:edu.duke.cabig.c3pr.webservice.integration.StudyUtilityWebServiceTest.java

/**
 * Need to do some DELETEs which could not be done via DbUnit.
 * //w  w w  .  jav  a2s.  c o m
 * @throws SQLException
 * @throws Exception
 */
private void cleanupDatabaseData() throws SQLException, Exception {
    try {
        Connection conn = getConnection().getConnection();
        Statement st = conn.createStatement();
        st.execute("DELETE FROM identifiers where stu_id is not null");
        st.execute("DELETE FROM reasons where per_reg_st_id is not null");
        st.close();
    } catch (Exception e) {
        logger.severe("cleanupDatabaseData() failed.");
        logger.severe(ExceptionUtils.getFullStackTrace(e));
        e.printStackTrace();
    }

}

From source file:gsn.storage.StorageManager.java

/**
 * This method executes the provided statement over the connection. If there
 * is an error retruns -1 otherwise it returns the output of the
 * executeUpdate method on the PreparedStatement class which reflects the
 * number of changed rows in the underlying table.
 *
 * @param sql/*w  ww  . j  ava  2 s  .co m*/
 * @param connection
 * @return Number of effected rows or -1 if there is an error.
 */
public void executeCommand(String sql, Connection connection) {
    Statement stmt = null;
    try {
        stmt = connection.createStatement();
        stmt.execute(sql);
    } catch (SQLException error) {
        logger.error(error.getMessage() + " FOR: " + sql, error);
    } finally {
        try {
            if (stmt != null && !stmt.isClosed())
                stmt.close();
        } catch (SQLException e) {
            logger.error(e.getMessage(), e);
        }
    }
}

From source file:kr.co.bitnine.octopus.frame.SessionServerTest.java

@Test
public void testUpdateDataSource3() throws Exception {
    Connection conn = getConnection("octopus", "bitnine");
    Statement stmt = conn.createStatement();

    final String comment = "commentOnTable";
    final String tblName = "employee";

    stmt.execute("COMMENT ON TABLE \"" + dataMemDb.name + "\".\"__DEFAULT\".\"" + tblName + "\" IS '" + comment
            + "'");
    DatabaseMetaData metaData = conn.getMetaData();

    ResultSet rs = metaData.getTables(dataMemDb.name, "%DEFAULT", tblName, null);
    while (rs.next()) {
        if (rs.getString("TABLE_NAME").equals(tblName))
            assertTrue(rs.getString("REMARKS").equals(comment));
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("TABLE_NAME") + ", " + rs.getString("REMARKS"));
    }/*from  w w  w. j a  va2 s  . c o  m*/
    rs.close();

    stmt.execute("ALTER SYSTEM UPDATE DATASOURCE \"" + dataMemDb.name + '"');

    rs = metaData.getTables(dataMemDb.name, "%DEFAULT", tblName, null);
    while (rs.next()) {
        if (rs.getString("TABLE_NAME").equals(tblName))
            assertTrue(rs.getString("REMARKS").equals(comment));
        System.out.println("  " + rs.getString("TABLE_CAT") + ", " + rs.getString("TABLE_SCHEM") + ", "
                + rs.getString("TABLE_NAME") + ", " + rs.getString("REMARKS"));
    }
    rs.close();

    stmt.close();
    conn.close();
}

From source file:com.splicemachine.derby.test.framework.SpliceTableWatcher.java

public void start() {
    Statement statement = null;
    ResultSet rs;//from   ww w. ja v  a 2  s .c om
    Connection connection;
    synchronized (SpliceTableWatcher.class) {
        try {
            connection = (userName == null) ? SpliceNetConnection.getConnection()
                    : SpliceNetConnection.getConnectionAs(userName, password);
            rs = connection.getMetaData().getTables(null, schemaName, tableName, null);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
    TableDAO tableDAO = new TableDAO(connection);

    try {
        if (rs.next()) {
            tableDAO.drop(schemaName, tableName);
        }
        connection.commit();
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }

    try {
        statement = connection.createStatement();
        statement.execute(String.format("create table %s.%s %s", schemaName, tableName, createString));
        connection.commit();
    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        DbUtils.closeQuietly(rs);
        DbUtils.closeQuietly(statement);
        DbUtils.commitAndCloseQuietly(connection);
    }
}

From source file:kr.co.bitnine.octopus.testutils.MemoryDatabase.java

public void importJSON(Class<?> clazz, String resourceName) throws Exception {
    Connection conn = getConnection();
    conn.setAutoCommit(false);/*from ww  w .ja  v  a 2  s .co  m*/
    Statement stmt = conn.createStatement();

    JSONParser jsonParser = new JSONParser();
    JSONArray tables = (JSONArray) jsonParser
            .parse(new InputStreamReader(clazz.getResourceAsStream(resourceName)));
    for (Object tableObj : tables) {
        StringBuilder queryBuilder = new StringBuilder();

        JSONObject table = (JSONObject) tableObj;

        String tableName = jsonValueToSqlIdent(table.get("table-name"));
        queryBuilder.append("CREATE TABLE ").append(tableName).append('(');
        JSONArray schema = (JSONArray) table.get("table-schema");
        for (Object columnNameObj : schema) {
            String columnName = jsonValueToSqlValue(columnNameObj);
            queryBuilder.append(columnName).append(',');
        }
        queryBuilder.setCharAt(queryBuilder.length() - 1, ')');
        stmt.execute(queryBuilder.toString());

        JSONArray rows = (JSONArray) table.get("table-rows");
        for (Object rowObj : rows) {
            JSONArray row = (JSONArray) rowObj;

            queryBuilder.setLength(0);
            queryBuilder.append("INSERT INTO ").append(tableName).append(" VALUES(");
            for (Object columnObj : row)
                queryBuilder.append(jsonValueToSqlValue(columnObj)).append(',');
            queryBuilder.setCharAt(queryBuilder.length() - 1, ')');
            stmt.executeUpdate(queryBuilder.toString());
        }
    }

    stmt.close();
    conn.commit();
    conn.close();
}

From source file:io.bibleget.BibleGetDB.java

public boolean addColumn(String colName, String type) {
    int count;/*from  w  w  w. j  a  v  a  2s . co  m*/
    try {
        colName = colName.toUpperCase();
        Statement stmt = instance.conn.createStatement();
        String sqlexec = "ALTER TABLE OPTIONS ADD COLUMN " + colName + " " + type;
        boolean colAdded = stmt.execute(sqlexec);

        if (colAdded == false) {
            count = stmt.getUpdateCount();
            if (count == -1) {
                //System.out.println("The result is a ResultSet object or there are no more results."); 
            } else if (count == 0) {
                //0 rows affected
                stmt.close();
                return true;
            }
        } else {
            //returns true only when returning a resultset; should not be the case here
        }

    } catch (SQLException ex) {
        Logger.getLogger(BibleGetDB.class.getName()).log(Level.SEVERE, null, ex);
    }
    return false;
}