public class PageBean {
private static final int size=5;
private int count;//总条数
private int pageSize;//每页条数
private int pageCount;//总页数
private int curPage;//当前页
public PageBean(){
this.pageSize=size;
curPage=1;
}
public PageBean(int pageSize){
this.pageSize=pageSize;
curPage=1;
}
// public int getPCount(){
// System.out.println("==============="+pageCount);
// return pageCount;
// }
public int getCount() {
//System.out.println("============"+count);
return count;
}
public void setCount(int count) {
this.count = count;
//计算总页数
this.pageCount=count%this.pageSize==0?count/this.pageSize:count/this.pageSize+1;
System.out.println("====~~~~"+pageCount);
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getPageCount() {
//this.pageCount=this.count%this.pageSize==0?this.count/this.pageSize:this.count/this.pageSize+1;
System.out.println("==============="+pageCount);
return this.pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getCurPage() {
return curPage;
}
public void setCurPage(int curPage) {
this.curPage = curPage;
}
public int prePage(){
if(this.curPage==1){
return 1;
}
return this.curPage-1;
}
public int nextPage(){
if(this.curPage==pageCount){
return pageCount;
}
return curPage+1;
}
public int getStart(){
return this.pageSize*(this.curPage-1);
}
public class DbDao {
private Statement st;
public DbDao(Statement st) {
// TODO 自动生成的构造函数存根
this.st = st;
}
public int insert(String sql) {
int t = 0;
try {
t = st.executeUpdate(sql);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return t;
}
public int update(String sql) {
int t = 0;
try {
t = st.executeUpdate(sql);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return t;
}
public int del(String sql) {
int t = 0;
try {
t = st.executeUpdate(sql);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return t;
}
public ResultSet query(String sql) {
ResultSet rs=null;
try {
rs = st.executeQuery(sql);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return rs;
}
public ResultSet pageQuery(String sql,PageBean page) {
ResultSet rs=null;
try {
int count=queryCount(sql);
if(count==0){
return null;
}
page.setCount(count);
sql=sql+" limit "+page.getStart()+","+page.getPageSize();
rs = st.executeQuery(sql);
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
return rs;
}
public int queryCount(String sql){
String countsql="select count(*) c from ("+sql+")t";
ResultSet rs=this.query(countsql);
try {
if(rs.next()){
return rs.getInt(1);
}
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
return 0;
}
return 0;
}
@Override
protected void finalize() throws Throwable {
// TODO 自动生成的方法存根
super.finalize();
if(st!=null)
{
st.close();
}
}
public <E> List<E> queryObjectList(String sql,String ClassName) throws SQLException, IllegalArgumentException, IllegalAccessException, InstantiationException, ClassNotFoundException
{
//连接数据库
ResultSet rs=this.query(sql);
ResultSetMetaData meta=rs.getMetaData();
int count=meta.getColumnCount();//字段的数量
Class cla=Class.forName(ClassName);
Field[] fs=cla.getDeclaredFields();
List<E> list=new ArrayList<E>();
while(rs.next())
{
Object obj=cla.newInstance();
for(int i=1;i<=count;i++)//数组的字段
{
for(Field f:fs)//类里的属性
{
//类的字段名称==数据库字段名称 赋值
if(f.getName().equals(meta.getColumnName(i))) //属性是否和字段一样
{
if("INT".equals(meta.getColumnTypeName(i)))
{
f.set(obj, rs.getInt(meta.getColumnName(i)));
}
if("VARCHAR".equals(meta.getColumnTypeName(i)))
{
f.set(obj, rs.getString(meta.getColumnName(i)));
}
}
}
}
list.add((E)obj);
}
rs.close();
return list;
}
public class Testservlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = -5562520410425589914L;
@Override
protected void service(HttpServletRequest req, HttpServletResponse response) throws ServletException, IOException {
// TODO 自动生成的方法存根
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}//驱动
String url = "jdbc:mysql://127.0.0.1:3306/mysql_test";//url
Connection conn=null;
Statement st=null;
try {
conn = DriverManager.getConnection(url, "root", "heihei77");
st=conn.createStatement();
} catch (SQLException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
String name="";
Integer age=0;
if(req.getParameter("username")!=null&&req.getParameter("username").trim()!=""){
name=req.getParameter("username").trim();
}
if(req.getParameter("age")!=null&&req.getParameter("age").trim()!=""){
age=Integer.parseInt(req.getParameter("age").trim());
}
//查询当前表格的数据
DbDao dbDao = new DbDao(st);
//ResultSet rs=null;
PageBean pageBean=new PageBean();
String page=req.getParameter("page");//??????????????????????
if(page!=null&&!"".equals(page)){
pageBean.setCurPage(Integer.parseInt(page.trim()));
}
List<User> list=new ArrayList<User>();
try {
ResultSet rs=dbDao.pageQuery("select id,name,age from sqlpp",pageBean);
if(rs!=null){
while(rs.next()){
User u=new User();
u.setAge(rs.getInt("age"));
u.setId(rs.getInt("id"));
u.setName(rs.getString("name"));
list.add(u);
}}
rs.close();
} catch (SQLException e1) {
// TODO 自动生成的 catch 块
e1.printStackTrace();
}
//保存list req
req.setAttribute("list", list);
req.setAttribute("pageBean", pageBean);
req.getRequestDispatcher("userList.jsp").forward(req, response);
}