Java Connector Data source

In the actual application, it may be necessary to dynamically change the data source according to the table name, such as in the program data set, through the table name parameters passed in to the database to fetch the corresponding table as the data source. For example, Finereport is reading a data source by abstracttabledata an abstract class, and all of the above data sources inherit the implementation of its abstract method, so users can use a custom type of data source as long as they implement the Abstracttabledata abstract class. (Program data set), which is a method for connecting a data set with a parametric program.

The Finereport report engine is able to read a defined data source for use as a report data source, with the principle of inheriting abstracttabledata.

1. Defining parameters

Define a parameter and define the data table structure, with the following code:

Public Paramtabledatademo () {          //define TableName parameter          this.parameters = new parameter[] {new Parameter ("TableName")};          //Define program Data set column name          columnnames = new String[columnnum];          for (int i = 0; i < Columnnum; i++) {              columnnames[i] = "column#" + string.valueof (i);          }  }

2. Set the data

Put the data into the defined table with the following code:

public void init () {//ensures only once if (valueList! = null) {return;} The saved database table name is string tableName = Parameters[0].getvalue (). toString ();//construct SQL statement and print out String sql = "SELECT * from" + Tablen Ame + ";"; Frcontext.getlogger (). info ("Query SQL of Paramtabledatademo: \ n" + SQL);//saved result set valuelist = new ArrayList ();//Start build number below According to the database connection, query Connection conn = This.getconnection () by the SQL statement just now; try {Statement stmt = conn.createstatement (); ResultSet rs = stmt.executequery (sql);//Get the details of the record and get the total number of columns ResultSetMetaData RSMD = Rs.getmetadata (); colnum = Rsmd.getcolumncount ();//Save data with Object object[] Objarray = Null;while (Rs.next ()) {Objarray = new Object[colnum];for (int i = 0; i < Colnum; i++) {Objarray[i] = Rs.getobject (i + 1);} Add this line of data Valuelist.add (Objarray) in ValueList;} Release the database resource Rs.close (); Stmt.close (); Conn.close ();//Print the total number of data rows taken Frcontext.getlogger (). info ("Query SQL of Paramtabledatademo: \ n "+ valuelist.size () +" rows selected ");} catch (Exception e) {e.printstacktrace ();}}

3. Complete Data Set code

The code for the entire parameter data set is as follows

Package Com.fr.data;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.resultset;import Java.sql.resultsetmetadata;import Java.sql.statement;import Java.util.arraylist;import Com.fr.base.Env;import Com.fr.base.frcontext;import Com.fr.data.abstracttabledata;import Com.fr.base.parameter;public Class Paramtabledatademo extends Abstracttabledata {//Column an array group, save program dataset all column names private string[] ColumnNames = null;// Define the number of columns for the program dataset private int columnnum = 10;//The actual number of columns saved in the query table private int colnum = 0;//Save query Get column value private ArrayList valueList = null;/ /constructor that defines the table structure, which has 10 columns of data, named Column#0,column#1, ... Column#9public Paramtabledatademo () {//define TableName parameter Setdefaultparameters (new parameter[] {new Parameter ("TableName") )});//define Program Data set column name ColumnNames = new String[columnnum];for (int i = 0; i < Columnnum; i++) {columnnames[i] = "column#" + String.valueof (i);}} Implementation of the other four methods public int getColumnCount () {return columnnum;} Public String getcolumnname (int columnindex) {return columnnames[columnindex];} Publicint GetRowCount () {init (); return valuelist.size ();} Public Object getvalueat (int rowIndex, int columnindex) {init (); if (columnindex >= colnum) {return null;} Return ((object[]) Valuelist.get (RowIndex)) [ColumnIndex];} Prepare data public void init () {//Ensure only once if (valueList! = null) {return;} The saved database table name is string tableName = Parameters[0].getvalue (). toString ();//construct SQL statement and print out String sql = "SELECT * from" + Tablen Ame + ";"; Frcontext.getlogger (). info ("Query SQL of Paramtabledatademo: \ n" + SQL);//saved result set valuelist = new ArrayList ();//Start build number below According to the database connection, query Connection conn = This.getconnection () by the SQL statement just now; try {Statement stmt = conn.createstatement (); ResultSet rs = stmt.executequery (sql);//Get the details of the record and get the total number of columns ResultSetMetaData RSMD = Rs.getmetadata (); colnum = Rsmd.getcolumncount ();//Save data with Object object[] Objarray = Null;while (Rs.next ()) {Objarray = new Object[colnum];for (int i = 0; i < Colnum; i++) {Objarray[i] = Rs.getobject (i + 1);} Add this line of data Valuelist.add (Objarray) in ValueList;} Release database resource Rs.close (); STMT.close (); Conn.close ();//Print the total number of data rows taken Frcontext.getlogger (). info ("Query SQL of Paramtabledatademo: \ n" + Valuelist.size () + "rows selected");} catch (Exception e) {e.printstacktrace ();}} Get the database connection drivername and URL can be replaced with the public Connection getconnection () {String drivername = "Org.sqlite.JDBC" you need; String url = "Jdbc:sqlite://d:\\finereport_8.0\\webreport\\frdemo.db"; String username = ""; String password = ""; Connection con = null;try {class.forname (drivername); con = drivermanager.getconnection (URL, username, password);} catch (Exception e) {e.printstacktrace (); return null;} return con;} Release some resources, because there may be repeated calls, so you need to release valuelist, releasing the results of the last query from public void release () throws Exception {super.release (); This.valuelist = null;}}

Compile the Paramtabledatademo.java to copy the generated Paramtabledatademo.class class files to the report Engineering/web-inf/classes directory. Since the class is in the Com.fr.data package, the class should eventually be placed under/web-inf/classes/com/fr/data. The program data source is now defined.

4. Configure the program data set

Create a new report, create a new program data source in a report dataset, select a program dataset that we have defined, such as a name that can be customized, such as divtable

5. Using the program Data set

After you have configured the program data source, you can use the defined program data set, select the DataSet, click Preview

button, you can enter the table name to dynamically get the corresponding data table, and make a template, such as

If the data is not previewed, verify that the URL address is correct when the database connection is defined in the code snippet.

As you can see, the data in the Stscore table has been extracted into the program dataset table, and as with other types of datasets, the cell data column bindings can be implemented by dragging and dropping the method.

Java Connector Data source