简单增删查改案例jdbc + Servlet + jsp

1.创建数据库

1.1数据字典

字段	数据类型	约束	描述
id	int(11)	主键 自增	用户id
username	varchar(20)	唯一	用户姓名
password	varchahr(20)	默认为空	用户密码
email	varchar(30)	默认为空	用户邮箱

1.2数据库新建user表

create table t_user(
    id int(11) primary key  auto_increment,
    username varchar(20) unique not null,
    password varchar(32) ,
    email varchar(200)
)charset=utf8;

1.3初始化数据

insert into t_user values(1,'李四','12345','149@qq.com');
insert into t_user values(2,'张三','12345','149@qq.com');
insert into t_user values(3,'tom','12345','149@qq.com');
insert into t_user values4,'jack','12345','149@qq.com');

2编写代码

新建javaee项目导入jar包和创建druid.properties配置文件

2.1创建项目

整个项目目录结构

2.2导包写工具类

在src目录下创建Utils工具包编写数据库连接工具

Env.java

package utlis;

import java.io.IOException;
import java.util.Properties;

//通过单例设计模式读取配置文件的信息
public class Env extends Properties {
    //单例设计模式的写法
    private static Env instance = null;

    private Env(){
        try {
            load(Env.class.getResourceAsStream("/druid.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static Env getInstance(){
        if(instance == null){
            instance = new Env();
        }
        return instance;
    }
}

在src包下创建test包测试Env

EnvTest.java

package test;

import org.junit.Test;
import utlis.Env;

import static org.junit.Assert.*;

public class EnvTest {

    @Test
    public void getInstance() {
        System.out.println(Env.getInstance().getProperty("url"));
    }
}

编写数据库工具类

DBPoolUtil.java

package utlis;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.util.Properties;

public class DBPoolUtil {
    //创建DateSource资源
    private static DruidDataSource dataSource = null;

    static {
    Properties p = new Properties();

        try {
            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(p);
            //通过Env读取配置文件
            dataSource.setDriverClassName(Env.getInstance().getProperty("driverClassName"));
            dataSource.setUrl(Env.getInstance().getProperty("url"));
            dataSource.setUsername(Env.getInstance().getProperty("username"));
            dataSource.setPassword(Env.getInstance().getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DataSource getDataSource(){
        return dataSource;
    }

}

2.3 IDEA连接数据库

（可不实现这一步骤）

测试连接出现问题（是因为没有设置时区）

解决方法

https://blog.csdn.net/qq_31762741/article/details/115184255

问题解决好后点击应用

2.4创建实体类

在src目录下创建entity包后在数据库中自动生成实体类

在entity包中创建User类

package entity;


public class User {

  private int id;
  private String username;
  private String password;
  private String email;

  public User() {
  }

  public User(int id, String username, String password, String email) {
    this.id = id;
    this.username = username;
    this.password = password;
    this.email = email;
  }

  public long getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }


  public String getUsername() {
    return username;
  }

  public void setUsername(String username) {
    this.username = username;
  }


  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;
  }

  @Override
  public String toString() {
    return "User{" +
            "id=" + id +
            ", username='" + username + '\'' +
            ", password='" + password + '\'' +
            ", email='" + email + '\'' +
            '}';
  }
}

2.5 创建dao层

在src下创建dao包在包下创建Impl包和IUser接口

接口IUserDao.java

package dao;

import entity.User;

import java.util.List;

public interface IUserDao {
    //查所有
    List<User> getAll();

    //分页查
    List<User> getByPage(int cp,int ns);

    //根据id查
    User getById(int id);

    //增加
    int save(User user);

    //修改
    int update(User user);

    //删除
    int delete(int id);

    //查询数量
    long getCount();
}

Impl包下的IUserDaoImpl实现类

package dao.impl;

import dao.IUserDao;
import entity.User;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utlis.DBPoolUtil;

import java.sql.SQLException;
import java.util.List;

public class IUserDaoImpl implements IUserDao {
    QueryRunner qr = new QueryRunner(DBPoolUtil.getDataSource());
    @Override
    public List<User> getAll() {
        try {
            return qr.query("select * from t_user ",new BeanListHandler<User>(User.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<User> getByPage(int cp,int ns) {
        int si = (cp - 1) * ns;

        try {
            return qr.query("select * from t_user limit ?,?",new BeanListHandler<User>(User.class),si,ns);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public User getById(int id) {
        try {
            return qr.query("select * from t_user where id = ?",new BeanHandler<User>(User.class),id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int save(User user) {
        try {
            return qr.update("insert into t_user values(?,?,?,?)",user.getId(),
                    user.getUsername(),user.getPassword(),user.getEmail());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(User user) {
        try {
            return qr.update("update t_user set username =?, password = ?,email =? where id =?",
                    user.getUsername(),user.getPassword(),user.getEmail(),user.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(int id) {
        try {
            return qr.update("delete from t_user where id = ?",id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public long getCount() {
        try {
            return qr.query("select count(1) from t_user",new ScalarHandler<>());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }
}

test包下的IUserDaoImplTest类

package test;

import dao.IUserDao;
import dao.impl.IUserDaoImpl;
import entity.User;
import org.junit.Test;

import java.util.List;

import static org.junit.Assert.*;

public class IUserDaoImplTest {
    private IUserDaoImpl iUserDao = new IUserDaoImpl();

    @Test
    public void getAll() {
        List<User> list = iUserDao.getAll();
        for (User user : list) {
            System.out.println(user);
        }
    }

    @Test
    public void getByPage() {
        List<User> list = iUserDao.getByPage(1,3);
        for (User user : list) {
            System.out.println(user);
        }
    }

    @Test
    public void getById() {
        User user = iUserDao.getById(1);
        System.out.println(user);
    }

    @Test
    public void save() {
        User user = new User(5,"猪猪","1235","hhi@com");
        int save = iUserDao.save(user);
        System.out.println(save);
    }

    @Test
    public void update() {
        User user = new User(5,"猪猪boy","1235","hhi@com");
        int update = iUserDao.update(user);
        System.out.println(update);
    }

    @Test
    public void delete() {
        int delete = iUserDao.delete(5);
        System.out.println(delete);
    }

    @Test
    public void getCount() {
        long count = iUserDao.getCount();
        System.out.println(count);
    }
}

2.6创建service层

在src包下创建Service包 创建IUserService接口和impl包

接口IUserService.java

package service;

import entity.User;

import java.util.List;

public interface IUserService {
    //查所有
    List<User> getAll();

    //分页查
    List<User> getByPage(int cp,int ns);

    //根据id查
    User getById(int id);

    //增加
    boolean save(User user);

    //修改
    boolean update(User user);

    //删除
    boolean delete(int id);

    //查询数量
    long getCount();
}

在impl包下创建实体类

IUserServiceImpl.java

package service.impl;

import dao.IUserDao;
import dao.impl.IUserDaoImpl;
import entity.User;
import service.IUserService;

import java.util.List;

public class IUserServiceImpl implements IUserService {
    private IUserDao iUserDao = new IUserDaoImpl();
    @Override
    public List<User> getAll() {
        return iUserDao.getAll();
    }

    @Override
    public List<User> getByPage(int cp, int ns) {
        return iUserDao.getByPage(cp,ns);
    }

    @Override
    public User getById(int id) {
        return iUserDao.getById(id);
    }

    @Override
    public boolean save(User user) {
        return iUserDao.save(user)>0;
    }

    @Override
    public boolean update(User user) {
        return iUserDao.update(user)>0;
    }

    @Override
    public boolean delete(int id) {
        return iUserDao.delete(id)>0;
    }

    @Override
    public long getCount() {
        return iUserDao.getCount();
    }
}

2.7 创建测试类

创建单元测试类IUserServiceImplTest.java

package test;

import entity.User;
import org.junit.Test;
import service.impl.IUserServiceImpl;

import java.util.List;

public class IUserServiceImplTest {
    private IUserServiceImpl iUserService = new IUserServiceImpl();

    @Test
    public void getAll() {
        List<User> list = iUserService.getAll();
        for (User user : list) {
            System.out.println(user);
        }
    }

    @Test
    public void getByPage() {
        List<User> list = iUserService.getByPage(1,3);
        for (User user : list) {
            System.out.println(user);
        }
    }

    @Test
    public void getById() {
        User byId = iUserService.getById(1);
        System.out.println(byId);
    }

    @Test
    public void save() {
        boolean save = iUserService.save(new User(6, "杰克", "12345", "ajjj@qq.com"));
        System.out.println(save);
    }

    @Test
    public void update() {
        boolean update = iUserService.update(new User(6, "杰克666", "12345", "ajjj@qq.com"));
        System.out.println(update);

    }

    @Test
    public void delete() {
        boolean delete = iUserService.delete(6);
        System.out.println(delete);
    }

    @Test
    public void getCount() {
        long count = iUserService.getCount();
        System.out.println(count);
    }
}

2.8创建fifter包

CharacterFilter.java

package filter;

import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import java.io.IOException;

@WebFilter(filterName = "CharacterFilter",value = "/*")
public class CharacterFilter implements Filter {
    public void destroy() {
    }

    public void doFilter(ServletRequest req, ServletResponse resp, FilterChain chain) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=UTF-8");
        chain.doFilter(req, resp);
    }

    public void init(FilterConfig config) throws ServletException {

    }

}

2.9创建controller包

创建BaseServlet.java

package controller;

import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

public class BaseServlet extends HttpServlet {
    @Override
    public void service(ServletRequest req, ServletResponse res) throws ServletException, IOException {
        String op = req.getParameter("op");
        if(op == null){
            op = "getAll";
        }

        if(op != null){
            try {
                Method method = getClass().getDeclaredMethod(op, HttpServletRequest.class, HttpServletResponse.class);

                method.setAccessible(true);

                method.invoke(this,req,res);

            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            }
        }
    }
}

3.整合前端页面

index.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
  <head>
    <title>$Title$</title>
  </head>
  <body>
  <a href="UserServlet">all User</a></p>
  </body>
</html>

修改Tomcat的名字和默认路径

3.1导包（JSTL使用）

导入jstl所需要的jar包

3.2 查找所有

UserServlet.java

package controller;

import entity.User;
import service.IUserService;
import service.impl.IUserServiceImpl;

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 java.io.IOException;
import java.util.List;

@WebServlet(name = "UserServlet",value = "/UserServlet")
public class UserServlet extends BaseServlet{
    private IUserService iUserService = new IUserServiceImpl();
    protected void getAll(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String op = req.getParameter("op");
        req.setAttribute("op",op);
        //得到数据库中的用户信息
        List<User> list = iUserService.getAll();
        //将用户信息保存在域中
        req.setAttribute("Users",list);
        //页面转发到users.jsp页面  查找用页面转发需要共享数据 增删改用重定向
        req.getRequestDispatcher("users.jsp").forward(req,resp);
    }
}

3.2.1

在web目录下新建pages包存放jsp页面

新建users.jsp页面 table的属性样式一定要正确不然出不来

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<html>
<head>
    <title>users</title>
</head>
<body>
<c:if test="${users == null || users.size()==0}">
    no date
</c:if>
<c:if test="${users != null || users.size()!=0}">
    <table border="1" align="center" width="80%">
        <tr>
            <th>id</th>
            <th>name</th>
            <th>password</th>
            <th>email</th>
            <th>manger</th>
        </tr>
        <c:forEach items="${users}" var="p">
            <tr>
                <td>${p.id}</td>
                <td>${p.username}</td>
                <td>${p.password}</td>
                <td>${p.email}</td>
                <td><a href="#">修改</a>
                    <a href="#">删除</a>
                </td>
            </tr>
        </c:forEach>
    </table>
</c:if>
</body>
</html>

3.2.2实现效果

3.3修改操作

3.3.1首先修改users.jsp页面修改的链接地址

<a href="UserServlet?op=getById&id=${p.id}">修改</a>

3.3.2在web/pages/user包下添加user.jsp

<%--
  Created by IntelliJ IDEA.
  User: ylk
  Date: 2022/8/27
  Time: 16:21
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>user</title>
</head>
<body>
     <form method="post" action="UserServlet">
    <input type="hidden" name="op" value="update"></p>
    id:<input type="text" name="id" value="${user.id}" readonly="readonly"></p>
    username:<input type="text" name="name" value="${user.username}"></p>
    Password:<input type="text" name="password" value="${user.password}"></p>
    email:<input type="text" name="email" value="${user.email}"></p>
    <input type="submit" value="修改"></p>
</form>
</body>
</html>

3.3.3在UserServlet.java中添加通过id查找和修改的方法

protected void getById(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
       //从页面获取id的值
        String id = req.getParameter("id");
        int byId = id ==null?0:Integer.parseInt(id);
        //调用service层获取User对象
        User user = iUserService.getById(byId);
        //把对象保存到域中
        req.setAttribute("user",user);
        //页面转发到到/pages/user/user.jsp
        req.getRequestDispatcher("/pages/user/user.jsp").forward(req, resp);

    }


    protected void update(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String id = req.getParameter("id");
        int id2 = id ==null ? 0 : Integer.parseInt(id);

        String name = req.getParameter("name");

        String password = req.getParameter("password");

        String email = req.getParameter("email");

        boolean update = iUserService.update(new User(id2, name, password, email));

        if(update){
            resp.sendRedirect("UserServlet");
        }
    }

3.4删除操作

3.4.1首先修改users.jsp页面修改的链接地址

<a href="UserServlet?op=delete&id=${p.id}">删除</a>

3.4.2添加删除方法

 protected void delete(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String id = req.getParameter("id");
        int id2 = id ==null ? 0 : Integer.parseInt(id);

        boolean delete = iUserService.delete(id2);

        if(delete){
            resp.sendRedirect("UserServlet");
        }

3.5添加操作

3.5.1添加超链接语句

<a href="/demo/pages/user/saveUser.jsp">添加</a>

3.5.2在web/pages/user包下添加saveUser.jsp

<%--
  Created by IntelliJ IDEA.
  User: ylk
  Date: 2022/8/27
  Time: 16:43
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>saveUser</title>
</head>
<body>
<form method="post" action="../../UserServlet">
    <input type="hidden" name="op" value="save"></p>
    id:<input type="text" name="id" ></p>
    username:<input type="text" name="name" ></p>
    Password:<input type="text" name="password" ></p>
    email:<input type="text" name="email" ></p>
    <input type="submit" value="添加"></p>
</form>
</body>
</html>

3.5.3在UserServlet中添加方法

 protected void save(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    String id = req.getParameter("id");
    int id2 = id ==null ? 0 : Integer.parseInt(id);

    String name = req.getParameter("name");

    String password = req.getParameter("password");

    String email = req.getParameter("email");

    boolean save = iUserService.save(new User(id2, name, password, email));

    if(save){
        resp.sendRedirect("UserServlet");
    }
}
le>
</head>
<body>
<form method="post" action="../../UserServlet">
    <input type="hidden" name="op" value="save"></p>
    id:<input type="text" name="id" ></p>
    username:<input type="text" name="name" ></p>
    Password:<input type="text" name="password" ></p>
    email:<input type="text" name="email" ></p>
    <input type="submit" value="添加"></p>
</form>
</body>
</html>