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:au.com.ish.derbydump.derbydump.main.DumpTest.java

@Test
public void theDumpTest() throws Exception {
    // Create table
    StringBuilder createTableBuffer = new StringBuilder();
    createTableBuffer.append("CREATE TABLE ");
    createTableBuffer.append(Configuration.getConfiguration().getSchemaName());
    createTableBuffer.append(".");
    createTableBuffer.append(tableName);
    createTableBuffer.append(" (");

    StringBuilder insertBuffer = new StringBuilder();
    insertBuffer.append("INSERT INTO ");
    insertBuffer.append(RESOURCE_SCHEMA_NAME);
    insertBuffer.append(".");
    insertBuffer.append(tableName);//w ww  .j a v a  2  s  . c  om
    insertBuffer.append(" VALUES (");

    for (String col : columns) {
        createTableBuffer.append(col.toUpperCase());
        //String[] c = col.split(" ");
        //insertBuffer.append(c[0].toUpperCase().trim());
        insertBuffer.append("?");
        if (!columns[columns.length - 1].equals(col)) {
            createTableBuffer.append(", ");
            insertBuffer.append(",");
        }
    }

    createTableBuffer.append(")");
    insertBuffer.append(")");

    config.setTableRewriteProperty("testSkip", "--exclude--");
    config.setTableRewriteProperty("testRename", "testRenameNew");
    config.setTruncateTables(truncate);

    File f = new File("./build/outputs/" + tableName + ".sql");
    if (f.exists()) {
        f.delete();
    }
    f.mkdirs();

    config.setOutputFilePath(f.getCanonicalPath());

    Connection connection = db.createNewConnection();
    Statement statement = connection.createStatement();
    PreparedStatement ps = null;

    try {
        statement.execute(createTableBuffer.toString());
        connection.commit();
        //config.setTableRewriteProperty("TABLE2", "--exclude--");

        for (Object o : valuesToInsert) {
            Object[] vals = (Object[]) o;
            if (vals.length > 0) {
                ps = db.getConnection().prepareStatement(insertBuffer.toString());
                for (int i = 0; i < vals.length; i++) {
                    if (vals[i] instanceof InputStream) {
                        ps.setBinaryStream(i + 1, (InputStream) vals[i]);
                    } else {
                        ps.setObject(i + 1, vals[i]);
                    }
                }
                ps.execute();
                connection.commit();
            }
        }

        OutputThread output = new OutputThread();
        Thread writer = new Thread(output, "File_Writer");
        writer.start();

        new DatabaseReader(output);
        // Let the writer know that no more data is coming
        writer.interrupt();
        writer.join();

        // Now let's read the output and see what is in it
        List<String> lines = FileUtils.readLines(f);

        assertEquals("Missing foreign key operations", "SET FOREIGN_KEY_CHECKS = 0;", lines.get(0));
        assertEquals("Missing foreign key operations", "SET FOREIGN_KEY_CHECKS = 1;",
                lines.get(lines.size() - 1));

        if (!skipped) {
            assertTrue("LOCK missing", lines.contains("LOCK TABLES `" + outputTableName + "` WRITE;"));
            assertTrue("UNLOCK missing", lines.contains("UNLOCK TABLES;"));

            int index = lines.indexOf("LOCK TABLES `" + outputTableName + "` WRITE;");

            if (truncate) {
                assertTrue("TRUNCATE missing", lines.contains("TRUNCATE TABLE " + outputTableName + ";"));
                assertTrue("INSERT missing, got " + lines.get(index + 2),
                        lines.get(index + 2).startsWith("INSERT INTO " + outputTableName));
            } else {
                assertTrue("INSERT missing, got " + lines.get(index + 1),
                        lines.get(index + 1).startsWith("INSERT INTO " + outputTableName));
            }

            for (String s : validOutputs) {
                assertTrue("VALUES missing :" + s, lines.contains(s));
            }
        } else {
            assertTrue("LOCK missing", !lines.contains("LOCK TABLES `" + outputTableName + "` WRITE;"));
        }
    } catch (Exception e) {
        e.printStackTrace();
        fail("failed to create test data" + e.getMessage());
    } finally {
        if (ps != null) {
            ps.close();
        }
        statement.close();
        connection.close();
    }
}

From source file:hmp.HMPReadFilterer.java

private int createRunInDatabase(Statement s, String runDate) throws SQLException {
    System.out.println(runDate + " not found, creating");
    s.execute("insert into run (date) values (\"" + runDate + "\")");
    ResultSet rs = s.executeQuery("select run_id from run where date = '" + runDate + "'");
    while (rs.next()) {
        return rs.getInt("run_id");
    }//  www .ja  v  a  2  s.  c  o m
    return 0;
}

From source file:com.flexive.core.storage.genericSQL.GenericTreeStorageSimple.java

/**
 * {@inheritDoc}/* w  ww .j a  v a 2s  .c  om*/
 */
@Override
protected void wipeTree(FxTreeMode mode, Statement stmt, FxPK rootPK) throws SQLException {
    stmt.execute(StorageManager.getReferentialIntegrityChecksStatement(false));
    try {
        stmt.executeUpdate("DELETE FROM " + getTable(mode));
        stmt.executeUpdate("INSERT INTO " + getTable(mode)
                + " (ID,NAME,MODIFIED_AT,DIRTY,PARENT,DEPTH,CHILDCOUNT,REF,TEMPLATE,LFT,RGT) " + "VALUES ("
                + ROOT_NODE + ",'Root'," + StorageManager.getTimestampFunction() + ",FALSE,NULL,1,0,0,"
                + rootPK.getId() + ",NULL,1,2)");
    } finally {
        stmt.executeUpdate(StorageManager.getReferentialIntegrityChecksStatement(true));
    }
}

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

@Before
public void setup() throws Exception {
    File folder = new File("/tmp");
    File[] files = folder.listFiles(new FilenameFilter() {
        @Override//from www  .  j  av a2s. c om
        public boolean accept(final File dir, final String name) {
            return name.startsWith("test.db");
        }
    });
    for (final File file : files) {
        if (!file.delete()) {
            System.out.println("Failed to remove " + file.getAbsolutePath());
        }
    }

    mongo.dropDatabase(DB_NAME);
    mongo.dropDatabase("local");
    mongo.dropDatabase("admin");
    mongo.dropDatabase("mongo");
    mongo.getDB(DB_NAME).dropDatabase();
    mongo.getDB("local").dropDatabase();
    mongo.getDB("admin").dropDatabase();
    mongo.getDB("mongo").dropDatabase();

    db.getCollection(MongoMetadata.DEFAULT_METADATA_COLLECTION).remove(new BasicDBObject());
    mongo.getDB("mongo").getCollection("metadata").remove(new BasicDBObject());

    db.createCollection(MongoMetadata.DEFAULT_METADATA_COLLECTION, null);
    BasicDBObject index = new BasicDBObject("name", 1);
    index.put("version.value", 1);
    db.getCollection(MongoMetadata.DEFAULT_METADATA_COLLECTION).ensureIndex(index, "name", true);

    if (notRegistered) {
        notRegistered = false;
        try {
            // Create initial context
            System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.apache.naming.java.javaURLContextFactory");
            System.setProperty(Context.URL_PKG_PREFIXES, "org.apache.naming");
            // already tried System.setProperty(Context.INITIAL_CONTEXT_FACTORY, "org.jboss.as.naming.InitialContextFactory");
            InitialContext ic = new InitialContext();

            ic.createSubcontext("java:");
            ic.createSubcontext("java:/comp");
            ic.createSubcontext("java:/comp/env");
            ic.createSubcontext("java:/comp/env/jdbc");

            JdbcConnectionPool ds = JdbcConnectionPool.create(
                    "jdbc:h2:file:/tmp/test.db;FILE_LOCK=NO;MVCC=TRUE;DB_CLOSE_ON_EXIT=TRUE", "sa", "sasasa");

            ic.bind("java:/mydatasource", ds);
        } catch (NamingException ex) {
            throw new IllegalStateException(ex);
        }
    } else {
        Context initCtx = new InitialContext();
        DataSource ds = (DataSource) initCtx.lookup("java:/mydatasource");
        Connection conn = ds.getConnection();
        Statement stmt = conn.createStatement();
        stmt.execute("DROP ALL OBJECTS ");
        stmt.close();
    }
}

From source file:jp.co.tis.gsp.tools.dba.dialect.OracleDialect.java

private void createDirectory(String user, String password, File directory) throws SQLException {
    Connection conn = null;/*ww w.  ja v  a 2 s  .c om*/
    Statement stmt = null;
    try {
        conn = DriverManager.getConnection(url, user, password);
        stmt = conn.createStatement();
        stmt.execute("CREATE OR REPLACE DIRECTORY exp_dir as '" + directory.getAbsolutePath() + "'");
    } finally {
        StatementUtil.close(stmt);
        ConnectionUtil.close(conn);
    }
}

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

public void alterColumn(String tableName, String columnName, String newType) throws SQLException {

    Statement stmt = connection.createStatement();
    try {/*w w w  . j a  v a  2s.  com*/
        String sql = "ALTER TABLE \"" + tableName + "\" ALTER COLUMN \"" + columnName + "\" " + newType;
        stmt.execute(sql);
        log.debug(
                "Modified column '" + columnName + "' in table '" + tableName + "' to type '" + newType + "'");
    } finally {
        stmt.close();
    }

}

From source file:com.cloudera.sqoop.manager.SQLServerManagerExportManualTest.java

public void createSQLServerBinaryTypeTable(String schema, String table) {
    String fulltableName = manager.escapeObjectName(schema) + "." + manager.escapeObjectName(table);

    Statement stmt = null;

    // Create schema if needed
    try {/*www .ja  v  a  2s .co  m*/
        conn = manager.getConnection();
        stmt = conn.createStatement();
        stmt.execute("CREATE SCHEMA " + schema);
        conn.commit();
    } catch (SQLException sqlE) {
        LOG.info("Can't create schema: " + sqlE.getMessage());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
        } catch (Exception ex) {
            LOG.warn("Exception while closing stmt", ex);
        }
    }

    // Drop the existing table, if there is one.
    try {
        conn = manager.getConnection();
        stmt = conn.createStatement();
        stmt.execute("DROP TABLE " + fulltableName);
        conn.commit();
    } catch (SQLException sqlE) {
        LOG.info("Table was not dropped: " + sqlE.getMessage());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
        } catch (Exception ex) {
            LOG.warn("Exception while closing stmt", ex);
        }
    }

    // Create and populate table
    try {
        conn = manager.getConnection();
        conn.setAutoCommit(false);
        stmt = conn.createStatement();

        // create the database table and populate it with data.
        stmt.executeUpdate("CREATE TABLE " + fulltableName + " (" + "id INT PRIMARY KEY, " + "b1 BINARY(10), "
                + "b2 VARBINARY(10))");
        conn.commit();
    } catch (SQLException sqlE) {
        LOG.error("Encountered SQL Exception: ", sqlE);
        sqlE.printStackTrace();
        fail("SQLException when running test setUp(): " + sqlE);
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
        } catch (Exception ex) {
            LOG.warn("Exception while closing connection/stmt", ex);
        }
    }
}

From source file:com.cloudera.sqoop.manager.SQLServerManagerExportManualTest.java

public void createTableAndPopulateData(String schema, String table) {
    String fulltableName = manager.escapeObjectName(schema) + "." + manager.escapeObjectName(table);

    Statement stmt = null;

    // Create schema if needed
    try {/*from   ww w.  j  a v a2  s.  co  m*/
        conn = manager.getConnection();
        stmt = conn.createStatement();
        stmt.execute("CREATE SCHEMA " + schema);
        conn.commit();
    } catch (SQLException sqlE) {
        LOG.info("Can't create schema: " + sqlE.getMessage());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
        } catch (Exception ex) {
            LOG.warn("Exception while closing stmt", ex);
        }
    }

    // Drop the existing table, if there is one.
    try {
        conn = manager.getConnection();
        stmt = conn.createStatement();
        stmt.execute("DROP TABLE " + fulltableName);
        conn.commit();
    } catch (SQLException sqlE) {
        LOG.info("Table was not dropped: " + sqlE.getMessage());
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
        } catch (Exception ex) {
            LOG.warn("Exception while closing stmt", ex);
        }
    }

    // Create and populate table
    try {
        conn = manager.getConnection();
        conn.setAutoCommit(false);
        stmt = conn.createStatement();

        // create the database table and populate it with data.
        stmt.executeUpdate(
                "CREATE TABLE " + fulltableName + " (" + "id INT NOT NULL, " + "name VARCHAR(24) NOT NULL, "
                        + "salary FLOAT, " + "dept VARCHAR(32), " + "PRIMARY KEY (id))");
        conn.commit();
    } catch (SQLException sqlE) {
        LOG.error("Encountered SQL Exception: ", sqlE);
        sqlE.printStackTrace();
        fail("SQLException when running test setUp(): " + sqlE);
    } finally {
        try {
            if (null != stmt) {
                stmt.close();
            }
        } catch (Exception ex) {
            LOG.warn("Exception while closing connection/stmt", ex);
        }
    }
}

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

/**
 * This method tries to delete all associations in root and parent tables
 * @throws SQLException if there was an error during deletion
 *///from   w w w  .ja  v a  2 s .c o m
private void dropAssociations(Connection connection) throws SQLException {
    Statement stmt = connection.createStatement();
    stmt.execute("DELETE FROM parent WHERE TRUE; DELETE FROM root WHERE TRUE;");
    stmt.close();
}