|
|
Below i have my java code which is a java bean which is to be used to connect to an sql database, run a query, store the query results in a vector of vectors and then using my jsp page(code further down page) i wish to display the resulting table entries(the vector) within an html table.
Could anyone possibly take a look at the following 2 pieces of code, my JAVA and JSP and suggest what i need to do to achieve my objectives. Thanks in advance. Bert.
JAVA Bean code
Code:
package myjavadocs;
import java.sql.*;
import java.util.Vector;
public class sqlquery {
String result = "";
String query = "select * from pix;";
String url = "jdbc:mysql://myjavasql.";
String driver = "org.gjt.mm.mysql.Driver";
String uname = "";
String dpass = "";
//Step 1) Make a database connection
private Connection dbconn = null;
Vector mainvector = new Vector();
public Vector getResults(String query)
{
try
{
Class.forName(driver);
dbconn = DriverManager.getConnection(url, uname, dpass);
//Create an SQL statement
Statement statement = dbconn.createStatement();
if (statement.execute(query))
{
//Step 2) If we have a result lets loop through
ResultSet results = statement.getResultSet();
ResultSetMetaData metadata = results.getMetaData();
//Validate result. Note switch to while loop if we plan on multiple results from query
if(results != null)
{
//Use results setmetadata object to determine the columns
int li_columns = metadata.getColumnCount();
Vector tmp = new Vector();
tmp.addElement(metadata.getColumnLabel(1));
tmp.addElement(metadata.getColumnLabel(2));
tmp.addElement(metadata.getColumnLabel(3));
tmp.addElement(metadata.getColumnLabel(4));
tmp.addElement(metadata.getColumnLabel(5));
tmp.addElement(metadata.getColumnLabel(6));
mainvector.addElement(tmp);
//loop throught the columns and append data to our table
while(results.next())
{
tmp = new Vector();
tmp.addElement(results.getObject(1).toString());
tmp.addElement(results.getObject(2).toString());
tmp.addElement(results.getObject(3).toString());
tmp.addElement(results.getObject(4).toString());
tmp.addElement(results.getObject(5).toString());
tmp.addElement(results.getObject(6).toString());
mainvector.addElement(tmp);
}
}
}
}
catch (ClassNotFoundException e)
{ result = "<tr><td> Error creating database drive class!";
result += " <br/>" + e.toString() + "</td></tr>";
}
catch (SQLException e)
{ result = "<tr><td> Error processing the SQL!";
result += " <br/>" + e.toString() + "</td></tr>";
}
finally
{
try {
if (dbconn !=null)
{ dbconn.close();}
}
catch (SQLException e)
{ result = " <tr><td> Error in closing mainvector.";
result += " <br/>" + e.toString() + "</td></tr>";
}
}
return mainvector;
}
public Vector getResults() {
return mainvector;
}
}
JSP Code
Code:
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<jsp:useBean id="sqlquery" scope="page" class="myjavadocs.sqlquery" />
<html>
<body>
<table border = "2">
<%=sqlquery.getResults()%>
<table>
</body>
</html>
|
|
|
|
|
|
|
|
dnew