具备的测试工具: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=男<ime=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}页
最终结果图如下: