Piece of cake:
Problem: How to have dynamically-generated String data from a database
populate fields in a JSP form, have that data set the keys and values of a
DynaActionForm bean of type HashMap, and propogate any changes to the
database and display changes in real time to the user.
Elements:
1. JSP form page that retrieves records from the db by userid and date;
2. struts_config.xml defining the DynaActionForm Map beans and action
forwards;
3. Action class to process the user events in the JSP form;
4. DAO to trasfer data to and from the db;
5. A shit load of luck.
Code:
1. JSP:
<%-- the DynaActionForm bean --%>
<logic:present name="adminForm" scope="session">
<%-- the userid bean to bring it into page scope as a matter of convenience
--%>
<bean:define name="userid"
property="value"
type="java.lang.String"
id="key"/>
<%-- the logic to display the correct true checkboxes;
a radio btn cannot be used because multiple records requires that
the value be the userid and the true|false aspect of the checkbox is
discriminated by the name; --%>
<logic:equal name="adminForm" property="<%= "approved(" + key + ")"
%>" value="checked" scope="session">
<input type="checkbox"
name="approve"
value="<bean:write name="userid"
property="value"/>"
checked/>
APPROVE Expense Report for <bean:write name="userid"
property="value"/>
<input type="checkbox"
name="disapprove"
value="<bean:write name="userid"
property="value"/>"/>
DISAPPROVE Expense Report for <bean:write name="userid"
property="value"/>
</logic:equal>
<logic:equal name="adminForm" property="<%= "approved(" + key + ")"
%>" value="NO" scope="session">
<input type="checkbox"
name="approve"
value="<bean:write name="userid"
property="value"/>"/>
APPROVE Expense Report for <bean:write name="userid"
property="value"/>
<input type="checkbox"
name="disapprove"
value="<bean:write name="userid"
property="value"/>"
checked/>
DISAPPROVE Expense Report for <bean:write name="userid"
property="value"/>
</logic:equal>
</logic:present>
2. relevant struts-config.xml:
<form-bean name="adminForm" type="org.apache.struts.action.DynaActionForm">
<!-- globals -->
<form-property name="approved" type="java.util.HashMap"/>
</form-bean>
<%-- the action forwards - note that it forwards to itself to refresh the
GUI with the updated values --%>
<!-- Admin functions -->
<action path="/admin" type="com.qat.expense.AdminAction"
name="adminForm"
scope="session"
input="/admin.jsp">
<forward name="success" path="/admin.jsp"/>
<forward name="excel" path="isAdminExcel.jsp"/>
<forward name="logout" path="/logout.do"/>
</action>
3. relevant parts of Action class
DynaActionForm adminForm = ( DynaActionForm ) form ;
ActionErrors errors = new ActionErrors();
String week_ending = ( String ) adminForm.get( "week_ending" );
// process approval changes
if( !(( Map ) adminForm.get( "approved" )).isEmpty() ) {
StringBuffer query = new StringBuffer( "SELECT emp_id, approved FROM
total WHERE week_ending='" )
.append( week_ending )
.append( "' ORDER BY emp_id" );
try {
DBresults empIds = DatabaseUtilities.getQueryResults(
query.toString(), true );
String[] data = new String[ empIds.getRowCount() ];
String[] fields = new String[ empIds.getColCnt() ];
fields = empIds.getColNames();
String[] emp_ids = new String[ empIds.getRowCount() ];
String[] dbValues = new String[ empIds.getRowCount() ];
if( empIds.getRowCount() > 0 ) {
for( int i = 0; i < data.length; i++ ) {
data = empIds.getRow( i );
adminForm.set( "approved", data[ 0 ], data[
1 ]);
emp_ids[ i ] = data[ 0 ];
dbValues[ i ] = data[ 1 ];
}
}
String[] aValues = null;
String[] dValues = null;
if( request.getParameterValues( "approve" ) != null ) {
aValues = request.getParameterValues( "approve" );
}
if( request.getParameterValues( "disapprove" ) != null ) {
dValues = request.getParameterValues( "disapprove"
);
}
if( aValues != null ) {
for( int i = 0; i < aValues.length; i++ ) {
adminForm.set( "approved", aValues[ i ],
"checked" );
for( int j = 0; j < emp_ids.length; j++ ) {
if( aValues[ i ].equals( emp_ids[ j
] )) {
StringBuffer update = new
StringBuffer( "UPDATE total SET approved='checked' WHERE emp_id='" )
.append( aValues[ i
] )
.append( "' AND
week_ending='" )
.append( week_ending
)
.append( "'" );
int k =
DatabaseUtilities.modifyDB( update.toString() );
}
}
}
}
if( dValues != null ) {
for( int i = 0; i < dValues.length; i++ ) {
adminForm.set( "approved", dValues[ i ],
"NO" );
for( int j = 0; j < emp_ids.length; j++ ) {
if( dValues[ i ].equals( emp_ids[ j
] )) {
StringBuffer update = new
StringBuffer( "UPDATE total SET approved='NO' WHERE emp_id='" )
.append( dValues[ i
] )
.append( "' AND
week_ending='" )
.append( week_ending
)
.append( "'" );
int k =
DatabaseUtilities.modifyDB( update.toString() );
}
}
}
}
} catch( SQLException e ) {
e.printStackTrace();
} catch( NamingException e ) {
e.printStackTrace();
}
}
return( retrieve( adminForm, week_ending, request, mapping ));
}
4. DAO and helper class
/**
* DAO Class to store completed results of JDBC. Differs from a
<code>ResultSet</code>
* in several ways (shameless stolen from Craig's code):
* <ul>
* <li><code>ResultSet</code> does not necessarily have all the
data; another call to
* the database in necessary for additional rows.</li>
* <li>This class stores results as <code>String</code> objects in
arrays.</li>
* <li>This class includes <code>DatabaseMetaData</code> and
<code>ResultSetMetaData</code>.</li>
* <li>This class has a toHTMLTable() method that turns the results
into a long
* <code>String</code> corresponding to an HTML table.</li>
* </ul>
*
* @author Mark Galbreath ([EMAIL PROTECTED])
* @version 1.0 March 2003
*/
public class DBresults {
private Connection conn = null;
private String dbName = null;
private String dbVersion = null;
private String[] colNames = null;
private String[] rowData = null;
private String[] data = null;
private int colCnt = 0;
private Vector queryResults = null;
/**
* Public constructor to initialize objects states.
*
* @param conn The <code>Connection</code> object used to connection
to the database.
* @param dbName The <code>String</code> name of the database.
* @param dbVersion The <code>String</code> version of the database.
* @param colCnt The <code>int</code> number of columns in the query
table.
* @param colNames The <code>String[]</code> array of column names.
*/
public DBresults( Connection conn,
String dbName,
String dbVersion,
int colCnt,
String[] colNames ) {
this.conn = conn;
this.dbName = dbName;
this.dbVersion = dbVersion;
this.colCnt = colCnt;
this.colNames = colNames;
rowData = new String[ colCnt ];
queryResults = new Vector();
}
/**
* Connection accessor.
*
* @return conn The <code>Connection</code> object.
*/
public Connection getConnection() {
return( conn );
}
/**
* Database name accessor.
*
* @return dbName The <code>String</code> name of the database.
*/
public String getDbName() {
return( dbName );
}
/**
* Database version accessor.
*
* @return dbVersion The <code>String</code> db version.
*/
public String getDbVersion() {
return( dbVersion );
}
/**
* Column count accessor.
*
* @return colCnt The <code>int</code> number of table columns.
*/
public int getColCnt() {
return( colCnt );
}
/**
* Column names accessor.
*
* @return colNames The <code>String[]</code> array of table column
names.
*/
public String[] getColNames() {
return( colNames );
}
/**
* Returned rows count accessor.
*
* @return queryResults.size() The <code>int</code> number of rows.
*/
public int getRowCount() {
return( queryResults.size() );
}
/**
* Row data accessor.
*
* @param index The <code>int</code> index of the data element.
* @return String[ index ] The <code>String[]</code> containing the
row data.
*/
public String[] getRow( int index ) {
try {
data = ( String[] ) queryResults.elementAt( index );
} catch( ArrayIndexOutOfBoundsException e ) {
System.out.println( "No row returned by database: "
+ e.getMessage() );
return( null );
}
return( data );
}
/**
* Add a row of data to the queryResults Vector.
*
* @param row The <code>String[]</code> row of data to add to the
Vector.
*/
public void addRow( String[] row ) {
queryResults.addElement( row );
}
}
package com.qat.expense;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;
/**
* <code>DatabaseUtitilies</code> and its associated classes provide
convenience
* methods for application objects to query the database.
*
* @author Mark Galbreath ([EMAIL PROTECTED])
* @version 1.0 March 2003
*/
public class DatabaseUtilities {
/**
* Default empty constructor.
*/
public DatabaseUtilities() {
}
/**
* Connect to database, execute specified query, and accumulate
results into
* a DBresults object. If the db connection is left open, it can be
retrieved
* with DBresults.getConnection().
*
* @param query The <code>String</code> SQL query to execute.
* @param close The <code>boolean</code> flag indicating whether or
not
* to close the connection.
* @return DBresults The object encapsulating the query result.
* @exception ClassNotFoundException Thrown if the db driver class
cannot be found.
* @exception SQLException Thrown if the query fails for some
reason.
*/
public static DBresults getQueryResults( String query, boolean close
) throws NamingException, SQLException {
InitialContext ctx = new InitialContext();
DataSource ds = ( DataSource ) ctx.lookup(
"com.qat.db.expense" );
Connection conn = ds.getConnection();
return( getQueryResults( conn, query, close ));
}
/**
* Override method that retrieves query results using an open
connection.
*
* @param conn The <code>Connection</code> to the database.
* @param query The <code>String</code> SQL query to be executed.
* @param close The <code>boolean</code> flag indicating whether to
close the connection.
* @return dbResults The <code>DBresults</code> object encapsulating
the query result.
* @exception SQLException Thrown if the query fails.
*/
public static DBresults getQueryResults( Connection conn, String
query, boolean close ) {
try {
DatabaseMetaData dbMetaData = conn.getMetaData();
String dbName = dbMetaData.getDatabaseProductName();
String dbVersion =
dbMetaData.getDatabaseProductVersion();
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery( query );
ResultSetMetaData rsetMetaData = rset.getMetaData();
int colCnt = rsetMetaData.getColumnCount();
String[] colNames = new String[ colCnt ];
for( int i = 1; i < colCnt + 1; i++ ) {
colNames[ i - 1 ] =
rsetMetaData.getColumnName( i ).trim();
}
DBresults dbResults = new DBresults( conn, dbName,
dbVersion, colCnt, colNames );
while( rset.next() ) {
String[] row = new String[ colCnt ];
for( int i = 1; i < colCnt + 1; i++ ) {
String datum = rset.getString( i );
if( datum != null ) {
datum = datum.trim();
}
row[ i - 1 ] = datum;
}
dbResults.addRow( row );
}
if( close ) {
conn.close();
}
return( dbResults );
} catch( SQLException e ) {
System.out.println( "Problem querying database: " +
e.getMessage() );
}
return( null );
}
/**
* Modify database tables with SQL inserts.
*
* @param sql The <code>String</code> sql statement to be executed.
* @return rows The <code>int</code> number of rows added.
* @exception SQLException Thrown if query fails.
* @exception IOException Thrown if database connection fails.
*/
public static int modifyDB( String sql ) {
try {
InitialContext ctx = new InitialContext();
DataSource ds = ( DataSource ) ctx.lookup(
"com.qat.db.expense" );
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
return( stmt.executeUpdate( sql ) );
} catch( NamingException e ) {
System.out.println( "JNDI lookup error: " +
e.getMessage() );
} catch( SQLException e ) {
System.out.println( "Error connecting to database: "
+ e.getMessage() );
}
return( 0 );
}
}
-----Original Message-----
From: Rick Reumann [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 2:03 PM
To: Struts Users Mailing List
Subject: 4th Of July Struts Challenge...
Ok stupid subject line, but now I can get back to something I was curious
about that I posted around a week ago. I'm really curious how to do
accomplish this and yes have tried it a bunch of different ways...
Here's the challenge....
First challenge is just with a regular ActionForm...
1) Your ActionForm has to have a property of type Map. For this adventure
call it employeesMap.
2) Each map will hold for the key and employeeID ( String ssn - social
security number whatever). The value will be an EmployeeBean. For testing
sake just have it have two properties String name, String age.
3) Put two employees into the Map and put this Map into your ActionForm:
HashMap empMap = new HashMap(); empMap.put( "1111", new EmployeeBean("John
Doe", "33" ) ); empMap.put( "2222", new EmployeeBean("Loser Boy", "22" ) );
setEmployeesMap( empMap );
4) Now have a jsp form iterate over this Map and provide text fields to edit
the name and age of each employee. When the form is submitted there should
be a way that it will submit this Map with updated EmployeeBeans with the
new names and ages for each key (1111 and 2222 ). Pull the map out of the
action you submit to and print the properties of the EmployeeBeans to test.
Second challenge... is do the above using your employeesMap as a property of
a DynaActionForm.
Preferably use JSTL and/or struts-el also would be nice.
(First one to successfully complete this challenge will win 100 dollars for
each person that they forward this e-mail to, as Microsoft will be
monitoring all the e-mails as well. That kid doing this project for his
science fair project to see how far e-mails travel will also be involved, so
please reply to him. The 100 dollars will come from that African tribe
leader with that money he is just dying to give away if you just contact
him. Some of the money might come from the stolen tourist kidney sales in
Mexico, but I'm not positive of that).
--
Rick
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
