导出 txt sql 脚本

最新推荐文章于 2021-01-22 11:34:03 发布

原创最新推荐文章于 2021-01-22 11:34:03 发布 · 493 阅读

0 ·

CC 4.0 BY-SA版权

java 专栏收录该内容

17 篇文章

订阅专栏

本文介绍了一种数据字典导出功能的实现方法，通过拼接SQL语句进行数据的导出，并详细展示了从后端代码到前端请求的全过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近做一个数据字典导出功能，来给博客添砖加瓦

SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//拼接sql
StringBuffer strb = new StringBuffer();
//要导出的对象
List<DictionaryItemInfo> list = dictionaryItemMapper.outputDictItem(dictionaryItem);
//插入前先删除
strb.append("delete from t_dict_item  where 1=1 ");
	if(StringUtil.isNotBlank(dictionaryItem.getCode())){
		strb.append("and code like '%"+dictionaryItem.getCode()+"%'");
	}
	if(StringUtil.isNotBlank(dictionaryItem.getName())){
		strb.append("and name like '%"+dictionaryItem.getName()+"%'");
	}
	if(StringUtil.isNotBlank(dictionaryItem.getDescript())){
		strb.append("and descript like '%"+dictionaryItem.getDescript()+"%'");
	}
strb.append("and dict_id=(select cid from t_dict where code='"+dictCode+"');\r\n");	
for (int j = 0; j < list.size(); j++) {strb.append("insert into t_dict_item(" + "code,"  + "descript," + "disabled,"+"name,"+"pid,"+"sort_index,"+ "dict_id"+ ")values\r\n(");
			if (StringUtil.isBlank(list.get(j).getCode())) {
				strb.append("NULL,");
			} else {
				strb.append("'" + list.get(j).getCode() + "',");
			}
			if (StringUtil.isBlank(list.get(j).getDescript())) {
				strb.append("NULL,");
			} else {
				strb.append("'" + list.get(j).getDescript() + "',");
			}
			if (StringUtil.isBlank(list.get(j).getDisabled2())) {
				strb.append("NULL,");
			} else {
				strb.append("'" + list.get(j).getDisabled2() + "',");
			}
			if (StringUtil.isBlank(list.get(j).getName())) {
				strb.append("NULL,");
			} else {
				strb.append("'" +list.get(j).getName()+"',");
			}
			if (StringUtil.isBlank(list.get(j).getPid())) {
				strb.append("NULL,");
			} else {
				strb.append("'" + list.get(j).getPid() + "',");
			}
			if (StringUtil.isBlank(list.get(j).getSortIndex())) {
				strb.append("NULL,");
			} else {
				strb.append("'" + list.get(j).getSortIndex() + "',");
			}
			if (StringUtil.isBlank(list.get(j).getDictId())) {
				strb.append("NULL);\r\n");
			} else {
				strb.append("(select cid from t_dict where code='"+dictCode+"'));\r\n");
			}
		}

可以看到上面这部分主要是拼接sql

try {
	Date date = new Date();
	response.setHeader("Content-Disposition",
	"attachment; filename=" +format.format(date)+"_"+ dictCode+"_"+list.size() + 
	java.net.URLEncoder.encode("." + "txt", "UTF-8"));
} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
}
	output(strb,response);

这里主要是设置导出文件名，也是在上面的方法里面，要注意的是设置文件名的编码格式

	//输出流
	public void output(StringBuffer strb,HttpServletResponse response){
		response.setContentType("application/octet-stream");
		response.setHeader("Expires", "0");
		response.setHeader("Cache-Control", "must-revalidate, post-check=0, precheck=0");
		response.setHeader("Pragma", "public");
		ByteArrayInputStream in = null;
		if (strb.toString() != null && !strb.toString().trim().equals("")) {
			try {
				in = new ByteArrayInputStream(strb.toString().getBytes());
			} catch (Exception ex) {
				ex.printStackTrace();
			}
		}
		OutputStream out = null;
		BufferedInputStream buff = null;
		try {
			out = response.getOutputStream();
			// 把文件读到IO流中
			buff = new BufferedInputStream(in);
			// 相当于我们的缓存
			byte[] b = new byte[1024];
			// 该值用于计算当前实际下载了多少字节
			int byteread = -1;
			// 开始循环下载
			while ((byteread = buff.read(b)) != -1) {
				// 将b中的数据写到客户端的内存
				out.write(b, 0, byteread);
			}
			// 将写入到客户端的内存的数据,刷新到磁盘
			out.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
				try {
					if (out != null)
						out.close();
					if (buff != null)
						buff.close();
					if (in != null)
						in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			
		}
	}

这个方法就是把拼好的sql 写入到磁盘里了（也就是下载）

再看看前端的请求

//导出
function output(){
	var id = $("#dictId").val();
	var code=$("#search_LIKE_code").val();
	var name=$("#search_LIKE_name").val();
	var descript=$("#search_LIKE_descript").val();
	var dictCode=$("#dictCode").val();
	if(id==null||id.trim()==""){
		$.msgbox({time: 2000,msg: "请选择你要查的字典项!",icon:"error"});				
	}else{
	    $.confirm("确认导出？",function(){
	       location.href="${pageContext.request.contextPath}/admin/dict/output?    
           id="+id+"&code="+code+"&name="+name+"&descript="+descript+"&dictCode="+dictCode;
	   	});				
	}
};

一个简单的get请求