用c3p0连接oracle怎么分页,JSP分页(MySql+c3p0+dbutils)

JSP分页(MySql+c3p0+dbutils)

来源：互联网

作者：佚名

时间：2015-02-16 17:27

为什么要对数据进行分页？当数据较多时，页面就会变的很庞大，不仅会影响到用户的使用，而且还有加重服务器的负担。下面简单的实现了数据的分页。第一步：导入相

为什么要对数据进行分页？当数据较多时，页面就会变的很庞大，不仅会影响到用户的使用，而且还有加重服务器的负担。下面简单的实现了数据的分页。

第一步：导入相应的jar包

需要导入c3p0，dbutils,mysql驱动等jar包。

第二步：创建数据库和表, 配置c3p0， 创建工具类，User类

创建数据库，并准备测试数据(可以自行生成)

create database contacts;

use contacts;

create table users(

id varchar(32),

username varchar(36),

password varchar(36),

constraint user_pk primary key(id)

);

c3p0-config.xml

30000

30

2

30

10

2

50

3

com.mysql.jdbc.Driver

root

123456

1000

30

2

30

5

2

50

3

com.mysql.jdbc.Driver

root

123456

DataSourceUtil.java

package cn.zq.util;

import java.sql.Connection;

import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DataSourceUtil {

private static DataSource ds;

static{

ds = new ComboPooledDataSource("contacts");

}

public static DataSource getDataSource(){

return ds;

}

public static Connection getConnection() throws SQLException{

return ds.getConnection();

}

}

User.java

package cn.zq.domain;

public class User {

private String id;

private String username;

private String password;

public User() {}

public User(String id, String username, String password) {

this.id = id;

this.username = username;

this.password = password;

}

public void setId(String id) {

this.id = id;

}

public void setUsername(String username) {

this.username = username;

}

public void setPassword(String password) {

this.password = password;

}

public String toString() {

return "User [id=" + id + ", username=" + username + ", password="

+ password + "]";

}

public String getId() {

return id;

}

public String getUsername() {

return username;

}

public String getPassword() {

return password;

}

}

第三步：创建并配置servlet，创建显示页面

UserServlet.java

package cn.zq.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 org.apache.commons.dbutils.QueryRunner;

import org.apache.commons.dbutils.handlers.BeanListHandler;

import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.zq.domain.User;

import cn.zq.util.DataSourceUtil;

public class UserServlet extends HttpServlet {

public void init() throws ServletException {

try {

Class.forName("cn.zq.util.DataSourceUtil");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

doPost(request, response);

}

public void doPost(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

//which page to show.

String pn = request.getParameter("pn");

int pageNum = 1;

try{

pageNum = Integer.parseInt(pn);

}catch(Throwable t){

//ignore

}

int pageSize = 10;

QueryRunner run = new QueryRunner(DataSourceUtil.getDataSource());

try {

String sql = "SELECT COUNT(1) from users";

int totalRecord = run.query( sql, new ScalarHandler() )

.intValue();

System.err.println("totalRecord = " + totalRecord);

//(11 + ( 10 -1))/10

int pageCount = (totalRecord + (pageSize - 1)) / pageSize;

if(pageNum < 0){

pageNum = 1;

}

if(pageNum > pageCount){

pageNum = pageCount;

}

//0, 10 10, 20

int m = (pageNum - 1)*pageSize;

int n = pageSize;

sql = "SELECT * FROM users LIMIT ?, ?";

List userList = run.query(sql, new BeanListHandler(User.class), m, n);

//分页显示多少个页号

int no = 10;

int beginPageIndex = 0;

int endPageIndex = 0;

if(pageCount <= no){

beginPageIndex = 1;

endPageIndex = pageNum;

}else{

beginPageIndex = pageNum - no/2;

endPageIndex = beginPageIndex + (no -1);

if(beginPageIndex < 1){

beginPageIndex = 1;

endPageIndex = no;

}

if(endPageIndex > pageCount){

endPageIndex = pageCount;

beginPageIndex = endPageIndex - (no - 1);

}

}

request.setAttribute("pageCount", pageCount);

request.setAttribute("totalRecord", totalRecord);

request.setAttribute("pageNum", pageNum);

request.setAttribute("beginPageIndex", beginPageIndex);

request.setAttribute("endPageIndex", endPageIndex);

request.setAttribute("userList", userList);

request.getRequestDispatcher("/page/user.jsp")

.forward(request, response);;

} catch (Exception e) {

e.printStackTrace();

}

}

}