routemsgbean.java

这是jsp通用模块导航的程序原代码,里面有新闻,bbs等jsp与javabean结合的程序.

package com.wxpn.tutorial.ec.bean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;


import com.wxpn.tutorial.db.ConnectionPool;
import com.wxpn.tutorial.db.DB;

/**
 * 描述: 描述信息管理类
 * 
 * @author 
 */

public class RouteMsgBean {

	

	public int delete(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "delete from TravRoute where routeid = '" + id + "'";
			// 执行sql语句：
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	

	public Collection getAll() {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		HashMap map = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from TravRoute";
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			RouteMsg routeMsg = new RouteMsg();
            Collection routeList = new ArrayList();
			if (rs.next()) {
				routeMsg.setRouteId(rs.getInt("ROUTEID"));
				routeMsg.setCompany(rs.getString("company"));	
				routeMsg.setContent(rs.getString("routecontent"));
				routeMsg.setCityName(rs.getString("provinceNAME"));
				routeMsg.setstarttime(rs.getDate("startdate"));
				routeMsg.setendtime(rs.getDate("enddate"));
				routeMsg.setPrice(rs.getDouble("PRICE"));
				routeMsg.setperiod(rs.getInt("period"));
				routeMsg.setCityId(rs.getInt("PROVINCEID"));
                
                routeList.add(routeMsg);
			}
			return routeList;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
	
		} catch (Exception e) {
			e.printStackTrace();
			return null;
	
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}

	}

	

	public int getCount(String clause) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		int num ='0';
		try {
			// 创建数据记录集对象：　
			stmt = conn.createStatement();
         
			// sql语句：
            String sql = "select count(routeid) countnum from TravRoute "+clause;
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			System.out.println(sql);
			if (rs.next()) {
				num=rs.getInt(1);
			}
            return num;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}
	
	public Collection getAll(int pagesize, int page) {
		return getAll(pagesize,page,"");
	}
	public Collection getAll(int pagesize, int page,String clause) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from TravRoute "+clause+" order by ROUTEID desc limit "
					+ (page - 1) * pagesize + "," + pagesize;

			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection routeList = new ArrayList();
			RouteMsg routeMsg = new RouteMsg();

			while (rs.next()) {
				routeMsg.setRouteId(rs.getInt("ROUTEID"));
				routeMsg.setCompany(rs.getString("company"));	
				routeMsg.setContent(rs.getString("routecontent"));
				routeMsg.setCityName(rs.getString("provinceNAME"));
				routeMsg.setstarttime(rs.getDate("startdate"));
				routeMsg.setendtime(rs.getDate("enddate"));
				routeMsg.setPrice(rs.getDouble("PRICE"));
				routeMsg.setperiod(rs.getInt("period"));
				routeMsg.setCityId(rs.getInt("PROVINCEID"));
                
                routeList.add(routeMsg);

			}
			return routeList;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}
	
	public Collection getAll(int id ) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from TravRoute where ROUTEID='"+id+"'";
            System.out.println(sql);
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection routeList = new ArrayList();
			RouteMsg routeMsg = new RouteMsg();
			while (rs.next()) {
				routeMsg.setRouteId(rs.getInt("ROUTEID"));
				routeMsg.setCompany(rs.getString("company"));	
				routeMsg.setContent(rs.getString("routecontent"));
				routeMsg.setCityName(rs.getString("provinceNAME"));
				routeMsg.setstarttime(rs.getDate("startdate"));
				routeMsg.setendtime(rs.getDate("enddate"));
				routeMsg.setPrice(rs.getDouble("PRICE"));
				routeMsg.setperiod(rs.getInt("period"));
				routeMsg.setCityId(rs.getInt("PROVINCEID"));
                
                routeList.add(routeMsg);

			}
			return routeList;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}
	public Collection getAll(String clause) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from TravRoute "+clause+" order by routeid desc  ";
           System.out.println(sql);
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection routeList = new ArrayList();
			RouteMsg routeMsg = new RouteMsg();
			while (rs.next()) {
				routeMsg.setRouteId(rs.getInt("ROUTEID"));
				routeMsg.setCompany(rs.getString("company"));	
				routeMsg.setContent(rs.getString("routecontent"));
				routeMsg.setCityName(rs.getString("provinceNAME"));
				routeMsg.setstarttime(rs.getDate("startdate"));
				routeMsg.setendtime(rs.getDate("enddate"));
				routeMsg.setPrice(rs.getDouble("PRICE"));
				routeMsg.setperiod(rs.getInt("period"));
				routeMsg.setCityId(rs.getInt("PROVINCEID"));
                
                routeList.add(routeMsg);

			}
			return routeList;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}
}