导出功能:
1.dao层的mapper(写方法名)
2.xml里用sql语句实现mapper中的方法
注意：
映射时对比数据库段名和bean变量名
映射的property和sql语句需要查询的所有变量名称必须都一一对应，
需要获取Bean对象时需要用到映射，因此在<>里写resultMap，获取list则不需要
3.编写工具类（写方法）
注意：
工具类中需要Bean中有对象和getter setter方法，这样才能才能正确获取对象，即get()方法正确
4.前端jsp添加按钮+function
注意：function中命名的servlet名称要和需要的servlet对应
5.实现servlet
注意：要用获取对应的的Mapper.class和Mapper.class对应的get方法
session = MyBatisUtil.createSqlsession();
deliveryList = session.getMapper(DeliveryManageMapper.class).getExpDeliverySearch(deliveryVo);// 获取数据
6.配置mybatis.xml
添加查询条件导出时，需要xml里添加where if判断，然后在servlet获取数据，通过前端jsp的url地址传值
private DeliveryVo getVo(HttpServletRequest request) {
DeliveryVo vo = new DeliveryVo();
vo.setRoleId(Integer.parseInt(
request.getParameter("roleId").equals("") ? "0" : request.getParameter("roleId")));
vo.setDeliveryBegin(request.getParameter("deliveryBegin"));
vo.setDeliveryEnd(request.getParameter("deliveryEnd"));
return vo;
}有多少个参数就&多少个'&deliveryEnd='+$('#deliveryEnd').val()
//hr 导出功能
function expDelivery(){
$(location).attr(
'href',
'${pageContext.request.contextPath}/DeliverySearchExpServlet?roleId='
+$('#roleId').val()+'&deliveryEnd='
+$('#deliveryEnd').val()+'&deliveryBegin='
+$('#deliveryBegin').val()
);
$('#table').datagrid('reload');
}
//hr 入库 审批查询数据导出
/* 根据条件获取入库审批表记录(已审批) */
public ArrayList<StorageBean> getExpStorageSearch(@Param("storage") StorageVo storage);
/* 根据条件获取入库审批表记录总数(已审批) */
public long getExpStorageSearchCount(@Param("storage") StorageVo storage);
<!-- 根据条件查询入库审批记录 -->
<select id="getExpStorageSearch" resultType="StorageBean" resultMap="storageResult">
SELECT
s.batch_id,
s.product_id,
p.product_name product_name,
u.user_name
approver_name,
u1.user_name delivery_name,
r.role_name delivery_depName
FROM storage_approve s
LEFT JOIN product p ON s.product_id =
p.product_id
LEFT JOIN role r ON s.deliveryRoleId = r.role_id
LEFT JOIN
users u ON s.approverId = u.user_id
LEFT JOIN users u1 ON s.deliveryId =
u1.user_id
<where>
<!-- 送货部门id(权限id) -->
<if
test="storage.deliveryRoleId != null and storage.deliveryRoleId >0">
AND s.deliveryRoleId = #{storage.deliveryRoleId}
</if>
</where>
ORDER BY
s.approve_time DESC,
s.delivery_time ASC
</select>
<!-- 根据条件查询入库审批记录总数 -->
<select id="long" resultType="getExpStorageSearchCount">
SELECT
IFNULL(COUNT(*),0)
FROM
storage_approve s
LEFT JOIN product p ON
s.product_id = p.product_id
LEFT JOIN role r ON s.deliveryRoleId =
r.role_id
LEFT JOIN users u ON s.approverId = u.user_id
LEFT JOIN users
u1 ON s.deliveryId = u1.user_id
<where>
<!-- 送货部门id(权限id) -->
<if
test="storage.deliveryRoleId != null and storage.deliveryRoleId >0">
AND s.deliveryRoleId = #{storage.deliveryRoleId}
</if>
</where>
</select>
else if (new UsersDao().repeatUserId(userList.get(i).getUserId().replaceAll(" ", ""))) {
erroCount++;
Cell cell = sh0.getRow(erroCount).createCell(4);
cell.setCellValue("用户id重复!");
cell.setCellStyle(cellStyle);
continue;
}
导入功能：
1.dao （判断3）
2.xml
3.工具类 （判断时写dao方法）插入
4.jsp（三个function）
5.servlet
6.配置mybatis.xml
导入：
1.1.dao层的mapper(写方法名)
2.xml里用sql语句实现mapper中的方法 （映射时对比数据库段名和bean变量名） insert select
3.编写工具类（写方法）try catch
4.前端jsp添加按钮+function（命名一个servlet）
5.实现servlet
6.配置mybatis.xml
导入模板：
提前在指定路径内添加模板
1.前端添加模块+改fileType名字
2.在servlet里修改名称
数据库
dao层方法名
导入的xml部分
<!-- hr 批量添加计划 -->
<insert id="addPlans">
INSERT INTO
plan(batch_id,product_id,plan_num,remaining_num,generate_time,plan_source,planerId,roleId)
VALUES
<foreach collection="list" item="plan" separator=",">
(#{plan.batchId},#{plan.productId},#{plan.planNum},#{plan.planNum},Now(),#{plan.planSource},#{plan.planerId},#{plan.roleId})
</foreach>
</insert>
<!-- 根据条件查询重复批次号 -->
<select id="repeatBatchId" resultType="int">
SELECT COUNT(*) FROM plan WHERE batch_id=#{batchId}
</select>
<!-- 根据条件查询重复零件号数量（规格型号） -->
<select id="repeatProductId" resultType="int">
SELECT COUNT(*) FROM plan WHERE product_id=#{productId}
</select>导入的工具类部分
//hr 导入excel到计划表中
@SuppressWarnings("deprecation")
public int importPlans(String filePath,String planerId,Long roleId ) {
int erroCount = 0;
int result = 0;
InputStream fileIn = null;
try {
fileIn = new FileInputStream(filePath);
Workbook excel = WorkbookFactory.create(fileIn);
//新建一个list存放待导入数据
List<PlanBean> planList = new ArrayList<PlanBean>();
System.out.println("获取excel成功！");
fileIn.close();
Sheet sh0 = excel.getSheetAt(0);
// 错误信息变红
XSSFCellStyle cellStyle = (XSSFCellStyle) excel.createCellStyle();
XSSFFont cellFont = (XSSFFont) excel.createFont();
cellFont.setColor(new XSSFColor(Color.red));
cellStyle.setFont(cellFont);
// 装载信息
for (Row row : sh0) {
if (row.getRowNum() < 1) {
continue;
}
PlanBean plan = new PlanBean();
plan.setBatchId(row.getCell(0).getStringCellValue().replaceAll(" ", ""));
plan.setProductId(row.getCell(1).getStringCellValue().replaceAll(" ", ""));
plan.setProductName(row.getCell(2).getStringCellValue().replaceAll(" ", ""));
//数字型
plan.setPlanNum(Integer.parseInt(new DataFormatter().formatCellValue(row.getCell(4))));
planList.add(plan);
}
SqlSession session = null;
try {
session = MyBatisUtil.createSqlsession();
// 判断
for (int i = 0, erroRow = 1; i < planList.size(); i++, erroRow++) {
if (planList.get(i).getBatchId().equals("")||planList.get(i).getProductId().equals("")||planList.get(i).getPlanNum()<=0) {
erroCount++;
Cell cell = sh0.getRow(erroRow).createCell(6);
cell.setCellValue("批次号/规格型号/计划数量不可为空!");
cell.setCellStyle(cellStyle);
continue;
}
else if (session.getMapper(PlanManageMapper.class).repeatBatchId(planList.get(i).getBatchId())>0) {
erroCount++;
Cell cell = sh0.getRow(erroRow).createCell(6);
cell.setCellValue("批次号重复!");
cell.setCellStyle(cellStyle);
continue;
}
else if(session.getMapper(PlanManageMapper.class).repeatProductId(planList.get(i).getProductId())<0) {
erroCount++;
Cell cell = sh0.getRow(erroRow).createCell(6);
cell.setCellValue("规格型号不存在！");
cell.setCellStyle(cellStyle);
continue;
}
}
// 插入数据(如果errorCount=0)
if (erroCount == 0) {
result = session.getMapper(PlanManageMapper.class).addPlans(planList);
} else {
OutputStream fileOut = new FileOutputStream(filePath);
excel.write(fileOut);
fileOut.close();
System.out.println("错误信息写入完毕！");
}
session.commit();
} catch (Exception e) {
e.printStackTrace();
result = 0;
session.rollback();
} finally {
MyBatisUtil.closeSqlSession(session);
}
System.out.println("导入结束，成功导入的数据有" + result + "条！");
} catch (IOException e) {
// TODO Auto-generated catch block
result = -1;
e.printStackTrace();
} catch (Exception e) {
result = -2;
e.printStackTrace();
System.out.println("导入过程出现错误！");
} catch (Throwable t) {
result = -3;
System.out.println("出现未知错误！");
} finally {
if (fileIn != null) {
try {
fileIn.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return result;
}导入的.jsp部分
//导入
//文件导入功能
function fileImport(){
//判断是否选择文件
if ($("#file").filebox('getValue') == '') {
alert("请选择文件！");
return false;
}
//创建formData对象加入文件
var formData = new FormData();
formData.append('file', $("#file").filebox("files")[0]);
formData.append('planer', '${loginUser.userName }');
formData.append('roleId','${loginUser.roleId}');
// formData.append('planSource','${loginUser}') //计划来源
$.ajax({
url : '${pageContext.request.contextPath}/PlanImportServlet',
type : 'post',
data : formData,
dataType : 'json',
async : true,
cache : false,
processData : false,
contentType : false,
success : function(data) {
var result = parseInt(data.result);
if (result < 0) {
switch (result) {
case -1:
alert("IO流出现问题，请检查文件或者联系管理员！");
fileDelete(data.filePath);
break;
case -2:
alert("导入过程出现错误，请检查问题或者联系管理员！");
fileDelete(data.filePath);
break;
default:
alert("出现未知错误，请联系管理员！");
fileDelete(data.filePath);
}
} else if (result == 0) {
download(result, data.filePath);
} else {
alert("导入成功!");
}
paging($("#pageNow").val(), $("#pageSize").val());
},
error : function() {
alert("导入错误！");
}
});
}
/*下载错误文件功能*/
function download(result, filePath) {
var download = confirm("导入失败，是否下载错误信息？");
if (download == true) {
$(location).attr(
'href',
'${pageContext.request.contextPath}/FileDownloadServlet?filePath='
+ filePath);
} else {
fileDelete(filePath);
}
}
//删除文件功能
function fileDelete(filePath) {
$.ajax({
url : '${pageContext.request.contextPath}/FileDeleteServlet',
type : 'post',
data : {
filePath : filePath,
},
async : true,
error : function() {
alert("删除失败！");
}
});
}
编写servlet
public class PlanImportServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
String filePath = "";
SmartUpload su = new SmartUpload();
// 初始化对象，规定写法，无需理解
su.initialize(this.getServletConfig(), request, response);
// 将上传至服务器临时目录并获取路径
filePath = new UploadUtils().uploadTemp(su);
// 切换回字符流request
Request req = su.getRequest();
String planerId = req.getParameter("planerId");
Long roleId = Long.parseLong(req.getParameter("roleId"));
int flag = 0;// 记录没有成功导入的数据数量
PrintWriter out = response.getWriter();
JSONObject json = new JSONObject();
flag = new ExcelUtils().importPlans(filePath, planerId, roleId);
json.put("result", flag);
json.put("filePath", filePath);
out.print(json);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doPost(request, response);
}
}
（user）的servlet
public class UsersImportServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
String filePath = "";
SmartUpload su = new SmartUpload();
// 初始化对象，规定写法，无需理解
su.initialize(this.getServletConfig(), request, response);
// 将上传至服务器临时目录并获取路径
filePath = new UploadUtils().uploadTemp(su);
int flag = 0;// 记录没有成功导入的数据数量
PrintWriter out = response.getWriter();
JSONObject json = new JSONObject();
flag = new ExcelUtils().importUser(filePath);
json.put("result", flag);
json.put("filePath", filePath);
out.print(json);
}
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
doPost(request, response);
}
}
121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
