Example usage for java.sql CallableStatement executeQuery

Introduction

In this page you can find the example usage for java.sql CallableStatement executeQuery.

Prototype

ResultSet executeQuery() throws SQLException;

Source Link

Document

Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

Usage

From source file:com.tera.common.database.query.CQueryService.java

@Override
public <T> T call(String call, CallReadQuery<T> query, String errorMessage) {
    Connection connection = null;
    CallableStatement statement = null;
    ResultSet resultSet = null;/* ww  w  .j av  a2s  . c o  m*/

    try {
        connection = databaseFactory.getConnection();
        statement = connection.prepareCall(call);
        query.setParams(statement);
        resultSet = statement.executeQuery();
        return query.handleRead(resultSet);
    } catch (Exception ex) {
        if (errorMessage == null)
            log.error("Error calling stored procedure " + ex, ex);
        else
            log.error(errorMessage + " " + ex, ex);
    } finally {
        close(resultSet, statement, connection);
    }
    return null;
}

---

From source file:com.oracle.tutorial.jdbc.StoredProcedureMySQLSample.java

public void runStoredProcedures(String coffeeNameArg, float maximumPercentageArg, float newPriceArg)
        throws SQLException {
    CallableStatement cs = null;

    try {//from w w w  .j  a v  a  2s  .c  o m

        System.out.println("\nCalling the procedure GET_SUPPLIER_OF_COFFEE");
        cs = this.con.prepareCall("{call GET_SUPPLIER_OF_COFFEE(?, ?)}");
        cs.setString(1, coffeeNameArg);
        cs.registerOutParameter(2, Types.VARCHAR);
        cs.executeQuery();

        String supplierName = cs.getString(2);

        if (supplierName != null) {
            System.out.println("\nSupplier of the coffee " + coffeeNameArg + ": " + supplierName);
        } else {
            System.out.println("\nUnable to find the coffee " + coffeeNameArg);
        }

        System.out.println("\nCalling the procedure SHOW_SUPPLIERS");
        cs = this.con.prepareCall("{call SHOW_SUPPLIERS}");
        ResultSet rs = cs.executeQuery();

        while (rs.next()) {
            String supplier = rs.getString("SUP_NAME");
            String coffee = rs.getString("COF_NAME");
            System.out.println(supplier + ": " + coffee);
        }

        System.out.println("\nContents of COFFEES table before calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

        System.out.println("\nCalling the procedure RAISE_PRICE");
        cs = this.con.prepareCall("{call RAISE_PRICE(?,?,?)}");
        cs.setString(1, coffeeNameArg);
        cs.setFloat(2, maximumPercentageArg);
        cs.registerOutParameter(3, Types.NUMERIC);
        cs.setFloat(3, newPriceArg);

        cs.execute();

        System.out.println("\nValue of newPrice after calling RAISE_PRICE: " + cs.getFloat(3));

        System.out.println("\nContents of COFFEES table after calling RAISE_PRICE:");
        CoffeesTable.viewTable(this.con);

    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (cs != null) {
            cs.close();
        }
    }
}

---

From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java

public List<StatistikaTypuKategorie> getTypeAndCategoryStatistics(StatistikaTypuKategorieParams params) {
    try (Connection connection = OracleJDBCConnector.getConnection();) {
        CallableStatement stmnt = connection
                .prepareCall("SELECT * FROM TABLE(get_statistika_typ_a_kategoria(?,?))");
        stmnt.setInt(1, params.getRok());
        stmnt.setString(2, params.getVelicina().name().toLowerCase());
        ResultSet result = stmnt.executeQuery();
        List<StatistikaTypuKategorie> output = new LinkedList<>();
        while (result.next()) {
            StatistikaTypuKategorie o = new StatistikaTypuKategorie();
            o.setTyp(result.getString("TYP_ODBERATELA"));
            o.setKategoria(result.getString("KATEGORIA"));
            o.setMinimalnaSpotreba(result.getDouble("MIN_SPOTREBA"));
            o.setMesiacMinimalnejSpotreby(result.getInt("MESIAC_MIN_SPOTREBY"));
            o.setMaximalnaSpotreba(result.getDouble("MAX_SPOTREBA"));
            o.setMesiacMaximalnejSpotreby(result.getInt("MESIAC_MAX_SPOTREBY"));
            o.setPriemernaSpotreba(result.getDouble("PRIEMER"));
            output.add(o);/*from   w w w.  j ava 2s .  c  o  m*/
        }
        return output;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

---

From source file:de.anycook.db.mysql.DBGetRecipe.java

public String getImageName(String recipe) throws SQLException {
    CallableStatement call = connection.prepareCall("{call recipe_image(?)}");
    call.setString(1, recipe);/*  w w w . j  a va 2  s.c  o  m*/
    ResultSet data = call.executeQuery();
    if (data.next()) {
        String imageName = data.getString("imagename");
        String categoryImage = "category/" + data.getString("image");

        return imageName == null || imageName.equals("") ? categoryImage : imageName;

    }

    return "nopicture.png";

}

---

From source file:org.gofleet.openLS.ddbb.dao.postgis.PostGisHBGeoCodingDAO.java

@Transactional(readOnly = true)
public List<AbstractResponseParametersType> reverseGeocode(final ReverseGeocodeRequestType param) {
    HibernateCallback<List<AbstractResponseParametersType>> action = new HibernateCallback<List<AbstractResponseParametersType>>() {
        public List<AbstractResponseParametersType> doInHibernate(Session session)
                throws HibernateException, SQLException {
            PositionType position = param.getPosition();

            position.getPoint().getPos().getValue();

            Geometry geometry = GeoUtil.getGeometry(position);

            List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>();

            // TODO change deprecation?
            @SuppressWarnings("deprecation")
            CallableStatement consulta = session.connection().prepareCall("{call gls_reverse_geocoding(?)}");
            PGgeometry geom = new PGgeometry(geometry.toText());
            consulta.setObject(1, geom);

            LOG.debug(consulta);//from w  w  w .  jav  a 2s  .  c  om

            ResultSet o = consulta.executeQuery();
            ReverseGeocodeResponseType grt = new ReverseGeocodeResponseType();
            while (o.next()) {
                ReverseGeocodedLocationType geocode = new ReverseGeocodedLocationType();
                if (geocode.getAddress() == null)
                    geocode.setAddress(new AddressType());
                if (geocode.getAddress().getStreetAddress() == null)
                    geocode.getAddress().setStreetAddress(new StreetAddressType());
                for (int i = 1; i < o.getMetaData().getColumnCount(); i++) {
                    String value = new String(o.getString(i).getBytes(), Charset.forName("ISO-8859-1"));
                    if (o.getMetaData().getColumnName(i).equals("street")) {
                        StreetNameType street = new StreetNameType();
                        street.setValue(value);
                        street.setOfficialName(value);
                        geocode.getAddress().getStreetAddress().getStreet().add(street);
                    } else if (o.getMetaData().getColumnName(i).equals("munsub")) {
                        NamedPlaceType place = new NamedPlaceType();
                        place.setValue(value);
                        place.setType(NamedPlaceClassification.MUNICIPALITY_SUBDIVISION);
                        geocode.getAddress().getPlace().add(place);
                    } else if (o.getMetaData().getColumnName(i).equals("mun")) {
                        NamedPlaceType place = new NamedPlaceType();
                        place.setValue(value);
                        place.setType(NamedPlaceClassification.MUNICIPALITY);
                        geocode.getAddress().getPlace().add(place);
                    } else if (o.getMetaData().getColumnName(i).equals("subcountry")) {
                        NamedPlaceType place = new NamedPlaceType();
                        place.setValue(value);
                        place.setType(NamedPlaceClassification.COUNTRY_SUBDIVISION);
                        geocode.getAddress().getPlace().add(place);
                    } else if (o.getMetaData().getColumnName(i).equals("country")) {
                        geocode.getAddress().setCountryCode(value);
                    }
                }
                try {

                    grt.getReverseGeocodedLocation().add(geocode);
                } catch (Throwable t) {
                    LOG.error("Error extracting data from database.", t);
                }
            }
            res_.add(grt);
            return res_;
        }

    };

    return hibernateTemplate.executeWithNativeSession(action);
}

---

From source file:org.gofleet.openLS.ddbb.dao.GeoCodingDAO.java

@Transactional(readOnly = true)
public List<List<AbstractResponseParametersType>> reverseGeocode(final ReverseGeocodeRequestType param) {
    HibernateCallback<List<List<AbstractResponseParametersType>>> action = new HibernateCallback<List<List<AbstractResponseParametersType>>>() {
        public List<List<AbstractResponseParametersType>> doInHibernate(Session session)
                throws HibernateException, SQLException {

            List<List<AbstractResponseParametersType>> res = new LinkedList<List<AbstractResponseParametersType>>();
            PositionType position = param.getPosition();

            position.getPoint().getPos().getValue();

            Geometry geometry = GeoUtil.getGeometry(position);

            List<AbstractResponseParametersType> res_ = new LinkedList<AbstractResponseParametersType>();

            // TODO change deprecation?
            @SuppressWarnings("deprecation")
            CallableStatement consulta = session.connection().prepareCall("{call gls_reverse_geocoding(?)}");
            PGgeometry geom = new PGgeometry(geometry.toText());
            consulta.setObject(1, geom);

            LOG.debug(consulta);/*  w w w  .j a va2  s . c  om*/

            ResultSet o = consulta.executeQuery();
            ReverseGeocodeResponseType grt = new ReverseGeocodeResponseType();
            while (o.next()) {
                ReverseGeocodedLocationType geocode = new ReverseGeocodedLocationType();
                if (geocode.getAddress() == null)
                    geocode.setAddress(new AddressType());
                if (geocode.getAddress().getStreetAddress() == null)
                    geocode.getAddress().setStreetAddress(new StreetAddressType());
                for (int i = 1; i < o.getMetaData().getColumnCount(); i++) {
                    String value = new String(o.getString(i).getBytes(), Charset.forName("ISO-8859-1"));
                    if (o.getMetaData().getColumnName(i).equals("street")) {
                        StreetNameType street = new StreetNameType();
                        street.setValue(value);
                        street.setOfficialName(value);
                        geocode.getAddress().getStreetAddress().getStreet().add(street);
                    } else if (o.getMetaData().getColumnName(i).equals("munsub")) {
                        NamedPlaceType place = new NamedPlaceType();
                        place.setValue(value);
                        place.setType(NamedPlaceClassification.MUNICIPALITY_SUBDIVISION);
                        geocode.getAddress().getPlace().add(place);
                    } else if (o.getMetaData().getColumnName(i).equals("mun")) {
                        NamedPlaceType place = new NamedPlaceType();
                        place.setValue(value);
                        place.setType(NamedPlaceClassification.MUNICIPALITY);
                        geocode.getAddress().getPlace().add(place);
                    } else if (o.getMetaData().getColumnName(i).equals("subcountry")) {
                        NamedPlaceType place = new NamedPlaceType();
                        place.setValue(value);
                        place.setType(NamedPlaceClassification.COUNTRY_SUBDIVISION);
                        geocode.getAddress().getPlace().add(place);
                    } else if (o.getMetaData().getColumnName(i).equals("country")) {
                        geocode.getAddress().setCountryCode(value);
                    }
                }
                try {

                    grt.getReverseGeocodedLocation().add(geocode);
                } catch (Throwable t) {
                    LOG.error("Error extracting data from database.", t);
                }
            }
            res_.add(grt);

            res.add(res_);
            return res;
        }

    };

    return hibernateTemplate.executeWithNativeSession(action);
}

---

From source file:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java

public List<ZvysenieSpotreby> getIncreasedSpendingStatistics(IncreasedSpendingStatisticParams params,
        double loadFactor) {
    try (Connection connection = OracleJDBCConnector.getConnection();) {
        String fn = "get_zvysena_miera_spotreby";
        if (loadFactor < 1) {
            fn = "get_znizena_miera_spotreby";
        }/*  w  w  w.j  a v  a 2  s  . c o m*/
        CallableStatement stmnt = connection.prepareCall("SELECT * FROM TABLE(" + fn + "(?,?,?))");
        stmnt.setDate(1, Utils.utilDateToSqlDate(params.getDatumOd()));
        stmnt.setDate(2, Utils.utilDateToSqlDate(params.getDatumDo()));
        stmnt.setDouble(3, loadFactor);
        ResultSet result = stmnt.executeQuery();
        List<ZvysenieSpotreby> output = new LinkedList<>();
        while (result.next()) {
            ZvysenieSpotreby o = new ZvysenieSpotreby();
            o.setMeno(result.getString("MENO"));
            o.setPriemernaSpotrebaVMinulosti(result.getDouble("PRIEMERNA_SPOTREBA_V_MINULOSTI"));
            o.setVelicina(MeraciaVelicina.valueOf(result.getString("VELICINA").toUpperCase()));
            o.setZvysenaSpotreba(result.getDouble("ZVYSENA_SPOTREBA"));
            output.add(o);
        }
        return output;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

---

From source file:weave.utils.SQLUtils.java

/**
 * @param connection An SQL Connection//w  w w.j a v a  2 s  .  co m
 * @param query An SQL Query with '?' place holders for parameters
 * @param params Parameters for the SQL query for all '?' place holders.
 * @return A SQLResult object containing the result of the query
 * @throws SQLException
 */
public static SQLResult getRowSetFromQuery(Connection connection, String query, String[] params)
        throws SQLException {
    CallableStatement stmt = null;
    ResultSet rs = null;
    SQLResult result = null;
    try {
        stmt = connection.prepareCall(query);
        for (int i = 0; i < params.length; i++)
            stmt.setString(i + 1, params[i]);
        rs = stmt.executeQuery();

        // make a copy of the query result
        result = new SQLResult(rs);
    } catch (SQLException e) {
        //e.printStackTrace();
        throw e;
    } finally {
        // close everything in reverse order
        SQLUtils.cleanup(rs);
        SQLUtils.cleanup(stmt);
    }

    // return the copy of the query result
    return result;
}

---

From source file:ro.nextreports.engine.util.QueryUtil.java

public List<NameType> executeQueryForDynamicColumn(String sql) throws Exception {

    StringWriter sw = new StringWriter(100);
    sw.append(sql);//from w  w w .  ja  va2  s  .  c om

    String sqlForHeader = sw.toString();
    LOG.info("call for chart dynamic columns = " + sqlForHeader);

    ResultSet rs = null;
    Statement stmt = null;
    try {
        if (isProcedureCall(sqlForHeader)) {
            Dialect dialect = DialectUtil.getDialect(con);
            CallableStatement cs = con.prepareCall("{" + sqlForHeader + "}");
            stmt = cs;
            if (dialect.hasProcedureWithCursor()) {
                cs.registerOutParameter(1, dialect.getCursorSqlType());
            }
            rs = cs.executeQuery();
            if (dialect.hasProcedureWithCursor()) {
                rs = (ResultSet) (cs.getObject(1));
            }
        } else {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sqlForHeader);
        }
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        if (columnCount != 2) {
            throw new QueryException(
                    "Column query must have two data columns : column name and column legend.");
        }

        List<NameType> columnNames = new ArrayList<NameType>();
        while (rs.next()) {
            columnNames.add(new NameType(rs.getString(1), rs.getString(2)));
        }
        return columnNames;
    } finally {
        ConnectionUtil.closeResultSet(rs);
        ConnectionUtil.closeStatement(stmt);
    }

}

---

From source file:ro.nextreports.engine.util.QueryUtil.java

public List<NameType> executeQueryForColumnNames(String sql) throws Exception {
    // long t = System.currentTimeMillis();
    StringWriter sw = new StringWriter(100);
    // sw.append("SELECT * FROM (");
    sw.append(sql);//  w  ww . j av a2 s . c  o  m
    // sw.append(") A WHERE 1 = -1");

    String sqlForHeader = sw.toString();
    LOG.info("call for header columns = " + sqlForHeader);

    ResultSet rs = null;
    Statement stmt = null;
    try {
        if (isProcedureCall(sqlForHeader)) {
            Dialect dialect = DialectUtil.getDialect(con);
            CallableStatement cs = con.prepareCall("{" + sqlForHeader + "}");
            stmt = cs;
            if (dialect.hasProcedureWithCursor()) {
                cs.registerOutParameter(1, dialect.getCursorSqlType());
            }
            rs = cs.executeQuery();
            if (dialect.hasProcedureWithCursor()) {
                rs = (ResultSet) (cs.getObject(1));
            }
        } else {
            stmt = con.createStatement();
            stmt.setMaxRows(1);
            rs = stmt.executeQuery(sqlForHeader);
        }
        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();

        List<NameType> columnNames = new ArrayList<NameType>();
        for (int i = 0; i < columnCount; i++) {
            columnNames.add(new NameType(rsmd.getColumnLabel(i + 1), dialect.getJavaType(
                    rsmd.getColumnTypeName(i + 1), rsmd.getPrecision(i + 1), rsmd.getScale(i + 1))));
            // rsmd.getColumnClassName(i + 1)));
        }

        // t = System.currentTimeMillis() - t;
        // System.out.println("execute query for column names in " + t +
        // "ms");

        return columnNames;
    } finally {
        ConnectionUtil.closeResultSet(rs);
        ConnectionUtil.closeStatement(stmt);
    }

}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• »