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层抛出的异常,且此层不再向上抛出异常.
20210424112946

连接配置文件

(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;
    }

分割线

实现登录

  • 在这里复现一下"登录"业务,其他功能以此类推.

    这里着重过程(自上而下),具体怎么实现的业务建议结合源码来学习.

    20210422103020
    20210425232035

分割线

项目总结构

20210426105422