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;
}
}