Accessing Databases in JSP
- You can perform Database access using the normal java database
objects or using the <sql:query> tag.
- We'll use the standard java objects and put the code into a
Java class
- Accessing a database in JSP is the same as accessing one in a
Java
form application.
- You must have a data source name setup (ms access)
- You must create a connection to the database
- Then the Statement and PreparedStatement objects can be
created
and executed
- Finally ResultSets can be processed
- Remember, the jsp code runs on the server, for ms access this
means the data
source name needs to be setup on the server and does not have to be set
on client machines.
- Some helpful tips.
- Use a java class object to represent the database
- Then add necessary methods to store, get, remove items from the
tables.
- Try to minimize the amount of actual java code in your .jsp
files
- Remember, you can pass the out object from the jsp
scriplet
to your javabean as a javax.servlet.jsp.JspWriter object.
Creating HTML Tables from ResultSet Objects
- A common operation of JSP web pages is to query a database and
show
the results. In order to have columns line up nicely (with
headings,
etc) it is advisable to format your output using HTML table tags.
- Review of table tags:
- <table border = n width = w>
starts a table, where n is the pixel width of the
border, w is the width of the table (percent of the
window
width = 80% or pixel count width = 400).
- </table> ends a table
- <tr align = a1 valign = a2>
starts
a row, where a1 is the horizontal alignment (left,
center,
right), and a2 is the vertical alignment (top, middle,
bottom).
e.g. <tr align = "center" valign="middle">
- </tr> ends a row
- <td> starts a data cell (may have width, align,
and
valign parameters as well)
- </td> ends a data cell
- <th> starts a heading cell (bolder than a data cell)
- </th> ends a heading cell
- You are already familar with the ResultSet object and its
methods
- next() - advance to next row, returns false when end of
table
is reached
- getInt("column name") or getInt(column number) - retrieves data
from
specified column as an int
- getString("column name") or getInt(column number) - retrieves
data
from specified column as a String
- getDouble("column name") or getInt(column number) - retrieves
data
from specified column as an double
- In addition, it is helpful to use a couple of methods form the ResultSetMetaData
object
- "meta data" is just data about data.
- the ResultSetMetaData object can be queried to find out how
many
columns are in the ResultSet, their names, and their datatypes
- to create a ResultSetMetaData object, call the ResultSet method
getMetaData()
, see below
- two useful ResultSetMetaData methods are:
- getColumnCount() - returns the number of columns as an int
- getColumnLabel(n) - returns the label of column
n
as a String, n is 1 relative.
- Finally, a result set can be displayed as a table by using a
for-loop
to generate column labels using the ResultSetMetaData and a while loop
to
display the actual result set contents.
- Note: no matter what the data type, you can use
rs.getString
to retieve the data in its default format.
- Example from the database java bean in Demo5:
Click here to download the demo project
public void
displayResultSetHtml(ResultSet
rs,javax.servlet.jsp.JspWriter out)
{
try{
ResultSetMetaData rsmd =
rs.getMetaData();
// get result set meta data
out.println("<table
cellspacing = 10>");
//
// add column headers
to
the table
//
out.println("<tr>");
for(int i = 1; i <=
rsmd.getColumnCount();
i++)
{
out.println("<th>"+rsmd.getColumnLabel(i)+"</th>");
}
out.println("</tr>");
//
// add row data to the
table
one row at a time
//
while(rs.next()) {
out.println("<tr>");
for(int i = 1; i
<=
rsmd.getColumnCount(); i++)
{
out.println("<td>"+rs.getString(i)+"</td>");
}
out.println("</tr>");
}
out.println("</table>");
}
catch(Exception e)
{
System.out.println("Error
processing
result set :
"+e.getMessage());
}
}
Creating shopping lists
- Another common operation of web pages in retailing is to display
a
list of products, each product has a button or link that allows the
customer
to add that item to the shopping cart.
- Programming this is just a variation of displaying a table, the
difference
here is that a link to a jsp page is added with the product information
incoded
into it.
- Steps
- Execute an SQL query to get the desired records.
- Create the table based on the ResultSet, one row per product
(note,
table rows can have multiple lines)
- Each row will have a link to a page that adds the item to
the
shopping cart. Encoded on that link is the information about the
product.
- the html for a line might look like the line below
<a href = 'orderItem.jsp?action=Add&upc=2202&name=bolts&quantity=1&price=1.23'> add to cart </a>
- the <a href = is the HTML tag
for
a clickable hyperlink
- orderItem.jsp is the url of the page to invoke if the user
clicks
on the link
- ? separates the url from the parameters
- & separates one parameter from the next
- name, price, upc, quantity are all parameter names
- 2202, bolts, 1, 1.23, are all parameter values
- note: you can have spaces in the parameters.
- add to cart is the text the customer sees and can click
on
(you could use an image instead)
- The code used to output this line dynamically looks like:
out.println("<td><a href =
'orderItem.jsp?action=Add&upc="+upc+"&name="+name+"&price="+price+"&quantity=1'
target='shoppingcart'> add to cart
</a></td>");
- The entire method to display the products table is shown below
OrderProducts.html
<html>
<head>
<title>Order
Products</title>
<meta
http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<h2>Search for
products</h2>
<form
name="orderproducts" method="get" action="orderProducts.jsp"
target="searchresults">
<table>
<tr><td>Enter all or part of a name
:</td><td><input type="text" name="productname"
size="25"></td></tr>
<tr><td>Enter maximum price :</td><td><input
type="text" name="maxprice" size="10"
value="1000"></td></tr>
<tr><td colspan ="2"><br><input type ="submit"
value="Search" name="action"><input type="reset"
value="Clear"></td></tr>
</table>
</form>
<p></p>
<iframe width="600"
height="300" name="searchresults"></iframe>
<iframe width="600"
height="200" name="shoppingcart"></iframe>
</body>
</html>
orderProducts.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="onlinecompany.*" %>
<%@page import="java.sql.*"
%>
<!DOCTYPE html>
<html>
<head>
<meta
http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Search
Results</title>
</head>
<body>
<%!
java.text.DecimalFormat currency = new
java.text.DecimalFormat("$#,##0.00");
%>
<%
session.setMaxInactiveInterval(1800); // 30
minutes
try{
String namefield =
request.getParameter("productname");
double maxprice =
Double.parseDouble(request.getParameter("maxprice"));
Database db = null;
synchronized(session)
{
db =
(Database)session.getAttribute("db");
if(db == null)
{
db =
new Database();
session.setAttribute("db",
db);
}
}
if(db == null ||
!db.connect())
{
out.println("Unable to
connect to database");
}
else // database ready to go
{
PreparedStatement prep = db.getConnection().prepareStatement("Select
UPC,ProductName, Price from Products where ProductName like ? and Price
<= ?");
prep.setString(1,"%"+namefield+"%");
prep.setDouble(2,maxprice);
ResultSet rs =
prep.executeQuery();
out.println("<table cellspacing=5>");
while(rs.next())
{
String upc
= rs.getString(1);
String
name = rs.getString(2);
double
price = rs.getDouble(3);
out.println("<tr>");
out.println("<td>"+upc+"</td>");
out.println("<td>"+name+"</td>");
out.println("<td>"+currency.format(price)+"</td>");
out.println("<td><a href =
'orderItem.jsp?action=Add&upc="+upc+"&name="+name+"&price="+price+"&quantity=1'
target='shoppingcart'> add to cart
</a></td>");
out.println("</tr>");
}
out.println("</table>");
prep.close();
}
db.closeConnection();
}
catch(Exception ex)
{
System.err.println(ex.toString());
}
%>
</body>
</html>