All.
my jdbc code properly retrieves data from mysql. However, I am getting rows of null values.
My jsp page(pollQueue.jsp) connects to this pollQueue(String type) method. Any ideas why I get the results, then followed by rows of null values? I even added a function to remove the nulls, but now I get extra rows.
public Hashtable<String, String> pollQueue(String type)
{
//System.out.println("Styarting methoid");
// Use this method to return a Hashtable filled with
// one or more records. You can specified a 'filter'
// for finding records and 'orderBy' will determine the sort order.
Hashtable<String, String> retVal = new Hashtable<String, String>();
retVal.put("ROWCOUNT", "0");
retVal.put("COLUMNCOUNT", "0");
retVal.put("STATUS", "EMPTY");
// Query by primary key.
boolean rc = dbWorker.open();
if(rc == false)
{
isDBError = true;
dbErrorMsg = dbWorker.errorMsg;
return retVal;
}
try // Remember to put all DB code inside a try block!
{
// 1) Get a connection.
Connection conn = dbWorker.getConnection();
//String sql = "select * from job where year = ? or ? and month = ? or ? and dayofweek= ? or ?";
String sql = "select * from queue Where priority = " + "'" + type + "'";
// 2) Create a statement.
Statement st = conn.createStatement();
st.executeQuery(sql);
//PreparedStatement st = conn.prepareStatement(sql);
// 3) Build your SQL.
//System.out.println("DEBUG SQL =" + sql);
// 4) Get a result set.
//ResultSet rs = st.executeQuery(sql);
ResultSet rs = st.executeQuery(sql);
int rowCount = 0;
// 5) Move to first record (and then next) record.
while(rs.next())
{
// 6) Retrieve fields into your properties using JDBC get??? methods.
String thisEntryDate = rs.getString("entrydate");
//String thisLogger = rs.getString("logger");
String thisPriority = rs.getString("priority");
String thisMessage = rs.getString("message");
// 7) Ensure non-null String fields with dbWorker.safeStr()
thisEntryDate = dbWorker.safeStr(thisEntryDate);
//thisLogger = dbWorker.safeStr(thisLogger);
thisPriority = dbWorker.safeStr(thisPriority);
thisMessage= dbWorker.safeStr(thisMessage);
if(thisEntryDate == null){
thisEntryDate = "";
}
/*if(thisLogger == null){
thisLogger = "";
}*/
if(thisPriority == null){
thisPriority = "";
}
if(thisMessage == null){
thisMessage = "";
}
// 8) Load this row into our Hashtable.
// The convention here is to pack each row's column name with an ID
// indicating the row. "ProjectId" + "0" = "ProjectId0" ===mapped to===> Value
// Put this record into Hashtable.
retVal.put("entrydate" + rowCount, "" + thisEntryDate);
//retVal.put("logger" + rowCount, "" + thisLogger);
retVal.put("priority" + rowCount, "" + thisPriority);
retVal.put("message" + rowCount, "" + thisMessage);
//System.out.println(thisEntryDate + thisPriority + thisMessage);
rowCount++;
}
// 9) Close all DB objects.
rs.close();
st.close();
dbWorker.close(); // Connection too.
// Write info to our hashtable--Since this container hold virtually anything,
// you can put in whatever 'metadata' you want about your result set.
retVal.put("ROWCOUNT", "" + rowCount);
retVal.put("COLUMNCOUNT", "23");
retVal.put("STATUS", "OK");
}
catch(SQLException e)
{
System.out.println("Error in pollQueue (" + e.getMessage() + ")");
retVal.put("STATUS", "ERROR");
}
return retVal;
}
Here is the jsp page:
<%
String type = request.getParameter("type");
Hashtable<String, String> queue = controller.pollQueue(type);
if(queue != null){
out.println("Records: " + queue.get("ROWCOUNT") + "<br>");
for(int i = 0;i<queue.size();i++){
String date = queue.get("entrydate" + i);
String priority = queue.get("priority" + i);
String message = queue.get("message" + i);
if(date == null){
date = "";
}
if(priority == null){
priority = "";
}
if(message == null){
message = "";
}
//String date = queue.get("entrydate" + i);
out.println(date + " | " /*+ queue.get("logger" + i) + " | " */+ priority + " | " + message + "<BR>");
}
}else{
out.println("No results for selected logging category.");
}
%>