SpringBoot（jdk1.8）+（vue）Element-UI导出ACCESS

最新推荐文章于 2023-06-24 12:17:31 发布

Lin____

最新推荐文章于 2023-06-24 12:17:31 发布

阅读量824

点赞数 1

分类专栏：技术文章标签： JAVA Element-UI

本文链接：https://blog.csdn.net/Lin____/article/details/88972707

版权

技术专栏收录该内容

5 篇文章 0 订阅

订阅专栏

参考自：https://www.2cto.com/kf/201807/759940.html
https://www.xuebuyuan.com/zh-hant/3246500.html
https://blog.csdn.net/wurui8/article/details/43236207
UCanAccess官网：http://ucanaccess.sourceforge.net/site.html#examples

Java8 连接Access数据库UCanAccess的操作介绍
Java8 中JDK1.8中不再包含access桥接驱动，因此不再支持jdbcodbc桥接方式。
1.java Access JDBC jar包：Access_JDBC30.jar（正常是收费的）使用导入数据库相应的jar包，进行连接。
2.UCanAccess是一个Microsoft Access的开源JDBC驱动实现。
我用的是方法2。
首先pom引入

net.sf.ucanaccess
ucanaccess
4.0.4

首先新建一个空白的access文件名字随便取，我这边取名为blank.accdb（新版的sccess）/blank.mdb(旧版的)都能用。把文件放在resource文件夹下
新建一个工具类：

package io.renren.modules.recruitStudents.util;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

/**
 * @program: security-enterprise
 * @@return: $
 * @description: 导出Access文件
 * @author: 
 * @create: 2019-03-26 10:24
 **/
public class AccessUtil {
    private Connection connection;
    private Statement statement;
    private String softPath;
    private String softAccessPath;

    // 空白mdb文件路徑. 直接保存在src/cn/iwoo/dataexport/common/下.（这边读取不到，直接放在respurce下面，然后就能直接读取到，我水平还比较差还没研究出路径的问题）

    private final String blankMdbFilePath = "io/renren/modules/recruitStudents/util/";
    // 空白mdb文件名
    private final String blankMdbFileName = "blank.accdb";//(新版的access是accdb结尾)
    //private final String blankMdbFileName = "blank.mdb";
    // 需要保存到的新的mdb文件路徑和名
    private String savedMdbFilePathAndName = defaultSavedMdbFilePath + defaultSavedMdbFileName;
    // 新mdb文件路徑
//    public static final String defaultSavedMdbFilePath = "E:\\WorkSpaceIDEA\\SpringBoot_kfzs\\";//windows本机
    public static final String defaultSavedMdbFilePath = "../file/";//linux服务器(不加..的话会生成在target文件夹下面)
    // 新mdb文件名
    public static final String defaultSavedMdbFileName = "studentInfo.mdb";
    // mdb文件後綴
    public static final String defaultSavedMdbFileExtension = ".mdb";

    //标准的单例模式
    private static AccessUtil instance = new AccessUtil();
    private AccessUtil() {
    }
    public static AccessUtil getInstance() {
        return instance;
    }


    /**
     * <p>Description: 设置新的文件路径和名称</p>
     */
    public void setSavedFilePathAndName(String newFilePathAndName) {
        this.savedMdbFilePathAndName = newFilePathAndName;
    }

    /**
     * <p>Description:删除存在的mdb文件</p>
     */
    public void deleteOldMdbFile() throws Exception {
        File oldTargetFile = new File(savedMdbFilePathAndName);
        if (oldTargetFile.exists()) {
            oldTargetFile.delete();
        }
    }

    /**
     * <p>Description: 将空白mdb文件复制到指定目录</p>
     */
    public void copyBlankMdbFile() throws Exception {
//        System.out.println(blankMdbFilePath + blankMdbFileName);
        InputStream is = this.getClass().getClassLoader().getResourceAsStream("blank.mdb");
        OutputStream out = new FileOutputStream(savedMdbFilePathAndName);
        byte[] buffer = new byte[1024];
        int numRead;
        while ((numRead = is.read(buffer)) != -1) {
            out.write(buffer, 0, numRead);
        }
        is.close();
        out.close();
    }
    public void connetAccessDB(String path, String fileName) throws Exception {
        String savedMdbFilePathAndName = path + fileName;
        Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); /* often not required for Java 6 and later (JDBC 4.x) */
        String database = "jdbc:ucanaccess://"+savedMdbFilePathAndName.trim()+";memory=true";
        connection = DriverManager.getConnection(database, "", "");
        statement = connection.createStatement();
    }
    /**
     * <p>Description: 打开mdb文件的连接</p>
     */
    public Connection connetAccessDB() throws Exception {
         Class.forName("net.ucanaccess.jdbc.UcanaccessDriver"); /* often not required for Java 6 and later (JDBC 4.x) */
        String database = "jdbc:ucanaccess://"+savedMdbFilePathAndName.trim()+";memory=true";
        connection=DriverManager.getConnection(database,"","");
        statement = connection.createStatement();
        return connection;
    }

    /**
     * <p>Description: 执行特定sql语句</p>
     */
    public void executeSql(String sql) throws Exception {
        statement.execute(sql);
    }

    /**
     * <p>Description: 关闭连接</p>
     */
    public void closeConnection() throws Exception {
        statement.close();
        connection.close();
    }
//测试用
    public static void main(String[] args) {
        AccessUtil au = AccessUtil.getInstance();
        String sql = "create table Archives_tbl (LLR MEMO,LLRQ MEMO,QZH MEMO,MLH MEMO,AJH MEMO,DH MEMO,BGJH MEMO,FLJH MEMO,MJ MEMO,AJBT MEMO,ND MEMO,DW MEMO,JS MEMO,YC MEMO,BGQX MEMO,QSRQ MEMO,JZRQ MEMO,BZ MEMO);";
        String sql1 = "create table Archives_tbl (QZH MEMO,MLH MEMO,AJH MEMO,DH MEMO,BGJH MEMO,FLJH MEMO,MJ MEMO,AJBT MEMO,ND MEMO,DW MEMO,JS MEMO,YS MEMO,BGQX MEMO,QSRQ MEMO,JZRQ MEMO,BZ MEMO);";
        String sql2 = "create table Files_tb1 (SXH MEMO,WH MEMO,TM MEMO,ZRZ MEMO,ZTC MEMO,RQ MEMO,YC MEMO,YS MEMO,MJ MEMO,LDPSR MEMO,GB MEMO,QTZT MEMO,BZ MEMO,DH MEMO,Vpath MEMO);";

        try {
            au.copyBlankMdbFile();
            au.connetAccessDB();
            au.executeSql(sql1);
            au.executeSql(sql2);
            au.closeConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void setSoftPath(String softPath) {
        this.softPath = softPath;
    }

    public void setSoftAccessPath(String softAccessPath) {
        this.softAccessPath = softAccessPath;
    }
//这个就是从服务器上下载文件
    public String downloadFile(HttpServletResponse response){

        String downloadFilePath = defaultSavedMdbFilePath;//被下载的文件在服务器中的路径,
        String fileName = defaultSavedMdbFileName;//被下载文件的名称

        File file = new File(downloadFilePath+fileName);
        System.out.println(file.length());
        if (file.exists()) {
//            response.setContentType("application/octet-stream");// 设置强制下载不打开
//            response.setHeader("Location",fileName);
//            response.reset();
            response.addHeader("Content-Disposition", "attachment;fileName=" + fileName);
            response.setContentType("application/octet-stream; charset=UTF-8");
            response.setHeader("Content_Length",file.length()+"");
            byte[] buffer = new byte[1024];
            FileInputStream fis = null;
            BufferedInputStream bis = null;
            try {

                fis = new FileInputStream(file);
                bis = new BufferedInputStream(fis);
                OutputStream outputStream = response.getOutputStream();
//                IOUtils.write(buffer, outputStream);
                int i = bis.read(buffer);
                while (i != -1) {
                  outputStream.write(buffer, 0, i);
                  i = bis.read(buffer);
                }
                return "下载成功";
              } catch (Exception e) {
                e.printStackTrace();
              } finally {
                try{
                    response.getOutputStream().flush();
                    response.getOutputStream().close();
                    fis.close();
                    bis.close();
                }catch(Exception e){
                    e.printStackTrace();
                }

              }
            }
          return "下载失败";
          }

}

下面是调用的方法：

@GetMapping("exportAccess")
    @ApiOperation("导出Access数据文件")
    @LogOperation("导出Access数据文件")
    @RequiresPermissions("recruitStudents:recruitsign:save")
    public Result exportAccess(@ApiIgnore @RequestParam  Map<String, Object> params,HttpServletResponse response){
        System.out.println("导出Access数据文件");
        PreparedStatement sql;
        List<RecruitSignEntity> list = recruitSignService.getList(params);
        AccessUtil au = AccessUtil.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        //这边也能直接执行sql语句
         //含特殊符号的字段整个都要用[]括起来
//        String sql1 = "create table Recruit_Student(编号 counter primary key,姓名 VarChar,性别 VarChar);";

        try {
            au.copyBlankMdbFile();
            Connection connection = au.connetAccessDB();
            sql = connection.prepareStatement("insert into Recruit_Student values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            for(int i=0;i<list.size();i++){
                RecruitSignEntity r = list.get(i);
                sql.setInt(1,i+1);
                sql.setString(2,r.getStudentName());
                sql.setString(3,r.getSex()==null?"":(r.getSex()==0?"女":"男"));
                sql.setString(4,r.getPoliticalName());
                sql.setString(5,r.getIdCardTypeName());
                sql.setString(6,r.getIdentification());
                sql.setString(7,r.getBirthDate()==null?"":sdf.format(r.getBirthDate()).toString());
                sql.setString(8,r.getNationName());
                sql.setString(9,r.getOnthejobStatusName());
                sql.setString(10,r.getDistributionName());
                sql.setString(11,r.getMaritalStatusName());
                sql.setString(12,r.getTuitionFeeSourcesName());
                sql.setString(13,r.getAccountCharacterName());
                sql.setString(14,r.getNativePlaceName());
                sql.setString(15,r.getNativePlaceDetails());
                sql.setString(16,r.getLocation());
                sql.setString(17,r.getPhone());
                sql.setString(18,r.getFixedTelephone());
                sql.setString(19,r.getEmail());
                sql.setString(20,r.getMailingAddress());
                sql.setString(21,r.getZipCode());
                sql.setString(22,r.getHierarchyName());
                sql.setString(23,r.getMajorName());
                sql.setString(24,r.getIsTvUniversityGraduate()==null?"":(r.getIsTvUniversityGraduate()==0?"是":"否"));
                sql.setString(25,r.getEducationName());
                sql.setString(26,r.getFormerGraduationSchool());
                sql.setString(27,r.getGraduateDate()==null?"":sdf.format(r.getGraduateDate()).toString());
                sql.setString(28,r.getOriginalDisciplineName());
                sql.setString(29,r.getPrimaryDisciplinesName());
                sql.setString(30,r.getLearningTypeName());
                sql.setString(31,r.getMajorStudied());
                sql.setString(32,r.getCertificateCoding());
                sql.setString(33,r.getProofMaterialName());
                sql.setString(34,r.getCertificateNumber());
                sql.setString(35,r.getNameOfEducation());
                sql.setString(36,r.getDocumentTypeName());
                sql.setString(37,r.getOriginalDocumentNumber());
                sql.setString(38,"");
                sql.setString(39,"");
                sql.setString(40,r.getIsPovertyAlleviationTarget()==null?"":(r.getIsPovertyAlleviationTarget()==0?"是":"否"));
                sql.setString(41,"");
                sql.setString(42,"");
                sql.setString(43,"");
                sql.setString(44,"");
                sql.setString(45,"");
                sql.setString(46,"");
                sql.setString(47,"");
                sql.setString(48,r.getItemsUnderName());
                sql.executeUpdate();
            }
//            au.executeSql(sql1);
            au.closeConnection();
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println(au.downloadFile(response));
        return new Result<>();
    }

后台代码到这完成了。我一直调用这个方法但是不成功，按照正常前台点击导出，就会下载文件了，但是我这边用的是element-ui前台的请求也是有讲究的才能正常下载文件请看前台方法代码：

// 导出access文件
    exportAccess () {
      var params = qs.stringify({
        'token': Cookies.get('token'),
        ...this.dataForm
      })
      window.location.href = `${window.SITE_CONFIG['apiURL']}/recruitStudents/recruitsign/exportAccess?${params}`
      //下面注释掉的能执行方法但是没办下载文件到本地
      //   this.$http['post'](`/recruitStudents/recruitsign/exportAccess`, this.dataForm).then(({ data: res })=> {
      //     if (res.code !== 0) {
      //       return this.$message.error(res.msg)
      //     }
      //   }).catch(() => { })
    },

如果还不行也有可能是access问题引起的，请看https://blog.csdn.net/qq_37616870/article/details/79057075链接

在这边再次感谢以上链接的作者。

Lin____

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
3
评论
SpringBoot（jdk1.8）+（vue）Element-UI导出ACCESS

参考自：https://www.2cto.com/kf/201807/759940.htmlhttps://www.xuebuyuan.com/zh-hant/3246500.htmlhttps://blog.csdn.net/wurui8/article/details/43236207UCanAccess官网：http://ucanaccess.sourceforge.net/site....
复制链接

扫一扫