JSP:航班信息查询模块加强版（附工…
采用三层架构设计的系统平台，在数据库层采用了oracle9i技术和jsp技术搭建。
系统功能包含分页浏览以及部分异常处理功能。
在数据查询方面实现了通过sql语句拼接来实现信息筛选而非使用setObject()方法。
系统实现了数据库访问基于数据池机制的管理方式。
项目结构图：
业务逻辑层面的实体类通过Data Access Object：数据访问对象实现其功能。
代码：
实体类：AirInfo.java
public class AirInfo {
private int aid;
private String takeOff;
private String destination;
public int getAid() {
return aid;
}
public void setAid(int aid) {
this.aid = aid;
}
public String getTakeOff() {
return takeOff;
}
public void setTakeOff(String takeOff) {
this.takeOff = takeOff;
}
public String getDestination() {
return destination;
}
public void setDestination(String destination) {
this.destination = destination;
}
}
数据访问类：
package dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class BaseDao {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
public Connection getConnection(){
try {
Context ctx = new InitialContext();
DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/scott");
conn = ds.getConnection();
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public void shutdownAll(Connection connection, PreparedStatement stmt, ResultSet resultSet) {
try {
if(null!=rs)
rs.close();
if(null!=pstmt)
pstmt.close();
if(null!=conn)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public ResultSet executeQuerySQL(String sql,String [] params){
getConnection();
try {
pstmt = conn.prepareStatement(sql);
if(null!=params){
for(int i=0;i<params.length;i++){
pstmt.setString(i+1, params[i]);
}
}
rs = pstmt.executeQuery();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return rs;
}
public int executeUpdateSQL(String sql,String[] params){
getConnection();
int result = 0;
try {
pstmt = conn.prepareStatement(sql);
if(null!=params){
for(int i=0;i<params.length;i++){
pstmt.setString(i+1, params[i]);
}
}
result = pstmt.executeUpdate();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeAll(conn,pstmt,rs);
}
return result;
}
}
=============================================================================
实现类：
public class AirDaoOracleImpl extends BaseDao implements AirDao {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
public List
lst
String sql = "SELECT * FROM(SELECT ROWNUM as r,t.*FROM(SELECT *FROM AIRINFOWHERE 1=1)");
if(null!=dest&&""!=dest){
sql += " AND destination='"+dest+"'";
}
if(null!=departure&&""!=departure){
sql += " AND takeoff=TO_DATE('"+departure+"','yyyy-mm-dd')";
}
执行后的变量赋值为（select语句后）的结果集表名为t，并对该结果集按照aid排序后连接到主表）。然后根据ROWNUM小于等于 pageSize乘以 pageIndex 的条件进行过滤，并在子查询中使用 where 子句将r 大于 pageSize乘以 pageIndex 减一的条件连接起来。
rs = executeQuerySQL(sql, null);
try {
while(rs.next()){
AirInfo ai = new AirInfo();
ai.setAid(rs.getInt("aid"));
ai.setTakeOff(rs.getString("takeoff").substring(0,11));
ai.setDestination(rs.getString("destination"));
list.add(ai);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeAll(conn, pstmt, rs);
}
return list;
}
public int getAirCount(String dest,String departure) {
int count = 0;
String sql = "SELECT COUNT(*) FROM AIRINFO WHERE 1=1";
if(null!=dest&&""!=dest){
sql += " AND destination='"+dest+"'";
}
if(null!=departure&&""!=departure){
sql += " AND takeoff=TO_DATE('"+departure+"','yyyy-mm-dd')";
}
rs = executeQuerySQL(sql, null);
try {
if(rs.next())
count = rs.getInt(1);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
closeAll(conn, pstmt, rs);
}
return count;
}
}
=========================================================================
业务层
public class AirBiz {
public List
AirDao aDao = new AirDaoOracleImpl();
List
return list;
}
public int getCount(String dest,String departure){
AirDao aDao = new AirDaoOracleImpl();
int count = aDao.getAirCount(dest, departure);
return count;
}
}
分页控制：
public class PageControl {
public int getTotalPage(int count,int pageSize){
int total = (count%pageSize==0)?(count/pageSize):(count/pageSize+1);
return total;
}
}
==============================================================================
首页：
<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
========================================================================================
信息显示页（注意"首页，上一页，下一页，末页的实现"）：
<%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<%@ page import="biz.*"%>
<%@ page import="entity.*"%>
<%
request.setCharacterEncoding("gbk");
String dest = request.getParameter("destination");
String departure = request.getParameter("takeoff");
String currentPage = request.getParameter("pageIndex");
if(null==currentPage){
currentPage = "1";
}
int pageIndex = Integer.parseInt(currentPage);
AirBiz ab = new AirBiz();
int count = ab.getCount(dest,departure);
int pageSize = 3;
PageControl pc = new PageControl();
int totalPage = pc.getTotalPage(count,pageSize);
if(pageIndex<1){
pageIndex = 1;
}
if(pageIndex>totalPage){
pageIndex = totalPage;
}
List
%>
| 航班号 | 起飞时间 | 目的地 |
| <%=ai.getAid() %> | <%=ai.getTakeOff() %> | <%=ai.getDestination() %> |
总共<%=totalPage %>页
工程项目
数据库表文件
PS:[new浪]就不敢增加个贴代码模块。。要是现在在[sin standards]开通博客的话,估计早就换了位置。