JDBC+Servlet综合练习

---

需求

用户登录案例需求：

1. 编写login.jsp登录页面
   username & password 两个输入框
2. 使用Druid数据库连接池技术,操作mysql数据库中user表
3. 使用JdbcTemplate技术封装JDBC
4. 登录成功展示：登录成功
5. 登录失败展示：登录失败

---

项目文件结构(Servlet_Execer)

│  pom.xml
│
└─src
    │
    └─main
        ├─java
        │  ├─dao
        │  │  │  UserDao.java
        │  │  │
        │  │  └─impl
        │  │          UserDaoImplement.java
        │  │
        │  ├─domain
        │  │      User.java
        │  │
        │  ├─services
        │  │  │  UserServices.java
        │  │  │
        │  │  └─impl
        │  │          UserServicesImplement.java
        │  │
        │  ├─servlet
        │  │      LoginServlet.java
        │  │
        │  └─utils
        │          JdbcUtils.java
        │
        ├─resources
        │      druid.properties
        │
        └─webapp
            │  index.jsp
            │  login.jsp
            │
            └─WEB-INF
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

---

pom依赖配置

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>

    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>4.0.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/javax.servlet.jsp/javax.servlet.jsp-api -->
    <dependency>
      <groupId>javax.servlet.jsp</groupId>
      <artifactId>javax.servlet.jsp-api</artifactId>
      <version>2.3.3</version>
      <scope>provided</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/javax.el/javax.el-api -->
    <dependency>
      <groupId>javax.el</groupId>
      <artifactId>javax.el-api</artifactId>
      <version>3.0.1-b06</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/javax.servlet.jsp.jstl/jstl -->
    <dependency>
      <groupId>javax.servlet.jsp.jstl</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>

<!--    jdbc-mysql-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>8.0.29</version>
    </dependency>

<!--    druid-->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>druid</artifactId>
      <version>1.2.11</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
    <dependency>
      <groupId>org.springframework</groupId>
      <artifactId>spring-jdbc</artifactId>
      <version>5.3.21</version>
    </dependency>

  </dependencies>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

---

resources

druid.properties

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstu
username=root
password=123456
1
2
3
4

---

domain

package domain;

public class User {

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

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

---

dao

UserDao

package dao;

import domain.User;

public interface UserDao {

    /**
     * 查询用户
     * @param username
     * @param password
     * @return user
     */
    User findUser(String username, String password);
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

impl（UserDaoImplement）

package dao.impl;

import dao.UserDao;
import domain.User;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import utils.JdbcUtils;

import javax.sql.DataSource;

public class UserDaoImplement implements UserDao {

    @Override
    public User findUser(String username, String password) {
        DataSource dataSource = JdbcUtils.getDataSource();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String sql = "select * from user where username = ? and password = ?";
        User user;
        try {
            // queryForObject期待结果为1,否则报错 此处设置为null,避免报错
            user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<User>(User.class), username, password);
        } catch (EmptyResultDataAccessException e) {
            user = null;
        }
        return user;
    }

}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

---

services

UserServices

package services;

public interface UserServices {
    boolean userLogin(String username, String password);
}

1
2
3
4
5
6

impl(UserServicesImplement )

package services.impl;

import dao.UserDao;
import dao.impl.UserDaoImplement;
import services.UserServices;

public class UserServicesImplement implements UserServices {
    @Override
    public boolean userLogin(String username, String password) {
        UserDao userDao = new UserDaoImplement();
        return userDao.findUser(username, password) != null;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13

---

servlet

LoginServlet

package servlet;

import services.UserServices;
import services.impl.UserServicesImplement;

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;

@WebServlet("/loginServlet")
public class LoginServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        // 获取参数
        String userNmaes = req.getParameter("username");
        String password = req.getParameter("password");
        // 验证用户
        UserServices userServices = new UserServicesImplement();
        boolean flag = userServices.userLogin(userNmaes, password);
        // 设置响应编码 内容
        resp.setContentType("text/html;charset=utf-8");
        if(flag) {
            resp.getWriter().write("登录成功");
        } else {
            resp.getWriter().write("登录失败");
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

---

utils

package utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class JdbcUtils {
    private static DataSource dataSource;

    static {
        try {
            Properties pro = new Properties();
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            pro.load(in);
            dataSource = DruidDataSourceFactory.createDataSource(pro);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /**
     * 获取Datasource
     */
    public static DataSource getDataSource() {
        return dataSource;
    }

    /**
     * 释放资源
     */
    public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
        try {
            if(resultSet != null) {
                resultSet.close();
            }
            if(preparedStatement != null) {
                preparedStatement.close();
            }
            if(connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60

---

webapp

login.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>LoginPage</title>
    <style>
        body {
            text-align: center;
        }
        .container {
            display: inline-block;
            border: 1px solid grey;
            padding: 20px;
            margin-top: 150px;
            border-radius: 5px;
            box-shadow: 5px 5px;
        }
        .password {
            margin-top: 10px;
        }
        .button {
            margin-top: 30px;
        }
    </style>
</head>

<body>
<div class="container">
    <form action="/loginServlet" method="post">
        <div class="userinfo">
            <div class="username">
                <label for="username">用户</label>
                <input type="text" name="username" id="username">
            </div>
            <div class="password">
                <label for="password">密码</label>
                <input type="text" name="password" id="password">
            </div>
        </div>
        <div class="button">
            <input type="submit" value="登录">
        </div>
    </form>
</div>
</body>
</html>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

---

tomcat

运行

成功登录

访问 http://localhost:8080/login.jsp
1

登录失败