Example usage for java.sql CallableStatement executeQuery

List of usage examples for java.sql CallableStatement executeQuery

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. CallableStatement
  6. 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:sk.uniza.fri.pds.spotreba.energie.service.SeHistoriaService.java

public List<KrokSpotreby> getSpendingStatistics(SpendingStatisticsParameters params) {
    try (Connection connection = OracleJDBCConnector.getConnection();) {
        CallableStatement stmnt = connection
                .prepareCall("SELECT * FROM TABLE(get_statistika_spotreby(?,?,?,?,?))");
        stmnt.setInt(1, params.getIdSpotrebitela());
        stmnt.setDate(2, Utils.utilDateToSqlDate(params.getDatumOd()));
        stmnt.setDate(3, Utils.utilDateToSqlDate(params.getDatumDo()));
        stmnt.setInt(4, params.getGranularita().val);
        stmnt.setString(5, params.getVelicina().name().toLowerCase());
        ResultSet result = stmnt.executeQuery();
        List<KrokSpotreby> output = new LinkedList<>();
        while (result.next()) {
            KrokSpotreby o = new KrokSpotreby();
            o.setDatumOd(result.getDate("DATUM_OD"));
            o.setDatumDo(result.getDate("DATUM_DO"));
            o.setSpotreba(result.getDouble("SPOTREBA"));
            output.add(o);//from  w  w w  .j a v a  2s. co m
        }
        return output;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

From source file:es.emergya.bbdd.dao.RoutingHome.java

/**
 * Devuelve la lista de ids de la ruta desde vertice_origen a
 * vertice_destino//from   w  w w  .  java 2s. c  o  m
 * 
 * @param origin
 * @param goal
 * @return
 */
@Transactional(readOnly = true, rollbackFor = Throwable.class)
private List<Long> getSimpleGid(final Long origin, final Long goal) {
    final List<Long> lista = new ArrayList<Long>();
    try {
        Session currentSession = getSession();
        CallableStatement consulta = currentSession.connection().prepareCall("{call shortest_path(?,?,?,?,?)}");
        consulta.setString(1, "SELECT id, " + source + "::int4, " + target + "::int4, " + "ST_length2d("
                + the_geom + ")::float8 as cost FROM " + table);
        consulta.setLong(2, origin);
        consulta.setLong(3, goal);
        consulta.setBoolean(4, false);
        consulta.setBoolean(5, false);
        log.trace(consulta);
        ResultSet resultado = consulta.executeQuery();

        while (resultado.next())
            lista.add(resultado.getLong("edge_id"));
    } catch (Exception e) {
        log.error("No se pudo calcular la ruta", e);
    }

    return lista;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @return The amount of classes currently existing in the database
 *//*from   ww w .j av a 2  s.c om*/
public static int getClassCount(String ApplicationRoot) {
    int result = 0;
    ResultSet resultSet = null;
    log.debug("*** Getter.getClassCount ***");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call classCount()");
        log.debug("Gathering classCount ResultSet");
        resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from classCount");
        resultSet.next();
        result = resultSet.getInt(1);
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = 0;
    }
    Database.closeConnection(conn);
    log.debug("*** END getClassCount");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @return Result set containing class info in the order classId, className and then classYear
 *///from  w ww . ja v a2  s .com
public static ResultSet getClassInfo(String ApplicationRoot) {
    ResultSet result = null;
    log.debug("*** Getter.getClassInfo (All Classes) ***");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call classesGetData()");
        log.debug("Gathering classesGetData ResultSet");
        result = callstmt.executeQuery();
        log.debug("Returning Result Set from classesGetData");
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    log.debug("*** END getClassInfo");
    return result;
}

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

public List<CelkovaStatistika> getOveralStatistics(StatistikaSpotriebParams params) {
    try (Connection connection = OracleJDBCConnector.getConnection();) {
        CallableStatement stmnt = connection
                .prepareCall("SELECT get_najm_najve_spotreba(?,?,?,?,?,?) from dual");
        stmnt.setString(1, params.getTypOdberatela().val.toString());
        stmnt.setString(2, params.getKategoriaOdberatela().name());
        stmnt.setInt(3, params.getIdRegionu());
        stmnt.setString(4, params.getVelicina().name().toLowerCase());
        stmnt.setDate(5, Utils.utilDateToSqlDate(params.getDatumOd()));
        stmnt.setDate(6, Utils.utilDateToSqlDate(params.getDatumDo()));
        ResultSet result = stmnt.executeQuery();
        List<CelkovaStatistika> output = new LinkedList<>();
        while (result.next()) {
            CelkovaStatistika o = new CelkovaStatistika();
            Object[] attributes = ((Struct) result.getObject(1)).getAttributes();
            o.setMesiacMinimalnejSpotreby(((Timestamp) attributes[0]));
            o.setMinimalnaSpotreba(((BigDecimal) attributes[1]).intValue());
            o.setMesiacMaximalnejSpotreby(((Timestamp) attributes[2]));
            o.setMaximalnaSpotreba(((BigDecimal) attributes[3]).intValue());
            output.add(o);/* w  ww  .  j  a  v  a2 s .  c  om*/
        }
        return output;
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

From source file:dbProcs.Getter.java

/**
 * Used to gather all module information for internal functionality. This method is used in creating View's or in control class operations
 * @param ApplicationRoot The current runing context of the application
 * @return An ArrayList of String arrays that contain the module identifier, module name, module type and module category of each module in the core database.
 *//*www  .ja v a2 s.c o m*/
public static ArrayList<String[]> getAllModuleInfo(String ApplicationRoot) {
    log.debug("*** Getter.getAllModuleInfo ***");
    ArrayList<String[]> modules = new ArrayList<String[]>();

    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetAll()");
        log.debug("Gathering moduleGetAll ResultSet");
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetAll");
        int i = 0;
        while (resultSet.next()) {
            String[] result = new String[4];
            i++;
            result[0] = resultSet.getString(1); //moduleId
            result[1] = resultSet.getString(2); //moduleName
            result[2] = resultSet.getString(3); //moduleType
            result[3] = resultSet.getString(4); //mdouleCategory
            modules.add(result);
        }
        log.debug("Returning Array list with " + i + " entries.");
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getAllModuleInfo ***");
    return modules;
}

From source file:dbProcs.Getter.java

/**
 * This method returns the module categories in option tags that are to be open or closed in a &lt;select&gt; element for administration manipulation
 * @param ApplicationRoot/*from   w  ww. j  a  v  a 2  s. com*/
 * @return Module Category List for Html (&lt;SELECT&gt; element)
 */
public static String getOpenCloseCategoryMenu(String ApplicationRoot) {
    log.debug("*** Getter.getOpenCloseCategoryMenu ***");
    String theModules = new String();
    String output = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        //Get the modules
        CallableStatement callstmt = conn
                .prepareCall("SELECT DISTINCT moduleCategory FROM modules ORDER BY moduleCategory");
        ResultSet modules = callstmt.executeQuery();
        while (modules.next()) {
            String theModule = "<option value='" + encoder.encodeForHTMLAttribute(modules.getString(1)) + "'>"
                    + encoder.encodeForHTML(modules.getString(1)) + "</option>\n";
            theModules += theModule;
        }
        //This is the actual output: It assumes a <table> environment
        output = "<select style='width: 300px; height: 200px;' multiple id='toDo'>" + theModules
                + "</select>\n";
        log.debug("Module Category Menu returned");
    } catch (Exception e) {
        log.error("Module Status Menu: " + e.toString());
    }
    Database.closeConnection(conn);
    return output;
}

From source file:dbProcs.Getter.java

/**
 * This method returns modules in option tags in different &lt;select&gt; elements depending on their current open/closed status. 
 * The output assumes it is contained in a table context
 * @param ApplicationRoot The Running Context of the Application
 * @return Tr/td elements containing a moduleStatusMenu that has lists of the current open and closed modules
 *//*  w  ww . ja  v a 2 s.co  m*/
public static String getModuleStatusMenu(String ApplicationRoot) {
    log.debug("*** Getter.getModuleStatusMenu ***");
    String openModules = new String();
    String closedModules = new String();
    String output = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        //Get the modules
        CallableStatement callstmt = conn.prepareCall("call moduleAllStatus()");
        log.debug("Gathering moduleAllStatus ResultSet");
        ResultSet modules = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleAllStatus");
        while (modules.next()) {
            String theModule = "<option value='" + encoder.encodeForHTMLAttribute(modules.getString(1)) + "'>"
                    + encoder.encodeForHTML(modules.getString(2)) + "</option>\n";
            if (modules.getString(3).equalsIgnoreCase("open")) {
                //Module is Open currently, so add it to the open side of the list
                openModules += theModule;
            } else {
                //If it is not open: It must be closed (NULL or not)
                closedModules += theModule;
            }
        }
        //This is the actual output: It assumes a <table> environment
        output = "<tr><th>To Open</th><th>To Close</th></tr><tr>\n"
                + "<td><select style='width: 300px; height: 200px;' multiple id='toOpen'>" + closedModules
                + "</select></td>\n" + "<td><select style='width: 300px; height: 200px;' multiple id='toClose'>"
                + openModules + "</select></td>\n" + "</tr>\n";
        log.debug("Module Status Menu returned");
    } catch (Exception e) {
        log.error("Module Status Menu: " + e.toString());
    }
    Database.closeConnection(conn);
    return output;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param classId The identifier of the class
 * @return String Array with Class information with the format of {name, year}
 *///from  www. j  a  v  a2  s . com
public static String[] getClassInfo(String ApplicationRoot, String classId) {
    String[] result = new String[2];
    log.debug("*** Getter.getClassInfo (Single Class) ***");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call classFind(?)");
        callstmt.setString(1, classId);
        log.debug("Gathering classFind ResultSet");
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from classFind");
        resultSet.next();
        result[0] = resultSet.getString(1);//Name
        result[1] = resultSet.getString(2);//Year
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    log.debug("*** END getClassInfo");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param userName The username of the user
 * @return The class id of the submitted user name
 *///from   w w w.j  a  va  2  s .  c  o  m
public static String getUserClassFromName(String ApplicationRoot, String userName) {
    log.debug("*** Getter.getUserClass ***");
    String result = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call userClassId(?)");
        log.debug("Gathering userClassId ResultSet");
        callstmt.setString(1, userName);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from userClassId");
        resultSet.next();
        result = resultSet.getString(1);
        log.debug("Found " + result);
    } catch (SQLException e) {
        log.error("Could not execute userClassId: " + e.toString());
        result = new String();
    }
    Database.closeConnection(conn);
    log.debug("*** END getUserClass ***");
    return result;
}