Javabean incorporating sql queries.
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>
<moderator added code tags>
Re: Javabean incorporating sql queries.
I usually just run my queries from the jsp page itself via the <sql:query> tags. The problem that i see is that you have a Vector returned from your bean but the way the data is stored in the Vector i don't see how you would be able to create a table in your jsp page. I would suggest using some of the tags that JSTL provides. Here's an example of what i normally do.
Code:
<%-- output db data --%>
<sql:query var = "dbdata">
SELECT * FROM Employees
WHERE empid = ?
<sql:param value = "${param.empid}"/>
</sql:query>
<table>
<tr>
<c:forEach items="${dbdata.columnNames}" var="colName">
<th>${fn:escapeXml(colName)}</th>
</c:forEach>
</tr>
<c:forEach items="${empList.rowsByIndex}" var="row">
<tr>
<c:forEach items="${row}" var="column">
<td>${fn:escapeXml(column)}</td>
</c:forEach>
</tr>
</c:forEach>
</table>
Re: Javabean incorporating sql queries.
I've sorted that problem, use the following jsp code to display your vector(from your java bean) as an html table.
However, i want to be able to return only certain records within a table based upon a search for a word within one of the table columns...I have created a jsp page with a form, the form takes an input box entry and then when the submit button is clicked the value in ther input box is passed to the jsp page below. This is my problem.
I need to somehow take this passed value, and use it in an sql query where the value is LIKE data held in a 'keywords' column.
Any way i can do this? Should this go in a new post? Or continue here? Its a very similar problem.
Code:
<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>
<jsp:useBean id="questionacoursework" class="webtech.questionacoursework" />
<html>
<body>
<%@page import="java.sql.*"%>
<%@page import="java.util.*"%>
<table>
<%
Vector outvec = new Vector();
Vector invec = new Vector();
outvec = questionacoursework.getResults();
invec = (Vector)outvec.elementAt(1);
for(int i=0; i<outvec.size(); i++) { %>
<tr>
<% for(int j=0; j<invec.size(); j++) {
if(i!=0 && j==3) { // When the current column is "thumb_url" column %>
<td align="center">
<a href="item.jsp?image_id=<%=((Vector)outvec.elementAt(i)).elementAt(0).toString()%>">
<img src="<%=((Vector)outvec.elementAt(i)).elementAt(j).toString() %>" border="0"/>
</a>
</td>
<% } //end if
else { %>
<td align="center"> <%-- current field of the current row --%>
<%= ((Vector)outvec.elementAt(i)).elementAt(j).toString() %>
</td>
<% }
} %>
</tr>
<% } %>
</table>
</body>
Re: Javabean incorporating sql queries.
Quote:
Posted by stu_fb
I need to somehow take this passed value, and use it in an sql query where the value is LIKE data held in a 'keywords' column.
Ive tried this before but i haven't been able to come up with a work around. The following works but only if the ? is by itself. Dosen't matter of you use sql pattern matching or sql reg expression.
Code:
<sql:query var = "data">
select * from directory where name like = ? + "%"
<sql:param value = "${param.name}"/>
</sql:query>
