Backing Bean/CSV
Query CSV file from Java
Xpages 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/
Introduction:
Query CSV file into Java Application, add to console.
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
At this point we assume Domino Designer 8.5.2 is downloaded/installed, CSVJdbc has been downloaded and jar file added to your CLASSPATH. You have also built a Java class read the CSV file and load its data to console. Copy and paste below code to your environment, areas of interest have been highlighted for your convenience.
ReadCSVFileBean.java
[CODE]
/**
*
* @AppName: Docu.nsf
* Program: ReadCSVFileVBean.java
* Created from Copy: 2012.04.21.5.56.PM
* Build Results into Console with CSV
*/
package com.dokoll.solutions.inc.cvs.trials;
/**
* @author Dököll Solutions, Inc.
* @version 2012.04.21.5.56.PM
*
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
//...
//Start of Program
public class ReadCSVFileBean
{
//...
//Button code
public static void doGetCSVConsoleData()
{
//...
//Entering try catch
try
{
// load the driver into memory
Class.forName("org.relique.jdbc.csv.CsvDriver");
// create a connection. The first command line parameter is assumed to
// be the directory in which the .csv files are held
Connection conn = DriverManager.getConnection("jdbc:relique:csv:C:\\temp\\CSV_DATA\\");
// create a Statement object to execute the query with
//2012.04.21.6.02.PM
//TO DO: Attempt using Prepared Statement here...
Statement stmt = conn.createStatement();
// Select* from UserNewLineOutboundInformation.csv
ResultSet results = stmt.executeQuery("SELECT * FROM UserNewLineOutboundInformation");
// dump out the results
while (results.next())
{
//TO DO: Write a separate JavaBean to build setters for variables
//that can used in Xpages...
String SiteName = results.getString("SiteName");
String SiteNumber = results.getString("SiteNumber");
//Load to Console
System.out.println("SiteName " + " SiteNumber");
System.out.println(SiteName + " " + SiteNumber);
}
// clean up
results.close();
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}//end of program...
}
Build Xpage file
Full code added below, jump ahead if necessary, areas of interest have been highlighted for your convenience.
xpquerycsv.xsp
<?xml version="1.0" encoding="UTF-8"?>
<xp:view xmlns:xp="http://www.ibm.com/xsp/core">
<xp:button value="Fetch CSV SQL Data" id="button2">
<xp:eventHandler event="onclick" submit="true"
refreshMode="complete" immediate="false" save="true" id="eventHandler2">
<xp:this.action><![CDATA[#{javascript:ReadCSVFileBean.doGetCSVConsoleData()}]]></xp:this.action>
</xp:eventHandler>
</xp:button>
</xp:view>
Conclusion:
You can now Query CSV files from Java, data that can be used by your application in the making of charts and other types of reports.
TIP: You must reference the csvjdbc jar file in your CLASSPATH, and connect it to your project via build path. Note: as a JavaAgent, you do not need to make the file available in your CLASSPATH. Be certain to add this file to your /lib/ext folder within Notes. You will of course need to reference ReadCSVFile.java in your faces-config.xml file.
References:
Questions, comments, please post a brief message on our Contact form on the main site. Thank you for coming...