建表建库 JDBC等
同上
表 tbl_user_info 在数据库database2
表里有 username,number,password 三列 全为 string类型
连接数据库
DBUtil.java
package com.imooc.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBUtil {
private static final String URI = "jdbc:mysql://localhost:3306/database2?"
+ "user=root&password=123456&useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
public static Connection connectDB() throws Exception {
//1、加载数据库驱动
Class.forName(DRIVER);
//2、获取数据库连接
Connection conn = DriverManager.getConnection(URI);
return conn;
}
}
用户层
UserVo.java
package com.imooc.jdbc;
import java.util.Date;
public class UserVO {
private String username;
private String number;
private String password;
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
@Override
public String toString() {
return "UserVO [username=" + username + ", number=" + number
+ ", password=" + password + "]";
}
}
Dao层
UserDao.java
package com.imooc.jdbc;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import com.mysql.cj.util.Util;
import java.sql.Statement;
public class UserDao {
public void updateUserNumber(UserVO user) throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "UPDATE tbl_user_info SET number=?"
+ " WHERE username=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getNumber());
pstmt.setString(2, user.getUsername());
pstmt.executeUpdate();
}
public void updateUserPassword(UserVO user) throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "UPDATE tbl_user_info SET password=?"
+ " WHERE username=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getPassword());
pstmt.setString(2, user.getUsername());
pstmt.executeUpdate();
}
/*
public boolean selectByNameAPwd(String userName,String userPwd ){
//加载
try {
//连接
Connection conn = DBUtil.connectDB();
//预执行
String sql="select * from tbl_user_info where username=? and password=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, userName);
ps.setString(2, userPwd);
//执行
ResultSet rs = ps.executeQuery();
boolean r;
if(rs.next()){
r=true;//登录成功,查询到信息
}else{
r=false;
}
//释放
rs.close();
ps.close();
conn.close();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
*/
public boolean selectByNameAPwd(UserVO user ){
//加载
try {
//连接
Connection conn = DBUtil.connectDB();
//预执行
String sql="select * from tbl_user_info where username=? and password=?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setString(1, user.getUsername());
ps.setString(2, user.getPassword());
//执行
ResultSet rs = ps.executeQuery();
boolean r;
if(rs.next()){
r=true;//登录成功,查询到信息
}else{
r=false;
}
//释放
rs.close();
ps.close();
conn.close();
return r;
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return false;
}
public void addUser(UserVO user) throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "INSERT INTO tbl_user_info(username,number,password) "
+ " VALUES(?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, user.getUsername());
pstmt.setString(2, user.getNumber());
pstmt.setString(3, user.getPassword());
pstmt.execute();
}
public void deleteUser(String u) throws Exception {
Connection conn = DBUtil.connectDB();
String sql = "DELETE FROM tbl_user_info WHERE username = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, u);
pstmt.execute();
}
public List<UserVO> queryByParams(List<Map<String, Object>> params) throws Exception {
Connection conn = DBUtil.connectDB();
StringBuilder sql = new StringBuilder("SELECT * FROM tbl_user_info WHERE 1=1 ");
for(Map<String, Object> param : params) {
sql.append(" and ");
sql.append(" " + param.get("col") + " ");
sql.append(" " + param.get("rel") + " ");
sql.append(" " + param.get("value") + " ");
}
List<UserVO> userList = new ArrayList<UserVO>();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql.toString());
while(rs.next()) {
UserVO user = new UserVO();
user.setUsername(rs.getString("username"));
user.setNumber(rs.getString("number"));
user.setPassword(rs.getString("password"));
userList.add(user);
}
return userList;
}
}
注册页面(往数据库增加数据)
register.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>用户注册</title>
<script type="text/javascript" language = "javascript">
function check(){
//验用户名
var username = document.getElementById("user").value;
var pwd1 = document.getElementById("pwd1").value;
var pwd2 = document.getElementById("pwd2").value;
if(username == ""){
alert("用户名不能为空");
return ;
}
if(pwd1 == ""){
alert("密码不能为空");
return ;
}
else if(pwd1!=pwd2){
alert("两次密码不一样");
return ;
}
document.getElementById("sub1").submit();//方法进行到这里将form的action提交
}
</script>
<body>
<form id="sub1" action="checkregister" method="post">
<table align="center">
<tr height="150dp">
<td><h2>创建新账户</h2></td>
</tr>
<tr>
<td>用户名:</td>
<td> <input type="text" id="user" name="user"></td>
</tr>
<tr>
<td>电话:</td>
<td> <input type="text" name="pnumber"></td>
</tr>
<tr>
<td>密码:</td>
<td> <input type="password" id="pwd1" name="pwd1"></td>
</tr>
<tr>
<td>密码确认:</td>
<td> <input type="password" id="pwd2" name="pwd2"></td>
</tr>
<tr>
<tr align="center">
<td colspan="2"><input type="button" value="注册" onclick="check()"/> </td>
</tr>
</table>
</form>
<form action="login.jsp">
<table align="center">
<tr align="center">
<td ><input type = "submit" value = "登录" /></td>
</tr>
</table>
</form>
</body>
</head>
</html>
提交到servlet验证 调用Dao层函数
checkregister.java
package com.imooc.jdbc;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import com.imooc.jdbc.UserDao;
@WebServlet(name="checkregister",urlPatterns={"/checkregister"})
public class checkregister extends HttpServlet{
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
response.setContentType("text/html);charset=UTF-8");
String user = request.getParameter("user");
String pwd = request.getParameter("pwd1");
String phone = request.getParameter("pnumber");
UserDao dao = new UserDao();
UserVO u=new UserVO();
u.setUsername(user);
u.setNumber(phone);
u.setPassword(pwd);
try {
dao.addUser(u);
response.getWriter().write("注册成功,1秒后回登录页");
response.setHeader("refresh", "1;url="+request.getContextPath()+"/login.jsp");
} catch (Exception e) {
response.getWriter().write("注册失败,1秒后回登录页");
response.setHeader("refresh", "1;url="+request.getContextPath()+"/login.jsp");
e.printStackTrace();
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
登陆页面(验证 数据库数据)
login.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<title>登录</title>
</head>
<meta charset="UTF-8">
<title>登录</title>
<script type="text/javascript" language = "javascript">
function reloadCode(){
var time = new Date().getTime();
document.getElementById("imag").src="authImg?d="+time;
}
function check(){
//验用户名
var username = document.getElementById("user").value;
var pwd = document.getElementById("pwd").value;
if(username == ""){
alert("用户名不能为空");
return ;
}
if(pwd == ""){
alert("密码不能为空");
return ;
}
document.getElementById("sub").submit();//方法进行到这里将form的action提交
}
</script>
<body>
<form id="sub" action="loginServlet" method="post">
<table align="center">
<tr >
<td>用户名:</td>
<td align="left"> <input type="text" placeholder="输入用户名" name="user" id="user"></td>
</tr>
<tr >
<td>密码:</td>
<td align="left"> <input type="password" placeholder="输入密码" name="pwd" id="pwd"></td>
</tr>
<tr >
<td>验证码:</td>
<td align="left"><input type="text" placeholder="输入验证码" name="text" id="text" size="10"/>
<img src="authImg" id="imag" onclick="reloadCode()"/>
</td>
</tr>
<tr table align="center">
<td ><input type="button" value="提交" onclick="check()"/> </td>
<td ><input type="reset" value="重置"/> </td>
<td ><input type="button" value="注册" onclick="window.location.href='register.jsp'"/></td>
</tr>
</table>
</form>
<center>
<%
//读取session值
String val= (String)session.getAttribute("name");
//如果session不存在
if(val==null){
val ="不存在";
}
out.print("当前\""+val+"\"用户已登录");
%>
<table align="center">
<tr>
<td><input type = "button" value = "主页" onclick="window.location.href='index.jsp'"/></td>
</tr>
</center>
</table>
</form>
</body>
</html>
登录成功后用户名保存在session里
验证码点击可刷新 servlet代码见 此页面
servlet调用Dao层函数验证数据库是否有用户名
LoginServlet.java
package com.imooc.jdbc;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import com.imooc.jdbc.UserDao;
@WebServlet(name="loginServlet",urlPatterns={"/loginServlet"}) //注解名和post的标签名相同
public class LoginServlet extends HttpServlet{
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
response.setContentType("text/html);charset=UTF-8");
String imageText = request.getParameter("text");
String text = (String) request.getSession().getAttribute("image");
String user = request.getParameter("user");
String pwd = request.getParameter("pwd");
UserVO u=new UserVO();
u.setUsername(user);
u.setPassword(pwd);
boolean r=new UserDao().selectByNameAPwd(u);//调用dao层
//页面跳转
if (!text.equalsIgnoreCase(imageText)){
response.getWriter().write("验证码错误,1秒后重新登录");
response.setHeader("refresh", "1;url="+request.getContextPath()+"/login.jsp");
}
else if(r){
//把用户数据保存在session域对象中
request.getSession().setAttribute( "user" , user);
HttpSession session = request.getSession(true);
session.setAttribute("name", user);
out.println("登陆成功,2秒后到主页");
response.setHeader("refresh", "1;url="+request.getContextPath()+"/index.jsp");
}else{
response.getWriter().write("登陆失败,2秒后重新登录");
response.setHeader("refresh", "2;url="+request.getContextPath()+"/login.jsp");
}
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
lisi 登陆成功后
登录页可跳转至主页
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>主页</title>
<style>
body{
margin:0px;padding:100px;
text-align:center;
}
</style>
</head>
<body>
<%
String userna=(String)session.getAttribute("name");
%>
<%
if(userna==null||"".equals(userna)){
%>
<table align="center">
<tr align="center">
<td > 欢迎!
<td ><a href="login.jsp">登录</a>
</td>
</tr>
<%
}else{
%>
<table align="center">
<tr align="center">
<td > 欢迎回来 <%=userna %></td>
<td ><a href="logout.jsp">注销</a></td>
<td ><a href="login.jsp">登录页</a></td>
<%
}
%>
<table align="center">
<tr align="center">
<td ><a href="searchServlet?us=<%=userna%>">查询用户信息</a></td>
</tr>
</table>
</body>
</html>
查询用户信息
提交到servlet 调用Dao层函数
searchServlet.java
package com.imooc.jdbc;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import com.imooc.jdbc.UserDao;
@WebServlet(name="searchServlet",urlPatterns={"/searchServlet"})
public class searchServlet extends HttpServlet{
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
response.setContentType("text/html);charset=UTF-8");
String na = request.getParameter("us");
String name="'%"+na+"%'";
UserDao dao = new UserDao();
List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();
Map<String, Object> param1 = new HashMap<String, Object>();
param1.put("col", "username");
param1.put("rel", "like");
param1.put("value", name);
params.add(param1);
try {
List<UserVO> userList = dao.queryByParams(params);
HttpSession session = request.getSession();
session.setAttribute("userList", userList);
response.sendRedirect("show.jsp");
} catch (Exception e) {
e.printStackTrace();
}
/*
UserDao dao = new UserDao();
List<Map<String, Object>> params = new ArrayList<Map<String,Object>>();
Map<String, Object> param1 = new HashMap<String, Object>();
param1.put("col", "username");
param1.put("rel", "like");
param1.put("value", name);
params.add(param1);
try {
List<UserVO> userList = dao.queryByParams(params);
for (UserVO user : userList) {
out.println(user);
}
response.setHeader("refresh", "3;url="+request.getContextPath()+"/index.jsp");
} catch (Exception e) {
e.printStackTrace();
}
*/
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
可跳转到 show.jsp 进行修改数据 删除数据等
show.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="com.imooc.jdbc.UserDao" %>
<%@ page import="com.imooc.jdbc.UserVO" %>
<%@ page import="java.util.List" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>用户信息</title>
</head>
<body>
<%
List<UserVO> users = (List) session.getAttribute("userList");
%>
<table align="center">
<tr align="center">
<td><a href="index.jsp">返回主页</a></td>
</tr>
</table>
<table border="1" cellspacing="0" align="center" width="500dp"
height="200dp">
<thead>
<tr>
<td align="center">
姓名
</td>
<td align="center">
号码
</td>
<td align="center">
密码
</td>
<td align="center">
操作
</td>
</tr>
</thead>
<tbody>
<tr>
<%
for (UserVO user : users) {
%>
<td><%=user.getUsername()%>
</td>
<td><%=user.getNumber()%>
</td>
<td><%=user.getPassword()%>
</td>
<td align="center">
<a href="updateshow.jsp?userN=<%=user.getUsername() %>">更改号码</a>
<a href="updatepassword.jsp?userNA=<%=user.getUsername() %>
&&userPA=<%=user.getPassword() %>">更改密码</a>
<a href="deleteServlet?userName_1=<%=user.getUsername() %>">删除用户</a>
</td>
</tr>
<%
}
%>
</tbody>
</table>
</body>
</html>
删除用户
在servlet调用Dao层函数
DeleteServlet.java
package com.imooc.jdbc;
import java.io.IOException;
import java.io.PrintWriter;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet(name="deleteServlet",urlPatterns={"/deleteServlet"})
public class DeleteServlet extends HttpServlet{
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("UTF-8");
PrintWriter out = response.getWriter();
response.setContentType("text/html);charset=UTF-8");
String u=request.getParameter("userName_1");
UserDao dao = new UserDao();
try {
dao.deleteUser(u);
} catch (Exception e) {
e.printStackTrace();
}
response.getWriter().write("删除成功并注销");
response.setHeader("refresh", "2;url="+request.getContextPath()+"/logout.jsp");
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
跳转至logout.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title></title>
</head>
<body>
<%
session.removeAttribute("user");
session.invalidate();
%>
<table table align="center">
<tr table align="center">
<td><h3>注销成功</h3><td>
<td ><input type="button" value="登录" onclick="window.location.href='login.jsp'"/></td>
<td ><input type="button" value="主页" onclick="window.location.href='index.jsp'"/></td>
</tr>
</table>
</body>
</html>
更改号码
updateshow.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>更新</title>
</head>
<body>
<form action="updateServlet" method="post">
<table align="center">
<tr height="150dp">
<td></td>
</tr>
<tr>
<td>用户名:</td>
<td><input type="text" name="userN" value="<%=request.getParameter("userN") %>"/></td>
</tr>
<tr>
<td>输入新号码:</td>
<td><input type="text" name="pnumber" /></td>
</tr>
<tr align="center">
<td colspan="2"><input type="submit" value="更新"/></td>
</tr>
</table>
</form>
</body>
</html>
同上在servlet调用Dao层函数
UserDao dao= new UserDao();
UserVO USer = new UserVO();
USer.setUsername(userN);
USer.setNumber(pnumber);
try {
dao.updateUserNumber(USer);
} catch (Exception e) {
e.printStackTrace();
}
更改密码
updatepassword.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>更新</title>
</head>
<body>
<form action="updatePassword?userNA=<%=request.getParameter("userNA") %>" method="post">
<table align="center">
<tr height="150dp">
<td></td>
</tr>
<tr>
<td>原密码:</td>
<td><input type="text" value="<%=request.getParameter("userPA") %>"/></td>
</tr>
<tr>
<td>输入新密码:</td>
<td><input type="text" name="passw" /></td>
</tr>
<tr align="center">
<td colspan="2"><input type="submit" value="更新"/></td>
</tr>
</table>
</form>
</body>
</html>
同上在servlet调用Dao层函数
String pass=request.getParameter("passw");
String userNA = request.getParameter("userNA");
UserDao dao= new UserDao();
UserVO USer = new UserVO();
USer.setUsername(userNA);
USer.setPassword(pass);
try {
dao.updateUserPassword(USer);
} catch (Exception e) {
e.printStackTrace();
}