Javaweb书城项目阶段02 - 通过JDBC实现对数据库的CRUD操作

项目需求

在 java 中通过 JDBC 实现数据库中数据的增删改查

---

准备工作:

① 新建一个 Module, 将第一阶段的静态页面导入 web 包下，并将数据库 jar 包放在 WEB-INF 下的 lib 中，右键 Add as Library.

② 在src下新建包 bean、dao、servlet、utils, 将 BaseDao 和 JDBCUtils 分别放在对应包下, 并将 druid.properties 配置文件放在 src 下.

③ 根据自己的数据库修改配置文件的参数, 以1.8为例

#key=value
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/bookstore?serverTimezone=UTC&characterEncoding=utf8&useSSL=false
#url=jdbc:mysql://localhost:3306/mysqldb
username=root
password=abc123
initialSize=10
minIdle=5
maxActive=20
maxWait=5000

---

连接数据库:

① 创建数据库 bookstore 和表 users (id, username, password, email), 并添加一条验证数据.

CREATE DATABASE IF NOT EXISTS bookstore CHARACTER SET utf8;

USE bookstore;

CREATE TABLE IF NOT EXISTS users(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
email VARCHAR(50) NOT NULL
);

INSERT INTO users VALUES(NULL,"liting","123456","liting@163.com");

SELECT * FROM users;

② 在bean包下创建表users对应的javabean类

package com.atguigu.bean;

public class User {
	//私有属性，对应数据库中的字段
    private Integer id; //包装类 优势：方便空值判断 方便类型转换
    private String username;
    private String password;
    private String email;
	//无参构造器
    public User() {
    }
	//带参构造器
    public User(Integer id, String username, String password, String email) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.email = email;
    }
	//属性对应的 get 和 set 方法
    public Integer getId() {
        return id;
    }

    public void setId(Integer 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;
    }
	//toString方法
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                '}';
    }
}

③ 书写User类对应的接口UserDao,编写需要实现的功能的方法

package com.atguigu.dao;
import com.atguigu.bean.User;

public interface UserDao{

    /**
     * 通过 用户名 & 密码 查询用户信息
     * select id,username,password,email from users where username = ? and password = ?
     * @param user
     * @return
     */
    public User getUser(User user);

    /**
     * 检查用户名是否存在
     * select id,username,password,email from users where username = ?
     * @param username
     * @return
     */
    public User CheckUserName(String username);

    /**
     * 添加User信息
     * insert into users(username,password,email) values(?,?,?)
     * @param user
     */
    public void saveUser(User user);
}

④ 在dao下新建一个impl包,创建UserDaoImpl实现类,编写具体的实现

package com.atguigu.dao.impl;

import com.atguigu.bean.User;
import com.atguigu.dao.BaseDao;
import com.atguigu.dao.UserDao;

public class UserDaoImpl extends BaseDao<User> implements UserDao {

    @Override
    public User getUser(User user) {
        String sql = "select id,username,password,email from users where username = ? and password = ?";
        return getBean(sql,user.getUsername(),user.getPassword());
    }

    @Override
    public User CheckUserName(String username) {
        String sql = "select id,username,password,email from users where username = ?";
        return getBean(sql,username);
    }

    @Override
    public void saveUser(User user) {
        String sql = "insert into users(username,password,email) values(?,?,?)";
        update(sql,user.getUsername(),user.getPassword(),user.getEmail());
    }
}

⑤ 新建一个test包,编写测试类,看是否连接数据库成功。

package com.atguigu.test;

import com.atguigu.bean.User;
import com.atguigu.dao.UserDao;
import com.atguigu.dao.impl.UserDaoImpl;

public class TestUserDao {
    public static void main(String[] args) {
        UserDao userdao = new UserDaoImpl();
        User user = userdao.getUser(new User(null, "liting", "123456","liting@163.com"));
        System.out.println("user = " + user);
    }
}

上述代码出现以下结果即可：

---

编写servlet:

① 修改表单提交的路径，创建对应的servlet并根据要求编写

package com.atguigu.test.servlet;

public class LoginServlet extends HttpServlet {
    private UserDao userDao = new UserDaoImpl();
    //private UserService userService = new UserServiceImpl();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //处理post请求乱码
        req.setCharacterEncoding("utf-8");
        //处理响应乱码
        resp.setContentType("text/html;charset=utf-8");

        //获取请求参数
        String username = req.getParameter("username");
        String password = req.getParameter("password");

        //调用dao
        User user = userDao.getUser(new User(null, username, password, null));

        //判断业务，选择路径
        if(user != null){
            //登录成功，重定向到login_success.html页面
            resp.sendRedirect(req.getContextPath() + "/pages/user/login_success.html");
        }else{
            //登录失败，转发到login.html页面
            req.getRequestDispatcher("/pages/user/login.html").forward(req,resp);
        }
    }

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

② 注册页面也与上述一致

package com.atguigu.test.servlet;

public class RegistServlet extends HttpServlet {
	
    private UserDao userDao = new UserDaoImpl();
    //private UserService userService = new UserServiceImpl();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //处理post请求乱码
        req.setCharacterEncoding("utf-8");
        //处理响应乱码
        resp.setContentType("text/html;charset=utf-8");

        //获取请求参数
        String username = req.getParameter("username");
        String pwd = req.getParameter("pwd");
        String email = req.getParameter("email");

        //调用dao
        User user = userDao.CheckUserName(username);

        if(user == null){
            //不存在，调用saveUser，重定向到regist_success.html
            userService.saveUser(new User(null,username,pwd,email));
            resp.sendRedirect(req.getContextPath()+"/pages/user/regist_success.html");
        }else{
            //存在，转发到regist.html
            req.getRequestDispatcher("/pages/user/regist.html").forward(req,resp);
        }

    }

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

② 配置xml即可测试

<servlet>
        <servlet-name>LoginServlet</servlet-name>
        <servlet-class>com.atguigu.test.servlet.LoginServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>LoginServlet</servlet-name>
        <url-pattern>/LoginServlet</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>RegistServlet</servlet-name>
        <servlet-class>com.atguigu.test.servlet.RegistServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>RegistServlet</servlet-name>
        <url-pattern>/RegistServlet</url-pattern>
    </servlet-mapping>

注册一个不存在的用户可以添加到数据库就成功了