[大数据]mysql(30) : java管理mysql

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

/**
 * @Author: liyue
 * @Date: 2022/03/30/15:09
 * @Description:
 */
public class MysqlInstance {
    /**
     * mysql驱动
     */
    private String driver;

    /**
     * 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
     */
    private String url;

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

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

    private Connection conn;

    public MysqlInstance(String driver, String url, String username, String password) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
        try {
            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 MysqlInstance() {
    }

    public String getDriver() {
        return driver;
    }

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

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    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 static void main(String[] args) {
        MysqlInstance m1 = new MysqlInstance("com.mysql.cj.jdbc.Driver",
                "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",
                "root","123456");
        System.out.println(m1.query("show tables;"));
        m1.close();


        MysqlInstance m2 = new MysqlInstance("com.mysql.cj.jdbc.Driver",
                "jdbc:mysql://127.0.0.1:3306/sys?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL",
                "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>