Examples of QueryRunner

   * @throws ControllerException
   */
  public int insertRecord(String tableName, String primaryKeyName, Map columnMap)  
  throws ControllerException  {


    QueryRunner queryRunner = new QueryRunner();
    String sql = null;
    String columnName = null;
    int i;


    try {
      // Get primary key in case primary key is null
      if (columnMap.get(primaryKeyName) == null) {
        //int uniqueKey = nz.co.transparent.client.db.SQL.getUniqueKey(conn, tableName, primaryKeyName);
        int uniqueKey = nz.co.transparent.client.db.SQL.getUniqueKey(tableName, primaryKeyName);
        columnMap.put(primaryKeyName, new Integer(uniqueKey));
      }
      
      sql = "insert into " + tableName;
      String parm = null;
      Set columnSet = columnMap.keySet();
      Iterator iterator = columnSet.iterator();
      Object[] params = new Object[columnSet.size()];
      i = 0;
      int j = 0;


      // Use prepared statement to avoid escaping special character
      while (iterator.hasNext()) {
        columnName = (String) iterator.next();
        // CURRENT_TIMESTAMP must be set directly into SQL to force date created by server
        // Alternatively these columns can be left out of the map
        if (columnName.equals("date_created")) {
          parm = "CURRENT_TIMESTAMP";
        } else if (columnName.equals("date_updated")) {
            parm = "CURRENT_TIMESTAMP";
        } else {
          parm = "?";
          params[i++] = columnMap.get(columnName);
        }


        if (j++ == 0) {
          sql += " set " + columnName + "=" + parm;
        } else {
          sql += " ," + columnName + "=" + parm;
        }
      }
      
      try {
        return queryRunner.update(conn, sql, params);
      } catch (SQLException se) {
        throw new ControllerException(se);
      }
    } catch (SQLException se) {
      log.warning("GenericController SQLException: " + se.getMessage());

Examples of org.apache.commons.dbutils.QueryRunner

   * @throws ControllerException
   */
  public int deleteRecord(String tableName, String whereClause)  
  throws ControllerException  {


    QueryRunner queryRunner = new QueryRunner();
    String sql = null;


    try {
      sql = "delete from " + tableName;
      
      if (whereClause != null) {
        sql += " where " + whereClause;
      }
      
      return queryRunner.update(conn, sql);
    } catch (SQLException se) {
      log.warning("GenericController SQLException: " + se.getMessage());
      throw new ControllerException(se);
    }
  }

Examples of org.apache.commons.dbutils.QueryRunner

   * @throws UpdaterException Thrown if concurrent change has happened
   */
  public int updateRecord(String tableName, String primaryKeyName, Map columnMap)  
    throws ControllerException, UpdaterException  {
    
    QueryRunner queryRunner = new QueryRunner();
    ResultSetHandler rsh = new MapHandler();
    String sql = null;


    try {
      sql = "select * from " + tableName;
      sql += " where (" + primaryKeyName + "=?)";
      Map columnMapTemp = (Map) queryRunner.query(conn, sql, columnMap.get(primaryKeyName), rsh);


      if (columnMapTemp == null) {
        throw new ControllerException("Cannot find record.");
      }
      
      Date oldDate = (java.util.Date) columnMap.get("date_updated"); 
      Date newDate = (java.util.Date) columnMapTemp.get("date_updated"); 
      if (!oldDate.equals(newDate)) {
        throw new UpdaterException();  // Signal that record has already been changed
      }
      
      sql = "update " + tableName;
      String parameter = null;
      String columnName = null;
      List columnNameList = null;
      List paramList = new ArrayList();
      // Iterate over columns
      Set columnSet = columnMapTemp.keySet();
      Iterator iterator = columnSet.iterator();
      int i = 0;
      
      while (iterator.hasNext()) {
        columnName = (String) iterator.next();
        
        if (columnName.equals("date_updated")) {
          parameter = "CURRENT_TIMESTAMP";
        } else {
          parameter = "?";
          paramList.add(columnMap.get(columnName));
        }
        
        if (i++ == 0) {
        sql += " set " + columnName + " = " + parameter; 
        } else {
          sql += " ," + columnName + " = " + parameter;
        }
      }
      
      sql += " where (" + primaryKeyName + "=?)";
      paramList.add(columnMap.get(primaryKeyName));  // Add primaryKey value to paramList
      return queryRunner.update(conn, sql, paramList.toArray());
    } catch (SQLException se) {
      log.warning("SQL Exception: " + se.getMessage());
      throw new ControllerException(se);
    }
  }

Examples of org.apache.commons.dbutils.QueryRunner

  public List findContactDetails(int clientID) 
    throws ControllerException  {
    
    List resultList = new ArrayList();
    Map titleMap;
    QueryRunner queryRunner = new QueryRunner(dataSource);
    ResultSetHandler rsh = new MapListHandler();
    String sql = null;


    try {
      sql = "select * from contact_detail, contact_type";
      sql += " where (";
      sql += " (contact_detail.contact_type_id=contact_type.contact_type_id)";
      sql += " and (client_id = ?)";
      sql += " ) ORDER BY contact_type";


      return (List) queryRunner.query(sql, new Integer(clientID), rsh);
    } catch (SQLException se) {
      String message = "SpecificController: SQL Exception: " + se.getMessage();
      log.warning(message);
      throw new ControllerException(se);  // wrap SQLException
    }

Examples of org.apache.commons.dbutils.QueryRunner

   */
  public Map findDefaultContactDetails(int clientID) 
    throws ControllerException  {
    
      DataSource dataSource = DataSourceHandler.getDataSource();
      QueryRunner queryRunner = new QueryRunner(dataSource);
      ResultSetHandler rsh = new MapHandler();
      String sql = null;


      try {
        sql = "select * from contact_detail INNER JOIN contact_type ON (contact_detail.contact_type_id=contact_type.contact_type_id)";
        sql += " where (";
        sql += " (contact_detail.client_id=" + clientID + ")";
        sql += " and (contact_type.is_default=true)";
        sql += " )";


        return (Map) queryRunner.query(sql, rsh);
      } catch (SQLException se) {
        String msg = "SQL error:\n" + se.getMessage();
        log.warning(msg);
        throw new ControllerException(msg);
      }

Examples of org.apache.commons.dbutils.QueryRunner

   * @throws ControllerException
   */
  public List findPayments(int invoiceID) 
    throws ControllerException  {
    
    QueryRunner queryRunner = new QueryRunner(dataSource);
    ResultSetHandler rsh = new MapListHandler();
    String sql = null;


    try {
      // Get connection from the connection pool
      sql = "select * from payment, tender";
      sql += " where (";
      sql += " (payment.tender_id=tender.tender_id)";
      sql += " and (invoice_id = ?)";
      sql += " ) ORDER BY payment_date DESC";


      return (List) queryRunner.query(sql, new Integer(invoiceID), rsh);
    } catch (SQLException se) {
      String message = "SpecificController: SQL Exception: " + se.getMessage();
      log.warning(message);
      throw new ControllerException(se);  // wrap SQLException
    }

Examples of org.apache.commons.dbutils.QueryRunner

   * @throws ControllerException
   */
  public List findInvoices(int clientID) 
      throws ControllerException  {
    
    QueryRunner queryRunner = new QueryRunner(dataSource);
    ResultSetHandler mapListRsh = new MapListHandler();
    ResultSetHandler mapRsh = new MapHandler();
    String sql = null;
  
    try {
      // Get connection from the connection pool
      sql = "select * from invoice";
      sql += " where (";
      sql += " (client_id = ?)";
      sql += " ) ORDER BY invoice_date DESC";
  
      List invoiceList = (List) queryRunner.query(sql, new Integer(clientID), mapListRsh);
      Iterator iterator = invoiceList.iterator();
      Map invoiceMap = null;
      Map paymentMap = null;
      BigDecimal balanceDue = null;
      BigDecimal paymentAmount = null;
      String dollarAmount = null;
        
      sql = "select sum(amount)  as sum_amount from payment";
      sql += " where (";
      sql += "  (invoice_id = ?)";
      sql += " )";
        
      while (iterator.hasNext()) {
        invoiceMap = (Map) iterator.next();
        paymentMap = (Map) queryRunner.query(sql, invoiceMap.get("invoice_id"), mapRsh);
        balanceDue = invoiceMap.get("amount") == null ? new BigDecimal((double) 0) : (BigDecimal) invoiceMap.get("amount");
        paymentAmount = paymentMap.get("sum_amount") == null ? new BigDecimal((double) 0) : (BigDecimal) paymentMap.get("sum_amount");
        balanceDue = balanceDue.add(paymentAmount.negate());


        invoiceMap.put("amount_paid", paymentAmount);

Examples of org.apache.commons.dbutils.QueryRunner

  public List findPersonRoles(int personID) 
    throws ControllerException  {
    
    List resultList = new ArrayList();
    Map roleMap;
    QueryRunner queryRunner = new QueryRunner(dataSource);
    ResultSetHandler rsh = new MapListHandler();
    String sql = null;


    try {
      // Get connection from the connection pool
      sql = "select * from person_role, role";
      sql += " where (";
      sql += " (person_role.role_id=role.role_id)";
      sql += " and (person_id = ?)";
      sql += " ) ORDER BY role";


      return (List) queryRunner.query(sql, new Integer(personID), rsh);
    } catch (SQLException se) {
      String message = "SpecificController: SQL Exception: " + se.getMessage();
      log.warning(message);
      throw new ControllerException(se);
    }

Examples of org.apache.commons.dbutils.QueryRunner

    // Set username and password once before getting DataSource
    DataSourceHandler.setUserName(userName);
    DataSourceHandler.setPassword(passWordInPlainText);
    DataSource dataSource = DataSourceHandler.getDataSource();
    
    QueryRunner queryRunner = new QueryRunner(dataSource);
    ResultSetHandler rshMap = new MapHandler();
    ResultSetHandler rshList = new MapListHandler();


    try {
      String sql =
        "select * from person" + " where (" + " (user_name=?)" + " )";
      personMap = (Map) queryRunner.query(sql, userName, rshMap);


      if (personMap == null) {
        // Check if this is the first user: admin
        sql = "select * from person";
        //params[1] = PasswordService.encrypt(passWordInPlainText);
        personMap = (Map) queryRunner.query(sql, rshMap);
        
        // Person could not be found, but there are already persons present
        if (personMap != null) {
          throw new FinderException();
        }
        
        // Add admin user
        personMap = new HashMap();
        int uniqueKey = nz.co.transparent.client.db.SQL.getUniqueKey("person", "person_id");
        personMap.put("person_id", new Integer(uniqueKey));
        personMap.put("user_name" , userName);
        personMap.put("comment" , "Added by system on " + new java.util.Date());
        personMap.put("updater_person_id" , new Integer(uniqueKey));
        GenericController genericController = GenericController.getInstance();
        genericController.insertRecord("person", "person_id", personMap);
        
        Map personRoleMap = new HashMap();
        // Add admin
        personRoleMap.put("person_role_id", null);
        personRoleMap.put("person_id", personMap.get("person_id"));
        personRoleMap.put("role_id",new Integer(1));
        personRoleMap.put("updater_person_id",new Integer(0));
        genericController.insertRecord("person_role", "person_role_id", personRoleMap);


        // Add operator
        personRoleMap.put("person_role_id", null);
        personRoleMap.put("role_id",new Integer(2));
        genericController.insertRecord("person_role", "person_role_id", personRoleMap);
        
        // Grant all premission to person table
        sql = "grant ALL on APP.person to " + userName + " with grant option";
        int result = queryRunner.update(sql);
      }


      sql =
        "select role_id from person_role where person_id = "
          + personMap.get("person_id");
      List personRoleList =
        (List) queryRunner.query(sql, rshList);
      Iterator iterator = personRoleList.iterator();
      Map personRoleMap = null;
      Map roleMap = null;
      Integer roleID = null;
      ArrayList roleList = new ArrayList(personRoleList.size());
      while (iterator.hasNext()) {
        personRoleMap = (Map) iterator.next();
        roleID = (Integer) personRoleMap.get("role_id");
        sql =
          "select role_code from role where role_id = "
            + roleID.intValue();
        roleMap = (Map) queryRunner.query(sql, rshMap);
        roleList.add(roleMap.get("role_code"));
      }


      personMap.put("role_list", roleList);


      // Update login
      sql =
        "update login"
          + " set login_date=CURRENT_TIMESTAMP"
          + " where ("
          + " (person_id=?)"
          + " )";


      Integer personID = (Integer) personMap.get("person_id");
      int result = queryRunner.update(sql, personID);


      if (result == 0) {
        sql =
          "insert into login"
            + " set user_name="
            + SQL.getUniqueKey("login", "user_name")
            + " ,person_id=?";
        result = queryRunner.update(sql, personID);
      }


      return;
    } catch (SQLException se) {
      String message =

Examples of org.apache.commons.dbutils.QueryRunner

   * Logoff Person
   */
  public static void logoffPerson() {


    DataSource dataSource = DataSourceHandler.getDataSource();
    QueryRunner queryRunner = new QueryRunner(dataSource);
    ResultSetHandler rshMap = new MapHandler();


    try {
      String sql =
        "update login"
          + " set logoff_date=CURRENT_TIMESTAMP"
          + " where ("
          + " (person_id=?)"
          + " )";


      Integer personID = (Integer) personMap.get("person_id");
      Object[] params = { personID };
      int result = queryRunner.update(sql, params);
    } catch (SQLException se) {
      String message =
        "LoginController: SQL Exception: " + se.getMessage();
      log.warning(message);
    }