最近写了一个分页小插件, 只能用于 mysql数据库,还得扩充啊。
mysql的分页sql 语句为 select * from tablename limit a,b。a为开始的索引,b 为结束索引。
若per 为每页要显示的数量 page 为当前第几页
则 查询到的sql语句为 String sql = "select * from "+ tablename +" limit " + page*per + "," + per ;
再把查询到的ResultSet结果集放到session 中不就实现分页了吗?
有一个问题,在表tablename中取出了数据后该以什么形式存到session里呢?
如表user为:
则相应的类为:
package a;
public class User {
private String name;
private int id;
private String pwd;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
}
将取得的数据放到此类的对象中存。用反射机制。
当我们用此插件时应只配置一些东西就能使用,如
要配置 表名 tablename,
插件不应当自己连接数据库,不然在项目运行时,就有>=2个地方会连接上数据库,不好.
写一个接口:我们在使用前都要实现它。
package cn.netjava.db;
import java.sql.Connection;
/**
* 取得数据库Connection接口
* 用户必须得实现这个接口
* @author sky
*
*/
public interface DB_Interface {
/**
* 取得数据的Connection连接对象
* @return Connection连接对象
*/
public Connection getConn();
}
它的作用上面已经说了, 取得数据的Connection连接对象 ,这样插件本身没有和数据库偶合在一起了。
我们若只作测试用,就可以这样写它的实现类:
package ***;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import cn.netjava.db.DB_Interface;
import cn.netjava.debug.Log;
public class DB_test implements DB_Interface{
Connection conn = null;
public Connection getConn() {
String url="jdbc:mysql://localhost:3306/catpage";
String root="root";
String pwd="root";
Log.printLog("user:"+root+"\tpwd:"+pwd);
if(conn!=null){
return conn;
}
try {
Log.printLog("user:"+root+"\tpwd:"+pwd);
Class.forName("com.mysql.jdbc.Driver");
Log.printLog("user:"+root+"\tpwd:"+pwd);
conn=DriverManager.getConnection(url,root,pwd);
Log.printLog("连接成功");
} catch (ClassNotFoundException e) {
e.printStackTrace();
Log.printLog(this.getClass()+"中,找不到Driver");
} catch (SQLException e) {
e.printStackTrace();
Log.printLog(this.getClass()+"中,连接出错");
}
return conn;
}
}
上面用到了Log类. 只是用于打印而已。
如果使用时将此DB_test类的Class传到别的类中,那么别的类就可以取得Conncetion对象了
同理也可以实例化User类的对象. 还需要配置一下表的字段名,和set方法,还有参数类型. 如将id 取出来了,就要setId(int id),参数类型为int
while(rs.next()){
Object o = typePojo.newInstance();
for(Function f: funlist){
if(f.getParamtype().equals("int")){
Method func1 =typePojo.getMethod(f.getFuncName(),new Class[]{int.class});
func1.invoke(o, rs.getInt(f.getWordname()));
}
else if(f.getParamtype().equals("String")){
Method func1 =typePojo.getMethod(f.getFuncName(),new Class[]{String.class});
func1.invoke(o, rs.getString(f.getWordname()));
}
else if(f.getParamtype().equals("double")){
Method func1 =typePojo.getMethod(f.getFuncName(),new Class[]{double.class});
func1.invoke(o, rs.getDouble(f.getWordname()));
}
注:typePojo为Class类型,此处为User.Class.
Function类:
package cn.netjava.model;
/**
* set方法的封装类
* @author sky
*
*/
public class Function {
private String wordname; //数据库里的字段
private String funcName; //对应的set方法
private String paramtype; //参数类型
public Function(String wordname, String funcName,String paramtype){
this.wordname = wordname;
this.funcName = funcName;
this.paramtype = paramtype;
}
public String getWordname() {
return wordname;
}
public void setWordname(String wordname) {
this.wordname = wordname;
}
public String getFuncName() {
return funcName;
}
public void setFuncName(String funcName) {
this.funcName = funcName;
}
public String getParamtype() {
return paramtype;
}
public void setParamtype(String paramtype) {
this.paramtype = paramtype;
}
}
分页管里类:
package cn.netjava.plugin;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import cn.netjava.db.DB_Conn;
import cn.netjava.db.DB_Interface;
import cn.netjava.debug.Log;
import cn.netjava.exception.CPException;
import cn.netjava.model.Function;
/**
* 分类操作
* @author sky
*
*/
public class PageCut {
private int per = -1; //每页显示数量
private String tablename = null; //"user"; //要分页的数据表名
private static DB_Conn dbconn = null; //数库连接
private Class typePojo = null; //从数据库中取出数据放入到一个类中
private List<Function> funlist = new ArrayList<Function>();//typePojo的set方法
private static PageCut pc = null; //单例模式的实例
private Class dbClass = null; //用户必须写的连接数据库
/**
* 构造一个实例
* @param dbClass 用户必须写的连接数据库类,可取得Connection对象
* @param typePojo 数据要存的类
* @param tablename 要查询的表名
*/
private PageCut(Class dbClass, Class typePojo, String tablename ){
this.dbClass = dbClass;
this.typePojo = typePojo;
this.tablename = tablename;
if(dbconn == null){
try {
DB_Interface dbt = (DB_Interface)dbClass.newInstance();
dbconn = new DB_Conn(dbt.getConn());
} catch (Exception e) {
Log.setAllowOut();
Log.printLog("无法实例化"+dbClass);
e.printStackTrace();
Log.setForbidden();
}
}
}
/**
* 创建或修改分页的设置
* @param dbClass //用于取得连接到数库Connection的类
* @param typePojo //数据存于此类中
* @param tablename //表名
* @return 分页操作类实例
*/
public static PageCut create(Class dbClass, Class typePojo, String tablename ){
if(pc == null){
pc = new PageCut(dbClass, typePojo, tablename);
}
else{
pc.setConfig(dbClass, typePojo, tablename);
}
pc.removeFunc();
return pc;
}
/**
* 修改分页配置
* @param dbClass用于取得连接到数库Connection的类
* @param typePojo数据存于此类中
* @param tablename表名
*/
public void setConfig(Class dbClass, Class typePojo, String tablename ){
this.dbClass = dbClass;
this.typePojo = typePojo;
this.tablename = tablename;
}
/**
* 添加set方法
* @param func 方法类的对象
*/
public void addFunc(Function func){
funlist.add(func);
}
/**
* 清空set方法
*/
public void removeFunc(){
funlist.clear();
}
/**
* 设置分页显示的个数
* @param per 每页显示数
*/
public void setPagePer(int per){
this.per = per;
}
/**
*
* @param per 每页要显示的数量
* @return 有多少页,
* 出错返回0;
*/
public int getPageCount(int per) throws CPException {
String sql = "select count(*) from "+ tablename;
if(tablename ==null){
CPException e = new CPException("表名没有进行设置");
throw e;
}
if(per<0){
CPException e = new CPException("per小于了0");
throw e;
}
this.per=per;
PreparedStatement pstmt = dbconn.getPSatement(sql);
try{
ResultSet rs = pstmt.executeQuery();
int rt = 0;
int rt1 = 0;
if(rs.next()){
rt = rs.getInt(1);
Log.printLog("数量为"+rt);
rt1 = rt/per + (rt%per==0? 0 : 1 );
Log.printLog("数量ujgj为"+rt1);
}
return rt1;
}
catch(Exception e){
e.printStackTrace();
}
return 0;
}
/**
*
* @param page 要显示第几页
* @return 要显示的内容
*/
public List<Object> getType(int page) throws CPException{
String sql = "select * from "+ tablename +" limit " + page*per + "," + per ;
if(tablename ==null){
CPException e = new CPException("表名没有进行设置");
throw e;
}
if(per<0){
CPException e = new CPException("per小于了0");
throw e;
}
PreparedStatement pstmt = dbconn.getPSatement(sql);
List<Object> list = new ArrayList<Object>();
ResultSet rs = null;
try {
rs = pstmt.executeQuery();
} catch (SQLException e1) {
Log.setAllowOut();
Log.printLog("sql语句出错,maybe tablename不正确");
e1.printStackTrace();
Log.setForbidden();
}
try {
while(rs.next()){
Object o = typePojo.newInstance();
for(Function f: funlist){
if(f.getParamtype().equals("int")){
Method func1 =typePojo.getMethod(f.getFuncName(),new Class[]{int.class});
func1.invoke(o, rs.getInt(f.getWordname()));
}
else if(f.getParamtype().equals("String")){
Method func1 =typePojo.getMethod(f.getFuncName(),new Class[]{String.class});
func1.invoke(o, rs.getString(f.getWordname()));
}
else if(f.getParamtype().equals("double")){
Method func1 =typePojo.getMethod(f.getFuncName(),new Class[]{double.class});
func1.invoke(o, rs.getDouble(f.getWordname()));
}
else{
CPException e = new CPException(f.getParamtype()+"无此参数类型");
throw e;
}
}
list.add(o);
}
} catch (SecurityException e) {
Log.setAllowOut();
Log.printLog("没有权限");
e.printStackTrace();
Log.setForbidden();
} catch (IllegalArgumentException e) {
Log.setAllowOut();
Log.printLog("参数不全法");
e.printStackTrace();
Log.setForbidden();
} catch (SQLException e) {
Log.setAllowOut();
Log.printLog("sql语句出错,maybe tablename不正确");
e.printStackTrace();
Log.setForbidden();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
return list;
}
}
最后在综合起来:写一个servlet
package cn.netjava.servlet;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import cn.netjava.db.DB_test;
import cn.netjava.model.Function;
import cn.netjava.model.User;
import cn.netjava.plugin.PageCut;
public class Control extends HttpServlet {
public void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String spage = request.getParameter("page");
PageCut pc = PageCut.create(DB_test.class, User.class,"user");
pc.addFunc(new Function("id", "setId","int"));
pc.addFunc(new Function("name", "setName" ,"String"));
pc.addFunc(new Function("pwd", "setPwd", "String"));
HttpSession session = request.getSession();
int page = 0;
if(spage!=null){
page = Integer.parseInt(spage);
}
try{
int pagenum = pc.getPageCount(10);
session.setAttribute("pagenum",pagenum);
session.setAttribute("pageindex",page);
List<Object> listuser = pc.getType(page);
session.setAttribute("listuser",listuser);
response.sendRedirect("cutpage.jsp");
}
catch(Exception e){
e.printStackTrace();
}
}
}
这样就把数据存在session中了
可以这样显示出来:
共${pagenum }页 <table> <tr> <td>id</td> <td>name</td> <td>密码</td> </tr> <% List<Object> listuser = (List<Object>)session.getAttribute("listuser"); for(Object o : listuser) { User u = (User)o; %><tr> <td><%=u.getId() %></td> <td><%=u.getName() %></td> <td><%=u.getPwd() %></td> </tr> <% } %> </table> <input type = "button" value="上一页" οnclick="before()"> <input type = "button" value="下一页" οnclick="next()"> <script type="text/javascript"> function before() { window.location.href="control?page="+${pageindex-1}; } function next() { window.location.href="control?page="+${pageindex+1}; } </script>
这个东西很不好,每次请求时都要写些配置,想把这些配置都放到xml中,就像 写一个servlet那样,一个servlet对应好多标签。但不知道该怎么做,哪位仁兄赐教一下啊!