JDBC+Servlet+jsp(增删查改)

先在mysql新增数据库和表先,把下面的几句代码复制去到mysql运行就可以创建成功了!

 创建数据库

create database jdbc01 character set utf8 collate utf8_general_ci;

创建表:

use  jdbc01;

create table users(

       id int primary key auto_increment,

       name varchar(40),

       password varchar(40),

       email varchar(60),

       birthday date,

      sex boolean

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

  运行的效果:

 一、创建MVC架构的Web项目

与mysql 需要的包是

 

项目所需要的包
序号	包名	描述	所属层次
1	zhu.jdbc.domain	相当于数据库的某张表 (只包含简单的属性以及属性对应的get和set方法，不包含具体的业务处理方法)，提供给【数据访问层】、【业务处理层】、【Web层】来使用	domain(域模型)层
2	zhu.jdbc.dao	存放访问数据库的操作接口类	数据访问层
3	zhu.jdbc.dao.imp	存放访问数据库的操作接口的实现类
4	zhu.jdbc.service	存放处理系统业务接口类	业务处理层
5	zhu.jdbc.service.imp	存放处理系统业务接口的实现类
6	zhu.jdbc.command	相当于数据库命令的  增删查改	相当于三层架构的(DB层)
7	zhu.jdbc.unit	存放系统的通用工具类，提供给【数据访问层】、【业务处理层】、【DB层】来使用
8	zhu.jdbc.servlet		Web层(相当于界面层)

创建好的项目如下图(图-1)所示:

图-1

二、代码的编写

1. zhu.jdbc.unit的包编写

在zhu.jdbc.unit包下创建一个UnitMysql的类(这个类是建立与mysql的链接)

UnitMysql代码如下：

 package zhu.jdbc.unit;

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

 public class UnitMysql {
       public static final  String URL="jdbc:mysql://localhost:3306/jdbc01";//链接的mysql
         public static final String NAME = "root";
         public static final String PASSWORD = "root";
          public static final String DREIVER = "com.mysql.jdbc.Driver"; 

          static {
             try {
                 //加载驱动器
                 Class.forName(DREIVER);

                 /*//还有另一种方法,但是会加载驱动二次,通常不使用该方法
                 try {
                     DriverManager.registerDriver(new com.mysql.jdbc.Driver());
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }*/
             } catch (ClassNotFoundException e) {
                 e.printStackTrace();
             }
         }

         public static Connection getConnection() {
             try {
                 return  DriverManager.getConnection(URL, NAME, PASSWORD);//创建与数据库的链接
             } catch (SQLException e) {
                 e.printStackTrace();
             }
             return null;
         }
 //设置一个公共的关闭链接、释放资源的方法    .   因为每次只要进行了增,删,查,改 之后 都必须要关闭事件,  那么就设置一个公共的方法
         //而关闭资源要从 ResultSet先关闭-->,再到 PreparedStatement-->,最后到 Connection关闭
         public static void Close(ResultSet rs, PreparedStatement ps, Connection conn) {
             if (rs != null) {
                 try {
                     rs.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }

             if (ps != null) {
                 try {
                     ps.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }

             if (conn != null) {
                 try {
                     conn.close();
                 } catch (SQLException e) {
                     e.printStackTrace();
                 }
             }
         }
 }

2. 相当于三层架构的(DB层)

在zhu.jdbc.command包下创建一个IDaoCommand的类(这个类是建立与mysql的链接)

IDaoCommand代码如下：

 package zhu.jdbc.command;

 import java.util.List;

 public interface IDaoCommand<T> {
     //查询所有的数据
     public List<T> queryAllData();
     //新增数据
      public int insertData(T t);
      //修改数据
      public int update(T t);
      //删除数据
      public int delete(int id);
      //查询一条数据通过ID
      public T queryDataById(int id);
 }

3.      domain(域模型)层

在zhu.jdbc.domain 包下创建一个Tb_User 类

Tb_User 代码如下：

 package zhu.jdbc.domain;

 import java.sql.Date;
  //这里相当于数据库的某张表
 public class Tb_User {
     private int id;
     private String name;
     private String password;
     private String email;
     private Date birthday;
     private boolean sex;

     public boolean isSex() {
         return sex;
     }
     public void setSex(boolean sex) {
         this.sex = sex;
     }
     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 String getPassword() {
         return password;
     }
     public void setPassword(String password) {
         this.password = password;
     }
     public String getEmail() {
         return email;
     }
     public void setEmail(String email) {
         this.email = email;
     }
     public Date getBirthday() {
         return birthday;
     }
     public void setBirthday(Date birthday) {
         this.birthday = birthday;
     }
 }

4.开发数据访问层(dao、dao.impl)

2.1： dao 继承 command的命令

在zhu.jdbc.dao包下创建一个ITb_User接口类，对于开发接口类，我习惯以字母I作类的前缀.

ITb_User代码如下：

 package zhu.jdbc.dao;

 import zhu.jdbc.command.IDaoCommand;
 import zhu.jdbc.domain.Tb_User;

 /**
  * 这里这个类 是为了 ,后续 添加自己需要的方法. 如:模糊查询, 分页查询....
  * 这个必须要继承ICommand类,那样就可以调用增删查改的方法了
  * @author Xiao_Zhu
  *
  */
 public interface ITb_User extends IDaoCommand<Tb_User> {
    //自己需要的方法
 }

2.2： dao.ImpI 实现 dao

在zhu.jdbc.dao.imp包下创建一个ITb_UserImpI类

ITb_UserImpI代码如下：

 package zhu.jdbc.dao.imp;

 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 zhu.jdbc.dao.ITb_User;
 import zhu.jdbc.domain.Tb_User;
 import zhu.jdbc.unit.UnitMysql;

 /**
  * 实现 ITb_User类
  *
  * @author Xiao_Zhu
  *
  */
 public class ITb_UserImpI implements ITb_User {
     public Connection conn1 = null;
     public ResultSet rs = null;
     public PreparedStatement ps = null;

     // 查询所有的数据
     @Override
     public List<Tb_User> queryAllData() {
         conn1 = UnitMysql.getConnection();// 链接数据库
         List<Tb_User> list = new ArrayList<Tb_User>();
         try {
             String sqlSelect = "select * from users "; // 查询多条数据
             ps = conn1.prepareStatement(sqlSelect);
             rs = ps.executeQuery();
             Tb_User user = null;
             while (rs.next()) {
                 user = new Tb_User();
                 user.setId(rs.getInt("id"));
                 user.setName(rs.getString("name"));
                 user.setPassword(rs.getString("password"));
                 user.setBirthday(rs.getDate("birthday"));
                 user.setSex(rs.getBoolean("sex"));
                 list.add(user);
             }
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             UnitMysql.Close(rs, ps, conn1);
         }
         return list;
     }

     // 新增
     @Override
     public int insertData(Tb_User t) {
         conn1 = UnitMysql.getConnection();
         int i = 0;
         try {
             String sqlInsert = "insert into users(name,password,email,birthday,sex) values(?,?,?,?,?) ;";
             ps = conn1.prepareStatement(sqlInsert,
                     PreparedStatement.RETURN_GENERATED_KEYS);
             // 这里的1,2..必须要按上面的新增的顺序来定义
             ps.setString(1, t.getName());
             ps.setString(2, t.getPassword());
             ps.setString(3, t.getEmail());
             ps.setDate(4, new java.sql.Date(t.getBirthday().getTime()));
             ps.setBoolean(5, t.isSex());
             ps.executeUpdate();
             rs = ps.getGeneratedKeys();// 得到 最新的 ID
             if (rs.next()) {// 是否存在
                 i = rs.getInt(1);
             }
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             UnitMysql.Close(rs, ps, conn1);
         }
         return i;
     }

     // 修改
     @Override
     public int update(Tb_User t) {
         conn1 = UnitMysql.getConnection();
         int i = 0;
         try {
             String sqlUpdate = "update users set name=?, password =? ,sex=?  where id=?";
             ps = conn1.prepareStatement(sqlUpdate);
             ps.setString(1, t.getName());
             ps.setString(2, t.getPassword());
             ps.setBoolean(3, t.isSex());
             ps.setInt(4, t.getId());
             i = ps.executeUpdate();
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             UnitMysql.Close(null, ps, conn1);
         }
         return i;
     }

     // 删除
     @Override
     public int delete(int id) {
         conn1 = UnitMysql.getConnection();
         int i = 0;
         try {
             String sqlDelete = "delete from users where id=?";
             ps = conn1.prepareStatement(sqlDelete);
             ps.setInt(1, id);
             i = ps.executeUpdate();
             if (i == 1) {
                 return i;
             }
         } catch (SQLException e) {
             e.printStackTrace();
         } finally {
             UnitMysql.Close(null, ps, conn1);
         }
         return i;
     }

     // 查询一条数据通过ID
     @Override
     public Tb_User queryDataById(int id) {
         conn1 = UnitMysql.getConnection();
         String sql = "select * from users where id=?";
         Tb_User user = null;
         if (id > 0) {
             try {
                 ps = conn1.prepareStatement(sql);
                 ps.setInt(1, id);
                 rs = ps.executeQuery();
                 if (rs.next()) {
                     user = new Tb_User();
                     user.setId(rs.getInt("id"));
                     user.setName(rs.getString("name"));
                     user.setPassword(rs.getString("password"));
                     user.setBirthday(rs.getDate("birthday"));
                     user.setSex(rs.getBoolean("sex"));

                 }
             } catch (SQLException e) {
                 e.printStackTrace();
             } finally {
                 UnitMysql.Close(null, ps, conn1);
             }
         }
         return user;
     }

 }

5.    业务处理层(service,service.imp)

1. .service层

在zhu.jdbc.service包下创建一个ITb_UserService 类

ITb_UserService 如下:

 package zhu.jdbc.service;

 import zhu.jdbc.command.IServiceCommand;
 import zhu.jdbc.domain.Tb_User;

 public interface ITb_UserService extends IServiceCommand<Tb_User>  {
    //这里与dao层的中的ITb_User.java是一样的意思
 }

1.1 这里继承的 IServiceCommand  类是在zhu.jdbc.command下 创建一个IServiceCommand的类

代码IServiceCommand  如下:

 package zhu.jdbc.command;

 import java.util.List;

 public interface IServiceCommand<T> {
       //查询所有的数据
         public List<T> queryAllData();
         //新增数据
          public boolean insertData(T t);
          //修改数据
          public boolean update(T t);
          //删除数据
          public boolean delete(int id);
          //查询一条数据通过ID
          public T queryDataById(int id);
 }

2.     service.imp层

//这里的 service层要想与 dao层(BAL层逻辑层)建立联系那么必须要 创建  dao层的对象  

在zhu.jdbc.service.imp包下创建一个ITb_UserServiceImpI 类

ITb_UserServiceImpI 代码如下:

 package zhu.jdbc.service.imp;

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

 import zhu.jdbc.dao.ITb_User;
 import zhu.jdbc.dao.imp.ITb_UserImpI;
 import zhu.jdbc.domain.Tb_User;
 import zhu.jdbc.service.ITb_UserService;
 /**
  * 链接与dao层的链接
  * @author Xiao_Zhu
  *
  */
 public class ITb_UserServiceImpI  implements ITb_UserService{
     public Connection conn1 = null;
     public ResultSet rs = null;
     public PreparedStatement ps = null;
     boolean b=false;
     //这里的 service层要想玉 dao层(BAL层逻辑层)建立联系那么必须要 创建  dao层的对象
    ITb_User  myiTb_User=new ITb_UserImpI();//创建了 dao层的ITb_UserImp对象 

     //查询所有数据
     @Override
     public List<Tb_User> queryAllData() {

         return myiTb_User.queryAllData();
     }

     //新增
     @Override
     public boolean insertData(Tb_User t) {
         if (t!=null) {  myiTb_User.insertData(t);
                  b=true;
 }
          return b;
     }

     //修改
     @Override
     public boolean update(Tb_User t) {
         if (t!=null) {
              myiTb_User.update(t);
                  b=true;
 }
          return b;
     }
     //删除
     @Override
     public boolean delete(int id) {
         if (id!=0) {
                  myiTb_User.delete(id);
                  b=true;
         }
          return b;
     }
    //查询一条数据
     @Override
     public Tb_User queryDataById(int id) {
          if (id!=0) {
         return myiTb_User.queryDataById(id);
         }
          else {
             return null;
         }
     }

 }

6.   Web层(相当于界面层)

在zhu.jdbc.servlet包下创建一个Servlet_TbUser 类

创建的Servlet_TbUser类要在web配置

web的配置内容如下:

   <!--Servlet_TbUser.java的配置-->
     <servlet>
     <servlet-name>Servlet_TbUser</servlet-name>
     <servlet-class>zhu.jdbc.servlet.Servlet_TbUser</servlet-class>
   </servlet>
   <servlet-mapping>
     <servlet-name>Servlet_TbUser</servlet-name>
     <url-pattern>/zhu/Servlet_TbUser</url-pattern>
   </servlet-mapping>

Servlet_TbUser  代码如下:

 package zhu.jdbc.servlet;

 import java.io.IOException;
 import java.sql.Date;
 import java.text.ParseException;
 import java.text.SimpleDateFormat;
 import java.util.List;

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

 import zhu.jdbc.domain.Tb_User;
 import zhu.jdbc.service.ITb_UserService;
 import zhu.jdbc.service.imp.ITb_UserServiceImpI;

 public class Servlet_TbUser extends HttpServlet {

     /**
      *
      */
     private static final long serialVersionUID = 1L;

      //这里建立 与service层的  联系  创建一个service层imp的某个的对
     ITb_UserService myITb_UserService=new ITb_UserServiceImpI();

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

     @Override
     protected void doPost(HttpServletRequest request, HttpServletResponse response)
             throws ServletException, IOException {
          request.setCharacterEncoding("UTF-8");//解决乱码
          String type=request.getParameter("who");
          //新增
          if("Insert".equals(type)){
              Insert(request, response);
             }
             else if("update".equals(type)){
                 update(request, response);
             }
             else if("queryById".equals(type)){
                 queryById(request, response);
             }
             else if("delete".equals(type)){
                 delete(request, response);
             }
             else if("queryAll".equals(type)){
                 queryAll(request, response);
             }
     }
 //新增
      public void Insert(HttpServletRequest request, HttpServletResponse response)
                 throws ServletException, IOException {
          //这里jsp中name专递过来的参数
          String name=request.getParameter("name");
          String birthday=request.getParameter("birthday");
          String password=request.getParameter("password");
          String email=request.getParameter("email");
          String sex=request.getParameter("sex");
           //把获取到的这些值放到user里
         Tb_User user =new Tb_User();

          try {
             //下面两句是把 string 转换为  sql类型的 时间格式
              SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
             user.setBirthday(new Date(sdf.parse(birthday).getTime()));
         } catch (ParseException e1) {
             e1.printStackTrace();
         }

          user.setEmail(email);
          user.setName(name) ;
          user.setPassword(password);
          if ("1".equals(sex)) {
              user.setSex(true);
         }
          else if ("0".equals(sex)) {
              user.setSex(false);
         }
          //最后调用服务来添加
         String message=null;
          if (myITb_UserService.insertData(user)==true) {
              queryAll(request, response);
         }
          else {
              message="新增失败!!!";
              request.setAttribute("msg", message);
                 request.getRequestDispatcher("/index.jsp").forward(request, response);
         }

      }
     //修改
      public void update(HttpServletRequest request, HttpServletResponse response)
                 throws ServletException, IOException {
           String name=request.getParameter("name");
           String birthday=request.getParameter("birthday");
          String password=request.getParameter("password");
 //     String email=request.getParameter("email");
          String sex=request.getParameter("sex");
          String id=request.getParameter("id");
           //把获取到的这些值放到user里
          Tb_User user =new Tb_User();
          try {
             //下面两句是把 string 转换为  sql类型的 时间格式
              SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
             user.setBirthday(new Date(sdf.parse(birthday).getTime()));
         } catch (ParseException e1) {
             e1.printStackTrace();
         }
          user.setId(Integer.parseInt(id));
     //     user.setEmail(email);
          user.setName(name) ;
          user.setPassword(password);
          if ("1".equals(sex)) {
              user.setSex(true);
         }
          else if ("0".equals(sex)) {
              user.setSex(false);
         }
          boolean b=  myITb_UserService.update(user);
           if (b==true) {
                  queryAll(request, response);
             }
              else {
                  request.setAttribute("msg", "修改失败!!");
                     request.getRequestDispatcher("/index.jsp").forward(request, response);
             }

      }
      //查询一条数据
      public void queryById(HttpServletRequest request, HttpServletResponse response)
                 throws ServletException, IOException {
              Tb_User user=null;
               String  id=  request.getParameter("id");
               System.out.println(id);
               user=   myITb_UserService.queryDataById(Integer.parseInt(id) );
             request.setAttribute("user", user);
             request.getRequestDispatcher("/jsp/User.jsp").forward(request, response);
      }
      //删除
      public void delete(HttpServletRequest request, HttpServletResponse response)
                 throws ServletException, IOException {
             String  id= request.getParameter("id");
              System.out.println(id);
              boolean  message=myITb_UserService.delete(Integer.parseInt(id));
              if (message==true) {
                  queryAll(request, response);
             }
              else {

                  request.setAttribute("msg", "删除失败!!");
                     request.getRequestDispatcher("/index.jsp").forward(request, response);
             }
      }
      //查询所有的数据
      public void queryAll(HttpServletRequest request,HttpServletResponse response)throws ServletException,IOException{
              List<Tb_User> lis=myITb_UserService.queryAllData();
              request.setAttribute("list", lis);
                  request.getRequestDispatcher("/jsp/User.jsp").forward(request, response);
         }

 }

三、创建jsp

User的代码如下:

 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
 <%
     String path = request.getContextPath();
     String basePath = request.getScheme() + "://"
             + request.getServerName() + ":" + request.getServerPort()
             + path + "/";
 %>
 <!--    c标签要使用,那么就必须要有它 -->
 <%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
 <c:set scope="page" var="url"
     value="${pageContext.request.contextPath }"></c:set>

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 <html>
 <head>
 <base href="<%=basePath%>">

 <title>新增用户</title>

 <meta http-equiv="pragma" content="no-cache">
 <meta http-equiv="cache-control" content="no-cache">
 <meta http-equiv="expires" content="0">
 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
 <meta http-equiv="description" content="This is my page"> 

 </head>

 <body>
     <div align="center"
         style="width: 400px; position: relative;left:450px">
         <form action="${url}/zhu/Servlet_TbUser?who=Insert" method="post">
             <h4>新增用户</h4>
             姓名: <input type="text" name="name"><br />
              密码: <input    type="text" name="password"><br />
              出生日期 : <input type="text" name="birthday"><br />
              性别: <select name="sex">
                 <option value="0">男</option>
                 <option value="1">女</option>
             </select><br />
             <input type="submit" value="新增"/>
             <hr />
         </form>
     </div>
     <div align="center"style="width: 400px; position: relative;left:450px;">
         <form action="${url}/zhu/Servlet_TbUser?who=queryAll" method="post">
              <input type="submit" value="查询所有的数据"/> <br/>
             <table border="1"  cellspacing="0">
                  <thead>
                   <tr><td>ID</td><td>姓名</td><td>密码</td><td>日期</td><td>性别</td><td>操作</td></tr>
                  </thead>
                  <tbody>
          <c:forEach items="${list}" var="list">
          <tr>
                 <td>${list.id }</td>
                 <td>${list.name }</td>
                 <td>${list.password }</td>
                 <td>${list.birthday }</td>
                 <td><c:if test="${list.sex==false }">男</c:if>
                 <c:if test="${list.sex==true }">女</c:if></td>
                  <td><a  href= "${url}/zhu/Servlet_TbUser?who=queryById&id=${list.id}"     style='text-decoration:none'    onclick='update(this)'   >修改&nbsp;</a>
                     <a    href= "${url}/zhu/Servlet_TbUser?who=delete&id=${list.id}"   style='text-decoration:none'     >删除</a>  </td>
          </tr>
          </c:forEach>
                  </tbody>
             </table>
             <hr />
         </form>
     </div>
     <div align="center"
         style="width: 400px; position: relative;left:450px">
         <form action="${url}/zhu/Servlet_TbUser?who=update" method="post">
             <h4>修改用户</h4>
             <input type="hidden"name="id"  value="${user.id }"/>
             姓名: <input type="text" name="name" value="${user.name }"><br />
              密码: <input    type="text" name="password" value="${user.password }"><br />
              出生日期 : <input type="text" name="birthday" value="${user.birthday }"><br />
           性别:<c:if test="${user.sex==false }">
                   <select name="sex" >
                 <option value="0">男</option>
                 <option value="1">女</option>
                </select>
                   </c:if>
               <c:if test="${user.sex==true }">
                   <select name="sex" >
                   <option value="1">女</option>
                 <option value="0">男</option>
               </select>
                   </c:if><br />
             <input type="submit" value="保存修改"/>
             <hr />
         </form>
     </div>
 </body>
 </html>

index.jsp代码如下:

 <%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
 <%
 String path = request.getContextPath();
 String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
 %>

 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 <html>
   <head>
     <base href="<%=basePath%>">

     <title>My JSP 'index.jsp' starting page</title>
     <meta http-equiv="pragma" content="no-cache">
     <meta http-equiv="cache-control" content="no-cache">
     <meta http-equiv="expires" content="0">
     <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
     <meta http-equiv="description" content="This is my page">
     <!--
     <link rel="stylesheet" type="text/css" href="styles.css">
     -->
   </head>

   <body>
     <h1>${msg }</h1> <br>
   </body>
 </html>

四、运行效果:  

    

源码下载地址:       https://pan.baidu.com/s/1bQKEAi