将数据库中数据下载到csv格式文件中

1、html：

<button id="download" class="btn btn-success" style="width:150px">批量导出</button>

2、js：

$(document).ready(function () {
    $("#download").click(function () {//批量导出
        var keyword = $("#brand_key").val();
        keyword = $.trim(keyword);
        var url = "/ps-admin-nimitz/knowledge-mining/downloadBrandDict.do";
        if (keyword != "") {
            keyword = encodeURIComponent(keyword);
            url += "?keyword=" + keyword;
        }
        window.location.href = url;
    });
});

3、后台controller

// 批量导出
    @RequestMapping("/knowledge-mining/downloadBrandDict.do")
    public void downloadBrandDict(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap, @RequestParam(required = false) String keyword) {
    String userId = request.getRemoteUser();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String filename = sdf.format(new Date());
        response.setCharacterEncoding("GBK");
        response.addHeader("Content-Disposition", "attachment;filename=" + filename + ".csv");
        String newKeyword = keyword == null ? "" : keyword.trim();
        int maxCount = brandDictService.getTotalCount(newKeyword.toLowerCase());
        String message = null;
        //-------------------------------------------------------------------------
        List<BrandDictBean> list;
        try {
            BufferedWriter wr = new BufferedWriter(response.getWriter());
            wr.write("品牌词\r\n");
            int segment = 5000;
            for (int start = 1; start < maxCount; start = start + segment) {
                list = brandDictService.getQueryResult(newKeyword.toLowerCase(), start, segment);
                for (BrandDictBean aList : list) {
                    String brand1 = aList.getBrand().trim();
                    wr.write(brand1 + "\r\n");
                    wr.flush();
                }
            }
            try {
                wr.close();
            } catch (IOException e) {
                log.error("关闭异常" + e.toString());
            }
            message = "导出成功";
        } catch (Exception e) {
            message = "导出失败！！请重试！";
        }
        if(message.equalsIgnoreCase("导出成功")){
String behavior = "导出品牌词数据";
log.info("用户"+userId+behavior);
showLogService.addLog(userId, "品牌词管理", behavior);
}
        modelMap.put("message", message);


    }

4、后台service：

@Service
public class BrandDictService {
@Autowired
    private BrandDictDao brandDictDao;
// 返回查询结果个数
    public int getTotalCount(String newKeyword) {
        return brandDictDao.getTotalCount(newKeyword);
    }
    // 查询
    public List<BrandDictBean> getQueryResult(String newKeyword, int start, int segment) {
        return brandDictDao.getQueryResult(newKeyword, start, segment);
    }
    //导入子母品牌数据
    public int addBatch(List<BrandDictBean> data){
    return brandDictDao.addBatch(data);
    }
}

5、后台dao：

@Repository
public class BrandDictDao {
private int seg = 10;
@Autowired
    private JdbcTemplate jdbcTemplate;
// 返回查询结果个数
    public int getTotalCount(String newKeyword) {
        String total_Sql = "select count(*) from ps_brand_dict ";
        if (newKeyword != null && !newKeyword.equals("")) {
                    total_Sql = total_Sql + " WHERE LOWER(brand) like CONCAT('%',?,'%')";
                    return jdbcTemplate.queryForInt(total_Sql, new Object[]{newKeyword});
        } 
        return jdbcTemplate.queryForInt(total_Sql);
    }
    public List<BrandDictBean> getQueryResult(String newKeyword, int start, int segment) {
    String selectSql = "SELECT BRAND,USER,TIME,(CASE WHEN SOURCE = '' THEN 'HIVE' else SOURCE end)SOURCE FROM ps_brand_dict";
        int seg1 = seg;
        if (segment != -1) {//批量导出专用逻辑，一次导出segment条。
            seg1 = segment;
        }
        if (newKeyword != null && !newKeyword.equals("")) {
        selectSql = selectSql
                    + " WHERE LOWER(BRAND) like CONCAT('%',?,'%') ORDER BY length(BRAND)  LIMIT ?,?";
            return jdbcTemplate.query(selectSql, new Object[]{newKeyword, start - 1, seg1}, new RowMapper<BrandDictBean>() {
                public BrandDictBean mapRow(ResultSet rs, int rowNum) throws SQLException {
                BrandDictBean brandDictBean = new BrandDictBean();
                brandDictBean.setBrand(rs.getString("BRAND"));
                brandDictBean.setUser(rs.getString("USER"));
                brandDictBean.setTime(rs.getString("TIME"));
                brandDictBean.setSource(rs.getString("SOURCE"));
                    return brandDictBean;
                }
            });
        }
        selectSql = selectSql+" ORDER BY length(BRAND)  LIMIT ?,?";
        return jdbcTemplate.query(selectSql, new Object[]{start - 1, seg1}, new RowMapper<BrandDictBean>() {
            public BrandDictBean mapRow(ResultSet rs, int rowNum) throws SQLException {
            BrandDictBean brandDictBean = new BrandDictBean();
            brandDictBean.setBrand(rs.getString("BRAND"));
            brandDictBean.setUser(rs.getString("USER"));
            brandDictBean.setTime(rs.getString("TIME"));
            brandDictBean.setSource(rs.getString("SOURCE"));
                return brandDictBean;
            }
        });
    }
    
    public int addBatch(final List<BrandDictBean> data){
    String sql = "INSERT INTO ps_brand_dict(brand,user,time,source) VALUES(?,?,?,?) ON DUPLICATE KEY UPDATE user=?,time=?,source=?";
    int[] res = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){


@Override
public void setValues(PreparedStatement ps, int i)
throws SQLException {
ps.setString(1, data.get(i).getBrand());
ps.setString(2, data.get(i).getUser());
ps.setString(3, data.get(i).getTime());
ps.setString(4, data.get(i).getSource());
ps.setString(5, data.get(i).getUser());//更新的数据
ps.setString(6, data.get(i).getTime());//更新的数据
ps.setString(7, data.get(i).getSource());//更新的数据
}


@Override
public int getBatchSize() {
return data.size();
}
    
    });
    return res[0];
    }
}

6、后台bean：

package com.suning.web.bean;


public class BrandDictBean {
private String brand;
private String user;
private String time;
private String source;

public String getBrand() {
        return brand;
    }


    public void setBrand(String brand) {
        this.brand = brand;
    }
    
    public String getUser() {
        return user;
    }


    public void setUser(String user) {
        this.user = user;
    }
    
    public String getTime() {
        return time;
    }


    public void setTime(String time) {
        this.time = time;
    }
    
    public String getSource() {
        return source;
    }


    public void setSource(String source) {
        this.source = source;
    }
}