自定义mvc增删改查

目录

一、搭建自定义mvc框架环境

二、通用的增删改查

三、查询删除及重复表单提交问题

四、新增修改的前端实现 

---

一、搭建自定义mvc框架环境

自定义jar包

1.将框架打成jar包，然后导入新工程，并且把框架的依赖jar包导入进去。

 

 

 然后点finish就出现在你定义的那个位置那里了

• book:

package ps.entity;

public class Book {

	private int bid;
	private String bname;
	private float price;
	public int getBid() {
		return bid;
	}
	public void setBid(int bid) {
		this.bid = bid;
	}
	public String getBname() {
		return bname;
	}
	public void setBname(String bname) {
		this.bname = bname;
	}
	public float getPrice() {
		return price;
	}
	public void setPrice(float price) {
		this.price = price;
	}
	public Book(int bid, String bname, float price) {
		super();
		this.bid = bid;
		this.bname = bname;
		this.price = price;
	}
	public Book() {
		super();
	}
	@Override
	public String toString() {
		return "Book [bid=" + bid + ", bname=" + bname + ", price=" + price + "]";
	}
	
}

• bookdao

package ps.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ps.entity.Book;
import ps.util.BaseDao;
import ps.util.DBAccess;
import ps.util.PageBean;
import ps.util.StringUtils;

public class BookDao extends BaseDao<Book>{

	//查询
	public List<Book> list(Book book,PageBean pageBean) throws Exception{
		String sql = "select * from t_mvc_book where 1=1";
		String bname = book.getBname();
		if(StringUtils.isNotBlank(bname)) {
			sql += "and bname like '%"+bname+"%'";
		}
		int bid = book.getBid();
		//前台jsp传递到后台，只要传递就有值，每传就是默认值，默认值就是0
		if(bid!=0) {
			sql+=" and bid ="+bid;
		}
		return super.executeQuery(sql, pageBean, rs -> {
			List<Book> list = new ArrayList<>();
			try {
				while(rs.next()) {
					list.add(new Book(rs.getInt("bid"), rs.getString("bname"), rs.getFloat("price")));
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return list;
		});
	}
	
	//增
	public int add(Book book) throws Exception {
		Connection con = DBAccess.getConnection();
		String sql = "insert into t_mvc_book values(?,?,?)";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setObject(1, book.getBid());
		pst.setObject(2, book.getBname());
		pst.setObject(3, book.getPrice());
		return pst.executeUpdate();
	}
	
	//删
	public int del(Book book) throws Exception {
		Connection con = DBAccess.getConnection();
		String sql = "insert into t_mvc_book where bid=?";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setObject(1, book.getBid());
		return pst.executeUpdate();
	}
	
	//改
	public int edit(Book book) throws Exception {
		Connection con = DBAccess.getConnection();
		String sql = "update t_mvc_book set bname=?,price=? where bid = ?";
		PreparedStatement pst = con.prepareStatement(sql);
		pst.setObject(3, book.getBid());
		pst.setObject(1, book.getBname());
		pst.setObject(2, book.getPrice());
		return pst.executeUpdate();
	}
	
}

• bookDaoTest:

 然后自动生成该方法：

package ps.dao;

import static org.junit.Assert.*;

import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import ps.entity.Book;

public class BookDaoTest {

	private BookDao bookDao=new BookDao();
	@Before
	public void setUp() throws Exception {
	}

	@After
	public void tearDown() throws Exception {
	}

	@Test
	public void testList() {
		try {
			List<Book> list = bookDao.list(new Book(), null);
			for (Book book : list) {
				System.out.println(book);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Test
	public void testAdd() {
		fail("Not yet implemented");
	}

	@Test
	public void testDel() {
		fail("Not yet implemented");
	}

	@Test
	public void testEdit() {
		fail("Not yet implemented");
	}

}

查询结果： 

二、通用的增删改查

BaseDao:

package ps.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.lang.reflect.Field;


/**
 * 将来你不知道你要查询那张表，用泛型来代替
 * T代表的是实体类，可以是Book/User/Goods...
 * @author ps
 *
 * @date 2022年6月22日 上午
 */
public class BaseDao<T> {
	
//	public List<T> executeQuery(String sql,PageBean pageBean,CallBack<T> callBack) throws Exception{
//		/*
//		 *1.拿到数据库连接
//		 *2.拿到Preparestatement
//		 *3.执行SQL语句 
//		 */
//		//创建连接
//		Connection con = DBAccess.getConnection();// 重复代码1
//		//获取执行对象
//		PreparedStatement ps = con.prepareStatement(sql);// 重复代码2
//		//获取结果集
//		ResultSet rs = ps.executeQuery();// 重复代码3
//		//循环遍历结果集
		while(rs.next()) {
			list.add(new Book(rs.getInt("bid"), rs.getString("bname"), rs.getFloat("price")));
		}
//		//查询不同的表，必然要处理不同的结果集
//		//接口是调用方来实现
//		return callBack.foreach(rs);
//	}
	
	
	
	public List<T> executeQuery(String sql,PageBean pageBean,CallBack<T> callBack) throws Exception{
//		select * from tb_book where bname like '%o%';
		//这个查询不行，因为跟上面代码查询组装不起来
//		select count(*) from tb_book where bname like '%o%'
		//所以说为什么要这么变，因为不仅仅考虑写出来简单，你还要发现转变的问题
//		String countsql="select count(1) as n from ("+sql+") t";
					//但这个可以组装
		//从上面得到select count(1) as n from (select * from tb_book where bname like '%o%') t;
		//目的是为了得到总记录数-->得到总页数
//		select * from tb_book where bname like '%o%' limit 10,10;
		
		
		/*
		 *1.拿到数据库连接
		 *2.拿到Preparestatement
		 *3.执行SQL语句 
		 */
		//创建连接
		Connection con = null;// 重复代码1
		//获取执行对象
		PreparedStatement ps = null;// 重复代码2
		//获取结果集
		ResultSet rs = null;// 重复代码3
		//判断是否分页 并且判断是否为true
		if(pageBean != null && pageBean.isPagination()) {//说明要分页
			//拿到总记录数		拼接原始的mysql
			String countSQL = getcountSQL(sql);
			con = DBAccess.getConnection();// 重复代码1
			ps = con.prepareStatement(countSQL);// 重复代码2
			rs= ps.executeQuery();// 重复代码3
			//查询出来的总行数是不是单行单列啊，所以说不需要while,判断即可
			if(rs.next()) {
				//把我们查询出来的总记录数放在pageBean里面，pageBean它是包含了所有的分页的一些元素的实体类
				//当前实体类就包含了总记录数
				pageBean.setTotal(rs.getString("n"));
			}
			//拼接
			String pageSQL = pagePageSQL(sql,pageBean);
			con = DBAccess.getConnection();// 重复代码1
			ps = con.prepareStatement(pageSQL);// 重复代码2
			rs= ps.executeQuery();// 重复代码3
			
		}
		else {
			con = DBAccess.getConnection();// 重复代码1
			ps = con.prepareStatement(sql);// 重复代码2
			rs= ps.executeQuery();// 重复代码3
		}
		
		
		return callBack.foreach(rs);
	}

	/**
	 * 拼装第N页的数据的SQL
	 * @param sql
	 * @param pageBean
	 * @return
	 */
	private String pagePageSQL(String sql, PageBean pageBean) {
		//这里相当于limit 1,10 
	return sql+" limit "+pageBean.getStartIndex()+","+pageBean.getRows();
	}

	/**
	 * 拼装符合条件总记录数的SQL
	 * @param sql
	 * @return
	 */
	private String getcountSQL(String sql) {
//		select * from tb_book where bname like '%o%';
		//这个查询不行，因为跟上面代码查询组装不起来
//		select count(*) from tb_book where bname like '%o%'
		//所以说为什么要这么变，因为不仅仅考虑写出来简单，你还要发现转变的问题
//		String countsql="select count(1) as n from ("+sql+") t";
		//下面这行代码就解决了上面的问题，由原始SQL换成查询总行数的SQL
		//为什么不是*因为*代表的所以列，而写1代表的是一列，哪个性能更好，肯定是1
		return "select count(1) as n from ("+sql+") t";
	}

	public int executeUpdate(String sql,T t,String[] sttrs ) throws Exception {
		Connection con=DBAccess.getConnection();
		PreparedStatement ps = con.prepareStatement(sql);
	//	将t的摸一个属性对应的值加到pst对象中
		for (int i = 0; i < sttrs.length; i++) {
			Field f = t.getClass().getDeclaredField(sttrs[i]);
			f.setAccessible(true);
			ps.setObject(i+1, f.get(t));
		}
	//	ps.setObject(1, book.getBid());
	//	ps.setObject(2, book.getBname());
	//	ps.setObj ect(3, book.getPrice());
		return ps.executeUpdate();	
	}

}

三、查询删除及重复表单提交问题

<?xml version="1.0" encoding="UTF-8"?>
	<!--
		config标签:可以包含0~N个action标签
	-->
<config>
	
	<action path ="/book" type="com.ps.web.BookAction">
		<forward name="list" path="/bookList.jsp" redirect="false" />
		<forward name="toEdit" path="/bookEdit.jsp" redirect="false" />
		<forward name="tolist" path="/book.action?methodName=list" redirect="true" />
		
	</action>
	
	
</config>

四、新增修改的前端实现 

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@taglib prefix="p" uri="http://jsp.pengshuai.ps" %>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link
	href="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/4.5.0/css/bootstrap.css"
	rel="stylesheet">
<script
	src="https://cdn.bootcdn.net/ajax/libs/twitter-bootstrap/4.5.0/js/bootstrap.js"></script>
<title>书籍列表</title>
<style type="text/css">
.page-item input {
	padding: 0;
	width: 40px;
	height: 100%;
	text-align: center;
	margin: 0 6px;
}
 
.page-item input, .page-item b {
	line-height: 38px;
	float: left;
	font-weight: 400;
}
 
.page-item.go-input {
	margin: 0 10px;
}
</style>
</head>
<body>
	<form class="form-inline"
		action="${pageContext.request.contextPath }/book.action?methodName=list" method="post">
		<div class="form-group mb-2">
			<input type="text" class="form-control-plaintext" name="bname"
				placeholder="请输入书籍名称">
		</div>
		<button type="submit" class="btn btn-primary mb-2">查询</button>
		<a class="btn btn-primary mb-2" href="${pageContext.request.contextPath }/book.action?methodName=preEdit" >增加</a>
		
	</form>
 
	<table class="table table-striped">
		<thead>
			<tr>
				<th scope="col">书籍ID</th>
				<th scope="col">书籍名</th>
				<th scope="col">价格</th>
				<th scope="col">操作</th> 
			
			</tr>
		</thead>
		<tbody>
			<c:forEach items="${list}" var="b">
				<tr>
					<td>${b.bid}</td>
					<td>${b.bname}</td>
					<td>${b.price}</td>
					<td>
						<a href="${pageContext.request.contextPath }/book.action?methodName=preEdit&bid=${b.bid}" >修改</a>
						<a href="${pageContext.request.contextPath }/book.action?methodName=del&bid=${b.bid}" >删除</a>
					
					</td>
				</tr>
			</c:forEach>
		</tbody>
	</table>
	
<!-- 	http://localhost:8080/Y1_mvc_cru/book.action?methodName=list
 -->	
 	<p:page pageBean="${pageBean }"></p:page>
 </body>
</html>

效果：

好啦，今天的分享就到这里了，咱们下次再见！