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

/**
 * This method hashes the user submitted password and sends it to the database.
 * The database does the rest of the work, including Brute Force prevention.
 * @param userName The submitted user name to be used in authentication process
 * @param password The submitted password in plain text to be used in authentication
 * @return A string array made up of nothing or information to be consumed by the initiating authentication process.
 *///from   w w w .  j  av  a 2 s  .  c  om

public static String[] authUser(String ApplicationRoot, String userName, String password) {
    String[] result = null;
    log.debug("$$$ Getter.authUser $$$");
    log.debug("userName = " + userName);

    boolean userFound = false;
    boolean goOn = false;
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        //See if user Exists
        CallableStatement callstmt = conn.prepareCall("call userFind(?)");
        log.debug("Gathering userFind ResultSet");
        callstmt.setString(1, userName);
        ResultSet userFind = callstmt.executeQuery();
        log.debug("Opening Result Set from userFind");
        try {
            userFind.next();
            log.debug("User Found"); //User found if a row is in the database, this line will not work if the result set is empty
            userFound = true;
        } catch (Exception e) {
            log.debug("User did not exist");
            userFound = false;
        }
        if (userFound) {
            //Authenticate User
            callstmt = conn.prepareCall("call authUser(?, ?)");
            log.debug("Gathering authUser ResultSet");
            callstmt.setString(1, userName);
            callstmt.setString(2, password);
            ResultSet loginAttempt = callstmt.executeQuery();
            log.debug("Opening Result Set from authUser");
            try {
                loginAttempt.next();
                goOn = true; //Valid password for user submitted
            } catch (SQLException e) {
                //... Outer Catch has preference to this one for some reason... This code is never reached!
                // But I'll leave it here just in case. That includes the else block if goOn is false
                log.debug("Incorrect Credentials");
                goOn = false;
            }
            if (goOn) {
                //ResultSet Not Empty => Credentials Correct
                result = new String[5];
                result[0] = loginAttempt.getString(1); //Id
                result[1] = loginAttempt.getString(2); //userName
                result[2] = loginAttempt.getString(3); //role
                result[4] = loginAttempt.getString(6); //classId
                if (loginAttempt.getBoolean(5)) //Checking for temp password flag, if true, index View will prompt to change
                    result[3] = "true";
                else
                    result[3] = "false";
                if (!result[1].equals(userName)) //If somehow this functionality has been compromised to sign in as other users, this will limit the expoitability. But the method is sql injection safe, so it should be ok
                {
                    log.fatal("User Name used (" + userName + ") and User Name retrieved (" + result[1]
                            + ") were not the Same. Nulling Result");
                    result = null;
                } else {
                    log.debug("User '" + userName + "' has logged in");
                    //Before finishing, check if user had a badlogin history, if so, Clear it
                    if (loginAttempt.getInt(4) > 0) {
                        log.debug("Clearing Bad Login History");
                        callstmt = conn.prepareCall("call userBadLoginReset(?)");
                        callstmt.setString(1, result[0]);
                        callstmt.execute();
                        log.debug("userBadLoginReset executed!");
                    }
                }
                //User has logged in, or a Authentication Bypass was detected... You never know! Better safe than sorry   
                return result;
            }
        }
    } catch (SQLException e) {
        log.error("Login Failure: " + e.toString());
        result = null;
        //Lagging Response
    }
    Database.closeConnection(conn);
    log.debug("$$$ End authUser $$$");
    return result;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServiceDataDAO#listServices(int)
 *///from w  ww  .  jav  a  2  s . c  o  m
public synchronized List<String[]> listServices(final int startRow) throws SQLException {
    final String methodName = IServiceDataDAO.CNAME + "#listServices(final int startRow) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<String[]> 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 listServices(?)}");
        stmt.setInt(1, startRow);

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

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

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

            if (resultSet.next()) {
                resultSet.beforeFirst();
                responseData = new ArrayList<String[]>();

                while (resultSet.next()) {
                    String[] data = new String[] { resultSet.getString(1), // GUID
                            resultSet.getString(2), // SERVICE_TYPE
                            resultSet.getString(3), // NAME
                    };

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("List<String>: {}", responseData);
                }
            }
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), 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

/**
 * Used to return a module cheat sheet//from   www. j  a v a2s .c om
 * @param ApplicationRoot The current running context of the application
 * @param moduleId The identifier of the module to return the cheat sheet for
 * @param lang The Locale the user has enabled
 * @return String[] containing {ModuleName, CheatSheetSolution}
 */
public static String[] getModuleSolution(String ApplicationRoot, String moduleId, Locale lang) {
    log.debug("*** Getter.getModuleSolution ***");
    String[] result = new String[2];
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    //Getting Translations
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.cheatsheets.solutions", lang);
    try {
        CallableStatement callstmt = conn.prepareCall("call cheatSheetGetSolution(?)");
        log.debug("Gathering cheatSheetGetSolution ResultSet");
        callstmt.setString(1, moduleId);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from cheatSheetGetSolution");
        resultSet.next();
        result[0] = resultSet.getString(1);
        result[1] = bundle.getString(resultSet.getString(2));

    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getModuleSolution ***");
    return result;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IServerDataDAO#removeServer(java.lang.String)
 *///from ww w. j  a  v  a 2s . c o  m
public synchronized boolean removeServer(final String serverGuid) throws SQLException {
    final String methodName = IServerDataDAO.CNAME
            + "#removeServer(final String serverGuid) throws SQLException";

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

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL removeServerFromAssets(?)}");
        stmt.setString(1, serverGuid);

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

        isComplete = (!(stmt.execute()));

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

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

    return isComplete;
}

From source file:dbProcs.Getter.java

/**
 * This method is used to gather users according by class. Thanks to MySQL syntax, where class = null will return nothing, is null must be used.
 *  <br/>is 'validClass' will Error, = 'validclass' must be used.<br/>
 * So there are two procedures this method calls. One that handles null classes, one that does not
 * @param ClassId Identifier of class/*ww  w.  j  a  v a2s .com*/
 * @param ApplicationRoot The current running context of the application
 * @return ResultSet that contains users for the selected class in the formate {userId, userName, userAddress}
 */
public static ResultSet getPlayersByClass(String ApplicationRoot, String classId) {
    ResultSet result = null;
    log.debug("*** Getter.getPlayersByClass (Single Class) ***");
    log.debug("classId: '" + classId + "'");
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        CallableStatement callstmt = null;
        if (classId != null) {
            log.debug("Gathering playersByClass ResultSet");
            callstmt = conn.prepareCall("call playersByClass(?)");
            callstmt.setString(1, classId);
            log.debug("Returning Result Set from playersByClass");
        } else {
            log.debug("Gathering playersWithoutClass ResultSet");
            callstmt = conn.prepareCall("call playersWithoutClass()");
            log.debug("Returning Result Set from playersByClass");
        }
        ResultSet resultSet = callstmt.executeQuery();
        result = resultSet;
        resultSet.next();
    } catch (SQLException e) {
        log.error("Could not execute query: " + e.toString());
        result = null;
    }
    log.debug("*** END getPlayersByClass");
    return result;
}

From source file:com.jedi.oracle.OracleCall.java

@Override
public void execute(Connection connection) throws Exception {
    if (connection == null) {
        DataSource dataSource = DataSourceManager.getInstance().getDataSource();
        if (dataSource == null) {
            throw new RuntimeException("Datasource is null");
        }/*from  w w  w .ja  va 2  s.  co m*/

        connection = dataSource.getConnection();
    }

    fillParametersFromFields();

    String sql = this.createSQL(this.getName());
    Map customTypes = this.getTypeMap();
    if (customTypes != null && !customTypes.isEmpty()) {
        Map map = connection.getTypeMap();
        map.putAll(customTypes);
        connection.setTypeMap(map);
    }

    OracleCallableStatement statement = (OracleCallableStatement) connection.prepareCall(sql);
    OracleParameterUtils.register(statement, this.parameters);
    try {
        statement.execute();
    } catch (SQLException e) {
        if (reExecutionRequired(e)) {
            statement.execute();
        } else {
            throw e;
        }
    }

    OracleParameterUtils.bind(this.parameters, statement);
    fillFieldValuesFromParameters();
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#deleteMessage(String)
 *//* w w w  .j  a v  a2  s  .c o  m*/
public synchronized boolean deleteMessage(final String messageId) throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME
            + "#deleteMessage(final String messageId) throws SQLException";

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

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

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

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

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

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

    return isComplete;
}

From source file:dbProcs.Getter.java

/**
 * Use to return the current progress of a class in JSON format with information like user name, score and completed modules
 * @param applicationRoot The current running context of the application
 * @param classId The identifier of the class to use in lookup
 * @return A JSON representation of a class's progress in the application
 */// ww  w.j  a  v  a  2s .  c om
@SuppressWarnings("unchecked")
public static String getProgressJSON(String applicationRoot, String classId) {
    log.debug("*** Getter.getProgressJSON ***");

    String result = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(applicationRoot);
    try {
        log.debug("Preparing userProgress call");
        //Returns User's: Name, # of Completed modules and Score
        CallableStatement callstmnt = conn.prepareCall("call userProgress(?)");
        callstmnt.setString(1, classId);
        log.debug("Executing userProgress");
        ResultSet resultSet = callstmnt.executeQuery();
        JSONArray json = new JSONArray();
        JSONObject jsonInner = new JSONObject();
        int resultAmount = 0;
        while (resultSet.next()) //For each user in a class
        {
            resultAmount++;
            jsonInner = new JSONObject();
            if (resultSet.getString(1) != null) {
                jsonInner.put("userName", new String(encoder.encodeForHTML(resultSet.getString(1)))); //User Name
                jsonInner.put("progressBar", new Integer(resultSet.getInt(2) * widthOfUnitBar)); //Progress Bar Width
                jsonInner.put("score", new Integer(resultSet.getInt(3))); //Score
                log.debug("Adding: " + jsonInner.toString());
                json.add(jsonInner);
            }
        }
        if (resultAmount > 0)
            result = json.toString();
        else
            result = new String();
    } catch (SQLException e) {
        log.error("getProgressJSON Failure: " + e.toString());
        result = null;
    } catch (Exception e) {
        log.error("getProgressJSON Unexpected Failure: " + e.toString());
        result = null;
    }
    Database.closeConnection(conn);
    log.debug("*** END getProgressJSON ***");
    return result;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#insertMessage(List)
 *//*ww  w  .  j a  va 2s .co m*/
public synchronized boolean insertMessage(final List<Object> messageList) throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME
            + "#insertMessage(final List<Object> messageList) throws SQLException";

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

    Connection sqlConn = null;
    CallableStatement stmt = null;
    boolean isComplete = false;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL submitSvcMessage(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, (String) messageList.get(0)); // message id
        stmt.setString(2, (String) messageList.get(1)); // message title
        stmt.setString(3, (String) messageList.get(2)); // message text
        stmt.setString(4, (String) messageList.get(3)); // author email
        stmt.setBoolean(5, (Boolean) messageList.get(4)); // is active
        stmt.setBoolean(6, (Boolean) messageList.get(5)); // is alert
        stmt.setBoolean(7, (Boolean) messageList.get(6)); // does expire
        stmt.setLong(8, (messageList.get(7) == null) ? 0 : (Long) messageList.get(7)); // expiry date

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

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

    return isComplete;
}

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

/**
 * @see com.cws.esolutions.core.dao.interfaces.IWebMessagingDAO#updateMessage(String, List)
 *//*from  ww w.j a  va2  s.co m*/
public synchronized boolean updateMessage(final String messageId, final List<Object> messageList)
        throws SQLException {
    final String methodName = IWebMessagingDAO.CNAME
            + "#updateMessage(final String messageId, final List<Object> messageList) throws SQLException";

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

    Connection sqlConn = null;
    boolean isComplete = false;
    CallableStatement stmt = null;

    try {
        sqlConn = dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);
        stmt = sqlConn.prepareCall("{CALL updateServiceMessage(?, ?, ?, ?, ?, ?, ?, ?)}");
        stmt.setString(1, messageId); // messageId
        stmt.setString(2, (String) messageList.get(0)); // messageTitle
        stmt.setString(3, (String) messageList.get(1)); // messageText
        stmt.setBoolean(4, (Boolean) messageList.get(2)); // active
        stmt.setBoolean(5, (Boolean) messageList.get(3)); // alert
        stmt.setBoolean(6, (Boolean) messageList.get(4)); // expiry
        stmt.setLong(7, (messageList.get(5) == null) ? 0 : (Long) messageList.get(5)); // expiry date
        stmt.setString(8, (String) messageList.get(6)); // modifyAuthor

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

        isComplete = (!(stmt.execute()));

        if (DEBUG) {
            DEBUGGER.debug("isComplete: {}", isComplete);
        }
    } catch (SQLException sqx) {
        ERROR_RECORDER.error(sqx.getMessage(), sqx);

        throw new SQLException(sqx.getMessage(), sqx);
    } finally {
        if (stmt != null) {
            stmt.close();
        }

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

    return isComplete;
}