jsp+servlet+jdbc实现对数据库的增删

一、JSP和Servlet的简单介绍

  • Servlet和JSP简介:
    Java开发Web应用程序时用到的技术主要有两种,即Servlet和JSP,Servlet是在服务器端执行的Java程序,一个被称为Servlet容器的程序(其实就是服务器) 负责执行Java程序,而JSP(Java Server Page)则是一个页面, 由JSP容器负责执行.

  • Servlet和JSP的区别:
    Servlet以Java程序为主, 输出HTML代码时需要使用out.println函数,也就是说Java中内嵌HTML; 而JSP则以HTML页面为主,需要写Java代码时则在页面中直接插入Java代码, 即HTML中内嵌Java.

  • 数据库内容可以点击右边的链接实现:点击查看数据库内容

二、实现增加和删除

  • (1)创建对象类:Author 类。作用是用于实例化对象,接收参数和传递参数
package com.entity;

public class Author {
	private int id;
	private String name;
	private int price ;
	private int num;
	private String dates;
	private String style;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getPrice() {
		return price;
	}
	public void setPrice(int price) {
		this.price = price;
	}
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getDates() {
		return dates;
	}
	public void setDates(String dates) {
		this.dates = dates;
	}
	public String getStyle() {
		return style;
	}
	public void setStyle(String style) {
		this.style = style;
	}

}

  • (2)Servlet类:AuthorServlet类。作用是将具体的功能和用户层连接。
package com.servlet;

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

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 com.dao.AuthorDao;
import com.entity.Author;

/**
 * Servlet implementation class AuthorServlet
 */
@WebServlet("/AuthorServlet")
public class AuthorServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public AuthorServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	@SuppressWarnings("unused")
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		//与Dao层连接
		 request.setCharacterEncoding("utf-8");
		 String op = request.getParameter("op");
		 if(op.equals("add"))
		 {
			 //用户新增
			 String name = request.getParameter("name");
			 int price = Integer.valueOf(request.getParameter("price"));
			 int num = Integer.valueOf(request.getParameter("num"));
			 String dates = request.getParameter("dates");
			 String style  = request.getParameter("style");
		
		
			 Author obj = new Author();
			 obj.setName(name);
			 obj.setPrice(price);
			 obj.setNum(num);
			 obj.setDates(dates);
			 obj.setStyle(style);
			 
			 AuthorDao ad = new AuthorDao();
			 
			 ad.add(obj);
			 
			 response.sendRedirect("AuthorServlet?op=query");
			 
		 }
		 //删除用户
		 else if(op.equals("del")) 
		 {
			 int id = Integer.valueOf(request.getParameter("id"));
			 AuthorDao ad = new AuthorDao();
			 ad.del(id);
			 response.sendRedirect("AuthorServlet?op=query");
		 }
		 
		 else if(op.equals("query")) {
			 //用户列表查询
			AuthorDao ad = new AuthorDao();
			List<Author> list = ad.queryAuthorList();
			request.setAttribute("authorList", list);
			request.getRequestDispatcher("userlist.jsp").forward(request, response);	 
			 
		 }
		 
		}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(request, response);
	}

}

  • (3)Dao层:AuthorDao类。作用是实现具体的增加和删除功能。
package com.dao;

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

import com.entity.Author;

public class AuthorDao {
	
	 public  Author check(String username ,int  password ) {
		 
		 Author obj = null ;
		 try {
			DBConnection db = new DBConnection();
			//获取数据库连接
			Connection conn = db.getConn();
			
			String sql="select *from furnitures where name = ? and id = ?";
			
			PreparedStatement ps=conn.prepareStatement(sql);
			//设置用户名和密码作为参数放入sql语句
			ps.setString(1,username);
			ps.setInt(2,password);
			//执行查询语句
			ResultSet rs = ps.executeQuery();
			//用户名和密码正确,查到数据  欧式风格  茶几
			if(rs.next()) {
				obj = new Author();
				obj.setId(rs.getInt(1));
				obj.setName(rs.getString(2));
				obj.setPrice(rs.getInt(3));
				obj.setNum(rs.getInt(4));
				obj.setDates(rs.getString(5));
				obj.setStyle(rs.getString(6));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return obj;
	 }
	 /**
	  * 用户列表信息查询
	  * @return
	  */
	 public List<Author> queryAuthorList(){
		 Author obj = null ;
		 List<Author> list = new ArrayList<Author>();
		 try {
			DBConnection db = new DBConnection();
			//获取数据库连接
			Connection conn = db.getConn();
			
			String sql="select *from furnitures";
			
			PreparedStatement ps=conn.prepareStatement(sql);
	
			//执行查询语句
			ResultSet rs = ps.executeQuery();
			//用户名和密码正确,查到数据  欧式风格  茶几
			//循环遍历获取用户信息
			while(rs.next()) {
				obj = new Author();
				obj.setId(rs.getInt(1));
				obj.setName(rs.getString(2));
				obj.setPrice(rs.getInt(3));
				obj.setNum(rs.getInt(4));
				obj.setDates(rs.getString(5));
				obj.setStyle(rs.getString(6));
				//将对象加入list里边
				list.add(obj);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 return list;
	 }
	 /**
	  * 用户新增
	  * @param obj
	  */
	 public void add(Author obj) {
		
		try {
			
			DBConnection db = new DBConnection();
			//获取数据库连接
			Connection conn = db.getConn();
			
			String sql="insert into furnitures values(id,?,?,?,?,?)";
			
			PreparedStatement ps=conn.prepareStatement(sql);
			ps.setObject(1, obj.getName());
			ps.setObject(2, obj.getPrice());
			ps.setObject(3, obj.getNum());
			ps.setObject(4,obj.getDates());
			ps.setObject(5, obj.getStyle());
			
			//执行sql语句
		   ps.execute();
		   
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
			
	 }
	 //删除用户
	 public void del(int id) {
		 try {
				
				DBConnection db = new DBConnection();
				//获取数据库连接
				Connection conn = db.getConn();
				
				String sql="delete from furnitures where id = ?";
				
				PreparedStatement ps=conn.prepareStatement(sql);
				
				ps.setObject(1, id);
				
				//执行sql语句
			   ps.execute();
			   
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
				
	 }
	
}

  • (4)数据库驱动Dao层:作用是方便获取数据库连接。
package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnection {
	
	private static String username="root";
	private static String password="root";
	private static String diver = "com.mysql.jdbc.Driver";
	private static String url="jdbc:mysql://localhost:3306/java12";
	
	
	private Connection conn;
	static {
		try {
			//加载驱动,捕获异常
			Class.forName(diver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public DBConnection() throws SQLException {
		conn = DriverManager.getConnection(url,username,password);
		
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}
	
	public static void main(String[] args) throws SQLException {
		DBConnection db = new DBConnection();
		Connection conn = db.getConn();
		
		//定义sql语句
		String sql = "select * from furnitures";
		//编译sql语句
		PreparedStatement ps = conn.prepareStatement(sql);
		//执行sql语句
		ResultSet rs = ps.executeQuery();
		
		while(rs.next()) {
			System.out.println(rs.getInt(1));
		}
		rs.close();
		ps.close();
		conn.close();
	}
	
}

三、jsp页面

  • (1)用户添加页面:主要用于数据库的增加功能,添加新的数据。
<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
</head>
<body>
<form action="AuthorServlet" method="post">
        名称:<input name ="name" type="text" size="20" value=""/><br/>
        价格:<input name ="price" type="text" size="20" value=""/><br/>
        数量:<input name ="num" type="text" size="20" value=""/><br/>
        日期:<input name ="dates" type="text" size="20" value=""/><br/>   
        风格:<input name ="style" type="text" size="20" value=""/><br/>
    <input type="hidden" name="op" value="add"/>
    <input type="submit" value ="提交"/> 
</form>
</body>
</html>

显示的页面如下图:输入所要添加的内容,点击提交。完成添加,同时抓到(2)的页面,可以进行删除操作。
在这里插入图片描述

  • (2)查询结果显示页面,同时可以进行删除操作。点击操作栏的删除按钮就可以删除相应的行。
<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>查询页面</title>
</head>
<body>
<table border="1">
  <tr>
    <td>编号</td>
    <td>名称</td>
    <td>价格</td>
    <td>数量</td>
    <td>日期</td>
    <td>风格</td>
    <td>操作</td>
  </tr>
  <c:forEach items="${authorList }" var="author">
  <tr>
    <td>${author.id}</td>
    <td>${author.name }</td>
    <td>${author.price }</td>
    <td>${author.num }</td>
    <td>${author.dates}</td>
    <td>${author.style}</td>
    <td><a href="AuthorServlet?op=del&id=${author.id}">删除</a></td>
  </tr>
  </c:forEach>
</table>
</body>
</html>

显示的页面如下图:点击操作栏的删除操作,就可以实现删除了。具体的实现是的显示见(3)(4)。
在这里插入图片描述

  • (3)添加数据的页面如下:
    a.填入数据点击提交
    在这里插入图片描述
    b.信息显示页面
    在这里插入图片描述
  • (4)删除页面和操作:点击删除,删除新增的数据,显示如下:
    在这里插入图片描述
  • 5
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值