jdbc-0.proxool.alias=proxool
jdbc-0.proxool.driver-class=com.microsoft.jdbc.sqlserver.SQLServerDriver
jdbc-0.proxool.driver-url=jdbc:microsoft:sqlserver://192.168.1.55:1433;DatabaseName=Hotel
jdbc-0.user=sa
jdbc-0.password=sa

jdbc-0.proxool.maximum-connection-count=15
jdbc-0.proxool.prototype-count=4

jdbc-0.proxool.house-keeping-test-sql=select CURRENT_DATE
jdbc-0.proxool.verbose=true
jdbc-0.proxool.statistics=15s,1m,1d
jdbc-0.proxool.statistics-log-level=DEBUG

web.xml:

? <servlet>
??? <servlet-name>ServletConfigurator</servlet-name>
??? <servlet-class>org.logicalcobwebs.proxool.configuration.ServletConfigurator</servlet-class>
??? <init-param>
????? <param-name>propertyFile</param-name>
????? <param-value>WEB-INF/classes/proxool.properties</param-value>
??? </init-param>
??? <load-on-startup>1</load-on-startup>?
? </servlet>

DAOException.java:

package com.travel.tools.database;

import java.sql.SQLException;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
?* @version
?* @author sichongjie
?*/
public class DAOException extends RuntimeException {

??? protected static final Log log = LogFactory.getLog(DAOException.class);

??? public static final int NOT_FOUND = 9999;

??? /**
???? */
??? private int errorCode = 0;

??? /**
???? * @param message
???? */
??? public DAOException(String message) {
??????? super(message);
??????? log.error("The exception occured. " + getMessage());
???????
??? }

??? /**
???? * @param ex
???? *??????????? SQLException
???? */
??? public DAOException(SQLException ex) {
??????? super(ex.getMessage());
??????? setErrorCode(ex.getErrorCode());
??????? log.error("The exception occured. " + getMessage());
??? }

??? /**
???? * @param message
???? * @param errorCode
???? */
??? public DAOException(String message, int errorCode) {
??????? super(message);
??????? setErrorCode(errorCode);
??????? log.error("The exception occured. " + getMessage());
??? }

??? /**
???? * @return int
???? */
??? public int getErrorCode() {
??????? return errorCode;
??? }

??? /**
???? * @param errorCode
???? */
??? public void setErrorCode(int errorCode) {
??????? this.errorCode = errorCode;
??? }

}

Database.java:

package com.travel.tools.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
//import javax.servlet.http.HttpSession;
//import javax.servlet.http.HttpServletRequest;
//import javax.servlet.http.HttpServletResponse;
//import javax.naming.Context;
//import javax.naming.InitialContext;
//import javax.naming.NamingException;
////
//import javax.sql.DataSource;

import java.sql.*;

//import org.apache.commons.dbcp.BasicDataSource;

/**
?* 數據庫操作類
?*
?* @version 1.0
?* @author Yangjian
?*/

public class Database {

??? private static final Log logger = LogFactory.getLog(Database.class);

??? /**
???? * DB conenction
???? */
??? private Connection connection = null;

??? int i = 0;

??? /**
???? * DB statement
???? */
??? private Statement statement = null;

??? /**
???? * ResultSet prepared
???? */
??? private PreparedStatement prepared = null;

??? /**
???? * DB ResultSet
???? */
??? private ResultSet resultset = null;

??? /**
???? * BD 初始化
???? *
???? * @return Connection
???? * @exception DAOException
???? *??????????????? 數據庫連接發生錯誤時拋出
???? */
??? public Connection initialize() throws DAOException {
??????? terminate();
??????? try {
//?????????? String dsName = "java:comp/env/jdbc/ctcvJNDI";
//?????????? String dsName = "java:comp/env/jdbc/oracle";

//?????????? Context ctx = new InitialContext();//
//?????????? DataSource ds = (DataSource) ctx.lookup("MyDataSource");
//?????????? connection = ds.getConnection();
??????????? connection = DriverManager.getConnection("proxool.proxool");

??????? } catch (SQLException ex) {
??????????? logger
??????????????????? .error("Failed to database connection. ex:"
??????????????????????????? + ex.getMessage());
??????????? connection = null;
??????????? throw new DAOException(ex);
//??????? } catch (NamingException ex) {
//??????????? logger.error("Failed to get datasource. ex:" + ex.getMessage());
//??????????? connection = null;
//??????????? throw new DAOException(ex.getMessage());
//??????? } catch (DAOException ex) {
//??????????? logger.error("Failed to get datasource. ex:" + ex.getMessage());
//??????????? connection = null;
//??????????? throw ex;
????????? }
???????
??????? return connection;
??? }

??? /**
???? * @return Statement
???? * @exception DAOException
???? *??????????????? 數據庫操作發生錯誤時拋出
???? */
??? private Statement open() throws DAOException {
??????? close();
??????? try {
??????????? statement = connection
??????????????????? .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
??????????????????????????? ResultSet.CONCUR_UPDATABLE);
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to call createStatement(). ex:"
??????????????????? + ex.getMessage());
??????????? statement = null;
??????????? throw new DAOException(ex);
??????? }
??????? return statement;
??? }

??? /**
???? * @return PreparedStatement
???? * @exception DAOException
???? *??????????????? 數據庫操作發生錯誤時拋出
???? */
??? private PreparedStatement open(String sql) throws DAOException {
??????? close();
??????? try {
??????????? //by sichongjie on 2004-7-7;because resulst set type is
??????????? // TYPE_SCROLL_INSENSITIVE
??????????? prepared = connection.prepareStatement(sql,
??????????????????? java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
??????????????????? java.sql.ResultSet.CONCUR_READ_ONLY);
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to call prepareStatement(). ex:"
??????????????????? + ex.getMessage());
??????????? prepared = null;
??????????? throw new DAOException(ex);
??????? }
??????? return prepared;
??? }

??? /**
???? * @param index
???? *??????????? 參數index
???? * @param param
???? *??????????? 匹配參數值(char)
???? * @exception DAOException
???? *??????????????? 數據庫操作發生錯誤時拋出
???? */
??? private void setParameter(int index, char param) throws DAOException {

??????? setParameter(index, new Character(param));
??? }

??? /**
???? * @param index
???? *??????????? 參數index
???? * @param param
???? *??????????? 匹配參數值(int)
???? * @exception DAOException
???? *??????????????? 數據庫操作發生錯誤時拋出
???? */
??? private void setParameter(int index, int param) throws DAOException {

??????? setParameter(index, new Integer(param));
??? }

??? /**
???? * @param index
???? *??????????? 參數index
???? * @param param
???? *??????????? 匹配參數值（double）
???? * @exception DAOException
???? *??????????????? 數據庫操作發生錯誤時拋出
???? */
??? private void setParameter(int index, double param) throws DAOException {

??????? setParameter(index, new Double(param));
??? }

??? /**
???? * @param index
???? *??????????? 參數index
???? * @param param
???? *??????????? 匹配參數(object)
???? * @exception DAOException
???? *??????????????? 數據庫操作發生錯誤時拋出
???? */
??? private void setParameter(int index, Object param) throws DAOException {
??????? try {
??????????? if (param instanceof String) {

??????????????? prepared.setString(index, (String) param);

??????????? }

??????????? if (param instanceof Character)
??????????????? prepared.setString(index, ((Character) param).toString());
??????????? if (param instanceof Integer)
??????????????? prepared.setInt(index, ((Integer) param).intValue());
??????????? if (param instanceof Double)
??????????????? prepared.setDouble(index, ((Double) param).doubleValue());
??????????? if (param instanceof Date)
??????????????? prepared.setDate(index, (Date) param);
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to set parameter to prepareStatement. ex:"
??????????????????? + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??? }

??? /**
???? * 執行數據庫更新操作
???? *
???? * @param sql
???? *??????????? SQL語句(update)
???? * @param param
???? *??????????? 匹配參數列表
???? * @exception DAOException
???? *??????????????? 數據庫操作錯誤是拋出
???? */
??? public void update(String sql, ArrayList param) throws DAOException {
??????? logger.info("Exceute update(). sql:" + sql + " param:"
??????????????? + param.toString());
??????? int count = 0;

??????? open(sql);

??????? for (int i = 0; i < param.size(); i++) {

??????????? setParameter(i + 1, param.get(i));
??????????? //System.out.println("this is updatesql"+sql+":"+param.get(i));
??????? }
??????? count = executeUpdate();
??????? //System.out.println("this is count"+count);
??????? if (count <= 0) {
??????????? throw new DAOException("update fail!1", DAOException.NOT_FOUND);
??????? }
??? }

??? /**
???? * 執行數據庫插入操作
???? *
???? * @param sql
???? *??????????? SQL語句(insert)
???? * @param param
???? *??????????? 匹配參數列表
???? * @exception DAOException
???? *??????????????? 數據庫操作錯誤是拋出
???? */
??? public void insert(String sql, ArrayList param) throws DAOException {
??????? logger.info("Exceute insert(). sql:" + sql + " param:"
??????????????? + param.toString());

??????? update(sql, param);
??? }

??? /**
???? * 執行數據庫刪除操作
???? *
???? * @param sql
???? *??????????? SQL語句(delete)
???? * @param param
???? *??????????? 匹配參數列表
???? * @exception DAOException
???? *??????????????? 數據庫操作錯誤是拋出
???? */
??? public void delete(String sql, ArrayList param) throws DAOException {
??????? logger.info("Exceute delete(). sql:" + sql + " param:"
??????????????? + param.toString());
??????? update(sql, param);
??? }

??? /**
???? * 執行數據庫檢索操作
???? *
???? * @param sql
???? *??????????? SQL語句(select)
???? * @param param
???? *??????????? 匹配參數列表
???? * @return ResultSet 檢索結果集
???? * @exception DAOException
???? *??????????????? 數據庫操作錯誤是拋出
???? */
??? public ResultSet select(String sql, ArrayList param) throws DAOException {
??????? logger.info("Exceute select(). sql:" + sql + " param:"
??????????????? + param.toString());
??????? ResultSet rs = null;
??????? open(sql);
??????? for (int i = 0; i < param.size(); i++) {
??????????? setParameter(i + 1, param.get(i));
??????? }
??????? rs = executeQuery();
??????? return rs;
??? }

??? /**
???? * @param sql
???? * @return
???? * @exception DAOException
???? */
??? public ResultSet executeQuery(String sql) throws DAOException {
??????? try {

??????????? resultset = statement.executeQuery(sql);
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to call execute executeQuery(). ex:"
??????????????????? + ex.getMessage());
??????????? resultset = null;
??????????? throw new DAOException(ex);
??????? }
??????? return resultset;
??? }

??? /**
???? * @return
???? * @exception DAOException
???? */
??? private ResultSet executeQuery() throws DAOException {
??????? try {

??????????? resultset = prepared.executeQuery();

??????? } catch (SQLException ex) {
??????????? logger.error("Failed to call execute executeQuery(). ex:"
??????????????????? + ex.getMessage());
??????????? resultset = null;
??????????? throw new DAOException(ex);
??????? }
??????? return resultset;
??? }

??? /**
???? * @param sql
???? * @return
???? * @exception DAOException
???? */
??? private int executeUpdate(String sql) throws DAOException {
??????? int count = 0;
??????? try {
??????????? count = statement.executeUpdate(sql);
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to call executeUpdate(). ex:"
??????????????????? + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??????? return count;
??? }

??? /**
???? * @param sql
???? * @return
???? * @exception DAOException
???? */
??? private int executeUpdate() throws DAOException {
??????? int count = 0;
??????? try {
??????????? count = prepared.executeUpdate();
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to call executeUpdate(). ex:"
??????????????????? + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??????? return count;
??? }

??? /**
???? * @exception DAOException
???? */

??? public void close() throws DAOException {
??????? try {

??????????? if (resultset != null) {

??????????????? resultset.close();

??????????????? resultset = null;

??????????? }
??????????? if (prepared != null) {

??????????????? prepared.close();

??????????????? prepared = null;

??????????? }
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to close PreparedStatement and ResultSet. ex:"
??????????????????? + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??????? resultset = null;
??? }

??? public void close1(int i) throws DAOException {
??????? try {

??????????? if (resultset != null) {

??????????????? resultset.close();

??????????????? resultset = null;

??????????? }
??????????? if (prepared != null) {

??????????????? prepared.close();

??????????????? prepared = null;

??????????? }
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to close PreparedStatement and ResultSet. ex:"
??????????????????? + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??????? resultset = null;
??? }

??? /**
???? * @exception DAOException
???? */
??? public void commit() throws DAOException {
??????? try {
??????????? connection.commit();
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to commit. ex:" + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??? }

??? /**
???? * @exception DAOException
???? */
??? public void rollback() throws DAOException {
??????? try {
??????????? connection.rollback();
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to rollback. ex:" + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??? }

??? /**
???? * @exception DAOException
???? */
??? public void terminate() throws DAOException {
??????? try {
??????????? if (connection != null) {
??????????????? if (connection.isClosed() == false)
??????????????????? connection.close();
??????????? }
??????? } catch (SQLException ex) {
??????????? logger.error("Failed to terminate. ex:" + ex.getMessage());
??????????? throw new DAOException(ex);
??????? }
??????? connection = null;
??? }
}

hotelDao.java:

package com.travel.system.hotel.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;

import com.datacenter.system.city.dao.cityDao;
import com.datacenter.system.province.dao.provinceDao;
import com.travel.system.travelInfo.dao.travelInfoDao;
import com.travel.tools.database.DAOException;
import com.travel.tools.database.Database;

/**
?* 作者:yangt
?*
?* 功能:對酒店信息的操作
?*?
?*/
public class hotelDao {

??? /**
???? * 功能:增加酒店信息 修改日期:2005-11-10
???? */
??? public void insert(ArrayList lstParm) throws DAOException {
??????? StringBuffer sql = new StringBuffer();
??????? sql
??????????????? .append("insert into Hotel_HotelInfor (TravelID,HotelName,HotelShow,HotelProvince,HotelCity,HotelAddress,HotelLevel,");
??????? sql
??????????????? .append("HotelContact,HotelTel,HotelFax,HotelWeb,HotelEmail,HotelPostCode,HotelPhoto,HotelNotice,");
??????? sql.append("HotelTraffic,HotelRebate,BankName,BankAccounts,HotelState)");
??????? sql.append(" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.insert(sql.toString(), lstParm);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:增加酒店信息 修改日期:2005-11-10
???? */
??? public void insertForAdmin(ArrayList lstParm) throws DAOException {
??????? StringBuffer sql = new StringBuffer();
??????? sql
??????????????? .append("insert into Hotel_HotelInfor (TravelID,HotelName,HotelShow,HotelProvince,HotelCity,HotelAddress,HotelLevel,");
??????? sql
??????????????? .append("HotelContact,HotelTel,HotelFax,HotelWeb,HotelEmail,HotelPostCode,HotelPhoto,HotelNotice,");
??????? sql.append("HotelTraffic,HotelRebate,BankName,BankAccounts,HotelState)");
??????? sql.append(" values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.insert(sql.toString(), lstParm);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:更新酒店信息 修改日期:2005-11-10
???? */
??? public void update(ArrayList lstParm) throws DAOException {

??????? StringBuffer sql = new StringBuffer();
??????? sql
??????????????? .append("update Hotel_HotelInfor set HotelName=?,HotelShow=?,HotelProvince=?,HotelCity=?,HotelAddress=?,HotelLevel=?, ");
??????? sql.append("HotelContact=?, ");
??????? sql
??????????????? .append("HotelTel=?,HotelFax=?,HotelWeb=?,HotelEmail=?,HotelPostCode=?,HotelNotice=?, ");
??????? sql.append("HotelTraffic=?,HotelRebate=?,BankName=?,BankAccounts=?");
??????? sql.append(" where HotelID=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.update(sql.toString(), lstParm);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:更新酒店信息 修改日期:2005-11-10
???? */
??? public void updateForAdmin(ArrayList lstParm) throws DAOException {

??????? StringBuffer sql = new StringBuffer();
??????? sql
??????????????? .append("update Hotel_HotelInfor set HotelName=?,HotelUserName=?,HotelPass=?,HotelShow=?,HotelProvince=?,HotelCity=?,HotelAddress=?,HotelLevel=?, ");
??????? sql.append("HotelContact=?, ");
??????? sql
??????????????? .append("HotelTel=?,HotelFax=?,HotelWeb=?,HotelEmail=?,HotelPostCode=?,HotelNotice=?, ");
??????? sql.append("HotelTraffic=?,HotelRebate=?,BankName=?,BankAccounts=?,HotelState=?");
??????? sql.append(" where HotelID=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.update(sql.toString(), lstParm);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:更新酒店圖片信息 修改日期:2005-11-14
???? */
??? public void updateHotelPic(String hotelID, String fname)
??????????? throws DAOException {

??????? StringBuffer sql = new StringBuffer();
??????? sql.append("update Hotel_HotelInfor set HotelPhoto=?");
??????? sql.append(" where HotelID=?");
??????? ArrayList lstParam = new ArrayList();
??????? lstParam.add(fname);
??????? lstParam.add(hotelID);
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.update(sql.toString(), lstParam);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:更新酒店視頻 修改日期:2005-11-14
???? */
??? public void updateHotelVideo(String hotelID, String fname)
??????????? throws DAOException {

??????? StringBuffer sql = new StringBuffer();
??????? sql.append("update Hotel_HotelInfor set HotelVideo=?");
??????? sql.append(" where HotelID=?");
??????? ArrayList lstParam = new ArrayList();
??????? lstParam.add(fname);
??????? lstParam.add(hotelID);
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.update(sql.toString(), lstParam);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
??? /**
???? * 功能:更新酒店pass 修改日期:2006-01-03
???? */
??? public void updateHotelPass(ArrayList lstParm)
??????????? throws DAOException {
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("update Hotel_HotelInfor set HotelPass=?");
??????? sql.append(" where HotelUserName=? and HotelID=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.update(sql.toString(), lstParm);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql=null;
??????? }
?? }

??? /**
???? * 功能:刪除酒店信息 修改日期:2005-11-10
???? */
??? public void delete(String hotelID) throws DAOException {
??????? StringBuffer sql = new StringBuffer();
??????? ArrayList lstParm = new ArrayList();
??????? lstParm.add(hotelID);
??????? sql.append("delete from Hotel_HotelInfor ");
??????? sql.append(" where HotelID=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? db.delete(sql.toString(), lstParm);
??????? } catch (DAOException ex) {
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:判斷用戶所在酒店信息是否存在 日期:2005-11-11
???? */
??? public boolean queryExistByHotelUserName(String hotelUserName)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where HotelUserName=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? lstParam.add(hotelUserName);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? if (rs.next()) {
??????????????? rs.close();
??????????????? rs = null;
??????????????? return true;
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? return false;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:得到指定酒店信息 修改日期:2005-11-13
???? */
??? public ArrayList query(String id) throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where HotelID=?");???????
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? lstParam.add(id);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? cityDao cdao = new cityDao();
??????????? while (rs.next()) {
??????????????? row = new HashMap();
??????????????? row.put("HotelID", rs.getString("HotelID"));
??????????????? row.put("HotelUserName", rs.getString("HotelUserName"));???????????????
??????????????? row.put("HotelPass", rs.getString("HotelPass"));
??????????????? row.put("HotelName", rs.getString("HotelName"));???????????????
??????????????? row.put("HotelShow", rs.getString("HotelShow"));
??????????????? row.put("HotelProvince", rs.getString("HotelProvince"));
??????????????? String cityID = rs.getString("HotelCity");
??????????????? String cityName=null;
??????????????? if(cityID!=null)
??????????????? {
??????????????????? cityName = cdao.getch(cityID);
??????????????? }
??????????????? if(cityName!=null)
??????????????? {
??????????????????? row.put("HotelCity",cityName);
??????????????? }else
??????????????? {
??????????????????? row.put("HotelCity",cityID);
??????????????? }
??????????????? row.put("HotelCityID", cityID);
??????????????? row.put("HotelAddress", rs.getString("HotelAddress"));
??????????????? row.put("HotelContact", rs.getString("HotelContact"));
??????????????? row.put("HotelLevel", rs.getString("HotelLevel"));
??????????????? row.put("HotelTel", rs.getString("HotelTel"));
??????????????? row.put("HotelFax", rs.getString("HotelFax"));
??????????????? row.put("HotelEmail", rs.getString("HotelEmail"));
??????????????? row.put("HotelWeb", rs.getString("HotelWeb"));
??????????????? row.put("HotelPostCode", rs.getString("HotelPostCode"));
??????????????? row.put("HotelPhoto", rs.getString("HotelPhoto"));
??????????????? row.put("HotelNotice", rs.getString("HotelNotice"));
??????????????? row.put("HotelTraffic", rs.getString("HotelTraffic"));
??????????????? row.put("HotelRebate", rs.getString("HotelRebate"));
??????????????? row.put("BankName", rs.getString("BankName"));
??????????????? row.put("BankAccounts", rs.getString("BankAccounts"));
??????????????? row.put("HotelVideo", rs.getString("HotelVideo"));
??????????????? row.put("HotelState", rs.getString("HotelState"));
??????????????? list.add(row);
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
??? /**
???? * 功能:得到指定旅行社(ID)的所有酒店 修改日期:2006-01-16
???? */
??? public ArrayList queryAllHotelByTravelID(String id) throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where TravelID=?");???????
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? lstParam.add(id);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;???????????
??????????? while (rs.next()) {
??????????????? row = new HashMap();
??????????????? row.put("HotelID", rs.getString("HotelID"));
??????????????? row.put("HotelName", rs.getString("HotelName"));
??????????????? list.add(row);
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:得到指定酒店圖片名 修改日期:2005-11-13
???? */
??? public String queryPic(String id) throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where HotelID=?");
??????? Database db = new Database();
??????? String temp = null;
??????? try {
??????????? db.initialize();
??????????? lstParam.add(id);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? if (rs.next()) {
??????????????? temp = rs.getString("HotelPhoto");
??????????????? rs.close();
??????????????? rs = null;
??????????????? row = null;
??????????????? return temp;
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return "noexist";
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:得到指定酒店視頻名 修改日期:2005-11-13
???? */
??? public String queryVideo(String id) throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where HotelID=?");
??????? Database db = new Database();
??????? String temp = null;
??????? try {
??????????? db.initialize();
??????????? lstParam.add(id);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? if (rs.next()) {
??????????????? temp = rs.getString("HotelVideo");
??????????????? rs.close();
??????????????? rs = null;
??????????????? row = null;
??????????????? return temp;
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return "noexist";
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:得到指定酒店信息 修改日期:2005-11-13
???? */
??? public ArrayList queryHotelInforByUName(String uName) throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where HotelUserName=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? lstParam.add(uName);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? cityDao cdao = new cityDao();
??????????? while (rs.next()) {
??????????????? row = new HashMap();
??????????????? row.put("HotelID", rs.getString("HotelID"));
??????????????? row.put("HotelUserName", rs.getString("HotelUserName"));
??????????????? row.put("HotelPass", rs.getString("HotelPass"));
??????????????? row.put("HotelName", rs.getString("HotelName"));
??????????????? row.put("HotelShow", rs.getString("HotelShow"));
??????????????? row.put("HotelProvince", rs.getString("HotelProvince"));
??????????????? String cityID = rs.getString("HotelCity");
??????????????? String cityName=null;
??????????????? if(cityID!=null)
??????????????? {
??????????????????? cityName = cdao.getch(cityID);
??????????????? }
??????????????? if(cityName!=null)
??????????????? {
??????????????????? row.put("HotelCity",cityName);
??????????????? }else
??????????????? {
??????????????????? row.put("HotelCity",cityID);
??????????????? }
??????????????? row.put("HotelCityID", cityID);
??????????????? row.put("HotelAddress", rs.getString("HotelAddress"));
??????????????? row.put("HotelContact", rs.getString("HotelContact"));
??????????????? row.put("HotelLevel", rs.getString("HotelLevel"));
??????????????? row.put("HotelTel", rs.getString("HotelTel"));
??????????????? row.put("HotelFax", rs.getString("HotelFax"));
??????????????? row.put("HotelEmail", rs.getString("HotelEmail"));
??????????????? row.put("HotelWeb", rs.getString("HotelWeb"));
??????????????? row.put("HotelPostCode", rs.getString("HotelPostCode"));
??????????????? row.put("HotelPhoto", rs.getString("HotelPhoto"));
??????????????? row.put("HotelNotice", rs.getString("HotelNotice"));
??????????????? row.put("HotelTraffic", rs.getString("HotelTraffic"));
??????????????? row.put("HotelRebate", rs.getString("HotelRebate"));
??????????????? row.put("BankName", rs.getString("BankName"));
??????????????? row.put("BankAccounts", rs.getString("BankAccounts"));
??????????????? row.put("HotelState", rs.getString("HotelState"));
??????????????? list.add(row);
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:得到指定酒店ID 修改日期:2005-12-31
???? */
??? public String queryHotelIDByUName(String uName) throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where HotelUserName=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? lstParam.add(uName);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? while (rs.next()) {
??????????????? return rs.getString("HotelID");
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? return null;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
??? /**
???? * 功能:得到指定ID的酒店名 修改日期:2006-01-16
???? */
??? public String queryNameByID(String id) throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where HotelID=?");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? lstParam.add(id);
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? while (rs.next()) {
??????????????? return rs.getString("HotelName");
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? return null;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }???

??? /**
???? * 功能:得到所有酒店(帶分頁查詢) 修改日期:2005-11-15
???? */
??? public ArrayList queryPage(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? int rowNum = 1;
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor");
??????? sql.append(lstpam);
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? if (intRowCount < 1 || intRowCount == 1) {
??????????????? return list;
??????????? }
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? //?調整待顯示的頁碼
??????????? if (intPage > intPageCount)
??????????????? intPage = intPageCount;
??????????? if (intPageCount > 0) {
??????????????? //將記錄指針定位到待顯示頁的第一條記錄上
??????????????? rs.absolute((intPage - 1) * intPageSize + 1);
??????????????? String[] checkId = new String[intPageSize];
??????????????? //顯示數據
??????????????? int i = 0;
??????????????? cityDao cdao = new cityDao();
??????????????? while (i < intPageSize && !rs.isAfterLast()) {
??????????????????? row = new HashMap();
??????????????????? row.put("HotelID", rs.getString("HotelID"));
??????????????????? row.put("HotelUserName", rs.getString("HotelUserName"));
??????????????????? row.put("HotelPass", rs.getString("HotelPass"));
??????????????????? row.put("HotelName", rs.getString("HotelName"));
??????????????????? row.put("HotelShow", rs.getString("HotelShow"));
??????????????????? row.put("HotelProvince", rs.getString("HotelProvince"));
??????????????????? String cityID = rs.getString("HotelCity");
??????????????????? String cityName=null;
??????????????????? if(cityID!=null)
??????????????????? {
??????????????????????? cityName = cdao.getch(cityID);
??????????????????? }
??????????????????? if(cityName!=null)
??????????????????? {
??????????????????????? row.put("HotelCity",cityName);
??????????????????? }else
??????????????????? {
??????????????????????? row.put("HotelCity",cityID);
??????????????????? }
??????????????????? row.put("HotelCityID", cityID);
??????????????????? row.put("HotelAddress", rs.getString("HotelAddress"));
??????????????????? row.put("HotelContact", rs.getString("HotelContact"));
??????????????????? row.put("HotelLevel", rs.getString("HotelLevel"));
??????????????????? row.put("HotelTel", rs.getString("HotelTel"));
??????????????????? row.put("HotelFax", rs.getString("HotelFax"));
??????????????????? row.put("HotelEmail", rs.getString("HotelEmail"));
??????????????????? row.put("HotelWeb", rs.getString("HotelWeb"));
??????????????????? row.put("HotelPostCode", rs.getString("HotelPostCode"));
??????????????????? row.put("HotelPhoto", rs.getString("HotelPhoto"));
??????????????????? row.put("HotelNotice", rs.getString("HotelNotice"));
??????????????????? row.put("HotelTraffic", rs.getString("HotelTraffic"));
??????????????????? row.put("HotelRebate", rs.getString("HotelRebate"));
??????????????????? row.put("BankName", rs.getString("BankName"));
??????????????????? row.put("BankAccounts", rs.getString("BankAccounts"));
??????????????????? row.put("HotelState", rs.getString("HotelState"));
??????????????????? list.add(row);
??????????????????? rs.next();
??????????????????? i++;
??????????????? }
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:得到總頁數 修改日期:2005-11-15
???? */
??? public String queryCount(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor");
??????? sql.append(lstpam);
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? String count = Integer.toString(intPageCount);
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return count;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
??? /**
???? * 功能:搜索引擎 修改日期:2006-01-17
???? */
??? public ArrayList queryPageForSeach(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? int rowNum = 1;
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select HotelName,HotelID,HotelAddress,HotelShow,HotelLevel,HotelPhoto from view_hotelandroom");
??????? sql.append(lstpam);
??????? sql.append(" group by HotelName,HotelID,HotelAddress,HotelShow,HotelLevel,HotelPhoto");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? if (intRowCount < 1 || intRowCount == 1) {
??????????????? return list;
??????????? }
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? //?調整待顯示的頁碼
??????????? if (intPage > intPageCount)
??????????????? intPage = intPageCount;
??????????? if (intPageCount > 0) {
??????????????? //將記錄指針定位到待顯示頁的第一條記錄上
??????????????? rs.absolute((intPage - 1) * intPageSize + 1);
??????????????? String[] checkId = new String[intPageSize];
??????????????? //顯示數據
??????????????? int i = 0;
??????????????? cityDao cdao = new cityDao();
??????????????? while (i < intPageSize && !rs.isAfterLast()) {
??????????????????? row = new HashMap();
??????????????????? row.put("HotelID", rs.getString("HotelID"));
//??????????????????? row.put("HotelUserName", rs.getString("HotelUserName"));
//??????????????????? row.put("HotelPass", rs.getString("HotelPass"));
??????????????????? row.put("HotelName", rs.getString("HotelName"));
??????????????????? row.put("HotelShow", rs.getString("HotelShow"));
//??????????????????? row.put("HotelProvince", rs.getString("HotelProvince"));
//??????????????????? String cityID = rs.getString("HotelCity");
//??????????????????? String cityName=null;
//??????????????????? if(cityID!=null)
//??????????????????? {
//??????????????????????? cityName = cdao.getch(cityID);
//??????????????????? }
//??????????????????? if(cityName!=null)
//??????????????????? {
//??????????????????????? row.put("HotelCity",cityName);
//??????????????????? }else
//??????????????????? {
//??????????????????????? row.put("HotelCity",cityID);
//??????????????????? }
//??????????????????? row.put("HotelCityID", cityID);
??????????????????? row.put("HotelAddress", rs.getString("HotelAddress"));
//??????????????????? row.put("HotelContact", rs.getString("HotelContact"));
??????????????????? row.put("HotelLevel", rs.getString("HotelLevel"));
//??????????????????? row.put("HotelTel", rs.getString("HotelTel"));
//??????????????????? row.put("HotelFax", rs.getString("HotelFax"));
//??????????????????? row.put("HotelEmail", rs.getString("HotelEmail"));
//??????????????????? row.put("HotelWeb", rs.getString("HotelWeb"));
//??????????????????? row.put("HotelPostCode", rs.getString("HotelPostCode"));
??????????????????? row.put("HotelPhoto", rs.getString("HotelPhoto"));
//??????????????????? row.put("HotelNotice", rs.getString("HotelNotice"));
//??????????????????? row.put("HotelTraffic", rs.getString("HotelTraffic"));
//??????????????????? row.put("HotelRebate", rs.getString("HotelRebate"));
//??????????????????? row.put("BankName", rs.getString("BankName"));
//??????????????????? row.put("BankAccounts", rs.getString("BankAccounts"));
//??????????????????? row.put("HotelState", rs.getString("HotelState"));
??????????????????? list.add(row);
??????????????????? rs.next();
??????????????????? i++;
??????????????? }
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:搜索引擎 修改日期:2006-01-17
???? */
??? public String queryCountForSeach(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select HotelName,HotelID,HotelAddress,HotelShow,HotelLevel,HotelPhoto from view_hotelandroom");
??????? sql.append(lstpam);
??????? sql.append(" group by HotelName,HotelID,HotelAddress,HotelShow,HotelLevel,HotelPhoto");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? String count = Integer.toString(intPageCount);
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return count;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
??? /**
???? * 功能:數據中心統計酒店數量-By Province? 修改日期:2006-01-05
???? */
??? public ArrayList queryPageForDataCenterTotalNumByProvince(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? int rowNum = 1;
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select count(HotelID) as HotelNum,HotelProvince from Hotel_HotelInfor");
??????? sql.append(lstpam);
??sql.append(" group by HotelProvince order by HotelNum desc");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? if (intRowCount < 1 || intRowCount == 1) {
??????????????? return list;
??????????? }
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? //?調整待顯示的頁碼
??????????? if (intPage > intPageCount)
??????????????? intPage = intPageCount;
??????????? if (intPageCount > 0) {
??????????????? //將記錄指針定位到待顯示頁的第一條記錄上
??????????????? rs.absolute((intPage - 1) * intPageSize + 1);
??????????????? String[] checkId = new String[intPageSize];
??????????????? //顯示數據
??????????????? int i = 0;
??????????????? provinceDao pdao = new provinceDao();
??????????????? String tempProvince = null;
??????????????? String tempProvinceName=null;
??????????????? //計算名次　公式：(當前頁數１-１１１１１１１1)* 每頁大小 + 1
??????????????? int j =(intPage-1)*intPageSize+1;??
??????????????? while (i < intPageSize && !rs.isAfterLast()) {
??????????????????? row = new HashMap();
??????????????????? tempProvince = rs.getString("HotelProvince");
??????????????????? row.put("HotelProvinceID",tempProvince);
??????????????????? row.put("HotelNum", rs.getString("HotelNum"));?????????????????
??????????????????? if(tempProvince!=null)
??????????????????? {
??????????????????????? tempProvinceName = pdao.getch(tempProvince);
??????????????????? }
??????????????????? if(tempProvinceName!=null)
??????????????????? {
??????????????????????? row.put("HotelProvinceName",tempProvinceName);
??????????????????? }else
??????????????????? {
??????????????????????? row.put("HotelProvinceName",tempProvince);
??????????????????? }
??????????????????? String OrderNumName= "第 "+j+" 名";
??????????????????? row.put("OrderNumName",OrderNumName);
??????????????????? list.add(row);
??????????????????? tempProvince = null;
??????????????????? tempProvinceName =null;
??????????????????? rs.next();
??????????????????? i++;
??????????????? }
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:數據中心統計酒店數量-By Province? 修改日期:2006-01-05
???? */
??? public String queryCountForDataCenterTotalNumByProvince(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select count(HotelID) as hotelNum,HotelProvince from Hotel_HotelInfor");
??????? sql.append(lstpam);
??sql.append(" group by HotelProvince order by HotelNum desc");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? String count = Integer.toString(intPageCount);
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return count;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
??? /**
???? * 功能:數據中心統計酒店數量-By City? 修改日期:2006-01-05
???? */
??? public ArrayList queryPageForDataCenterTotalNumByCity(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? int rowNum = 1;
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select count(HotelID) as HotelNum,HotelProvince,HotelCity from Hotel_HotelInfor");
??????? sql.append(lstpam);
??sql.append(" group by HotelCity,HotelProvince");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? if (intRowCount < 1 || intRowCount == 1) {
??????????????? return list;
??????????? }
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? //?調整待顯示的頁碼
??????????? if (intPage > intPageCount)
??????????????? intPage = intPageCount;
??????????? if (intPageCount > 0) {
??????????????? //將記錄指針定位到待顯示頁的第一條記錄上
??????????????? rs.absolute((intPage - 1) * intPageSize + 1);
??????????????? String[] checkId = new String[intPageSize];
??????????????? //顯示數據
??????????????? int i = 0;
??????????????? provinceDao pdao = new provinceDao();
??????????????? cityDao cdao = new cityDao();
??????????????? String tempProvince = null;
??????????????? String tempProvinceName=null;
??????????????? String tempCityID = null;
??????????????? String tempCityName=null;
??????????????? //計算名次　公式：(當前頁數１-１１１１１１１1)* 每頁大小 + 1
??????????????? int j =(intPage-1)*intPageSize+1;??
??????????????? while (i < intPageSize && !rs.isAfterLast()) {
??????????????????? row = new HashMap();
??????????????????? tempProvince = rs.getString("HotelProvince");
??????????????????? row.put("HotelProvinceID",tempProvince);
??????????????????? row.put("HotelNum", rs.getString("HotelNum"));?????????????????
??????????????????? if(tempProvince!=null)
??????????????????? {
??????????????????????? tempProvinceName = pdao.getch(tempProvince);
??????????????????? }
??????????????????? if(tempProvinceName!=null)
??????????????????? {
??????????????????????? row.put("HotelProvinceName",tempProvinceName);
??????????????????? }else
??????????????????? {
??????????????????????? row.put("HotelProvinceName",tempProvince);
??????????????????? }
???????????????????
??????????????????? tempCityID = rs.getString("HotelCity");
??????????????????? row.put("HotelCityID",tempCityID);
??????????????????? if(tempCityID!=null)
??????????????????? {
??????????????????????? tempCityName = cdao.getch(tempCityID);
??????????????????? }
??????????????????? if(tempCityName!=null)
??????????????????? {
??????????????????????? row.put("HotelCityName",tempCityName);
??????????????????? }else
??????????????????? {
??????????????????????? row.put("HotelCityName",tempCityID);
??????????????????? }
??????????????????? String OrderNumName= "第 "+j+" 名";
??????????????????? row.put("OrderNumName",OrderNumName);
??????????????????? list.add(row);
??????????????????? tempProvince = null;
??????????????????? tempProvinceName =null;
??????????????????? tempCityID = null;
??????????????????? tempCityName=null;
??????????????????? OrderNumName=null;
??????????????????? rs.next();
??????????????????? i++;
??????????????? }
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:數據中心統計酒店數量-By City? 修改日期:2006-01-05
???? */
??? public String queryCountForDataCenterTotalNumByCity(int intPage, int intPageSize, String lstpam)
??????????? throws DAOException {
??????? ArrayList lstParam = new ArrayList();
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select count(HotelID) as hotelNum,HotelProvince,HotelCity from Hotel_HotelInfor");
??????? sql.append(lstpam);
??sql.append(" group by HotelCity,HotelProvince");
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstParam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? String count = Integer.toString(intPageCount);
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return count;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
??? /**
???? * 功能:得到所有酒店(帶分頁查詢) 修改日期:2005-11-15
???? */
??? public ArrayList queryPageForTravel(int intPage, int intPageSize, ArrayList lstpam)
??????????? throws DAOException {??????
??????? int rowNum = 1;
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where TravelID =? order by HotelID desc");??????
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstpam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? if (intRowCount < 1 || intRowCount == 1) {
??????????????? return list;
??????????? }
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? //?調整待顯示的頁碼
??????????? if (intPage > intPageCount)
??????????????? intPage = intPageCount;
??????????? if (intPageCount > 0) {
??????????????? //將記錄指針定位到待顯示頁的第一條記錄上
??????????????? rs.absolute((intPage - 1) * intPageSize + 1);
??????????????? String[] checkId = new String[intPageSize];
??????????????? //顯示數據
??????????????? int i = 0;
??????????????? provinceDao pdao = new provinceDao();
??????????????? cityDao cdao = new cityDao();
??????????????? travelInfoDao tdao= new travelInfoDao();
??????????????? String tempTravelID = null;
??????????????? String tempProvinceID = null;
??????????????? String cityID=null;
??????????????? String cityName=null;
??????????????? while (i < intPageSize && !rs.isAfterLast()) {
??????????????????? row = new HashMap();
??????????????????? row.put("HotelID", rs.getString("HotelID"));
??????????????????? tempTravelID = rs.getString("TravelID");
??????????????????? row.put("TravelID", tempTravelID);
??????????????????? if(tempTravelID!=null)
??????????????????? {???????????????????????
??????????????????????? row.put("TravelName", tdao.getch(tempTravelID));
??????????????????? }else
??????????????????????? row.put("TravelName", tempTravelID);
??????????????????? row.put("HotelUserName", rs.getString("HotelUserName"));
??????????????????? row.put("HotelPass", rs.getString("HotelPass"));
??????????????????? row.put("HotelName", rs.getString("HotelName"));
??????????????????? row.put("HotelShow", rs.getString("HotelShow"));
??????????????????? tempProvinceID = rs.getString("HotelProvince");
??????????????????? row.put("HotelProvince", tempProvinceID);
??????????????????? if(tempProvinceID!=null)
??????????????????? {
??????????????????????? row.put("ProvinceName", pdao.getch(tempProvinceID));
??????????????????? }else
??????????????????????? row.put("ProvinceName", tempProvinceID);
??????????????????? cityID = rs.getString("HotelCity");??????????????????
??????????????????? if(cityID!=null)
??????????????????? {
??????????????????????? cityName = cdao.getch(cityID);
??????????????????? }
??????????????????? if(cityName!=null)
??????????????????? {
??????????????????????? row.put("HotelCity",cityName);
??????????????????? }else
??????????????????? {
??????????????????????? row.put("HotelCity",cityID);
??????????????????? }
??????????????????? row.put("HotelCityID", cityID);
??????????????????? row.put("HotelAddress", rs.getString("HotelAddress"));
??????????????????? row.put("HotelContact", rs.getString("HotelContact"));
??????????????????? row.put("HotelLevel", rs.getString("HotelLevel"));
??????????????????? row.put("HotelTel", rs.getString("HotelTel"));
??????????????????? row.put("HotelFax", rs.getString("HotelFax"));
??????????????????? row.put("HotelEmail", rs.getString("HotelEmail"));
??????????????????? row.put("HotelWeb", rs.getString("HotelWeb"));
??????????????????? row.put("HotelPostCode", rs.getString("HotelPostCode"));
??????????????????? row.put("HotelPhoto", rs.getString("HotelPhoto"));
??????????????????? row.put("HotelNotice", rs.getString("HotelNotice"));
??????????????????? row.put("HotelTraffic", rs.getString("HotelTraffic"));
??????????????????? row.put("HotelRebate", rs.getString("HotelRebate"));
??????????????????? row.put("BankName", rs.getString("BankName"));
??????????????????? row.put("BankAccounts", rs.getString("BankAccounts"));
??????????????????? row.put("HotelState", rs.getString("HotelState"));
??????????????????? list.add(row);
??????????????????? rs.next();
??????????????????? tempTravelID = null;
??????????????????? tempProvinceID = null;
??????????????????? cityID=null;
??????????????????? cityName=null;
??????????????????? i++;
??????????????? }
??????????? }
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return list;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }

??? /**
???? * 功能:得到總頁數 修改日期:2005-11-15
???? */
??? public String queryCountForTravel(int intPage, int intPageSize, ArrayList lstpam)
??????????? throws DAOException {??????
??????? StringBuffer sql = new StringBuffer();
??????? sql.append("select * from Hotel_HotelInfor where TravelID =? order by HotelID desc");??????
??????? Database db = new Database();
??????? try {
??????????? db.initialize();
??????????? ResultSet rs = db.select(sql.toString(), lstpam);
??????????? ArrayList list = new ArrayList();
??????????? HashMap row;
??????????? int intRowCount = 0;
??????????? rs.last();
??????????? intRowCount = rs.getRow();
??????????? intRowCount = intRowCount + 1; //獲取記錄總數
??????????? //?記算總頁數
??????????? int intPageCount = (intRowCount + intPageSize - 1) / intPageSize;
??????????? if ((intRowCount - 1) % intPageSize == 0) {
??????????????? intPageCount = (intRowCount - 1) / intPageSize;
??????????? }
??????????? String count = Integer.toString(intPageCount);
??????????? rs.close();
??????????? rs = null;
??????????? row = null;
??????????? return count;
??????? } catch (SQLException ex) {
??????????? System.out.println(ex);
??????????? throw new DAOException(ex.getMessage());
??????? } finally {
??????????? db.close();
??????????? db.terminate();
??????????? db = null;
??????????? sql = null;
??????? }
??? }
???
}

        本Blog純屬個人學習、工作需要，記錄相關資料。請不要發表任何有人身攻擊的言論，謝謝！ www.zhipsoft.cn