Salesforce.com Data Download via Java Swing, RSSBus JDBC Driver, and Apache Commons Daemon

I recently developed an application that synchronizes Salesforce.com data to a local PC. The application is developed in Java using the Swing graphical framework and uses a Salesforce.com JDBC driver provided by RSSBus. The application stores Salesforce.com credentials and synchronization interval timing data in a Java properties file. The application allows non-graphical execution via the “nogui” command argument. I also include an Apache Commons Daemon class and script that allows running the application as a service. The follow code is licensed under the GPLv3.

Here’s the main application class. This class can be executed via a command prompt by “java -jar myfile.jar nogui”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
package com.zydecodigital.emb;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.BufferedReader;
import java.io.FileReader;
import java.io.OutputStreamWriter;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPasswordField;
import javax.swing.JTextField;
import javax.swing.SwingUtilities;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * Sync Salesforce data to local folder.
 * @author rtoepfer
 *
 */
public class SalesforceSync implements Runnable {

  // If true do not create GUI
  public boolean bCommandLine = false;  

  String m_strHomeDirectory;
  String m_strZDDirectory;
  String m_strLogFile;
  String m_strPropertiesFile;
  String m_strOS;
  String m_strSyncInterval;

  // Create the window.
  JFrame frame = new JFrame();

  // Declare components
  JButton btnLog = new JButton("Open Log File");
  JButton btnFile = new JButton("Destination Folder");
  JFileChooser fileChooser = new JFileChooser();
  JLabel lblSync = new JLabel("Sync Time (24 Hour):");
  JTextField tfSyncInterval = new JTextField("24:00");
  JButton btnSaveSyncInterval = new JButton("Save");
  JButton btnSync = new JButton("Synchronize Local Data");
  JLabel lblCredentials = new JLabel("Salesforce Credentials (User/Pass/Token):");
  JTextField tfUser = new JTextField();
  JPasswordField tfPass = new JPasswordField();
  JPasswordField tfToken = new JPasswordField();

  // Salesforce JDBC connection
  Connection conn = null;
  Properties conn_prop = null;

  public SalesforceSync(boolean bCommandLine) {

    this.bCommandLine = bCommandLine;
    this.m_strOS = System.getProperty("os.name");

    this.setFiles("");

    // RSSBus JDBC connection properties
    this.conn_prop = new Properties();

    this.readProperties();

    // DEBUG properties
    // this.conn_prop.setProperty("Logfile", this.m_strDirectory + "/JDBC_Debug.txt");
    // this.conn_prop.setProperty("Verbosity", "1");
    // this.conn_prop.setProperty("RecordToFile", this.m_strDirectory + "/JDBC_Debug_Socket.txt");
  }

  /**
   * Invokes Java Swing event dispatch thread
   * @param args
   */
  public static void main(String[] args) {
    SalesforceSync se = null;
    if (args.length > 0) {
      if (args[0].contains("nogui")) {
        se = new SalesforceSync(true);
      } else {
        se = new SalesforceSync(false);
      }
    } else {
      se = new SalesforceSync(false);
    }

    SwingUtilities.invokeLater(se);
  }

  void readProperties() {
    // Load properties file
    File fileProp = new File(this.m_strPropertiesFile);
    try {
      this.conn_prop.load(new FileInputStream(fileProp));

      this.m_strZDDirectory = this.conn_prop.getProperty("ZD-Directory", this.m_strZDDirectory);
      this.m_strSyncInterval = this.conn_prop.getProperty("Sync Interval", "24:00");

    } catch (Exception e) {
    }
  }

  void setFiles(String strZDDirectory) {

    // Set directory structure

    // Home folder
    String strHome = System.getProperty("user.home");
    this.m_strHomeDirectory = strHome + File.separator + "ZydecoDigital";
    this.m_strLogFile = this.m_strHomeDirectory + File.separator + "log.txt";
    this.m_strPropertiesFile = this.m_strHomeDirectory + File.separator + "prop.txt";

    // ZD save folder
    if (strZDDirectory.isEmpty()) {
      this.m_strZDDirectory = this.m_strHomeDirectory;
    } else {
      this.m_strZDDirectory = strZDDirectory;
    }
  }

  void saveProperties() {

    try {
      // get user name, password, token, sync interval, directory
      SalesforceSync.this.conn_prop.setProperty("User", SalesforceSync.this.tfUser.getText());
      SalesforceSync.this.conn_prop.setProperty("Password", String.copyValueOf(SalesforceSync.this.tfPass.getPassword()));
      SalesforceSync.this.conn_prop.setProperty("Access Token", String.copyValueOf(SalesforceSync.this.tfToken.getPassword()));
      SalesforceSync.this.conn_prop.setProperty("Sync Interval", SalesforceSync.this.tfSyncInterval.getText());
      SalesforceSync.this.conn_prop.setProperty("ZD-Directory", SalesforceSync.this.m_strZDDirectory);

      // save properties to text file
      File fileProp = new File(SalesforceSync.this.m_strPropertiesFile);
      if (!fileProp.exists()) {
        // test whether the directory exists
        File zdDirectory = new File(SalesforceSync.this.m_strZDDirectory);
        if (!zdDirectory.exists())
          zdDirectory.mkdirs();
        fileProp.createNewFile();
      }
      OutputStream out = new FileOutputStream(fileProp);
      SalesforceSync.this.conn_prop.store(out, "Saved on " + DateFormat.getDateInstance().format(new Date()));
    } catch (Exception e1) {
      JOptionPane.showMessageDialog(SalesforceSync.this.frame, e1.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
    }
  }

  /**
   * Connect to Salesforce via RSSBus JDBC driver and download all opportunity data
   */
  synchronized void syncToSalesforce() {
    try {
      // save dialog fields to properties file
      if (this.bCommandLine)
        this.readProperties(); // re-read properties in case this call is from periodic scheduled task
      else
        this.saveProperties();

      // read last entry date in log file (latest update is pre-pended to file)
      BufferedReader bufferLogFile = null;
      String strLine = "";

      // open log file
      File logFile = new File(this.m_strLogFile);
      if (logFile.exists()) {
        bufferLogFile = new BufferedReader(new FileReader(logFile));
        strLine = bufferLogFile.readLine();
        if (strLine == null)
          strLine = "";
      }

      // parse date from first line
      // Ex: '[2000-01-01] Synced...'
      String strLastSyncDate = null;
      if (!strLine.isEmpty()) {
        strLastSyncDate = strLine.substring(1, 11);
        if (strLastSyncDate.length() != 10)
          strLastSyncDate = null;
      }

      // create temp log file
      File fileTemp = File.createTempFile("zydeco", "tmp");
      fileTemp.deleteOnExit();
      BufferedWriter writerTemp = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(fileTemp)));

      // connect to Salesforce
      if (this.conn == null) {
        this.conn = DriverManager.getConnection("jdbc:salesforce", this.conn_prop);
      }

      // pull all opportunities on and after log date
      Statement dbStatement = this.conn.createStatement();
      String strSql;
      ResultSet results = null;
      if (strLastSyncDate != null)
        strSql = "SELECT * FROM Opportunity WHERE CloseDate >= '" + strLastSyncDate + "' AND (StageName = 'Closed Won' OR StageName = 'Closed Lost')";
      else
        strSql = "SELECT * FROM Opportunity WHERE StageName = 'Closed Won' OR StageName = 'Closed Lost'";

      boolean status = dbStatement.execute(strSql);
      if (status) {

        results = dbStatement.getResultSet();
       
        // store results in arraylist
        filltable data = new filltable(this.conn, results);

        // get indices of columns
        int iOpportunityIdIx = data.getColumnIndex("Id");
        int iEmbProjectNameIx = data.getColumnIndex("EMB_Project_Name__c");
        int iEmbProjectId = data.getColumnIndex("Assigned_EMB_Project_Number__c");
        int iEmbProposalId = data.getColumnIndex("Assigned_EMB_Proposal_Number__c");

        // save name of each opportunity synced to Log file
        String strOpportunitiesSaved = "";

        // folder name will have year as prefix
        Date date = new Date();
        SimpleDateFormat folderDateFormat = new SimpleDateFormat("y");
        String strFolderDatePrefix = folderDateFormat.format(date);

        // Create folders if they do not exist, save opportunity and proposal data
        int i, j;
        for (i = 0; i < data.getRowCount(); i++) {

          String strOpportunityId;
          String strEMB_ProjectName;
          String strEMB_ProjectId;
          String strEMB_ProposalId;

          strOpportunityId = data.getValueAt(i, iOpportunityIdIx).toString();
          strEMB_ProjectName = (String) data.getValueAt(i, iEmbProjectNameIx).toString();
          strEMB_ProjectId = (String) data.getValueAt(i, iEmbProjectId).toString();
          strEMB_ProposalId = (String) data.getValueAt(i, iEmbProposalId).toString();

          // if no proposal id do not continue
          // NOTE: for Salesforce developer account dummy data will not have this field set; however, in production
          //       this field is required for all accounts
          if (strEMB_ProposalId.isEmpty())
            continue;

          // create folder name
          String strFolderName = strFolderDatePrefix + "-" + strEMB_ProjectId + "-" + strEMB_ProjectName;
          String strFolderPath = SalesforceSync.this.m_strZDDirectory + File.separator + strFolderName;

          // create folder if it doesn't exist
          File folder = new File(strFolderPath);
          if (!folder.exists())
            folder.mkdirs();

          // create CSV text file of opportunity data
          String strCsvFilePath = strFolderPath + File.separator + "Data.csv";
          File opportunityCSV = new File(strCsvFilePath);
          data.storeAsCSV(i, opportunityCSV);

          // save percent construction line items
          String strPercentConstructionSql = "SELECT * FROM Opportunity_Percent_Construction_Item__c WHERE Opportunity__c = '" + strOpportunityId + "'";
          Statement dbPercentConstructionStatement = this.conn.createStatement();
          boolean pcStatus = dbPercentConstructionStatement.execute(strPercentConstructionSql);
          if (pcStatus) {
            ResultSet pcResults = dbPercentConstructionStatement.getResultSet();
            filltable pcData = new filltable(this.conn, pcResults);
            if (pcData.getRowCount() > 0) {
              // save to CSV file
              String strPCCsvFilePath = strFolderPath + File.separator + "LineItemsData.csv";
              File opportunityPCCSV = new File(strPCCsvFilePath);
              pcData.storeAsCSV(i, opportunityPCCSV);
            }
          }

          // save all proposals
          CallableStatement cs = null;
          cs = this.conn.prepareCall("DownloadAttachment");

          // IN params
          cs.setString("ObjectId", strOpportunityId);

          // due to a bug in RSSBus JDBC driver not using File.separator for file paths we
          // need to add slash for POSIX based systems
          if (!this.m_strOS.contains("Windows"))
            cs.setString("LocalPath", strFolderPath + "/");
          else
            cs.setString("LocalPath", strFolderPath);
          cs.execute();

          // remove backslash at beginning of filename on Linux/Apple machines
          if (!this.m_strOS.contains("Windows")) {
            File[] arrFiles = folder.listFiles();
            for (j = 0; j < arrFiles.length; j++) {
              String strFileName = arrFiles[j].getName();
              if (strFileName.startsWith("\\")) {
                String strNewFileName = strFileName.substring(1);
                File fileNew = new File(strFolderPath + File.separator + strNewFileName);
                arrFiles[j].renameTo(fileNew);
              }
            }
          }

          // log opportunity saves
          if (i < data.getRowCount() - 1)
            strOpportunitiesSaved += "'" + strFolderName + "', ";
          else
            strOpportunitiesSaved += "'" + strFolderName + "'";

        } // end for each opportunities loop

        // update log file
        SimpleDateFormat dateLogFormat = new SimpleDateFormat("yyyy-MM-dd");
        String strLogMsg = "[" + dateLogFormat.format(date) + "]" + " Synced Opportunities: " + strOpportunitiesSaved + "\n";
        writerTemp.write(strLogMsg);

        // read our log file into our temp file (already read first line above)
        if (!strLine.isEmpty()) {
          do {
            if (strLine != null)
              strLine += "\n";
            writerTemp.write(strLine);
            strLine = bufferLogFile.readLine();
          } while(strLine != null);
        }
        writerTemp.flush();

        // rename temp file to log file
        if(bufferLogFile != null)
          bufferLogFile.close();
        if(logFile.exists())
          logFile.delete();
        fileTemp.renameTo(new File(this.m_strLogFile));

      } // end if result status
    } catch (Exception e1) {
      JOptionPane.showMessageDialog(this.frame, e1.getMessage(), "Error", JOptionPane.ERROR_MESSAGE);
    }
  }

  /**
   * Open the Log file in text editor
   */
  class ButtonLogListener implements ActionListener {
    public void actionPerformed(ActionEvent e) {
      File f = new File(SalesforceSync.this.m_strLogFile);
      if (f.exists()) {
        String strCmd;
        // open text file in gedit/Notepad
        if (SalesforceSync.this.m_strOS.contains("Windows")) {
          strCmd = "notepad.exe " + SalesforceSync.this.m_strLogFile;
        } else if (SalesforceSync.this.m_strOS.contains("Mac")) {
          strCmd = "open -t " + SalesforceSync.this.m_strLogFile;
        } else {
          strCmd = "gedit " + SalesforceSync.this.m_strLogFile;
        }
        try {
          @SuppressWarnings("unused")
          Process tr = Runtime.getRuntime().exec(strCmd);
        } catch (IOException e1) {
          JOptionPane.showMessageDialog(SalesforceSync.this.frame, "Could not open log file", "Error", JOptionPane.ERROR_MESSAGE);
        }
      } else {
        JOptionPane.showMessageDialog(SalesforceSync.this.frame, "Log file does not exist", "Error", JOptionPane.ERROR_MESSAGE);
      }
    }
  }

  /**
   * Select a file to store synced data to
   * Note: application will only download data based on last log file entry not based
   *       on data that exists in new directory
   */
  class ButtonFileListener implements ActionListener {
    public void actionPerformed(ActionEvent e) {

      // setup dialog
      SalesforceSync.this.fileChooser.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
      File zdDir = new File(SalesforceSync.this.m_strZDDirectory);
      SalesforceSync.this.fileChooser.setCurrentDirectory(zdDir);

      // open dialog
      int nReturnVal = SalesforceSync.this.fileChooser.showOpenDialog(null);
      if (nReturnVal == JFileChooser.APPROVE_OPTION) {

        File dir = SalesforceSync.this.fileChooser.getSelectedFile();
        SalesforceSync.this.m_strZDDirectory = dir.getAbsolutePath();

        SalesforceSync.this.saveProperties();
      }
    }
  }

  /**
   * Save sync interval change to properties file
   */
  class ButtonSaveListener implements ActionListener {
    public void actionPerformed(ActionEvent e) {
      SalesforceSync.this.saveProperties();
    }
  }

  /**
   * Download Salesforce data
   */
  class ButtonSyncListener implements ActionListener {
    public void actionPerformed(ActionEvent e) {
      // set sync button text
      SalesforceSync.this.btnSync.setEnabled(false);
      SalesforceSync.this.btnSync.setText("Synchronizing...Please Wait");

      // sync Salesforce data
      SalesforceSync.this.syncToSalesforce();

      // enable button
      SalesforceSync.this.btnSync.setEnabled(true);
      SalesforceSync.this.btnSync.setText("Synchronize Local Data");
    }
  }

  @Override
  public void run() {

    // no GUI
    if (this.bCommandLine) {
      // sync Salesforce data
      this.syncToSalesforce();
    }
   
    // show GUI
    else {
      // Use absolute positioning
      frame.setLayout(null);

      // Size components
      btnLog.setBounds(25, 25, 140, 25);
      btnFile.setBounds(180, 25, 180, 25);
      lblSync.setBounds(25, 75, 200, lblSync.getPreferredSize().height);
      tfSyncInterval.setBounds(200, 70, 50, tfSyncInterval.getPreferredSize().height + 10);
      btnSaveSyncInterval.setBounds(290, 70, 70, 25);
      btnSync.setBounds(25, 120, 300, 25);
      lblCredentials.setBounds(25, 175, 300, lblSync.getPreferredSize().height);
      tfUser.setBounds(25, 200, 250, tfUser.getPreferredSize().height + 10);
      tfPass.setBounds(25, 245, 250, tfPass.getPreferredSize().height + 10);
      tfToken.setBounds(25, 290, 250, tfToken.getPreferredSize().height + 10);

      // Add listeners
      ButtonLogListener logListener = new ButtonLogListener();
      btnLog.addActionListener(logListener);
      ButtonFileListener fileChooseListener = new ButtonFileListener();
      btnFile.addActionListener(fileChooseListener);
      ButtonSaveListener saveListener = new ButtonSaveListener();
      btnSaveSyncInterval.addActionListener(saveListener);
      ButtonSyncListener syncListener = new ButtonSyncListener();
      btnSync.addActionListener(syncListener);

      // Get saved values
      String strUser = this.conn_prop.getProperty("User", "User Name");
      String strPassword = this.conn_prop.getProperty("Password", "");
      String strAccessToken = this.conn_prop.getProperty("Access Token", "");
      String strSyncInterval = this.conn_prop.getProperty("Sync Interval", "24:00");

      tfUser.setText(strUser);
      tfPass.setText(strPassword);
      tfToken.setText(strAccessToken);
      tfSyncInterval.setText(strSyncInterval);

      // Add components to frame
      frame.add(btnLog);
      frame.add(btnFile);
      frame.add(lblSync);
      frame.add(tfSyncInterval);
      frame.add(btnSaveSyncInterval);
      frame.add(btnSync);
      frame.add(lblCredentials);
      frame.add(tfUser);
      frame.add(tfPass);
      frame.add(tfToken);

      // Sets the behavior for when the window is closed.
      frame.setTitle("EMB Salesforce Sync Utility");
      frame.setSize(400, 400);
      frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      frame.setLocationRelativeTo(null); // centers the window

      // Display frame
      frame.setVisible(true);
    }
  }

}

The following class was copied from the RSSBus JDBC demo folder. The class basically takes a java.sql.ResultSet object and copies the data to an ArrayList. Several functions were added to the class by Zydeco.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
package com.zydecodigital.emb;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.swing.table.AbstractTableModel;

/**
 * This class has been copied from the RSSBus Salesforce JDBC driver demo folder.
 * @author rtoepfer
 *
 */
@SuppressWarnings("serial")
public class filltable extends AbstractTableModel {
  private ResultSet rs;
  private int rowCount;
  private int columnCount;
  @SuppressWarnings("rawtypes")
  private ArrayList data=new ArrayList();  
  @SuppressWarnings({ "unused", "rawtypes" })
  private List updateRows = new ArrayList();
  @SuppressWarnings({ "unused", "rawtypes" })
  private List insertRows = new ArrayList();  
  @SuppressWarnings("unused")
  private Connection conn=null;

  /**
   * Constructor
   * @param conn
   * @param _rs
   * @throws Exception
   */
  public filltable(Connection conn, ResultSet _rs) throws Exception
  {
    this.conn = conn;
    setRS(_rs);
  }

  /**
   * Set result set to internal result set.  Store each value in an array as default
   * result set does not allow cursor manipulation.
   *
   * @param _rs
   * @throws Exception
   */
  @SuppressWarnings("unchecked")
  public void setRS(ResultSet _rs)  throws Exception
  {
    this.rs=_rs;
    ResultSetMetaData metaData=_rs.getMetaData();
    rowCount=0;
    columnCount=metaData.getColumnCount();
    while(_rs.next()){
      Object[] row=new Object[columnCount];
      for(int j=0;j<columnCount;j++){
        row[j]=_rs.getObject(j+1);
      }
      data.add(row);
      rowCount++;
    }
  }

  public int getColumnCount(){
    return columnCount;
  }

  public int getRowCount(){
    return rowCount;
  }

  /**
   * Returns value from ArrayList data.  Uses 0 based indices.
   */
  public Object getValueAt(int rowIndex, int columnIndex){
    Object[] row=(Object[]) data.get(rowIndex);
    Object datum = row[columnIndex];
    if(datum == null)
        datum = new String("");
    return datum;
  }

  /**
   * Return the column name for a column index that uses 0 based indices.
   * Note ResultSetMetaData class uses 1 based indices.
   */
  public String getColumnName(int columnIndex) {
    try{
      ResultSetMetaData metaData=rs.getMetaData();
      return metaData.getColumnName(columnIndex+1);
    }catch(Exception e){
      e.printStackTrace();
      return null;
    }
  }
 
  /**
   * Get 0 based column index for a given column name.
   * @param strDesiredColumnName
   * @return
   */
  public int getColumnIndex(String strDesiredColumnName) {
    int columnIx = -1;
    ResultSetMetaData metaData;
    int columnCount = this.getColumnCount();
    int i;
    String strColumnName;
   
    try {
        metaData=rs.getMetaData();
      for(i=1; i<=columnCount;i++) {
        strColumnName = metaData.getColumnName(i);
       
        if(strDesiredColumnName.equals(strColumnName)) {
            columnIx = i-1;
            break;
        }
      }
    } catch(Exception e){
      e.printStackTrace();
    }  
   
    return columnIx;
  }

  /**
   * Store a row as a CSV file, uses 0 based indices.
   * @param row Row to store; if -1 store all rows.
   * @param f File to save CSV to.
   * @throws IOException
   * @throws SQLException
   */
    public void storeAsCSV(int row, File f) throws IOException,
            SQLException {
        FileOutputStream fos;
        Writer out = new OutputStreamWriter(new BufferedOutputStream(fos = new FileOutputStream(f, false)));
        int i, j;
        int nColumnCount = this.getColumnCount();
        int nRowCount = this.getRowCount();
       
        // write columns
        for (i=0; i<nColumnCount; i++) {
            out.append(CSVQuote(this.getColumnName(i)));
            if (i < nColumnCount - 1)
                out.append(",");
        }
        out.append("\r\n");
       
        // write data
        int startRow = 0;
        int endRow = nRowCount;
       
        if(row >= 0 && row < this.getRowCount()) {
            startRow = row;
            endRow = row + 1;
        }

        for (i=startRow; i<endRow; i++) {
            for (j=0; j<nColumnCount; j++) {
                out.append(CSVQuote(this.getValueAt(i, j).toString()));
                if (i < nColumnCount - 1)
                    out.append(",");
            }
            out.append("\r\n");
        }
        out.append("\r\n");
       
        // close file
        out.flush();
        fos.close();
    }

    private CharSequence CSVQuote(String value) {
        return "\"" + value + "\"";
    }

}

Here’s the Apache Commons Daemon class and associated Linux shell script to interact with the service. These files have not been fully tested.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package com.zydecodigital.emb;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.concurrent.Executors;
import java.util.concurrent.ScheduledExecutorService;
import java.util.concurrent.ScheduledFuture;
import java.util.concurrent.TimeUnit;

import org.apache.commons.daemon.*;

public class Launcher implements Daemon {

  SwingGUI syncSalesforce = null;
  ScheduledExecutorService scheduler = null;
  ScheduledFuture<?> schedulerHandle = null;
   
  @Override
  public void destroy() {    
  }

  @Override
  public void init(DaemonContext arg0) throws DaemonInitException, Exception {      
    // sync salesforce data
    syncSalesforce = new SwingGUI(true);
    scheduler = Executors.newScheduledThreadPool(1);
  }

  @Override
  public void start() throws Exception {
   
    if(schedulerHandle == null) {
      Calendar now = Calendar.getInstance();
     
      int year = now.get(Calendar.YEAR);
      int month = now.get(Calendar.MONTH);
      int day = now.get(Calendar.DAY_OF_MONTH);
      //int hour = now.get(Calendar.HOUR_OF_DAY);  // 24 hour format
      //int minute = now.get(Calendar.MINUTE);
      //int seconds = now.get(Calendar.SECOND);
     
      Calendar future = Calendar.getInstance();
      future.set(year, month, day, 24, 0);
     
      long delay = future.getTimeInMillis() - now.getTimeInMillis();
     
      schedulerHandle = scheduler.scheduleAtFixedRate(syncSalesforce, delay, 24*60*60*1000, TimeUnit.MILLISECONDS);
    }
  }

  @Override
  public void stop() throws Exception {
    if(schedulerHandle != null) {
      scheduler.schedule(new Runnable() {
        public void run() {
          schedulerHandle.cancel(true);
        }
      }, 0, TimeUnit.SECONDS);
    }
  }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
#!/bin/sh

# Setup variables
EXEC=/usr/bin/jsvc
JAVA_HOME=$(readlink -f /usr/bin/javac | sed "s:/bin/javac::")
CLASS_PATH="/home/rtoepfer/workspace/EMB-Salesforce-Service/lib/commons-daemon-1.0.15.jar":"/home/rtoepfer/workspace/EMB-Salesforce-Service/lib/RSSBus JDBC Driver for Salesforce V3/lib/rssbus.jdbc.salesforce.jar"
CLASS=Launcher
USER=rtoepfer
PID=/tmp/ZydecoDigital-EMB-Salesforce.pid
LOG_OUT=/tmp/ZydecoDigital-EMB-Salesforce.out
LOG_ERR=/tmp/ZydecoDigital-EMB-Salesforce.err

do_exec()
{
    $EXEC -home "$JAVA_HOME" -cp $CLASS_PATH -user $USER -outfile $LOG_OUT -errfile $LOG_ERR -pidfile $PID $1 $CLASS
}

case "$1" in
    start)
        do_exec
            ;;
    stop)
        do_exec "-stop"
            ;;
    restart)
        if [ -f "$PID" ]; then
            do_exec "-stop"
            do_exec
        else
            echo "service not running, will do nothing"
            exit 1
        fi
            ;;
    *)
            echo "usage: daemon {start|stop|restart}" >&2
            exit 3
            ;;
esac

One thought on “Salesforce.com Data Download via Java Swing, RSSBus JDBC Driver, and Apache Commons Daemon

  1. Pingback: Zydeco Digital LLC

Leave a Reply

Your email address will not be published. Required fields are marked *