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>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            <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()
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页