Maven项目 cityFileFK02数据库连接（课外完成）

这篇文章是接着上面的 Maven项目 cityFileFK01课后完成的作业，上课可能要重新写，仅供参考。

1.数据库设计如下图：

2.文件目录分类如下：

3.DBUtil类

package util;

/*

连接数据的类

 */


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class DBUtil {
    private static String username;
    private static String password;
    private static String url;
    private static String driver;

    static {

        try {
            InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("db.properties");

            //  FileInputStream in=new FileInputStream("db.properties");
            System.out.println("in:" + in);
            Properties p = new Properties();
            p.load(in);

            username = p.getProperty("db.username");
            password = p.getProperty("db.password");
            url = p.getProperty("db.url");
            driver = p.getProperty("db.driver");

            System.out.println(username);
            System.out.println(password);
            System.out.println(url);
            System.out.println(driver);

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    //封装一个连接方法
    public static Connection getConn() {

        Connection conn = null;

        try {
            //加载驱动
            Class.forName(driver);
            conn = DriverManager.getConnection(url, username ,password);
            System.out.println("...........................");
            System.out.println(conn);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return conn;
    }

4.resources下的db.properties

db.username=root
db.password=123456
db.url=jdbc:mysql://localhost:3306/citylifefk?characterEncoding=utf8
db.driver=com.mysql.jdbc.Driver

5.dao下的UsersCDao类

package dao;

import util.DBUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class UsersCDao {
    private String username;
    private String password;

    public UsersCDao(String username, String password) {
        this.username = username;
        this.password = password;
    }

    public Integer selectStudent() throws SQLException {
        //获得连接
        Connection connection= DBUtil.getConn();
        //编辑数据库语句
        String sql="select role from tb_admin_user where uname=\""+username+"\"and pwd=\""+password+"\"";
        //建立statement对象和resuleset结果对象
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            int role=-1;
            int count=0;
            statement=connection.createStatement();
            resultSet=statement.executeQuery(sql);
            //用ResultSet的next方法判断是否有下一个
            while (resultSet.next()){
                //将相关的属性值引出来 并且存入到student对象中
                role=resultSet.getInt("role");
                count++;
            }
            if (count!=1){
                return -1;
            }
            return role;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            resultSet.close();
            statement.close();
            connection.close();
        }
        return -1;
    }
}

6.controller下的LoginServlet类

package controller;

import dao.UsersCDao;

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.sql.SQLException;

/*登录*/
@WebServlet(urlPatterns = "/toLogin")
public class LoginServlet extends HttpServlet {
    @Override
    protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //当前servlet默认编码为ISO8859-1不支持中文，应先设置中文编码格式
        req.setCharacterEncoding("utf-8");
        //接受参数（接收用户填写的信息）
        String username = req.getParameter("username");
        String password = req.getParameter("password");

        //根据用户名和密码查询当前用户信息是否存在，在dao里写
        //定义一个变量存储结果
        Integer result = null;
        UsersCDao usersCDao=new UsersCDao(username,password);
        try {
            //将查询结果返回给result
            result=usersCDao.selectStudent();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        //根据查询结果值不同进入不同页面，在这里只是原理演示，如果result等于0或1或2则分别为超级用户、普通用户、管理员用户
        if (result == 0){
            //使用重定向的方法 跳转到对应的页面
            resp.sendRedirect("index.jsp");
        }else if (result==1){
            resp.sendRedirect("index.jsp");
        }else if (result==2){
            resp.sendRedirect("index.jsp");
        }else {
            //如果查询结果不为0、1、2，则表明在用户名错误或者密码错误，返回错误提示界面
            resp.sendRedirect("error.jsp");
        }


    }

}

7.login.jsp

前端 login.jsp 页面提交用户名和密码

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@page isELIgnored="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="<%=request.getContextPath()%>/"/>
    <title>用户登录</title>
    <link type="text/css" rel="stylesheet" href="/css/style.css;">
</head>
<body bgcolor="#E7ECEF">
    <center>
        <form action="/toLogin" method="post">
            <table border="0" cellspacing="0" cellpadding="0" style="margin-top:130px">
                <tr><td><img src="/images/logon_top.gif"></td></tr>
                <tr height="180">
                    <td background="/images/logon_middle.gif" align="center" valign="top">
                         <table border="0" width="90%" cellspacing="0" cellpadding="0">
                             <tr height="50"><td colspan="2"></td></tr>
                             <tr height="30">
                                 <td align="right" width="40%">用户名：&nbsp;&nbsp;</td>
                                 <td style="text-indent:5px">
                                     <input type="text" name="username" size="30px" value="" id="log_Login_action_user_userName"/>
                                     <span>${error}</span>
                                 </td>
                             </tr>                
                             <tr height="30">
                                 <td align="right">密&nbsp;&nbsp;码：&nbsp;&nbsp;</td>
                                 <td style="text-indent:5px"><input type="password" name="password" size="30px" id="log_Login_action_user_userPassword"/></td>
                             </tr>
                             <tr height="60">
                                 <td></td>
                                 <td>
								
                                     <input type="submit" id="" value="登录"/>

                                     <input type="reset" value="重置"/>

                                     <a id="log_Login_action_" href="/view/indextemp.jsp">[返回首页]</a>
                                 </td>
                             </tr>
                         </table>
                    </td>
                </tr>
                <tr><td><img src="/images/logon_end.gif"></td></tr>
            </table>
        </form>



     
    </center>
</body>
</html>

8.index.jsp

用户名密码正确进入的页面 index.jsp

<html>
<body>
<h2>Hello World!</h2>
</body>
</html>

9.error.jsp

用户名密码错误进入的页面 error.jsp

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body >
<img src="/images/1.png">

</body>
</html>

10.运行结果：

运行tomcat，进入 http://localhost:8066/login
效果：

若用户名密码输入正确，进入http://localhost:8066/index.jsp

若用户名密码输入错误，进入http://localhost:8066/error.jsp