简单的三层结构到数据库例子

来源：互联网发布：淘宝装修市场编辑：程序博客网时间：2024/06/05 23:41

1.数据库的数据表

2.jsp页面的代码（没样式）addBookOrder.jsp

//把jsp页面的数据用from表单提到Servlet插入数据库

//用table获取显示从数据库的数据
<table class="table table-bordered table-striped table-hover">
<tbody>
<tr align="center">
<td nowrap="nowrap"><strong>书本编号</strong></td>
<td nowrap="nowrap"><strong>书本名称</strong></td>
<td nowrap="nowrap"><strong>书本数量</strong></td>
<td nowrap="nowrap"><strong>书本价格</strong></td>
<td nowrap="nowrap"><strong>书本总价</strong></td>
<td nowrap="nowrap"><strong>是否购买</strong></td>
<td width="80" nowrap="nowrap"><strong> 操作 </strong></td>
</tr>
//用EL
<c:forEach items="${pu.list}" var="ord">
<tr align="center">
<td nowrap="nowrap">${ord.id}</td>
<td nowrap="nowrap">${ord.boname}</td>
<td nowrap="nowrap">${ord.bocount}</td>
<td nowrap="nowrap">${ord.boprice}</td>
<td nowrap="nowrap">${ord.bosumprice}</td>
<td nowrap="nowrap">${ord.boisbuy}</td>
<td>
<a href='AddBookOrderServlet?tag=del&id=${ord.id}'
onclick='return confirm("你确定要删除 ${ord.boname} 吗")'>删除订单</a>
</td>
</tr>
</c:forEach>
</tbody>
//分页，用来获取数据库的信息，
<table class="margin-bottom-20 table no-border" >
<tr>
<td class="text-center">总记录数 ${pu.pageNumber} 条, 共 ${pu.countPage} 页,当前第 ${pu.indexPage} 页 </td>
<td class="text-center"><a href='AddBookOrderServlet'><span class="btn btn-info" style="width:80px;" >首页</span></a></td>
<td class="text-center"><span><a href="AddBookOrderServlet?indexPage=${pu.indexPage>1?pu.indexPage-1:1 }"><input type="button" value="上一页" class="btn btn-info" style="width:80px;" ></a></span></td>
<td class="text-center"><span><a href="AddBookOrderServlet?indexPage=${pu.indexPage<pu.countPage?pu.indexPage+1:pu.countPage }"><input type="button" value="下一页" class="btn btn-info" style="width:80px;" ></a></span></td>

<td class="text-center"><a href='AddBookOrderServlet?indexPage=${pu.countPage}'><span class="btn btn-info" style="width:80px;" > 尾页</span></a></td>
</tr>
</table>

3.实体类

package com.etc.entity;

public class BookOrder {

private int id;
private String boname;
private int bocount;
private int boprice;
private int bosumprice;
private int boisbuy;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getBoname() {
return boname;
}
public void setBoname(String boname) {
this.boname = boname;
}
public int getBocount() {
return bocount;
}
public void setBocount(int bocount) {
this.bocount = bocount;
}
public int getBoprice() {
return boprice;
}
public void setBoprice(int boprice) {
this.boprice = boprice;
}
public int getBosumprice() {
return bosumprice;
}
public void setBosumprice(int bosumprice) {
this.bosumprice = bosumprice;
}
public int getBoisbuy() {
return boisbuy;
}
public void setBoisbuy(int boisbuy) {
this.boisbuy = boisbuy;
}

}

4.servlet类

package com.etc.servlet;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.etc.biz.BookOrderBiz;
import com.etc.biz.impl.BookOrderBizImpl;
import com.etc.dao.BookOrderDao;
import com.etc.entity.BookOrder;
import com.etc.entity.PageUtil;

public class AddBookOrderServlet extends HttpServlet {

//get方法点击用get方法
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

request.setCharacterEncoding("utf-8");

//获取jsp的tag值
String tag = request.getParameter("tag");

//判断tag
if (tag != null && tag.equals("del")) {
int id = Integer.parseInt(request.getParameter("id"));
new BookOrderBizImpl().delete(id);

}
//分页 indexPage
int indexPage = 1;
if (request.getParameter("indexPage") != null) {
indexPage = Integer.parseInt(request.getParameter("indexPage"));
indexPage = indexPage <= 0 ? 1 : indexPage;
}

int Size = 5;
// 实现业务逻辑层接口
BookOrderBiz biz = new BookOrderBizImpl();
PageUtil pu = biz.getByPage(indexPage, Size);

request.setAttribute("pu", pu);

// 转发到编辑页面
request.getRequestDispatcher("addBookOrder.jsp").forward(request,
response);

}

//post方法表单，多个数据用post方法
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

String tag = request.getParameter("tag");
if (tag != null && tag.equals("add")) {
// int id = Integer.parseInt(request.getParameter("id"));
String boname = request.getParameter("boname");
int bocount = Integer.parseInt(request.getParameter("bocount"));
int boprice = Integer.parseInt(request.getParameter("boprice"));
// int bosumprice = Integer.parseInt(request.getParameter("bosumprice"));
int boisbuy = Integer.parseInt(request.getParameter("boisbuy"));


//创建实体对象
BookOrder b = new BookOrder();
// b.setId(id);
b.setBoname(boname);
b.setBocount(bocount);
b.setBoprice(boprice);
// b.setBosumprice(bosumprice);
b.setBoisbuy(boisbuy);

BookOrderBiz bob = new BookOrderBizImpl();
bob.addNewBook(b);

//分页
int indexPage = 1;
if (request.getParameter("indexPage") != null) {
indexPage = Integer.parseInt(request.getParameter("indexPage"));
indexPage = indexPage <= 0 ? 1 : indexPage;
}

int Size = 5;

PageUtil pu = bob.getByPage(indexPage, Size);

request.setAttribute("pu", pu);

// 转发到编辑页面
request.getRequestDispatcher("addBookOrder.jsp").forward(request,
response);
}

}
}

5.Impl业务逻辑层

package com.etc.biz.impl;

import com.etc.biz.BookOrderBiz;
import com.etc.dao.BookOrderDao;
import com.etc.dao.impl.BookOrderDaoImpl;
import com.etc.entity.BookOrder;
import com.etc.entity.PageUtil;

public class BookOrderBizImpl implements BookOrderBiz {

//调用数据访问类的方法，
BookOrderDao dao = new BookOrderDaoImpl();
@Override
public int addNewBook(BookOrder bookorder) {
return dao.addNewBook(bookorder);
}

//修改
@Override
public int alterOrder(BookOrder bookorder) {
return dao.alterOrder(bookorder);
}

//分页
@Override
public PageUtil getByPage(int indexPage, int Size) {

return dao.getByPage(indexPage,Size);
}
//删除
public int delete(int id) {
return dao.delete(id);

}
public BookOrder fingById(int id) {

return dao.fingById(id) ;
}

}

6.业务逻辑层接口

package com.etc.biz;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.etc.dao.BookOrderDao;
import com.etc.entity.BookOrder;
import com.etc.entity.PageUtil;

public interface BookOrderBiz {
/**
* 新书订购
*/
public int addNewBook(BookOrder bookorder);
/**
* 验收新书修改订单状态
*/
public int alterOrder(BookOrder bookorder);

/**
* 以分页方式查询新书订购列表的方法
*/
public PageUtil getByPage(int indexPage,int Size);
/**
* 删除
*
*/
public int delete(int id);
//获取id进行编辑修改
public BookOrder fingById(int id);

}

7.数据访问层接口实现类daoImpl

package com.etc.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.etc.dao.BaseDao;
import com.etc.dao.BookOrderDao;
import com.etc.entity.BookOrder;
import com.etc.entity.PageUtil;
/**
* 新书订购接口实现类
* @author 妖精
*从数据库获取数据的命令
*/
public class BookOrderDaoImpl implements BookOrderDao {

@Override
/**
* 总价自动触发器
*/

public int addNewBook(BookOrder bookorder) {
Connection con = BaseDao.getConnection();
String sql="INSERT INTO bookorder VALUE (NULL,?,?,?,?,?)";
return BaseDao.executeUpdate(sql, bookorder.getBoname(),
bookorder.getBocount(),bookorder.getBoprice(),
bookorder.getBocount()*bookorder.getBoprice(),bookorder.getBoisbuy());
}

@Override
/**
* 通过ID获取当前订购单信息
*/
//修改命令
public int alterOrder(BookOrder bookorder) {
String sql ="update bookorder set boname=?,bocount=?,boprice=?,bosumprice=?," +
"boisbuy=? where id=?";

return BaseDao.executeUpdate(sql, bookorder.getBoname(),bookorder.getBocount(),
bookorder.getBoprice(),bookorder.getBosumprice(),bookorder.getBoisbuy(), bookorder.getId());

}

//分页，获取当前页和每页最大值
@Override
public PageUtil<BookOrder> getByPage(int indexPage, int Size) {

// SQL 用于获取总记录数
String sql = "select count(*) from bookorder";
// SQL 用于取本页的数据
String sql1 = "select * from bookorder limit ?,?";
PageUtil<BookOrder> pu = new PageUtil<BookOrder>(); // 分页的工具类
// 本页的数据集合
List<BookOrder> list = new ArrayList<BookOrder>();
Connection conn = BaseDao.getConnection();
PreparedStatement st = null;
ResultSet rs = null;
try {
st = conn.prepareStatement(sql); // 执行查询总记录数
rs = st.executeQuery();
if (rs.next()) {
int count = rs.getInt(1);
pu.setPageNumber(count); // 封装总记录数
}
st = conn.prepareStatement(sql1); // 取本页的数据
st.setInt(1, (indexPage - 1) * Size); // 计算跳过记录数
st.setInt(2, Size);
rs = st.executeQuery(); // 执行查询
while (rs.next()) { // 循环封装数据
// 创建对象并给属性赋值
BookOrder bo = new BookOrder();
bo.setId(rs.getInt(1));
bo.setBoname(rs.getString(2));
bo.setBocount(rs.getInt(3));
bo.setBoprice(rs.getInt(4));
bo.setBosumprice(rs.getInt(5));
bo.setBoisbuy(rs.getInt(6));

list.add(bo);
}
pu.setList(list); // 封装数据集合
pu.setIndexPage(indexPage);
pu.setSize(Size);

// Math.ceil方法返回大于它的最小整数
int countPage = (int) Math.ceil(pu.getPageNumber()
/ (double) Size);
pu.setCountPage(countPage);

} catch (Exception e) {

} finally {
BaseDao.closeAll(conn, st, rs);
}
return pu; // 封装了分页所需的所有数据
}

//删除的命令
@Override
public int delete(int id) {
String sql = "delete from bookorder where id = ?";
return BaseDao.executeUpdate(sql, id);
}

//用id获取数据库相同ID的数据
@Override
public BookOrder fingById(int id) {
BookOrder bo = null;
// 取连接
Connection conn = BaseDao.getConnection();
Statement st = null;
ResultSet rs = null;
String sql = "select * from bookorder where id =" + id;
try {
st = conn.createStatement(); // 命令对象的创建
rs = st.executeQuery(sql); // 执行SQL命令获取结果集
if (rs.next()) { // 循环读取每一条数据
// 创建学员对象并给属性赋值
bo = new BookOrder();
bo.setId(rs.getInt(1));
bo.setBoname(rs.getString(2));
bo.setBocount(rs.getInt(3));
bo.setBoprice(rs.getInt(4));
bo.setBosumprice(rs.getInt(5));
bo.setBoisbuy(rs.getInt(6));

}
} catch (Exception e) {
} finally {
BaseDao.closeAll(conn, st, rs);
}
return bo; // 把集合返回
}

}

8.数据访问层接口

package com.etc.dao;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.etc.entity.BookOrder;
import com.etc.entity.PageUtil;

/**
* 新书订购
* @author 妖精
*
*/
public interface BookOrderDao {
/**
* 新书订购
*/
public int addNewBook(BookOrder bookorder);
/**
* 验收新书修改订单状态
*/
public int alterOrder(BookOrder bookorder);
//删除
public int delete(int id);
/**
* 分页用获得最大页数
*/
public int getMaxPage();
/**
* 以分页方式查询新书订购列表的方法
*/
public PageUtil getByPage(int indexPage,int Size);
//Id
public BookOrder fingById(int id);

}

9.web.xml

<servlet>
<servlet-name>AddBookOrderServlet</servlet-name>
<servlet-class>com.etc.servlet.AddBookOrderServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>AlterBookOrderServlet</servlet-name>
<servlet-class>com.etc.servlet.AlterBookOrderServlet</servlet-class>
</servlet>

<servlet-mapping>
<servlet-name>AddBookOrderServlet</servlet-name>
<url-pattern>/AddBookOrderServlet</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>AlterBookOrderServlet</servlet-name>
<url-pattern>/AlterBookOrderServlet</url-pattern>
</servlet-mapping>

10.图片

0 0