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 top 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:

http://csvjdbc.sourceforge.net/


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