Book Management System (JSP + JDBC + Servlet) Implementation-03: Project Construction & Tool Class Implementation & Dependency Import

01: Process analysis and database table building phase
02: Write and configure filters (to prevent page garbled)
03: Project construction & tool class implementation & dependency import
04: Entity class for database creation (pojo)
05: Realize login function
06: Query all books function
07: Fuzzy query function
08: Implementation of all functions on the page (download source code attached)

1.4 Create a project (IDEA will be used throughout the rest)

可以不用Maven项目, 我只是为了方便导入依赖才选择的Maven项目,你们只需要导入相关的依赖就行了。

Insert picture description here

Select the Maven project, Maven is a project management tool, the biggest advantage is that it can easily import dependencies

Insert picture description here


Basic information of configuration items

Insert picture description here

Add web support

Insert picture description here

1.5 Writing the database configuration file

Insert picture description here

database.properties

# 配置数据库驱动
driver=com.mysql.cj.jdbc.Driver
# 数据库地址
url=jdbc:mysql://localhost:3306/cqutlms?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
# 配置数据库用户名
user=root
# 配置密码
password=317525

1.6 Establish a public class for operating the database

遵循高耦合,低内聚的思想,封装公共的工具类是非常好的习惯

Insert picture description here


BaseDao.java

package com.tian.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * ClassName: BaseDao
 * Description: 操作数据库的基类(工具类)
 *
 * @author Tianjiao
 * @date 2021/5/27 21:56
 */
public class BaseDao {
    // 静态代码块,在类加载的时候执行
    static {
        init();
    }

    private static String driver;
    private static String url;
    private static String user;
    private static String password;

    /**
     * MethodName: init
     * Description: 初始化连接参数,从配置文件里获得
     *
     * @return void
     * @date 2021/5/27 22:00
     * @params: []
     * @author Tianjiao
     */
    public static void init() {
        Properties params = new Properties();
        InputStream is = BaseDao.class.getClassLoader().getResourceAsStream("database.properties");
        try {
            params.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
        driver = params.getProperty("driver");
        url = params.getProperty("url");
        user = params.getProperty("user");
        password = params.getProperty("password");
    }

    /**
     * MethodName: getConnection
     * Description: 获取数据库连接
     *
     * @return java.sql.Connection
     * @date 2021/5/27 22:00
     * @params: []
     * @author Tianjiao
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            //   加载数据库驱动
            Class.forName(driver);
            connection = DriverManager.getConnection(url, user, password);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return connection;
    }

    /**
     * MethodName: execute
     * Description: 查询操作
     *
     * @return java.sql.ResultSet
     * @date 2021/5/27 22:01
     * @params: [connection, preparedStatement, rs, sql, params]
     * @author Tianjiao
     */
    public static ResultSet execute(Connection connection, PreparedStatement preparedStatement, ResultSet rs,
                                    String sql, Object[] params) throws Exception {
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            // 占位符从1开始,但是数组是从0开始
            preparedStatement.setObject(i + 1, params[i]);
        }
        rs = preparedStatement.executeQuery();
        return rs;
    }

    /**
     * MethodName: execute
     * Description: 增删改操作
     *
     * @return int
     * @date 2021/5/27 22:02
     * @params: [connection, preparedStatement, sql, params]
     * @author Tianjiao
     */
    public static int execute(Connection connection, PreparedStatement preparedStatement,
                              String sql, Object[] params) throws Exception {
        int updateRows = 0;
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 0; i < params.length; i++) {
            preparedStatement.setObject(i + 1, params[i]);
        }
        updateRows = preparedStatement.executeUpdate();
        return updateRows;
    }

    /**
     * MethodName: closeResource
     * Description: 关闭资源
     *
     * @return boolean
     * @date 2021/5/27 22:02
     * @params: [connection, preparedStatement, rs]
     * @author Tianjiao
     */
    public static boolean closeResource(Connection connection, PreparedStatement preparedStatement, ResultSet rs) {
        boolean flag = true;
        if (rs != null) {
            try {
                rs.close();
                rs = null;
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
                // 垃圾回收, 设置为空后, JVM会自动回收
                preparedStatement = null;
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }
        if (connection != null) {
            try {
                connection.close();
                // 垃圾回收, 设置为空后, JVM会自动回收
                connection = null;
            } catch (SQLException e) {
                e.printStackTrace();
                flag = false;
            }
        }
        return flag;
    }
}

Insert picture description here

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.tian</groupId>
    <artifactId>cqutlms</artifactId>
    <version>1.0-SNAPSHOT</version>
    <dependencies>
        <!-- Servlet 依赖 -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
        </dependency>
        <!-- Jsp 依赖 -->
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>javax.servlet.jsp-api</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!-- jstl表达式的依赖 -->
        <dependency>
            <groupId>javax.servlet.jsp.jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
        <!-- standard标签库 -->
        <dependency>
            <groupId>taglibs</groupId>
            <artifactId>standard</artifactId>
            <version>1.1.2</version>
        </dependency>
        <!--连接数据库的驱动-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.25</version>
        </dependency>
    </dependencies>
</project>