一.导入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;
}
}