Java如何查看数据库文件大小（oracle,mysql,sqlserver）

怼烎@ 2024-04-17 05:51 75阅读 0赞

如果是oracle数据库需要确保当前用户拥有DBA权限

import java.io.File;
    import java.io.FileReader;
    import java.sql.*;
    import java.text.DecimalFormat;
    import java.util.Properties;
    
    /**
     * 数据库文件增长情况
     *
     * @author yanzy
     * @date 2019/1/21 14:28
     */
    public class DataBaseFileSize {
    
        public static void main(String[] args) {
            try {
                System.out.println(new DataBaseFileSize().getOracleFileSize());
            } catch (SQLException e) {
                System.out.println("当前用户没有DBA权限");
            }
        }
    
        /**
         * 获取数据库信息
         *
         * @param key
         * @return java.lang.String
         * @author yanzy
         * @version 1.0
         * @date 2019/1/22 15:04
         */
        public static String getConfig(String key) {
            try {
                Properties properties = new Properties();
                properties.load(new FileReader(Properties.class.getResource("/").getPath() + "db.properties"));
                return (properties.getProperty(key) == null) ? "未找到相应的值" : properties.getProperty(key);
            } catch (Throwable e) {
                return "未找到相应的值";
            }
        }
    
        /**
         * 获取数据库连接
         *
         * @return java.sql.Connection
         * @author yanzy
         * @version 1.0
         * @date 2019/1/22 15:11
         */
        public static Connection getcon() {
            Connection connection = null;
            try {
                Class.forName(getConfig("jdbc.driver"));
                connection = DriverManager.getConnection(getConfig("jdbc.url"), getConfig("jdbc.username"), getConfig("jdbc.password"));
            } catch (Exception e) {
                e.printStackTrace();
            }
            return connection;
        }
    
        /**
         * 将Long字节转换成文件大小
         *
         * @param fileSize
         * @return java.lang.String
         * @author yanzy
         * @version 1.0
         * @date 2019/1/21 14:51
         */
        public static String getFileType(long fileSize) {
            String size = "";
            // 保留小数后两位
            DecimalFormat decimalFormat = new DecimalFormat("#.00");
            if (fileSize < 1024) {
                size = decimalFormat.format((double) fileSize) + "BT";
            } else if (fileSize < 1048576) {
                size = decimalFormat.format((double) fileSize / 1024) + "KB";
            } else if (fileSize < 1073741824) {
                size = decimalFormat.format((double) fileSize / 1048576) + "MB";
            } else {
                size = decimalFormat.format((double) fileSize / 1073741824) + "GB";
            }
            return size;
        }
    
        /**
         * 将文件大小转成Long字节
         *
         * @param value
         * @return long
         * @author yanzy
         * @version 1.0
         * @date 2019/1/22 16:14
         */
        public static long getTypeSize(String value) {
            long longValue = 0;
            if (value.contains("KB")) {
                String kbValue = value.replace("KB", "").trim();
                double doubleValue = Double.parseDouble(kbValue) * 1024;
                longValue = (long) doubleValue;
            } else if (value.contains("MB")) {
                String kbValue = value.replace("MB", "").trim();
                double doubleValue = Double.parseDouble(kbValue) * 1048576;
                longValue = (long) doubleValue;
            } else if (value.contains("GB")) {
                String kbValue = value.replace("GB", "").trim();
                double doubleValue = Double.parseDouble(kbValue) * 1073741824;
                longValue = (long) doubleValue;
            }
            return longValue;
        }
    
        /**
         * 获取Oracle,SqlServer,Mysql数据库文件大小
         *
         * @return java.lang.String
         * @author yanzy
         * @version 1.0
         * @date 2019/1/22 15:26
         */
        public String getOracleFileSize() throws SQLException {
            // 获取Connection连接
            Connection connection = getcon();
            // 获取Statement
            Statement statement = connection.createStatement();
            String sql = "";
            long fileSize = 0;
    
            // Oracle数据库
            if (getConfig("jdbc.driver").contains("oracle")) {
                sql = "select sum(bytes) as database_size from dba_data_files";
    
                // 执行sql
                ResultSet resultSet = statement.executeQuery(sql);
                // 处理ResultSet
                while (resultSet.next()) {
                    fileSize = resultSet.getLong("database_size");
                }
            }
    
            // Sqlserver数据库
            else if (getConfig("jdbc.driver").contains("sqlserver")) {
                sql = "exec sp_spaceused";
                // 执行sql
                ResultSet resultSet = statement.executeQuery(sql);
                // 处理ResultSet
                while (resultSet.next()) {
                    String value = resultSet.getString("database_size");
                    fileSize = getTypeSize(value);
                }
            }
    
            // Mysql数据库
            else if (getConfig("jdbc.driver").contains("mysql")) {
                sql = "select sum(data_length) as database_size from tables";
                // 执行sql
                ResultSet resultSet = statement.executeQuery(sql);
                // 处理ResultSet
                while (resultSet.next()) {
                    fileSize = resultSet.getLong("database_size");
                }
            }
            return getFileType(fileSize);
        }
    
    
        /**
         * 获取文件大小
         *
         * @param filePath
         * @return java.lang.String
         * @author yanzy
         * @version 1.0
         * @date 2019/1/21 14:42
         */
        public String getFileSize(String filePath) {
            String size = "";
            File file = new File(filePath);
            // 判断文件是否存在
            if (file.exists()) {
    
                // 如果是文件直接返回文件大小
                if (file.isFile()) {
                    long fileSize = file.length();
                    size = getFileType(fileSize);
                }
                // 如果是文件夹则递归计算其内容的总大小
                else if (file.isDirectory()) {
                    long dirSize = getDirSize(file);
                    size = getFileType(dirSize);
                }
            } else {
                size = "文件或者文件夹不存在，请检查路径是否正确！";
            }
            return size;
        }
    
        /**
         * 递归获取文件夹的总大小
         *
         * @param file
         * @return long
         * @author yanzy
         * @version 1.0
         * @date 2019/1/22 10:44
         */
        public long getDirSize(File file) {
            if (file.isDirectory()) {
                File[] children = file.listFiles();
                long size = 0;
                for (File files : children)
                    size += getDirSize(files);
                return size;
            } else {
                return file.length();
            }
        }
    }