下面是我用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