JDBC 使用之 DataBaseConnection类

package com.how2java.springboot.web;

import javax.xml.transform.Result;
import java.sql.*;

/**
 * @fileName: DataBaseConnection
 * @author: 宇哥
 * @date: 2019/6/25 20:48
 * @description: 数据库连接
 */

public class DataBaseConnection {
    /**
     * 要连接的数据库、数据库账号、密码
     */
    //连接本地数据库
    //public static final String URL = "jdbc:mysql://localhost:3306/aikq?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8";
    // 连接服务器数据库
    public static final String URL = "jdbc:mysql://134.175.88.185:3306/aikq?useSSL=false&serverTimezone=UTC&characterEncoding=utf-8";

    //本地数据库账号
    //public static final String USER = "root";
    //服务器数据库账号
    public static final String USER = "sac";
    public static final String PASSWORD = "123456";


    private static Connection connection = null;

    //静态代码块只在类第一次被加载时执行一次
    static {
        try {
            //1.加载驱动程序     （JVM加载指定的类）
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取数据库连接
            connection = DriverManager.getConnection(URL, USER, PASSWORD);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * @return 返回数据库连接
     */
    public static Connection getConnection() {
        return connection;
    }

    /**
     * @return - 指定表、指定列的最大值
     */
    public static String getMaxValue(String columnName, String tableName) {
        Connection connection = getConnection();
        String SQL = "SELECT MAX(" + columnName + ") FROM " + tableName;
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(SQL);
            ResultSet resultSet = preparedStatement.executeQuery();
            String maxId = "";
            while (resultSet.next()) {
                maxId = resultSet.getString(1);
            }
            return maxId;
        } catch (SQLException e) {
            e.printStackTrace();
            return "";
        }
    }
}

使用示例：

/**
 * @fileName: HelperTeacher
 * @author: 宇哥
 * @date: 2019/7/6 12:38
 * @description: 教师表相关操作
 */
package com.how2java.springboot.web;

import com.fasterxml.jackson.annotation.JsonTypeInfo;

import javax.xml.transform.Result;
import java.net.IDN;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class HelperTeacher {

    /**
     * @param id - 教师id
     * @return 可能携带数据的RecordTeacher对象
     * @throws SQLException 作用：根据id查询教师信息
     */
    public static RecordTeacher queryByTeacherId(String id) throws SQLException {
        System.out.println("根据id查询教师信息");
        Connection connection = DataBaseConnection.getConnection();
        String SQL = "SELECT * FROM teacher WHERE id = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(SQL);
        preparedStatement.setObject(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        RecordTeacher recordTeacher = new RecordTeacher();
        while (resultSet.next()) {
            recordTeacher.setId(resultSet.getString("id"));
            recordTeacher.setName(resultSet.getString("name"));
            recordTeacher.setCollegeId(resultSet.getString("collegeId"));
            recordTeacher.setIdNumber(resultSet.getString("IdNumber"));
            recordTeacher.setSex(resultSet.getString("sex"));

        }
        System.out.println("查询到的数据：" + recordTeacher.toString());
        return recordTeacher;
    }

    /**
     * @param collegeId - 学院id
     * @return - 学院下所有教师信息
     * @throws SQLException 作用：查询某学院所有教师信息
     */
    public static List<RecordTeacher> queryByCollegeId(String collegeId) throws SQLException {
        System.out.println("查询学院" + collegeId + "所有教师信息");
        //1、查询数据
        Connection connection = DataBaseConnection.getConnection();
        String SQL = "SELECT * FROM teacher WHERE collegeId = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(SQL);
        preparedStatement.setObject(1, collegeId);
        ResultSet resultSet = preparedStatement.executeQuery();
        //2、输出数据
        List<RecordTeacher> recordTeacherList = new ArrayList<RecordTeacher>();
        RecordTeacher recordTeacher;
        while (resultSet.next()) {
            recordTeacher = new RecordTeacher();
            recordTeacher.setId(resultSet.getString("id"));
            recordTeacher.setName(resultSet.getString("name"));
            recordTeacher.setCollegeId(resultSet.getString("collegeId"));
            recordTeacher.setIdNumber(resultSet.getString("IdNumber"));
            recordTeacher.setSex(resultSet.getString("sex"));
            recordTeacherList.add(recordTeacher);
        }
        System.out.println("查询结果：" + recordTeacherList.toString());
        return recordTeacherList;
    }

    /**
     * @return - 学校所有教师信息
     * @throws SQLException 数据库操作错误
     */
    public static List<RecordTeacher> queryAll() throws SQLException {
        System.out.println("查询所有教师信息");
        Connection connection = DataBaseConnection.getConnection();
        String SQL = "SELECT * FROM teacher";
        PreparedStatement preparedStatement = connection.prepareStatement(SQL);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<RecordTeacher> recordTeacherList = new ArrayList<RecordTeacher>();
        RecordTeacher recordTeacher;
        while (resultSet.next()) {
            recordTeacher = new RecordTeacher();
            recordTeacher.setId(resultSet.getString("id"));
            recordTeacher.setName(resultSet.getString("name"));
            recordTeacher.setCollegeId(resultSet.getString("collegeId"));
            recordTeacher.setIdNumber(resultSet.getString("IdNumber"));
            recordTeacher.setSex(resultSet.getString("sex"));
            recordTeacherList.add(recordTeacher);
        }
        System.out.println("查询结果：" + recordTeacherList.toString());
        return recordTeacherList;
    }

    /**
     * @param recordTeacher - 要插入的教师信息
     * @return 函数执行结果
     */
    public static String insert(RecordTeacher recordTeacher) {
        System.out.println("插入教师信息：" + recordTeacher.toString());
        Connection connection = DataBaseConnection.getConnection();
//        String SQL = "INSERT INTO teacher(name,collegeId,IdNumber,sex) VALUES(?,?,?,?)";
        String SQL = "INSERT INTO teacher VALUES(?,?,?,?,?)";
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(SQL);
            preparedStatement.setObject(1, recordTeacher.getId());
            preparedStatement.setObject(2, recordTeacher.getName());
            preparedStatement.setObject(3, recordTeacher.getCollegeId());
            preparedStatement.setObject(4, recordTeacher.getIdNumber());
            preparedStatement.setObject(5, recordTeacher.getSex());
            preparedStatement.execute();
            System.out.println("Success");
            createTeacherAccount(recordTeacher);
            return "Success";
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("Failed");
            return "Failed";
        }
    }

    //在账号表中创建一个教师账号
    public static String createTeacherAccount(RecordTeacher recordTeacher) {
        String IdNumber = recordTeacher.getIdNumber();
        String accountId = IdNumber.substring(IdNumber.length() - 6);
        RecordAccount account = new RecordAccount(recordTeacher.getId(), accountId, "teacher", recordTeacher.getName(), recordTeacher.getSex());
        try {
            HelperAccount.insert(account);
            return "account create success";
        } catch (SQLException e) {
            e.printStackTrace();
            return "account create failed";
        }
    }
}