上机考试测试项目
一、设计数据库
二、domain包装类
package cn.edu.xit.domain;
public class User {
private int id;
private String username;
private String password;
private String jobs;
private String phone;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
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 getJobs() {
return jobs;
}
public void setJobs(String jobs) {
this.jobs = jobs;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
三、Dao层
package cn.edu.xit.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import com.mchange.v2.c3p0.cfg.C3P0ConfigUtils;
import com.sun.org.apache.bcel.internal.generic.NEW;
import cn.edu.xit.domain.User;
import cn.edu.xit.util.C3p0Utils;
public class UserDao {
//根据username和password查找user表
public User findUserByUsernameandPwd(String username,String password) throws SQLException{
QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from user where username = ? and password = ?";
User user = queryRunner.query(sql,new BeanHandler<User>(User.class),username,password);
return user;
}
public List<User> findUser() throws SQLException{
QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select * from user";
List<User> user = queryRunner.query(sql,new BeanListHandler<User>(User.class));
return user;
}
//根据id查询 user表数据
public User findUserById(int id) throws SQLException{
QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select from user where id = ?";
User user = queryRunner.query(sql,new BeanHandler<User>(User.class),id);
return user;
}
//根据username使用模糊查询 user表数据
public List<User> findUserByUsername(String username) throws SQLException{
QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
List<Object> list = new ArrayList<Object>();
String sql = "select * from user where "; // 要多个空格，否则是whereusername
if(username!=null && username.trim().length()>0){
sql = sql + "username like ?";
list.add(username);
}
Object[] params = list.toArray();
List<User> user1 = queryRunner.query(sql,new BeanListHandler<User>(User.class),params);
return user1;
}
//按照id删除 user表
public int deleteUserById(int id) throws SQLException{
QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
String sql = "select from user where id = ?";
int count = queryRunner.update(sql,id);
return count;
}
//增加数据到user表
public int addUser(User user) throws SQLException{
String sql = "insert into user(id,username,password,jobs,phone) values (?,?,?,?,?)";
QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
int row = queryRunner.update(sql,user.getId(),user.getUsername(),user.getPassword(),user.getJobs(),user.getPhone());
return row;
}
//修改user数据
public int updateUser(User user) throws SQLException{
String sql = "update user set id = ?,username = ?,password = ?,jobs = ?,phone = ?";
QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
int row = queryRunner.update(sql,user.getId(),user.getUsername(),user.getPassword(),user.getJobs(),user.getPassword());
return row;
}
}
四、service层
package cn.edu.xit.service;
import java.sql.SQLException;
import java.util.List;
import org.apache.commons.dbutils.QueryRunner;
import cn.edu.xit.dao.UserDao;
import cn.edu.xit.domain.User;
import cn.edu.xit.util.C3p0Utils;
public class UserService {
private UserDao userDao=new UserDao();
public User login(String username,String password) throws SQLException {
return userDao.findUserByUsernameandPwd(username, password);
}
public List<User> findAll() throws SQLException{
return userDao.findUser();
}
public int deleteUserById(int id) throws SQLException{
return userDao.deleteUserById(id);
}
public List<User> findUserByUsername(String username) throws SQLException{
return userDao.findUserByUsername( username);
}
}
五、servlet层
①
package cn.edu.xit.servlet;
import java.io.IOException;
import java.sql.SQLException;
import javax.security.auth.login.LoginException;
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 cn.edu.xit.dao.UserDao;
import cn.edu.xit.domain.User;
import cn.edu.xit.service.UserService;
/**
* 用户登录servlet编写
*/
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public LoginServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
UserService userService = new UserService();
String username = request.getParameter("username");
String password = request.getParameter("password");
try{
User user = userService.login(username, password);
if(user!=null){
response.sendRedirect("FindUserServlet");
}else{
response.sendRedirect("login.jsp");
}
}catch(SQLException e){
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request,response);
}
}
②
package cn.edu.xit.servlet;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
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 cn.edu.xit.domain.User;
import cn.edu.xit.service.UserService;
/**
* 显示用户的所有信息
*/
@WebServlet("/FindUserServlet")
public class FindUserServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public FindUserServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
UserService userService = new UserService();
try{
List<User> user = userService.findAll();
if(user!=null){
request.getSession().setAttribute("user", user);
response.sendRedirect("list.jsp");
}
}catch(SQLException e){
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request,response);
}
}
③
package cn.edu.xit.servlet;
import java.io.IOException;
import java.sql.SQLException;
import java.util.List;
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 cn.edu.xit.domain.User;
import cn.edu.xit.service.UserService;
/**
* Servlet implementation class FindUserByUsername
*/
@WebServlet("/FindUserByUsername")
public class FindUserByUsername extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public FindUserByUsername() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
UserService userService = new UserService();
String username = request.getParameter("username");
try{
List<User> user = userService.findUserByUsername(username);
if(user!=null){
request.getSession().setAttribute("username", username);
request.getSession().setAttribute("user", user);
response.sendRedirect("list.jsp");
}
}catch(SQLException e){
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request,response);
}
}
④
package cn.edu.xit.servlet;
import java.io.IOException;
import java.sql.SQLException;
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 cn.edu.xit.dao.UserDao;
import cn.edu.xit.service.UserService;
/**
*删除用户servlet编写
*/
@WebServlet("/DeleteServlet")
public class DeleteServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public DeleteServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
request.setCharacterEncoding("utf-8");
UserService userService = new UserService();
int id = Integer.parseInt(request.getParameter("id"));
try{
int count = userService.deleteUserById(id);
if(count!=0){
response.sendRedirect("FindUserServlet");
}
}catch(SQLException e){
e.printStackTrace();
}
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
doGet(request,response);
}
}
六、前端页面
① login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<link rel="stylesheet" type="text/css" href="css/bootstrap.css">
</head>
<body>
<div class = "container">
<div class = "row">
<div class = "col-md-6 offset-md-3">
<form action="LoginServlet" method="post">
<div class="form-group">
<label for="formGroupExampleInput">用户名</label>
<input type="text" class="form-control" id="formGroupExampleInput" name="username" placeholder="请输入用户的姓名 ">
</div>
<div class="form-group">
<label for="formGroupExampleInput2">密码</label>
<input type="text" class="form-control" id="formGroupExampleInput2" name="password" placeholder="请输入密码">
</div>
<div class="form-group row">
<div class="col-md-10">
<button type="submit" class="btn btn-primary">登录</button>
<button type="reset" class="btn btn-primary">重置</button>
</div>
</div>
</form>
</div>
</div>
</div>
<!-- <center>
<h1>用户登录</h1>
<form action="LoginServlet" method="post" >
username:<input type="text" name="username"><br/><br/>
password:<input type="password" name="password"><br/><br/>
<input type="submit" value="login">
</form>
</center> -->
</body>
</html>
②list.jsp
<%@page import="java.util.List"%>
<%@page import="cn.edu.xit.domain.User"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
<!-- 显示用户的所有信息页面 -->
<form action="FindUserByUsername" method="post">
<label>名称:</label><input type="text" name="username" value="${username}">
<input type="submit" value="查询"><br/><br/><br/><br/>
</form>
<table border="1px">
<tr>
<td>编号</td>
<td>名称</td>
<td>职业</td>
<td>电话</td>
<td>操作</td>
</tr>
<c:forEach items="${user}" var="user1" >
<tr>
<td>${user1.id}</td>
<td>${user1.username}</td>
<td>${user1.jobs}</td>
<td>${user1.phone}</td>
<td><a href="DeleteServlet?id=${user1.id}" onclick="return confirm('确认删除')">删除</a>
<a href="UpdateServlet?id=${user1.id}">修改</a>
</td>
</tr>
</c:forEach>
</table>
<a href="InsertServlet">增加</a>
</body>
</html>
七、C3p0连接池配置
①
package cn.edu.xit.util;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3p0Utils {
private static DataSource ds;
static {
ds = new ComboPooledDataSource();
}
public static DataSource getDataSource() {
return ds;
}
}
② c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">
jdbc:mysql://127.0.0.1/student
</property>
<property name="user">root</property>
<property name="password">swj93980</property>
<property name="checkoutTimeout">30000</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
</c3p0-config>
PS：用户名和密码以及jdbcURL地址需要跟你们自己的数据库对应
项目跑通结果：
工程项目整体图：
附上jar包资源：
链接: https://pan.baidu.com/s/1WfCsLgHatGYac-2jn_4Xiw
提取码: g8v1
jar包都放在WEB-INF的lib文件夹中
1255
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
