eclipse实现MySQL分页的类_JavaWeb案例(MVC+MySQL+分页功能)+前后端分离

具备的测试工具：Eclipse，MySQL数据库，HBuilder，jstl1.2jar，mysql.connector.java.8.0.jar

jstl1.2jar下载地址：https://pan.baidu.com/s/1T23zxoEg3jlZHFikrbWtHw

mysql.connector.java.8.0.jar包下载地址：https://pan.baidu.com/s/1kqfOuxo3nOT--CTrlp1BmA

首先打开Eclipse创建一个动态Web项目(Dynamic Web Project)，在Src下分别创建以下包

其中web其实就是对应action层，这就是典型的MVC模型，这是属于后端

首先创建与你数据库的表结构对应的实体类

1、我的数据库结构如下

建立数据库

create database LoveDB;

建立数据表LoveInHeart

use LoveDB;

create table LoveInHeart(

lid int auto_increment primary key,

lname varchar(50) not null,

lsex varchar(50) not null,

ltime date not null,

lmoney float

);

然后可以向里面添加一些测试数据

insert into LoveInHeart(lname,lsex,ltime,lmoney)

values ('张三','男','1999-02-15',1000000);

insert into LoveInHeart(lname,lsex,ltime,lmoney)

values ('李四','女','1998-8-19',1000);

insert into LoveInHeart(lname,lsex,ltime,lmoney)

values ('科比','男','2008-05-05',100);

然后实体类的属性参数要和数据库的表结构保持一致，注：实体类是放在entity包下

声明变量以后进行封装，右键选择 source --> Getter and Setter ，然后select All 点击完成

接下来创建与数据库链接的DatabaseConnection类来完成与数据库的交互，在dao包下新建一个类，名字为：BaseDB

packagecom.nf.dao;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;public classBaseDao {private static final String DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";private static final String SERVER_IP = "localhost";//服务器IP地址

private static final String DATABASE_NAME = "lovedb";//连接到哪一个数据库

private static final String USER_NAME = "root";//用户名

private static final String PASSWORD = "123456";//密码

publicConnection getConnection() {

Connection conn= null;

String jdbcUrl= "jdbc:mysql://" + SERVER_IP + ":3306/" +DATABASE_NAME+ "?serverTimezone=Asia/Shanghai&useSSL=true";try{

Class.forName(DRIVER_CLASS);

conn=DriverManager.getConnection(jdbcUrl, USER_NAME, PASSWORD);

}catch(Exception e) {

System.out.println("获取连接时，异常" +e.getMessage());

conn= null;

}returnconn;

}public voidcloseAll(Connection conn, PreparedStatement pst, ResultSet rs) {try{if (rs != null) {

rs.close();

}if (pst != null) {

pst.close();

}if (conn != null) {

conn.close();

}

}catch(SQLException e) {//TODO Auto-generated catch block

e.printStackTrace();

}

}

}

在dao层下面创建一个Interface类，声明三个方法(查询、添加、删除)，名字：LoveInHeartDao

packagecom.nf.dao;importjava.util.List;importcom.nf.entity.LoveInHeart;importcom.nf.util.MyPage;public interfaceLoveInHeartDao{public ListgetAll(MyPage myPage);public booleanadd(LoveInHeart love);public boolean delete(intid);

}

然后创建一个实现接口class类(Implments)，名字：LoveInHeart_Impl

packagecom.nf.dao;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.SQLException;importjava.util.ArrayList;importjava.util.List;importcom.nf.entity.LoveInHeart;importcom.nf.util.MyPage;public class LoveInHeartDaoImpl extends BaseDao implementsLoveInHeartDao {

@Override //查询public ListgetAll(MyPage myPage) {//操作数据库，需要一个连接Connection

Connection connection =getConnection();

PreparedStatement prepareStatement= null;

ResultSet resultSet= null;

List loveInHeartList = newArrayList();

String sql_data= "select * from loveinheart order by lid desc ";

String sql_recodeCount= "select count(1) as mycount from loveinheart";

sql_data+= "limit ";

sql_data+= (myPage.getCurrentPage()-1)*myPage.getPerPageSize();

sql_data+= ",";

sql_data+=myPage.getPerPageSize();

System.out.println("分页的SQL:"+sql_data);try{

prepareStatement=connection.prepareStatement(sql_data);

resultSet=prepareStatement.executeQuery();while(resultSet.next()) {

LoveInHeart l= newLoveInHeart();

l.setLid(resultSet.getInt("lid"));

l.setLname(resultSet.getString("lname"));

l.setLsex(resultSet.getString("lsex"));

l.setLtime(resultSet.getDate("ltime"));

l.setLmoney(resultSet.getFloat("lmoney"));

loveInHeartList.add(l);

}

}catch(SQLException e) {

e.printStackTrace();

}try{

prepareStatement=connection.prepareStatement(sql_recodeCount);

resultSet=prepareStatement.executeQuery();

resultSet.next();

myPage.setRecodeCount( resultSet.getInt("mycount") );

}catch(SQLException e) {

e.printStackTrace();

}

closeAll(connection, prepareStatement, resultSet);//System.out.println(connection);

returnloveInHeartList;

}

@Override //添加public booleanadd(LoveInHeart love) {//操作数据库，需要一个连接Connection

Connection connection =getConnection();

PreparedStatement prepareStatement= null;try{

prepareStatement=connection

.prepareStatement("insert into LoveInHeart(lname,lsex,ltime,lmoney) values (?,?,?,?)");

prepareStatement.setString(1, love.getLname());

prepareStatement.setString(2, love.getLsex());

prepareStatement.setDate(3, love.getLtime());

prepareStatement.setFloat(4, love.getLmoney());

prepareStatement.executeUpdate();

closeAll(connection, prepareStatement,null);return true;

}catch(SQLException e) {

e.printStackTrace();return false;

}

}

@Override //删除public boolean delete(intid) {//操作数据库，需要一个连接Connection

Connection connection =getConnection();

PreparedStatement prepareStatement= null;try{

prepareStatement=connection

.prepareStatement("delete from LoveInHeart where lid=?");

prepareStatement.setInt(1, id);

prepareStatement.executeUpdate();

closeAll(connection, prepareStatement,null);return true;

}catch(SQLException e) {

e.printStackTrace();return false;

}

}

}

接下来在Service层再创建一个interface类，名字：LoveInHeartService

packagecom.nf.service;importjava.util.List;importcom.nf.entity.LoveInHeart;importcom.nf.entity.MyData;public interfaceLoveInHeartService {public MyData getAll(intcurrentPage);public booleanadd(LoveInHeart love);public boolean delete(intid);

}

随机在同Service层下实现该接口，实现类名：LoveInHeartServiceImpl，在这里实现类的方式直接调用dao层的方法就可以了

packagecom.nf.service;importjava.util.List;importcom.nf.dao.LoveInHeartDao;importcom.nf.dao.LoveInHeartDaoImpl;importcom.nf.entity.LoveInHeart;importcom.nf.entity.MyData;importcom.nf.util.MyPage;public class LoveInHeartServiceImpl implementsLoveInHeartService {private LoveInHeartDao loveDao = newLoveInHeartDaoImpl();

@Overridepublic MyData getAll(intcurrentPage) {

MyPage myPage= newMyPage();

myPage.setCurrentPage(currentPage);//myPage.setRecodeCount( 0 );//让Dao层来计算

myPage.setPerPageSize( 10 );//设置每页10条

List loveList =loveDao.getAll( myPage );

MyData myData= newMyData();

myData.setLoveInHeartList(loveList);

myData.setMyPage(myPage);returnmyData;

}

@Overridepublic booleanadd(LoveInHeart love) {returnloveDao.add(love);

}

@Overridepublic boolean delete(intid) {//TODO Auto-generated method stub

returnloveDao.delete(id);

}

}

接下来就是存放Servlet的action层，Servlet名：LoveInHeartActionGetAll(获取全部信息的Servlet)

packagecom.nf.web;importjava.io.IOException;importjava.util.List;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcom.nf.entity.LoveInHeart;importcom.nf.entity.MyData;importcom.nf.service.LoveInHeartService;importcom.nf.service.LoveInHeartServiceImpl;

@WebServlet("/LoveInHeartActionGetAll")public class LoveInHeartActionGetAll extendsHttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {//第一步：先获得客户端的参数

String currentPage_str = request.getParameter("currentPage");if (currentPage_str==null){

currentPage_str= "1";

}int currentPage =Integer.parseInt(currentPage_str);//第二步：调用Model(service层)的方法，来获取数据

LoveInHeartService loveService = newLoveInHeartServiceImpl();

MyData myData=loveService.getAll(currentPage);//第三步：把数据存放到request的属性中，然后把请求转发到jsp

request.setAttribute("myData", myData);

request.getRequestDispatcher("showList.jsp").forward(request, response);

}protected void doPost(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {

doGet(request, response);

}

}

再在同一层下创建添加的Servlet，Servlet名：LoveInHeartActionAdd

packagecom.nf.web;importjava.io.IOException;importjava.text.ParseException;importjava.text.SimpleDateFormat;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcom.nf.entity.LoveInHeart;importcom.nf.service.LoveInHeartService;importcom.nf.service.LoveInHeartServiceImpl;

@WebServlet("/LoveInHeartActionAdd")public class LoveInHeartActionAdd extendsHttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {//获取参数//?lname=123&lsex=男&ltime=111&lmoney=222

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

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

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

String lmoney= request.getParameter("lmoney");//把参数构造成一个实体类

LoveInHeart love = newLoveInHeart();

love.setLname(lname);

love.setLsex(lsex);

SimpleDateFormat sdf= new SimpleDateFormat("yyyy-MM-dd");try{

love.setLtime(newjava.sql.Date( sdf.parse(ltime).getTime() ) );

}catch(ParseException e) {

System.out.println("日期格式转换错误");

e.printStackTrace();

}

love.setLmoney( Float.parseFloat(lmoney) );//调用Model，保存数据

LoveInHeartService loveService = newLoveInHeartServiceImpl();boolean flag =loveService.add(love);//跳转

if(flag){//response.sendRedirect("showList.jsp");

response.sendRedirect("LoveInHeartActionGetAll");

}else{

response.sendRedirect("error.jsp");

}

}protected void doPost(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {

doGet(request, response);

}

}

删除的Servlet，Servlet名：LoveInHeartActionDelete

packagecom.nf.web;importjava.io.IOException;importjavax.servlet.ServletException;importjavax.servlet.annotation.WebServlet;importjavax.servlet.http.HttpServlet;importjavax.servlet.http.HttpServletRequest;importjavax.servlet.http.HttpServletResponse;importcom.nf.service.LoveInHeartService;importcom.nf.service.LoveInHeartServiceImpl;

@WebServlet("/LoveInHeartActionDelete")public class LoveInHeartActionDelete extendsHttpServlet {protected void doGet(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {//获取参数

String id_str = request.getParameter("id");int id =Integer.parseInt(id_str);//调用model的api

LoveInHeartService loveService = newLoveInHeartServiceImpl();boolean flag =loveService.delete(id);if(flag){

response.sendRedirect("LoveInHeartActionGetAll");

}else{

response.sendRedirect("error.jsp");

}

}protected void doPost(HttpServletRequest request, HttpServletResponse response) throwsServletException, IOException {

doGet(request, response);

}

}

添加JSP代码，此网页只有主体，无其它程序文件之类的引用，JSP名：add.jsp

中华慈善捐款等级

姓名：

性别：

男

女

捐款日期：

(yyyy年MM月dd日)

捐款金额：

(元)

首页的全部代码，这里只是用来做一个中转站，并不实现什么功能，所以可以只保留这一点东西，JSP名：index.jsp

最重要的展示信息界面，包括了删除功能，JSP名：showList.jsp

展示所有信息

functionmydelete(id){

alert(id);//ajax();

}

每页大小:${myData.myPage.perPageSize }，

一共${myData.myPage.pageCount}页

第${sta.index}页

---中华爱心捐款查询系统添加新捐款

编号姓名性别捐款金额捐款时间操作${love.lid}${love.lname}${love.lsex}${love.lmoney}${love.ltime}

删除 

无刷新删除1 

无刷新删除2

第${sta.index}页

最终结果图如下：