This chapter is from the book
This chapter is from the book
This chapter is from the book
Data Source for Reports
The data source for the examples in this and the following chapters come from two tables in the xmlbook database created in Appendix A,"Setting Up." The data in these tables contains some very basic information about clinic patients and their health ailments. These tables are ReportClientData and ReportClientAilment and between them there is a one-to-many relationships. That is, for each patient in ReportClientData, there might be multiple ailment records in ReportClientAilment. This relationship was created, and will be used in the next two chapters, to aid in demonstrating stylesheet methods for dealing with these types of data relationships.
Creating Database Data
The first thing that must be done is to populate the two previously mentioned tables with data. A JSP has been created to create as many records as are necessary for this purpose, and its description follows. This JSP contains a simple form that allows the user to enter the number of client records to be created.
When the form is submitted, the same JSP will begin by creating the requested number of client records. As each client record is created, a random number of ailment records are created corresponding to that client record.
The JSP named CreateDBData.jsp found in Listing 11.1 begins with an import statement and the small HTML form that allows the user to enter the number of rows of data that are created. Next, the variables are set, and the parameter entered through the form is parsed through a try-catch block as follows:
try{
client_rows = Integer.parseInt(request.getParameter("client_rows"));
}
//catch any and all errors with form data
catch(NumberFormatException e){
client_rows = 0;
}
If any errors result from the parsing of the form variable, such as a nonexistent value or a non-integer value, the parameter is set to 0 in the preceding catch block. This will prevent the creation of any new records.
After creating our database Connection and Statement objects, the looping construct that will insert client records into the database begins:
for(int i = 0; i < client_rows; i++){
db_statement.execute(createQueryClient());
ResultSet rs = db_statement.executeQuery("select LAST_INSERT_ID()");
rs.last();
int last_insert_id = rs.getInt(1);
Each iteration of the preceding for loop will insert one record into the ReportClientData table. After which, a statement is executed, and the resulting ResultSet is traversed to find the identity value of the newly inserted record.
This identity value is used to key ailment records with a client record. The identity value of the corresponding client record will be contained in each ailment record. This will result a one-client-to-many-ailments relationship that will be used in the following reporting chapter.
Next, a random number is generated to determine how many ailment records will be created for the current client. This number is used to loop through the creation of each ailment record:
int client_ailment = (int) (Math.random() * 5);
for(int k = 0; k < client_ailment; k++){
try{
db_statement.execute(createQueryAilment(last_insert_id));
out.print("<br />Ailment added for record index: " +
last_insert_id + ". With " + (k+1) + " out of " +
client_ailment + " Records.");
ailment_rows++;
} catch (SQLException e){
out.print("<br />Ailment skipped for record index:" +
last_insert_id + ". Due to duplicate." );
}
}
The processing of these statements is wrapped in a try-catch block to enable the handling of SQLExceptions. All the data is created randomly, and this causes the occasional duplicate ailment record to be created. Duplicates are unacceptable in this table because each client can only have one record indicating an ailment. This duplication error is handled in the preceding catch block, and permits the rest of the processing to continue.
Next is the code that outputs status statements and catch blocks to catch the various exceptions that the previous statements can create. The page declaration follows the end of the scriptlet.
This is where the methods that create our dates, names, and other record data reside. Following these are the methods that bring it all together to create the complete insert statements executed against the database.
The complete JSP that will dynamically create and insert data can be found in Listing 11.1 and should be saved as \webapps\xmlbook\chapter11\CreateDBData.jsp.
Listing 11.1 CreateDBData.jsp in Its Complete Form
<%@page import = "java.sql.*" %>
<html>
<head><title></title></head>
<body>
<form method="post">
<table>
<tr>
<td align="right">Number of rows to create:</td>
<td><input type="text" name="client_rows" value="" size="5"/></td>
</tr>
<tr>
<td colspan="2" align="left">
<br /><br /><input type="submit" value="Create Records" />
</td>
</tr>
<table>
</form>
<%
// 1. Initialize variables
int client_rows;
String final_status = "There were 0 Client Data records added.";
String db_query = "";
String db_location = "jdbc:mysql://localhost/xmlbook";
String db_driver = "org.gjt.mm.mysql.Driver";
int ailment_rows = 0;
try{
client_rows = Integer.parseInt(request.getParameter("client_rows"));
}
//catch any and all errors with form data
catch(NumberFormatException e){
client_rows = 0;
}
// 2. Make a database connection
Connection db_connection = null;
try{
Class.forName(db_driver);
db_connection = DriverManager.getConnection(db_location);
Statement db_statement = db_connection.createStatement();
// 3. Create records for ReportClientData table in xmlbook database
for(int i = 0; i < client_rows; i++){
db_statement.execute(createQueryClient());
ResultSet rs = db_statement.executeQuery
("select LAST_INSERT_ID()");
rs.last();
int last_insert_id = rs.getInt(1);
int client_ailment = (int) (Math.random() * 5);
for(int k = 0; k < client_ailment; k++){
try{
db_statement.execute(createQueryAilment(last_insert_id));
out.print("<br />Ailment added for record index: " +
last_insert_id + ". With " + (k+1) + " out of " +
client_ailment + " Records.");
ailment_rows++;
} catch (SQLException e){
out.print("<br />Ailment skipped for record index:" +
last_insert_id + ". Due to duplicate." );
}
}
if (client_rows -1 == i)
final_status = "There were " + client_rows +
" Client Data records added.";
}
}
catch (ClassNotFoundException e)
{ final_status = " Error creating database drive class!";
final_status += " <br />" + e.toString();
}
catch (SQLException e)
{ final_status = " Error processing the SQL!";
final_status += " <br />" + e.toString();
}
finally
{
/* We must close the database connection now */
try
{ if (db_connection != null)
{ db_connection.close(); }
}
catch (SQLException e)
{ final_status = "Error in closing connection.";
final_status += " <br />" + e.toString();
}
}
%>
<br /><br />
<font color="blue"><%=final_status%>
<br />And <%=ailment_rows%> Ailment Data records added.
</font>
<br /><br />
</body>
</html>
<%!
char letters[] = {'a', 'b', 'c', 'd', 'e', 'f',
'g', 'h', 'i', 'j', 'k', 'l', 'm', 'n', 'o',
'p', 'q', 'r', 's', 't', 'u', 'v', 'w', 'x', 'y', 'z'};
private String createString(int length){
String result = "";
for(int i = 0; i < length; i++){
int temp = (int) (Math.random() * 26);
//capitalize the first letter in this string
if (i == 0)
result += Character.toUpperCase(letters[temp]);
else
result += letters[temp];
}
return new String(result);
}
//1 means birthdate (1930 - 1996)
//2 means ailment 10 years (1980 - 1997)
private String createDate(int year_range){
String result = "19";
if (year_range == 1)
result += "" + (30 + (int) (Math.random() * 67));
else
result += "" + (80 + (int) (Math.random() * 18));
result += "-" + (1 + (int) (Math.random() * 12));
result += "-" + (1 + (int) (Math.random() * 28));
return new String(result);
}
private String createColor(){
String result = "";
int pick_a_color = (int) (Math.random() * 6);
switch (pick_a_color) {
case 0: result = "red";
break;
case 1: result = "yellow";
break;
case 2: result = "blue";
break;
case 3: result = "green";
break;
case 4: result = "purple";
break;
case 5: result = "orange";
break;
default: result = "black";
}
return result;
}
private String createQueryClient(){
String db_query = " insert into ReportClientData ";
db_query += "(RCD_lname,RCD_fname,RCD_clinic,RCD_dob,RCD_color)";
db_query += " values (";
db_query += "'" + createString( 7 ) + "' ,";
db_query += "'" + createString( 5 ) + "' ,";
db_query += "'" + (1 + (int) (Math.random() * 5)) + "' ,";
db_query += "'" + createDate(1) + "' ,";
db_query += "'" + createColor() + "')";
return db_query;
}
private String createQueryAilment(int clientID){
String db_query = " insert into ReportClientAilments ";
db_query += "(RCA_medicalailment,RCA_datefound,RCA_clientID)";
db_query += " values (";
db_query += "'" + createString( 1 ) + "' ,";
db_query += "'" + createDate(2) + "' ,";
db_query += "'" + clientID + "' )";
return db_query;
}
%>
Now that some data exists, let's start creating some reports. The first step in this process is creating a class that will create a JDOM representation out of a ResultSet.