Example usage for java.sql Statement getResultSet

List of usage examples for java.sql Statement getResultSet

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Statement
  6. getResultSet

Introduction

In this page you can find the example usage for java.sql Statement getResultSet.

Prototype

ResultSet getResultSet() throws SQLException;

Source Link

Document

Retrieves the current result as a ResultSet object.

Usage

From source file:org.apache.hadoop.hive.jdbc.TestJdbcDriver.java

private void doTestSelectAll(String tableName, int maxRows, int fetchSize) throws Exception {
    boolean isPartitionTable = tableName.equals(partitionedTableName);

    Statement stmt = con.createStatement();
    if (maxRows >= 0) {
        stmt.setMaxRows(maxRows);/*from  www.  j  a  v  a 2s . c  o m*/
    }
    if (fetchSize > 0) {
        stmt.setFetchSize(fetchSize);
        assertEquals(fetchSize, stmt.getFetchSize());
    }

    // JDBC says that 0 means return all, which is the default
    int expectedMaxRows = maxRows < 1 ? 0 : maxRows;

    assertNotNull("Statement is null", stmt);
    assertEquals("Statement max rows not as expected", expectedMaxRows, stmt.getMaxRows());
    assertFalse("Statement should not be closed", stmt.isClosed());

    ResultSet res;

    // run some queries
    res = stmt.executeQuery("select * from " + tableName);
    assertNotNull("ResultSet is null", res);
    assertTrue("getResultSet() not returning expected ResultSet", res == stmt.getResultSet());
    assertEquals("get update count not as expected", 0, stmt.getUpdateCount());
    int i = 0;

    ResultSetMetaData meta = res.getMetaData();
    int expectedColCount = isPartitionTable ? 3 : 2;
    assertEquals("Unexpected column count", expectedColCount, meta.getColumnCount());

    String colQualifier = ((tableName != null) && !tableName.isEmpty()) ? tableName.toLowerCase() + "." : "";
    boolean moreRow = res.next();
    while (moreRow) {
        try {
            i++;
            assertEquals(res.getInt(1), res.getInt(colQualifier + "under_col"));
            assertEquals(res.getString(1), res.getString(colQualifier + "under_col"));
            assertEquals(res.getString(2), res.getString(colQualifier + "value"));
            if (isPartitionTable) {
                assertEquals(res.getString(3), partitionedColumnValue);
                assertEquals(res.getString(3), res.getString(colQualifier + partitionedColumnName));
            }
            assertFalse("Last result value was not null", res.wasNull());
            assertNull("No warnings should be found on ResultSet", res.getWarnings());
            res.clearWarnings(); // verifying that method is supported

            // System.out.println(res.getString(1) + " " + res.getString(2));
            assertEquals("getInt and getString don't align for the same result value",
                    String.valueOf(res.getInt(1)), res.getString(1));
            assertEquals("Unexpected result found", "val_" + res.getString(1), res.getString(2));
            moreRow = res.next();
        } catch (SQLException e) {
            System.out.println(e.toString());
            e.printStackTrace();
            throw new Exception(e.toString());
        }
    }

    // supposed to get 500 rows if maxRows isn't set
    int expectedRowCount = maxRows > 0 ? maxRows : 500;
    assertEquals("Incorrect number of rows returned", expectedRowCount, i);

    // should have no more rows
    assertEquals(false, moreRow);

    assertNull("No warnings should be found on statement", stmt.getWarnings());
    stmt.clearWarnings(); // verifying that method is supported

    assertNull("No warnings should be found on connection", con.getWarnings());
    con.clearWarnings(); // verifying that method is supported

    stmt.close();
    assertTrue("Statement should be closed", stmt.isClosed());
}

From source file:mom.trd.opentheso.bdd.helper.GroupHelper.java

/**
 * Cette fonction permet de savoir si l'identifiant est un identifiant de
 * Groupe ou non//from w w  w .  j  a va  2  s  .  c  om
 *
 * @param ds
 * @param idGroup
 * @param idThesaurus
 * @return boolean
 */
public boolean isIdOfGroup(HikariDataSource ds, String idGroup, String idThesaurus) {

    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    boolean existe = false;

    try {
        // Get connection from pool
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "select idgroup from concept_group where " + " idgroup = '" + idGroup + "'"
                        + " and idthesaurus = '" + idThesaurus + "'";
                stmt.executeQuery(query);
                resultSet = stmt.getResultSet();
                resultSet.next();
                existe = (resultSet.getRow() != 0);
            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        log.error("Error while Asking if Is Id of Group : " + idGroup, sqle);
    }
    return existe;
}

From source file:de.uniwue.info6.database.jdbc.ConnectionManager.java

/**
 *
 *
 * @param scenario//  ww  w .  j  a  va 2s.com
 * @param user
 * @param table
 * @return
 *
 * @throws SQLException
 */
public synchronized String getTableChecksum(Scenario scenario, User user, String table) throws SQLException {
    if (scenario != null) {
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            connection = instance.getConnection(scenario);
            statement = connection.createStatement();

            if (user == null) {
                statement.execute("CHECKSUM TABLE " + table);
            } else {
                statement.execute("CHECKSUM TABLE `" + user.getId().toLowerCase().trim() + "_" + table + "`");
            }
            resultSet = statement.getResultSet();

            if (resultSet.next()) {
                return resultSet.getString(2);
            }

        } catch (Exception e) {
            LOGGER.error("PROBLEM GETTING TABLE CHECKSUM", e);
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    return null;
}

From source file:je3.rmi.MudClient.java

 /** Open an account */
 public synchronized void openAccount(String name, String password)
throws RemoteException, BankingException
 {
     // First, check if there is already an account with that name
     Statement s = null;
     try { /*w  w w .jav a 2s  .c o  m*/
         s = db.createStatement();
         s.executeQuery("SELECT * FROM accounts WHERE name='" + name + "'");
         ResultSet r = s.getResultSet();
         if (r.next()) throw new BankingException("Account name in use.");
       
         // If it doesn't exist, go ahead and create it Also, create a
         // table for the transaction history of this account and insert an
         // initial transaction into it.
         s = db.createStatement();
         s.executeUpdate("INSERT INTO accounts VALUES ('" + name + "', '" +
          password + "', 0)");
         s.executeUpdate("CREATE TABLE " + name + 
          "_history (msg VARCHAR(80))");
         s.executeUpdate("INSERT INTO " + name + "_history " +
          "VALUES ('Account opened at " + new Date() + "')");
       
         // And if we've been successful so far, commit these updates,
         // ending the atomic transaction.  All the methods below also use
         // this atomic transaction commit/rollback scheme
         db.commit();
     }
     catch(SQLException e) {
         // If an exception was thrown, "rollback" the prior updates,
         // removing them from the database.  This also ends the atomic
         // transaction.
         try { db.rollback(); } catch (Exception e2) {}
         // Pass the SQLException on in the body of a BankingException
         throw new BankingException("SQLException: " + e.getMessage() + 
                ": " + e.getSQLState());
     }
     // No matter what happens, don't forget to close the DB Statement
     finally { try { s.close(); } catch (Exception e) {} }
 }

From source file:mom.trd.opentheso.bdd.helper.GroupHelper.java

/**
 * Permet de retourner la liste des Groupes pour un Concept et un thsaurus
 * donn/*www. j  av  a 2 s .c  o  m*/
 *
 * @param ds le pool de connexion
 * @param idThesaurus
 * @param idConcept
 * @return Objet Class ArrayList NodeConceptGroup
 */
public ArrayList<String> getListIdGroupOfConcept(HikariDataSource ds, String idThesaurus, String idConcept) {

    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    ArrayList tabIdConceptGroup = null;

    try {
        // Get connection from pool
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "select id_group from concept where id_thesaurus = '" + idThesaurus + "'"
                        + " and id_concept = '" + idConcept + "'";

                stmt.executeQuery(query);
                resultSet = stmt.getResultSet();
                if (resultSet != null) {
                    tabIdConceptGroup = new ArrayList<>();
                    while (resultSet.next()) {
                        tabIdConceptGroup.add(resultSet.getString("id_group"));
                    }
                }

            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        log.error("Error while getting List Id or Groups of Concept : " + idConcept, sqle);
    }
    return tabIdConceptGroup;
}

From source file:migration.ProjektMigration.java

/**
 * Creates the besteller./*from www  .  j a  v a  2 s  .c o  m*/
 * 
 * @param con
 *            the con
 */
public void createBesteller(final Connection con) {

    String load_sql;
    Statement load_stmt;
    ResultSet load_rs;

    String store_sql;
    PreparedStatement store_prepstmt;
    final ResultSet store_rs;

    try {
        // insert into Besteller (anrede, bestellerName,
        // einzahlungErwuenscht, einzahlungFestgelegt, funktion, projekt,
        // sigel_sigelId) values (?, ?, ?, ?, ?, ?, ?)

        load_sql = "SELECT Besteller, AnredeKuv, Einzahlungerwuenscht, Einzahlungfestgelegt, Sigel, Projekt, Funktion FROM Bestellertabelle";
        load_stmt = this.leg_con.createStatement();

        store_sql = "INSERT INTO Besteller (anrede, bestellerName, einzahlungErwuenscht, einzahlungFestgelegt, funktion, projekt, sigel_sigelId) VALUES (?, ?, ?, ?, ?, ?, ?)";
        store_prepstmt = this.tgt_con.prepareStatement(store_sql); // evtl.
        // brauchen
        // wir
        // was
        // in
        // Richtung:
        // Statement.RETURN_GENERATED_KEYS
        final int laenge = this.help.sqlGetLength(con, load_sql);
        this.bestellers = new String[laenge];
        this.bestellers_sigels = new int[laenge];
        // logger.info("Lese von Besteller");
        load_stmt.execute(load_sql);
        load_rs = load_stmt.getResultSet();

        // logger.info("Schreibe nach Besteller");
        for (int i = 0; i < laenge; i++) {
            load_rs.next();
            store_prepstmt.setString(1, load_rs.getString("AnredeKuv"));
            this.bestellers[i] = load_rs.getString("Besteller");
            store_prepstmt.setString(2, this.bestellers[i]);
            store_prepstmt.setFloat(3, load_rs.getFloat("Einzahlungerwuenscht"));
            store_prepstmt.setFloat(4, load_rs.getFloat("Einzahlungfestgelegt"));
            store_prepstmt.setString(5, load_rs.getString("Funktion"));
            store_prepstmt.setString(6, load_rs.getString("Projekt"));
            this.bestellers_sigels[i] = this.help.getIdFromStringArray(this.help.getSigel(),
                    load_rs.getString("Sigel"));
            store_prepstmt.setInt(7, this.bestellers_sigels[i]);
            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:mom.trd.opentheso.bdd.helper.RelationsHelper.java

/**
 * Cette fonction permet de rcuprer les termes spcifiques d'un concept
 *
 * @param ds//from   w w w . j  a v a2 s  .c o  m
 * @param idConcept
 * @param idThesaurus
 * @param idLang
 * @return Objet class Concept
 */
/*public ArrayList <NodeNT> getListNT(HikariDataSource ds,
 String idConcept, String idThesaurus, String idLang) {
        
 Connection conn;
 Statement stmt;
 ResultSet resultSet;
 ArrayList<NodeNT> nodeListNT = null;
        
 try {
 // Get connection from pool
 conn = ds.getConnection();
 try {
 stmt = conn.createStatement();
 try {
 String query = "SELECT term.lexical_value, " +
 " preferred_term.id_concept, concept.status" +
 " FROM term,preferred_term,concept,hierarchical_relationship" +
 " WHERE preferred_term.id_term = term.id_term AND" +
 " preferred_term.id_thesaurus = term.id_thesaurus AND" +
 " concept.id_concept = preferred_term.id_concept AND" +
 " concept.id_thesaurus = preferred_term.id_thesaurus AND" +
 " hierarchical_relationship.id_concept2 = concept.id_concept" +
 " and concept.id_thesaurus = '" + idThesaurus + "'" +
 " and hierarchical_relationship.role = 'NT'" +
 " and hierarchical_relationship.id_concept1 = '" + idConcept + "'" +
 " and term.lang = '" + idLang + "'" +
 " ORDER BY upper((unaccent_string(term.lexical_value))) ASC;";
                    
 stmt.executeQuery(query);
 resultSet = stmt.getResultSet();
 if (resultSet != null) {
 nodeListNT = new ArrayList<>();
 while (resultSet.next()) {
 NodeNT nodeNT = new NodeNT();
 nodeNT.setIdConcept(resultSet.getString("id_concept"));
 nodeNT.setStatus(resultSet.getString("status"));
 if(resultSet.getString("lexical_value").trim().equals(""))
 nodeNT.setTitle("");
 else
 nodeNT.setTitle(resultSet.getString("lexical_value").trim());
 nodeListNT.add(nodeNT);
 }
 }
        
 } finally {
 stmt.close();
 }
 } finally {
 conn.close();
 }
 } catch (SQLException sqle) {
 // Log exception
 log.error("Error while getting ListConcept of Concept : " + idConcept, sqle);
 }
 //  Collections.sort(nodeConceptTree);
 return nodeListNT;
 }*/
public ArrayList<NodeNT> getListNT(HikariDataSource ds, String idConcept, String idThesaurus, String idLang) {

    Connection conn;
    Statement stmt;
    ResultSet resultSet;
    ArrayList<NodeNT> nodeListNT = new ArrayList<>();

    try {
        // Get connection from pool
        conn = ds.getConnection();
        try {
            stmt = conn.createStatement();
            try {
                String query = "select id_concept2 from hierarchical_relationship" + " where id_thesaurus = '"
                        + idThesaurus + "'" + " and id_concept1 = '" + idConcept + "'" + " and role = '" + "NT"
                        + "'";
                stmt.executeQuery(query);
                resultSet = stmt.getResultSet();
                if (resultSet != null) {
                    while (resultSet.next()) {
                        NodeNT nodeNT = new NodeNT();
                        nodeNT.setIdConcept(resultSet.getString("id_concept2"));
                        nodeListNT.add(nodeNT);
                    }
                }
                for (NodeNT nodeNT : nodeListNT) {
                    query = "SELECT term.lexical_value, term.status FROM term, preferred_term"
                            + " WHERE preferred_term.id_term = term.id_term"
                            + " and preferred_term.id_thesaurus = term.id_thesaurus"
                            + " and preferred_term.id_concept ='" + nodeNT.getIdConcept() + "'"
                            + " and term.lang = '" + idLang + "'" + " and term.id_thesaurus = '" + idThesaurus
                            + "'" + " order by lexical_value DESC";

                    stmt.executeQuery(query);
                    resultSet = stmt.getResultSet();
                    if (resultSet != null) {
                        resultSet.next();
                        if (resultSet.getRow() == 0) {
                            nodeNT.setTitle("");
                            nodeNT.setStatus("");
                        } else {
                            if (resultSet.getString("lexical_value") == null
                                    || resultSet.getString("lexical_value").equals("")) {
                                nodeNT.setTitle("");
                            } else {
                                nodeNT.setTitle(resultSet.getString("lexical_value"));
                            }
                            if (resultSet.getString("status") == null
                                    || resultSet.getString("status").equals("")) {
                                nodeNT.setStatus("");
                            } else {
                                nodeNT.setStatus(resultSet.getString("status"));
                            }
                        }
                    }
                }

            } finally {
                stmt.close();
            }
        } finally {
            conn.close();
        }
    } catch (SQLException sqle) {
        // Log exception
        log.error("Error while getting NT of Concept : " + idConcept, sqle);
    }
    Collections.sort(nodeListNT);
    return nodeListNT;
}

From source file:je3.rmi.MudClient.java

 /** 
  * This convenience method checks whether the name and password match
  * an existing account.  If so, it returns the balance in that account.
  * If not, it throws an exception.  Note that this method does not call
  * commit() or rollback(), so its query is part of a larger transaction.
  **//*  w  w w.j  ava  2  s  . com*/
 public int verify(String name, String password) 
throws BankingException, SQLException
 {
     Statement s = null;
     try {
         s = db.createStatement();
         s.executeQuery("SELECT balance FROM accounts " +
         "WHERE name='" + name + "' " +
         "  AND password = '" + password + "'");
         ResultSet r = s.getResultSet();
         if (!r.next())
             throw new BankingException("Bad account name or password");
         return r.getInt(1);
     }
     finally { try { s.close(); } catch (Exception e) {} }
 }

From source file:de.uniwue.info6.database.jdbc.ConnectionManager.java

/**
 *
 *
 * @param scenario//from  ww  w  .ja  va 2 s  . c  om
 * @return
 *
 * @throws SQLException
 */
public synchronized String getDropSlaveDBList(Scenario scenario) throws SQLException {
    if (scenario != null) {
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            connection = instance.getConnection(scenario);
            statement = connection.createStatement();

            String query = "SELECT CONCAT('DROP DATABASE IF EXISTS `',schema_name,'`; ') AS stmt FROM "
                    + "information_schema.schemata WHERE schema_name "
                    + "LIKE 'ueps\\_slave\\_%' ESCAPE '\\\\' ORDER BY schema_name";
            statement.execute(query);

            resultSet = statement.getResultSet();

            StringBuilder dropQuery = new StringBuilder();
            while (resultSet.next()) {
                dropQuery.append(resultSet.getString(1));
            }
            return dropQuery.toString();
        } catch (Exception e) {
            LOGGER.error("PROBLEM GETTING DATABASE LIST", e);
        } finally {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    return null;
}

From source file:org.apache.zeppelin.jdbc.JDBCInterpreter.java

private InterpreterResult executeSql(String propertyKey, String sql, InterpreterContext interpreterContext) {
    Connection connection;/*from w w w  . j  ava2 s.  com*/
    Statement statement;
    ResultSet resultSet = null;
    String paragraphId = interpreterContext.getParagraphId();
    String user = interpreterContext.getAuthenticationInfo().getUser();

    InterpreterResult interpreterResult = new InterpreterResult(InterpreterResult.Code.SUCCESS);

    try {
        connection = getConnection(propertyKey, interpreterContext);
        if (connection == null) {
            return new InterpreterResult(Code.ERROR, "Prefix not found.");
        }

        ArrayList<String> multipleSqlArray = splitSqlQueries(sql);
        for (int i = 0; i < multipleSqlArray.size(); i++) {
            String sqlToExecute = multipleSqlArray.get(i);
            statement = connection.createStatement();
            if (statement == null) {
                return new InterpreterResult(Code.ERROR, "Prefix not found.");
            }

            try {
                getJDBCConfiguration(user).saveStatement(paragraphId, statement);

                boolean isResultSetAvailable = statement.execute(sqlToExecute);
                getJDBCConfiguration(user).setConnectionInDBDriverPoolSuccessful(propertyKey);
                if (isResultSetAvailable) {
                    resultSet = statement.getResultSet();

                    // Regards that the command is DDL.
                    if (isDDLCommand(statement.getUpdateCount(), resultSet.getMetaData().getColumnCount())) {
                        interpreterResult.add(InterpreterResult.Type.TEXT, "Query executed successfully.");
                    } else {
                        interpreterResult.add(
                                getResults(resultSet, !containsIgnoreCase(sqlToExecute, EXPLAIN_PREDICATE)));
                    }
                } else {
                    // Response contains either an update count or there are no results.
                    int updateCount = statement.getUpdateCount();
                    interpreterResult.add(InterpreterResult.Type.TEXT,
                            "Query executed successfully. Affected rows : " + updateCount);
                }
            } finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException e) {
                        /*ignored*/ }
                }
                if (statement != null) {
                    try {
                        statement.close();
                    } catch (SQLException e) {
                        /*ignored*/ }
                }
            }
        }
        //In case user ran an insert/update/upsert statement
        if (connection != null) {
            try {
                if (!connection.getAutoCommit()) {
                    connection.commit();
                }
                connection.close();
            } catch (SQLException e) {
                /*ignored*/ }
        }
        getJDBCConfiguration(user).removeStatement(paragraphId);
    } catch (Exception e) {
        if (e.getCause() instanceof TTransportException && Throwables.getStackTraceAsString(e).contains("GSS")
                && getJDBCConfiguration(user).isConnectionInDBDriverPoolSuccessful(propertyKey)) {
            return reLoginFromKeytab(propertyKey, sql, interpreterContext, interpreterResult);
        } else {
            logger.error("Cannot run " + sql, e);
            String errorMsg = Throwables.getStackTraceAsString(e);
            try {
                closeDBPool(user, propertyKey);
            } catch (SQLException e1) {
                logger.error("Cannot close DBPool for user, propertyKey: " + user + propertyKey, e1);
            }
            interpreterResult.add(errorMsg);
            return new InterpreterResult(Code.ERROR, interpreterResult.message());
        }
    }
    return interpreterResult;
}