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.clican.pluto.dataprocess.BaseDataProcessTestCase.java

protected void onSetUp() throws Exception {
    super.onSetUp();
    Connection conn = null;//from  w ww. j a  va 2 s  . co  m
    Statement stat = null;
    InputStream is = null;
    try {
        Class.forName("org.hsqldb.jdbcDriver");
        conn = DriverManager.getConnection("jdbc:hsqldb:.", "sa", "");
        stat = conn.createStatement();

        is = Thread.currentThread().getContextClassLoader()
                .getResourceAsStream("ibatis/createtable-dataprocess-test.sql");
        byte[] script = new byte[is.available()];
        is.read(script);

        String sql = new String(script);
        stat.execute(sql);

        databaseTester = new JdbcDatabaseTester("org.hsqldb.jdbcDriver", "jdbc:hsqldb:.", "sa", "");

        // initialize your dataset here
        IDataSet dataSet = getDataSet();
        if (dataSet != null) {
            databaseTester.setDataSet(dataSet);
        }
        // will call default setUpOperation
        databaseTester.onSetup();
    } catch (Exception e) {
        log.error("", e);
        throw e;
    } finally {
        if (stat != null) {
            stat.close();
        }
        if (conn != null) {
            conn.close();
        }
        if (is != null) {
            is.close();
        }
    }
}

From source file:eu.optimis_project.monitoring.storage.MySQLStorageManager.java

private void createTable() throws SQLException {
    final String createTable = "CREATE TABLE IF NOT EXISTS " + tableName + "(" + ENTRIES_COLUMNNAME_SERVICEID
            + " VARCHAR(256) NOT NULL, " + ENTRIES_COLUMNNAME_INSTANCEID + " VARCHAR(256) NOT NULL, "
            + ENTRIES_COLUMNNAME_NAME + " VARCHAR(256) NOT NULL, " + ENTRIES_COLUMNNAME_DATA
            + " VARCHAR(256) NOT NULL, " + ENTRIES_COLUMNNAME_TIMESTAMP + " BIGINT NOT NULL, " + "PRIMARY KEY ("
            + ENTRIES_COLUMNNAME_SERVICEID + ", " + ENTRIES_COLUMNNAME_TIMESTAMP + ", "
            + ENTRIES_COLUMNNAME_NAME + ")" + ");";

    Statement statement = null;
    try {/*  ww  w  .j  av a  2s. com*/
        log.debug("Executing query: " + createTable);
        statement = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        statement.execute(createTable);
    } catch (SQLException e) {
        throw e;
    } finally {
        try {
            if (statement != null) {
                statement.close();
            }
        } catch (SQLException e) {
            log.debug("Failed to close statement.", e);
        }
    }
}

From source file:com.nextep.designer.dbgm.services.impl.DerbyStorageService.java

@Override
public void renameStorageHandle(IDataSet set, String newName) {
    IStorageHandle handle = set.getStorageHandle();
    if (handle == null) {
        handle = createDataSetStorage(set);
    }/*from   w w  w .j a  v  a  2 s.co m*/
    Connection conn = null;
    Statement stmt = null;
    try {
        conn = getLocalConnection();
        stmt = conn.createStatement();
        try {
            stmt.execute("DROP TABLE " + newName); //$NON-NLS-1$
        } catch (SQLException e) {
            // May generally fail when no previously existing table, it's normal behaviour
        }
        stmt.execute("RENAME TABLE " + handle.getStorageUnitName() + " TO " + newName); //$NON-NLS-1$ //$NON-NLS-2$
        final StorageHandle newHandle = new StorageHandle(newName, set);
        newHandle.setInsertStatement(buildInsertDatalineStatement(set, newHandle, false));
        newHandle.setSelectStatement(buildSelectDatalineStatement(set, newHandle, false));
        set.setStorageHandle(newHandle);
    } catch (SQLException e) {
        throw new ErrorException(
                DBGMMessages.getString("service.derbyStorage.renameDatasetFailed") + e.getMessage(), e); //$NON-NLS-1$
    }
}

From source file:de.ufinke.cubaja.sql.Database.java

/**
 * Executes SQL provided as <tt>Sql</tt> instance immediately.
 * <p>//w  w  w . j a  v a2  s  .  com
 * There may be more than one SQL statement; each
 * statement separated by semicolon.
 * <p>
 * You may optionally specify any number of SQL codes which are expected
 * and should not throw an <tt>SQLException</tt>. This is
 * useful e.g. for <tt>drop</tt> statements.
 * The SQL codes are vendor specific. 
 * @param sql
 * @param acceptedSqlCodes
 * @throws SQLException
 */
public void execute(Sql sql, int... acceptedSqlCodes) throws SQLException {

    if (sql.hasVariables()) {
        throw new SQLException(text.get("execVariables"));
    }

    Statement statement = connection.createStatement();

    for (String stm : sql.getStatements()) {

        if (logger != null) {
            logger.debug(text.get("execute", myId, stm));
        }

        try {
            statement.execute(stm);
        } catch (SQLException e) {
            int sqlCode = e.getErrorCode();
            boolean accepted = false;
            int i = 0;
            while ((!accepted) && (i < acceptedSqlCodes.length)) {
                accepted = (acceptedSqlCodes[i] == sqlCode);
                i++;
            }
            if (!accepted) {
                try {
                    statement.close();
                } catch (SQLException ignore) {
                }
                throw e;
            }
        }
    }

    statement.close();
}

From source file:de.xwic.sandbox.server.installer.impl.SQLServerDatabaseHandler.java

public boolean renameScope(String oldScope, String newScope) {

    boolean exist = false;
    try {/* w w  w. ja  va 2s. co  m*/

        String sql = "SELECT NAME FROM CRM_SEC_SCOPE WHERE NAME = '" + newScope + "'";
        Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        try {
            if (stmt.execute(sql)) {
                ResultSet rs = stmt.getResultSet();
                if (rs.next()) {
                    exist = true;
                }
            }
        } finally {
            stmt.close();
        }

        // determine if the newScope does not exist?
        if (exist) {
            return false;
        }
        stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        sql = "UPDATE CRM_SEC_SCOPE SET NAME = '" + newScope + "' WHERE NAME = '" + oldScope + "'";
        try {
            if (stmt.executeUpdate(sql) != 1) {
                log.warn("More than one entry with the name " + oldScope + " have been found.");
            }
        } finally {
            stmt.close();
        }

        // now update the SCOPE.propNAME scope types
        stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        sql = "SELECT NAME FROM CRM_SEC_SCOPE WHERE NAME LIKE '" + oldScope + ".%'";
        try {
            if (stmt.execute(sql)) {
                ResultSet rs = stmt.getResultSet();
                while (rs.next()) {
                    String oldName = rs.getString("NAME");
                    String propertyName = oldName.substring(oldScope.length() + 1);
                    String newName = newScope + "." + propertyName;

                    //rs.updateString("NAME", newName);
                    //rs.updateRow();

                    Statement stmtUpdate = connection.createStatement();
                    try {
                        String sqlUpdate = "UPDATE CRM_SEC_SCOPE SET NAME = '" + newName + "' WHERE NAME='"
                                + oldName + "'";
                        int num = stmtUpdate.executeUpdate(sqlUpdate);
                        if (num == 0) {
                            log.warn("Warning: RENAME FAILED FROM " + oldName + " TO " + newName);
                        }
                    } finally {
                        stmtUpdate.close();
                    }

                }
            }
        } finally {
            stmt.close();
        }

        // else, rename the scope + all dependent scopes
        // that include the property names
        // oldScopeName + ".%"

        return true;
    } catch (Exception e) {
        // the update process should not be aborted when this happens, so
        // its just a warning
        log.warn("Error renaming '" + oldScope + "', into '" + newScope + "'", e);
        return false;
    }
}

From source file:ca.queensu.cs.sail.mailboxmina2.main.modules.ThreadsModule.java

/**
 * This method stores associations of parents given as Hash Table
 * in the Database//from w  w  w.  j a v  a  2 s  .com
 * 
 * @param parents
 *            a table that maps for every method having a parent this
 *            parent's ID
 * @throws SQLException
 */
private void storeParents(Hashtable<String, String> parents, Connection connection) throws SQLException {
    // Use StringBuilders to create big insertion queries

    StringBuilder parentQueryBuilder = new StringBuilder();

    // Queries use the pgpsql functions
    // merge_parent(int msg_uid, int parent_uid)
    // merge_root(int msg_uid, int root_uid)
    // that take care of insertion / update automatically

    parentQueryBuilder.append("PREPARE parentplan (int, int) AS SELECT merge_parent($1, $2);");
    // Genereate the parents query
    int i = 0;
    for (String key : parents.keySet()) {
        parentQueryBuilder.append("EXECUTE parentplan (" + key + ", " + parents.get(key) + ");"
                + System.getProperty("line.separator"));
        i++;
        if ((i % 1000) == 0) {
            i = 0;
            parentQueryBuilder.append("DEALLOCATE parentplan;" + System.getProperty("line.separator"));
            String sqlstr = parentQueryBuilder.toString();
            Statement statement = connection.createStatement();
            statement.execute(sqlstr);
            statement.close();
            parentQueryBuilder.delete(0, parentQueryBuilder.length());
            parentQueryBuilder.append("PREPARE parentplan (int, int) AS SELECT merge_parent($1, $2);");
            Main.getLogger().debug(4, "Stored 1000 parent relations!");
        }
    }
    if (i > 0) {
        parentQueryBuilder.append("DEALLOCATE parentplan;" + System.getProperty("line.separator"));
        String sqlstr = parentQueryBuilder.toString();
        Statement statement = connection.createStatement();
        statement.execute(sqlstr);
        statement.close();
        Main.getLogger().debug(4, "Stored " + i + " parent relations!");
    }
}

From source file:ca.queensu.cs.sail.mailboxmina2.main.modules.ThreadsModule.java

/**
 * This method stores the root associations of a roots table into the database
 * @param roots a list of roots associations
 * @throws SQLException//w ww  .  j  a v  a 2  s  . co m
 */
private void storeRoots(Hashtable<String, String> roots, Connection connection) throws SQLException {
    // Use StringBuilders to create big insertion queries

    StringBuilder rootQueryBuilder = new StringBuilder();

    // Queries use the pgpsql functions
    // merge_parent(int msg_uid, int parent_uid)
    // merge_root(int msg_uid, int root_uid)
    // that take care of insertion / update automatically

    rootQueryBuilder.append("PREPARE rootplan (int, int) AS SELECT merge_root($1, $2);");
    // Genereate the roots query
    int i = 0;
    for (String key : roots.keySet()) {
        rootQueryBuilder.append("EXECUTE rootplan (" + key + ", " + roots.get(key) + ");"
                + System.getProperty("line.separator"));
        i++;
        if ((i % 1000) == 0) {
            i = 0;
            rootQueryBuilder.append("DEALLOCATE rootplan;" + System.getProperty("line.separator"));
            String sqlstr = rootQueryBuilder.toString();
            Statement statement = connection.createStatement();
            statement.execute(sqlstr);
            statement.close();
            rootQueryBuilder.delete(0, rootQueryBuilder.length());
            rootQueryBuilder.append("PREPARE rootplan (int, int) AS SELECT merge_root($1, $2);");
            Main.getLogger().debug(4, "Stored 1000 root relations!");
        }
    }
    if (i > 0) {
        rootQueryBuilder.append("DEALLOCATE rootplan;" + System.getProperty("line.separator"));
        String sqlstr = rootQueryBuilder.toString();
        Statement statement = connection.createStatement();
        statement.execute(sqlstr);
        statement.close();
        Main.getLogger().debug(4, "Stored " + i + " root relations!");
    }
}

From source file:UploadImage.java

public void doPost(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //  change the following parameters to connect to the oracle database
    String username = "wang8";
    String password = "Steelrose101";
    String drivername = "oracle.jdbc.driver.OracleDriver";
    String dbstring = "jdbc:oracle:thin:@gwynne.cs.ualberta.ca:1521:CRS";
    int pic_id;/*  w  w w  . j  av a  2  s . c  o m*/

    try {
        //Parse the HTTP request to get the image stream
        DiskFileUpload fu = new DiskFileUpload();
        List FileItems = fu.parseRequest(request);

        // Process the uploaded items, assuming only 1 image file uploaded
        Iterator i = FileItems.iterator();
        FileItem item = (FileItem) i.next();
        while (i.hasNext() && item.isFormField()) {
            item = (FileItem) i.next();
        }

        //Get the image stream
        InputStream instream = item.getInputStream();

        // Connect to the database and create a statement
        Connection conn = getConnected(drivername, dbstring, username, password);
        Statement stmt = conn.createStatement();

        /*
         *  First, to generate a unique pic_id using an SQL sequence
         */
        ResultSet rset1 = stmt.executeQuery("SELECT pic_id_sequence.nextval from dual");
        rset1.next();
        pic_id = rset1.getInt(1);

        //Insert an empty blob into the table first. Note that you have to 
        //use the Oracle specific function empty_blob() to create an empty blob
        stmt.execute("INSERT INTO pictures VALUES(" + pic_id + ",'test',empty_blob())");

        // to retrieve the lob_locator 
        // Note that you must use "FOR UPDATE" in the select statement
        String cmd = "SELECT * FROM pictures WHERE pic_id = " + pic_id + " FOR UPDATE";
        ResultSet rset = stmt.executeQuery(cmd);
        rset.next();
        BLOB myblob = ((OracleResultSet) rset).getBLOB(3);

        //Write the image to the blob object
        OutputStream outstream = myblob.getBinaryOutputStream();
        int size = myblob.getBufferSize();
        byte[] buffer = new byte[size];
        int length = -1;
        while ((length = instream.read(buffer)) != -1)
            outstream.write(buffer, 0, length);
        instream.close();
        outstream.close();

        stmt.executeUpdate("commit");
        response_message = " Upload OK!  ";
        conn.close();

    } catch (Exception ex) {
        //System.out.println( ex.getMessage());
        response_message = ex.getMessage();
    }

    //Output response to the client
    response.setContentType("text/html");
    PrintWriter out = response.getWriter();
    out.println("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.0 " + "Transitional//EN\">\n" + "<HTML>\n"
            + "<HEAD><TITLE>Upload Message</TITLE></HEAD>\n" + "<BODY>\n" + "<H1>" + response_message
            + "</H1>\n" + "</BODY></HTML>");
}

From source file:com.quartzdesk.executor.common.db.DatabaseScriptExecutor.java

/**
 * Execute the given SQL script./*from  w  w  w  .jav a 2 s. c o  m*/
 * <p>There should be one statement per line. Any {@link #setSeparator(String) statement separators}
 * will be removed.
 * <p><b>Do not use this method to execute DDL if you expect rollback.</b>
 *
 * @param connection the JDBC Connection with which to perform JDBC operations.
 * @param scriptUrl the URL of the SQL script to execute.
 */
private void executeScript(Connection connection, URL scriptUrl) throws SQLException {
    log.info("Executing SQL script: {}", scriptUrl);

    StopWatch sw = new StopWatch().start();

    List<String> statements = new LinkedList<String>();
    String script;
    try {
        script = readScript(scriptUrl);
    } catch (IOException ex) {
        throw new SQLException("Error reading SQL script: " + scriptUrl, ex);
    }
    String delimiter = separator;
    if (delimiter == null) {
        delimiter = DEFAULT_STATEMENT_SEPARATOR;
        if (!containsSqlScriptDelimiters(script, delimiter)) {
            delimiter = "\n";
        }
    }

    splitSqlScript(script, delimiter, commentPrefix, statements);

    Statement stat = null;
    try {
        stat = connection.createStatement();

        int statNumber = 0;
        for (String statStr : statements) {
            statNumber++;
            try {
                stat.execute(statStr);
                int updateCount = stat.getUpdateCount(); // rows affected
                log.debug("Update count: {} returned for SQL statement: {}", updateCount, statStr);
            } catch (SQLException ex) {
                boolean dropStatement = statStr.trim().toUpperCase().startsWith("drop");
                if (continueOnError || (dropStatement && ignoreFailedDrops)) {
                    log.debug("Failed to execute SQL statement #" + statNumber + " of SQL script " + scriptUrl
                            + ": " + statStr, ex);
                } else {
                    throw new SQLException("Failed to execute SQL statement #" + statNumber + " of SQL script "
                            + scriptUrl + ": " + statStr, ex);
                }
            }
        }
    } finally {
        if (stat != null) {
            DbUtils.close(stat);
        }

        if (commitAfterScript) {
            connection.commit();
        }
    }

    sw.stop();

    log.info("Finished executing SQL script {}. Time taken: {}", scriptUrl, sw.getFormattedElapsedTime());
}

From source file:com.extrahardmode.module.MsgPersistModule.java

/**
 * Set the count of a certain message to a certain value
 *
 * @param node     node to set the count for
 * @param playerId player for whom we are tracking the count
 * @param value    value to set/*from  w ww . ja va2  s.  com*/
 */
private void set(MessageNode node, int playerId, int value) {
    Validate.isTrue(value >= 0, "Count has to be positive");
    Connection conn = null;
    Statement statement = null;
    try {
        conn = retrieveConnection();
        statement = conn.createStatement();

        //Set the count to the provided value
        String setQuery = String.format("UPDATE %s SET %s = %s WHERE id = %s", msgTable, node.getColumnName(),
                value, playerId);
        statement.execute(setQuery);
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (conn != null)
                conn.close();
            if (statement != null)
                statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}