JDBC封装

一.导入mysql包

二.封装DButils
DButils类(关闭流和取得连接)

package util;
import java.sql.*;

public class DBUtil {

private static String driver = “com.mysql.jdbc.Driver”;
private static String url = “jdbc:mysql://localhost:3306/user?useSSL=true&characterEncoding=utf-8”;
private static String user=“root”;
private static String password=“123456”;
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}

// 创建连接
public static Connection get_Conn() throws SQLException {
    Connection conn = DriverManager.getConnection(url, user, password);
    System.out.println("数据路连接成功！");
    return conn;
}


// 关闭连接
public static void get_CloseConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws SQLException {
    if(rs !=null){
        rs.close();
    }
    if(pstm !=null){
        pstm.close();
    }
    if(conn !=null){
        conn.close();
    }
}
//   单元测试/白盒测试
public static void main(String[] args) {
    try {
        get_Conn();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}
}

四.Dao层(接口)
IUserDao类

package dao;

import bean.User;

import java.util.List;

public interface IUserDao {

    // 登录
    User doLogin(String username);


    List<User> allUser();

    int save(User user);

    int delete(String id);

    User findid(String id);

    int update(User user);
}

五.DaoImpl层(dao实现类)

package dao.impl;

import bean.User;
import dao.IUserDao;
import util.DBUtil;

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

public class UserDaoImpl implements IUserDao {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
User user = null;

@Override
public List<User> findAll() {
    List<User> userList = new ArrayList<>();

    try {
        // 1. 创建连接
        conn = DBUtil.get_Conn();
        // 2. 获取存放sql语句的对象
        pstm = conn.prepareStatement("select * from tb_user");
        // 3. 执行并返回结果
        rs = pstm.executeQuery();
        // 4. 遍历结果集
        while (rs.next()){
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            userList.add(user);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return userList;
}

//测试运行
public static void main(String[] args) {
    UserDaoImpl us = new UserDaoImpl();
    us.findAll();
}
}




Servlet层

package controller;

import bean.User;
import service.IUserService;
import service.impl.UserServiceimpl;

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("*.do")
public class UserServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doPost(request, response);
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException, ServletException {
    request.setCharacterEncoding("utf-8");
    response.setContentType("text/html;charset=utf-8");

    String url = request.getRequestURI();
    System.out.println(url);
    String action = request.getRequestURI().substring(url.lastIndexOf('/') + 1, url.lastIndexOf('.'));
    System.out.println(action);

    if("login".equals(action)){

        String username = request.getParameter("username");
        String password = request.getParameter("password");

        IUserService userService = new UserServiceimpl();
        User user = userService.doLogin(username);
        if(user !=null){
            if(user.getPassword().equals(password)){
                System.out.println("登陆成功");
                request.getSession().setAttribute("user",user);
                response.sendRedirect("allUser.do");
            }else {
                System.out.println("用户名或者密码错误，请重新登录！");
                request.setAttribute("message","用户名或者密码错误");
                request.getRequestDispatcher("login.jsp").forward(request,response);
            }
        }


    }else if("save".equals(action)){
        String id = request.getParameter("id");
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        User user = new User(Integer.valueOf(id),username,password);
        IUserService userService = new UserServiceimpl();
        int i = userService.save(user);
        if(i != 0){
            response.sendRedirect("allUser.do");
        }else {
            response.getWriter().write("添加失败");
        }
    }else if("allUser".equals(action)){
        IUserService userService = new UserServiceimpl();
        List<User> users = userService.allUser();
        request.getSession().setAttribute("userInfos",users);
        response.sendRedirect("allUser.jsp");
    }else if("toAddUser".equals(action)){
        request.getRequestDispatcher("addUser.jsp").forward(request,response);
    }else if("delete".equals(action)){
        IUserService userService = new UserServiceimpl();
        String id = request.getParameter("id");
        int i = userService.delete(id);
        if(i != 0){
            response.sendRedirect("allUser.do");
        }else {
            response.getWriter().write("删除失败");
        }
    }else if("toUpdate".equals(action)){
        IUserService userService = new UserServiceimpl();
        String id = request.getParameter("id");
        User user = userService.findid(id);
        request.getSession().setAttribute("userInfo",user);
        request.getRequestDispatcher("updateUser.jsp").forward(request,response);
    }else if("update".equals(action)){
        IUserService userService = new UserServiceimpl();
        String id = request.getParameter("id");
        String username = request.getParameter("username");
        String password = request.getParameter("password");
        User user = new User(Integer.valueOf(id),username,password);
        int i = userService.update(user);
        if(i != 0){
            response.sendRedirect("allUser.do");
        }else {
            response.getWriter().write("修改失败");
        }

    }


}

}

USerDaoImpl

qpackage dao.impl;

import bean.User;
import dao.IUserDao;
import util.DBUtil;

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

public class UserDaoImpl implements IUserDao {
Connection conn = null;
PreparedStatement pstm = null;
ResultSet rs = null;
User user = null;

@Override
public User doLogin(String username) {
    try {
        conn = DBUtil.get_Conn();
        pstm = conn.prepareStatement("select * from user where username=?");
        pstm.setString(1,username);
        rs = pstm.executeQuery();
        while (rs.next()){
            user = new User();
            user.setId(rs.getInt("uid"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return user;
}

@Override
public List<User> allUser() {
    List<User> users = new ArrayList<>();
    try {
        conn = DBUtil.get_Conn();
        pstm = conn.prepareStatement("select * from adminuser");
        rs = pstm.executeQuery();
        while (rs.next()){
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
            users.add(user);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return users;
}

@Override
public int save(User user) {
    try {
        conn = DBUtil.get_Conn();
        pstm = conn.prepareStatement("insert into adminuser(id,username,password) value (?,?,?)");
        pstm.setInt(1,user.getId());
        pstm.setString(2,user.getUsername());
        pstm.setString(3,user.getPassword());
        return pstm.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return 0;
}

@Override
public int delete(String id) {
    try {
        conn = DBUtil.get_Conn();
        pstm = conn.prepareStatement("delete from adminuser where id=?");
        pstm.setInt(1,Integer.valueOf(id));
        return pstm.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return 0;
}

@Override
public User findid(String id) {
    try {
        conn = DBUtil.get_Conn();
        pstm = conn.prepareStatement("select * from adminuser where id=?");
        pstm.setInt(1,Integer.valueOf(id));
        rs = pstm.executeQuery();
        while (rs.next()){
            user = new User();
            user.setId(rs.getInt("id"));
            user.setUsername(rs.getString("username"));
            user.setPassword(rs.getString("password"));
        }
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return user;
}

@Override
public int update(User user) {
    try {
        conn = DBUtil.get_Conn();
        pstm = conn.prepareStatement("update adminuser set username=? ,password=?  where id=?");
        pstm.setString(1,user.getUsername());
        pstm.setString(2,user.getPassword());
        pstm.setInt(3,user.getId());
        return pstm.executeUpdate();
    } catch (Exception e) {
        e.printStackTrace();
    }finally {
        try {
            DBUtil.get_CloseConn(rs, pstm, conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return 0;
}

}