Java(Script) Library
Query CSV file from Script Library
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 as a Script Library, results to JfreeChart, show user our chart via Explorer window. This Script can be accessed by other parts of your application, Xpages, Notes form and so on.
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 (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.
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 Java via Lotus Notes Script Library, 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:
Questions, comments, please post a brief message on our Contact form on the main site. Thank you for coming...