excel备份数据库

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KszuzFLD-1651796839409)(assets/image-20220427232649361.png)]
在这里插入图片描述

步骤
- 步骤1：添加坐标
- 步骤2：编写封装类
- 步骤3：编写核心类

步骤1：添加坐标

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
    </dependencies>

步骤2：编写封装类

package com.czxy.zx.demo03;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

import java.util.Date;
import java.util.List;

/**
 * 课程
 *
 */
@Data
@ContentRowHeight(20)
@HeadRowHeight(20)  //行高
@ColumnWidth(25)    //列宽
public class Chapter {

    @ExcelProperty("章节ID")
    private String id;

    @ExcelProperty("课程ID")
    private String courseId;

    @ExcelProperty("章节名称")
    private String title;

    @ExcelProperty("显示排序")
    private Integer sort;

    @ExcelProperty("创建时间")
    private Date gmtCreate;

    @ExcelProperty("更新时间")
    private Date gmtModified;

}

package com.czxy.zx.demo03;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;

import java.util.Date;

/**
 * 课程
 *
 */
@Data
@ContentRowHeight(20)
@HeadRowHeight(20)  //行高
@ColumnWidth(25)    //列宽
public class Course {

    @ExcelProperty("课程ID")
    private String id;

    @ExcelProperty("课程讲师ID")
    private String teacherId;

    @ExcelProperty("课程专业ID二级分类ID")
    private String subjectId;

    @ExcelProperty("一级分类ID")
    private String subjectParentId;

    @ExcelProperty("课程标题")
    private String title;

    @ExcelProperty("课程销售价格，设置为0则可免费观看")
    private Double price;

    @ExcelProperty("总课时")
    private Integer lessonNum;

    @ExcelProperty("课程封面图片路径")
    private String cover;

    @ExcelProperty("销售数量")
    private Long buyCount;

    @ExcelProperty("浏览数量")
    private Long viewCount;

    @ExcelProperty("乐观锁")
    private Long version;

    @ExcelProperty("视频状态 Draft未发布  Normal已发布")
    private String status;

    @ExcelProperty("创建时间")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date gmtCreate;

    @ExcelProperty("更新时间")
    @DateTimeFormat("yyyy年MM月dd日")
    private Date gmtModified;


}

步骤3：编写核心类

package com.czxy.zx.demo03;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.czxy.zx.demo02.Student2;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

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

/**
 * @author 桐叔
 * @email liangtong@itcast.cn
 */
public class TestBackdb {

    public Class getClassByTableName(String tableName) {
        Map<String,Class> map = new HashMap<>();
        map.put("edu_chapter", Chapter.class);
        map.put("edu_course", Course.class);
        return map.get(tableName);
    }

    public String getPath() {
        return this.getClass().getResource("/").getPath();
    }


    public Connection getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/zx_edu_course?useUnicode=true&characterEncoding=utf8";
            String username = "root";
            String password = "1234";

            Properties props =new Properties();
            props.setProperty("user", username);
            props.setProperty("password", password);
            props.setProperty("remarks", "true");               //设置可以获取remarks信息
            props.setProperty("useInformationSchema", "true");  //设置可以获取tables remarks信息

            return DriverManager.getConnection(url, props);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Test
    public void testDB() throws Exception {
        String file = getPath() + "db.xls";
        QueryRunner queryRunner = new QueryRunner();
        ExcelWriter excelWriter = EasyExcel.write(file).build();
        String dbName = "zx_edu_course";
        //获得连接
        Connection conn = getConnection();
        //语句执行者
        Statement st = conn.createStatement();

        //数据库的元数据
        DatabaseMetaData databaseMetaData = conn.getMetaData();

        //获得所有的数据库
        ResultSet catalogResultSet = databaseMetaData.getCatalogs();

        //遍历所有的数据库
        while(catalogResultSet.next()) {
            //获得数据库的名称
            String databaseName = catalogResultSet.getString(1);
            if(dbName.equals(databaseName)) {
                //使用数据库
                st.execute("use " + databaseName);
                ResultSet tableResultSet = databaseMetaData.getTables(databaseName, null, null, null);
                //遍历所有的表名
                while(tableResultSet.next()) {
                    //表名
                    String tableName = tableResultSet.getString(3);             //TABLE_NAME
                    String tableRemarks = tableResultSet.getString("REMARKS"); //获得表的备注
                    // 通过表名获得excel处理类
                    Class excelBeanClass = getClassByTableName(tableName);
                    if(excelBeanClass != null) {
                        //获得当前表的所有数据
                        String sql = "select * from " + tableName;
                        //
                        List data = (List) queryRunner.query(conn, sql, new BeanListHandler<>(excelBeanClass, new BasicRowProcessor(new GenerousBeanProcessor()) ));
                        // 创建sheet
                        WriteSheet writeSheet = EasyExcel.writerSheet(tableRemarks != null ? tableRemarks : tableName).head(excelBeanClass).build();
                        excelWriter.write(data, writeSheet);
                    }
                }
            }
        }
        //写入完成
        excelWriter.finish();
    }

}