Java(Script) Lib/LS

Query CSV file from Java and LotusScript

Form Button



System Requirements:

Download Domino Designer 8.5.2 Environment (DDE)

http://www.ibm.com/developerworks/downloads/ls/dominodesigner/


Download CSV file JDBC driver

http://sourceforge.net/projects/csvjdbc/


Download JFreeChart

http://www.jfree.org/jfreechart/download.html


Download Jcommon jar

http://www.jfree.org/jcommon/


Introduction:

Query CSV file into Java Application, run LotusScript code on form to load results to JfreeChart, show user our chart via Explorer window.


Disclaimer:

Information contained in the following is presented as is. This tutorial assumes you have basic programming knowledge. All tutorials are based on an Eclipse/Eclipse-based software. Should you need to familiarize yourself with a certain Eclipse environment, prior to continuing this tutorial, please stop now and see our Tutorials page...


Write Java class to Query CSV file with Java and LotusScript

At this point we assume Domino Designer 8.5.2 is downloaded/installed, csvjdbc, jfreechart, and jcommon have been downloaded and jar files are added to your CLASSPATH. You have also built a Java class as a Script Library to read the CSV file and load the data to the JFreeChart Engine. Copy and paste below code to your environment, areas of interest have been highlighted for your convenience.


-Add below in Options, from your objects pane

-Add subsequent code in Click


Run JfreeChart.lss (Action)


[CODE]



'@AppName Döcu

'@author: Dököll Solutions, Inc.

'@version 2012.04.27.5.05.AM

'Program: Run JFreeChart Action

'Code to run JFreeChart Bar Chart



'call LS2J

Uselsx "*javacon"

'call Java (Script) Library

Use "JFreeChartWithCSVDataJavaAgent"


*You can add the above code in your Options

'button code

Sub Click(Source As Button)

' jump down to error handling, as issues arise...

On Error Goto ErrorHandlerMsg

'...

Dim JFreeChartScriptLibClass As JavaClass

Dim JFreeChartScriptLibObject As JavaObject

' set up a session

Dim JFreeChartSession As New JavaSession()

' load JFreeChartScriptLibClass

Set JFreeChartScriptLibClass = JFreeChartSession.GetClass("JFreeChartWithCSVDataJavaAgent")

' Call JFreeChartScriptLibClass,

Set JFreeChartScriptLibObject = JFreeChartScriptLibClass.CreateObject()

'Main method in Java code

Call JFreeChartScriptLibObject.doLoadJFreeChart()

'...

Exit Sub

'pick up errors

ErrorHandlerMsg:

'set up variable to load errors

Dim JFreeChartError As JavaError

'...

Set JFreeChartError = JFreeChartSession.GetLastJavaError

'...

Msgbox JFreeChartError.errorMsg + ": " + JFreeChartError.stackTrace

'end of program...

Exit Sub

End Sub


Write Java (Script) Library to query CSV

Full code added below, jump ahead if necessary, areas of interest have been highlighted for your convenience. Below JavaScript Library run a query against a CSV file and loads the results to a bar chart using the JfreeChart Engine; the code works in conjunction with the above LotusScript



JfreeChartWithCSVDataJavaAgent.java


/**

* Created from copy: 2012.04.23.10.47.AM

* Load CSV Data into Bar Chart

*

*/


import java.io.File;

import java.io.FileOutputStream;

import java.io.IOException;

import java.sql.SQLException;


import org.jfree.chart.ChartFactory;

import org.jfree.chart.ChartUtilities;

import org.jfree.chart.JFreeChart;

import org.jfree.chart.plot.PlotOrientation;

import org.jfree.data.jdbc.JDBCCategoryDataset;


/**

* @author Dököll Solutions, Inc.

* @version 2012.04.23.10.47.AM

*

*/

public class JFreeChartWithCSVDataJavaAgent {

// Set up static path/file variables

private static final String FILE_PATH = "C:\\temp\\JFREE_DATA\\";

private static final String OUTFILE_NAME = "JFreeChartCSVExplorerData.jpg";


//TO DO: Add button code to this, run as a bean

public String doLoadJFreeChart() throws ClassNotFoundException, SQLException{

//public static void main(String[] args) throws Exception {

//set up query string

String query = "SELECT * FROM UserNewLineOutboundInformation";

//set path and connection to CSV file

JDBCCategoryDataset dataset = new JDBCCategoryDataset(

"jdbc:relique:csv:C:\\temp\\CSV_DATA\\",

"org.relique.jdbc.csv.CsvDriver", "root", "root");

//run query

dataset.executeQuery(query);

//create bar chart in 3D

JFreeChart chart = ChartFactory.createBarChart3D(

"JFreeChart Agent CSV Trend", "SiteName", "SiteNumber", dataset,

PlotOrientation.VERTICAL, true, true, false);

try {

//save chart to location, gets picked up by Explorer

ChartUtilities.saveChartAsJPEG(new File(FILE_PATH+OUTFILE_NAME), chart, 400,

300);

System.out.println("Chart created from CSV file.");


//...

} catch (IOException e) {

System.out.println("Problem in creating chart.");

}


// Load Chart to Explorer

// 2012.04.23.8.25.AM

try {


//Grab file created from C:/

//set it up to run through Explorer window

FileOutputStream fos = new FileOutputStream(FILE_PATH+OUTFILE_NAME);

ChartUtilities.writeChartAsJPEG(fos, 1, chart, 950, 600);

//...

fos.flush();

fos.close();


//load chart to Explorer window...

Runtime run = Runtime.getRuntime();

//...

run.exec("explorer.exe " + FILE_PATH+OUTFILE_NAME);


} catch (Exception e) {


}

//...

return query;


}

}




Conclusion:

You can now Query CSV files from LotusScript and Java via Lotus Notes form button, chart that can be used by your application documents.


TIP: Reference jfreechart, jcommon jar files in your CLASSPATH, connect them to your project via build path. Be certain to add these files to your /lib/ext folder within Notes install directory.


References:

http://csvjdbc.sourceforge.net/

http://www.java2s.com/Code/Java/Chart/CatalogChart.htm


Questions, comments, please post a brief message on our Contact form on the main site. Thank you for coming...