MySql8.0的JDBC封装类

Java连接mysql8.0封装类

开发这个封装类的目的：有部分新学习的小伙伴对java连接mysql的不熟悉，同时又有部分朋友想体验开箱即用的快感，特此设计了这个封装类。提供调用的接口，封闭内部连接结构。代码粗糙，仅供参考。
GitHub地址：https://github.com/nicesiri4268/MySQLConnection

MySQL8.0的JDBC简介

新的MySQL8.0使用的JDBC需要在com.mysql.cj.jdbc.Driver中导入，驱动下载地址：菜鸟教程MySQLConnection/J 8.0 jar包下载
在java中载入jdbc的语句

Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test_demo?useSSL=false&serverTimezone=UTC","root","password");

驱动连接信息类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class MysqlMessage {
    private String DBName = "";
    private String DBUser = "";
    private String DBUserserPasswd = "";
    private String connName = "";
    private String allMessage = "";
    private Connection connection = null;


    public MysqlMessage(String DBName, String DBUser, String DBUserserPasswd) {
        /*
        * 对类进行初始化操作
        * DBName 数据库名称，具体看你的数据库设置名
        * DBUser 数据库用户名称
        * DBUserPasswd 数据库用户密码
        * */
        super();
        this.DBName = DBName;
        this.DBUser = DBUser;
        this.DBUserserPasswd = DBUserserPasswd;//为了安全这里可以更改
        this.connName = getConnName();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            this.connection = DriverManager.getConnection(this.connName,
                    this.DBUser,
                    this.DBUserserPasswd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("无法找到类名");
        } catch (SQLException throwable) {
            throwable.printStackTrace();
            //希望下一步可以把异常放置到一个dialog文件里面
            System.out.println("数据库连接失败");
        }
    }

    public MysqlMessage() {
        super();
    }

    //为了重设连接名字需要更改时使用
    public void setDBName(String DBName) {
        this.DBName = DBName;
    }

    public void setDBUser(String DBUser) {
        this.DBUser = DBUser;
    }

    public void setDBUserserPasswd(String DBUserserPasswd) {
        this.DBUserserPasswd = DBUserserPasswd;
    }

    private String getConnName() {
        return "jdbc:mysql://localhost:3306/" + DBName + "?useSSL=false&serverTimezone=UTC";
    }

    public Connection getConnection() {
        return connection;
    }

    public String getAllMessage() {
        allMessage = "数据库名称：" + this.DBName + "/n用户名：" + this.DBUser + "JDBC连接：" + this.connName;
        return allMessage;
    }
}

使用Connection创建PrepareStatement对象预编译SQL

import com.Mysql.Exception.MysqlConnectException;

import java.sql.*;

public class MysqlConnect {
    private Connection connection;
    private PreparedStatement preparedStatement = null;
    private ResultSet resultSet = null;
    private int resultCounts = -1;
    /*
     * 使用MySQLMessage产生的Connection对象，
     * 创建PrepareStatement对象
     * 预编译SQL
     * 对SQL赋值：columnsType数组（int）,values数组（String）
     * 使用方法getResultType（）， 返回一个ResultType类（封装Result和ResultCount属性）
     * */

    public MysqlConnect(MysqlMessage mysqlMessage) {
        connection = mysqlMessage.getConnection();//从mysqlMessage获取连接
    }

    public String setPstmtParam(String SQL, int[] columnsType, String[] values) {
        /*
         * 预编译命令SQL
         * 需要提供一个int类型的类型标识数组，和一个String类型的值数组
         * Types.Integer 为类型标识数组的值
         * 结果集放置在MySQLConnection类中,调用getResultType获取
         * 返回值为String类型，当执行成功时返回”执行成功“
         * 同时有可能出现的错误有，SQL为空，SQL预编译失败，SQL
         */

        if (SQL == null) return "SQL为空";
        try {
            preparedStatement = connection.prepareStatement(SQL);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return "预编译失败";
        }
        if (columnsType != null && values != null && columnsType.length == values.length) {
            //分类填入预编译类中
            for (int i = 0; i < columnsType.length; i++) {
                try {
                    switchColumnsType(columnsType, values, i);//预编译后的赋值
                } catch (SQLException throwable) {
                    throwable.printStackTrace();
                    return "第" + i + "个赋值失败";
                } catch (MysqlConnectException throwable) {
                    throwable.printStackTrace();
                    return "赋值失败" + "编号" + i;
                }
            }
        } else if (columnsType == null && values == null) {
            System.out.print("");
            //允许类型数组或者值数组为空
        } else {
            return "类型数组或值数组出现错误";
        }

        try {
            //开始执行SQL预编译命令，并且保存结果集到resultSet
            if (preparedStatement.execute()) {
                //execute()返回的是一个boolean值,代表两种不同的操作啊
                // getResultSet()返回的是结果集,而getUpdateCount()返回的是更新的记数
                resultSet = preparedStatement.executeQuery();
            } else {
                resultCounts = preparedStatement.getUpdateCount();//插入或修改操作
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return "预编译成功";
    }

    /**
     * @param columns 参数1 int数组存储着有关value的类型
     * @param values  参数2 String数组 存储着预编译后的要赋的值
     * @param i       参数3 循环的次数
     * @apiNote 可以添加Type.的分支以适应更多类型
     */
    private void switchColumnsType(int[] columns, String[] values, int i) throws SQLException {
        switch (columns[i]) {
            case Types.INTEGER: {
                preparedStatement.setInt(i + 1, Integer.parseInt(values[i]));
                break;
            }
            case Types.SMALLINT:{
                preparedStatement.setShort(i+1,Short.parseShort(values[i]));
                break;
            }
            case Types.FLOAT: {
                preparedStatement.setFloat(i + 1, Float.parseFloat(values[i]));
                break;
            }
            case Types.DOUBLE: {
                preparedStatement.setDouble(i + 1, Double.parseDouble(values[i]));
                break;
            }
            case Types.VARCHAR:
            case Types.CHAR: {
                preparedStatement.setString(i + 1, values[i]);
                break;
            }
            default: {
                throw new MysqlConnectException("第" + (i + 1) + "个参数出现问题");
            }
            //可自行添加输入项
        }
    }

    public PreparedStatement getPreparedStatement() {
        if (preparedStatement != null) {
            return preparedStatement;
        } else {
            return null;
        }
    }

    public ResultType getResultType() {
        //返回sql语句结果集
        if (resultSet != null) {
            return new ResultType(resultSet);
        } else if (resultCounts >= 0) {
            return new ResultType(resultCounts);
        } else {
            return null;
        }
    }

    public boolean close() {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException throwables) {
            System.out.println("关闭数据库失败");
            throwables.printStackTrace();
        }
        return false;
    }
}

连接异常类

public class MysqlConnectException extends RuntimeException {
    /**
     * @program: MySql工具类
     * @description:MysqlConnectionException的异常类
     * @author: nicesiri
     * @create: 2020-05-15 00:10
     */
    public MysqlConnectException() {
        super();
    }

    public MysqlConnectException(String string) {
        super(string);
    }

}

ResultType类

import java.sql.ResultSet;

public class ResultType {
    /**
     * @program: MySql工具类
     * @description: 控制返回的是ResultSet还是ResultCount，提供返回方法
     * @author: nicesiri
     * @create: 2020-05-24 20:38
     */

    private ResultSet resultSet = null;
    private int resultCount = -1;
    private boolean resultSetFlag = false;

    public ResultType() {
        super();
    }

    public ResultType(ResultSet resultSet) {
        super();
        if (resultSet != null) {
            this.resultSet = resultSet;
            this.resultSetFlag = true;
        }
    }

    public ResultType(int resultCount) {
        super();
        if (resultCount >= 0) {
            this.resultCount = resultCount;
        }
    }

    public ResultSet getResultSet() {
        if (resultSet != null) {
            return resultSet;
        }
        return null;
    }

    public ResultType setResultSet(ResultSet resultSet) {
        this.resultSet = resultSet;
        return this;
    }

    public int getResultCount() {
        if (resultCount != -1 && resultSetFlag == false) {
            return resultCount;
        }
        return -1;
    }

    public ResultType setResultCount(int resultCount) {
        this.resultCount = resultCount;
        return this;
    }

    public boolean isResultSetFlag() {
        return resultSetFlag;
    }

}

结果集处理类

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

public class MysqlResultManage {
    private ResultSet resultSet = null;
    private int resultCount = -1;
    private String[] columnsName = null;
    private DoubleArray[] doubleArrays;
    private Map<String, ArrayList<String>> map;

    /*
     * 从ResultType传入一个ResultSet或者resultCount
     * 调用 manageResult()方法，返回一个DataTable对象
     * 或者直接调用getResultCount()返回一个int值，表示修改了多少行
     * */
    public MysqlResultManage(ResultType resultType) {
        if (resultType.getResultSet() != null) {
            this.resultSet = resultType.getResultSet();
        }
        if (resultType.getResultCount() >= -1) {
            this.resultCount = resultType.getResultCount();
        }
        map = new ConcurrentHashMap<>();//初始化map
    }

    public DataTable manageResult() {
        if (resultSet == null) {
            return null;
        }
        try {
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            //获取结果集的元数据
            int n = resultSetMetaData.getColumnCount();//计算有多少行
            columnsName = new String[n];
            doubleArrays = new DoubleArray[n];
            for (int i = 0; i < n; i++) {
                doubleArrays[i] = new DoubleArray();//初始化doubleArrays类，需要注意java底层编译逻辑
                String valueName = resultSetMetaData.getColumnName(i + 1);//此处应该为i+1，为第一行
                //resultSetMetaData.getColumnName()，该函数返回值从1开始
                columnsName[i] = valueName;
                //保存结果集的列名，初始化doubleArrays类
            }

            while (resultSet.next()) {
                //一个arraylist的数组
                for (int i = 0; i < n; i++) {//循环获取列名，提取表中的数据值
                    String arrayListValue;
                    Object temptest = resultSet.getObject(columnsName[i]);
                    if (temptest != null) {
                        //此处放置返回的是空值，导致出现nullpoint异常
                        arrayListValue = temptest.toString();
                    } else {
                        arrayListValue = null;
                    }
                    //将所有表的信息都转换为String类型
                    doubleArrays[i].add(arrayListValue);//赋值到DoubleArrays内部类的Arraylist里面
                }
            }

            for (int i = 0; i < n; i++) {
                //将结果集封装到Map中
                //有没有可能多线程的同步封装呢？
                ArrayList<String> temp = doubleArrays[i].getArrayList();
                map.put(columnsName[i], temp);

            }
            return new DataTable(columnsName, map);//返回一个DataTable类，包含完整信息
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public int getResultCount() {
        if (resultCount > -1)
            return this.resultCount;
        else {
            return -1;
        }
    }

    //需要处理查询单行，多行
    class DoubleArray {
        //内部类，实现的是DoubleArray内部封装ArrayList<String>
        //作用：ArrayList类的数组
        private final ArrayList<String> arrayList;

        public DoubleArray() {
            arrayList = new ArrayList<>();
        }

        public void add(String value) {
            arrayList.add(value);
        }

        public ArrayList<String> getArrayList() {
            return arrayList;
        }
    }
}

数据表类

import java.util.*;

public class DataTable {
    /**
     * @program: MySql工具类
     * @description:
     * @author: nicesiri
     * @create: 2020-05-22 19:56
     */
    private String[] column;
    private String[][] row;
    private int columnCount;
    private int rowCount;
    private Map<String, ArrayList<String>> map;

    public DataTable() {
        super();
    }

    public DataTable(String[] column, Map<String, ArrayList<String>> map) {
        super();
        this.column = column;
        this.map = map;
        this.setDataTable();
    }

    public DataTable(String[] column, String[][] row, int columnCount, int rowCount) {
        super();
        this.column = column;
        this.row = row;
        this.columnCount = columnCount;
        this.rowCount = rowCount;
    }

    public void setDataTable() {
        /**
         * 设置结果集的表列，无论是一行还是多行，无论一列还是多列
         **/
        columnCount = map.size();
        //System.out.println(map.get(0));
        //需要使用迭代器的方式来提取出 map中的值，或者返回一个列名回来获取存储在map中的值

        ArrayList<String> temp = map.get(column[0]);
        rowCount = temp.size();//行数
        row = new String[rowCount][columnCount];
        //此处断点查看是否获取到了map中ArrayList的值
        for (int i = 0; i < rowCount; i++) {
            //二维数组的定义与用法
            //这里是第一行
            for (int j = 0; j < columnCount; j++) {
                temp = map.get(column[j]);//获取储存在map中的ArrayList数组，也就是一列值
                row[i][j] = temp.get(i);
            }
        }
    }

    public String[] getColumn() {
        return column;
    }

    public DataTable setColumn(String[] column) {
        this.column = column;
        return this;
    }

    public String[][] getRow() {
        return row;
    }

    public DataTable setRow(String[][] row) {
        this.row = row;
        return this;
    }

    public int getColumnCount() {
        return columnCount;
    }

    public DataTable setColumnCount(int columnCount) {
        this.columnCount = columnCount;
        return this;
    }

    public int getRowCount() {
        return rowCount;
    }

    public DataTable setRowCount(int rowCount) {
        this.rowCount = rowCount;
        return this;
    }

    public Map<String, ArrayList<String>> getMap() {
        return map;
    }

    public DataTable setMap(Map<String, ArrayList<String>> map) {
        this.map = map;
        return this;
    }

}

测试模板

import com.Mysql.DataTable;
import com.Mysql.MysqlConnect;
import com.Mysql.MysqlMessage;
import com.Mysql.MysqlResultManage;
import java.sql.Types;
import java.util.Scanner;

public class Demo {
    /**
     * @program: MySql工具类
     * @description: MySQL8.0封装类测试
     * @author: nicesiri
     * @create: 2020-05-27 11:39
     */
    public Demo() {
        super();
    }

    public static void main(String[] args) {
        Demo demo = new Demo();
        demo.insertSQL();
        demo.SearchSQL();
        demo.updateSQL();
        demo.SearchSQL();
        demo.deleteSQL();
    }

    private static void showRowValues(DataTable dataTable) {
        int m = dataTable.getColumnCount();
        int n = dataTable.getRowCount();
        String[][] row = dataTable.getRow();
        for (int i = 0; i < n; i++) {
            for (int j = 0; j < m; j++) {
                System.out.print(row[i][j] + "  ");
                if (j == m - 1) {
                    System.out.print("\n");
                }
            }
        }
    }

    private static void showColumnName(DataTable dataTable) {
        int n = dataTable.getColumnCount();
        String[] column = dataTable.getColumn();
        for (int i = 0; i < n; i++) {
            System.out.print(column[i] + "  ");
            if (i == n - 1) {
                System.out.println("");
            }
        }
    }

    public void SearchSQL() {
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String user = input.nextLine();
        System.out.print("请输入密码： ");
        String passwd = input.nextLine();
        String searchSQL = "select * from studenttest";
        MysqlMessage mysqlMessage = new MysqlMessage("test", user, passwd);
        //设置访问的数据库，用户名，密码
        MysqlConnect mysqlConnect = new MysqlConnect(mysqlMessage);
        //构建连接类
        mysqlConnect.setPstmtParam(searchSQL, null, null);
        //预编译
        MysqlResultManage mysqlResultManage = new MysqlResultManage(mysqlConnect.getResultType());
        //构建结果类
        DataTable dataTable = mysqlResultManage.manageResult();
        //返回赋好值的结果表
        showColumnName(dataTable);
        showRowValues(dataTable);
    }

    public void insertSQL() {
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String user = input.nextLine();
        System.out.print("请输入密码： ");
        String passwd = input.nextLine();
        String insertSQL = "insert into studenttest(Sno, Sname, Ssex, Sage, Sdept)values(?,?,?,?,?)";
        MysqlMessage mysqlMessage = new MysqlMessage("test", user, passwd);
        MysqlConnect mysqlConnect = new MysqlConnect(mysqlMessage);
        mysqlConnect.setPstmtParam(insertSQL,
                new int[]{Types.CHAR, Types.CHAR, Types.CHAR, Types.SMALLINT, Types.CHAR},
                new String[]{"201215128", "张三", "男", "20", "MS"});
        MysqlResultManage mysqlResultManage = new MysqlResultManage(mysqlConnect.getResultType());
        //调用结果集的getresultCount方法返回修改的行数
        System.out.println(mysqlResultManage.getResultCount());


    }
    public void deleteSQL(){
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String user = input.nextLine();
        System.out.print("请输入密码： ");
        String passwd = input.nextLine();
        String deleteSQL = "delete from Studenttest where Sno = ?";
        MysqlMessage mysqlMessage = new MysqlMessage("test", user, passwd);
        MysqlConnect mysqlConnect = new MysqlConnect(mysqlMessage);
        mysqlConnect.setPstmtParam(deleteSQL,
                new int[]{Types.CHAR},
                new String[]{"201215127"});
        MysqlResultManage mysqlResultManage = new MysqlResultManage(mysqlConnect.getResultType());
        System.out.println(mysqlResultManage.getResultCount());
    }
    public void updateSQL(){
        Scanner input = new Scanner(System.in);
        System.out.print("请输入用户名：");
        String user = input.nextLine();
        System.out.print("请输入密码： ");
        String passwd = input.nextLine();
        String updateSQL = "update Studenttest set Sname = ? where Sno = ?";
        MysqlMessage mysqlMessage = new MysqlMessage("test", user, passwd);
        MysqlConnect mysqlConnect = new MysqlConnect(mysqlMessage);
        mysqlConnect.setPstmtParam(updateSQL,
                new int[]{Types.CHAR,Types.CHAR},
                new String[]{"张三","201215127"});
        MysqlResultManage mysqlResultManage = new MysqlResultManage(mysqlConnect.getResultType());
        System.out.println(mysqlResultManage.getResultCount());
    }
}