mysql(30) : java管理mysql8(开发用轻量版)

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * @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 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();) {
            Class.forName(driver);
            statement.execute(sql);
            System.out.println("执行sql成功,sql:" + sql);
        } catch (Exception e) {
            System.err.println("sql执行失败:" + sql);
            throw new RuntimeException(e);
        }
    }

    public List<List<Object>> query(String sql) {
        List<List<Object>> lists = new LinkedList<>();
        long startTime = System.currentTimeMillis();
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            List<Object> titles = new LinkedList<>();
            ResultSetMetaData rsmd = rs.getMetaData();
            final int columnCount = rsmd.getColumnCount();
            // 结果为空时直接返回，方便后续处理。关于如何判空参见下面链接
            boolean rsIsEmpty = !rs.isBeforeFirst();
            if (rsIsEmpty) {
                return lists;
            }
            // 添加列名方便后续处理
            for (int i = 1; i <= columnCount; i++) {
                titles.add(rsmd.getColumnName(i));
            }
            lists.add(titles);
            while (rs.next()) {
                List<Object> list = new LinkedList<>();
                for (int i = 1; i <= columnCount; i++) {
                    list.add((rs.getString(i) == null || rs.getString(i).equals("")) ? "" : rs.getString(i).trim());
                }
                lists.add(list);
            }
        } catch (SQLException e) {
            System.err.println("查询sql失败,sql:" + sql);
            throw new RuntimeException(e);
        }
        long endTime = System.currentTimeMillis();
        System.out.println("查询sql:" + sql + ", 运行时长:" + (endTime - startTime) + " ms, 数据量: " + lists.size());
        return lists;
    }

    @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 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>