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.Dialect.java

protected void dropObjectsInSchema(Connection conn, String dropListSql, String schema, OBJECT_TYPE objType)
        throws SQLException {
    Statement stmt = null;
    ResultSet rs = null;/*from  w w w . j a v a  2  s  . co  m*/

    try {
        stmt = conn.createStatement();
        rs = stmt.executeQuery(dropListSql);
        String dropSql = "";

        while (rs.next()) {
            switch (objType) {
            case FK: // 
                dropSql = "ALTER TABLE " + schema + "." + rs.getString(1) + " DROP CONSTRAINT "
                        + rs.getString(2);
                break;
            case TABLE: // 
                dropSql = "DROP TABLE " + schema + "." + rs.getString(1);
                break;
            case VIEW: // 
                dropSql = "DROP VIEW " + schema + "." + rs.getString(1);
                break;
            case SEQUENCE: // 
                dropSql = "DROP SEQUENCE " + schema + "." + rs.getString(1);
                break;
            }

            stmt = conn.createStatement();
            System.err.println(dropSql);
            stmt.execute(dropSql);
        }
    } finally {
        StatementUtil.close(stmt);
    }
}

From source file:com.netflix.genie.web.controllers.ClusterRestControllerIntegrationTests.java

/**
 * Make sure can successfully delete all clusters with retries.
 *
 * @throws Exception on a configuration error
 *///from   www  .  j a va 2 s . c  o  m
@Test
public void canDeleteAllClustersWithRetry() throws Exception {
    final RetryListener retryListener = Mockito.mock(RetryListener.class);
    final Connection conn = dataSource.getConnection();
    final Statement stmt = conn.createStatement();
    try {
        Mockito.when(retryListener.open(Mockito.any(), Mockito.any())).thenReturn(true);
        final RetryListener[] retryListeners = { retryListener };
        dataServiceRetryAspect.setRetryListeners(retryListeners);
        Assert.assertThat(this.jpaClusterRepository.count(), Matchers.is(0L));
        this.createConfigResource(new Cluster.Builder(NAME, USER, VERSION, ClusterStatus.UP).build(), null);
        conn.setAutoCommit(false);
        stmt.execute("select * from clusters for update");
        this.mvc.perform(MockMvcRequestBuilders.delete(CLUSTERS_API))
                .andExpect(MockMvcResultMatchers.status().is5xxServerError());
        Mockito.verify(retryListener, Mockito.times(2)).onError(Mockito.any(), Mockito.any(), Mockito.any());
        Mockito.doAnswer(invocation -> {
            conn.commit();
            return null;
        }).when(retryListener).onError(Mockito.any(), Mockito.any(), Mockito.any());
        this.mvc.perform(MockMvcRequestBuilders.delete(CLUSTERS_API))
                .andExpect(MockMvcResultMatchers.status().isNoContent());
        Mockito.verify(retryListener, Mockito.times(3)).onError(Mockito.any(), Mockito.any(), Mockito.any());
        Assert.assertThat(this.jpaClusterRepository.count(), Matchers.is(0L));
    } finally {
        stmt.close();
        conn.commit();
        conn.close();
        dataServiceRetryAspect.setRetryListeners(new RetryListener[0]);
    }
}

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

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

    dataMemDb.runExecuteUpdate("CREATE TABLE AA1 (ID INTEGER, NAME STRING)");
    dataMemDb.runExecuteUpdate("CREATE TABLE AA2 (ID INTEGER, NAME STRING)");
    dataMemDb.runExecuteUpdate("CREATE TABLE BB1 (ID INTEGER, NAME STRING)");
    dataMemDb.runExecuteUpdate("INSERT INTO AA1 VALUES (1, 'yjchoi')");

    boolean exceptionCaught = false;
    try {//w  ww  .j a v a 2s.c  om
        checkNumRows(stmt, "AA1");
    } catch (SQLException e) {
        exceptionCaught = true;
    }
    assertTrue(exceptionCaught);

    stmt.execute("ALTER SYSTEM UPDATE TABLE \"" + dataMemDb.name + "\".\"__DEFAULT\".'AA%'");

    int rows = checkNumRows(stmt, "AA1");
    assertEquals(1, rows);

    rows = checkNumRows(stmt, "AA2");
    assertEquals(0, rows);

    exceptionCaught = false;
    try {
        checkNumRows(stmt, "BB1");
    } catch (SQLException e) {
        exceptionCaught = true;
    }
    assertTrue(exceptionCaught);

    dataMemDb.runExecuteUpdate("DROP TABLE AA1");
    dataMemDb.runExecuteUpdate("DROP TABLE AA2");
    dataMemDb.runExecuteUpdate("DROP TABLE BB1");

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

From source file:es.tekniker.framework.ktek.commons.mng.db.CommonsLoadFile.java

public static boolean loadData(List<String> data) {
    boolean boolOK = true;

    PersistentSession session = null;/*from   w  w w . j a  v  a2  s . c o m*/
    PersistentTransaction tr = null;
    Statement st;

    StringBuffer sql = null;
    String[] dataline = null;
    String tablename = null;
    boolean boolExec = false;
    try {

        session = KTEKPersistentManager.instance().getSession();

        tr = session.beginTransaction();

        try {
            st = session.connection().createStatement();
            System.out.println(data.size());
            for (int i = 0; i < data.size(); i++) {

                dataline = data.get(i).split(";");

                log.debug("data by line " + data.get(i) + " num items " + dataline.length + " data line 0 "
                        + dataline[0]);

                tablename = dataline[0];

                tablename = tablename.trim();

                sql = null;
                if (tablename.equals(TABLE_ktek_language)) {
                    log.debug("language ");
                    sql = getSQL4TABLE_LANGUAGE(dataline);
                } else if (tablename.equals(TABLE_ktek_tpsettings))
                    sql = getSQL4TABLE_TPSETTINGS(dataline);
                else if (tablename.equals(TABLE_ktek_pathology))
                    sql = getSQL4TABLE_PATHOLOGY(dataline);
                else if (tablename.equals(TABLE_ktek_translation_text))
                    sql = getSQL4TABLE_TRANSLATION_TEXT(dataline);
                else if (tablename.equals(TABLE_ktek_user))
                    sql = getSQL4TABLE_USER(dataline);
                else if (tablename.equals(TABLE_ktek_user_ext))
                    sql = getSQL4TABLE_USER_EXT(dataline);
                else if (tablename.equals(TABLE_ktek_usersessiondata))
                    sql = getSQL4TABLE_USERSESSIONDATA(dataline);
                else {
                    log.debug("table name not found " + dataline[0]);
                }

                //log.debug(sql);

                log.debug("i : " + i + " SQL : " + sql);
                if (sql != null) {
                    boolExec = st.execute(sql.toString());

                    log.debug(" executed " + boolExec);
                }
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block

            log.debug(" SQLException " + e.getMessage());
            e.printStackTrace();
            boolOK = false;
        }

        //if(boolOK)   
        tr.commit();

        session.close();

    } catch (PersistentException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return boolOK;
}

From source file:migration.ProjektMigration.java

/**
 * Creates the interessen./*  w ww . java  2s  . c  o  m*/
 */
public void createInteressen() {

    String load_sql;
    Statement load_stmt;
    ResultSet load_rs;

    String store_sql;
    PreparedStatement store_prepstmt;
    final ResultSet store_rs;

    try {

        load_sql = "SELECT Besteller, Titelnummer, Interesse FROM Interessentabelle";
        load_stmt = this.leg_con.createStatement();

        store_sql = "INSERT INTO Interesse (besteller_bestellerId, interesse, journal_id) values (?, ?, ?)";
        store_prepstmt = this.tgt_con.prepareStatement(store_sql); // evtl.
        // brauchen
        // wir
        // was
        // in
        // Richtung:
        // Statement.RETURN_GENERATED_KEYS

        // logger.info("Lese von Interessen");
        load_stmt.execute(load_sql);
        load_rs = load_stmt.getResultSet();

        // logger.info("Schreibe nach Interessen");
        while (load_rs.next()) {
            final int titelnummer = load_rs.getInt("Titelnummer");
            final int journalID = this.help.getIdFromIntArray(this.help.getJournals(), titelnummer);
            // System.out.println("Titelnummer: " + titelnummer +
            // " JournalID " + journalID);
            if ((titelnummer > 0) && (journalID > 0)) {
                store_prepstmt.setLong(1,
                        this.help.getIdFromStringArray(this.bestellers, load_rs.getString("Besteller")));
                store_prepstmt.setString(2, load_rs.getString("Interesse"));
                store_prepstmt.setLong(3, journalID);// help.getIdFromIntArray(help.getJournals(),
                // load_rs.getInt("Titelnummer")));
                store_prepstmt.executeUpdate();
            }
        }

    } catch (final SQLException e) {
        e.printStackTrace(); // To change body of catch statement use File |
                             // Settings | File Templates.
    }

    // insert into Interesse (besteller_bestellerId, interesse, journal_id)
    // values (?, ?, ?)
    // insert into Nutzung (journal_id, nutzungsjahr, rechnungsbetrag,
    // zeitraum, zugriffe) values (?, ?, ?, ?, ?)
    // insert into Rechnung (betrag, bezugsform, bezugsjahr,
    // exemplar_exemplarId, sigel_sigelId) values (?, ?, ?, ?, ?)

}

From source file:it.cnr.icar.eric.server.persistence.rdb.RegistryObjectDAO.java

/**
 * Update the status of specified objects (homogenous collection) to the
 * specified status.// w  w w.  j a va  2s  .  c o m
 * 
 * @param statusUnchanged
 *            if an id in registryObjectIds is in this ArrayList, no
 *            AuditableEvent generated for that RegistryObject
 */
public void updateStatus(RegistryObjectType ro, String status) throws RegistryException {
    Statement stmt = null;

    try {
        stmt = context.getConnection().createStatement();

        String str = "UPDATE " + getTableName() + " SET status = '" + status + "' WHERE id = '" + ro.getId()
                + "'";

        log.trace("stmt = " + str);
        stmt.execute(str);
    } catch (SQLException e) {
        log.error(ServerResourceBundle.getInstance().getString("message.CaughtException"), e);
        throw new RegistryException(e);
    } finally {
        closeStatement(stmt);
    }
}

From source file:com.mysql.stresstool.RunnableQueryInsertDR.java

public void run() {

    BufferedReader d = null;//from ww  w. j  a va2 s.c o  m
    Connection conn = null;

    try {
        if (jdbcUrlMap.get("dbType") != null && !((String) jdbcUrlMap.get("dbType")).equals("MySQL")) {
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("dbType"), "test", "test");
        } else
            conn = DriverManager.getConnection((String) jdbcUrlMap.get("jdbcUrl"));
    } catch (SQLException ex) {
        ex.printStackTrace();
    }

    if (conn != null) {

        try {

            Statement stmt = null;
            //                ResultSet rs = null;
            //                ResultSet rs2 = null;

            conn.setAutoCommit(false);
            stmt = conn.createStatement();
            stmt.execute("SET AUTOCOMMIT=0");
            long execTime = 0;
            int pkStart = 0;
            int pkEnds = 0;
            int intDeleteInterval = 0;
            int intBlobInterval = 0;
            int intBlobIntervalLimit = StressTool.getNumberFromRandom(4).intValue();
            ThreadInfo thInfo;

            long threadTimeStart = System.currentTimeMillis();
            active = true;

            thInfo = new ThreadInfo();
            thInfo.setId(this.ID);
            thInfo.setType("insert");
            thInfo.setStatusActive(this.isActive());

            StressTool.setInfo(this.ID, thInfo);
            boolean lazy = false;
            int lazyInterval = 0;

            populateLocalInfo(conn);

            for (int repeat = 0; repeat <= repeatNumber; repeat++) {
                String query = null;
                ArrayList insert1 = null;
                ArrayList insert2 = null;
                int pk = 0;

                if (repeat > 0 && lazyInterval < 500) {
                    lazy = true;
                    ++lazyInterval;
                } else {
                    lazy = false;
                    lazyInterval = 0;
                }

                intBlobInterval++;
                //IMPLEMENTING lazy
                Vector v = this.getTablesValues(lazy);

                insert1 = (ArrayList<String>) v.get(0);
                insert2 = (ArrayList<String>) v.get(1);

                //                    System.out.println(insert1);
                //                    System.out.println(insert2);

                //                    pk = ((Integer) v.get(2)).intValue();

                int[] iLine = { 0, 0 };

                //                    pkStart = StressTool.getNumberFromRandom(2147483647).intValue();
                //                    pkEnds = StressTool.getNumberFromRandom(2147483647).intValue();

                try {

                    long timeStart = System.currentTimeMillis();

                    if (this.ignoreBinlog)
                        stmt.execute("SET sql_log_bin=0");
                    stmt.execute("SET GLOBAL max_allowed_packet=1073741824");

                    if (dbType.equals("MySQL") && !engine.toUpperCase().equals("BRIGHTHOUSE"))
                        stmt.execute("BEGIN");
                    else
                        stmt.execute("COMMIT");
                    //                                stmt.execute("SET TRANSACTION NAME 'TEST'");
                    {
                        Iterator<String> it = insert1.iterator();
                        while (it.hasNext()) {
                            stmt.addBatch(it.next());
                        }
                    }

                    if (!this.doSimplePk) {
                        if (intBlobInterval > intBlobIntervalLimit) {
                            Iterator<String> it = insert2.iterator();
                            while (it.hasNext()) {
                                stmt.addBatch(it.next());
                            }
                            intBlobInterval = 0;

                        }
                    }
                    if (debug) {
                        System.out.println("Thread " + thInfo.getId() + " Executing loop "
                                + thInfo.getExecutedLoops() + " QUERY1==" + insert1);
                        System.out.println("Thread " + thInfo.getId() + " Executing loop "
                                + thInfo.getExecutedLoops() + " QUERY2==" + insert2);

                    }

                    iLine = stmt.executeBatch();
                    stmt.clearBatch();
                    //                            System.out.println("Query1 = " + insert1);
                    //                            System.out.println("Query2 = " + insert2);
                    //                            stmt.execute("START TRANSACTION");
                    //                            stmt.execute(insert1);
                    //                            iLine = stmt.executeBatch();
                    //                            conn.commit();
                    long timeEnds = System.currentTimeMillis();
                    execTime = (timeEnds - timeStart);

                } catch (Exception sqle) {
                    conn.rollback();
                    System.out.println("FAILED QUERY1==" + insert1);
                    System.out.println("FAILED QUERY2==" + insert2);
                    sqle.printStackTrace();
                    System.exit(1);
                    //conn.close();
                    //this.setJdbcUrl(jdbcUrl);
                    //System.out.println("Query Insert TH RE-INIZIALIZING");

                } finally {
                    //                           conn.commit();
                    stmt.execute("COMMIT");
                    //                            intDeleteInterval++;
                    if (doLog) {

                        System.out.println("Query Insert TH = " + this.getID() + " Loop N = " + repeat + " "
                                + iLine[0] + "|" + ((iLine.length > 1) ? iLine[1] : 0) + " Exec Time(ms) ="
                                + execTime + " Running = " + repeat + " of " + repeatNumber + " to go ="
                                + (repeatNumber - repeat) + " Using Lazy=" + lazy);
                    }
                }
                thInfo.setExecutedLoops(repeat);
                if (sleepFor > 0 || this.getSleepWrite() > 0) {
                    if (this.getSleepWrite() > 0) {
                        Thread.sleep(getSleepWrite());
                    } else
                        Thread.sleep(sleepFor);
                }

            }

            long threadTimeEnd = System.currentTimeMillis();
            this.executionTime = (threadTimeEnd - threadTimeStart);
            //                this.setExecutionTime(executionTime);
            active = false;
            //                System.out.println("Query Insert TH = " + this.getID() + " COMPLETED!  TOTAL TIME = " + execTime + "(ms) Sec =" + (execTime/1000));

            thInfo.setExecutionTime(executionTime);
            thInfo.setStatusActive(false);
            StressTool.setInfo(this.ID, thInfo);
            return;

        } catch (Exception ex) {
            ex.printStackTrace();
            try {
                conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }

}

From source file:com.openddal.test.BaseTestCase.java

/**
 * Check if two databases contain the same met data.
 *
 * @param stat1 the connection to the first database
 * @param stat2 the connection to the second database
 * @throws AssertionError if the databases don't match
 *///from   w  ww  .  j  av  a  2  s .co  m
protected void assertEqualDatabases(Statement stat1, Statement stat2) throws SQLException {
    ResultSet rs = stat1
            .executeQuery("select value from information_schema.settings " + "where name='ANALYZE_AUTO'");
    int analyzeAuto = rs.next() ? rs.getInt(1) : 0;
    if (analyzeAuto > 0) {
        stat1.execute("analyze");
        stat2.execute("analyze");
    }
    ResultSet rs1 = stat1.executeQuery("SCRIPT simple NOPASSWORDS");
    ResultSet rs2 = stat2.executeQuery("SCRIPT simple NOPASSWORDS");
    ArrayList<String> list1 = new ArrayList<String>();
    ArrayList<String> list2 = new ArrayList<String>();
    while (rs1.next()) {
        String s1 = rs1.getString(1);
        s1 = removeRowCount(s1);
        if (!rs2.next()) {
            fail("expected: " + s1);
        }
        String s2 = rs2.getString(1);
        s2 = removeRowCount(s2);
        if (!s1.equals(s2)) {
            list1.add(s1);
            list2.add(s2);
        }
    }
    for (String s : list1) {
        if (!list2.remove(s)) {
            fail("only found in first: " + s + " remaining: " + list2);
        }
    }
    Assert.assertEquals("remaining: " + list2, 0, list2.size());
    assertFalse(rs2.next());
}

From source file:com.sterlingcommerce.xpedx.webchannel.services.XPEDXGetAllReportsAction.java

public void getConnection() throws SQLException {
    String XCOM_MST_CUST = getCustomerNo(getWCContext().getBuyerOrgCode());
    String DBUrl = YFSSystem.getProperty("datasource_url");
    String DBName = YFSSystem.getProperty("datasource_name");

    //String DBUrl= "t3://localhost:7002";
    //String DBName= "SeptJNDI";
    Connection connection = null;
    Statement stmt = null;
    ResultSet rs = null;/* w ww . ja  v a2 s. c o m*/
    XPEDXReportBean rpBean = null;
    try {
        Hashtable ht = new Hashtable();
        ht.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory");
        ht.put("java.naming.provider.url", DBUrl);
        Context env = new InitialContext(ht);

        //InitialContext context = new InitialContext(ht);
        DataSource dataSource = (DataSource) env.lookup(DBName);
        connection = dataSource.getConnection();
        if (log.isDebugEnabled()) {
            log.debug("Connection successful..");
        }
        //String schemaName=YFSSystem.getProperty("schemaname");
        //String Query="select distinct RPT_CUID, RPT_NAME,RPT_ID,RPT_KIND, RPT_DESC from " + schemaName + ".xpedx_custom_rpt_dtl where XCOM_MST_CUST=" + "'"+ XCOM_MST_CUST +"'"+"AND CUST_ROLE in (";
        String Query = "select distinct RPT_CUID, RPT_NAME,RPT_ID,RPT_KIND, RPT_DESC from DH.xpedx_custom_rpt_dtl where XCOM_MST_CUST="
                + "'" + XCOM_MST_CUST + "'" + "AND CUST_ROLE in (";
        Query = getUserRole(Query);
        stmt = connection.createStatement();
        boolean test = stmt.execute(Query);
        dataExchangeReportList = new ArrayList<Report>();
        if (test == true) {
            rs = stmt.getResultSet();
            while (rs.next()) {
                Report report = new Report();
                report.setCuid(rs.getString("RPT_CUID"));
                report.setName(rs.getString("RPT_NAME"));
                report.setKind(rs.getString("RPT_KIND"));
                report.setId(rs.getInt("RPT_ID"));
                report.setDescription(rs.getString("RPT_DESC"));

                dataExchangeReportList.add(report);
            }
        }
    } catch (Exception e) {
        LOG.debug("Not able to connect to DEV Datasource:->" + e.getMessage());
    } finally {
        stmt.close();
        connection.close();
    }
}

From source file:ca.sqlpower.matchmaker.address.AddressPool.java

public void clear() throws SQLException {
    SQLTable resultTable = project.getResultTable();
    Connection con = null;//ww  w . ja  va 2  s.co  m
    Statement stmt = null;

    try {
        con = project.createResultTableConnection();
        stmt = con.createStatement();

        con.setAutoCommit(false);
        String sql = "DELETE FROM " + DDLUtils.toQualifiedName(resultTable) + " WHERE 1=1";
        stmt.execute(sql);
        con.commit();
    } catch (Exception ex) {
        if (con != null) {
            con.rollback();
        }
        if (ex instanceof SQLException) {
            throw (SQLException) ex;
        } else {
            throw new RuntimeException("An unexpected error occured while clearing the Address Pool", ex);
        }
    } finally {
        if (stmt != null)
            stmt.close();
        if (con != null)
            con.close();
    }

    addresses.clear();
}