🥼JavaWeb~(陆)JDBC,JUnit,SMBMS项目

JDBC(复习)
JUnit
SMBMS

JDBC(复习)

JDBC 连接 🚀 进修 MySQL~(肆)

事务 🚀 进修 MySQL~(伍)

JUnit

在一个方法上标记 @Test 注解后,这个方法可以脱离 Main 直接运行.

依赖 (一般创建项目时自带)

<!--单元测试-->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

例子:

import org.junit.Test;

public class JDBCTest {
  @Test
  public void test() {
    System.out.println("123");
  }
}

SMBMS

项目代码: https://github.com/Weidows/Java/tree/master/JavaWeb/SMBMS

可以拉取代码直接看,下面的笔记着重讲从零开发的流程
我的这个项目应该是跑不起来,在 web,xml 中 filter 那里有个 bug,不过无大碍,知道怎么个原理就好.

搭建

Maven-webapp 项目,补全 src/resources,添加依赖,连接 Tomcat 和 MySQL,修改 web.xml 版本

依赖:

<dependencies>
  <dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.1</version>
    <scope>compile</scope>
  </dependency>
  <!-- servlet依赖 -->
  <dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>4.0.1</version>
    <scope>provided</scope>
  </dependency>
  <!-- JSP依赖 -->
  <dependency>
    <groupId>javax.servlet.jsp</groupId>
    <artifactId>javax.servlet.jsp-api</artifactId>
    <version>2.3.3</version>
    <scope>provided</scope>
  </dependency>
  <!-- JSTL表达式依赖 -->
  <dependency>
    <groupId>javax.servlet</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>org.apache.tomcat</groupId>
    <artifactId>jsp-api</artifactId>
    <version>6.0.13</version>
  </dependency>
  <dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter-api</artifactId>
    <version>${junit.version}</version>
    <scope>test</scope>
  </dependency>
  <dependency>
    <groupId>org.junit.jupiter</groupId>
    <artifactId>junit-jupiter-engine</artifactId>
    <version>${junit.version}</version>
    <scope>test</scope>
  </dependency>
  <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
  <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.23</version>
  </dependency>
</dependencies>

更改版本号:web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
        version="4.0"
        metadata-complete="true">
  <display-name>Archetype Created Web Application</display-name>

</web-app>

后端项目结构

|> src/main
  |> java/com.weidows
    |> dao/mapper           // data access object (对应pojo的CRUD类)
    |> filter               // 过滤器
    |> pojo/entity          // Plain Ordinary Java Object (ORM映射的实体类,内部一般只有getter和setter)
    |> service              // 业务类 (操作dao)
    |> servlet/controller   // servlet后端
    |> utils                // 工具类
    |> bean                 // spring中 == entity + dao
  |> resources              // 资源文件

-------

pojo对应数据库中每个记录
-> 在dao中编写操作pojo的SQL/Java代码,实现CRUD的"方法"
-> 在service中通过Java编写事务,并调用dao中CRUD方法实现增删改查"功能"
-> 在servlet就是对应请求和响应来做业务,调用service中的方法.

-------

pojo为最底层,不涉及对象增删操作
-> dao层
  -> BaseDao实现创建connection/preparedStatement对象,以及回收connection/preparedStatement/resultSet的方法.
  -> 进一步的dao类中进行创建resultSet对象,并回收resultSet/preparedStatement对象
-> 在service层进行connection的关闭,捕获dao层抛出的异常,且此层不再向上抛出异常.

连接配置文件

(resources/db.properties)

driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/smbms?useSSL=false&useUnicode=true&characterEncoding=utf-8
user=root
password=123456

ORM 映射

(编写 pojo 下的实体类)

初始化 SQL: https://github.com/Weidows/Java/blob/master/JavaWeb/SMBMS/smbms.sql

pojo: https://github.com/Weidows/Java/blob/master/JavaWeb/SMBMS/src/main/java/com/weidows/pojo

静态基本类

https://github.com/Weidows/Java/blob/master/JavaWeb/SMBMS/src/main/java/dao/BaseDao.java

package com.weidows.dao;

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

/**
 * 操作数据库的基类--静态类
 */
public class BaseDao {

  private static final String driver;
  private static final String url;
  private static final String username;
  private static final String password;

  private static Connection connection = null;
  private static PreparedStatement preparedStatement = null;


  //静态代码块,在类加载的时候执行
  static {
    String configFile = "db.properties";
    InputStream inputStream = BaseDao.class.getClassLoader().getResourceAsStream(configFile);
    Properties properties = new Properties();
    try {
      properties.load(inputStream);
    } catch (IOException e) {
      e.printStackTrace();
    }
    driver = properties.getProperty("driver");
    url = properties.getProperty("url");
    username = properties.getProperty("username");
    password = properties.getProperty("password");
  }


  /**
   * 获取数据库连接
   *
   * @return connection
   */
  public static Connection getConnection() {
    if (connection == null) {
      try {
        Class.forName(driver);
        connection = DriverManager.getConnection(url, username, password);
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return connection;
  }

  /**
   * 查询数据库的公共操作
   *
   * @param connection
   * @param rs
   * @param sql
   * @param params
   * @return
   */
  public static ResultSet execute(Connection connection, ResultSet rs, String sql, Object[] params) {
    try {
      preparedStatement = connection.prepareStatement(sql);
      for (int i = 0; i < params.length; i++) {
        preparedStatement.setObject(i + 1, params[i]);
      }
      rs = preparedStatement.executeQuery();
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return rs;
  }

  /**
   * 更新数据库的公共操作
   *
   * @param connection
   * @param sql
   * @param params
   * @return
   * @throws Exception
   */
  public static int execute(Connection connection, String sql, Object[] params) {
    int updateRows = 0;
    try {
      preparedStatement = connection.prepareStatement(sql);
      for (int i = 0; i < params.length; i++) {
        preparedStatement.setObject(i + 1, params[i]);
      }
      updateRows = preparedStatement.executeUpdate();
    } catch (SQLException throwables) {
      throwables.printStackTrace();
    }
    return updateRows;
  }

  /**
   * 释放资源
   *
   * @param connection
   * @param pstm
   * @param rs
   * @return
   */
  public static boolean closeResource(Connection connection, PreparedStatement pstm, ResultSet rs) {
    boolean flag = true;
    try {
      if (rs != null) {
        rs.close();
        rs = null;//GC回收
      }
      if (pstm != null) {
        pstm.close();
        pstm = null;//GC回收
      }
      if (connection != null) {
        connection.close();
        connection = null;//GC回收
      }
    } catch (SQLException e) {
      e.printStackTrace();
      flag = false;
    }
    return flag;
  }
}

题外话

一个编程书写习惯: 方法中首先把需要用到的变量提出

就像这里的三个变量,在一开始提出来,就像是类里面的 field,使用起来比较规范,容易排查

public User getLoginUser(Connection connection, String userCode) {
  PreparedStatement pstm = null;
  ResultSet resultSet = null;
  User user = null;
  try {
    if (null != connection) {
      String sql = "select * from smbms_user where userCode=?";
      Object[] params = {userCode};
      resultSet = BaseDao.execute(connection, pstm, resultSet, sql, params);
      if (resultSet.next()) {
        user = setData(resultSet);
      }
    }
    BaseDao.closeResource(null, pstm, resultSet);
  } catch (SQLException throwables) {
    throwables.printStackTrace();
  }
  return user;
}