idea连接数据库实现简单Web项目----部门查询注册功能

下面是我用idea写的一个简单地web项目，中间使用了tomcat,maven等工具

实现功能：

1、输入人的名称，查询并输出此人对应的部门名称

2、如果查询成功，跳转到成功页面，显示对应的部门名称

3、如果查询失败，跳转到失败页面

4、跳转到失败页面你可以选择重新回到登录页面，或者是去注册

5、假设回到登录页面，则跳转到最开始的页面

6、假设去到注册页面，则在里面输入具体信息进行注册

7、注册成功则显示成功

8、注册失败则显示失败

数据库信息

create database mytest;

use mytest;


drop table department;

create table department
(
	departId int(11) not null primary key auto_increment,
    departName varchar(20) not null
)engine=InnoDB default charset=utf8;

insert into department (departName)
select 'DEV' union
select 'Sale' union
select 'Product' union
select 'Tech';

select * from department;

create table employee
(
	employeeId int(11) not null primary key auto_increment,
    employeeName varchar(20) not null,
    departNum int(11) not null
)engine=InnoDb default charset=utf8;

alter table employee
add constraint FK_DEPARTNUM foreign key(departNum) references department (departId) on delete cascade on update cascade;

insert into employee(employeeName,departNum)
select 'arvin',1 union
select 'jerry',2 union
select 'tom',3 union
select 'ming',4 ;

select * from employee;

idea中JDBC连接代码

import java.sql.*;

public class DBmanager {
    private Connection conn = null;
    private Statement state = null;
    private ResultSet rs = null;

    private String driver = "com.mysql.cj.jdbc.Driver";
    private String url = "jdbc:mysql://localhost:3306/mytest?user=root&password=root&&serverTimezone=GMT";

    public Connection getConnection(){
        try {
            Class.forName(driver);
            System.out.println("loading driver success.......");
            conn = DriverManager.getConnection(url);
            System.out.println("connection db success.....");
            return conn;
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return  conn;
    }


    // insert update delete
    public int nonQueryMethod(String strSql){
        int flag = 0;
        try {
            conn = this.getConnection();
            state = conn.createStatement();
            flag = state.executeUpdate(strSql);
            return flag;
        }catch (Exception ex){
            ex.printStackTrace();
        }
        return flag;
    }
    //select
    public String query(String strSql){
            String name=null;
        try {
            conn = this.getConnection();
            state = conn.createStatement();
            String front="select department.departName from department " +
                    "where department.departId=(select employee.departNum " +
                    "from employee where employee.employeeName=\"";
            String mark="\")";
            strSql=front+strSql+mark;
            rs = state.executeQuery(strSql);
            if(rs==null){
                return null;
            }
            while(rs.next()){
               name=rs.getString("departName");
            }
        }catch (Exception ex){
            ex.printStackTrace();
        }
       return name;
    }

    public void closeDb(){
        try {
            if(rs != null) rs.close();
            if(state!= null) state.close();
            if(conn != null) conn.close();
        }catch (Exception ex){
            ex.printStackTrace();
        }
    }

登录界面servlet代码

import DB.DBmanager;

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.SQLSyntaxErrorException;

@WebServlet("/loginServlet")
public class login 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 from_name = req.getParameter("username");
        String str = null;
            DBmanager db = new DBmanager();
            str = db.query(from_name);
            req.setAttribute("name", str);
            if (str != null) {
                req.getRequestDispatcher("success.jsp").forward(req, resp);
            }
            if (str == null)
                req.getRequestDispatcher("failure.jsp").forward(req, resp);
    }
}

登录界面jsp代码

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>login</title>
</head>
<body>
<form action="loginServlet" method="post">
    username:<input type="text" name="username"></br>
    <input type="submit" value="submit"></br>
    <input type="reset"  value="reset">
</form>
</body>
</html>

查询成功以后成功界面的jsp代码

<%@ page import="servlet.login" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>sucess</title>
</head>
<body>
  departName: <%=request.getAttribute("name") %>
</body>
</html>

查询失败以后失败界面的jsp代码

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>failure</title>
</head>
<body>
   <h3>The username which you put in is not exited!</h3></br>
   <h3>Choose to rigist?</h3></br>
   <a href=regisit.jsp>Go To regisit</a></br>
   <a href=login.jsp>Return to login and submit again!</a>
</body>
</html>

失败以后选择注册的注册界面jsp代码

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>注册</title>
</head>
<body>
<form action="regist" method="post">
    employeeName:<input type="text" name="employeeName"></br>
    employeeId  :<input type="text" name="employeeId"></br>
    departName  :<input type="text" name="departName"></br>
    <input type="submit" value="submit">
    <input type="reset" value="reset">

</form>
</body>
</html>

注册的servlet代码

import DB.DBmanager;

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(urlPatterns = "/regist")
public class regisit 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 from_employeeName=req.getParameter("employeeName");
        String from_employeeId=req.getParameter("employeeId");
        String from_departName=req.getParameter("departName");
        DBmanager db=new DBmanager();
        String front=" insert into employee values(";
        String strSql=front+from_employeeId+",\""+from_employeeName+"\""+","+from_departName+")";
        int num=db.nonQueryMethod(strSql);
        if(num!=0){
            req.getRequestDispatcher("successful.jsp").forward(req,resp);
        }else{
            req.getRequestDispatcher("fail.jsp").forward(req,resp);
        }
    }
}

注册成功界面的jsp代码

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>successful</title>
</head>
<body>
<h1>You have regisited successful!</h1>
</body>
</html>

注册失败界面的jsp代码

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>fail</title>
</head>
<body>
<h1>You failed!</h1>
</body>
</html>

全程截图




工程代码github链接
https://github.com/Fancccy/CodingHome/tree/master/query_servlet_jsp_javabean