- JDBC+Servlet综合练习
- 需求
- 项目文件结构(Servlet_Execer)
- pom依赖配置
- resources
- domain
- dao
- services
- servlet
- utils
- webapp
- tomcat
- 运行
需求
用户登录案例需求：
- 编写login.jsp登录页面
username & password 两个输入框 - 使用Druid数据库连接池技术,操作mysql数据库中user表
- 使用JdbcTemplate技术封装JDBC
- 登录成功展示：登录成功
- 登录失败展示：登录失败
项目文件结构(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
pom依赖配置
junit junit 4.11 test javax.servlet javax.servlet-api 4.0.1 javax.servlet.jsp javax.servlet.jsp-api 2.3.3 provided javax.el javax.el-api 3.0.1-b06 javax.servlet.jsp.jstl jstl 1.2 mysql mysql-connector-java 8.0.29 com.alibaba druid 1.2.11 org.springframework spring-jdbc 5.3.21
resources
druid.properties
driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstu username=root password=123456
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 +
", username='" + username + ''' +
", password='" + password + ''' +
'}';
}
}
dao
UserDao
package dao;
import domain.User;
public interface UserDao {
User findUser(String username, String password);
}
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.class), username, password);
} catch (EmptyResultDataAccessException e) {
user = null;
}
return user;
}
}
services
UserServices
package services;
public interface UserServices {
boolean userLogin(String username, String password);
}
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;
}
}
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("登录失败");
}
}
}
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();
}
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();
}
}
}
webapp
login.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
LoginPage
tomcat 运行
成功登录
访问 http://localhost:8080/login.jsp
登录失败
