学习笔记——JDBC

一、学习重点

image

二、学习内容

案例

创建工程：登录和注册标准化！！！
在web目录下，index.html有两个链接登录，注册。
点击登录链接，就去到login.html，点击注册，去到register.html
利用我们之前写过的JDBC的工具类，登录操作。
注册：利用JS做一些数据校验，用户名，密码，性别，爱好，用户名是否存在！给出相应的提示。
注册成功，跳转到登录页。

如果登录成功，跳转到web下的main目录下的main.html，
有几个按钮，
student表，
查询所有的学生信息，只能在控制台打印。
新增学生--》addstudent.html，提交，友好的提示。

登录失败，给出提示。

代码实现

Java工具类

package com.jsoft.dao;

import java.sql.SQLException;
import java.util.List;

public interface DAO<T> {

    /**
     * 更新
     * @return
     */
    int update(String sql, Object... args) throws Exception;

    /**
     * 通用的查询所有
     */
    List<T> getForList(String sql, Object... args) throws Exception;

    /**
     * 通用的查询单个
     */
    T get(String sql, Object... args) throws Exception;

    /**
     * 查询某一个列的值，统计
     */
    <E> E getForValue(String sql, Object... args) throws SQLException;

}


package com.jsoft.dao;

import com.jsoft.util.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.ParameterizedType;
import java.sql.SQLException;
import java.util.List;

public class DAOImpl<T> implements DAO<T> {

    private QueryRunner runner = null;
    private Class<T> type;

    /**
     * 这个构造器中在做的事：
     *  为了获取Class<T> type = Teacher.class
     */
    public DAOImpl() {
        runner = new QueryRunner(JDBCUtil.getDataSource());
        // 获得当前类的带有泛型类型的父类（运行期this其实是DAOImpl的某个子类）
        ParameterizedType ptClass = (ParameterizedType) this.getClass().getGenericSuperclass();
        type = (Class<T>) ptClass.getActualTypeArguments()[0];
    }

    /**
     * 通用的增删改
     * @param sql
     * @param args
     * @return
     * @throws Exception
     */
    @Override
    public int update(String sql, Object... args) throws Exception {
        return runner.update(sql,args);
    }

    /**
     * 根据sql语句查询多条记录
     * @param sql
     * @param args
     * @return
     * @throws Exception
     */
    @Override
    public List<T> getForList(String sql, Object... args) throws Exception {
        return runner.query(sql,new BeanListHandler<>(type),args);
    }

    /**
     * 根据sql语句查询一条记录
     * @param sql
     * @param args
     * @return
     * @throws Exception
     */
    @Override
    public T get(String sql, Object... args) throws Exception {
        return runner.query(sql,new BeanHandler<>(type),args);
    }

    /**
     * 根据sql语句查询某一列的值
     * @param sql
     * @param args
     * @return
     * @param <E>
     * @throws SQLException
     */
    @Override
    public <E> E getForValue(String sql, Object... args) throws SQLException {

        return (E) runner.query(sql,new ScalarHandler<>(),args);
    }
}

具体实现代码

package com.jsoft.entity;

public class User {

    private Integer id;
    private String username;
    private String password;

    public User() {
    }

    public User(Integer id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer 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;
    }
}

package com.jsoft.entity;

public class Student {

    private Integer id;
    private String name;
    private Integer age;
    private String gender;


    public Student() {
    }

    public Student(Integer id, String name, Integer age, String gender) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.gender = gender;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", gender='" + gender + '\'' +
                '}';
    }
}

package com.jsoft.dao;

import com.jsoft.entity.User;

public class UserDao extends DAOImpl<User> {
}

package com.jsoft.dao;

import com.jsoft.entity.Student;

public class StudentDAO extends DAOImpl<Student> {
}

后端代码

package com.jsoft.servlet;

import com.jsoft.dao.UserDao;
import com.jsoft.entity.User;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.util.Objects;

@WebServlet(name="login.do",value = "/login.do")
public class LoginServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");

        String sql = "select id,username,password from user where username = ? and password = ?";
        UserDao userDao = new UserDao();
        try {
            User user = userDao.get(sql, username, password);
            if(Objects.nonNull(user)){
                response.sendRedirect("main/main.html");
            }else {
                response.sendRedirect("error.html");
            }

        } catch (Exception e) {
            throw new RuntimeException(e);
        }


    }
}

package com.jsoft.servlet;

import com.jsoft.dao.UserDao;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.Objects;

@WebServlet(name="register.do",value = "/register.do")
public class RegisterServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");


        response.setCharacterEncoding("utf-8");
        response.addHeader("content-type","text/html;charset=utf-8");
        PrintWriter out = response.getWriter();


        String sql = "select 1 from user where username = ?";
        UserDao userDao = new UserDao();
        try {
            Object o = userDao.getForValue(sql, username);
            if(Objects.isNull(o)){
                // 新增
                sql = "insert into user values(?,?,?)";
                userDao.update(sql,null,username,password);
                out.write("注册成功！");
                out.write("请<a href='login.html'>登录</a>");
            }else {
                out.write("用户名已存在，注册失败！");
                out.write("<a href='register.html'>重新注册</a>");
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
}

package com.jsoft.servlet;

import com.jsoft.dao.StudentDAO;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet(name = "AddStudentServlet", value = "/addStudent.do")
public class AddStudentServlet extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");

        String name = request.getParameter("name");
        String gender = request.getParameter("gender");
        Integer age = Integer.parseInt(request.getParameter("age"));

        String sql = "insert into student values(?,?,?,?)";

        StudentDAO studentDAO = new StudentDAO();
        try {
            studentDAO.update(sql,null,name,age,gender);
            response.sendRedirect("main/main.html");
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
}

package com.jsoft.servlet;

import com.jsoft.dao.StudentDAO;
import com.jsoft.entity.Student;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

@WebServlet(name = "ShowServlet", value = "/show.do")
public class ShowServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        String sql = "select id,name,age,gender from student";
        StudentDAO studentDAO = new StudentDAO();
        response.setCharacterEncoding("utf-8");
        response.addHeader("content-type","text/html;charset=utf-8");
        PrintWriter out = response.getWriter();

        try {
            List<Student> students = studentDAO.getForList(sql);
            out.write("数据已经展示在后台...");
            System.out.println(students);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

}

前端代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<h1>用户登录</h1>
<form action="login.do" method="post">
    <p>账号：<input type="text" name="username"></p>
    <p>密码：<input type="password" name="password"></p>
    <p><input type="submit" value="登录"></p>
</form>
<a href="register.html">注册</a>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>

  <h1>用户注册</h1>
  <form action="register.do" method="post" onsubmit="return valid()">
      <p>
          账号：<input type="text" name="username" id="username" placeholder="用户名长度为6-12位" required>
      </p>
      <p>
          密码：<input type="password" name="password" id="password" required>
      </p>
      <p>
          确认密码：<input type="password" id="repassword" required>
      </p>
      <p style="color:red" id="msg"></p>
      <p>
          <input type="submit" value="注册">
      </p>
  </form>

    <script>
        function valid() {
            // alert();
            let username = document.querySelector("#username").value;
            let password = document.querySelector("#password").value;
            let repassword = document.querySelector("#repassword").value;
            let p = document.querySelector("#msg")

            if(username.length >= 6 && username.length <= 12){
                p.innerHTML = "";
                if(password === repassword) {
                    return true;
                }else {
                    p.innerHTML = "两次密码输入不一致！";
                    return false;
                }
            }else {
                p.innerHTML = "用户名长度不合法！";
                return false;
            }
        }
    </script>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <form action="../addStudent.do" method="post">
        <p>
            姓名：<input type="text" name="name" >
        </p>
        <p>
            年龄：<input type="number" name="age" >
        </p>
        <p>
            性别：<input type="radio" name="gender" value="男" checked> 男
            <input type="radio" name="gender" value="女"> 女
        </p>
        <p>
            <input type="submit" value="保存">
        </p>
    </form>
</body>
</html>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
  <button onclick="show()">显示学生信息</button>
  <a href="addStudent.html">新增学生信息</a>

    <script>
        function show() {
            location.href = "../show.do";
        }
    </script>

</body>
</html>

三、笔记内容

Servlet请求与响应

最常见的客户端传参的方式两种，get，post

浏览器地址栏直接输入：一定是get请求
超级链接：一定是get请求
表单：可以是get，也可以是post，取决于method

GET请求和POST请求的区别:

GET：请求的参数会在浏览器的地址栏中显示，不安全；请求的长度限制在1K之内。
没有请求体，无法通过request去设置一些参数。
POST：请求的参数不会在浏览器的地址栏中显示，相对安全。
把请求的参数封装成几个数据报文（请求体）。请求参数的长度没有限制。

HttpServletRequest--request（请求）
所有和请求相关的操作，都用这个对象来处理
当有请求来的时候，request就被实例化。
HttpServletResponse--response（响应）
所有和响应相关的操作，都用这个对象来处理
当有请求来的时候，response就被实例化。
当你想要给出响应时，使用response。
我们要给出的响应，必须要展示在客户端（浏览器）

doGet方法

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 给出响应
        // 客户端和服务器端的信息传递

        // 设置响应的字符集
        // 一定要放在获取流之前
        // 服务器处理字符串按照UTF-8处理
        response.setCharacterEncoding("utf-8");
        // 添加响应头，告诉浏览器以HTML的格式来解析响应字符串
        response.addHeader("content-type","text/html;charset=utf-8");

        // IO流
        PrintWriter out = response.getWriter();
        // 向客户端回复的数据
        out.write("我是后台，已经接到了你的请求！！！");

        // 流的操作
        out.flush();
        out.close();
    }

doPost方法

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("doPost方法...");
    }

servlet初始化的方法

init方法什么时候执行？
默认当我们第一次访问servlet的时候被调用，延迟加载。
初始化方法只执行一次。

    @Override
    public void init(ServletConfig config) throws ServletException {
        System.out.println("servlet初始化...");
        System.out.println(config.getInitParameter("username"));
        System.out.println(config.getInitParameter("password"));
    }

匹配数据库

通过JDBC连接数据库，并且查询记录，返回结果
操作步骤：
1、加载驱动，驱动包放在哪？数据库连接池。
2、JDBC，工具类。
3、处理结果
注册--新增
登录--查询

登陆界面

前端代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<!--
    a标签是可以传参数的
    a标签和我们直接在地址栏输入的地址没有区别，他们的都是GET请求
 -->
<a href="login.do?username=admin&password=1234567">登录</a>

<!--
    当我们使用form表单提交数据时，注意：
        1、所有的表单元素都必须有name属性，后台获取数据的根基。
        2、按钮必须是submit。
        3、提交数据的地址
-->
<form action="login.do" method="post">
    <p>账号：<input type="text" name="username"></p>
    <p>密码：<input type="password" name="password"></p>
    <input type="submit" value="登录">
</form>

</body>
</html>

后端代码

package com.jsoft.morning.servlet;

import com.jsoft.morning.dao.UserDao;
import com.jsoft.morning.entity.User;

import javax.servlet.*;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Objects;

/**
 * 我要让我现在的servlet既可以处理get请求，又可以处理post请求，只需要写一次。
 */
@WebServlet("/login.do")
public class LoginServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 接收参数，处理请求
        // 前台传过来的参数是：username:admin password:123456
        // 后来接收参数是根据key来获取value的值
        String username = request.getParameter("username");
        String password = request.getParameter("password");

        response.setCharacterEncoding("utf-8");
        response.addHeader("content-type","text/html;charset=utf-8");
        PrintWriter out = response.getWriter();

        String sql = "select id,username,password from user where username = ? and password = ?";
        UserDao userDao = new UserDao();
        try {
            User user = userDao.get(sql, username, password);
            if(Objects.nonNull(user)){
                out.write("login success!!!");
            }else {
                out.write("username or password may be wrong!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);

    }
}

注册界面

前端代码

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form action="register.do" method="post" onsubmit="return valid()">
    <p>
        账号：<input type="text" name="username" id="username" placeholder="用户名长度在6-12位">
    </p>
    <p>
        密码：<input type="password" name="password" id="password">
    </p>
    <p>
        确认密码：<input type="password" id="repassword">
    </p>
    <p>
        <input type="submit" value="注册">
    </p>
</form>
<script>
    function valid() {

        // 获取两次密码输入的值
        let password = document.querySelector("#password").value;
        let repassword = document.querySelector("#repassword").value;

        let username = document.querySelector("#username").value;

        if(username.length >= 6 && username.length <= 12){
            if(password === repassword) {
                return true;
            }else {
                alert("两次密码输入不一致！");
                return false;
            }
        }else {
            alert("用户名长度不合法！");
            return false;
        }
    }
</script>
</body>
</html>

后端代码

package com.jsoft.morning.servlet;

import com.jsoft.morning.dao.UserDao;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.io.PrintWriter;

@WebServlet("/register.do")
public class RegisterServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String password = request.getParameter("password");

        String sql = "insert into user values (?,?,?)";

        PrintWriter out = response.getWriter();

        UserDao userDao = new UserDao();
        try {
            int i = userDao.update(sql, null, username, password);
            if(i > 0){
                out.print("success!");
            }else {
                out.print("fail!");
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request,response);
    }
}

请求具体内容方法

后端代码

package com.jsoft.morning.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/user.do")
public class UserServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
//        System.out.println(request.getHeader("Host"));
        System.out.println("请求的方法：" + request.getMethod());
        System.out.println("请求的根路径：" + request.getContextPath());
        System.out.println("请求的URI：" + request.getRequestURI());
        System.out.println("请求的URL：" + request.getRequestURL());
        System.out.println("请求的servletPath：" + request.getServletPath());
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}

Key值doget()

根据key获取对应的value

request.getParameter("");

获取一个key对应的多个值，返回值为String类型的数组

String[] hobbies = request.getParameterValues("hobby");
System.out.println(Arrays.toString(hobbies));

获取请求中传过来的参数的key值，根据key值获取对应的value值

Enumeration<String> parameterNames = request.getParameterNames();
while(parameterNames.hasMoreElements()){
        System.out.println(parameterNames.nextElement());
}

可以获取到请求中的所有的键值对，返回值是一个map集合

这个map集合的key是String，value是String[]
username=admin,password=123456,hobby=a,b,c,gender=man

        Map<String, String[]> map = request.getParameterMap();
        for (Map.Entry<String, String[]> name : map.entrySet()) {
            String key = name.getKey();
            String[] value = name.getValue();
            System.out.println(key + "--->" + Arrays.toString(value));
        }

请求转发——由多个servlet处理同一个请求

请求转发是在服务器上完成的，跟客户端是否无关！！！
后端代码demo02------>demo03

package com.jsoft.morning.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/demo02.do")
public class Demo02Servlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        /*
        * 请求转发---由多个servlet来处理同一个请求
        *
        * 请求转发是在服务器上完成的，跟客户端是否无关！！！
        *
        */
        String name = request.getParameter("name");
        System.out.println("Demo02Servlet,name:" + name);
        // getRequestDispatcher参数：转发的servlet的地址
        // forward参数：request,response，把当前servlet用的request和response传给下一个
        request.getRequestDispatcher("demo03.do").forward(request,response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        doGet(request, response);
    }
}

package com.jsoft.morning.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/demo03.do")
public class Demo03Servlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String name = request.getParameter("name");
        System.out.println("Demo03Servlet,name:" + name);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}

作用域：起作用的域demo04--->demo05

例：往request作用域中添加了一个属性name，值是zhangsan
请求转发的时候是同一个request

package com.jsoft.morning.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;
import java.util.Enumeration;

@WebServlet("/demo04.do")
public class Demo04Servlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        request.setAttribute("name","zhangsan");
        // name怎么取出来？
        System.out.println("demo04.do---name：" + request.getAttribute("name"));
        request.getRequestDispatcher("demo05.do").forward(request,response);

        //根据指定的key，删除对应的value
//        request.removeAttribute("name");
        // 获取request作用域中的所有的key
//        Enumeration<String> attributeNames = request.getAttributeNames();

    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}


package com.jsoft.morning.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/demo05.do")
public class Demo05Servlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        System.out.println("demo05.do---name：" + request.getAttribute("name"));
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}

重定向

请求转发和重定向的区别

1.重定向是两次请求，请求转发是一个请求
2.重定向是客户端行为，请求转发是服务器行为
3.重定向浏览器地址会发生改变，转发不会
4.重定向可以定向到任意地址，转发只能项目内转发
后端代码

package com.jsoft.morning.servlet;

import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.*;
import java.io.IOException;

@WebServlet("/demo06.do")
public class Demo06Servlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.getWriter();
        response.setCharacterEncoding("utf-8");
        response.addHeader("content-type","text/html;charset=utf-8");
        response.sendRedirect("success.html");

    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

    }
}
posted @   LJMMJL  阅读(28)  评论(2)  编辑  收藏  举报
相关博文：
· 学习笔记——http
· 学习笔记——request
· 2_Servlet
· JavaWeb2
· Servlet&HTTP&Request笔记
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
点击右上角即可分享
微信分享提示