下面是访问数据库的JavaBean:DataOperation.java package com.winton.business;import java.util.*; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;import com.winton.object.ProductObject;/** * @author winton.huang * @version 1.0 */ public class DataOperation { /** * Get the maximum product ID of all products * @return the maximum product ID in the table products * @throws ClassNotFoundException * @throws SQLException */ public int getRecordCount() throws ClassNotFoundException, SQLException { Class.forName("org.gjt.mm.mysql.Driver"); Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root",""); Statement st = cnct.createStatement(); ResultSet rs = st.executeQuery("select count(*) from Product"); rs.next(); int RowCount = rs.getInt(1); rs.close(); st.close(); cnct.close(); return RowCount; } /** * * Get the minimal product ID of all products * @return the minimal product ID in the table products * @throws ClassNotFoundException * @throws SQLException */ public int GetMinProductID() throws ClassNotFoundException, SQLException { Class.forName("org.gjt.mm.mysql.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root",""); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery("SELECT MIN(ID) FROM Product"); rs.first(); int minID = rs.getInt(1); rs.close(); st.close(); conn.close(); return minID; }
/** * Get Product List by page, from table Products * @param recordsPerPage indicate how many records can be fetched in one page * @param beginRecord the number indicates the position of the begin record * @param orderBy fix the field by which the returned result is sorted * @return A ArrayList of products * @throws ClassNotFoundException * @throws SQLException */
public ArrayList GetProductsByPage( int recordsPerPage, int beginRecord, String orderBy ) throws ClassNotFoundException, SQLException { Class.forName("org.gjt.mm.mysql.Driver"); Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root",""); Statement st = cnct.createStatement(); ResultSet rs; rs = st.executeQuery("SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage ); if( orderBy.equalsIgnoreCase( "Name" ) ) { rs = st.executeQuery("(SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage + ") ORDER BY Name" ); } else if( orderBy.equalsIgnoreCase( "RetailPrice")) { rs = st.executeQuery("(SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage + ") ORDER BY RetailPrice" ); } else if( orderBy.equalsIgnoreCase( "WholeSalePrice")) { rs = st.executeQuery("(SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage + ") ORDER BY WholeSalePrice" ); }
ArrayList al = new ArrayList(); ProductObject po = null;
int id; String name; double retailPrice; double WholeSalePrice; String description; int quantity; double discount;
int i = 0; while( rs.next()) { id = rs.getInt("ID"); name = rs.getString("Name"); retailPrice = rs.getDouble("RetailPrice"); WholeSalePrice = rs.getDouble("WholeSalePrice"); description = rs.getString("Description"); quantity = rs.getInt("Quantity");
po = new ProductObject(id, name, retailPrice, WholeSalePrice, description, quantity ); al.add( po ); }
return al; }
/** * Add a product to table Products * @param aProduct the product to be added * @throws ClassNotFoundException * @throws SQLException */
public void AddProduct(ProductObject po) throws ClassNotFoundException, SQLException {
Class.forName("org.gjt.mm.mysql.Driver"); Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root",""); Statement st = cnct.createStatement(); String productName = po.getName(); double retailPrice = po.getRetailPrice(); double WholeSalePrice =po.getWholeSalePrice(); String description = po.getDescription(); int quantity = po.getQuantity(); String sqlInsert = "INSERT INTO Product ( Name, RetailPrice, WholeSalePrice, Description, Quantity ) VALUES " + "(" + "'" + productName + "'" + "," + retailPrice + "," + WholeSalePrice + "," + "'" + description + "'" + "," + quantity + ")" ; st.execute(sqlInsert); st.close(); cnct.close(); } /** * Get the product information according to the Product ID * @param productID determin which product to get * @return a Product object details the product information * @throws ClassNotFoundException * @throws SQLException */
public ProductObject GetProductByID( int iID) throws ClassNotFoundException, SQLException { Class.forName("org.gjt.mm.mysql.Driver"); Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root",""); Statement st = cnct.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM PRODUCT WHERE ID = " + iID); rs.first(); int id = rs.getInt("ID"); String name = rs.getString("Name"); double retailPrice = rs.getDouble("RetailPrice"); double WholeSalePrice = rs.getDouble("WholesalePrice"); String description = rs.getString("Description"); int quantity = rs.getInt("Quantity"); rs.close(); st.close(); cnct.close(); ProductObject po = new ProductObject(id, name, retailPrice, WholeSalePrice, description, quantity); return po; }
/** * Delete a product according to the Product ID * @param productID the ID of the product to be deleted * @throws ClassNotFoundException * @throws SQLException */ public void DeleteById(int productID) throws ClassNotFoundException, SQLException { Class.forName("org.gjt.mm.mysql.Driver"); Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root",""); Statement st = cnct.createStatement(); String sSql = "delete from Product WHERE ID=" + productID; st.execute(sSql); st.close(); cnct.close(); } }
1.写一个类, 里面封装一个方法用来返回一个RESULTSET ,
2. 在JSP 页面里调用这个类,得到结果集,然后循环输出记录!3.我时候需要 jsp/applet/servlet/ejb/的知识?如果可以多做一下也是可以的.
4.如果用EJB 可以试一下 servlet把+ SESSION + DAO ,返回给客户端结果,然后显示出来.
5.解决一个问题的方法有很多,但有时杀鸡不用牛力的 ^_^
努力学习,终有好宝
JSP/JavaBean/Mysql贴点示例代码:
这是Jsp页面:<%@ page contentType="text/html;charset=gb2312" %>
<%@ page language="java" errorPage="error.jsp" %>
<%@ page import="com.winton.business.DataOperation" %>
<%@ page import="com.winton.object.ProductObject" %>
<%@ page import="java.util.*" %><html>
<head><title>Administrator's Page</title></head>
<body bgcolor="#f3fff4">
<%
String orderBy = request.getParameter("OrderBy");
if(orderBy == null ){
orderBy = "ID";
}
String strID = request.getParameter("MinID");
int minID = Integer.parseInt( strID );
DataOperation dataeditobject = new DataOperation();
int minIdOfPage = 0;
int RowCount = dataeditobject.getRecordCount();
int Pages = RowCount/20;
if ((RowCount%20) > 0) Pages++;
%>
<h2 align="center" color="BLUE">Products Detail</h2>
<table width="98%" align="center" border="1" cellspacing="0" cellpadding="0" bordercolor="#DCDCED">
<tr bgcolor="#4BDADA">
<%
String sOrder = request.getParameter("Order");
if (sOrder == null) {
sOrder = "ASC";
}
else if( sOrder.equalsIgnoreCase( "ASC") ){
sOrder = "DESC";
}
else{
sOrder = "ASC";
}
%>
<th> <a href="Admin.jsp?MinID=<%= minID %>&OrderBy=ID&Order=<%= sOrder %>" > ID </a> </th>
<th> <a href="Admin.jsp?MinID=<%= minID %>&OrderBy=Name&Order=<%= sOrder %>" > Name </a></th>
<th> <a href="Admin.jsp?MinID=<%= minID %>&OrderBy=RetailPrice&Order=<%= sOrder %> " > RetailPrice </a> </th>
<th color="white"> WholeSalePrice </a></th>
<th color="white"> Description </th>
<th color="white"> Quantity </th>
<th> </th>
<th> </th>
</tr>
<%
try {
ArrayList al;
al = dataeditobject.GetProductsByPage( 20, minID, "ID" );
if( sOrder.equalsIgnoreCase("DESC") ){
Collections.reverse( al );
}
ProductObject po = null;
String color1="#99ccff";
String color2="#66cc22";
String color ="";
for( int i = 0; i < al.size(); i++ ){
po = (ProductObject)al.get(i);
if(i%2==0) color=color1; else color=color2;
%>
<tr bgcolor=<%=color%>>
<td> <%= po.getId()%> </td>
<td> <%= po.getName()%></td>
<td> <%= po.getRetailPrice() %></td>
<td> <%= po.getWholeSalePrice() %> </td>
<td> <%= po.getDescription() %> </td>
<td> <%= po.getQuantity() %> </td>
<td> <a href="Edit.jsp?ID=<%= po.getId() %>&MinID=<%= minID %>&OrderBy=<%=orderBy%>" > Edit</a> </td>
<td> <a href="Delete.jsp?ID=<%= po.getId() %>&MinID=<%=minID %>&OrderBy=<%=orderBy%>" > Delete</a> </td>
<td><input type="checkbox" name="Id" value="<%=po.getId()%>"></td>
</tr>
<%
}
}
catch( Exception e )
{
out.println( e.getMessage() );
}
%>
</table> <table bgcolor="#eeeecc" width="98%" align="center" border="1" cellspacing="0" cellpadding="0" bordercolor="#DCDCED">
<form action="order.jsp" method="get">
<td align="LEFT">Order by
<select name="key">
<option value="0">Id</option>
<option value="1">Name</option>
<option value="2">RetailPrice</option>
</select>
<input type="submit" name="submit" value="Execute">
</td>
</form>
<td><a href="ProductAdd.jsp?MinID=<%= minID %>&OrderBy=<%=orderBy%>" > Add</a></td>
<td>
<a href="Admin.jsp?MinID=<%=minID - 20 %>&OrderBy=<%=orderBy%>" >Previous</a>
</td>
<td>
<a href="Admin.jsp?MinID=<%=minID + 20%>&OrderBy=<%=orderBy%>" > Next</a>
</td>
<td>
<input type="button" OnClick="FirstPage()" value="First"/>
<input type="button" OnClick="previousPage(1)" value="Privious"/>
<input type="button" OnClick="nextPage(1)" value="Next"/>
<input type="button" OnClick="LastPage()" value="Last"/>
</td>
<td>Display Nums:<input type="text" name="Nums" size="3" >
<input type="submit" value="Save"></td>
<td Align="Right" valign=middle>Page:1/<%=Pages%> Total:<%=RowCount%> </td>
</table>
</form>
</body>
</html>
package com.winton.business;import java.util.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;import com.winton.object.ProductObject;/**
* @author winton.huang
* @version 1.0
*/
public class DataOperation {
/**
* Get the maximum product ID of all products
* @return the maximum product ID in the table products
* @throws ClassNotFoundException
* @throws SQLException
*/
public int getRecordCount() throws ClassNotFoundException, SQLException
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root","");
Statement st = cnct.createStatement();
ResultSet rs = st.executeQuery("select count(*) from Product");
rs.next();
int RowCount = rs.getInt(1);
rs.close();
st.close();
cnct.close();
return RowCount;
}
/**
*
* Get the minimal product ID of all products
* @return the minimal product ID in the table products
* @throws ClassNotFoundException
* @throws SQLException
*/
public int GetMinProductID() throws ClassNotFoundException, SQLException
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root","");
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("SELECT MIN(ID) FROM Product");
rs.first();
int minID = rs.getInt(1);
rs.close();
st.close();
conn.close();
return minID;
}
/**
* Get Product List by page, from table Products
* @param recordsPerPage indicate how many records can be fetched in one page
* @param beginRecord the number indicates the position of the begin record
* @param orderBy fix the field by which the returned result is sorted
* @return A ArrayList of products
* @throws ClassNotFoundException
* @throws SQLException
*/
public ArrayList GetProductsByPage( int recordsPerPage, int beginRecord, String orderBy ) throws ClassNotFoundException, SQLException
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root","");
Statement st = cnct.createStatement();
ResultSet rs;
rs = st.executeQuery("SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage );
if( orderBy.equalsIgnoreCase( "Name" ) )
{
rs = st.executeQuery("(SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage + ") ORDER BY Name" );
}
else if( orderBy.equalsIgnoreCase( "RetailPrice"))
{
rs = st.executeQuery("(SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage + ") ORDER BY RetailPrice" );
}
else if( orderBy.equalsIgnoreCase( "WholeSalePrice"))
{
rs = st.executeQuery("(SELECT * FROM Product ORDER BY ID LIMIT " + beginRecord + ", " + recordsPerPage + ") ORDER BY WholeSalePrice" );
}
ArrayList al = new ArrayList();
ProductObject po = null;
int id;
String name;
double retailPrice;
double WholeSalePrice;
String description;
int quantity;
double discount;
int i = 0;
while( rs.next())
{
id = rs.getInt("ID");
name = rs.getString("Name");
retailPrice = rs.getDouble("RetailPrice");
WholeSalePrice = rs.getDouble("WholeSalePrice");
description = rs.getString("Description");
quantity = rs.getInt("Quantity");
po = new ProductObject(id, name, retailPrice, WholeSalePrice, description, quantity );
al.add( po );
}
return al;
}
/**
* Add a product to table Products
* @param aProduct the product to be added
* @throws ClassNotFoundException
* @throws SQLException
*/
public void AddProduct(ProductObject po) throws ClassNotFoundException, SQLException
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root","");
Statement st = cnct.createStatement();
String productName = po.getName();
double retailPrice = po.getRetailPrice();
double WholeSalePrice =po.getWholeSalePrice();
String description = po.getDescription();
int quantity = po.getQuantity();
String sqlInsert = "INSERT INTO Product ( Name, RetailPrice, WholeSalePrice, Description, Quantity ) VALUES " +
"(" + "'" + productName + "'" + "," +
retailPrice + "," + WholeSalePrice + "," +
"'" + description + "'" + "," +
quantity + ")"
;
st.execute(sqlInsert);
st.close();
cnct.close();
}
/**
* Get the product information according to the Product ID
* @param productID determin which product to get
* @return a Product object details the product information
* @throws ClassNotFoundException
* @throws SQLException
*/
public ProductObject GetProductByID( int iID) throws ClassNotFoundException, SQLException
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root","");
Statement st = cnct.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM PRODUCT WHERE ID = " + iID);
rs.first();
int id = rs.getInt("ID");
String name = rs.getString("Name");
double retailPrice = rs.getDouble("RetailPrice");
double WholeSalePrice = rs.getDouble("WholesalePrice");
String description = rs.getString("Description");
int quantity = rs.getInt("Quantity");
rs.close();
st.close();
cnct.close();
ProductObject po = new ProductObject(id, name, retailPrice, WholeSalePrice, description, quantity);
return po;
}
/**
* @param aProduct the Product object which is being updated
* @throws ClassNotFoundException
* @throws SQLException
*/
public void UnpdateProduct( ProductObject po) throws ClassNotFoundException, SQLException
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root","");
Statement st = cnct.createStatement();
String sql = "UPDATE PRODUCT SET Name=" + "'" + po.getName() +"', " +
"RetailPrice=" + po.getRetailPrice() + ", " +
"WholeSalePrice=" + po.getWholeSalePrice() + ", " +
"Description=" + "'" + po.getDescription() +"', " +
"Quantity=" + po.getQuantity() +" " +
"WHERE Id=" + po.getId();
System.out.println( sql );
st.executeUpdate("UPDATE PRODUCT SET Name=" + "'" + po.getName() +"', " +
"RetailPrice=" + po.getRetailPrice() + ", " +
"WholeSalePrice=" + po.getWholeSalePrice() + ", " +
"Description=" + "'" + po.getDescription() +"', " +
"Quantity=" + po.getQuantity() + " " +
"WHERE Id=" + po.getId()
);
st.close();
cnct.close(); }
/**
* Delete a product according to the Product ID
* @param productID the ID of the product to be deleted
* @throws ClassNotFoundException
* @throws SQLException
*/
public void DeleteById(int productID) throws ClassNotFoundException, SQLException
{
Class.forName("org.gjt.mm.mysql.Driver");
Connection cnct = DriverManager.getConnection("jdbc:mysql://localhost:3306/Traning","root","");
Statement st = cnct.createStatement();
String sSql = "delete from Product WHERE ID=" + productID;
st.execute(sSql);
st.close();
cnct.close();
}
}