三、附加一些常用配置

1.JDBC-DBConnection

package com.sun.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBConnection {
  private static final String driverName = "com.mysql.cj.jdbc.Driver";
  private static final String url = "jdbc:mysql://localhost:3306/testdb?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true&rewriteBatchedStatements=true";
  private static final String user = "root";
  private static final String password = "sm1208";
  private DBConnection() {
  }
  static {
  try {
    Class.forName("com.mysql.cj.jdbc.Driver");
  } catch (ClassNotFoundException e) {
    e.printStackTrace();
  }
  }
  public static Connection getConnection() throws SQLException {
  return DriverManager.getConnection(url, user, password);
  }
  public static void close(ResultSet rs, Statement st, Connection conn) {
  try {
    if (rs != null) {
    rs.close();
    }
  } catch (SQLException e) {
    e.printStackTrace();
  } finally {
    try {
    if (st != null) {
      st.close();
    }
    } catch (SQLException e) {
    e.printStackTrace();
    } finally {
    if (conn != null) {
      try {
      conn.close();
      } catch (SQLException e) {
      e.printStackTrace();
      }
    }
    }
  }
  }
}

2.直接连接方式

try {
    //1.数据库连接的4个基本要素：
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "sm1208";
    String driverName = "com.mysql.cj.jdbc.Driver";
    //2.加载驱动 
    Class.forName(driverName);
    //3.获取连接
    Connection conn = DriverManager.getConnection(url, user, password);
  } catch (Exception e) {
    e.printStackTrace();
}

3.JDBC-CRUD

创建类StudentCRUD实现对student表的CRUD操作（使用DBConnection类）：该类具有main()方法和使用PreparedStatement实现增加记录的方法add()、实现查询全部记录的方法listAll()、实现根据id更新数据的方法update()、实现根据id删除记录的方法delete()。

add()
public static boolean add(String name, int age, String hobby)throws SQLException{
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  boolean successflag = false;
  try {
    conn = DBConnection.getConnection();
    String sql = "insert into student values(null,?,?,?)";
    // 预编译SQL语句，得到PrepareStatement对象
         ps = conn.prepareStatement(sql);
         // 填充占位符
         ps.setObject(1,name);
         ps.setObject(2,age);  
         ps.setObject(3,hobby);
         ps.execute();
  } finally {
    // 关闭资源对象
    DBConnection.close(rs, ps, conn);
    successflag = true;
  }
  return successflag;
  } 
listAll()
public static List<Student> listAll()throws SQLException{
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  List<Student> list = new ArrayList<Student>();
  try {
    conn = DBConnection.getConnection();
    String sql = "select * from student";
    ps = conn.prepareStatement(sql);
    // 执行executeQuery(),得到结果集：ResultSet
    rs = ps.executeQuery();
    // 通过ResultSet得到列值
    while (rs.next()) {  
    list.add(new Student(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4)));
    }
  } finally {
    DBConnection.close(rs, ps, conn);
  }
  return list;
  }
update()
public static boolean update(Student student)throws SQLException{
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  boolean successflag = false; 
  try {
    conn = DBConnection.getConnection();
    String sql = "update student set name = ?,age = ?,hobby = ? where studentId = ?";
    ps = conn.prepareStatement(sql);
    ps.setObject(1, student.getName());
    ps.setObject(2, student.getAge());
    ps.setObject(3, student.getHobby());
    ps.setObject(4, student.getStudentId());
    ps.execute();
  } finally {
    DBConnection.close(rs, ps, conn);
    successflag = true;
  }
  return successflag;
  }
delete()
public static boolean delete(int id)throws SQLException{
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  boolean successflag = false; 
  try {
    conn = DBConnection.getConnection();
    String sql = "delete from student where studentId = ?";
    ps = conn.prepareStatement(sql);
    ps.setObject(1, id);
    ps.execute();
  } finally {
    DBConnection.close(rs, ps, conn);
    successflag = true;
  }
  return successflag;
}

在数据库testDB中创建存储过程，名为getSnameAndClassBySno，其可以通过学号查询学生的姓名和班级信息。

create procedure getSnameAndClassBySno(in id VARCHAR(25))
BEGIN
SELECT * FROM student WHERE studentId=id;
END
CALL getSnameAndClassBySno(1);

在类StudentCRUD中定义方法，调用存储过程getSnameAndClassBySno。

// 调用存储过程
  public static Student getStudentByid(int id)throws SQLException{
  Connection conn = null;
  CallableStatement cs = null;
  ResultSet rs = null;
  Student stu = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "CALL getSnameAndClassBySno(?);";
    cs = (CallableStatement) conn.prepareCall(sql);
    cs.setInt(1, id);
    // 执行execute()
    cs.execute();
    // 存储过程结果集
    rs = cs.getResultSet();
    if(rs.next()) {
    stu = new Student(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getString(4));
    }
  } finally {
    try {
    if (rs != null) {
      rs.close();
    }
    } catch (SQLException e) { 
    e.printStackTrace();
    } finally {
    try {
      if (cs != null) {
      cs.close();
      }
    } catch (SQLException e) { 
      e.printStackTrace();
    } finally {
      if (conn != null) {
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      }
    }
    }
  }
  return stu;
}

创建TransactionTest类，使用JDBC事务管理实现用户转账功能。（注意，user表需要有float类型的字段money，并存在用于测试的2条用户记录）

// 使用JDBC事务管理实现用户转账功能
public class TransactionTest {
  public static void main(String args[]) throws SQLException {
  transferAccounts(1,2,1000);
  }
  // 实现id1向id2转账money元
  public static boolean transferAccounts(int id1,int id2,float money)throws SQLException{
  Connection conn = null;
  PreparedStatement ps1 = null;
  PreparedStatement ps2=null;
  ResultSet rs = null;
  boolean successflag = false;
  try {
    conn = DBConnection.getConnection();
    conn.setAutoCommit(false);//开启事务
    // id1 扣钱
    String sql1 = "update user set money = money - ? where userId = ?";
         ps1 = conn.prepareStatement(sql1);
         ps1.setObject(1,money);
         ps1.setObject(2,id1);  
         ps1.executeUpdate(); 
         // 程序出问题了
         // int i = 10 / 0 ;
         // id2 加钱
    String sql2 = "update user set money = money + ? where userId = ?";
          ps2 = conn.prepareStatement(sql2);
          ps2.setObject(1,money);
          ps2.setObject(2,id2);  
          ps2.executeUpdate();
          conn.commit();//提交事务
  } finally {
    DBConnection.close(rs, ps, conn);
    successflag = true;
  }
  return successflag;
  } 
}

4. Session 校验（用户登录）

// 方式一: 
<% 
    String username=(String)(session.getAttribute("username")); 
  if(username==null || username.equals("")){ 
        response.sendRedirect("index.jsp"); 
    } 
%> 
// 方式二: 
<%@ include file="/LoginCheck.jsp" %> 
<% 
    request.setCharacterEncoding("utf-8"); 
  response.setCharacterEncoding("utf-8"); 
  String path=request.getContextPath();//部署的应用程序名"/www" 
%> 
<%  
    String username=user.getUsername(); 
  if(username==null || username.equals("")){    
  response.sendRedirect(path+"/index.jsp"); 
    } 
%>

5.JPA

User实体类

@Entity
public class User {
    @Id
    private int id;
    private String name;
    private String password;
    private int age;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    // get set
}

userDao

import entity.User;
public interface UserDao {
  public void add( String name, String password,int age);
    public void delete(int id); 
  public void update(User user);
    public User get(int id);
}

userDaoImplement

import javax.persistence.EntityManager;
public class UserDaoImpl implements UserDao{
  EntityManagerFactory factory;
  EntityManager em;
  public UserDaoImpl() {
        factory = Persistence.createEntityManagerFactory(persistenceUnitName);
        em = factory .createEntityManager();
  }
  public void add( String name ,String password,int age) {
  User user = new User();
  user.setName( name) ;
  user. setAge( age);
  user.setPassword(password);
        em.getTransaction( ).begin();
        em.persist(user) ;
        em.getTransaction().commit();
    }
    public void delete(int id) {
  em.getTransaction().begin();
  User user = em.find(User.class, id);
        if (user != null) {
    em. remove(user);
  }
  em.getTransaction( ).commit();
  }
  public User get(int id) {
  User user = em.find(User.class,id);
        return user;
  }
  public void update(User user) {
       em.getTransaction( ).begin();
       User user2= em.merge(user);
       em.getTransaction( ).commit(); 
    }
}

6.JavaBean

package com.sun.bean;

import com.sun.util.DBConnection;
import com.sun.util.Date_String;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserBean {
  private int userid;
  private String username;
  private String password;
  private String sex;
  private int age;
  private String birthday;
  public int getUserid() {
  return userid;
  }
  public void setUserid(int userid) {
  this.userid = userid;
  }
  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 getSex() {
  return sex;
  }
  public void setSex(String sex) {
  this.sex = sex;
  }
  public int getAge() {
  return age;
  }
  public void setAge(int age) {
  this.age = age;
  }
  public String getBirthday() {
  return birthday;
  }
  public void setBirthday(String birthday) {
  this.birthday = birthday;
  }
  // 验证用户登录信息
  public boolean validlogin()throws SQLException{
  Connection conn = null;
  Statement st = null;
  ResultSet rs = null;
  boolean successflag=false;
  try {
    conn = DBConnection.getConnection();
    String sql = "select * from user where name='"+this.username+"' and password='"+this.password+"'";
    st = conn.createStatement();
    rs=st.executeQuery(sql);
    if(rs.next()==true) {successflag=true;}
  } finally {
    DBConnection.close(rs, st, conn);
  }
  return successflag;
  }
  public List<UserBean> getUsersPage()throws SQLException{
  List<UserBean> users=new ArrayList<UserBean>();
  Connection conn = null;
  Statement st = null;
  ResultSet rs = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "select * from user limit 0,3";
    st = conn.createStatement();
    rs=st.executeQuery(sql);
    while(rs.next()==true){
    UserBean tmp=new UserBean();
    tmp.setUserid(rs.getInt("id"));
    tmp.setUsername(rs.getString("name"));
    tmp.setPassword(rs.getString("password"));
    tmp.setSex(rs.getString("sex"));
    tmp.setAge(rs.getInt("age"));
    String birthday= Date_String.getStringDate1(rs.getDate("birthday"));
    tmp.setBirthday(birthday);
    users.add(tmp);
    }
  } finally {
    DBConnection.close(rs, st, conn);
  }
  return users;
  }
  // 条件查询 没用到
  public List<UserBean> getUsers(String username, String sex)throws SQLException{
  if(username==null) username="";
  if(sex==null) sex="";
  List<UserBean> users=new ArrayList<UserBean>();
  Connection conn = null;
  Statement st = null;
  ResultSet rs = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "select * from user where name like '%"+username+"%' and ifnull(sex,'') like '%"+sex+"%'";
    st = conn.createStatement();
    rs=st.executeQuery(sql);
    while(rs.next()==true){
    UserBean tmp=new UserBean();
    tmp.setUserid(rs.getInt("id"));
    tmp.setUsername(rs.getString("name"));
    tmp.setPassword(rs.getString("password"));
    tmp.setSex(rs.getString("sex"));
    String birthday=Date_String.getStringDate1(rs.getDate("birthday"));
    tmp.setBirthday(birthday);
    users.add(tmp);
    }
  } finally {
    DBConnection.close(rs, st, conn);
  }
  return users;
  }
  // 查询用户信息
  public static List<UserBean> getUsers()throws SQLException{
  List<UserBean> users=new ArrayList<UserBean>();
  Connection conn = null;
  Statement st = null;
  ResultSet rs = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "select * from user limit 0,3";
    st = conn.createStatement();
    rs=st.executeQuery(sql);
    while(rs.next()==true){
    UserBean tmp=new UserBean();
    tmp.setUserid(rs.getInt("id"));
    tmp.setUsername(rs.getString("name"));
    tmp.setPassword(rs.getString("password"));
    tmp.setSex(rs.getString("sex"));
    tmp.setAge(rs.getInt("age"));
    String birthday= Date_String.getStringDate1(rs.getDate("birthday"));
    tmp.setBirthday(birthday);
    users.add(tmp);
    }
  } finally {
    DBConnection.close(rs, st, conn);
  }
  return users;
  }
  // 查询单个用户信息
  public UserBean getUserById(String userid)throws SQLException{
  Connection conn = null;
  Statement st = null;
  ResultSet rs = null;
  UserBean user=null;
  try {
    conn = DBConnection.getConnection();
    String sql = "select * from user where id="+userid;
    st = conn.createStatement();
    rs=st.executeQuery(sql);
    if(rs.next()==true){
    user=new UserBean();
    user.setUserid(Integer.valueOf(userid));
    user.setUsername(rs.getString("name"));;
    user.setPassword(rs.getString("password"));
    user.setSex(rs.getString("sex"));
    user.setAge(rs.getInt("age"));
    this.birthday=Date_String.getStringDate1(rs.getDate("birthday"));
    user.setBirthday(birthday);
    }
  } finally {
    DBConnection.close(rs, st, conn);
  }
  return user;
  }
  // 修改用户个人信息
  public void updateUser()throws Exception{
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "update user set name=?,sex=?,age=?,birthday=? where id="+this.userid;
    ps = conn.prepareStatement(sql);
    ps.setString(1, this.username);
    ps.setString(2, this.sex);
    ps.setInt(3, this.age);
    ps.setTimestamp(4, Date_String.toTimestamp(this.birthday));
    ps.executeUpdate();
  } finally {
    DBConnection.close(rs, ps, conn);
  }
  }
  // 删除用户个人信息
  public void delUserById(String userid)throws Exception{
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "delete from user where id="+userid;
    ps = conn.prepareStatement(sql);
    ps.executeUpdate();
  } finally {
    DBConnection.close(rs, ps, conn);
  }
  }
  // 注册用户信息
  public boolean registerUser(){
  Connection conn = null;
  PreparedStatement ps = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "insert into user(name,password) values( '" + this.username + "' , '" + this.password
      + "')";
    ps = conn.prepareStatement(sql);
    ps.execute();
    return true;
  } catch (Exception e) {
  }
  finally {
    DBConnection.close(null,ps, conn);
  }
  return false;
  }
  // 增加用户信息
  public void addUser()throws Exception{
  Connection conn = null;
  PreparedStatement ps = null;
  ResultSet rs = null;
  try {
    conn = DBConnection.getConnection();
    String sql = "insert into user(id,name,password,sex,age,birthday) values(?,?,?,?,?,?)";
    ps = conn.prepareStatement(sql);
    ps.setInt(1, this.userid);
    ps.setString(2, this.username);
    ps.setString(3, this.password);
    ps.setString(4, this.sex);
    ps.setInt(5, this.age);
    ps.setTimestamp(6, Date_String.toTimestamp(this.birthday));
    ps.executeUpdate();
  } finally {
    DBConnection.close(rs, ps, conn);
  }
  }
  // 校验输入的数据是否符合要求 add update 使用
  public Map<String, String> checkUser()throws Exception{
  Map<String, String> errors = new HashMap<String, String>();
  if(username==null||username.equals("")) errors.put("username", "用户名不能为空！");
  if(password==null||password.equals("")) errors.put("password", "密码不能为空！");
  return errors;
  }
}

7.JSP展示层代码

error.jsp

<%@ page contentType="text/html; charset=utf-8" language="java"  isErrorPage="true"%>
<html>
<head>
<title>错误页面</title>
</head>
<body>
   <div id="error">发生了以下错误：</div><br><hr>
   <%=exception.getMessage()%><br><hr>
   <br>
</body>
</html>

exit.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"  pageEncoding="utf-8" errorPage="error.jsp" %>
<%
  session.invalidate();
    response.sendRedirect("index.jsp");
%>

header.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"  pageEncoding="utf-8" %>
<%@ page import="java.util.*" %>
<%
  String pathHeader=request.getContextPath();
%>
<div align="center" style="margin-top: 25px"><h3>学生管理系统</h3></div>
<div align="left">
<jsp:useBean id="LoginUser" class="com.sun.bean.UserBean" scope="session"/>
当前用户：<jsp:getProperty name="LoginUser" property="username"/>&nbsp;&nbsp;&nbsp;&nbsp;
当前时间：<%=(new java.util.Date()).toString() %>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <a href="<%=pathHeader%>/exit.jsp">退出系统</a>
</div>

index.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
         pageEncoding="utf-8" %>
<%
    String pathHeader = request.getContextPath();
    String error = (String) request.getAttribute("error");
    if (error == null) error = "";
%>
<!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>Student Manager Login</title>
</head>
<body>
<h3>Student Manager Login</h3>
<form action="loginValid.jsp" method="post">
    <table border=0>
        <tr>
            <td>用户名：</td>
            <td><input name='name' type='text' value=''/></td>
        </tr>
        <tr>
            <td>密码：</td>
            <td><input name='password' type='password' value=''/></td>
        </tr>
        <tr>
            <td colspan=2 align=right>
                <input type="button" οnclick='location.href=("register.jsp")' value="注册账号" class="btn">
                <input type="reset" value="重置" class="btn">
                <input type='submit' value='登录'/></td>
        </tr>
        <tr>
            <td colspan=2 align=right><%=error%>
            </td>
        </tr>
    </table>
</form>
</body>
</html>

loginCheck.jsp

<%
  request.setCharacterEncoding("utf-8");
  response.setCharacterEncoding("utf-8");
  String path=request.getContextPath();
%>
<jsp:useBean id="user" class="com.sun.bean.UserBean" scope="session"/>
<%
  String username=user.getUsername();
  if(username == null){
  response.sendRedirect(path+"/index.jsp");
  }else{
  session.setAttribute("LoginUser",user);
  }
%>

loginValid.jsp

<%@ page import="java.sql.SQLException" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"
   pageEncoding="UTF-8"%>
<body>
    <jsp:useBean id="user" class="com.sun.bean.UserBean"/>
  <jsp:useBean id="pageBeanUser" class="com.sun.bean.PageBean" scope="page"/>
  <jsp:setProperty name="user" property="username" param="name"/>
  <jsp:setProperty name="user" property="password"/>
  <%
  boolean successflag=user.validlogin();
  if(successflag==true){
      session.setAttribute("user", user);
    response.sendRedirect("/test4/getUserListByPage");
  }else{
      request.setAttribute("error", "用户名或密码不正确，登陆失败！");
      request.getRequestDispatcher("index.jsp").forward(request, response);
     }
  %>
</body>

Main.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"  pageEncoding="utf-8" %>
<%@ include file="/loginCheck.jsp" %>
<!DOCTYPE html>
<html>
<head>
    <title>Student Manager</title>
    <link rel="stylesheet" type="text/css" href="css/style.css" />
</head>
<body>
<div id="root">
    <div class="header">
        <%@include file="header.jsp" %>
    </div>
    <div class="menu">
        <%@include file="user/leftMenu.jsp" %>
    </div>
    <div class="listStudent">
        <%@include file="user/listStudent.jsp" %>
    </div>
    <div class="footer">
        <%@include file="footer.jsp" %>
    </div>
</div>
</body>
</html>

8.JSTL

引入标签库语句：<%@ taglib uri = "http://java.sun.com/jsp/jstl/core" prefix = "c" %>

导包语句 <%@page import="entity.User"%>

b2242ec66a354620c31a67c7baf480a5_image-20230301134303075.png

【JavaWeb】复习重点内容（三）

三、附加一些常用配置

1.JDBC-DBConnection

2.直接连接方式

3.JDBC-CRUD

4. Session 校验（用户登录）

5.JPA

6.JavaBean

7.JSP展示层代码

8.JSTL

热门文章

最新文章

相关课程

相关电子书

相关实验场景

【JavaWeb】复习重点内容（三）

三、附加一些常用配置

1.JDBC-DBConnection

2.直接连接方式

3.JDBC-CRUD

4. Session 校验 （用户登录）

5.JPA

6.JavaBean

7.JSP展示层代码

8.JSTL

热门文章

最新文章

相关课程

相关电子书

相关实验场景

4. Session 校验（用户登录）