将数据库中数据下载到csv格式文件中
来源:互联网 发布:vue.js a标签跳转 编辑:程序博客网 时间:2024/06/08 13:35
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;
}
}
- 将数据库中数据下载到csv格式文件中
- 将数据库中数据下载到csv格式文件中
- 将数据从DataGridView中导出成CSV格式文件
- 将Csv文件数据导入到数据库表中
- 如何将数据库的数据导入到csv文件中
- c#将DataTable中数据写入到CSV文件中
- 解析.CSV文件中的数据并将其插入到数据库中代码
- Bulk Insert:将文本数据(csv和txt)导入到数据库中
- oracle中如何将csv格式文件导入table
- 将文本格式csv文件导入到sqlserver数据库中
- 将csv数据文件导入到sqlite数据库中
- 使用python将csv数据导入到sqlite中
- C++ 将数据导入到CSV,txt文档中
- java将数据写入到csv文件中
- sql中导入*.csv格式文件
- 将CSV格式文件导入SQLSERVER数据库
- 把数据库文件写入csv文件和从csv文件中导入数据到数据库中
- 将Excel数据导入到数据库中
- D13
- 机器学习笔记(XIII)决策树(III)连续与缺失值
- 一键解决您scrapy安装不上的问题,包括lxml的难题,非常好使
- 面试过阿里等互联网大公司,我知道了这些套路
- consistent hashing(一致性hash)
- 将数据库中数据下载到csv格式文件中
- Python Scrapy(1)-新建scrapy工程,爬取豆瓣读书
- 电商工作后台首页的商业价值重构与产品化设计
- java 并发之 CyclicBarrier
- FileOutputStream
- 谈谈对协程的理解
- Java单例模式
- Keil4 品字菜单使用
- JavaWeb学习笔记-Hibernate-02-开发流程