package com.ora.jsp.sql;
import java.util.*;
import java.sql.*;
import com.ora.jsp.sql.value.*;
/**
* 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 a SQL statement with question
* marks as placeholders for the Value objects in the values
* property.
*
* @author Hans Bergsten, Gefion software <hans@gefionsoftware.com>
* @version 1.0
*/
public class SQLCommandBean {
private Connection conn;
private String sqlValue;
private Vector values;
private boolean isExceptionThrown = false;
/**
* Sets the Connection to use.
*/
public void setConnection(Connection conn) {
this.conn = conn;
}
/**
* Set the SQL string, possibly with ? place holders for
* values set by setValues().
*/
public void setSqlValue(String sqlValue) {
this.sqlValue = sqlValue;
}
/**
* Sets the values to use for the place holders in the SQL
* string. The Vector must contain one Value object for
* each place holder.
*/
public void setValues(Vector values) {
this.values = values;
}
/**
* Executes the specified SQL string as a query and returns
* a Vector with Row objects, or an empty Vector if no rows
* where found.
*
* @return a Vector of Row objects
* @exception SQLException
* @exception UnsupportedTypeException, if the returned value
* doesn't match any Value subclass
*/
public Vector executeQuery() throws SQLException, UnsupportedTypeException {
Vector rows = 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);
}
// Save the result in a Vector of Row object
rows = toVector(rs);
}
finally {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (pstmt != null) {
pstmt.close();
}
}
catch (SQLException e) {
// Ignore. Probably caused by a previous SQLException thrown
// by the outer try block
}
}
return rows;
}
/**
* 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
*/
public int executeUpdate() throws 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 {
try {
if (rs != null) {
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (pstmt != null) {
pstmt.close();
}
}
catch (SQLException e) {
// Ignore. Probably caused by a previous SQLException thrown
// by the outer try block
}
}
return noOfRows;
}
/**
* Calls setXXX() methods on the PreparedStatement for all Value
* objects in the values Vector.
*
* @param pstmt the PreparedStatement
* @param values a Vector with Value objects
* @exception SQLException
*/
private void setValues(PreparedStatement pstmt, Vector values)
throws SQLException {
for (int i = 0; i < values.size(); i++) {
try {
Value v = (Value) values.elementAt(i);
// Set the value using the method corresponding to the type.
// Note! Set methods are indexed from 1, so we add 1 to i
if (v instanceof BigDecimalValue) {
pstmt.setBigDecimal(i + 1, v.getBigDecimal());
}
else if (v instanceof BooleanValue) {
pstmt.setBoolean(i + 1, v.getBoolean());
}
else if (v instanceof ByteValue) {
pstmt.setByte(i + 1, v.getByte());
}
else if (v instanceof BytesValue) {
pstmt.setBytes(i + 1, v.getBytes());
}
else if (v instanceof DateValue) {
pstmt.setDate(i + 1, v.getDate());
}
else if (v instanceof DoubleValue) {
pstmt.setDouble(i + 1, v.getDouble());
}
else if (v instanceof FloatValue) {
pstmt.setFloat(i + 1, v.getFloat());
}
else if (v instanceof IntValue) {
pstmt.setInt(i + 1, v.getInt());
}
else if (v instanceof LongValue) {
pstmt.setLong(i + 1, v.getLong());
}
else if (v instanceof ShortValue) {
pstmt.setShort(i + 1, v.getShort());
}
else if (v instanceof StringValue) {
pstmt.setString(i + 1, v.getString());
}
else if (v instanceof TimeValue) {
pstmt.setTime(i + 1, v.getTime());
}
else if (v instanceof TimestampValue) {
pstmt.setTimestamp(i + 1, v.getTimestamp());
}
else {
pstmt.setObject(i + 1, v.getObject());
}
}
catch (UnsupportedConversionException e) {
// Can not happen here since we test the type first
}
}
}
/**
* Gets all data from the ResultSet and returns it as a Vector,
* of Row objects.
*
* @param rs the ResultSet
* @return a Vector of Row objects
* @exception SQLException, thrown by the JDBC API calls
* @exception UnsupportedTypeException, if the returned value
* doesn't match any Value subclass
*/
private Vector toVector(ResultSet rs) throws SQLException,
UnsupportedTypeException {
Vector rows = new Vector();
while (rs.next()) {
Row row = new Row(rs);
rows.addElement(row);
}
return rows;
}
} |
