src/habeas/Utility.java

   1 /*
   2  * To change this license header, choose License Headers in Project Properties.
   3  * To change this template file, choose Tools | Templates
   4  * and open the template in the editor.
   5  */
   6 package habeas;
   7
   8 import java.io.FileNotFoundException;
   9 import java.io.FileOutputStream;
  10 import java.io.FileWriter;
  11 import java.io.IOException;
  12 import java.io.InputStream;
  13 import java.util.ArrayList;
  14 import java.util.prefs.Preferences;
  15 import java.sql.*;
  16 import java.text.DateFormat;
  17 import java.text.MessageFormat;
  18 import java.text.SimpleDateFormat;
  19 import java.util.Arrays;
  20 import java.util.logging.Level;
  21 import java.util.logging.Logger;
  22 import jdk.dynalink.linker.support.Guards;
  23
  24 /**
  25  *
  26  * @author hari
  27  */
  28 public class Utility {
  29
  30     static void saveStationerySettings (String left_header,
  31             String right_header, String signatory) {
  32         leftLetterHeader = left_header;
  33         rightLetterHeader = right_header;
  34         signatoryName = signatory;
  35         Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
  36         myPrefs.put ("LeftHeader", left_header);
  37         myPrefs.put ("RightHeader", right_header);
  38         myPrefs.put ("Signatory", signatory);
  39     }
  40
  41     static void retrieveStationerySettings () {
  42         Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
  43         leftLetterHeader = myPrefs.get("LeftHeader", "Left Header");
  44         rightLetterHeader = myPrefs.get ("RightHeader", "Right Header");
  45         signatoryName = myPrefs.get ("Signatory", "Signatory Name");
  46     }
  47
  48     static void saveConnectionURL(String text) {
  49         connectionURL = text;
  50         Preferences myPrefs = Preferences.userRoot().node ("org/harishankar/Habeas");
  51         myPrefs.put("ConnectionURL", text);
  52     }
  53
  54     static ArrayList<Object> getClientDetails(int r) {
  55         ArrayList<Object> res = new ArrayList <>();
  56         try {
  57             Connection conn = DriverManager.getConnection(JDBC+connectionURL);
  58             PreparedStatement st = conn.prepareStatement("SELECT * FROM clients WHERE id=?;");
  59             st.setInt(1, r);
  60             ResultSet rs = st.executeQuery();
  61             while (rs.next()) {
  62                 res.add (rs.getString("ClientName"));
  63                 res.add(rs.getString("ClientAddress"));
  64                 res.add (rs.getString("ContactPerson"));
  65                 res.add (rs.getString("MailID"));
  66                 res.add (rs.getString("ContactNumber"));
  67             }
  68             conn.close ();
  69             return res;
  70
  71         } catch (SQLException ex) {
  72             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
  73             return null;
  74         }
  75
  76     }
  77
  78     static boolean addClient(String client_name, String client_address,
  79             String contact_person, String email_id, String phone_number) {
  80         if ("".equals(client_name))
  81             return false;
  82         try {
  83             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
  84             PreparedStatement st = conn.prepareStatement("INSERT INTO clients (ClientName,"
  85                     + "ClientAddress, ContactPerson, MailID, ContactNumber) VALUES (?, ?, ?, ?, ?);");
  86             st.setString(1, client_name);
  87             st.setString(2, client_address);
  88             st.setString(3, contact_person);
  89             st.setString (4, email_id);
  90             st.setString (5, phone_number);
  91             st.execute();
  92             conn.close();
  93             return true;
  94
  95         } catch (SQLException ex) {
  96             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
  97             return false;
  98         }
  99
 100
 101     }
 102
 103     static boolean deleteClient(int r) {
 104         try {
 105             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 106             PreparedStatement st = conn.prepareStatement("DELETE FROM clients WHERE id=?;");
 107             PreparedStatement st2 = conn.prepareStatement("DELETE FROM legalnotices WHERE ClientId=?;");
 108             st.setInt(1, r);
 109             st2.setInt (1, r);
 110             st.execute();
 111             st2.execute();
 112             conn.close();
 113             return true;
 114         } catch (SQLException ex) {
 115             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 116             return false;
 117         }
 118
 119     }
 120
 121     static boolean updateClient(int r, String client_name, String client_address,
 122             String contact_person, String mail_id, String phone_number) {
 123         if ("".equals(client_name))
 124                 return false;
 125         try {
 126             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 127             PreparedStatement st = conn.prepareStatement("UPDATE clients SET "
 128                     + "ClientName=?, ClientAddress=?,"
 129                     + "ContactPerson=?, MailID=?, ContactNumber=? WHERE id=?;");
 130             st.setString(1, client_name);
 131             st.setString(2, client_address);
 132             st.setString(3, contact_person);
 133             st.setString(4, mail_id);
 134             st.setString(5, phone_number);
 135             st.setInt (6, r);
 136             st.execute();
 137             conn.close();
 138             return true;
 139         } catch (SQLException ex) {
 140             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 141             return false;
 142         }
 143
 144
 145     }
 146
 147     static boolean addLegalNotice(String reference_number,
 148             String description, java.util.Date entrustment_date, DBItem client) {
 149         if ("".equals(reference_number) || "".equals(description) ||
 150                 entrustment_date == null || client == null)
 151             return false;
 152         try {
 153             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 154             PreparedStatement st = conn.prepareStatement("INSERT INTO legalnotices"
 155                     + " (ReferenceNumber, Description, EntrustmentDate, ClientId) "
 156                     + "VALUES (?, ?, ?, ?);");
 157             st.setString(1, reference_number);
 158             st.setString(2, description);
 159             st.setLong(3, entrustment_date.getTime()/1000);
 160             st.setInt (4, client.getKey());
 161             st.execute();
 162             conn.close();
 163             return true;
 164
 165         } catch (SQLException ex) {
 166             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 167             return false;
 168         }
 169
 170     }
 171
 172     static ArrayList<Object> getNotices() {
 173         ArrayList<Object> notices = new ArrayList<>();
 174         try {
 175             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 176             Statement st = conn.createStatement();
 177             ResultSet rs = st.executeQuery("SELECT id, ReferenceNumber, Description "
 178                     + "FROM legalnotices;");
 179             while (rs.next()) {
 180                 notices.add(rs.getInt("id"));
 181                 notices.add(rs.getString("ReferenceNumber"));
 182                 notices.add(rs.getString("Description"));
 183             }
 184             conn.close();
 185             return notices;
 186         } catch (SQLException ex) {
 187             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 188             return null;
 189         }
 190
 191     }
 192     static java.util.Date getValidDate (ResultSet rs, String datefield) throws SQLException {
 193         // Since resultset.getDate returns a java.sql.Date, we need a way to get
 194         // the date in java.util.Date which is usable in our application. Hence
 195         // this helper function. Since we don't want a valid Date object if the
 196         // field is null, we are using this if clause
 197         if (rs.getDate(datefield) == null)
 198             return null;
 199         else
 200             return (new java.util.Date(rs.getLong(datefield)*1000));
 201     }
 202
 203     static ArrayList<Object> getNoticeDetails(int selid) {
 204         ArrayList<Object> notice = new ArrayList<>();
 205         try {
 206             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 207             PreparedStatement st = conn.prepareStatement("SELECT legalnotices.*, clients.ClientName "
 208                     + "FROM legalnotices "
 209                     + "INNER JOIN clients WHERE ClientId=clients.id AND legalnotices.id=?;");
 210             st.setInt(1, selid);
 211             ResultSet rs = st.executeQuery();
 212             while (rs.next()) {
 213                 notice.add(rs.getString("ReferenceNumber"));
 214                 notice.add(rs.getString("Description"));
 215                 notice.add(getValidDate(rs, "EntrustmentDate"));
 216                 notice.add (rs.getInt ("ClientId"));
 217                 notice.add (rs.getBoolean("DraftCreated"));
 218                 notice.add (rs.getBoolean("DraftApproved"));
 219                 notice.add (rs.getBoolean("NoticeSent"));
 220                 notice.add (getValidDate(rs, "SentDate"));
 221                 notice.add (rs.getString("RPADReference"));
 222                 notice.add (rs.getBoolean("NoticeDelivered"));
 223                 notice.add (getValidDate(rs, "DeliveryDate"));
 224                 notice.add (rs.getString("BillStatus"));
 225                 notice.add (rs.getInt ("BillAmount"));
 226                 notice.add (getValidDate (rs, "BillDate"));
 227                 notice.add (rs.getBoolean ("ClarificationPending"));
 228                 notice.add (rs.getString("ClarificationRemarks"));
 229                 notice.add (rs.getString("ClientName"));
 230             }
 231            return notice;
 232         } catch (SQLException ex) {
 233             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 234             return null;
 235         }
 236
 237
 238     }
 239     private static final String JDBC = "jdbc:sqlite:";
 240
 241     static boolean updateNoticeDraftStatus(int selectednotice_id, boolean created, boolean approved) {
 242         try {
 243             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 244             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 245                     + "SET DraftCreated=?, DraftApproved=? WHERE id=?;");
 246             st.setBoolean(1, created);
 247             st.setBoolean(2, approved);
 248             st.setInt (3, selectednotice_id);
 249             st.execute();
 250             conn.close();
 251             return true;
 252         } catch (SQLException ex) {
 253             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 254             return false;
 255         }
 256
 257     }
 258
 259     static boolean updateNoticeDescription(int selectednotice_id, String text) {
 260         try {
 261             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 262             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices "
 263                     + "SET Description=? WHERE id=?;");
 264             st.setString(1, text);
 265             st.setInt(2, selectednotice_id);
 266             st.execute();
 267             conn.close();
 268             return true;
 269         } catch (SQLException ex) {
 270             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 271             return false;
 272         }
 273
 274     }
 275
 276     static boolean updateNoticeDispatchDetails(int selid, boolean notice_sent,
 277             java.util.Date sent_date, String rpad_reference,
 278             boolean notice_delivered, java.util.Date delivery_date) {
 279         try {
 280             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 281             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 282                     + " SET NoticeSent=?, SentDate=?, RPADReference=?,"
 283                     + " NoticeDelivered=?, DeliveryDate=? WHERE id=?;");
 284
 285             st.setBoolean(1, notice_sent);
 286             st.setDate(2,  toSqlDate(sent_date));
 287             st.setString(3, rpad_reference);
 288             st.setBoolean(4, notice_delivered);
 289             st.setDate(5, toSqlDate(delivery_date));
 290             st.setInt (6, selid);
 291             st.execute();
 292             conn.close();
 293             return true;
 294         } catch (SQLException ex) {
 295             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 296             return false;
 297         }
 298
 299     }
 300
 301     private static Date toSqlDate(java.util.Date date) {
 302             if (date == null)
 303                 return null;
 304             Date sqldate = new Date (date.getTime()/1000);
 305             return sqldate;
 306     }
 307
 308     static boolean updateNoticeBillDetails(int selid, String bill_status,
 309             java.util.Date bill_date, int bill_amount) {
 310         try {
 311             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 312             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 313                     + " SET BillStatus=?,BillDate=?,BillAmount=? WHERE id=?;");
 314             st.setString (1, bill_status);
 315             st.setDate(2, toSqlDate(bill_date));
 316             st.setInt(3, bill_amount);
 317             st.setInt(4, selid);
 318             st.execute();
 319             conn.close();
 320             return true;
 321         } catch (SQLException ex) {
 322             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 323             return false;
 324         }
 325
 326     }
 327
 328     static boolean createDatabase(String db_path) {
 329         saveConnectionURL(db_path);
 330         String tblClients = "CREATE TABLE IF NOT EXISTS \"clients\" (\n" +
 331 "       \"id\"  INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,\n" +
 332 "       \"ClientName\"  VARCHAR(255) NOT NULL UNIQUE,\n" +
 333 "       \"ClientAddress\"       TEXT,\n" +
 334 "       \"ContactPerson\"       TEXT,\n" +
 335 "       \"MailID\"      VARCHAR(255),\n" +
 336 "       \"ContactNumber\"       VARCHAR(30)\n" +
 337 "       );";
 338         String tblNotices = "CREATE TABLE IF NOT EXISTS \"legalnotices\" (\n" +
 339                 "       \"id\"  INTEGER PRIMARY KEY AUTOINCREMENT,\n" +
 340                 "       \"ReferenceNumber\"     TEXT NOT NULL UNIQUE,\n" +
 341                 "       \"Description\" TEXT NOT NULL,\n" +
 342                 "       \"EntrustmentDate\"     INTEGER NOT NULL,\n" +
 343                 "       \"ClientId\"    INTEGER,\n" +
 344                 "       \"DraftCreated\"        INTEGER DEFAULT 0,\n" +
 345                 "       \"DraftApproved\"       INTEGER DEFAULT 0 CHECK(DraftApproved<=DraftCreated),\n" +
 346                 "       \"NoticeSent\"  INTEGER DEFAULT 0 CHECK(NoticeSent<=DraftApproved),\n" +
 347                 "       \"SentDate\"    INTEGER CHECK(SentDate>=EntrustmentDate),\n" +
 348                 "       \"RPADReference\"       TEXT,\n" +
 349                 "       \"NoticeDelivered\"     INTEGER DEFAULT 0 CHECK(NoticeDelivered<=NoticeSent),\n" +
 350                 "       \"DeliveryDate\"        INTEGER CHECK(DeliveryDate>=SentDate),\n" +
 351                 "       \"BillStatus\"  TEXT DEFAULT 'PENDING',\n" +
 352                 "       \"BillAmount\"  INTEGER DEFAULT 1000 CHECK(BillAmount>0),\n" +
 353                 "       \"BillDate\"    INTEGER CHECK(BillDate>=DeliveryDate),\n" +
 354                 "       \"ClarificationPending\"        INTEGER DEFAULT 0,\n" +
 355                 "       \"ClarificationRemarks\"        TEXT\n" +
 356                 ");";
 357
 358         try {
 359             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 360             Statement st1 = conn.createStatement();
 361             st1.execute(tblClients);
 362             st1.execute(tblNotices);
 363             conn.close();
 364             return true;
 365         } catch (SQLException ex) {
 366             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 367             return false;
 368         }
 369
 370     }
 371
 372     static boolean updateNoticeClarificationDetails(int selected_id, boolean
 373             clarification_pending, String clarification_remarks) {
 374         try {
 375             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 376             PreparedStatement st = conn.prepareStatement ("UPDATE legalnotices"
 377                     + " SET ClarificationPending=?, ClarificationRemarks=? "
 378                     + "WHERE id=?;");
 379             st.setBoolean(1, clarification_pending);
 380             st.setString(2, clarification_remarks);
 381             st.setInt (3, selected_id);
 382             st.execute();
 383             conn.close ();
 384             return true;
 385         } catch (SQLException ex) {
 386             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 387             return false;
 388         }
 389
 390     }
 391
 392     static boolean deleteNotice(int r) {
 393         try {
 394             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 395             PreparedStatement st = conn.prepareStatement("DELETE FROM legalnotices"
 396                     + " WHERE id=?;");
 397             st.setInt(1, r);
 398             st.execute();
 399             conn.close();
 400             return true;
 401         } catch (SQLException ex) {
 402             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 403             return false;
 404         }
 405
 406     }
 407
 408     static ArrayList<Object> getRaisedBills (int client_id) {
 409         try {
 410             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 411             PreparedStatement st = conn.prepareStatement("SELECT ReferenceNumber, "
 412                      + "Description, BillDate, BillAmount"
 413                      + " FROM legalnotices WHERE BillStatus='RAISED' AND ClientId=?;");
 414             st.setInt (1, client_id);
 415
 416              ResultSet rs = st.executeQuery ();
 417              ArrayList<Object> bills = new ArrayList<>();
 418              while (rs.next()) {
 419                  bills.add (rs.getString("ReferenceNumber"));
 420                  bills.add (rs.getString("Description"));
 421                  bills.add (getValidDate(rs, "BillDate"));
 422                  bills.add (rs.getInt("BillAmount"));
 423              }
 424              conn.close();
 425              return bills;
 426
 427         } catch (SQLException ex) {
 428             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 429             return null;
 430         }
 431
 432     }
 433
 434     static boolean generateRaisedNoticesBill(String fileName, String clientName,
 435             String clientAddress, String contactPerson, ArrayList<Object> bills) {
 436         if (bills == null)
 437                 return false;
 438         try {
 439             InputStream templ = Utility.class.getClassLoader().getResourceAsStream("resources/noticebill.template.fodt");
 440             if (templ == null)
 441                 return false;
 442             DateFormat fmt = new SimpleDateFormat("dd MMM yyyy");
 443             String templateMain = new String (templ.readAllBytes());
 444             // this is for openoffice ODT - replace normal line breaks with the XML equivalent
 445             String left = leftLetterHeader.replaceAll("\n", "<text:line-break/>");
 446             String right = rightLetterHeader.replaceAll("\n", "<text:line-break/>");
 447             String client_address = clientAddress.replaceAll ("\n", "<text:line-break/>");
 448             String rows = generateBillRows (bills);
 449             System.out.println (rows);
 450
 451             String strMain = MessageFormat.format(templateMain,
 452                     left, right,
 453                     fmt.format(new Date(System.currentTimeMillis())),
 454                     clientName, client_address, contactPerson, signatoryName, rows);
 455
 456             FileOutputStream f = new FileOutputStream (fileName);
 457             f.write (strMain.getBytes());
 458             f.close();
 459             return true;
 460         } catch (IOException ex) {
 461             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 462             return false;
 463         }
 464     }
 465
 466     private static String generateBillRows(ArrayList<Object> bills) {
 467         try {
 468             InputStream tmpl = Utility.class.getClassLoader().getResourceAsStream("resources/tablerow.template.xml");
 469             String rowtpl = new String(tmpl.readAllBytes());
 470
 471             DateFormat fmt = new SimpleDateFormat("dd/MM/yyyy");
 472
 473           StringBuilder bldr = new StringBuilder ();
 474           for (int i = 0; i < bills.size(); i += 4) {
 475               String row = MessageFormat.format (rowtpl, (String)bills.get(i),
 476                       (String)bills.get(i+1), fmt.format((java.util.Date)bills.get(i+2)),
 477                               (int)bills.get(i+3));
 478               bldr.append(row);
 479           }
 480           return (bldr.toString());
 481         } catch (IOException ex) {
 482             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 483             return null;
 484         }
 485     }
 486
 487     static boolean updateNoticeBillStatus(int client_id, String from_status,
 488             String to_status) {
 489         try {
 490             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 491             PreparedStatement st = conn.prepareStatement("UPDATE legalnotices"
 492                     + " SET BillStatus=? WHERE BillStatus=? AND ClientId=?;");
 493             st.setString (1, to_status);
 494             st.setString (2, from_status);
 495             st.setInt (3, client_id);
 496             st.execute();
 497             conn.close();
 498             return true;
 499         } catch (SQLException ex) {
 500             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 501             return false;
 502         }
 503
 504     }
 505
 506     static ArrayList<Object> getPendingPaymentBills() {
 507         ArrayList<Object> data = new ArrayList<>();
 508         try {
 509             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 510             Statement st = conn.createStatement();
 511             ResultSet rs = st.executeQuery("SELECT ReferenceNumber, Description, "
 512                     + "BillDate, BillAmount, ClientName"
 513                     + " FROM legalnotices INNER JOIN clients ON ClientId=clients.id "
 514                     + "WHERE BillStatus='AWAITING PAYMENT';");
 515
 516             DateFormat fmt = new SimpleDateFormat ("dd/MM/yyyy");
 517             while (rs.next()) {
 518                 data.add (rs.getString("ReferenceNumber"));
 519                 data.add (rs.getString("Description"));
 520                 data.add (fmt.format (getValidDate(rs, "BillDate")));
 521                 data.add (rs.getInt ("BillAmount"));
 522                 data.add (rs.getString("ClientName"));
 523             }
 524             conn.close ();
 525             return data;
 526         } catch (SQLException ex) {
 527             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 528             return null;
 529         }
 530
 531     }
 532
 533     static boolean saveReportCSV(String filePath, String[] reportCols, ArrayList<Object> reportData) {
 534         try {
 535             FileWriter f = new FileWriter (filePath);
 536             for (int i = 0; i < reportCols.length; i ++) {
 537                 f.append (escapeQuote(reportCols[i]));
 538                 f.append (", ");
 539             }
 540             f.append("\n");
 541             for (int i = 0; i < reportData.size(); i += reportCols.length) {
 542                 for (int j = 0; j < reportCols.length; j ++) {
 543                     f.append (escapeQuote(reportData.get(i+j)));
 544                     f.append (", ");
 545                 }
 546                 f.append ("\n");
 547             }
 548             f.flush();
 549             f.close();
 550             return true;
 551         } catch (IOException ex) {
 552             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 553             return false;
 554         }
 555
 556     }
 557
 558     private static String escapeQuote(Object bareStr) {
 559         if (bareStr.getClass() == String.class) {
 560             String str = (String)bareStr;
 561             String escapedStr = str.replace ("\"", "\"\"");
 562             System.out.println(escapedStr);
 563             String finalStr = String.format ("\"%s\"", escapedStr);
 564             return finalStr;
 565         }
 566         else
 567             return String.format("\"%s\"", bareStr.toString());
 568     }
 569     public Utility () {
 570
 571     }
 572     public static String connectionURL ;
 573     public static String leftLetterHeader;
 574     public static String rightLetterHeader;
 575     public static String signatoryName;
 576
 577     /**
 578      *
 579      * @return
 580      */
 581     public static ArrayList<Object> getClientsNameAndId () {
 582         ArrayList<Object> data = new ArrayList<>();
 583         try {
 584             Connection conn = DriverManager.getConnection(JDBC + connectionURL);
 585             Statement st = conn.createStatement();
 586             ResultSet rs = st.executeQuery("SELECT id, ClientName from clients;");
 587             while (rs.next()) {
 588                 data.add(rs.getInt("id"));
 589                 data.add(rs.getString("ClientName"));
 590             }
 591             conn.close();
 592             return data;
 593         } catch (SQLException ex) {
 594             Logger.getLogger(Utility.class.getName()).log(Level.SEVERE, null, ex);
 595             return null;
 596         }
 597
 598     }
 599
 600     public static void retrieveConnectionURL () {
 601         Preferences myPrefs = Preferences.userRoot().node("org/harishankar/Habeas");
 602         connectionURL = myPrefs.get("ConnectionURL", "legaldb");
 603     }
 604
 605 }