FileDocCategorySizeDatePackage
SQLCommandBean.javaAPI DocExample4747Tue Feb 28 11:34:06 GMT 2006com.ora.jsp.beans.sql

SQLCommandBean

public class SQLCommandBean extends Object
This class is a bean for executing SQL statements. It has three properties that can be set: connection, sqlValue and values. The connection and sqlValue properties must always be set before calling one of the execute methods. If the values property is set, the sqlValue property must be an SQL statement with question marks as placeholders for the value objects in the values property.
author
Hans Bergsten, Gefion software
version
2.0

Fields Summary
private Connection
conn
private String
sqlValue
private List
values
Constructors Summary
Methods Summary
public ResultexecuteQuery()
Executes the specified SQL string as a query and returns a Result object

return
a javax.servlet.jsp.jstl.sql.Result object
exception
SQLException

        Result result = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        try {
            if (values != null && values.size() > 0) {
                // Use a PreparedStatement and set all values
                pstmt = conn.prepareStatement(sqlValue);
                setValues(pstmt, values);
                rs = pstmt.executeQuery();
            }
            else {
                // Use a regular Statement
                stmt = conn.createStatement();
                rs = stmt.executeQuery(sqlValue);
            }
            result = ResultSupport.toResult(rs);
        }
        finally {
            if (rs != null) {
                try {rs.close();} catch (SQLException e) {}
            }
            if (stmt != null) {
                try {stmt.close();} catch (SQLException e) {}
            }
            if (pstmt != null) {
                try {pstmt.close();} catch (SQLException e) {}
            }
        }
        return result;
public intexecuteUpdate()
Executes the specified SQL string (any statement except SELECT, such as UPDATE, INSERT, DELETE or CREATE TABLE) and returns the number of rows affected by the statement, or 0 if none.

return
the number of rows affected
exception
SQLException

        int noOfRows = 0;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        Statement stmt = null;
        try {
            if (values != null && values.size() > 0) {
                // Use a PreparedStatement and set all values
                pstmt = conn.prepareStatement(sqlValue);
                setValues(pstmt, values);
                noOfRows = pstmt.executeUpdate();
            }
            else {
                // Use a regular Statement
                stmt = conn.createStatement();
                noOfRows = stmt.executeUpdate(sqlValue);
            }
        }
        finally {
            if (rs != null) {
                try {rs.close();} catch (SQLException e) {}
            }
            if (stmt != null) {
                try {stmt.close();} catch (SQLException e) {}
            }
            if (pstmt != null) {
                try {pstmt.close();} catch (SQLException e) {}
            }
        }
        return noOfRows;
public voidsetConnection(java.sql.Connection conn)
Sets the Connection to use.

        this.conn = conn;
public voidsetSqlValue(java.lang.String sqlValue)
Set the SQL string, possibly with question mark placeholders for values set by setValues().

        this.sqlValue = sqlValue;
public voidsetValues(java.util.List values)
Sets the values to use for the place holders in the SQL string. The List must contain one Object for each place holder, suitable for use with the PreparedStatement setObject() method.

        this.values = values;
private voidsetValues(java.sql.PreparedStatement pstmt, java.util.List values)
Calls setObject() method on the PreparedStatement for all objects in the values List.

param
pstmt the PreparedStatement
param
values a List with objects
exception
SQLException

        for (int i = 0; i < values.size(); i++) {
	    Object v = values.get(i);
	    // Set the value using the method corresponding to the type.
	    // Note! Set methods are indexed from 1, so we add 1 to i
	    pstmt.setObject(i + 1, v);
        }