Example usage for java.sql Connection 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:com.cws.esolutions.core.dao.impl.ApplicationDataDAOImpl.java

/**
 * @see com.cws.esolutions.core.dao.interfaces.IApplicationDataDAO#listApplications(int)
 *//*from w  ww.j  a va2  s. c o m*/
public synchronized List<String[]> listApplications(final int startRow) throws SQLException {
    final String methodName = IApplicationDataDAO.CNAME
            + "#listApplications(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 listApplications(?, ?)}");
        stmt.setInt(1, startRow);
        stmt.registerOutParameter(2, Types.INTEGER);

        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), // APPLICATION_GUID
                            resultSet.getString(2), // APPLICATION_NAME
                    };

                    if (DEBUG) {
                        DEBUGGER.debug("Value: {}", (Object[]) data);
                    }

                    responseData.add(data);
                }

                if (DEBUG) {
                    DEBUGGER.debug("Value: {}", 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:com.cws.esolutions.security.dao.userauth.impl.SQLAuthenticator.java

/**
 * @see com.cws.esolutions.security.dao.userauth.interfaces.Authenticator#performLogon(java.lang.String, java.lang.String)
 *///  w  ww  .  jav  a 2  s .  c o  m
public synchronized List<Object> performLogon(final String username, final String password)
        throws AuthenticatorException {
    final String methodName = SQLAuthenticator.CNAME
            + "#performLogon(final String user, final String password) throws AuthenticatorException";

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

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

    try {
        sqlConn = SQLAuthenticator.dataSource.getConnection();

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

        sqlConn.setAutoCommit(true);

        stmt = sqlConn.prepareCall("{CALL performAuthentication(?, ?)}");
        stmt.setString(1, username); // username
        stmt.setString(2, password); // password

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

        if (!(stmt.execute())) {
            throw new AuthenticatorException("No user was found for the provided user information");
        }

        resultSet = stmt.getResultSet();

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

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

            userAccount = new ArrayList<Object>();

            for (String returningAttribute : userAttributes.getReturningAttributes()) {
                if (DEBUG) {
                    DEBUGGER.debug("returningAttribute: {}", returningAttribute);
                }

                userAccount.add(resultSet.getString(returningAttribute));
            }

            if (DEBUG) {
                DEBUGGER.debug("List<Object>: {}", userAccount);
            }
        }
    } catch (SQLException sqx) {
        throw new AuthenticatorException(sqx.getMessage(), sqx);
    } finally {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

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

            if (!(sqlConn == null) && (!(sqlConn.isClosed()))) {
                sqlConn.close();
            }
        } catch (SQLException sqx) {
            throw new AuthenticatorException(sqx.getMessage(), sqx);
        }
    }

    return userAccount;
}

---

From source file:com.taobao.tddl.jdbc.group.TGroupConnection.java

private CallableStatement getCallableStatement(Connection conn, String sql, int resultSetType,
        int resultSetConcurrency, int resultSetHoldability) throws SQLException {
    if (resultSetType == Integer.MIN_VALUE) {
        return conn.prepareCall(sql);
    } else if (resultSetHoldability == Integer.MIN_VALUE) {
        return conn.prepareCall(sql, resultSetType, resultSetConcurrency);
    } else {//from  w ww .ja  va 2 s . c om
        return conn.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
    }
}

---

From source file:dbProcs.Getter.java

/**
 * This method returns modules in option tags in different <select> 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
 *///from   w  w w  .  ja  v a  2s.com
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:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getProductData(String, String) throws SQLException
 *//* ww  w.  j a v  a  2  s.  c  o m*/
@Override
public List<Object> getProductData(final String productId, final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getProductData(final int productId, final String lang) throws SQLException";

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

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

    try {
        sqlConn = this.dataSource.getConnection();

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

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

        stmt = sqlConn.prepareCall("{ CALL getProductData(?, ?) }");
        stmt.setString(1, productId);
        stmt.setString(2, lang);

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

        if (resultSet.next()) {
            resultSet.beforeFirst();
            results = new ArrayList<Object>();

            while (resultSet.next()) {
                results.add(resultSet.getString(1)); // PRODUCT_ID
                results.add(resultSet.getString(2)); // PRODUCT_NAME
                results.add(resultSet.getString(3)); // PRODUCT_SHORT_DESC
                results.add(resultSet.getString(4)); // PRODUCT_DESC
                results.add(resultSet.getBigDecimal(5)); // PRODUCT_PRICE
                results.add(resultSet.getBoolean(6)); // IS_FEATURED
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } 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();
        }
    }

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

    return results;
}

---

From source file:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getProductList(String) throws SQLException
 *///from   w ww.j  a va  2s. co m
@Override
public List<Object[]> getProductList(final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getProductList(final String lang) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> results = null;

    try {
        sqlConn = this.dataSource.getConnection();

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

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

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

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

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

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // PRODUCT_ID
                        resultSet.getString(2), // PRODUCT_NAME
                        resultSet.getString(3), // PRODUCT_SHORT_DESC
                        resultSet.getString(4), // PRODUCT_DESC
                        resultSet.getBigDecimal(5), // PRODUCT_PRICE
                        resultSet.getBoolean(6) // IS_FEATURED
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } 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();
        }
    }

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

    return results;
}

---

From source file:com.cws.us.pws.dao.impl.ProductReferenceDAOImpl.java

/**
 * @see com.cws.us.pws.dao.interfaces.IProductReferenceDAO#getFeaturedProducts(String) throws SQLException
 *//*w  w w. j  a  v  a 2 s . co m*/
@Override
public List<Object[]> getFeaturedProducts(final String lang) throws SQLException {
    final String methodName = IProductReferenceDAO.CNAME
            + "#getFeaturedProducts(final String lang) throws SQLException";

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

    Connection sqlConn = null;
    ResultSet resultSet = null;
    CallableStatement stmt = null;
    List<Object[]> results = null;

    try {
        sqlConn = this.dataSource.getConnection();

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

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

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

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

        if (!(stmt.execute())) {
            throw new SQLException("PreparedStatement is null. Cannot execute.");
        }

        resultSet = stmt.getResultSet();

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

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

            while (resultSet.next()) {
                Object[] data = new Object[] { resultSet.getString(1), // PRODUCT_ID
                        resultSet.getString(2), // PRODUCT_NAME
                        resultSet.getString(3), // PRODUCT_SHORT_DESC
                        resultSet.getString(4), // PRODUCT_DESC
                        resultSet.getBigDecimal(5), // PRODUCT_PRICE
                        resultSet.getBoolean(6) // IS_FEATURED
                };

                results.add(data);
            }

            if (DEBUG) {
                DEBUGGER.debug("results: {}", results);
            }
        }
    } 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();
        }
    }

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

    return results;
}

---

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

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

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

    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 removeServiceData(?)}");
        stmt.setString(1, datacenter);

        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

/**
 * Returns the result key for a module using the module's hash for the lookup procedure.
 * @param ApplicationRoot The current running context of the application
 * @param moduleHash The hash to use for module look up
 * @return The db stored solution key value for the moduleHash submited
 *///from   ww  w.j  av a2s.  c  o m
public static String getModuleResultFromHash(String ApplicationRoot, String moduleHash) {
    log.debug("*** Getter.getModuleResultFromHash ***");
    String result = new String();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        log.debug("hash '" + moduleHash + "'");
        CallableStatement callstmt = conn.prepareCall("call moduleGetResultFromHash(?)");
        log.debug("Gathering moduleGetResultFromHash ResultSet");
        callstmt.setString(1, moduleHash);
        ResultSet resultSet = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleGetResultFromHash");
        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 getModuleResultFromHash ***");
    return result;
}

---

From source file:dbProcs.Getter.java

/**
 * Used to gather a menu of lessons for a user, including markers for each lesson they have completed or not completed
 * @param ApplicationRoot The current running context of the application
 * @param userId Identifier of the user/*from ww w. ja v  a 2  s.  c om*/
 * @return HTML lesson menu for Open Floor Plan.
 */
public static String getLessons(String ApplicationRoot, String userId, Locale lang) {
    log.debug("*** Getter.getLesson ***");
    //Getting Translated Level Names
    ResourceBundle bundle = ResourceBundle.getBundle("i18n.moduleGenerics.moduleNames", lang);
    String output = new String();
    Encoder encoder = ESAPI.encoder();
    Connection conn = Database.getCoreConnection(ApplicationRoot);
    try {
        //Get the lesson modules
        CallableStatement callstmt = conn.prepareCall("call lessonInfo(?)");
        callstmt.setString(1, userId);
        log.debug("Gathering lessonInfo ResultSet for user " + userId);
        ResultSet lessons = callstmt.executeQuery();
        log.debug("Opening Result Set from moduleAllInfo");
        while (lessons.next()) {
            //log.debug("Adding " + lessons.getString(1));
            output += "<li>";
            //Markers for completion
            if (lessons.getString(4) != null) {
                output += "<img src='css/images/completed.png'/>";
            } else {
                output += "<img src='css/images/uncompleted.png'/>";
            }
            //Prepare lesson output
            output += "<a class='lesson' id='" + encoder.encodeForHTMLAttribute(lessons.getString(3))
                    + "' href='javascript:;'>" + encoder.encodeForHTML(bundle.getString(lessons.getString(1)))
                    + "</a>";
            output += "</li>";
        }
        //If no output has been found, return an error message
        if (output.isEmpty()) {
            output = "<li><a href='javascript:;'>No lessons found</a></li>";
        } else {
            log.debug("Lesson List returned");
        }
    } catch (Exception e) {
        log.error("lesson Retrieval: " + e.toString());
    }
    Database.closeConnection(conn);
    log.debug("*** END getLesson() ***");
    return output;
}

---

• «
• 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
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44
• 45
• 46
• 47
• 48
• 49
• 50
• »