填报监听类中实现修改报表原纪录执行insert操作(转载)

首页 > 分类 > 填报监听类中实现修改报表原纪录执行insert操作（一）

填报监听类中实现修改报表原纪录执行insert操作（一）

类别：baobiao时间：2012年12月06日 阅读：0 views

问题背景

客户希望用户在修改填报表中数据的时候，若修改的字段为对应数据表的主键，则报表提交时该记录执行insert操作；若修改的字段为非主键，则报表提交时执行update操作。

问题分析

当前润乾报表对于修改报表原有数据后进行填报，都执行update操作，无论修改的字段是主键还是非主键，要想实现上面问题背景中提到的需求，我们可以考虑不使用报表的更新属性设置，而是自己写sql进行数据库操作。

我们可以使用自定义更新类进行处理，而本例是在填报监听类中做的，好处是因为填报监听类中的beforeSave()和afterSave()方法处理问题的灵活性较强。接下来我们看一下具体实现。

实现步骤

1、 做报表

做一张简单的填报表，本例使用报表自带的demo数据源，报表制作时需注意，将报表属性中的【数据提交方案】修改为 全部单元格，不设置任何更新属性，如下图：

 

2、 写填报监听类：CustomInputListener.java

① 循环报表行列，判断主键是否被修改

② 将修改主键的行号放到ArrayList中，未修改主键的行号放到另外ArrayList中

③ 遍历两个ArrayList，对修改主键的行进行insert操作；未修改主键的行进行update操作。

（源码见附在本文最后）

 

3、写发布报表的jsp

在发布报表的标签中配置：inputListener=”runqian.CustomInputListener”

 

附：

填报监听类 CustomInputListener.java

package runqian;

import java.sql.*;

import java.util.ArrayList;

import java.util.Collection;

import java.util.List;

 

import com.runqian.report4.usermodel.Context;

import com.runqian.report4.usermodel.INormalCell;

import com.runqian.report4.usermodel.input.AbstractInputListener;

 

public class CustomInputListener extends AbstractInputListener {

public void beforeSave() throws Exception {

//获取报表行列数

int row = report.getRowCount();

int col = report.getColCount();

 

INormalCell iCell;

Object oldObject;

Object inputObject;

String oldString;

String inputString;

List<Integer> listOldLine = new ArrayList<Integer>();// 存放主鍵未被修改行

List<Integer> listInputLine = new ArrayList<Integer>();// 存放主鍵被修改行

for (int i = 2; i <= row; i++) {// 从数据区开始

for (int j = 1; j <= 2; j++) {// j的最大值为主键个数，即第一列和第二列为主键

iCell = report.getCell(i, (short) j);

oldObject = iCell.getValue();

inputObject = iCell.getInputValue();

 

if (oldObject == null) {

oldString = “”;

} else {

oldString = oldObject.toString();

}

 

if (inputObject == null) {

inputString = “”;

} else {

inputString = inputObject.toString();

}

 

if (!oldString.equals(inputString)) {// 主键被修改

listInputLine.add(i);//若存在多个主键，则list中会有重复值，用的时候需去除重复

}

}

}

 

//将未改变主键值放入list，有重复值，使用时需去重

for (int i = 2; i <= row; i++) {

if (!listInputLine.contains(i)) {

listOldLine.add(i);

}

}

 

// 去除List中的重复数据

listOldLine = new NotRepeatedList<Integer>(listOldLine);

listInputLine = new NotRepeatedList<Integer>(listInputLine);

 

// 根据主键是否被修改，更新数据库

Context cxt = new Context();

Connection conn = null;

Statement stmt = null;

ResultSet rs = null;

//定义存储要更新的各字段值

String CUSTOMER_ID = “”;

String CUSTOMER_NAME = “”;

String CUSTOMERTYPE = “”;

String PROVINCE = “”;

String CITY = “”;

String TEL = “”;

String sql1 = “”;

String sql2 = “”;

int size = 0;

 

try {

conn = cxt.getConnectionFactory(“demo”).getConnection();

stmt = conn.createStatement();

 

//主键被修改的行，执行insert操作

for (int i = 0; i < listInputLine.size(); i++) {

size = listInputLine.get(i);

CUSTOMER_ID = (report.getCell(size, (short) 1).getInputValue()==null?”":report.getCell(size, (short) 1).getInputValue().toString());

CUSTOMER_NAME = (report.getCell(size, (short) 2).getInputValue()==null?”":report.getCell(size, (short) 2).getInputValue().toString());

CUSTOMERTYPE = (report.getCell(size, (short) 3).getInputValue()==null?”":report.getCell(size, (short) 3).getInputValue().toString());

PROVINCE = (report.getCell(size, (short) 4).getInputValue()==null?”":report.getCell(size, (short) 4).getInputValue().toString());

CITY = (report.getCell(size, (short) 5).getInputValue()==null?”":report.getCell(size, (short) 5).getInputValue().toString());

TEL = (report.getCell(size, (short) 6).getInputValue()==null?”":report.getCell(size, (short) 6).getInputValue().toString());

 

sql1 = “insert into CUSTOMER(CUSTOMER_ID,CUSTOMER_NAME,CUSTOMERTYPE,PROVINCE,CITY,TEL) values(” +

“‘” +CUSTOMER_ID+”‘,’”+CUSTOMER_NAME+”‘,’”+CUSTOMERTYPE+”‘,’”+PROVINCE+”‘,’”+CITY+”‘,’”+TEL+”‘”

+”)”;

System.out.println(“sql:”+sql1);

stmt.executeUpdate(sql1);

conn.commit();

}

 

//主键未修改行，执行update操作

for(int i=0;i<listOldLine.size();i++){

size = listOldLine.get(i);

CUSTOMER_ID = (report.getCell(size, (short) 1).getInputValue()==null?”":report.getCell(size, (short) 1).getInputValue().toString());

CUSTOMER_NAME = (report.getCell(size, (short) 2).getInputValue()==null?”":report.getCell(size, (short) 2).getInputValue().toString());

CUSTOMERTYPE = (report.getCell(size, (short) 3).getInputValue()==null?”":report.getCell(size, (short) 3).getInputValue().toString());

PROVINCE = (report.getCell(size, (short) 4).getInputValue()==null?”":report.getCell(size, (short) 4).getInputValue().toString());

CITY = (report.getCell(size, (short) 5).getInputValue()==null?”":report.getCell(size, (short) 5).getInputValue().toString());

TEL = (report.getCell(size, (short) 6).getInputValue()==null?”":report.getCell(size, (short) 6).getInputValue().toString());

 

sql2 = “update CUSTOMER set CUSTOMER_ID=’”+CUSTOMER_ID+”‘,CUSTOMER_NAME=’”+CUSTOMER_NAME+”‘,CUSTOMERTYPE=’”+CUSTOMERTYPE+

“‘,PROVINCE=’”+PROVINCE+”‘,CITY=’”+CITY+”‘,TEL=’”+TEL+”‘ where CUSTOMER_ID=’”+CUSTOMER_ID+”‘ and CUSTOMER_NAME=’”+CUSTOMER_NAME+”‘”;

 

System.out.println(“sq2:”+sql2);

stmt.executeUpdate(sql2);

conn.commit();

}

 

} catch (SQLException e) {

e.printStackTrace();

}finally{

if(rs!=null)

rs.close();

if(stmt!=null)

stmt.close();

if(conn!=null)

conn.close();

}

}

 

public void afterSave() throws Exception {

super.afterSave();

}

}

 

/**

* 去掉List中重复数据类

*/

class NotRepeatedList<E> extends ArrayList<E> {

private static final long serialVersionUID = 1269085290242775328L;

 

public NotRepeatedList() {

super();

}

 

public NotRepeatedList(Collection<? extends E> c) {

for (E e : c) {

add(e);

}

}

 

public NotRepeatedList(int initialCapacity) {

super(initialCapacity);

}

 

public boolean add(E o) {

if (!contains(o)) {

return super.add(o);

}

return false;

};

 

public void add(int index, E element) {

if (!contains(element)) {

super.add(index, element);

}

};

}

 

发布报表的jsp

<%@ page language=“java” contentType=“text/html; charset=GBK”

pageEncoding=“GBK”%>

 

<%@ taglib uri=“/WEB-INF/runqianReport4.tld” prefix=“report”%>

<html>

<head>

<title>customInput</title>

</head>

 

<body>

<div align=“center”>

<report:html name=“report1″

reportFileName=“custom.raq”

generateParamForm=“no”

inputListener=“runqian.CustomInputListener” />

</div>

</body>

</html>

上一篇： 字段数不固定的类同期比实现

下一篇： 填报监听类中实现修改报表原纪录执行insert操作（二）