大多数情况下,FineReport直接在设计器里使用“数据集查询”,直接写SQL就能满足报表要求,但对于一些复杂的报表,有时候SQL处理并不方便,这时可以把查询结果在应用层做一些预处理后,再传递给报表,即所谓的“程序数据集”,FineReport的帮助文档上给了一个示例:
1 package com.fr.data; 2 3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.ResultSetMetaData; 7 import java.sql.Statement; 8 import java.util.ArrayList; 9 import com.fr.base.FRContext; 10 import com.fr.data.AbstractTableData; 11 import com.fr.base.Parameter; 12 13 public class ParamTableDataDemo extends AbstractTableData { 14 // 列名数组,保存程序数据集所有列名 15 private String[] columnNames = null; 16 // 定义程序数据集的列数量 17 private int columnNum = 10; 18 // 保存查询表的实际列数量 19 private int colNum = 0; 20 // 保存查询得到列值 21 private ArrayList valueList = null; 22 23 // 构造函数,定义表结构,该表有10个数据列,列名为column#0,column#1,。。。。。。column#9 24 public ParamTableDataDemo() { 25 // 定义tableName参数 26 this.parameters = new Parameter[] { new Parameter("tableName") }; 27 // 定义程序数据集列名 28 columnNames = new String[columnNum]; 29 for (int i = 0; i < columnNum; i++) { 30 columnNames[i] = "column#" + String.valueOf(i); 31 } 32 } 33 34 // 实现其他四个方法 35 public int getColumnCount() { 36 return columnNum; 37 } 38 39 public String getColumnName(int columnIndex) { 40 return columnNames[columnIndex]; 41 } 42 43 public int getRowCount() { 44 init(); 45 return valueList.size(); 46 } 47 48 public Object getValueAt(int rowIndex, int columnIndex) { 49 init(); 50 if (columnIndex >= colNum) { 51 return null; 52 } 53 return ((Object[]) valueList.get(rowIndex))[columnIndex]; 54 } 55 56 // 准备数据 57 public void init() { 58 // 确保只被执行一次 59 if (valueList != null) { 60 return; 61 } 62 // 保存得到的数据库表名 63 String tableName = parameters[0].getValue().toString(); 64 // 构造SQL语句,并打印出来 65 String sql = "select * from " + tableName + ";"; 66 FRContext.getLogger().info("Query SQL of ParamTableDataDemo: \n" + sql); 67 // 保存得到的结果集 68 valueList = new ArrayList(); 69 // 下面开始建立数据库连接,按照刚才的SQL语句进行查询 70 Connection conn = this.getConnection(); 71 try { 72 Statement stmt = conn.createStatement(); 73 ResultSet rs = stmt.executeQuery(sql); 74 // 获得记录的详细信息,然后获得总列数 75 ResultSetMetaData rsmd = rs.getMetaData(); 76 colNum = rsmd.getColumnCount(); 77 // 用对象保存数据 78 Object[] objArray = null; 79 while (rs.next()) { 80 objArray = new Object[colNum]; 81 for (int i = 0; i < colNum; i++) { 82 objArray[i] = rs.getObject(i + 1); 83 } 84 // 在valueList中加入这一行数据 85 valueList.add(objArray); 86 } 87 // 释放数据库资源 88 rs.close(); 89 stmt.close(); 90 conn.close(); 91 // 打印一共取到的数据行数量 92 FRContext.getLogger().info( 93 "Query SQL of ParamTableDataDemo: \n" + valueList.size() 94 + " rows selected"); 95 } catch (Exception e) { 96 e.printStackTrace(); 97 } 98 } 99 100 // 获取数据库连接 driverName和 url 可以换成您需要的 101 public Connection getConnection() { 102 String driverName = "sun.jdbc.odbc.JdbcOdbcDriver"; 103 String url = "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\\FineReport_7.0\\WebReport\\FRDemo.mdb"; 104 String username = ""; 105 String password = ""; 106 Connection con = null; 107 try { 108 Class.forName(driverName); 109 con = DriverManager.getConnection(url, username, password); 110 } catch (Exception e) { 111 e.printStackTrace(); 112 return null; 113 } 114 return con; 115 } 116 117 // 释放一些资源,因为可能会有重复调用,所以需释放valueList,将上次查询的结果释放掉 118 public void release() throws Exception { 119 super.release(); 120 this.valueList = null; 121 } 122 }
这个示例我个人觉得有二个地方不太方便:
1、db连接串硬编码写死在代码里,维护起来不太方便,目前大多数b/s应用,对于数据库连接,通常是利用spring在xml里配置datasource bean,运行时动态注入2、将查询出的结果,填充到数据集时,采用的是数字索引(见82行),代码虽然简洁,但是可读性比较差
折腾一番后,于是便有了下面的改进版本:
1 package infosky.ckg.fr.data; 2 3 import infosky.ckg.utils.AppContext; 4 import java.sql.Connection; 5 import java.sql.ResultSet; 6 import java.sql.Statement; 7 import java.util.LinkedHashMap; 8 import java.util.LinkedHashSet; 9 import javax.sql.DataSource; 10 import com.fr.base.Parameter; 11 import com.fr.data.AbstractTableData; 12 import com.fr.general.data.TableDataException; 13 14 public class ParameterLinkedHashSetDataDemo extends AbstractTableData { 15 16 private static final long serialVersionUID = 8818000311745955539L; 17 18 // 字段名枚举 19 enum FIELD_NAME { 20 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY 21 } 22 23 private String[] columNames; 24 25 private LinkedHashSet> rowData; 26 27 public ParameterLinkedHashSetDataDemo() { 28 this.parameters = new Parameter[] { new Parameter("jobId"), 29 new Parameter("minSalary"), new Parameter("maxSalary") }; 30 31 // 填充字段名 32 columNames = new String[FIELD_NAME.values().length]; 33 int i = 0; 34 for (FIELD_NAME fieldName : FIELD_NAME.values()) { 35 columNames[i] = fieldName.toString(); 36 i++; 37 } 38 39 } 40 41 @Override 42 public int getColumnCount() throws TableDataException { 43 return columNames.length; 44 } 45 46 @Override 47 public String getColumnName(int columnIndex) throws TableDataException { 48 return columNames[columnIndex]; 49 } 50 51 @Override 52 public int getRowCount() throws TableDataException { 53 queryData(); 54 return rowData.size(); 55 } 56 57 @Override 58 public Object getValueAt(int rowIndex, int columnIndex) { 59 queryData(); 60 int tempRowIndex = 0; 61 for (LinkedHashMap row : rowData) { 62 if (tempRowIndex == rowIndex) { 63 return row.get(columNames[columnIndex]); 64 } 65 tempRowIndex += 1; 66 } 67 return null; 68 } 69 70 // 查询数据 71 private void queryData() { 72 // 确保只被执行一次 73 if (rowData != null) { 74 return; 75 } 76 77 // 传入的参数 78 String jobId = parameters[0].getValue().toString(); 79 float minSalary = Float.parseFloat(parameters[1].getValue().toString()); 80 float maxSalary = Float.parseFloat(parameters[2].getValue().toString()); 81 82 // 拼装SQL 83 String sql = "select * from EMPLOYEES where JOB_ID='" + jobId 84 + "' and SALARY between " + minSalary + " and " + maxSalary; 85 86 rowData = new LinkedHashSet >(); 87 88 Connection conn = this.getConnection(); 89 try { 90 Statement stmt = conn.createStatement(); 91 // 执行查询 92 ResultSet rs = stmt.executeQuery(sql); 93 while (rs.next()) { 94 // 填充行数据 95 // 注:字段赋值的顺序,要跟枚举里的顺序一样 96 LinkedHashMap row = new LinkedHashMap (); 97 row.put(FIELD_NAME.EMPLOYEE_ID.toString(), 98 rs.getInt(FIELD_NAME.EMPLOYEE_ID.toString())); 99 row.put(FIELD_NAME.FIRST_NAME.toString(),100 rs.getString(FIELD_NAME.FIRST_NAME.toString()));101 row.put(FIELD_NAME.LAST_NAME.toString(),102 rs.getString(FIELD_NAME.LAST_NAME.toString()));103 row.put(FIELD_NAME.EMAIL.toString(),104 rs.getString(FIELD_NAME.EMAIL.toString()));105 row.put(FIELD_NAME.PHONE_NUMBER.toString(),106 rs.getString("PHONE_NUMBER"));107 row.put(FIELD_NAME.HIRE_DATE.toString(),108 rs.getDate(FIELD_NAME.HIRE_DATE.toString()));109 row.put(FIELD_NAME.JOB_ID.toString(),110 rs.getString(FIELD_NAME.JOB_ID.toString()));111 row.put(FIELD_NAME.SALARY.toString(),112 rs.getFloat(FIELD_NAME.SALARY.toString()));113 rowData.add(row);114 }115 rs.close();116 stmt.close();117 conn.close();118 } catch (Exception e) {119 e.printStackTrace();120 }121 122 }123 124 // 获取数据库连接125 private Connection getConnection() {126 Connection con = null;127 try {128 DataSource dataSource = AppContext.getInstance().getAppContext()129 .getBean("dataSource", DataSource.class);130 con = dataSource.getConnection();131 } catch (Exception e) {132 e.printStackTrace();133 return null;134 }135 return con;136 }137 138 // 释放资源139 public void release() throws Exception {140 super.release();141 this.rowData = null;142 }143 144 }
改进的地方:
1、getConnection方法,利用Spring注入datasource,当然为了注入方便,还需要一个辅助类AppContext1 package infosky.ckg.utils; 2 3 import org.springframework.context.support.AbstractApplicationContext; 4 import org.springframework.context.support.ClassPathXmlApplicationContext; 5 6 public class AppContext { 7 private static AppContext instance; 8 9 private AbstractApplicationContext appContext;10 11 public synchronized static AppContext getInstance() {12 if (instance == null) {13 instance = new AppContext();14 }15 return instance;16 }17 18 private AppContext() {19 this.appContext = new ClassPathXmlApplicationContext(20 "spring/root-context.xml");21 }22 23 public AbstractApplicationContext getAppContext() {24 return appContext;25 }26 27 }
classes/spring/root-context.xml 里配置db连接
1 26 7 9 1510 11 12 13 14
2、将原来的数组,换成了LinkedHashSet<LinkedHashMap<String, Object>>,这样db查询结果填充到"数据集"时,处理代码的可读性就多好了(见queryData方法),但也要注意到LinkedHashSet/LinkedHashMap的性能较Array而言,有所下降,正所谓:有所得必有得失。但对于复杂的汇总统计报表,展示的数据通常不会太多,所以这个问题我个人看来并不严重。