jsp+servlet+jdbc实现简单增删改查-蒲公英云

今天在编程的时候又重新往前面学习jsp+servlet+jdbc做了一个小案例，希望能够程序猿们一点小启发，下篇博客会介绍在这个编程中可能遇到的问题。首先我们开始今天的案例;

首先所需要工具，以及开发环境

eclipse+mysql5.1+navicat foe mysql 这些工具是基本开发都会用到的

开发环境 jdk1.8以上，其实都无所谓。能搭建好tomcat就行。

开始建web工程，整体结构如图一个实体处理的servlet 以及需要的试图jsp文件

user

user.java实体类数据库根据实体图建属性一致就行

public class User {
   private Integer id;
   private String name;
   private Integer age;
   private String tel;
   public Integer getId() {
       return id;
   }
   public void setId(Integer id) {
       this.id = id;
   }
   public String getName() {
       return name;
   }
   public void setName(String name) {
       this.name = name;
   }
   public Integer getAge() {
       return age;
   }
   public void setAge(Integer age) {
       this.age = age;
   }
   public String getTel() {
       return tel;
   }
   public void setTel(String tel) {
       this.tel = tel;
   }
   @Override
   public String toString() {
       return “User [name=” + name + “, age=” + age + “, tel=” + tel + “]”;
   }

}

工具类：一般在开发过程中都会用到就封装一个类

JdbcUtils.java

import java.sql.DriverManager;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;

public class JdbcUtils {
   public static Connection getConn() {
   String driver = “com.mysql.jdbc.Driver”;
   String url = “jdbc:mysql://localhost:3306/test_yuanping”;
   String username = “root”;
   String password = “root”;
   Connection conn = null;
   try {
   Class.forName(driver); //
   conn = (Connection) DriverManager.getConnection(url, username, password);
   } catch (ClassNotFoundException e) {
   e.printStackTrace();
   } catch (SQLException e) {
   e.printStackTrace();
   }
   return conn;
   }
}

之后就是增删改查的servlet 每个servlet都有标识

FindServlet

import java.io.IOException;

import java.sql.ResultSet;
import java.util.ArrayList;
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.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import com.yp.pojo.User;
import com.yp.utils.JdbcUtils;
@WebServlet(“/FindServlet”)
public class FindServlet extends HttpServlet {

   /*

   /
   private static final long serialVersionUID = 1L;

   @Override
   protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       try {

               Connection conn=JdbcUtils.getConn();
       String sql=”select from users”;
           Statement st=(Statement) conn.createStatement();
           ResultSet resultSet = st.executeQuery(sql);
           List<User> list = new ArrayList<User>();
           while(resultSet.next()) {
               User user=new User();
               user.setId(resultSet.getInt(“id”));
               user.setAge(resultSet.getInt(“age”));
               user.setName(resultSet.getString(“name”));
               user.setTel(resultSet.getString(“tel”));
               list.add(user);
           }
           req.setAttribute(“list”, list);
           resultSet.close();
           st.close();
       }catch (Exception e) {
           // TODO: handle exception
           e.printStackTrace();
       }
       req.getRequestDispatcher(“list.jsp”).forward(req, resp);


       }
   @Override
   protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       // TODO Auto-generated method stub
       super.doPost(req, resp);
   }
}

InsertServlet, DeleteServlet, UpdateServlet分别如下

import java.io.IOException;
import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.yp.utils.JdbcUtils;

/
Servlet implementation class InsertSerlet
/
@WebServlet(“/InsertServlet”)
public class InsertServlet extends HttpServlet {
   private static final long serialVersionUID = 1L;

   protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

           String name1 = new String(request.getParameter(“name”).getBytes(“iso-8859-1”),”utf-8”);
           System.out.println(name1);
           String tel=request.getParameter(“tel”);
           int age=Integer.valueOf(request.getParameter(“age”));
       try {

           Connection conn=JdbcUtils.getConn();
               String sql=”insert into users(name,age,tel) values(?,?,?)”;
               PreparedStatement ps=(PreparedStatement) conn.prepareStatement(sql);
               ps.setString(1, name1);
               ps.setInt(2, age);
               ps.setString(3, tel);
               int i=ps.executeUpdate();
               System.out.println(“success”+i);
               ps.close();
               conn.close();
           } catch (SQLException e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
           }

       response.sendRedirect(“FindServlet”);
   }

   /
   @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);
   }

}

import java.io.IOException;

import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import com.yp.utils.JdbcUtils;

/
Servlet implementation class DeleteServlet
/
@WebServlet(“/DeleteServlet”)
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;

   protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
           String ids=request.getParameter(“id”);
           int id=Integer.parseInt(ids);
       try {

           Connection conn=JdbcUtils.getConn();
           //预编译语句
           String sql=”delete from users where id=?”;
           //Statement st=(Statement) conn.createStatement();
           PreparedStatement st=(PreparedStatement) conn.prepareStatement(sql);
           st.setInt(1, id);
           st.execute();
           //关流
           st.close();
           conn.close();
           }
           catch (SQLException e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
           }



       response.sendRedirect(“FindServlet”);
   }

}

import java.io.IOException;

import java.sql.SQLException;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.yp.utils.JdbcUtils;

/
Servlet implementation class UpdateServlet
/
@WebServlet(“/UpdateServlet”)
public class UpdateServlet extends HttpServlet {
private static final long serialVersionUID = 1L;

   protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
           String tel=request.getParameter(“tel”);
           String name = new String(request.getParameter(“name”).getBytes(“iso-8859-1”),”utf-8”);
           System.out.println(tel);
           String ids=request.getParameter(“id”);
           int id=Integer.parseInt(ids);
           int age=Integer.valueOf(request.getParameter(“age”));
           System.out.println(id);
       try {

           Connection conn=JdbcUtils.getConn();
               String sql=”update users set tel=?,name=?,age=? where id=?”;
               PreparedStatement ps=(PreparedStatement) conn.prepareStatement(sql);

                   ps.setString(1, tel);

                   ps.setString(2, name);

                   ps.setInt(3, age);

                   ps.setInt(4, id);
               ps.executeUpdate();
               ps.close();
               conn.close();
           } catch (SQLException e) {
               // TODO Auto-generated catch block
               e.printStackTrace();
           }

       System.out.println(“update success”);
       response.sendRedirect(“FindServlet”);
   }

}

下面的SelectServlet是用来更改数据时表单回显用的

package com.yp.servlet;

import java.io.IOException;

import java.sql.ResultSet;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import com.yp.pojo.User;
import com.yp.utils.JdbcUtils;

/
Servlet implementation class SelectServlet
/
@WebServlet(“/SelectServlet”)
public class SelectServlet extends HttpServlet {
   private static final long serialVersionUID = 1L;

   protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
       int id=Integer.valueOf(request.getParameter(“id”));
       try {

               Connection conn=JdbcUtils.getConn();
       String sql=”select * from users where id=? “;
           PreparedStatement ps=(PreparedStatement) conn.prepareStatement(sql);
           ps.setInt(1, id);

           ResultSet resultSet = ps.executeQuery();
           User user=new User();
           while(resultSet.next()) {

               user.setId(resultSet.getInt(“id”));
               user.setAge(resultSet.getInt(“age”));
               user.setName(resultSet.getString(“name”));
               user.setTel(resultSet.getString(“tel”));
           }
           request.setAttribute(“user”, user);
           resultSet.close();
           ps.close();
           conn.close();
       }catch (Exception e) {
           // TODO: handle exception
           e.printStackTrace();
       }
       request.getRequestDispatcher(“update.jsp”).forward(request, response);

   }

}

jsp页面，index.jsp就是新增用的，list.jsp是操作界面，update.jsp是更新页面，以下是分别jsp页面代码

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>

<%@ page language=”java” contentType=”text/html; charset=utf-8”
   pageEncoding=”utf-8”%>
<%@page import=”java.util.List”%>
<%@page import=”com.yp.pojo.User”%>
<!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=ISO-8859-1”>
<title>Insert title here</title>
</head>
<body>
   <center>
   <h1>USER LIST</h1>
       <table cellpadding=”10” cellspacing=”0” border=”1”>
           <tr>
               <td>id</td>
               <td>name</td>
               <td>age</td>
               <td>tel</td>
               <td>操作</td>
           </tr>
           <%
           List<User> list=(List<User>)request.getAttribute(“list”);
       if (list == null || list.size() < 1) {
       out.print(“没有数据！”);
          } else {
       // 遍历图书集合中的数据
       for (User user : list) {
       %>
           <tr>
               <td><%=user.getId()%></td>
               <td><%=user.getName()%></td>
               <td><%=user.getAge()%></td>
               <td><%=user.getTel()%></td>
               <td>
               <a href=”DeleteServlet?id=<%=user.getId()%>”> 删除</a>
               <a href=”SelectServlet?id=<%=user.getId()%>”> 修改</a>
               </td>
           </tr>
           <%
}
}
%>
       </table>
   </center>
</body>
</html>

<%@ page language=”java” contentType=”text/html; charset=UTF-8”
pageEncoding=”UTF-8”%>
<%@page import=”com.yp.pojo.User”%>
<!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>Insert title here</title>
</head>
<body>
   <center>
   <h1>USER Update</h1>
           <%
           User user=(User)request.getAttribute(“user”);

           %>
       <form action=”UpdateServlet” method=”post”>
       <input type=”hidden” name=”id” value=”<%=user.getId()%>”>
           name:<input type=”text” name=”name” value=”<%=user.getName()%>”>
           <br><br>
           age:<input type=”text” name=”age” value=”<%=user.getAge()%>”>
           <br><br>
           tel:<input type=”text” name=”tel” value=”<%=user.getTel()%>”>
           <br>
           <input type=”submit” value=”update”>
       </form>

</body>
</html>

完成后效果图：

70 1 70 2 70 3