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:jp.co.tis.gsp.tools.dba.dialect.MysqlDialect.java

@Override
public void createUser(String user, String password, String adminUser, String adminPassword)
        throws MojoExecutionException {
    Connection conn = null;/* w  ww.  ja va 2  s . co m*/
    Statement stmt = null;
    try {
        conn = DriverManager.getConnection(url, adminUser, adminPassword);
        stmt = conn.createStatement();
        String db = normalizeSchemaName(conn.getCatalog());

        if (existsUser(conn, user)) {
            // ???????????????
            stmt.execute("GRANT ALL ON " + db + ".* TO '" + user + "'");
            return;
        }
        stmt.execute("CREATE USER '" + user + "' IDENTIFIED BY '" + password + "'");
        stmt.execute("GRANT ALL ON " + db + ".* TO '" + user + "'");
    } catch (SQLException e) {
        throw new MojoExecutionException("CREATE USER?", e);
    } finally {
        StatementUtil.close(stmt);
        ConnectionUtil.close(conn);
    }
}

From source file:coral.reef.service.ReefCoralDAO.java

public ReefCoralDAO(String dbname, boolean setup, String dbmode, String dbprov, String dbdriver) {

    try {//from   w  ww. j a v a2  s .c  om
        Class.forName(dbdriver);

        String dbconnectstr = "jdbc:" + dbprov + ":" + dbmode + ":" + dbname;
        logger.info("setup/connect to db: " + dbconnectstr);

        conn = DriverManager.getConnection(dbconnectstr, "sa", // username
                "");

        if (!setup) {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet res = meta.getTables(null, null, "DATAS", new String[] { "TABLE" });
            setup = !res.next();
        }

        if (!setup) {
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet res = meta.getTables(null, null, "STATES", new String[] { "TABLE" });
            setup = !res.next();
        }

        if (dbmode.equals("mem") || setup) {
            logger.info("SETUP DATABASE");

            Statement stat = conn.createStatement();
            // stat.execute("delete from datas;");
            // stat.execute("delete from states;");
            stat.execute("drop table if exists DATAS;");
            stat.execute(
                    "CREATE TABLE DATAS ( id BIGINT, collection VARCHAR(10), name VARCHAR(80), value VARCHAR(1024));");
            stat.execute("CREATE INDEX IDATAS ON DATAS ( id, name );");
            stat.execute("drop table if exists STATES;");
            stat.execute(
                    "CREATE TABLE STATES ( id BIGINT, collection VARCHAR(10), template VARCHAR(80), block INTEGER, round INTEGER, stage INTEGER, msg VARCHAR(1024));");
            stat.execute("COMMIT");

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

    } catch (ClassNotFoundException e) {
        e.printStackTrace();
    } catch (SQLException e) {
        e.printStackTrace();
    }

}

From source file:ca.sqlpower.architect.swingui.TestPlayPen.java

/**
 * This tests that copying and pasting a table from one session to another
 * session within the same context will add the data source to the new session
 * and add the table properly.//from   www  .  j a  va 2s. c  o  m
 */
public void testPasteTableAcrossSessions() throws Exception {
    ArchitectSwingSessionContext context = pp.getSession().getContext();
    context.setPlDotIniPath("pl.regression.ini");
    DataSourceCollection<JDBCDataSource> pl = context.getPlDotIni();
    JDBCDataSource ds = pl.getDataSource("regression_test");
    Connection con = ds.createConnection();
    Statement stmt = con.createStatement();
    stmt.execute("Create table newtable (newcol1 varchar(50), newcol2 varchar(50))");
    stmt.close();
    con.close();

    SQLDatabase db = new SQLDatabase(ds);
    SQLTable table = db.getTableByName("newtable");

    //New play pen in same context
    PlayPen newPP = pp.getSession().getContext().createSession().getPlayPen();

    DuplicateProperties duplicateProperties = ASUtils.createDuplicateProperties(newPP.getSession(), table);
    newPP.importTableCopy(table, new Point(0, 0), duplicateProperties);
    assertEquals(1, newPP.getTables().size());
    SQLTable copy = newPP.getTables().get(0);
    assertEquals("NEWTABLE", copy.getName().toUpperCase());
    assertEquals(2, copy.getColumns().size());
    assertTrue(copy.getColumnByName("NewCol1") != null);
    assertTrue(copy.getColumnByName("NewCol2") != null);
    assertTrue(table.getColumnByName("newcol1") != copy.getColumnByName("NewCol1").getSourceColumn());
    assertTrue(table.getColumnByName("newcol2") != copy.getColumnByName("NewCol2").getSourceColumn());
}

From source file:com.xpn.xwiki.internal.store.hibernate.HibernateStore.java

/**
 * Execute an SQL statement using Hibernate.
 *
 * @param sql the SQL statement to execute
 * @param session the Hibernate Session in which to execute the statement
 *///w  w w  . ja v  a2 s  . c o  m
private void executeSQL(final String sql, Session session) {
    session.doWork(new Work() {
        @Override
        public void execute(Connection connection) throws SQLException {
            Statement stmt = null;
            try {
                stmt = connection.createStatement();
                stmt.execute(sql);
            } finally {
                try {
                    if (stmt != null) {
                        stmt.close();
                    }
                } catch (Exception e) {
                }
            }
        }
    });
}

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

/**
 * Liest einen Einzelwert aus einer Tabellenabfrage
 * /*from   w w  w .j  a  va2  s.  c o  m*/
 * Beispiel: | Execute SQL | ACSD_FULL | DELETE FROM table |
 */
public void executeSQL(String dsName, String sql) throws IllegalArgumentException, SQLException {
    Statement statement = null;
    try {
        Connection con = getConnection(dsName);
        statement = con.createStatement();
        statement.execute(sql);
        con.commit();

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

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 = "lingbo";
    String password = "TlboSci1994";
    String drivername = "oracle.jdbc.driver.OracleDriver";
    String dbstring = "jdbc:oracle:thin:@gwynne.cs.ualberta.ca:1521:CRS";
    int pic_id;//from   w w  w . j av a 2 s .c  om

    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();

        BufferedImage img = ImageIO.read(instream);
        BufferedImage thumbNail = shrink(img, 10);

        // 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.setBinaryStream(1);
        ImageIO.write(thumbNail, "jpg", outstream);

        /*
        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.npstrandberg.simplemq.MessageQueueImp.java

private void setWriteDelay() {
    if (queueConfig instanceof PersistentMessageQueueConfig) {
        PersistentMessageQueueConfig pqc = (PersistentMessageQueueConfig) queueConfig;

        try {//from  w  ww . j ava2s .  co  m
            Statement st = conn.createStatement();

            st.execute("SET WRITE_DELAY " + pqc.getDatabaseWriteDelay() + " MILLIS");
            st.close();
        } catch (SQLException e) {
            logger.error(e);
        }
    }
}

From source file:com.npstrandberg.simplemq.MessageQueueImp.java

private void shutdown() {
    try {//from   w w w.j  a  v a  2  s. c  o  m
        Statement st = conn.createStatement();
        st.execute("SHUTDOWN");
        st.close();
        conn.close();
    } catch (SQLException e) {
        logger.error(e);
    }

    // shutdown schedular
    shutdownAndAwaitTermination(scheduler);

    // unreg shutdown thread
    if (shutdownThread != null) {
        Runtime.getRuntime().removeShutdownHook(shutdownThread);
        shutdownThread = null;
    }
}

From source file:com.ikanow.aleph2.search_service.elasticsearch.utils.TestElasticsearchHiveUtils.java

@Test
public void test_registerHiveTable() throws SQLException {

    // Basic end-end success
    {//from   w ww . j  a  va  2s .co  m
        final Statement mock_statement_works = Mockito.mock(Statement.class);
        Mockito.when(mock_statement_works.execute(Mockito.anyString())).thenReturn(true);

        final Connection mock_cxn_works = Mockito.mock(Connection.class);
        Mockito.when(mock_cxn_works.createStatement()).thenReturn(mock_statement_works);

        Validation<String, Boolean> res = ElasticsearchHiveUtils.registerHiveTable(Optional.of(mock_cxn_works),
                new Configuration(), Optional.of("dummy delete"), Optional.of("dummy create"));

        assertTrue("Should succeed: " + res.f().toArray(), res.isSuccess());
        assertTrue(res.success());
    }
    // Check only executes the delete if the create is not specified
    {
        final Statement mock_statement_works = Mockito.mock(Statement.class);
        Mockito.when(mock_statement_works.execute("dummy delete")).thenReturn(true);

        final Connection mock_cxn_works = Mockito.mock(Connection.class);
        Mockito.when(mock_cxn_works.createStatement()).thenReturn(mock_statement_works);

        Validation<String, Boolean> res = ElasticsearchHiveUtils.registerHiveTable(Optional.of(mock_cxn_works),
                new Configuration(), Optional.of("dummy delete"), Optional.empty());

        assertTrue("Should succeed: " + res.f().toArray(), res.isSuccess());
        assertTrue(res.success());
    }
    // Check only executes the delete if the create is not specified - else returns false
    {
        final Statement mock_statement_works = Mockito.mock(Statement.class);
        Mockito.when(mock_statement_works.execute("dummy delete")).thenReturn(true);

        final Connection mock_cxn_works = Mockito.mock(Connection.class);
        Mockito.when(mock_cxn_works.createStatement()).thenReturn(mock_statement_works);

        Validation<String, Boolean> res = ElasticsearchHiveUtils.registerHiveTable(Optional.of(mock_cxn_works),
                new Configuration(), Optional.of("dummy delete"), Optional.of("dummy create"));

        assertTrue("Should succeed: " + res.f().toArray(), res.isSuccess());
        assertFalse(res.success());
    }
    // Check error case #1
    {
        final Statement mock_statement_works = Mockito.mock(Statement.class);
        Mockito.when(mock_statement_works.execute("dummy delete")).thenThrow(new RuntimeException("fail"));
        Mockito.when(mock_statement_works.execute("dummy create")).thenReturn(true);

        final Connection mock_cxn_works = Mockito.mock(Connection.class);
        Mockito.when(mock_cxn_works.createStatement()).thenReturn(mock_statement_works);

        Validation<String, Boolean> res = ElasticsearchHiveUtils.registerHiveTable(Optional.of(mock_cxn_works),
                new Configuration(), Optional.of("dummy delete"), Optional.empty());

        assertTrue(res.isFail());
    }
    // Check error case #2
    {
        final Statement mock_statement_works = Mockito.mock(Statement.class);
        Mockito.when(mock_statement_works.execute("dummy create")).thenThrow(new RuntimeException("fail"));

        final Connection mock_cxn_works = Mockito.mock(Connection.class);
        Mockito.when(mock_cxn_works.createStatement()).thenReturn(mock_statement_works);

        Validation<String, Boolean> res = ElasticsearchHiveUtils.registerHiveTable(Optional.of(mock_cxn_works),
                new Configuration(), Optional.of("dummy delete"), Optional.of("dummy create"));

        assertTrue(res.isFail());
    }
    // Check error case #3
    {
        final Statement mock_statement_works = Mockito.mock(Statement.class);
        Mockito.when(mock_statement_works.execute("dummy delete")).thenThrow(new RuntimeException("fail"));
        Mockito.when(mock_statement_works.execute("dummy create")).thenReturn(true);

        final Connection mock_cxn_works = Mockito.mock(Connection.class);
        Mockito.when(mock_cxn_works.createStatement()).thenReturn(mock_statement_works);

        Validation<String, Boolean> res = ElasticsearchHiveUtils.registerHiveTable(Optional.of(mock_cxn_works),
                new Configuration(), Optional.of("dummy delete"), Optional.of("dummy create"));

        assertTrue(res.isFail());
    }
}

From source file:com.npstrandberg.simplemq.MessageQueueImp.java

private void createTableAndIndex() throws SQLException {

    // The table may allready exsists if the queue is persistent.
    if (tableExists("message"))
        return;/*from   ww  w .j a v  a  2  s  . co  m*/

    String cached = "";

    if (queueConfig instanceof PersistentMessageQueueConfig) {
        PersistentMessageQueueConfig pqc = (PersistentMessageQueueConfig) queueConfig;

        if (pqc.isCached()) {
            cached = "CACHED ";
        }
    }

    Statement st = conn.createStatement();

    st.execute("CREATE " + cached
            + "TABLE message (id BIGINT IDENTITY PRIMARY KEY, object LONGVARBINARY, body VARCHAR, time BIGINT, read BOOLEAN)");
    st.execute("CREATE INDEX id_index ON message(id)");
    st.execute("CREATE INDEX time_index ON message(time)");
    st.execute("CREATE INDEX read_index ON message(read)");
    st.close();
}