1.sql中用 in 关键字大批量增删改数据时,需分段提交
controller代码:
if(qygh != null && qygh.size() > 0){
int numxj = 0;
int numb = qygh.size() / 1000; //求整,可以提交多少个1000条
int syb = qygh.size() % 1000; //取余
for(int i = 0; i < numb * 1000; i++){
if(i%1000 == 0){
numxj = distributionService.updateSd(qygh.subList(i, i+1000),fpdm);
if(numxj != 1000){
// 事务回滚
TransactionAspectSupport.currentTransactionStatus()
.setRollbackOnly();
logger.error("失败");
rst = "false||失败";
rst = gson.toJson(rst);
return rst;
}
}
}
numxj = distributionService.updateSd(qygh.subList(numb * 1000,qygh.size()),fpdm);
if(numxj != syb){
// 事务回滚
TransactionAspectSupport.currentTransactionStatus()
.setRollbackOnly();
logger.error("失败");
rst = "false||失败";
rst = gson.toJson(rst);
return rst;
}
}
mybatis代码:
<update id="updateSd" parameterType="map">
update gh_qyghgx set sfsd = '1',sdfpdm = #{fpdm,jdbcType=VARCHAR}
where qybm in
<foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
#{item.qybm,jdbcType=VARCHAR}
</foreach>
</update>
2.使用poi 导出excel
import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import com.java.entity.dto.ZeroIn;
public class CreateExcel {
public static void createExcelZeroIn(List<ZeroIn> list,String sheetName,
HttpServletResponse res) throws IOException{
// 1.创建一个workbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 2.在workbook中添加一个sheet,对应Excel中的一个sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 3.在sheet中添加表头第0行,老版本poi对excel行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 4.创建单元格,设置值表头,设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
/** 设置表头*/
setTableZeroIn(row,style);
writeExcelZeroIn(list,sheet,row);
tkDownload(sheetName,wb,res);
}
public static void setTableZeroIn(HSSFRow row,HSSFCellStyle style){
HSSFCell cell = row.createCell(0);
cell.setCellValue("日期");
cell.setCellStyle(style);
cell = row.createCell(1);
cell.setCellValue("操作");
cell.setCellStyle(style);
cell = row.createCell(2);
cell.setCellValue("备注");
cell.setCellStyle(style);
}
public static void writeExcelZeroIn(List<ZeroIn> lists,HSSFSheet sheet,HSSFRow row) throws IOException{
// 循环将数据写入Excel
for(int i = 0; i < lists.size(); i++){
row = sheet.createRow((int) i + 1);
ZeroIn list = lists.get(i);
// 创建单元格,设置值
row.createCell(0).setCellValue(list.getRkrq());
row.createCell(1).setCellValue(list.getRkje());
row.createCell(2).setCellValue(list.getBz());
}
}
public static void tkDownload(String sheetName,HSSFWorkbook wb,HttpServletResponse res) throws IOException{
String fileName = sheetName;
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
byte[] content = os.toByteArray();
InputStream is = new ByteArrayInputStream(content);
// 设置response参数,可以打开下载页面
res.reset();
res.setContentType("application/vnd.ms-excel;charset=utf-8");
res.addHeader(
"Content-Disposition",
"attachment;filename="
+ URLEncoder.encode(fileName+".xls", "UTF-8"));// 设置文件名
ServletOutputStream out = res.getOutputStream();
BufferedInputStream bis = null;
BufferedOutputStream bos = null;
try {
bis = new BufferedInputStream(is);
bos = new BufferedOutputStream(out);
byte[] buff = new byte[2048];
int bytesRead;
// Simple read/write loop.
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos.write(buff, 0, bytesRead);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
if (bis != null)
bis.close();
if (bos != null)
bos.close();
}
}
}
3.ajax 跨域问题解决
在controller上加上:
(临时测试用)
response.addHeader("Access-Control-Allow-Origin","*");
response.addHeader("Access-Control-Allow-Methods","*");
response.addHeader("Access-Control-Max-Age","100");
response.addHeader("Access-Control-Allow-Headers", "Content-Type");
response.addHeader("Access-Control-Allow-Credentials","false");