示例#1
1
  public Vector getUsacFiles(String invoice) {
    Vector fileList = new Vector();
    String query = "";

    query =
        query
            + " SELECT distinct filename, '  Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat ";
    query = query + " FROM stage_usac_form ";
    query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?";
    query = query + " ORDER BY usac_prcs_dat ";

    USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null);

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = cConn.prepareStatement(query);
      pstmt.setString(1, invoice);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        fileList.addElement(rs.getString(1));
        fileList.addElement(rs.getString(2));
      }

      // rs.close();
      // stmt.close();
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();

    } catch (SQLException ex) {
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (Exception e) {
        USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e);
      }
      USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex);
    }

    /*	try
    {
    	if( rs != null)
    		rs.close();
    }
    catch(Exception e)
    {
    	USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
    }
    try
    {
    	if( pstmt != null)
    		pstmt.close();
    }
    catch(Exception e)
    {
    USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
    }*/
    return fileList;
  }
示例#2
1
  private String getMonth(String month) {
    String query;
    String i_month = "";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";

    USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, month);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        i_month = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return i_month;
  }
示例#3
1
  @Override
  public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
    Set<EmpVO> set = new LinkedHashSet<EmpVO>();
    EmpVO empVO = null;

    Connection con = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);
      pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
      pstmt.setInt(1, deptno);
      rs = pstmt.executeQuery();

      while (rs.next()) {
        empVO = new EmpVO();
        empVO.setEmpno(rs.getInt("empno"));
        empVO.setEname(rs.getString("ename"));
        empVO.setJob(rs.getString("job"));
        empVO.setHiredate(rs.getDate("hiredate"));
        empVO.setSal(rs.getDouble("sal"));
        empVO.setComm(rs.getDouble("comm"));
        empVO.setDeptno(rs.getInt("deptno"));
        set.add(empVO); // Store the row in the vector
      }

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      throw new RuntimeException("A database error occured. " + se.getMessage());
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
    return set;
  }
 public StoredTransactionOutput getTransactionOutput(Sha256Hash hash, long index)
     throws BlockStoreException {
   maybeConnect();
   PreparedStatement s = null;
   try {
     s =
         conn.get()
             .prepareStatement(
                 "SELECT height, value, scriptBytes FROM openOutputs "
                     + "WHERE hash = ? AND index = ?");
     s.setBytes(1, hash.getBytes());
     // index is actually an unsigned int
     s.setInt(2, (int) index);
     ResultSet results = s.executeQuery();
     if (!results.next()) {
       return null;
     }
     // Parse it.
     int height = results.getInt(1);
     BigInteger value = new BigInteger(results.getBytes(2));
     // Tell the StoredTransactionOutput that we are a coinbase, as that is encoded in height
     StoredTransactionOutput txout =
         new StoredTransactionOutput(hash, index, value, height, true, results.getBytes(3));
     return txout;
   } catch (SQLException ex) {
     throw new BlockStoreException(ex);
   } finally {
     if (s != null)
       try {
         s.close();
       } catch (SQLException e) {
         throw new BlockStoreException("Failed to close PreparedStatement");
       }
   }
 }
示例#5
0
  /**
   * This method queries the database to get the name of the Billing System.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getBlgsysnm(String blgsys) {
    String query;
    String blgsysnm = "";
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select bs_nm from blg_sys where bs_id = ?";

    USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, blgsys);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        blgsysnm = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
      }
    }

    return blgsysnm;
  }
  /**
   * Method called by the Form panel to delete existing data.
   *
   * @param persistentObject value object to delete
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response deleteRecord(ValueObject persistentObject) throws Exception {
    PreparedStatement stmt = null;
    try {
      EmpVO vo = (EmpVO) persistentObject;

      // delete from WORKING_DAYS...
      stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      stmt.close();

      // delete from EMP...
      stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      gridFrame.reloadData();

      frame.getGrid().clearData();

      return new VOResponse(vo);
    } catch (SQLException ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        stmt.close();
        conn.commit();
      } catch (SQLException ex1) {
      }
    }
  }
示例#7
0
  /**
   * This method queries the database to get the details related to the bp_id passed.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getBpdet(String bpid) {
    String query;
    Vector bpdet = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";

    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, bpid);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        bpdet.addElement(rs.getString(1));
        bpdet.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return bpdet;
  }
示例#8
0
  /**
   * This method queries the database to get the list of the Billing Systems.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getYears(String year) {
    String query;
    Vector years = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";

    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, year);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        years.addElement(rs.getString(1));
        years.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return years;
  }
示例#9
0
 /**
  * ************************************************************************ Lineas de Remesa
  *
  * @param whereClause where clause or null (starting with AND)
  * @return lines
  */
 public MRemesaLine[] getLines(String whereClause, String orderClause) {
   ArrayList list = new ArrayList();
   StringBuffer sql = new StringBuffer("SELECT * FROM C_RemesaLine WHERE C_Remesa_ID=? ");
   if (whereClause != null) sql.append(whereClause);
   if (orderClause != null) sql.append(" ").append(orderClause);
   PreparedStatement pstmt = null;
   try {
     pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
     pstmt.setInt(1, getC_Remesa_ID());
     ResultSet rs = pstmt.executeQuery();
     while (rs.next()) list.add(new MRemesaLine(getCtx(), rs));
     rs.close();
     pstmt.close();
     pstmt = null;
   } catch (Exception e) {
     log.saveError("getLines - " + sql, e);
   } finally {
     try {
       if (pstmt != null) pstmt.close();
     } catch (Exception e) {
     }
     pstmt = null;
   }
   //
   MRemesaLine[] lines = new MRemesaLine[list.size()];
   list.toArray(lines);
   return lines;
 } //	getLines
  private void putUpdateStoredBlock(StoredBlock storedBlock, boolean wasUndoable)
      throws SQLException {
    try {
      PreparedStatement s =
          conn.get()
              .prepareStatement(
                  "INSERT INTO headers(hash, chainWork, height, header, wasUndoable)"
                      + " VALUES(?, ?, ?, ?, ?)");
      // We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
      byte[] hashBytes = new byte[28];
      System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, hashBytes, 0, 28);
      s.setBytes(1, hashBytes);
      s.setBytes(2, storedBlock.getChainWork().toByteArray());
      s.setInt(3, storedBlock.getHeight());
      s.setBytes(4, storedBlock.getHeader().unsafeRimbitSerialize());
      s.setBoolean(5, wasUndoable);
      s.executeUpdate();
      s.close();
    } catch (SQLException e) {
      // It is possible we try to add a duplicate StoredBlock if we upgraded
      // In that case, we just update the entry to mark it wasUndoable
      if (!(e.getSQLState().equals(POSTGRES_DUPLICATE_KEY_ERROR_CODE)) || !wasUndoable) throw e;

      PreparedStatement s =
          conn.get().prepareStatement("UPDATE headers SET wasUndoable=? WHERE hash=?");
      s.setBoolean(1, true);
      // We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
      byte[] hashBytes = new byte[28];
      System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, hashBytes, 0, 28);
      s.setBytes(2, hashBytes);
      s.executeUpdate();
      s.close();
    }
  }
  public void closeConnections() throws SQLException {
    if (itemPreparedStatement != null) {
      itemPreparedStatement.close();
    }

    if (itemConnection != null) {
      itemConnection.close();
    }

    if (holdingsPreparedStatement != null) {
      holdingsPreparedStatement.close();
    }

    if (holdingsConnection != null) {
      holdingsConnection.close();
    }

    if (bibResultSet != null) {
      bibResultSet.close();
    }
    if (bibStatement != null) {
      bibStatement.close();
    }

    if (bibConnection != null) {
      bibConnection.close();
    }
    if (connection != null) {
      connection.close();
    }
  }
示例#12
0
  /**
   * This method queries the database to check if the passed start date for the Journal Month starts
   * exactly after the Previous end date.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
    if (month.length() > ((new Integer("2")).intValue())) {
      month = getMonth(month);
    }

    String query;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
    query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";

    if ((rgn != null) && !(rgn.equals(""))) {
      query = query + " and bp_rgn = ?";
    } else {
      query = query + " and bp_rgn is null";
    }

    query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";

    query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";

    USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, month);
      pstmt.setString(2, month);
      pstmt.setString(3, blgsys);

      int m = 4;
      if ((rgn != null) && !(rgn.equals(""))) {
        pstmt.setString(m, rgn);
        m = m + 1;
      }

      pstmt.setString(m, month);
      pstmt.setString(m + 1, year);
      pstmt.setString(m + 2, year);
      pstmt.setString(m + 3, strtdat);

      rs = pstmt.executeQuery();

      if (rs.next()) {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return false;
  }
示例#13
0
  /**
   * This method queries the database to get the Journal Dates for the passed in Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getJrnldts(String year) {
    String query;
    Vector jrnllst = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query =
        "select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
    query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
    query =
        query
            + "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
    query =
        query
            + "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
    query = query + "from blg_prd,blg_sys ";
    query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
    query = query + "(select strt_dat from fung_yr where yr = ?) and ";
    query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
    query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
    query = query + "order by ord,nm,bp_strt_dat ";

    USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, year);
      pstmt.setString(2, year);
      pstmt.setString(3, year);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        Dinvjrnl jrnldts = new Dinvjrnl(null);

        jrnldts.strBpid = rs.getString(1);
        jrnldts.strBlgsys = rs.getString(3);
        jrnldts.strBlgmnth = rs.getString(5);
        jrnldts.strBlgstrtdt = rs.getString(6);
        jrnldts.strBlgenddt = rs.getString(7);

        jrnllst.addElement(jrnldts);
        jrnldts = null;
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    USFEnv.getLog()
        .writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
    return jrnllst;
  }
示例#14
0
  private static Map saveSites(ExchSite3[] sites, int siteType, int sourceID, Connection c)
      throws SQLException {
    PreparedStatement insert = null;
    PreparedStatement delete = null;
    try {
      insert =
          c.prepareStatement(
              "insert into dat_customer_sites (site_id, site_type, source_id, internal_name, display_name) "
                  + "values (?, ?, ?, ?, ?)");
      delete = c.prepareStatement("delete from dat_customer_sites where site_id = ?");

      Map siteIDs = new HashMap(sites.length * 2 + 1);
      for (int i = 0; i < sites.length; i++) {
        ExchSite3 site = sites[i];

        int siteID = queryLookupSiteId(sourceID, site.getInternalName(), siteType, c);

        if (siteID == 0) {
          // if we couldn't find an existing siteID, grab the next one from the sequence
          siteID = getNextFromSequence("seq_site_id", c);
        } else {
          // if there is an existing siteID, delete it so we can insert the changes
          delete.setInt(1, siteID);
          int deleted = delete.executeUpdate();
          if (deleted != 1) {
            throw new SQLException("Delete for siteID " + siteID + " returned " + deleted);
          }
        }

        siteIDs.put(site.getInternalName(), siteID);
        insert.setInt(1, siteID);
        insert.setInt(2, siteType);
        insert.setInt(3, sourceID);
        insert.setString(
            4, DirectoryUtils.truncateString(site.getInternalName(), DB_SITE_INTERNALNAME_LENGTH));
        insert.setString(
            5, DirectoryUtils.truncateString(site.getDisplayName(), DB_SITE_DISPLAYNAME_LENGTH));
        insert.executeUpdate();
      }
      return siteIDs;
    } finally {
      if (delete != null) {
        delete.close();
      }
      if (insert != null) {
        insert.close();
      }
    }
  }
示例#15
0
  public String getCustomerByCard(String cardId)
      throws ClassNotFoundException, SQLException, Exception {

    String sql = "";
    Connection conn = null;
    PreparedStatement stm = null;
    ResultSet result = null;
    try {
      conn = getConnection();
      try {
        sql =
            " select t1.* from t_cif_customer t1,t_pif_card t2 "
                + " where  t1.cut_id = t2.cosumer_id and t2.card_id="
                + cardId;
        stm = conn.prepareStatement(sql);
        result = stm.executeQuery();
        result.next();
        CustomerDTO temp = new CustomerDTO();
        return result.getString("stuemp_no").toString();
      } finally {
        if (stm != null) {
          stm.close();
        }
      }
    } catch (SQLException e) {
      logger.error("²éѯÊý¾Ý¿âʧ°Ü");
      e.printStackTrace();
      return null;
    } catch (Exception e) {
      logger.error("²éѯÊý¾Ý¿âʧ°Ü");
      e.printStackTrace();
      return null;
    }
  }
示例#16
0
 private static int queryLookupServerId(int sourceID, String serverInternalName, Connection c)
     throws SQLException {
   PreparedStatement idSelect = null;
   ResultSet rs = null;
   try {
     int serverId = 0;
     idSelect =
         c.prepareStatement(
             "select server_id from dat_customer_servers "
                 + "where source_id = ? and internal_name = ?");
     idSelect.setInt(1, sourceID);
     idSelect.setString(2, serverInternalName);
     rs = idSelect.executeQuery();
     if (rs.next()) {
       serverId = rs.getInt(1);
     }
     return serverId;
   } finally {
     if (rs != null) {
       rs.close();
     }
     if (idSelect != null) {
       idSelect.close();
     }
   }
 }
示例#17
0
 public void deleteUser(String username) {
   if (isReadOnly()) {
     // Reject the operation since the provider is read-only
     throw new UnsupportedOperationException();
   }
   Connection con = null;
   PreparedStatement pstmt = null;
   boolean abortTransaction = false;
   try {
     // Delete all of the users's extended properties
     con = DbConnectionManager.getTransactionConnection();
     pstmt = con.prepareStatement(DELETE_USER_PROPS);
     pstmt.setString(1, username);
     pstmt.execute();
     pstmt.close();
     // Delete the actual user entry
     pstmt = con.prepareStatement(DELETE_USER);
     pstmt.setString(1, username);
     pstmt.execute();
   } catch (Exception e) {
     Log.error(e);
     abortTransaction = true;
   } finally {
     DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
   }
 }
示例#18
0
  String getRandomChannelWithVelociraptors(String exclude) {
    Connection con = null;
    String value = "";

    try {
      con = pool.getConnection(timeout);

      PreparedStatement s =
          con.prepareStatement(
              "SELECT `channel` FROM `channels` WHERE `channel` != ? AND `active_velociraptors` > 0 ORDER BY (RAND() * active_velociraptors) DESC LIMIT 1;");
      s.setString(1, exclude);
      s.executeQuery();

      ResultSet rs = s.getResultSet();
      while (rs.next()) {
        value = rs.getString("channel");
      }

      rs.close();
      s.close();
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }

    return value;
  }
示例#19
0
  void update_war(int db_id, long duration, long remaining, long time_to_start, int current_chain) {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      PreparedStatement s =
          con.prepareStatement(
              "UPDATE `wars` SET `duration` = ? ,`remaining` = ?, `time_to_start` = ?, `current_chain` = ? WHERE id = ?");
      s.setLong(1, duration);
      s.setLong(2, remaining);
      s.setLong(3, time_to_start);
      s.setInt(4, current_chain);
      s.setInt(5, db_id);
      s.executeUpdate();
      s.close();
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
  }
示例#20
0
  String getSetting(String key) {
    Connection con = null;
    String value = "";

    try {
      con = pool.getConnection(timeout);

      PreparedStatement s = con.prepareStatement("SELECT `value` FROM `settings` WHERE `key` = ?");
      s.setString(1, key);
      s.executeQuery();

      ResultSet rs = s.getResultSet();
      while (rs.next()) {
        value = rs.getString("value");
      }
      rs.close();
      s.close();
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }

    return value;
  }
示例#21
0
  void joinChannel(Channel channel) {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      PreparedStatement s = con.prepareStatement("INSERT INTO `channels` (`channel`) VALUES (?)");
      s.setString(1, channel.getName().toLowerCase());
      s.executeUpdate();
      s.close();

      if (!this.channel_data.containsKey(channel.getName().toLowerCase())) {
        ChannelInfo new_channel = new ChannelInfo(channel.getName().toLowerCase());
        new_channel.setDefaultOptions();

        this.channel_data.put(channel.getName().toLowerCase(), new_channel);
      }

      this.saveChannelSettings(this.channel_data.get(channel.getName().toLowerCase()));
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
  }
示例#22
0
  public void removeFromChannelGroup(String group, ChannelInfo channel) {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      PreparedStatement s =
          con.prepareStatement(
              "DELETE `channel_groups`.* FROM `channel_groups` INNER JOIN `channels` ON (`channel_groups`.`channel_id` = `channels`.`id`) WHERE `channels`.`channel` = ? AND `channel_groups`.`name` = ?");
      s.setString(1, channel.channel);
      s.setString(2, group);
      s.executeUpdate();
      s.close();

      // Will do nothing if the channel is not in the list.
      this.channel_groups.get(group.toLowerCase()).remove(channel);
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
  }
示例#23
0
  public void addToChannelGroup(String group, ChannelInfo channel) {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      PreparedStatement s =
          con.prepareStatement(
              "REPLACE INTO `channel_groups` SET `name` = ?, `channel_id` = (SELECT `id` FROM `channels` WHERE `channel` = ?)");
      s.setString(1, group.toLowerCase());
      s.setString(2, channel.channel.toLowerCase());
      s.executeUpdate();
      s.close();

      // Will do nothing if the channel is not in the list.
      if (!this.channel_groups.containsKey(group.toLowerCase())) {
        this.channel_groups.put(group.toLowerCase(), new HashSet<>());
      }

      this.channel_groups.get(group.toLowerCase()).add(channel);
    } catch (SQLException ex) {
      Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
      try {
        if (con != null) {
          con.close();
        }
      } catch (SQLException ex) {
        Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
      }
    }
  }
示例#24
0
  @Override
  public void delete(Integer deptno) {
    int updateCount_EMPs = 0;

    Connection con = null;
    PreparedStatement pstmt = null;

    try {

      Class.forName(driver);
      con = DriverManager.getConnection(url, userid, passwd);

      // 1●設定於 pstm.executeUpdate()之前
      con.setAutoCommit(false);

      // 先刪除員工
      pstmt = con.prepareStatement(DELETE_EMPs);
      pstmt.setInt(1, deptno);
      updateCount_EMPs = pstmt.executeUpdate();
      // 再刪除部門
      pstmt = con.prepareStatement(DELETE_DEPT);
      pstmt.setInt(1, deptno);
      pstmt.executeUpdate();

      // 2●設定於 pstm.executeUpdate()之後
      con.commit();
      con.setAutoCommit(true);
      System.out.println("刪除部門編號" + deptno + "時,共有員工" + updateCount_EMPs + "人同時被刪除");

      // Handle any driver errors
    } catch (ClassNotFoundException e) {
      throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
      // Handle any SQL errors
    } catch (SQLException se) {
      if (con != null) {
        try {
          // 3●設定於當有exception發生時之catch區塊內
          con.rollback();
        } catch (SQLException excep) {
          throw new RuntimeException("rollback error occured. " + excep.getMessage());
        }
      }
      throw new RuntimeException("A database error occured. " + se.getMessage());
    } finally {
      if (pstmt != null) {
        try {
          pstmt.close();
        } catch (SQLException se) {
          se.printStackTrace(System.err);
        }
      }
      if (con != null) {
        try {
          con.close();
        } catch (Exception e) {
          e.printStackTrace(System.err);
        }
      }
    }
  }
  /**
   * Calculate the balance for a coinbase, to-address, or p2sh address.
   *
   * @param address The address to calculate the balance of
   * @return The balance of the address supplied. If the address has not been seen, or there are no
   *     outputs open for this address, the return value is 0
   * @throws BlockStoreException
   */
  public BigInteger calculateBalanceForAddress(Address address) throws BlockStoreException {
    maybeConnect();
    PreparedStatement s = null;

    try {
      s =
          conn.get()
              .prepareStatement(
                  "select sum(('x'||lpad(substr(value::text, 3, 50),16,'0'))::bit(64)::bigint) "
                      + "from openoutputs where toaddress = ?");
      s.setString(1, address.toString());
      ResultSet rs = s.executeQuery();
      if (rs.next()) {
        return BigInteger.valueOf(rs.getLong(1));
      } else {
        throw new BlockStoreException("Failed to execute balance lookup");
      }

    } catch (SQLException ex) {
      throw new BlockStoreException(ex);
    } finally {
      if (s != null)
        try {
          s.close();
        } catch (SQLException e) {
          throw new BlockStoreException("Could not close statement");
        }
    }
  }
示例#26
0
 /**
  * Returns the siteId for the site with the given source, name, and type. If no such site is
  * found, this method returns 0;
  */
 private static int queryLookupSiteId(
     int sourceID, String siteInternalName, int siteType, Connection c) throws SQLException {
   PreparedStatement ps = null;
   ResultSet rs = null;
   try {
     ps =
         c.prepareStatement(
             "select site_id from dat_customer_sites "
                 + "where source_id = ? and internal_name = ? and site_type = ?");
     int siteID = 0;
     ps.setInt(1, sourceID);
     ps.setString(2, siteInternalName);
     ps.setInt(3, siteType);
     rs = ps.executeQuery();
     if (rs.next()) {
       siteID = rs.getInt(1);
     }
     return siteID;
   } finally {
     if (rs != null) {
       rs.close();
     }
     if (ps != null) {
       ps.close();
     }
   }
 }
示例#27
0
    public MetadataBlob[] select(String selectString) {
      PreparedStatement stmt = null;
      try {
        stmt =
            conn.prepareStatement(
                "select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
        ResultSet rs = stmt.executeQuery();
        List<MetadataBlob> list = new ArrayList<MetadataBlob>();
        while (rs.next()) {
          MetadataBlob f =
              new MetadataBlob(
                  rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
          list.add(f);
        }

        return list.toArray(new MetadataBlob[0]);
      } catch (SQLException sqe) {
        System.err.println("Error selecting: " + selectString);
        sqe.printStackTrace();
        return null;
      } finally {
        if (stmt != null)
          try {
            stmt.close();
          } catch (SQLException sqe2) {
            sqe2.printStackTrace();
          }
      }
    }
示例#28
0
  /** Test large batch behavior. */
  public void testLargeBatch() throws Exception {
    final int n = 5000;
    getConnection().close();

    Statement stmt = con.createStatement();
    stmt.executeUpdate("create table #testLargeBatch (val int)");
    stmt.executeUpdate("insert into #testLargeBatch (val) values (0)");

    PreparedStatement pstmt = con.prepareStatement("update #testLargeBatch set val=? where val=?");
    for (int i = 0; i < n; i++) {
      pstmt.setInt(1, i + 1);
      pstmt.setInt(2, i);
      pstmt.addBatch();
    }
    int counts[] = pstmt.executeBatch();
    //        System.out.println(pstmt.getWarnings());
    assertEquals(n, counts.length);
    for (int i = 0; i < n; i++) {
      assertEquals(1, counts[i]);
    }
    pstmt.close();

    ResultSet rs = stmt.executeQuery("select count(*) from #testLargeBatch");
    assertTrue(rs.next());
    assertEquals(1, rs.getInt(1));
    assertFalse(rs.next());
    rs.close();
    stmt.close();
  }
示例#29
0
  public void UpdateCustomer(CustomerDTO customer)
      throws ClassNotFoundException, SQLException, Exception {
    String sql = "";
    Connection conn = null;
    PreparedStatement stm = null;

    try {
      conn = getConnection();
      String stuempid = customer.getStuemp_no();
      sql =
          "update ykt_cur.t_cif_customer set password='******' where stuemp_no='"
              + stuempid
              + "'";
      stm = conn.prepareStatement(sql);
      stm.executeUpdate();
    } catch (SQLException e) {
      logger.error("²éѯÊý¾Ý¿âʧ°Ü");
      throw (e);
    } catch (Exception e) {
      e.printStackTrace();
      throw (e);
    } finally {
      if (stm != null) {
        stm.close();
      }
    }
  }
示例#30
0
 public int update(String tableName, String colName, Object newValue, Object oldValue)
     throws SQLException {
   // syntax:
   // update [table] set [column] = [new value]
   // where [column] = [old value]
   int updated = 0;
   boolean hasWhere = false;
   if ((oldValue != null) && !(oldValue.equals(""))) {
     hasWhere = true;
   }
   PreparedStatement pstmt = pstmtBuilder.buildUpdateStatement(conn, tableName, colName, hasWhere);
   try {
     pstmt.setObject(1, newValue);
     if (hasWhere) {
       pstmt.setObject(2, oldValue);
     }
     updated = pstmt.executeUpdate();
   } catch (SQLException sqle) {
     System.out.println(sqle);
   } catch (Exception e) {
     System.out.println(e);
   } finally {
     try {
       pstmt.close();
       conn.close();
     } catch (Exception e) {
       System.out.println(e);
     }
   }
   return updated;
 }