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]