数据集监听类实现数据准备的实际用例
1. 问题概述
A：由于有些客户有源数据库和本地数据库,在报表运算之前需要实现一些数据准备等工作,报表可能从源数据库,即时的根据特定参数条件过滤出想要的数据,在数据集创建之前将过滤出的信息插入到本地的临时数据库表,之后报表展现时利用临时表的数据,当报表展现完成之后,删除临时表及其数据.
B:对于这类需求,在润乾报表里,我们叫数据集监听类,总体来说，数据集监听类主要用于数据集创建前后对数据库进行业务操作,提供接口.
2. 实现原理
A:在V4版本里需要实现IDataSetFactoryListener接口，提供beforeCreated和afterCreated两个方法.
B: 设计思路:第一步,数据集创建之前首先获取条件参数和sql语句
第二步,通过SQLDataStConfig配置数据集
第三步,创建临时数据库表导入满足条件的数据
第四步,把临时表中的sql语句配置到ReportDefine模板,运算报表展示数据
第五步,展示完毕afterCreated()方法中删除临时表
3. 图例说明
A.部署好数据监听类到指定路径,源数据库存放数据表数据如图:
B. 报表调用数据集监听类:
C. 报表模板根据特定参数进行数据过滤:
D. 报表从临时数据库表中提取想要的数据,然后删除临时表,后台打印详细信息:
4. 关键性程序说明
createData.java代码:
package api;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.JFrame;
import com.runqian.report4.dataset.DataSet;
import com.runqian.report4.dataset.IDataSetFactoryListener;
import com.runqian.report4.model.ReportDefine;
import com.runqian.report4.usermodel.ByteMap;
import com.runqian.report4.usermodel.CSReport;
import com.runqian.report4.usermodel.Context;
import com.runqian.report4.usermodel.DataSetConfig;
import com.runqian.report4.usermodel.DataSetMetaData;
import com.runqian.report4.usermodel.DataSourceConfig;
import com.runqian.report4.usermodel.Engine;
import com.runqian.report4.usermodel.IConnectionFactory;
import com.runqian.report4.usermodel.INormalCell;
import com.runqian.report4.usermodel.IReport;
import com.runqian.report4.usermodel.SQLDataSetConfig;
public class createData implements IDataSetFactoryListener {
private static final String driver = “com.newatlanta.jturbo.driver.Driver”;
// 源数据库连接
private static final String Originurl = “jdbc:JTurbo://localhost/exercise/charset=GBK”;
// 目标数据库连接
private static final String Targeturl = “jdbc:JTurbo://localhost/bookstore/charset=GBK”;
private static final String user = “sa”;
private static final String password = “sa”;
public void afterCreated(Context arg0, DataSetConfig arg1, DataSet arg2) {
// TODO Auto-generated method stub
Connection conn = getConnection(driver, Targeturl, user, password);
if (conn == null)
System.out.println(“[连接为空]“);
String tempsql = null;
Statement st = null;
try { // 展示完毕 删除临时表
st = conn.createStatement();
tempsql = “if exists(select *from sysObjects where name=’contract’) drop table contract”;
st.executeUpdate(tempsql);
} catch (SQLException e) {
e.printStackTrace();
}
}
public void beforeCreated(Context arg0, DataSetConfig arg1, DataSet arg2) {
// TODO Auto-generated method stub
Statement st = null;
SQLDataSetConfig dsc = (SQLDataSetConfig) arg1;
// 从报表模板里提取SQL,根据条件参数从源数据库表中过滤出自己想要的值,创建临时表
String sql = dsc.getSQL();
System.out.println(“报表模板自带SQL:” + sql);
String ID = arg0.getParamValue(“customerID”).toString(); // 获取报表参数CustmorID
sql = sql + ” where CUSTOMERID=’” + ID + “‘”;
Connection temp = null;
Statement stt = null;
ResultSet rs = null;
try { // 源数据库,获取符合条件的合同信息,可从报表里获取默认连接,也可新建源连接
// temp =arg0.getConnectionFactory(arg0.getDefDataSourceName()).getConnection();
temp = getConnection(driver, Originurl, user, password);
stt = temp.createStatement();
rs = stt.executeQuery(sql);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 建立本地连接创建临时表
System.out.println(“参数:” + ID + “;条件SQL语句:” + sql);
Connection conn = getConnection(driver, Targeturl, user, password);
if (conn == null)
System.out.println(“[连接为空]“);
String tempsql = null;
try {
st = conn.createStatement();
tempsql = “if exists(select *from sysObjects where name=’contract’) drop table contract”;
st.executeUpdate(tempsql);
System.out.println(“CONTRACT数据表已删除“);
tempsql = “CREATE TABLE CONTRACT(CONTRACTNO varchar(20),CUSTOMERID varchar(20),AMOUNT float)”;
System.out.println(“新建CONTRACT数据表“);
st.executeUpdate(tempsql);
while (rs.next()) {
tempsql = “INSERT INTO CONTRACT VALUES(‘” + rs.getString(1)
+ “‘,’” + rs.getString(2) + “‘,” + rs.getFloat(3) + “)”;
System.out.println(“INSERT INTO CONTRACT VALUES(‘”
+ rs.getString(1) + “‘,’” + rs.getString(2) + “‘,”
+ rs.getFloat(3) + “)”);
st.executeUpdate(tempsql);
}
System.out.println(“数据导入成功“);
} catch (SQLException se) {
se.printStackTrace();
}
}
private static Connection getConnection(String driver, String url,
String user, String password) // 建立数据库连接
{
Connection con = null;
try {
Class.forName(driver);
} catch (ClassNotFoundException cfe) {
cfe.printStackTrace();
}
try {
con = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return con;
}
}