hotel.zip

package com.fcx.page; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.HashMap; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; public class dbpage { //定义类内的私有成员 //====================================================================================== //定义数据库连接信息 private String DRIVER="com.microsoft.sqlserver.jdbc.SQLServerDriver"; private String URL="jdbc:sqlserver://127.0.0.1:1434;dataBaseName=shop"; private String USER="sa"; private String PASSWORD="sa"; //====================================================================================== //数据库连接API接口 private Connection conn;//定义连接对象 private PreparedStatement ps;//执行SQL语句对象 private ResultSet res;//定义结果集对象 //====================================================================================== //分页参数变量 static private int CountRecord = 0;//记录的总条数 static private int PageRecord =0;//每页显示记录条数 static private int PageSize = 0;//总页数 static private int PageNo = 1;//当前是页 //====================================================================================== //初始化无参构造函数 public dbpage() throws SQLException, ClassNotFoundException { //初始化:数据库的驱动包 //SQL SERVER DRIVER： com.microsoft.jdbc.sqlserver.SQLServerDriver //MYSQL DRIVER： com.mysql.jdbc.Driver //初始化JDBC数据库连接字符串 //SQL SERVER DRIVER： "jdbc:microsoft:sqlserver://127.0.0.1:1433;dataBaseName=数据库","账号","密码" //MYSQL DRIVER： "jdbc:mysql://127.0.0.1:3306/数据库","账号","密码" //初始化:数据库的驱动包 Class.forName(this.DRIVER); } //获取数据库的连接指针对象 public Connection getConnection() throws SQLException{ Connection connection=null; connection=DriverManager.getConnection(this.URL,this.USER,this.PASSWORD); return connection; } //====================================================================================== //获取数据表内的总记集数 public int get_CountRecord(String table_name) throws SQLException{ //执行SQL语句 try { conn=getConnection(); ps=conn.prepareStatement("select count(*) from "+table_name); //通过记录集指针来接收返回的SQL查询的结果集 res=ps.executeQuery();//执行SQL查询命令，并返回结果集 //方法一：(MySql 测试通过) //res.last();//将记集移动数据表的最后一条 //countRecord=res.getRow();//获取总行数 //方法二：(SqlServer 测试通过) res.next();//将记录集指针进行下移 CountRecord=res.getInt(1); //获取结果集中的第一个索引得取的值 } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ this.releaseStatement(res);//关闭记录集 this.releaseStatement(ps);//关闭SQL this.releaseConnection(conn);//关闭数据库连接 } // CountRecord=this.getCountRecord();//获取总条数 // System.out.println(CountRecord); return CountRecord; } //====================================================================================== //获取总页数 (DWR 调用测试不通过) public int get_PageSize(){ if(this.CountRecord%this.PageRecord==0){ PageSize=this.CountRecord/this.PageRecord; } else{ PageSize=this.CountRecord/this.PageRecord+1; } // PageSize=this.getPageSize(); // System.out.println(PageSize); return PageSize; } //获取总页数 (DWR 调用测试通过) public int getCountPageSize(int countrecord,int pagesize){ if(countrecord%pagesize==0)//如果能整除 this.PageSize=countrecord/pagesize; else this.PageSize=countrecord/pagesize+1; return this.PageSize; } //========================================================B============================== //测试方法一: //返回查询的页 public ResultSet get_PageData(String table,String primaryId,int pageNumber,int sum) throws SQLException { //加载数据库连接字符串 //conn=DriverManager.getConnection("jdbc:microsoft:sqlserver://127.0.0.1:1433;dataBaseName=sbgl","sa","sa"); //获取连接 conn=getConnection(); //执行SQL语句 ps=conn.prepareStatement("select top "+sum+" * from "+table+" where "+primaryId+" not in(select top "+pageNumber+" "+primaryId+" from "+table+")"); //select * from user limit 0,5 //通过记录集指针来接收返回的SQL查询的结果集 res=ps.executeQuery();//执行SQL查询命令，并返回结果集 return res; } //====================================================================================== //测试方法二: //====================================================================================== //查询所有数据函数，返回LIST集合类 //重载方法一： public List executeQuery(String sql){ try { this.conn =getConnection(); Statement stmt = this.conn.createStatement(); this.res= stmt.executeQuery(sql); List row = new ArrayList(); while (this.res.next()) { //方法一：键值对对象 HashMap()类为无序排列方式 //Map col = new HashMap(); //方法一：键值对对象 LinkedHashMap()类为有序排列方式 Map col = new LinkedHashMap(); for (int i = 1; i <= this.res.getMetaData().getColumnCount(); i++) col.put(this.res.getMetaData().getColumnName(i), this.res.getString(i)); row.add(col); } return (List) row; } catch (SQLException e) { return null; } catch (Exception e) { return null; }finally{ this.releaseStatement(this.res); this.releaseConnection(this.conn); } } //====================================================================================== //分页查询函数,返回LIST集合 //重载方法二： //制做翻页查询函数 public List executeQuery(String SQL,int pageNumber,int pageRecord){ List row = new ArrayList();//创建集合对象 Statement stmt;//定义starement对象 int p = 0;//控制读取的数量 try { stmt = getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); this.res= stmt.executeQuery(SQL);//执行SQL命令 if(res.last()){ //只要到记录集的最后一条数据时 this.CountRecord = res.getRow(); //则获取共数少数，求得总行数 //将总数赋给当前类的私有成员变量 //this.CountRecord = numberOfRecords; //求得总页数 this.PageSize = CountRecord / pageRecord + ( (CountRecord % pageRecord)>0? 1 : 0); //此函数应用了三元运算符 //this.PageSize=totalPages; //根据用户输入的页数，则求得应从多少条开始查询，并查出指定的数目 int start = pageRecord * (( pageNumber > PageSize ? PageSize : pageNumber) -1 )+1 ; res.absolute(start); //将记录集移动到指定的位置 do { Map col = new LinkedHashMap(); for (int i = 1; i <= res.getMetaData().getColumnCount(); i++) col.put(res.getMetaData().getColumnName(i), res.getString(i)); row.add(col); } while (res.next() && (++p)<pageRecord); } return row; }catch (SQLException e) { e.printStackTrace(); return null; } catch (Exception e) { e.printStackTrace(); return null; }finally{ this.releaseStatement(this.re