xzc 实训jdbc总结
xzc实训jdbc总结
以一张表连接数据库实现增删改查和网页显示为例
建项目和准备工作
- 新建web Dynamic Web Project
- 将三个jar包粘贴到WebContact文件夹下Web-INF下的lib文件夹中
- MySQL8.1要装对应的jar包
jstl.jar
mysql-connector-java-5.1.41-bin.jar
standard.jar
一、分包(src中)
cn.ccnu.contorller (放servlet文件)
cn.ccnu.util 放DBHelper (连接数据库的底层操作)
cn.ccnu.pojo 放java类(把数据库的属性封装成类)
cn.ccnu.ccnu.dao 放IMemberDao.java(接口文件)
package cn.ccnu.dao;
import java.sql.SQLException;
import java.util.List;
import cn.ccnu.pojo.Member;
public interface IMemberDao {
int add(Member m) throws Exception; //add返回值是int
//add()函数传入一个打包好的Member类对象，插入数据库中
int delete(int level) throws Exception;
//delete() 给出主键，删除数据库对象
int update(Member m) throws Exception;
//update()给出一个打包好的对象，找到主键，并更新其它属性
List<Member> showAll() throws Exception;
//返回查询到的所有元素，用List存放
Member showOne(int id) throws Exception;
//给出主键，删除元素
}
cn.ccnu.service 放IMemberService.java(接口文件)
package cn.ccnu.service;
import java.util.List;
import cn.ccnu.pojo.Member;
public interface IMemberService {
int add(Member m) throws Exception; //add返回值是int
int delete(int level) throws Exception;
int update(Member m) throws Exception;
List<Member> showAll() throws Exception;
Member showOne(int id) throws Exception;
}
cn.ccnu.service.impl 放实现类MemberServiceImpl
jsp文件(WebContent中新建)
一张表要写如几个jsp：
showUser.jsp 接受参数list,显示所有
update.jsp 接受参数
(可以讲参数打包成类对象，然后再网页上接收用户的输入，传回servlet进行update)
registed.jsp 用户注册(插入数据)
login.jsp 用户输入信息并登陆
success.jsp
并不是非要按下面这个顺序写，只是按这个顺序写的话逻辑比较清晰，不会报错，调用的都是前面写好的包和方法，不会报错
第一步 在cn.ccnu.util中新建DBHelper.class
package cn.ccnu.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DBHelper {
private final static String DRIVER="com.mysql.jdbc.Driver";
private final static String URL="jdbc:mysql://localhost:3306/mydb";//mydb是你的数据库名
private final static String USER="xxx";//你自己数据库登录名
private final static String PASSWORD="xx";//你自己数据库的密码
//获取数据库连接
public static Connection getConnection() throws Exception{
//反射加载驱动
Class.forName(DRIVER);
//DriverManager获取数据库连接对象
Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
return conn;
}
//关闭数据库资源
public static void closeConn(ResultSet rs,PreparedStatement ps,Connection conn) throws Exception{
if (rs != null){ //如果调用的时候没有rs就不用关闭这个资源
rs.close();
}
if (ps != null){
ps.close();
}
if (conn != null){
conn.close();
}
}
}
第二步，在cn.ccnu.pojo中新建class，把数据库中表的属性变成类成员变量
- 属性写成private
- 然后右键sources 自动添加setter getter constructor方法
- 注意属性命名规范，首字母和第二个字母小写，驼峰命名
大致流程
做两张表：user
GoodsType
需要新建两个servlet
在此新的
一、GoodsTypeServlet上新建方法
showAll()
showAll：去了showAll.jsp 使用requset跳转传递list
showAll.jsp: 删除超链接：<a href="GoodsTypeServlet?action=delete">
修改超链接: <a href="GoodsTypeServlet?action=showOne?id=${对象名.id}">
添加超链接: <a href="add.jsp">
showOne()
对象名 = service.showOne(request.getParameter(id))
requset.setAttribute("页面对象名",对象名);
request跳转到update.jsp
update.jsp:
form表单的action=GoodsTypeServlet
<hidden name="action" value="update">
delete()
删除成功、失败(msg)
request.getRequestDispatcher("GoodsTypeServlet?action=showall");
add();
代码：调用service.add()
结果：添加成功：resopse("GoodsTypeServlet?action=showall")
添加失败：msg request. add.jsp
add.jsp的form action=GoodsTypeServlet?
<input type="hidden" name="action" value="update"/>
update();
那道页面的值，存到对象里
service.update(对象名)
结果两个页面： 修改成功：response("GoodsTypeServlet?action=showall");
修改失败：requset msg="失败" request->update.jsp
二、
新建一个UserServlet
里面写一个login()方法，
Login.jsp页面跳转到UserServlet，调用login()方法，hidden name="action" value="login"
form表单的action=UserServlet //表示执行servlet里面的代码
跳转的时候：
//response.sendRedirect("successful.jsp");
插入成功： response.getRedict重定向(showAll.jsp)//页面要带后缀
login()方法的
登录成功：response. GoodsTypeServlet的showAll（）方法
response.重定向("GoodsTypeServlet?action=showAll") //showAll不要双引号，传字符串
请求重定向相当于是服务器在地址栏上改了
登录失败： request 显示错误 只要有数据通过servlet传到页面，就要用请求转发
request.getRequestDispatcher("login.jsp")请求转发，地址栏上显示的是jsp
-----------------------
showAll.jsp //显示所有
update.jsp //显示更新(修改)的界面
login.jsp //登录界面
add.jsp //增加界面
第三步，写dao的接口和实现类
//老师的演示代码
package cn.ccnu.dao;
import java.util.List;
import cn.ccnu.pojo.ShippingInfo;
public interface IShippingInfoDao {
int add(ShippingInfo info) throws Exception;
int delete(int id) throws Exception;
int update(ShippingInfo info) throws Exception;
List<ShippingInfo> showAll() throws Exception;
ShippingInfo showOne(int id) throws Exception;
}
- int add(对象);
- int delete(主码的属性);
- int update(对象);
- List<对象> showAll();
- 对象 showOne(主码的属性);
package cn.ccnu.dao;
import java.util.List;
import cn.ccnu.pojo.GoodsType;
public interface IGoodsTypeDao {
int add(GoodsType g) throws Exception;
int delete(int id) throws Exception;
int update(GoodsType g) throws Exception;
List<GoodsType> showAll() throws Exception;
GoodsType showOne(int id) throws Exception;
}
dao的实现类
package cn.ccnu.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.ccnu.dao.IGoodsTypeDao;
import cn.ccnu.pojo.GoodsType;
import cn.ccnu.util.DBHelper;
public class GoodsTypeDaoImpl implements IGoodsTypeDao{
private Connection conn = null;
private PreparedStatement ps = null;
private String sql = null;
@Override
public int add(GoodsType g) throws Exception {
// TODO Auto-generated method stub
conn = DBHelper.getConnection();
sql = "insert into goods_type(Goods_type_id,Goods_type_name,Goods_type_level,Goods_type_prelevel) values(?,?,?,?)";
ps = conn.prepareStatement(sql);
ps.setInt(1, g.getId());
ps.setString(2, g.getName());
ps.setInt(3, g.getLevel());
ps.setInt(4, g.getPreLevel());
int row = ps.executeUpdate();
DBHelper.getConnection();
return row;
}
@Override
public int delete(int id) throws Exception {
// TODO Auto-generated method stub
conn = DBHelper.getConnection();
sql = "delete from goods_type where Goods_type_id = ?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
int row = ps.executeUpdate();
DBHelper.closeConn(null, ps, conn);
return row;
}
@Override
public int update(GoodsType g) throws Exception {
// TODO Auto-generated method stub
conn = DBHelper.getConnection();
sql = "update goods_type set Goods_type_name=?,Goods_type_level=?,Goods_Type_preLevel=? where Goods_type_id=?";
ps = conn.prepareStatement(sql);
ps.setString(1, g.getName());
ps.setInt(2, g.getLevel());
ps.setInt(3, g.getPreLevel());
ps.setInt(4, g.getId());
int row = ps.executeUpdate();
DBHelper.closeConn(null, ps, conn);
return row;
}
@Override
public List<GoodsType> showAll() throws Exception {
// TODO Auto-generated method stub
List<GoodsType> list = new ArrayList<GoodsType>();
conn = DBHelper.getConnection();
sql = "select * from goods_type";
ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while(rs.next())
{
GoodsType g = new GoodsType();
g.setId(rs.getInt("Goods_type_id"));
g.setName(rs.getString("Goods_type_name"));
g.setLevel(rs.getInt("Goods_type_level"));
g.setPreLevel(rs.getInt("Goods_type_preLevel"));
list.add(g);
}
DBHelper.closeConn(rs, ps, conn);
return list;
}
@Override
public GoodsType showOne(int id) throws Exception {
// TODO Auto-generated method stub
conn = DBHelper.getConnection();
sql = "select * from goods_type";
ps = conn.prepareStatement(sql);
GoodsType g = new GoodsType();
ResultSet rs = ps.executeQuery();
while(rs.next())
{
g.setId(rs.getInt("Goods_type_id"));
g.setName(rs.getString("Goods_type_name"));
g.setLevel(rs.getInt("Goods_type_level"));
g.setPreLevel(rs.getInt("Goods_type_preLevel"));
}
DBHelper.closeConn(rs, ps, conn);
return g;
}
}
service的接口
package cn.ccnu.service;
import java.util.List;
import cn.ccnu.pojo.GoodsType;
public interface IGoodsTypeService {
int add(GoodsType g) throws Exception;
int delete(int id) throws Exception;
int update(GoodsType g) throws Exception;
List<GoodsType> showAll() throws Exception;
GoodsType showOne(int id) throws Exception;
}
service的实现类
package cn.ccnu.service.impl;
import java.util.List;
import cn.ccnu.dao.IGoodsTypeDao;
import cn.ccnu.dao.impl.GoodsTypeDaoImpl;
import cn.ccnu.pojo.GoodsType;
import cn.ccnu.service.IGoodsTypeService;
public class GoodsTypeServiceImpl implements IGoodsTypeService{
private IGoodsTypeDao dao = new GoodsTypeDaoImpl();
@Override
public int add(GoodsType g) throws Exception {
// TODO Auto-generated method stub
return dao.add(g);
}
@Override
public int delete(int id) throws Exception {
// TODO Auto-generated method stub
return dao.delete(id);
}
@Override
public int update(GoodsType g) throws Exception {
// TODO Auto-generated method stub
return dao.update(g);
}
@Override
public List<GoodsType> showAll() throws Exception {
// TODO Auto-generated method stub
return dao.showAll();
}
@Override
public GoodsType showOne(int id) throws Exception {
// TODO Auto-generated method stub
return dao.showOne(id);
}
}
然后我们滚去写servlet
- 新建一个GoodsTypeServlet
- 然后doGet()调用doPost(),doPost()接受从jsp网页传来的参数action选择调用哪个方法
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
doPost(request, response);
}
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
String action = request.getParameter("action");
if(action.equals("showAll")){
showAll(request,response);
}
else if(action.equals("showOne")){
showOne(request,response);
}
else if(action.equals("add")){
add(request,response);
}
else if(action.equals("update")){
update(request,response);
}
else if(action.equals("delete")){
delete(request,response);
}
}
接口中声明的方法都是public abstract, 所以不需要写着两个修饰词。
阿里的java标准规定接口中的方法一律不加public 和abstract修饰词
下面是GoodsTypeServlet这个servlet文件的代码
package cn.ccnu.controller;
import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import cn.ccnu.pojo.GoodsType;
import cn.ccnu.service.IGoodsTypeService;
import cn.ccnu.service.impl.GoodsTypeServiceImpl;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/** * Servlet implementation class GoodsTypeServlet */
@WebServlet("/GoodsTypeServlet")
public class GoodsTypeServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private IGoodsTypeService service = new GoodsTypeServiceImpl();
/** * @see HttpServlet#HttpServlet() */
public GoodsTypeServlet() {
super();
// TODO Auto-generated constructor stub
}
/** * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response) */
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//response.getWriter().append("Served at: ").append(request.getContextPath());
doPost(request, response);
}
/** * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response) */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String action = request.getParameter("action");
if(action.equals("showAll")){
showAll(request,response);
}
else if(action.equals("showOne")){
showOne(request,response);
}
else if(action.equals("add")){
add(request,response);
}
else if(action.equals("update")){
update(request,response);
}
else if(action.equals("delete")){
delete(request,response);
}
}
protected void showAll(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
//showAll()不需要接收什么参数
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
List<GoodsType> list=null;
try {
list = service.showAll();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
request.setAttribute("list", list);
request.getRequestDispatcher("showAllGoodsType.jsp").forward(request, response);
}
protected void showOne(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// 接受来自showAll.jsp里面修改超链接的参数id,然后在数据库中查询这个元组，并将对象传给upate.jsp
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
int id = Integer.parseInt(request.getParameter("id"));//shipId是超链接?后的名字
GoodsType info = null;
try {
info = service.showOne(id);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
request.setAttribute("info", info);//传给网页的要更新的对象名
request.getRequestDispatcher("updateGoodsType.jsp").forward(request, response);
}
protected void add(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
int id = Integer.parseInt(request.getParameter("id"));
int row = 0;
String name = request.getParameter("name");
int level = Integer.parseInt(request.getParameter("level"));
int preLevel = Integer.parseInt(request.getParameter("preLevel"));
GoodsType g = new GoodsType(id,name,level,preLevel);
try {
row = service.add(g);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(row>0){
response.sendRedirect("GoodsTypeServlet?action=showAll");
}
else{
request.setAttribute("msg", "添加失败");
request.getRequestDispatcher("addGoodsType.jsp");
}
}
protected void update(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
int id = Integer.parseInt(request.getParameter("id"));
int row = 0;
String name = request.getParameter("name");
int level = Integer.parseInt(request.getParameter("level"));
int preLevel = Integer.parseInt(request.getParameter("preLevel"));
GoodsType g = new GoodsType(id,name,level,preLevel);
try {
row = service.update(g);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(row>0){
response.sendRedirect("GoodsTypeServlet?action=showAll");
}
else{
request.setAttribute("msg", "修改失败");
request.getRequestDispatcher("updateGoodsType.jsp");
}
}
protected void delete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
int id = Integer.parseInt(request.getParameter("id"));//shipId是超链接?后的名字
int row =0;
try{
row= service.delete(id);
}catch(Exception e){
e.printStackTrace();
}
if(row<=0){
request.setAttribute("msg", "删除失败");
}
request.getRequestDispatcher("GoodsTypeServlet?action=showAll").forward(request, response);
}
}
下面是几个jsp文件的代码
showAllGoodsType.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!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">
<title>产品类别表</title>
</head>
<body>
<div align="center">
<h1>产品类别表</h1>
</div>
<!-- c:forEach表示循环 items表示从这里取值 放在var中-->
<table border="1px solide" align="center">
<tr>
<td>产品类型编号</td>
<td>产品名称</td>
<td>产品级别</td>
<td>产品上级编号</td>
<td>操作</td>
</tr>
<c:forEach items="${list}" var="g">
<tr>
<td>${g.id }</td>
<td>${g.name }</td>
<td>${g.level}</td>
<td>${g.preLevel}</td>
<!-- a表签是超链接标签 默认使用get方式提交 -->
<td>
<a href="GoodsTypeServlet?id=${g.id}&action=delete">删除</a>
<a href="GoodsTypeServlet?id=${g.id}&action=showOne">修改</a>
</td>
</tr>
</c:forEach>
</table>
<div align="center">
<h2>
<a href="addGoodsType.jsp">添加新的商品类别</a>
</h2>
</div>
</body>
</html>
addGoodsType.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!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">
<title>添加商品种类信息</title>
</head>
<body>
<h1>添加商品种类信息</h1>
<form action="GoodsTypeServlet" method="post">
<table>
<tr>
<td>物品类型编号</td>
<td><input type="text" name="id" value="${info.id}" /></td>
</tr>
<tr>
<td>物品类别名称</td>
<td><input type="text" name="name" value="${info.name}" /></td>
</tr>
<tr>
<td>物品类别级别</td>
<td><input type="text" name="level" value="${info.level}" /></td>
</tr>
<tr>
<td>物品类别上级编号</td>
<td><input type="text" name="preLevel" value="${info.preLevel}" /></td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="提交添加申请"/>
<input type="reset" value = "重置"/>
</td>
</tr>
</table>
<input type="hidden" name="action" value="add"/>
</form>
</body>
</html>
updateGoodsType.jsp
<%@ page language="java" contentType="text/html; charset=utf-8"
pageEncoding="utf-8"%>
<!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">
<title>修改物品类别信息</title>
</head>
<body>
<h1 style="color:red;">${msg }</h1>
<form action="GoodsTypeServlet" method="post">
<!-- info是传进来的一个class GoodsType的对象 -->
<table>
<tr>
<td>物品类型编号</td>
<td><input type="text" name="id" value="${info.id}" readonly="readonly"/></td>
</tr>
<tr>
<td>物品类别名称</td>
<td><input type="text" name="name" value="${info.name}" /></td>
</tr>
<tr>
<td>物品类别级别</td>
<td><input type="text" name="level" value="${info.level}" /></td>
</tr>
<tr>
<td>物品类别上级编号</td>
<td><input type="text" name="preLevel" value="${info.preLevel}" /></td>
</tr>
</table>
<input type="hidden" name="action" value="update"/>
<input type="submit" value="提交修改"/>
</form>
</body>
</html>
几个操作：
1. showAll(显示所有表的信息)
- 先跳转到servlet数据库里去查，然后返回一个List,传递给showAll.jsp
2. update(更新数据)
- 先去servlet里去showOne()，(其实应该叫做getOne)，查找对应的元组，然后用对象打包返回，去update.jsp
- update.jsp接收用户的修改后的信息，然后跳到servlet，调用update()方法 (参数靠input传递) action传递信息以供servlet选择调用哪个方法
- update()完了之后有showAll()
3.delete() 在页面上点击，删除元素
- 在showAll.jsp页面上面每个元素都有删除的超链接
- 用链接传递主键的信息，传给servlet调用delete()
- 链接传参是?变量名=字符串
4.add 插入数据
- 从showAll.jsp点击超链接，可以跳转到add.jsp
- 输入信息，form表单传递input的信息给servlet调用add()方法插入
- 插入后接着showAll()
