Servlet项目源码——servlet连接JDBC实现增删改查

jar包：

database.properties:

# 数据库连接对象基础配置
driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/****?useUnicode=true&characterEncoding=UTF-8
username = ****
password = ****

# 配置连接池相关内容
# 初始容量
initialSize = 10
# 最大连接数量
maxActive = 20
# 最小空闲连接
minIdle = 5
# 超时
maxWait = 3000

---

utils:

DbUtils:

package com.hyq.servletProject.utils;

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

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 工具类：
 *      1. 创建连接池
 *      2. 获取连接
 *      3. 事务控制
 *      4. 释放资源
 */
public class DbUtils {
    // 创建数据库连接池对象
    private static DruidDataSource ds;
    // 新建ThreadLocal控制事务，通过Connection存储
    private static final ThreadLocal<Connection> THREAD_LOCAL = new ThreadLocal<>();

    // 提供静态代码块，完成初始创建
    static {
        // 创建properties集合
        Properties properties = new Properties();
        // 通过类的自身得到字节输入流 inputStream，拿到database.properties
        InputStream inputStream = DbUtils.class.getResourceAsStream("/database.properties");
        try {
            // 通过.load()方法加载文件
            properties.load(inputStream);
            // 创建连接池，传入配置文件集合
            ds = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     * @return connection连接
     */
    public static Connection getConnection(){
        // .get()方法是拿到ThreadLocal的value副本，Key为connection,value是数据库连接对象还是注册驱动
        Connection connection = THREAD_LOCAL.get();

            try {
                if(connection == null) {
                    // 若connection为空，从连接池中拿一个connection
                    connection = ds.getConnection();
                    // 将拿到的 connection 存进 THREAD_LOCAL
                    THREAD_LOCAL.set(connection);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        return connection;
    }

    /**
     * 开启事务方法
     */
    public static void begin(){
        // 设空若存在
        Connection connection = null;
        // 获取连接
        connection = getConnection();
        try {
            // 取消数据库自动提交
            connection.setAutoCommit(false);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     * 提交事务方法 && 释放资源
     */
    public static void commit(){
        // 创建 connection
        Connection connection = null;
        connection = getConnection();
        try {
            connection.commit();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally{
            // 提交事务后要释放连接
            closeAll(connection,null,null);
        }
    }

    /**
     *事务回滚
     */
    public static void rollback(){
        Connection connection = null;
        connection = getConnection();
        try {
            connection.rollback();
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }finally{
            closeAll(connection,null,null);
        }
    }

    /**
     *  释放资源，关闭通道
     * @param connection 数据库连接对象
     * @param statement 数据库操作对象
     * @param resultSet 结果集
     */
    public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
        try{
            if(resultSet != null){
                resultSet.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();

                // 关闭连接后，要从ThreadLocal中移除
                THREAD_LOCAL.remove();
            }
        }catch(Exception e){
            e.printStackTrace();
        }

    }
}

---

entity:

Admin:

package com.hyq.servletProject.entity;

/**
 * entity实体类
 *      完成数据库对表的映射
 */
public class Admin {
    private String username;
    private String password;
    private String phone;
    private String address;

    @Override
    public String toString() {
        return "Admin{" +
                "username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", phone='" + phone + '\'' +
                ", address='" + address + '\'' +
                '}';
    }

    public Admin(String username, String password, String phone, String address) {
        this.username = username;
        this.password = password;
        this.phone = phone;
        this.address = address;
    }

    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 getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Admin() {

    }

//    public Admin(String username,String password,String phone,String address){
//        this.username = username;
//        this.password = password;
//        this.phone = phone;
//        this.address = address;
//    }


//    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 getPhone(){
//        return phone;
//    }
//    public void setPhone(String password){
//        this.password = password;
//    }
//
//    public String getAddress(){
//        return password;
//    }
//    public void setAddress(String password){
//        this.password = password;
//    }
}

---

dao

接口：

package com.hyq.servletProject.dao;

import com.hyq.servletProject.entity.Admin;

import java.util.List;

/**
 * 数据访问接口
 */
public interface AdminDao {
    public int insert(Object[] params);
    public int delete(String username);
    public int update(Admin admin);
    public Admin select(String username);
    public List<Admin> selectAll();
}

实现类：

package com.hyq.servletProject.dao.impl;

import com.hyq.servletProject.dao.AdminDao;
import com.hyq.servletProject.entity.Admin;
import com.hyq.servletProject.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

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

/**
 * 实现接口，重写五个方法
 */
public class AdminDaoImpl implements AdminDao {
    // 创建apache queryRunner,实现事务控制，不传入数据源
    // 什么时候控制事务 --> 在queryRunner执行对应方法的时候再传连接
    private QueryRunner queryRunner = new QueryRunner();


    /**
     * 新增表数据
     * @param params 传入一个数组，包含insert所需要的数据
     * @return 返回一个int类型的值，表示为所影响的行数
     */
    @Override
    public int insert(Object[] params) {
        Object[] params3 = params;
        try {
//            Admin adminInsert = queryRunner.query(DbUtils.getConnection(),
//                                                "insert into admin(username,password,phone,address) values(?,?,?,?)",
//                                                    new BeanHandler<Admin>(Admin.class),
//                                                    params);
//            Admin adminInsert = queryRunner.query(DbUtils.getConnection(),"insert into admin values(?,?,?,?)",params);
            int count = queryRunner.update(DbUtils.getConnection(),
                                                "insert into admin(username,password,phone,address) values(?,?,?,?)",
                                                    params3);
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
//        return null;
        return 0;
    }

    /**
     *删除表数据
     * @param username 传入要要删掉哪个用户的数据
     * @return 返回一个int类型的值，表示为所影响的行数
     */
    @Override
    public int delete(String username) {
        try {
            int count = queryRunner.update(DbUtils.getConnection(),
                                            "delete from admin where username = ?",
                                                username);
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int update(Admin admin) {
        return 0;
    }

    /**
     * 登录查询
     * @param username
     * @return admin 返回一整条数据
     * admin = this.query(conn, false, sql, rsh, params);
     */
    @Override
    public Admin select(String username) {
        try {
            Admin admin = queryRunner.query(DbUtils.getConnection(),
                                            "select * from admin where username = ?;",
                                            new BeanHandler<Admin>(Admin.class),
                                            username);
            return admin;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    /**
     * 展示所有用户的功能
     * @return admins = this.query(conn, false, sql, rsh, (Object[])null);
     */
    @Override
    public List<Admin> selectAll() {
        try {
            List<Admin> admins = queryRunner.query(DbUtils.getConnection(),
                    "select * from admin",
                    new BeanListHandler<Admin>(Admin.class));
            return admins;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}

---

service:

接口：

package com.hyq.servletProject.service;

import com.hyq.servletProject.entity.Admin;

import java.util.List;

/**
 * 提供给用户的业务功能
 *      1. login()
 *      2. showAllAdmin()
 */
public interface AdminService {
    // 返回对象admin
    public Admin login(String username, String password);

    public List<Admin> showAllAdmin();

    public int Insert(Object[] params);

    public int delete(String username);
}

实现类：

package com.hyq.servletProject.service.impl;

import com.hyq.servletProject.dao.AdminDao;
import com.hyq.servletProject.dao.impl.AdminDaoImpl;
import com.hyq.servletProject.entity.Admin;
import com.hyq.servletProject.service.AdminService;
import com.hyq.servletProject.utils.DbUtils;

import java.util.List;

public class AdminServiceImpl implements AdminService {
    private AdminDao adminDao = new AdminDaoImpl();

    /**
     * 登录业务功能
     * @param username
     * @param password
     * @return result 查到的整条数据
     */
    @Override
    public Admin login(String username, String password) {
        // 创建对象result,用作接收数据
        Admin result = null;
        try {
            // 1. 开启事务
            DbUtils.begin();
            // 2. 通过adminDao.select判断用户名
            Admin admin = adminDao.select(username);
            if (admin != null) {
                // 3. 验证密码，equal()用于判断两个String类型数据是否相等
                if (admin.getPassword().equals(password)) {
                    // 如果用户密码存在且正确，就将admin赋值给result
                    result = admin;
                }
            }
            // 事务结束且成功就提交
            DbUtils.commit();
        }catch(Exception e){
            // 事务出异常就回滚
            DbUtils.rollback();
            e.printStackTrace();
        }
        return result;
    }

    /**
     * 业务功能：
     *      显示所有数据
     * @return admin 返回所有的数据映射表
     */
    @Override
    public List<Admin> showAllAdmin() {
        // 制空admins,方便返回
        List<Admin> admins = null;
        try {
            // 控制事务开启
            DbUtils.begin();
            // admins调用selectAll()接收数据
            admins = adminDao.selectAll();
            // 能接收数据说明事务完成，调用提交方法
            DbUtils.commit();
        }catch(Exception e){
            // 出现异常就回滚
            DbUtils.rollback();
            e.printStackTrace();
        }
        // 返回结果给servlet
        return admins;
    }

    /**
     * 业务功能：
     *      插入数据
     * @param params 传入一个数组，包含insert所需要的数据
     * @return 返回一个int类型的值，表示为所影响的行数
     */
    @Override
    public int Insert(Object[] params) {
//        int result2 = Integer.parseInt(null);
        int result2 = 0;
        try{
            DbUtils.begin();
            result2 = adminDao.insert(params);
            DbUtils.commit();
        }catch (Exception e){
            DbUtils.rollback();
            e.printStackTrace();
        }

        return result2;
    }

    /**
     * 业务功能：
     *      删除某个用户数据
     * @param username 传入要删除的哪个用户名称
     * @return 返回一个int类型的值，表示为所影响的行数
     */
    @Override
    public int delete(String username) {
        int result3 = 0;
        try{
            DbUtils.begin();
            result3 = adminDao.delete(username);
            DbUtils.commit();
        }catch (Exception e){
            DbUtils.rollback();
            e.printStackTrace();
        }

        return result3;
    }
}

---

servlet:

LoginServlet:

package com.hyq.servletProject.servlet;

import com.hyq.servletProject.entity.Admin;
import com.hyq.servletProject.service.AdminService;
import com.hyq.servletProject.service.impl.AdminServiceImpl;

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.io.PrintWriter;

/**
 * 登录servlet业务：
 *      用户通过login.html页面输入用户名和密码，点击提交
 *      数据提交到loginServlet里，loginServlet将收餐并调用service类判断是否正确
 *      并在html页面上显示结果
 */

@WebServlet(value = "/login")
public class LoginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 设置字符集编码格式
        req.setCharacterEncoding("UTF-8");
        // 设置响应给客户端的结果也是以test/html;charset = UTF-8的格式
        resp.setContentType("text/html;charset=UTF-8");

        // 1.收参，通过req的getParameter()方法拿到用户输入的用户名和密码
        String username = req.getParameter("username");
        String password = req.getParameter("password");

        // 2.调用业务逻辑，将数据传给业务逻辑层
        AdminService adminService = new AdminServiceImpl();     // new一个AdminService对象
        Admin admin = adminService.login(username,password);    // 调用AdminService的login方法传参

        // 3.处理结果
        PrintWriter printWriter = resp.getWriter();// 获取输出流
        if(admin != null){
            // 响应给客户端一个结果页面，显示登录成功
            printWriter.println("<html>");
            printWriter.println(    "<head>");
            printWriter.println(        "<meta charset = 'UTF-8'>");
            printWriter.println(        "<title>结果页面</title>");
            printWriter.println(    "</head>");

            printWriter.println(    "<body>");
            printWriter.println(        "<div>");
            printWriter.println(            "<h3>登录成功</h3>");
            printWriter.println(        "</div>");
            printWriter.println(    "</body>");
            printWriter.println("</html>");

        }else{
            // 响应给客户端一个结果页面，显示登录失败
            printWriter.println("<html>");
            printWriter.println(    "<head>");
            printWriter.println(        "<meta charset = 'UTF-8'>");
            printWriter.println(        "<title>结果页面</title>");
            printWriter.println(    "</head>");

            printWriter.println(    "<body>");
            printWriter.println(        "<h3>登录失败</h3>");
            printWriter.println(    "</body>");
            printWriter.println("</html>");

        }

    }

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

ShowAllAdminServlet:

package com.hyq.servletProject.servlet;

import com.hyq.servletProject.entity.Admin;
import com.hyq.servletProject.service.AdminService;
import com.hyq.servletProject.service.impl.AdminServiceImpl;

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

/**
 * 业务：
 *      展示所有用户信息
 * 缺点：
 *      ShowAllAdminServlet即调用了业务逻辑功能，又通过流显示了结果信息
 *      造成职能不单一，职责不清晰，因为做了两个动作
 *      假设后续显示效果需要替换，就要修改servlet或者再写一个servlet
 *      所以：
 *          问题1：不符合单一职能原则
 *          问题2：不利于后续的维护
 * 解决：
 *      应该将业务逻辑和显示结果分开
 *
 *                   --> xxxController extends HttpServlet  //调用业务逻辑
 *      xxxServlet--|
 *                  --> xxxJSP extends HttpServlet  // 显示页面结果
 */
@WebServlet(value = "/showall")
public class ShowAllAdminServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setContentType("text/html;charset=utf-8");

        // 1.调用业务逻辑功能
        AdminService adminService = new AdminServiceImpl();     // 获取执行业务逻辑层的结果
        List<Admin> adminList = adminService.showAllAdmin();        // 拿到结果集合

        // 2.通过流打印结果显示
        PrintWriter printWriter = resp.getWriter();     // 获取输出流
        // 判空
        if(adminList != null){
            printWriter.println("<html>");
            printWriter.println(    "<head>");
            printWriter.println(        "<meta charset = 'UTF-8'>");
            printWriter.println(        "<title>显示所有</title>");
            printWriter.println(    "</head>");

            printWriter.println(    "<body>");
            printWriter.println(        "<table border = '3'>");
            printWriter.println(            "<tr>");
            printWriter.println(                "<td>username</td>");
            printWriter.println(                "<td>password</td>");
            printWriter.println(                "<td>phone</td>");
            printWriter.println(                "<td>address</td>");
            printWriter.println(            "</tr>");

            for(Admin admin : adminList){
                printWriter.println(            "<tr>");
                printWriter.println(                "<td>"+admin.getUsername()+"</td>");
                printWriter.println(                "<td>"+admin.getPassword()+"</td>");
                printWriter.println(                "<td>"+admin.getPhone()+"</td>");
                printWriter.println(                "<td>"+admin.getAddress()+"</td>");
                printWriter.println(            "</tr>");

            }
            printWriter.println(        "</table>");
            printWriter.println(    "</body>");
            printWriter.println("</html>");

        }else{
            printWriter.println("<html>");
            printWriter.println(    "<head>");
            printWriter.println(        "<meta charset = 'UTF-8'>");
            printWriter.println(        "<title>显示所有</title>");
            printWriter.println(    "</head>");

            printWriter.println(    "<body>");
            printWriter.println(        "<h3>当前没有数据</h3>");
            printWriter.println(    "</body>");
            printWriter.println("</html>");

        }
    }

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

AhowAllAdminController:

package com.hyq.servletProject.servlet;

import com.hyq.servletProject.entity.Admin;
import com.hyq.servletProject.service.AdminService;
import com.hyq.servletProject.service.impl.AdminServiceImpl;

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;

/**
 * 业务逻辑层servlet
 * 页面跳转：
 *      request.getRequestDispatcher("/目标URL-patern").forward(request,response);
 *      使用forward跳转时，是在服务器内部跳转，地址栏不发生改变，属于同一次请求
 *
 * 数据传递：
 *      存数据：
 *      request.setAttribute(key,value);
 *      以键值对形式存储再request作用域中，key作为 String类型，value为 Object类型
 *
 *       取数据：
 *       request.getAttribute(key)
 *  *    通过String类型的key访问呢Object类型的value
 */
@WebServlet(value = "/showallcontroller")
public class ShowAllAdminController extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 只负责调用业务逻辑功能
        AdminService adminService = new AdminServiceImpl();

        List<Admin> adminList = adminService.showAllAdmin();

        // request作用域存储数据
        req.setAttribute("admins",adminList);
        // 通过转发，跳转到显示结果的Servlet
        req.getRequestDispatcher("/showalljsp").forward(req,resp);
    }

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

ShowAllAdminJSP:

package com.hyq.servletProject.servlet;

import com.hyq.servletProject.entity.Admin;

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

/**
 * 显示结果servlet
 * 数据传递：
 *      取数据：request.getAttribute(key)
 *      通过String类型的key访问呢Object类型的value
 */
@WebServlet("/showalljsp")
public class ShowAllAdminJSP extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");

        List<Admin> adminList = (List)req.getAttribute("admins");

        // 2.通过流打印结果显示
        PrintWriter printWriter = resp.getWriter();
        if(adminList != null){
            printWriter.println("<html>");
            printWriter.println(    "<head>");
            printWriter.println(        "<meta charset = 'UTF-8'>");
            printWriter.println(        "<title>显示所有</title>");
            printWriter.println(    "</head>");

            printWriter.println(    "<body>");
            printWriter.println(        "<table border = '1'>");
            printWriter.println(            "<tr>");
            printWriter.println(                "<td>username</td>");
            printWriter.println(                "<td>password</td>");
            printWriter.println(                "<td>phone</td>");
            printWriter.println(                "<td>address</td>");
            printWriter.println(            "</tr>");

            for(Admin admin : adminList){
                printWriter.println(            "<tr>");
                printWriter.println(                "<td>"+admin.getUsername()+"</td>");
                printWriter.println(                "<td>"+admin.getPassword()+"</td>");
                printWriter.println(                "<td>"+admin.getPhone()+"</td>");
                printWriter.println(                "<td>"+admin.getAddress()+"</td>");
                printWriter.println(            "</tr>");

            }
            printWriter.println(        "</table>");
            printWriter.println(    "</body>");
            printWriter.println("</html>");

        }else{
            printWriter.println("<html>");
            printWriter.println(    "<head>");
            printWriter.println(        "<meta charset = 'UTF-8'>");
            printWriter.println(        "<title>显示所有</title>");
            printWriter.println(    "</head>");

            printWriter.println(    "<body>");
            printWriter.println(        "<h3>当前没有数据</h3>");
            printWriter.println(    "</body>");
            printWriter.println("</html>");

        }
    }

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

---

Web:

login.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <form action = "/untitled2_war_exploded/login" method = "post">
        <div>
            用户名：<input type = "test" name = "username" /></br>
            密码：<input type = "test" name = "password" /></br>
            <input type = "submit" value = "登录" />
        </div>
    </form>
</body>
</html>

---

showAll.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>showAll</title>
</head>
<body>
<div>
<form action = "/untitled2_war_exploded/showall" method = "post">

    <input type = "submit" value = "查询所有" />

</form>
</div>
<div>
    <form action = "/untitled2_war_exploded/login" method = "post">

        用户名：<input type = "test" name = "username" /></br>
        密码：<input type = "" name = "password" /></br>
        <input type = "submit" value = "登录" />

    </form>

</div>

</body>
</html>

---

Insert.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Insert</title>
</head>
<body>
<form action = "/untitled2_war_exploded/insert" method = "post">

    用户名：<input type = "test" name = "username" /></br>
    密码：<input type = "test" name = "password" /></br>
    手机：<input type = "test" name = "phone" /></br>
    地址：<input type = "test" name = "address" /></br>
    <input type = "submit" value = "提交" />

</form>
</body>
</html>

---

delete.html:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>删除业务</title>
</head>
<body>

    <form action = "/untitled2_war_exploded/delete" method = "post">
        请输入要删除的用户名：<input type = "test" name = "username" />&emsp;
        <input type = "submit" value = "提交" />
    </form>

</body>
</html>

---