mysql(30) : java管理mysql8(开发用轻量版)_mysql 8 java驱动-优快云博客

代码



import java.sql.*;
import java.util.*;

/**
 * @Author: liyue
 * @Date: 2022/03/30/15:09
 * @Description:
 */
public class Mysql8Instance {

    /**
     * mysql连接地址
     * 示例 jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL
     */

    /**
     * mysql驱动
     */
    private String driver = "com.mysql.cj.jdbc.Driver";

    /**
     * 连接地址
     */
    private String host;

    /**
     * mysql端口
     */
    private Integer port;

    /**
     * 默认数据库
     */
    private String database;

    /**
     * mysql账号名
     */
    private String username;

    /**
     * mysql密码
     */
    private String password;

    /**
     * 是否打印日志, 默认否
     */
    private Boolean printLog = Boolean.FALSE;

    /**
     * 查询结果是否返回字段名, 默认否
     */
    private Boolean returnColumnName = Boolean.FALSE;

    private Connection conn;

    public Mysql8Instance(String host, Integer port, String database, String username, String password) {
        this.host = host;
        this.port = port;
        this.database = database;
        this.username = username;
        this.password = password;
        try {
            String url = "jdbc:mysql://" + host + ":" + port + "/" + database + "?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL";
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            System.err.println("连接池获取失败");
            throw new RuntimeException(e);
        }
    }

    public void execute(String sql) {
        try (Statement statement = conn.createStatement();) {
            long startTime = System.currentTimeMillis();
            Class.forName(driver);
            statement.execute(sql);
            if (printLog) {
                System.out.println("执行sql成功,sql:[" + sql + "], 运行时长:" + (System.currentTimeMillis() - startTime) + " 毫秒");
            }
        } catch (Exception e) {
            System.err.println("sql执行失败:[" + sql + "]");
            throw new RuntimeException(e);
        }
    }

    public List<List<String>> query(String sql) {
        long startTime = System.currentTimeMillis();
        try (Statement stmt = conn.createStatement();) {
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData resultSetMetaData = rs.getMetaData();
            int column = resultSetMetaData.getColumnCount();
            List<List<String>> result = new LinkedList<>();
            if (returnColumnName) {
                List<String> title = new ArrayList<>();
                for (int i = 1; i <= column; i++) {
                    title.add(resultSetMetaData.getColumnName(i));
                }
                result.add(title);
            }
            while (rs.next()) {
                List<String> list = new LinkedList<>();
                for (int i = 1; i <= column; i++) {
                    list.add(rs.getString(i));
                }
                result.add(list);
            }
            if (printLog) {
                System.out.println("查询sql:[" + sql + "], 运行时长:" + (System.currentTimeMillis() - startTime) + " 毫秒");
            }
            return result;
        } catch (Exception e) {
            System.err.println("sql执行失败,sql:[" + sql + "]");
            e.printStackTrace();
            return null;
        }
    }

    public void print(List<List<String>> res) {
        Map<Integer, Integer> columnMaxLength = new HashMap<>();
        for (int i = 0; i < res.size(); i++) {
            List<String> strings = res.get(i);
            for (int k = 0; k < strings.size(); k++) {
                if (!columnMaxLength.containsKey(k)) {
                    columnMaxLength.put(k, 0);
                }
                if (strings.get(k) == null) {
                    continue;
                }
                int length = strings.get(k).getBytes().length;

                if (length > columnMaxLength.get(k)) {
                    columnMaxLength.put(k, length);
                }
            }
        }
        for (int i = 0; i < res.size(); i++) {
            List<String> strings = res.get(i);
            for (int k = 0; k < strings.size(); k++) {
                System.out.format("%-" + (columnMaxLength.get(k) + 5) + "s", strings.get(k));
            }
            System.out.println();
        }
    }

    @Override
    protected void finalize() throws Throwable {
        close();
        super.finalize();
    }

    public void close() {
        try {
            conn.close();
            System.out.println("连接关闭成功");
        } catch (Exception e) {
            System.err.println("连接关闭失败");
            throw new RuntimeException(e);
        }
    }

    public Mysql8Instance() {
    }

    public String getDriver() {
        return driver;
    }

    public void setDriver(String driver) {
        this.driver = driver;
    }

    public String getHost() {
        return host;
    }

    public void setHost(String host) {
        this.host = host;
    }

    public Integer getPort() {
        return port;
    }

    public void setPort(Integer port) {
        this.port = port;
    }

    public String getDatabase() {
        return database;
    }

    public void setDatabase(String database) {
        this.database = database;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public Connection getConn() {
        return conn;
    }

    public void setConn(Connection conn) {
        this.conn = conn;
    }

    public Boolean getPrintLog() {
        return printLog;
    }

    public void setPrintLog(Boolean printLog) {
        this.printLog = printLog;
    }

    public Boolean getReturnColumnName() {
        return returnColumnName;
    }

    public void setReturnColumnName(Boolean returnColumnName) {
        this.returnColumnName = returnColumnName;
    }
}

测试

    public static void main(String[] args) {
        
        Mysql8Instance m1 = new Mysql8Instance("127.0.0.1",3307,"test","root","123456");
        System.out.println(m1.query("show tables;"));
        m1.close();


        
        Mysql8Instance m2 = new Mysql8Instance("127.0.0.1",3307,"sys","root","123456");
        System.out.println(m2.query("show tables;"));
        m2.close();
    }

mysql驱动

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.18</version>
</dependency>