Example usage for java.sql Connection prepareCall

List of usage examples for java.sql Connection prepareCall

  1. HOME
  2. Java
  3. java
  4. java.sql.*
  5. Connection
  6. prepareCall

Introduction

In this page you can find the example usage for java.sql Connection prepareCall.

Prototype

CallableStatement prepareCall(String sql) throws SQLException;

Source Link

Document

Creates a CallableStatement object for calling database stored procedures.

Usage

From source file:dbProcs.Getter.java

/**
 * Convert module hash to ID//from  w w  w .  j  a va2  s .co  m
 * @param ApplicationRoot The current running context of the application
 * @param moduleHash The module hash to use for look up
 * @return The identifier of the module with the module hash of the moduleHash parameter
 */
public static String getModuleIdFromHash(String ApplicationRoot, String moduleHash) {
    log.debug("*** Getter.getModuleIdFromHash ***");
    log.debug("Getting ID from Hash: " + moduleHash);
    String result = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetIdFromHash(?)");
        log.debug("Gathering moduleGetIdFromHash ResultSet");
        callstmt.setString(1, moduleHash);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetIdFromHash");
        resultSet.next();
        result = resultSet.getString(1);
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleIdFromHash ***");
    return result;
}

From source file:dbProcs.Getter.java

/**
 * @param ApplicationRoot The current running context of the application
 * @param moduleId Identifier of module//from   w w  w  . j  av a 2 s  .  co m
 * @return The db stored solution key value for the moduleId submitted
 */
public static String getModuleResult(String ApplicationRoot, String moduleId) {
    log.debug("*** Getter.getModuleResult ***");
    String moduleFound = null;
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetResult(?)");
        log.debug("Gathering moduleGetResult ResultSet");
        callstmt.setString(1, moduleId);
        ResultSet moduleFind = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetResult");
        moduleFind.next();
        log.debug("Module " + moduleFind.getString(1) + " Found");
        moduleFound = moduleFind.getString(2);
    } catch (Exception e) {
        log.error("Module did not exist: " + e.toString());
        moduleFound = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleResult ***");
    return moduleFound;
}

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
 *//* w ww .  jav  a 2 s .co 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;
}

From source file:dbProcs.Getter.java

/**
 * Used to decipher whether or not a user exists as a player
 * @param userId The user identifier of the player to be found
 * @return A boolean reflecting the state of existence of the player
 *///from  w  w  w . ja  v  a 2  s.c  o  m
public static boolean findPlayerById(String ApplicationRoot, String userId) {
    log.debug("*** Getter.findPlayerById ***");
    boolean userFound = false;
    //Get connection
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call playerFindById(?)");
        log.debug("Gathering playerFindById ResultSet");
        callstmt.setString(1, userId);
        ResultSet userFind = callstmt.executeQuery();
        log.debug("Opening Result Set from playerFindById");
        userFind.next(); //This will throw an exception if player not found
        log.debug("Player Found: " + userFind.getString(1)); //This line will not execute if player not found
        userFound = true;
    } catch (Exception e) {
        log.error("Player did not exist: " + e.toString());
        userFound = false;
    }
    Database.closeConnection(conn);
    log.debug("*** END findPlayerById ***");
    return userFound;
}

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.
 *//*from  w w  w.jav  a  2 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 address of a module based on the module identifier submitted. 
 * If user has not accessed this level before, they are put down as starting the level at this time.
 * If the level is a client side attack, or other issues that cannot be abused to return a result key (like XSS, CSRF or network sniffing)
 * the address is of the core server. Otherwise the modules sit on the vulnerable application server
 * @param ApplicationRoot The current running context of the application
 * @param moduleId Identifier of the module the to return
 * @param userId The identifier of the user that wants to get the module
 * @return The module address//from ww  w.ja  va  2 s.  c  o  m
 */
public static String getModuleAddress(String ApplicationRoot, String moduleId, String userId) {
    log.debug("*** Getter.getModuleAddress ***");
    String output = new String();
    String type = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleGetHash(?, ?)");
        callstmt.setString(1, moduleId);
        callstmt.setString(2, userId);
        log.debug("Gathering moduleGetHash ResultSet");
        ResultSet modules = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetHash");
        modules.next(); //Exception thrown if no hash was found
        //Set Type. Used to ensure the URL points at the correct directory
        if (modules.getString(3).equalsIgnoreCase("challenge")) {
            type = "challenges";
        } else {
            type = "lessons";
        }
        output = type + "/" + modules.getString(1) + ".jsp";
    } catch (Exception e) {
        log.error("Module Hash Retrieval: " + e.toString());
        log.error("moduleID = " + moduleId);
        log.error("userID = " + userId);
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleAddress() ***");
    return output;
}

From source file:com.cws.esolutions.core.dao.impl.ApplicationDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#getApplication(java.lang.String)
 *//*  w w w .  ja va  2 s . com*/
public synchronized List<Object> getApplication(final String value) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#getApplication(final String value) throws SQLException";

    if (DEBUG) {
        DEBUGGER.debug(methodName);
        DEBUGGER.debug("value: {}", value);
    }

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object> responseData = null;

    try {
        sqlConn = dataSource.getConnection();

        if (sqlConn.isClosed()) {
            throw new SQLException("Unable to obtain application datasource connection");
        }

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL getApplicationData(?)}");
        stmt.setString(1, value);

        if (DEBUG) {
            DEBUGGER.debug("CallableStatement: {}", stmt);
        }

        if (stmt.execute()) {
            resultSet = stmt.getResultSet();

            if (DEBUG) {
                DEBUGGER.debug("resultSet: {}", resultSet);
            }

            if (resultSet.next()) {
                resultSet.first();

                responseData = new ArrayList<Object>(Arrays.asList(resultSet.getString(1), // APPLICATION_GUID
                        resultSet.getString(2), // APPLICATION_NAME
                        resultSet.getDouble(3), // APPLICATION_VERSION
                        resultSet.getString(4), // INSTALLATION_PATH
                        resultSet.getString(5), // PACKAGE_LOCATION
                        resultSet.getString(6), // PACKAGE_INSTALLER
                        resultSet.getString(7), // INSTALLER_OPTIONS
                        resultSet.getString(8), // LOGS_DIRECTORY
                        resultSet.getString(9) // PLATFORM_GUID
                ));

                if (DEBUG) {
                    DEBUGGER.debug("data: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (resultSet != null) {
            resultSet.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if ((sqlConn != null) && (!(sqlConn.isClosed()))) {
            sqlConn.close();
        }
    }

    return responseData;
}

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//  ww w  . j  a v a 2 s .c  o  m
 * @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

/**
 * Returns HTML menu for challenges. Challenges are only referenced by their id, 
 * The user will have to go through another servlet to get the module's View address
 * @param ApplicationRoot The current running context of the application
 * @return HTML menu for challenges   /*from w ww.  j a  va 2 s.  c o  m*/
 */
public static String getChallenges(String ApplicationRoot, String userId, Locale lang) {
    log.debug("*** Getter.getChallenges ***");
    String output = new String();
    //Getting Translated Level Names
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    //Encoder to prevent XSS
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = conn.prepareCall("call moduleAllInfo(?, ?)");
        callstmt.setString(1, "challenge");
        callstmt.setString(2, userId);
        log.debug("Gathering moduleAllInfo ResultSet");
        ResultSet challenges = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleAllInfo");
        String challengeCategory = new String();
        int rowNumber = 0; // Identifies the first row, ie the start of the list. This is slightly different output to every other row
        while (challenges.next()) {
            if (!challengeCategory.equalsIgnoreCase(challenges.getString(2))) {
                challengeCategory = challenges.getString(2);
                //log.debug("New Category Detected: " + challengeCategory);
                if (rowNumber > 0) //output prepared for Every row after row 1
                    output += "</ul></li><li><a href='javascript:;' class='challengeHeader' >"
                            + encoder.encodeForHTML(bundle.getString("category." + challengeCategory))
                            + "</a><ul class='challengeList' style='display: none;'>";
                else //output prepared for First row in entire challenge
                    output += "<li><a href='javascript:;' class='challengeHeader'>"
                            + encoder.encodeForHTML(bundle.getString("category." + challengeCategory))
                            + "</a><ul class='challengeList' style='display: none;'>";
                //log.debug("Compiling Challenge Category - " + challengeCategory);
            }
            output += "<li>"; //Starts next LI element
            if (challenges.getString(4) != null) {
                output += "<img src='css/images/completed.png'/>"; //Completed marker
            } else {
                output += "<img src='css/images/uncompleted.png'/>"; //Incomplete marker
            }
            //Final out put compilation
            output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(challenges.getString(3))
                    + "' href='javascript:;'>"
                    + encoder.encodeForHTML(bundle.getString(challenges.getString(1))) + "</a>";
            output += "</li>";
            rowNumber++;
        }
        //Check if output is empty
        if (output.isEmpty()) {
            output = "<li><a href='javascript:;'>No challenges found</a></li>";
        } else {
            log.debug("Appending End tags");
            output += "</ul></li>";
        }
    } catch (Exception e) {
        log.error("Challenge Retrieval: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getChallenges() ***");
    return output;
}

From source file:com.gnadenheimer.mg3.utils.Utils.java

public Boolean exectueBackUp(String backupDirectory) {
    try {/*from w w w  .j  ava2 s .c  o m*/
        //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH-mm-ss");
        //String backupfile = backupDirectory + "\\BackUp_" + sdf.format(new LocalDateTime());
        String backupfile = backupDirectory + "\\BackUp_"
                + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd-HH-mm-ss"));
        Connection conn = DriverManager.getConnection(getPersistenceMap().get("javax.persistence.jdbc.url"),
                getPersistenceMap().get("javax.persistence.jdbc.user"),
                getPersistenceMap().get("javax.persistence.jdbc.password"));

        try (CallableStatement cs = conn.prepareCall("CALL SYSCS_UTIL.SYSCS_BACKUP_DATABASE(?)")) {
            cs.setString(1, backupfile);
            cs.execute();
            cs.close();
        } catch (Exception ex) {
            LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
            App.showException(Thread.currentThread().getStackTrace()[1].getMethodName(), ex.getMessage(), ex);
        }
        App.showInfo("BackUp", "BackUp guardado con exito en: " + backupfile);
        return true;
    } catch (Exception ex) {
        LOGGER.error(Thread.currentThread().getStackTrace()[1].getMethodName(), ex);
        App.showException(Thread.currentThread().getStackTrace()[1].getMethodName(), ex.getMessage(), ex);
        return false;
    }
}