northboat / neuqhelper Goto Github PK

package DBUtils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class DBCP {

    static public ComboPooledDataSource users = new ComboPooledDataSource("users");

    static public ComboPooledDataSource searchers = new ComboPooledDataSource("ques&ans");
}

package DBUtils;


import java.sql.*;
import java.util.*;

public class DBUtilsForQuesAndRes {

    private Connection con = null;

    public HashMap<String, List<String>> getAns(String ques) throws SQLException {
        //获取一个连接
        con = DBCP.searchers.getConnection();
        HashMap<String, List<String>> res = new HashMap<>();
        //一个连接对应一条语句！如果中途执行第二条语句，第一条语句的set将被销毁
        Statement sql1 = con.createStatement();
        Statement sql2 = con.createStatement();
        String SQL = "select * from questions where question like '%" + ques + "%'";
        ResultSet question = sql1.executeQuery(SQL);
        while(question.next()){
            String num = question.getString("num");
            String q = question.getString("question");
            res.put(num+". "+q, new ArrayList<String>());
            SQL = "select * from answers where num = " + num;
            ResultSet ans = sql2.executeQuery(SQL);
            while(ans.next()){
                res.get(num+". "+q).add((ans.getString("answer")));
            }
        }
        if(res.keySet().size() == 0){
            con.close();
            return null;
        }
        con.close();
        return res;
    }

    public List<String> getPointedAns(String num) throws SQLException{
        //获取一个连接
        con = DBCP.searchers.getConnection();
        Statement sql = con.createStatement();
        String SQL = "select * from answers where num = '" + num + "'";
        ResultSet ans = sql.executeQuery(SQL);
        List<String> res = new ArrayList<>();
        while(ans.next()){
            res.add(ans.getString("answer"));
        }
        con.close();
        return res;
    }

    public boolean releaseQues(String ques, String type) throws SQLException{
        //获取一个连接
        con = DBCP.searchers.getConnection();
        //获取问题序号
        //设置连接属性：可滚动，可修改数据库
        Statement sql = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
        String SQL = "select * from questions";
        ResultSet question = sql.executeQuery(SQL);
        question.last();
        int num = Integer.parseInt(question.getString("num")) + 1;
        //向后插入数据
        String record = "('" + num + "', '" + ques + "', " + "'" + "', '" + type + "')";
        SQL = "insert into questions values " + record;
        int ok = sql.executeUpdate(SQL);
        if(ok == 0){
            con.close();
            return false;
        }
        con.close();
        return true;
    }

    public boolean releaseAns(String num, String ans) throws SQLException{
        con = DBCP.searchers.getConnection();
        Statement sql = con.createStatement();
        String record = "('" + num + "', '" + ans + "', '', '', '')";
        String SQL = "insert into answers values" + record;
        int ok = sql.executeUpdate(SQL);
        if(ok == 0){
            con.close();
            return false;
        }
        con.close();
        return true;
    }

    public List<String> getQues(String type) throws SQLException{
        con = DBCP.searchers.getConnection();
        List<String> questions = new ArrayList<>();
        Statement sql = con.createStatement();
        String SQL = "select * from questions where type = '" + type +"'";
        ResultSet set = sql.executeQuery(SQL);
        while(set.next()){
            questions.add(set.getString("num") + ". " + set.getString("question"));
        }
        con.close();
        return questions;
    }
}

package DBUtils;

import Controller.Account;
import Controller.Student;
import Controller.Teacher;


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


/**
 * 要注意注册、登录、修改密码的顺序，连接在各个函数中均有关闭命令（遇挫则关闭）
 */
public class DBUtilsForUser {

    private Connection con = null;


    //判断账号（account）是否存在，存在则返回数据库连接，不存在则返回null
    protected Connection isExist(String nums) throws SQLException {
        con = DBCP.users.getConnection();
        Statement sql = con.createStatement();
        String SQL = "select * from account where num = '" + nums + "'";
        ResultSet set = sql.executeQuery(SQL);
        if(!set.next()){
            con.close();
            return null;
        }
        return con;
    }


    /**登录验证
    * 若账号不存在，返回null，输出账号不存在
    * 若账号存在但未注册，返回null，输出尚未注册
    * 若账号已注册但密码错误，返回null，输出密码错误
    * 登录成功，返回账户信息
     */
    public ResultSet verify(Account a) throws SQLException{
        Connection con = isExist(a.getNums());
        if(con == null){
            System.out.println("账户不存在");
            return null;
        }
        Statement sql = con.createStatement();
        String SQL = "select * from " + a.getTable() + " where nums = '" + a.getNums() + "'";
        ResultSet set = sql.executeQuery(SQL);
        if(!set.next()){
            System.out.println("该账户未注册，请先注册");
            con.close();
            return null;
        }
        String p = set.getString("password");
        //比较字符串用equals()方法
        if(!a.getPassword().equals(p))
        {
            con.close();
            System.out.println("密码错误");
            return null;
        }
        return set;
    }


    /**学生用户注册
    * 账号不存在，返回false，输出账号不存在
    * 账号存在但已注册，返回false，输出账号已注册
    * 账号存在且未注册，录入学生信息，未报错，返回true
    * 账号、密码、名字为必填项
     */
    public boolean registerForStudent(Student stu) throws SQLException{
        Connection con = isExist(stu.getAccount().getNums());
        if(con == null){
            System.out.println("账号不存在");
            return false;
        }
        Statement sql = con.createStatement();
        String SQL = "select * from student where nums = " + stu.getAccount().getNums();
        ResultSet set = sql.executeQuery(SQL);
        if(set.next()){
            System.out.println("账号已被注册");
            con.close();
            return false;
        }

        //获取用户名字
        SQL = "select * from account where num = " + stu.getAccount().getNums();
        ResultSet getName = sql.executeQuery(SQL);
        getName.next();
        String name = getName.getString("name");

        //在数据库中注册信息
        String record = "('" + stu.getAccount().getNums() + "', '" + stu.getAccount().getPassword() + "', '"
                + name + "', '" + stu.getMajor() + "', '" + stu.getGrade() + "', '" +  stu.getGender() + "')";
        SQL = "insert into student values " + record;
        int ok = sql.executeUpdate(SQL);
        if(ok == 0){
            System.out.println("注册失败，请重试");
            con.close();
            return false;
        }
        System.out.println("注册成功,请登录");
        con.close();
        return true;
    }


    /**老师用户注册
    * 账号不存在，返回false，输出账号不存在
    * 账号存在但已注册，返回false，输出账号已注册
    * 账号存在且未注册，录入学生信息，未报错，返回true
    * 账号、密码、名字为必填项
     */
    public boolean registerForTeacher(Teacher tea) throws SQLException{
        Connection con = isExist(tea.getAccount().getNums());
        if(con == null){
            System.out.println("账号不存在");
            return false;
        }
        Statement sql = con.createStatement();
        String SQL = "select * from student where nums = " + tea.getAccount().getNums();
        ResultSet set = sql.executeQuery(SQL);
        if(set.next()){
            System.out.println("账号已被注册");
            con.close();
            return false;
        }

        //获取用户名字
        SQL = "select * from account where num = " + tea.getAccount().getNums();
        ResultSet getName = sql.executeQuery(SQL);
        getName.next();

        String name = getName.getString("name");
        String record = "('" + tea.getAccount().getNums() + "', '" + tea.getAccount().getPassword() + "', '"
                + name + "', '" + tea.getMajor() + "', '" + tea.getGender() + "', '" +  tea.getPosition() + "')";
        SQL = "insert into teacher values " + record;
        int ok = sql.executeUpdate(SQL);
        if(ok == 0){
            System.out.println("注册失败，请重试");
            con.close();
            return false;
        }
        System.out.println("注册成功,请登录");
        con.close();
        return true;
    }


    /**修改密码
     * 从表中拿到账号对应密码，与输入旧密码对比
     * 若匹配，修改旧密码为新密码，返回true；反之返回false
     */
    public boolean modifyPassword(String table, String nums, String oldPassword, String newPassword) throws  SQLException{
        Connection con = isExist(nums);
        if(con==null){
            System.out.println("账号错误");
            return false;
        }
        Statement sql = con.createStatement();
        String SQL = "select * from " + table + " where nums = " + nums;
        ResultSet set = sql.executeQuery(SQL);
        set.next();
        String realPassword = set.getString("password");
        if(!oldPassword.equals(realPassword)){
            System.out.println("旧密码错误");
            con.close();
            return false;
        }
        SQL = "update " + table + " set password = '" + newPassword + "'where nums = '" + nums + "'";
        int ok = sql.executeUpdate(SQL);
        if(ok == 0){
            System.out.println("更新数据失败，请重试");
            con.close();
            return false;
        }
        System.out.println("修改密码成功");
        con.close();
        return true;
    }

    public void exit(){
        try{
            con.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
}

package Controller;

public class Account{
    private String table;
    private String nums;
    private String password;

    public Account(String table, String nums, String password) {
        this.table = table;
        this.nums = nums;
        this.password = password;
    }

    public Account(String table) {
        this.table = table;
    }

    public String getTable() {
        return table;
    }

    public String getNums() {
        return nums;
    }

    public void setNums(String nums) {
        this.nums = nums;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }
}

package Controller;

public class Info {
    private String nums;
    private String gender;
    private String major;

    public Info(String nums, String major, String gender) {
        this.nums = nums;
        this.gender = gender;
        this.major = major;
    }

    public String getNums() {
        return nums;
    }

    public String getMajor() {
        return major;
    }

    public String getGender() {
        return gender;
    }

    @Override
    public String toString() {
        return getNums()+"\t"+getMajor()+"\t"+getGender();
    }
}

package Controller;

import DBUtils.DBUtilsForQuesAndRes;

import java.sql.SQLException;


public class Releaser {

    private DBUtilsForQuesAndRes jdbc;

    public Releaser(){
        jdbc = new DBUtilsForQuesAndRes();
    }

    public boolean releaseQues(String ques, String type) throws SQLException{
        return jdbc.releaseQues(ques, type);
    }

    public boolean releaseAns(String num, String ans) throws SQLException{
        return jdbc.releaseAns(num, ans);
    }
}

package Controller;

import DBUtils.DBUtilsForQuesAndRes;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

public class Searcher {

    private DBUtilsForQuesAndRes jdbc;

    public Searcher() throws SQLException{
        jdbc = new DBUtilsForQuesAndRes();
    }

    public HashMap<String, List<String>> getAns(String question) throws SQLException {
        return jdbc.getAns(question);
    }

    public List<String> getQues(String type) throws SQLException{
        return jdbc.getQues(type);
    }

    public List<String> getPointedAns(String num) throws SQLException{
        return jdbc.getPointedAns(num);
    }
}

package Controller;

import DBUtils.DBUtilsForUser;

import java.sql.ResultSet;
import java.sql.SQLException;

public class Student{
    private Account account;
    private String name;
    private String major;
    private String grade;
    private String gender;
    private DBUtilsForUser jdbc = new DBUtilsForUser();

    public Student(Account a){
        account = a;
    }

    public Student(Account account, String name, String major, String grade, String gender) {
        this.account = account;
        this.name = name;
        this.major = major;
        this.grade = grade;
        this.gender = gender;
    }

    public Account getAccount() { return account; };

    public String getName() {
        return name;
    }

    public String getMajor() {
        return major;
    }

    public String getGrade() {
        return grade;
    }

    public String getGender() {
        return gender;
    }

    public void setName(String name) {
        this.name = name;
    }

    public void setMajor(String major) {
        this.major = major;
    }

    public void setGrade(String grade) {
        this.grade = grade;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public ResultSet login() throws SQLException{
        return jdbc.verify(this.account);
    }

    public boolean register() throws SQLException{
        return jdbc.registerForStudent(this);
    }

    public boolean change(String newPassword) throws SQLException{
        return jdbc.modifyPassword("student", this.account.getNums(), this.account.getPassword(), newPassword);
    }

    public void exit(){
        jdbc.exit();
    }
}

package Servlet;

import Controller.Account;
import Controller.Student;
import Controller.Teacher;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;

public class ChangePassword extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        String table = req.getParameter("table");
        String nums = req.getParameter("username");
        String password = req.getParameter("password");
        String newPassword = req.getParameter("newPassword");
        boolean resetSuccessfully = false;
        Account acc = new Account(table, nums, password);
        if(table.equals("student")){
            try{
                Student stu = new Student(acc);
                resetSuccessfully = stu.change(newPassword);
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        else if(table.equals("teacher")){
            try{
                Teacher tea = new Teacher(acc);
                resetSuccessfully = tea.change(newPassword);
            }catch (SQLException e){
                e.printStackTrace();
            }
        }
        if(resetSuccessfully){
            resp.sendRedirect("/NEUQHelper/hello/Login.jsp");
        }else{
            resp.sendRedirect("/NEUQHelper/hello/ChangePassword.jsp");
        }
    }

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

package Servlet;

import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;


public class Distribute extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        String modular = req.getParameter("option");
        //用cookie判断用户是否登录，若未登录，返回登录界面
        String isLogin = null;
        Cookie[] cookies = req.getCookies();
        for(Cookie c: cookies){
            if(c.getName().equals("isLogin")){
                isLogin = c.getValue();
            }
        }
        //判断是否登录，排除直接通过url进入的用户
        if(modular == null || isLogin.equals("no") || isLogin == null){
            System.out.println("请先登录");
            resp.sendRedirect("/NEUQHelper/hello/Login.jsp");
        }
        else{
            if(modular.equals("<h2>校内外美食推荐</h2>")){
                req.getSession().setAttribute("type", "Eating");
            }
            else if(modular.equals("<h2>对缺德地图说NO</h2>")){
                req.getSession().setAttribute("type", "Outing");
            }
            else if(modular.equals("<h2>就是玩儿</h2>")){
                req.getSession().setAttribute("type", "Entertainment");
            }
            else if(modular.equals("<h2>住寝、修电脑...</h2>")){
                req.getSession().setAttribute("type", "DailyLife");
            }
            else if(modular.equals("<h2>图书馆、实验室...</h2>")){
                req.getSession().setAttribute("type", "Study");
            }
            else if(modular.equals("<h2>社团活动、体育赛事...</h2>")){
                req.getSession().setAttribute("type", "Sports");
            }
            resp.sendRedirect("/NEUQHelper/hello/ShowQuesInfo.jsp");
        }
    }

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

package Servlet;

import Controller.Admin;

import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;

public class SetAcc extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //用cookie判断用户是否登录，若未登录，返回登录界面
        String isLogin = null;
        Cookie[] cookies = req.getCookies();
        for(Cookie c: cookies){
            if(c.getName().equals("isLogin")){
                isLogin = c.getValue();
            }
        }
        if(isLogin.equals("no") || isLogin==null){
            resp.sendRedirect("/NEUQHelper/hello/Login.jsp");
        }
        else{
            boolean setSuccessfully = false;
            try{
                req.setCharacterEncoding("utf-8");
                resp.setCharacterEncoding("utf-8");
                String setInfo = req.getParameter("setInfo");
                if(!setInfo.equals("") && setInfo!=null && setInfo.contains(":")){
                    String nums = setInfo.substring(0, setInfo.indexOf(':'));
                    String name = setInfo.substring(nums.length()+1);
                    Admin admin = (Admin)req.getSession().getAttribute("admin");
                    setSuccessfully = admin.setAccount(nums, name);
                }
            }catch (SQLException e){
                e.printStackTrace();
                System.out.println("数据库错误");;
            }
            if(setSuccessfully){
                resp.sendRedirect("/NEUQHelper/hello/SetSuccessfully.jsp");
            }else{
                resp.sendRedirect("/NEUQHelper/hello/SetFailed.jsp");
            }
        }
    }

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

long time = System.currentTimeMills();
java.util.Date date = new Date(time);
int hours = date.getHours();

resp.setCharacterEncoding("utf-8");
resp.setContentType("text/html");
req.setCharacterEncoding("utf-8");

//将str转化为指定编码，返回一个String
URLEncoder.encode(String str, String encodeType);

out.write("<br>");