mysql(26) : mysql8輕量java工具類

maven依賴

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

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

/**
 * @Auther: liyue
 * @Date: 2021/4/21 18:10
 * @Description:
 */
public class MysqlUtil {

    private static final String driver = "com.mysql.cj.jdbc.Driver";
    // 獲取mysql連接地址
    private static final String url = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&useSSL=false&characterEncoding=utf-8&serverTimezone=GMT%2b8&allowMultiQueries=true";
    // 資料名稱
    private static final String username = "root";
    // 資料庫密碼
    private static final String password = "123456";

    private static Connection conn;

    static {
        try {
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            System.err.println("連接池獲取失敗");
            throw new RuntimeException(e);
        }
    }


    public static void execute(String sql) {
        try (Statement statement = conn.createStatement();) {
            Class.forName(driver);
            statement.execute(sql);
        } catch (Exception e) {
            System.err.println(sql);
            throw new RuntimeException(e);
        }
    }


    public static void close() {
        try {
            conn.close();
        } catch (Exception e) {
            System.err.println("連接關閉失敗");
            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;
    }

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