For the seventh assignment, write a Java program and an html form that first gets the name for a new column from the client and uses that to alter the database by adding the column. Then it should send back a second form to the client that sets up a table for entering data into that column. Collect the new data and use it to update the database. See the programs below for an example of how to do this.
Computer Science Example
A program that returns a second form to the client.
/* NewGradeProcessor is a Java program that gets an assignment name from the request, and adds a new column to the table.*/
package course;
import java.sql.*;
import java.io.*;
/* NewGradeProcessor gets the output page and a connection. It
then adds a new column to the table. */
public class NewGradeProcessor extends WebRequestProcessor
{
private PrintWriter out;
private Connection con;
private String assign;
public void process (Request request, Response
response)
{
try
{
out = response.getWriter ();
// Get a jdbc-odbc bridge and connect to addresses.mdb.
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection ("jdbc:odbc:course");
Page.createHeader (out, "Course Roster");
alterTable (out, request);
enterScores (out, request);
Page.createFooter (out);
con.close ();
} catch (ClassNotFoundException
e){System.out.println ("Class Not Found exception.\n");}
catch (SQLException e){System.out.println ("SQL Exception\n");}
} // process
/* alterTable adds a new column to the database
with the name given by the request parameter.*/
public void alterTable (PrintWriter out, Request
request)
{
assign = request.getParameter
("assign");
try
{
Statement stmt = con.createStatement ();
String query = "Alter Table StudentTable Add " + assign + " varchar
(10)";
int success = stmt.executeUpdate (query);
if (success == 0) out.println ("Alter error.");
else out.println ("Column inserted.");
stmt.close ();
} catch (SQLException
es) {out.println ("SQL Exception");}
} // alterTable
/* enterScores creates a form to be sent back
to the client. It will be used to enter the scores in the database.
*/
public void enterScores (PrintWriter out,
Request request)
{
try
{
out.println ("<form method = 'get' action='http:" +'/'+'/'+"localhost:8080/course.EnterScoresProcessor'>");
out.println ("<table cellspacing='10'>");
out.println ("<tr><td>ID</td><td>"+assign+"</td></tr>");
Statement stmt = con.createStatement ();
String query = "SELECT * FROM StudentTable";
ResultSet rs = stmt.executeQuery (query);
while (rs.next ())
{
String id = rs.getString ("ID");
String name = rs.getString ("Name");
out.println ("<tr><td>"+name+"</td>");
out.println ("<td><input name='"+id+"' type='text' value = '' size
= '10' /></td></tr>");
}
stmt.close ();
} catch (SQLException
es) {out.println ("SQL Exception");}
out.println
("</table><p><input type='submit' value='Send'></p></form>");
} // enterScores
} // class NewGradeProcessor
/* EnterScoresProcessor is a Java program that enters scores into the database.*/
package course;
import java.sql.*;
import java.io.*;
/* EnterScoresProcessor gets the output page and a connection.
It then adds the grades for the new assignment and displays the database.
*/
public class EnterScoresProcessor extends WebRequestProcessor
{
private PrintWriter out;
private Connection con;
private ResultSetMetaData metaData;
public void process (Request request, Response
response)
{
try
{
out = response.getWriter ();
// Get a jdbc-odbc bridge and connect to addresses.mdb.
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection ("jdbc:odbc:course");
Page.createHeader (out, "Enter Scores");
ResultSet rs = getTableData (con, out);
enterScores (out, request);
displayData (con, out);
Page.createFooter (out);
con.close ();
} catch (ClassNotFoundException
e){System.out.println ("Class Not Found exception.\n");}
catch (SQLException e){System.out.println ("SQL Exception\n");}
} // process
// getTableData is used to get the result set
and the meta data.
public ResultSet getTableData (Connection
con, PrintWriter out)
{
ResultSet rs
= null;
try
{
Statement stmt = con.createStatement ();
String query = "SELECT * FROM StudentTable";
rs = stmt.executeQuery (query);
metaData = rs.getMetaData ();
} catch (SQLException
es) {out.println ("SQL Exception");}
return rs;
} // getTableData
// enterScores enters the scores for the new
assignment.
public void enterScores (PrintWriter out,
Request request)
{
ResultSet rs
= getTableData (con, out);
String id, score,
assign;
try
{
int number = metaData.getColumnCount ();
assign = metaData.getColumnName (number);
while (rs.next ())
{
id = rs.getString ("ID");
score = request.getParameter (id);
Statement stmt = con.createStatement ();
String query = "UPDATE StudentTable SET "+assign+" = '" + score +
"' WHERE ID = '" + id + "'";
int success = stmt.executeUpdate (query);
if (success == 0) out.println ("Update error.");
stmt.close ();
}
} catch (SQLException
es) {out.println ("SQL Exception - Enter Scores");}
} // enterScores
// displayData sends a copy of the database
to the client formatted as a html table.
public void displayData (Connection con, PrintWriter
out)
{
try
{
int num = metaData.getColumnCount ();
out.println ("<h3>Course Roster</h3>");
out.println ("<table cellspacing='10'>");
out.println ("<tr>");
for (int count = 1; count <= num; count++)
out.println ("<td>"+metaData.getColumnName (count)+"</td>");
out.println ("</tr>");
Statement stmt = con.createStatement ();
String query = "SELECT * FROM StudentTable";
ResultSet rs = stmt.executeQuery (query);
while (rs.next ())
{
out.println ("<tr>");
for (int count = 1; count <= num; count ++)
out.println ("<td>" + rs.getString (count) + "</td>");
out.println ("</tr>");
}
stmt.close ();
} catch (SQLException
es) {out.println ("SQL Exception");}
out.println
("</table>");
} // displayData
} // class EnterScoresProcessor