JSP Data-Access Sample Using JDBC
The following example creates a query dynamically from search conditions the user enters through an HTML form (typed into a box, and entered with an Ask Oracle button). To perform the specified query, it uses JDBC code in a method called runQuery() that is defined in a JSP declaration. It also defines a method, formatResult(), within the JSP declaration to produce the output. The runQuery() method uses the scott schema with password tiger.
The HTML INPUT tag specifies that the string entered in the form be named cond. Therefore, cond is also the input parameter to the getParameter() method of the implicit request object for this HTTP request, and the input parameter to the runQuery() method (which puts the cond string into the WHERE clause of the query).
Code:
<%@ page language="java" import="java.sql.*" %>
<HTML>
<HEAD> <TITLE> The JDBCQuery JSP </TITLE> </HEAD>
<BODY BGCOLOR="white">
<% String searchCondition = request.getParameter("cond");
if (searchCondition != null) { %>
<H3> Search results for <I> <%= searchCondition %> </I> </H3>
<B> <%= runQuery(searchCondition) %> </B> <HR><BR>
<% } %>
<B>Enter a search condition:</B>
<FORM METHOD="get">
<INPUT TYPE="text" NAME="cond" SIZE=30>
<INPUT TYPE="submit" VALUE="Ask Oracle");
</FORM>
</BODY>
</HTML>
<%-- Declare and define the runQuery() method. --%>
<%! private String runQuery(String cond) throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rset = null;
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection("jdbc:oracle:oci:@",
"scott", "tiger");
stmt = conn.createStatement();
// dynamic query
rset = stmt.executeQuery ("SELECT ename, sal FROM scott.emp "+
(cond.equals("") ? "" : "WHERE " + cond ));
return (formatResult(rset));
} catch (SQLException e) {
return ("<P> SQL error: <PRE> " + e + " </PRE> </P>\n");
} finally {
if (rset!= null) rset.close();
if (stmt!= null) stmt.close();
if (conn!= null) conn.close();
}
}
private String formatResult(ResultSet rset) throws SQLException {
StringBuffer sb = new StringBuffer();
if (!rset.next())
sb.append("<P> No matching rows.<P>\n");
else { sb.append("<UL>");
do { sb.append("<LI>" + rset.getString(1) +
" earns $ " + rset.getInt(2) + ".</LI>\n");
} while (rset.next());
sb.append("</UL>");
}
return sb.toString();
}
%>
hope this helps.
Member
Posting Permissions
Reply With Quote
Bookmarks