【Servlet+JDBC+HTML+MySQL+】------查询综合案例

墨蓝 2024-03-30 12:40 63阅读 0赞

> ### 分享第二十四篇励志语句 ###
> 
> #### 对自己狠一点，逼自己努力，再过五年你将会感谢今天发狠的自己，恨透今天懒惰自卑的自己，既然认准了一条路，那就坚持走下去，命运不会辜负每一个认真而努力的人。 ####

![7b82d0ea9ca7480b9e2849a438431db7.png][]

**目录**

分享第二十四篇励志语句

1 案例需求

2 创建表admin并添加数据

3 创建Web项目

4 database.properties文件

5 DBUtile类代码

6 Admin实体类

7 AdminDao接口

8 AdminDaoImpl实现类

9 AdminService接口

10 AdminServiceImpl实现类

11 HTML页面

11.1 login.html页面

11.2 login.css

11.3 table.css

12 LoginServlet

13 ShowAllAdminServlet

--------------------

在MySQL中新建一个servletdatabase数据库，专门用来学习servlet操作数据库

![ad02b9a32c58431480869226418ae604.png][]

### 1 案例需求 ###

实现登录功能，登录成功后显示所有管理员信息，登录失败给出“账号或密码错误，无法登录”提示信息

### 2 创建表admin并添加数据 ###

#创建表admin
    CREATE TABLE IF NOT EXISTS `admin`(
    `username` VARCHAR(20) PRIMARY KEY,
    `password` VARCHAR(20) NOT NULL,
    `phone` VARCHAR(11) UNIQUE NOT NULL,
    `address` VARCHAR(20) NOT NULL
    );
    
    #向admin表中插入数据
    INSERT INTO `admin`(`username`,`password`,`phone`,`address`)
    VALUES('张三','123456','13112345678','安徽合肥蜀山区');
    
    INSERT INTO `admin`(`username`,`password`,`phone`,`address`)
    VALUES('李四','123456','13822334455','安徽合肥高新区');

### 3 创建Web项目 ###

创建Web项目adminProject01，在项目下创建包目录结构如下，并导入相关jar包及配置文件

*  com.cxyzxc.www.dao包：数据访问层接口
 *  com.cxyzxc.www.dao.impl包：数据访问层接口实现类
 *  com.cxyzxc.www.entity包：实体类
 *  com.cxyzxc.www.service包：业务逻辑层接口
 *  com.cxyzxc.www.service.impl包：业务逻辑层接口实现类
 *  com.cxyzxc.www.servlet包：Servlet类
 *  com.cxyzxc.www.utils包：工具类
 *  database.properties:数据库连接及连接池配置文件

### 4 database.properties文件 ###

![7114030ac065428f9faf85b27a4ef882.png][]

###  ###

### 5 DBUtile类代码 ###

package com.cxyzxc.www.utils;
    
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.Properties;
    import javax.sql.DataSource;
    import com.alibaba.druid.pool.DruidDataSource;
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    
    public class DBUtils {
    
    	// 声明一个连接池对象
    	private static DruidDataSource druidDataSource;
    
    	static {
    		// 实例化配置文件对象
    		Properties properties = new Properties();
    
    		try {
    			// 加载配置文件内容
    			InputStream is = DBUtils.class
    					.getResourceAsStream("/database.properties");
    			properties.load(is);
    			// 创建连接池
    			druidDataSource = (DruidDataSource) DruidDataSourceFactory
    					.createDataSource(properties);
    		} catch (IOException e) {
    			e.printStackTrace();
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    	}
    
    	//返回一个数据源
    	public static DataSource getDataSource(){
    		return druidDataSource;
    	}
    
    }

### 6 Admin实体类 ###

package com.cxyzxc.www.entity;
    
    public class Admin {
    
        private String username;
        private String password;
        private String phone;
        private String address;
    
        public Admin() {
        }
    
        public Admin(String username, String password, String phone, String address) {
            this.username = username;
            this.password = password;
            this.phone = phone;
            this.address = address;
        }
    
        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 getPhone() {
            return phone;
        }
    
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        @Override
        public String toString() {
            return "Admin{" +
                    "username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    ", phone='" + phone + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

### 7 AdminDao接口 ###

package com.cxyzxc.www.dao;
    
    import com.cxyzxc.www.entity.Admin;
    
    import java.util.List;
    
    public interface AdminDao {
    
        //查询用户（查询单个）
        Admin selectOneByUsernameAndPassword(String username, String password);
    
        //查询所有用户
        List<Admin> selectAll();
    }

### 8 AdminDaoImpl实现类 ###

package com.cxyzxc.www.dao.impl;
    
    import com.cxyzxc.www.dao.AdminDao;
    import com.cxyzxc.www.entity.Admin;
    import com.cxyzxc.www.utils.DBUtils;
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    
    import java.sql.SQLException;
    import java.util.List;
    
    public class AdminDaoImpl implements AdminDao {
        // 创建QueryRunner对象，并传递一个数据源对象
        private final QueryRunner QUERYRUNNER = new QueryRunner(DBUtils.getDataSource());
    
        @Override
        public Admin selectOneByUsernameAndPassword(String username, String password) {
            String sql = "SELECT * FROM `admin` WHERE `username` = ? AND `PASSWORD`=?;";
            Object[] args = {username, password};
            try {
                return QUERYRUNNER.query(sql, new BeanHandler<Admin>(Admin.class), args);
            } catch (SQLException e) {
                e.printStackTrace();
    
            }
    
            return null;
        }
    
    
        @Override
        public List<Admin> selectAll() {
            String sql = "SELECT * FROM `admin`;";
            try {
                return QUERYRUNNER.query(sql, new BeanListHandler<Admin>(Admin.class));
            } catch (SQLException e) {
                e.printStackTrace();
            }
    
            return null;
        }
    
    }

### 9 AdminService接口 ###

package com.cxyzxc.www.service;
    
    import com.cxyzxc.www.entity.Admin;
    
    import java.util.List;
    
    public interface AdminService {
    
        Admin login(String username, String password);
    
        List<Admin> selectAllAdmin();
    }

### 10 AdminServiceImpl实现类 ###

package com.cxyzxc.www.service.impl;
    
    import com.cxyzxc.www.service.AdminService;
    import com.cxyzxc.www.dao.AdminDao;
    import com.cxyzxc.www.dao.impl.AdminDaoImpl;
    import com.cxyzxc.www.entity.Admin;
    
    import java.util.List;
    
    public class AdminServiceImpl implements AdminService {
        private final AdminDao ADMINDAO = new AdminDaoImpl();
    
        @Override
        public Admin login(String username, String password) {
            return ADMINDAO.selectOneByUsernameAndPassword(username, password);
        }
    
        @Override
        public List<Admin> selectAllAdmin() {
            return ADMINDAO.selectAll();
        }
    }

### 11 HTML页面 ###

#### 11.1 login.html页面 ####

<!DOCTYPE html>
    <html lang="en">
        <head>
            <meta charset="UTF-8">
            <title>登录页面</title>
            <link type="text/css" rel="stylesheet" href="css/login.css"/>
        </head>
    
        <body>
            <div>
                <form action="LoginServlet" method="post">
                    <p>
                        账号：<input type="text" name="username"/>
                    </p>
                    <p>
                        密码：<input type="password" name="password"/>
                    </p>
                    <p>
                        <input type="submit" value="登录"/>
                    </p>
                </form>
            </div>
        </body>
    </html>

#### 11.2 login.css ####

* {
        margin: 0;
        padding: 0;
    }
    
    div {
        width: 400px;
        height: 100px;
        background-color: #ccc;
        margin: 30px auto;
        padding-top: 30px;
        text-align: center;
    }
    
    p {
        margin-top: 10px;
    }
    
    input {
        outline: none;
    }

#### 11.3 table.css ####

*{
        margin: 0;
        padding: 0;
    }
    
    table{
        margin: 20px auto;
        width: 500px;
        height: 100px;
        text-align: center;
    }

###  ###

### 12 LoginServlet ###

package com.cxyzxc.www.servlet;
    
    import com.cxyzxc.www.service.AdminService;
    import com.cxyzxc.www.service.impl.AdminServiceImpl;
    import com.cxyzxc.www.entity.Admin;
    
    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.io.PrintWriter;
    
    @WebServlet("/LoginServlet")
    public class LoginServlet extends HttpServlet {
        private final AdminService ADMINSERVICE = new AdminServiceImpl();
    
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
            req.setCharacterEncoding("UTF-8");
            //获取数据
            String username = req.getParameter("username");
            String password = req.getParameter("password");
    
            Admin admin = ADMINSERVICE.login(username, password);
    
            resp.setContentType("text/html;charset=UTF-8");
            PrintWriter printWriter = resp.getWriter();
    
            if (admin != null) {
                printWriter.println("<html>");
                printWriter.println("<head>");
                printWriter.println("<title>登录成功</title>");
                printWriter.println("</head>");
                printWriter.println("<body>");
                printWriter.println("<h2>登录成功</h2>");
                printWriter.println("</body>");
                printWriter.println("</html>");
            } else {
                printWriter.println("<html>");
                printWriter.println("<head>");
                printWriter.println("<title>登录失败</title>");
                printWriter.println("</head>");
                printWriter.println("<body>");
                printWriter.println("<h2>账号或密码错误，无法登录</h2>");
                printWriter.println("</body>");
                printWriter.println("</html>");
    
            }
    
        }
    
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
            doGet(req, resp);
        }
    }

### 13 ShowAllAdminServlet ###

package com.cxyzxc.www.servlet;
    
    import com.cxyzxc.www.service.AdminService;
    import com.cxyzxc.www.service.impl.AdminServiceImpl;
    import com.cxyzxc.www.entity.Admin;
    
    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.io.PrintWriter;
    import java.util.List;
    
    @WebServlet("/ShowAllAdminServlet")
    public class ShowAllAdminServlet extends HttpServlet {
        private final AdminService ADMINSERVICE = new AdminServiceImpl();
    
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
            req.setCharacterEncoding("UTF-8");
            resp.setContentType("text/html;charset=UTF-8");
    
            PrintWriter printWriter = resp.getWriter();
            List<Admin> adminList = ADMINSERVICE.selectAllAdmin();
            if (adminList.size() != 0) {
                printWriter.println("<html>");
                printWriter.println("<head>");
                printWriter.println("<title>所有admin</title>");
                printWriter.println("<link type=\"text/css\" rel=\"stylesheet\" href=\"css/table.css\" />");
                printWriter.println("</head>");
                printWriter.println("<table border='1px' cellspacing='0'>");
                printWriter.println("<tr>");
                printWriter.println("<th>账号</th>");
                printWriter.println("<th>密码</th>");
                printWriter.println("<th>手机号码</th>");
                printWriter.println("<th>住址</th>");
                printWriter.println("<th>操作</th>");
                printWriter.println("</tr>");
                for (Admin admin : adminList) {
                    printWriter.println("<tr>");
                    printWriter.println("<td>" + admin.getUsername() + "</td>");
                    printWriter.println("<td>" + admin.getPassword() + "</td>");
                    printWriter.println("<td>" + admin.getPhone() + "</td>");
                    printWriter.println("<td>" + admin.getAddress() + "</td>");
                    printWriter.println("<td><a href=\"#\">修改</a> <a href=\"#\">删除</a></td>");
                    printWriter.println("</tr>");
                }
                printWriter.println("</table>");
                printWriter.println("</html>");
            } else {
                printWriter.println("<html>");
                printWriter.println("<head>");
                printWriter.println("<title>所有admin</title>");
                printWriter.println("</head>");
                printWriter.println("<body>");
                printWriter.println("<h2>当前没有用户</h2>");
                printWriter.println("</body>");
                printWriter.println("</html>");
            }
    
        }
    
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws IOException {
            doGet(req, resp);
        }
    }

好了，这些就是单独查和全部查的全部方法，感谢大家支持

#### 今天的分享就到此结束了 ####

#### **创作不易点赞评论互关三连** ####

![c7a579659984427c951f24021bdb4be8.gif][]

[7b82d0ea9ca7480b9e2849a438431db7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/df8fefd89bb340b3b90773dc388bc659.png
[ad02b9a32c58431480869226418ae604.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/f2774e7a8fdb495e8902ff662d40521d.png
[7114030ac065428f9faf85b27a4ef882.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/0b252d4f28604838a9d88d698a4096f4.png
[c7a579659984427c951f24021bdb4be8.gif]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/5f26f7046b794439a16da4914495241e.gif