Beispiel #1
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;
  }
Beispiel #2
1
  @Override
  public int size(final DatabaseTable table) throws DatabaseException {
    preOperationCheck();

    final StringBuilder sb = new StringBuilder();
    sb.append("SELECT COUNT(" + KEY_COLUMN + ") FROM ").append(table.toString());

    PreparedStatement statement = null;
    ResultSet resultSet = null;
    try {
      statement = connection.prepareStatement(sb.toString());
      resultSet = statement.executeQuery();
      if (resultSet.next()) {
        return resultSet.getInt(1);
      }
    } catch (SQLException e) {
      final ErrorInformation errorInformation =
          new ErrorInformation(
              PwmError.ERROR_DB_UNAVAILABLE, "size operation failed: " + e.getMessage());
      lastError = errorInformation;
      throw new DatabaseException(errorInformation);
    } finally {
      close(statement);
      close(resultSet);
    }

    updateStats(true, false);
    return 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);
   }
 }
  /**
   * 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) {
      }
    }
  }
Beispiel #5
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);
        }
      }
    }
  }
  public void init(int startIndex, int endIndex, String updateDate) throws SQLException {

    if (connection == null || connection.isClosed()) {
      connection = getConnection();
    }
    if (startIndex != 0 && endIndex != 0) {
      bibQuery =
          "SELECT * FROM OLE_DS_BIB_T WHERE BIB_ID BETWEEN "
              + startIndex
              + " AND "
              + endIndex
              + " ORDER BY BIB_ID";
    } else if (StringUtils.isNotEmpty(updateDate)) {
      updateDate = getDateStringForOracle(updateDate);
      bibQuery = "SELECT * FROM OLE_DS_BIB_T where DATE_UPDATED > '" + updateDate + "'";
    } else {
      bibQuery = "SELECT * FROM OLE_DS_BIB_T ORDER BY BIB_ID";
    }
    if (!isStaffOnly) {
      bibQuery = bibStaffOnly;
      holdingsQuery = holdingsQuery + staffOnlyHoldings;
      itemQuery = itemQuery + staffOnlyItem;
    }

    fetchCallNumberType();
    fetchReceiptStatus();
    fetchAuthenticationType();
    fetchItemType();
    fetchItemStatus();
    fetchStatisticalSearchCode();
    fetchExtentOfOwnershipType();

    bibConnection = getConnection();
    holdingsConnection = getConnection();
    itemConnection = getConnection();
    bibConnection.setAutoCommit(false);

    bibStatement =
        bibConnection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    if (dbVendor.equalsIgnoreCase("oracle")) {
      bibStatement.setFetchSize(1);
    } else if (dbVendor.equalsIgnoreCase("mysql")) {
      bibStatement.setFetchSize(Integer.MIN_VALUE);
    }

    bibResultSet = bibStatement.executeQuery(bibQuery);

    holdingsPreparedStatement = holdingsConnection.prepareStatement(holdingsQuery);

    itemPreparedStatement = itemConnection.prepareStatement(itemQuery);

    String insertQuery =
        "INSERT INTO OLE_DS_BIB_INFO_T(BIB_ID, BIB_ID_STR, TITLE, AUTHOR, PUBLISHER, ISXN) VALUES (?,?,?,?,?,?)";
    bibInsertPreparedStatement = connection.prepareStatement(insertQuery);

    String updateQuery =
        "UPDATE OLE_DS_BIB_INFO_T SET TITLE=?, AUTHOR=?, PUBLISHER=?, ISXN=?, BIB_ID_STR=? WHERE BIB_ID=?";
    bibUpdatePreparedStatement = connection.prepareStatement(updateQuery);
  }
Beispiel #7
0
  /**
   * Consulta as glosas do prestador passado em um determinado periodo
   *
   * @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as glosas
   * @param strNumPeriodo - o periodo de referencia do qual se deseja obter os glosas
   * @return um array de glosas do prestador fornecido como paramentro
   */
  public static final GlosaPrestador[] buscaGlosaPrest(String strCdContrato, String strNumPeriodo)
      throws Exception {

    Connection con = ConnectionPool.getConnection();
    GlosaPrestador[] glosas = null;
    PreparedStatement pst;
    ResultSet rset;
    int qtdeGlosas = 0;

    try {

      pst = con.prepareStatement(CONSULTA_GLOSA);
      pst.setString(1, strCdContrato);
      pst.setString(2, strNumPeriodo);
      rset = pst.executeQuery();

      if (!rset.next()) {
        return null;
      } // if ( ! rset.next() )

      do {
        qtdeGlosas += 1;
      } while (rset.next());

      System.out.println("qtdeGlosas -> " + qtdeGlosas);

      glosas = new GlosaPrestador[qtdeGlosas];

      pst = con.prepareStatement(CONSULTA_GLOSA);
      pst.setString(1, strCdContrato);
      pst.setString(2, strNumPeriodo);
      rset = pst.executeQuery();

      qtdeGlosas = 0;

      while (rset.next()) {
        glosas[qtdeGlosas] = montaGlosaPrestador(rset);
        qtdeGlosas++;
      } // while

    } catch (SQLException sqle) {
      sqle.printStackTrace();
      throw new Exception(
          "Não foi possivel estabelecer conexão com a base de "
              + "dados.Erro:\n"
              + sqle.getMessage());
    } finally { // catch()
      if (con != null) {
        con.close();
        System.out.println("Fechou a conexao");
      } // if
    }
    return glosas;
  } // consultaGlosaPrest()
Beispiel #8
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();
      }
    }
  }
Beispiel #9
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;
  }
Beispiel #10
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);
      }
    }
  }
Beispiel #11
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);
      }
    }
  }
 // metoda za pretragu po broju stanovnika
 public ArrayList<Country> SearchCountryPopulation(long Population) {
   ArrayList<Country> countries = new ArrayList<Country>();
   try {
     Connection connection = getConnected("world");
     PreparedStatement statement =
         connection.prepareStatement(
             "SELECT * FROM country WHERE Population <= " + Population + ";");
     ResultSet result = statement.executeQuery();
     while (result.next()) {
       countries.add(
           new Country(
               result.getString("Code"),
               result.getString("Name"),
               result.getString("Continent"),
               result.getString("Region"),
               result.getDouble("SurfaceArea"),
               result.getInt("IndepYear"),
               result.getLong("Population"),
               result.getDouble("LifeExpectancy"),
               result.getDouble("GNP"),
               result.getDouble("GNPOld"),
               result.getString("LocalName"),
               result.getString("GovernmentForm"),
               result.getString("HeadOfState"),
               result.getInt("Capital"),
               result.getString("Code2")));
     }
     connection.close();
   } catch (Exception e) {
     System.out.println(e.toString());
     return null;
   }
   return countries;
 }
  public int update(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : update() ");
    }
    PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("update PT_KICA_ERR_LOG  set ")
        .append("U_D_FLAG = ")
        .append(toDB(entity.getU_D_FLAG()))
        .append(",")
        .append("YYYYMMDD = ")
        .append(toDB(entity.getYYYYMMDD()))
        .append(",")
        .append("TRANSHOUR = ")
        .append(toDB(entity.getTRANSHOUR()))
        .append(",")
        .append("FILENAME = ")
        .append(toDB(entity.getFILENAME()))
        .append(",")
        .append("ERRLOG = ")
        .append(toDB(entity.getERRLOG()))
        .append(",")
        .append("RESULT_FLAG = ")
        .append(toDB(entity.getRESULT_FLAG()))
        .append(",")
        .append("UPD_DT = ")
        .append(toDB(entity.getUPD_DT()))
        .append(",")
        .append("INS_DT = ")
        .append(toDB(entity.getINS_DT()))
        .append(" where  1=1 ");

    sb.append(" and SEQ = ").append(toDB(entity.getSEQ()));

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      int i = 1;

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;
    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result;
  }
 public List getBooksByCost(String cost) {
   List al = new ArrayList();
   try {
     con = JdbcUtil.getMysqlConnection();
     ps = con.prepareStatement("select *from jlcbooks where cost=?");
     ps.setString(1, cost);
     rs = ps.executeQuery();
     while (rs.next()) {
       Book bo = new Book();
       bo.setBid(rs.getString(1));
       bo.setBname(rs.getString(2));
       bo.setAuthor(rs.getString(3));
       bo.setPub(rs.getString(4));
       bo.setCost(rs.getString(5));
       bo.setEdi(rs.getString(6));
       bo.setIsbn(rs.getString(7));
       al.add(bo);
     }
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     JdbcUtil.cleanup(ps, con);
   }
   return al;
 }
Beispiel #15
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);
      }
    }
  }
 public CFSecuritySecDeviceBuff[] readBuffByUserIdx(
     CFSecurityAuthorization Authorization, UUID SecUserId) {
   final String S_ProcName = "readBuffByUserIdx";
   ResultSet resultSet = null;
   try {
     Connection cnx = schema.getCnx();
     String sql =
         "call "
             + schema.getLowerDbSchemaName()
             + ".sp_read_secdev_by_useridx( ?, ?, ?, ?, ?"
             + ", "
             + "?"
             + " )";
     if (stmtReadBuffByUserIdx == null) {
       stmtReadBuffByUserIdx = cnx.prepareStatement(sql);
     }
     int argIdx = 1;
     stmtReadBuffByUserIdx.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
     stmtReadBuffByUserIdx.setString(
         argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
     stmtReadBuffByUserIdx.setString(
         argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
     stmtReadBuffByUserIdx.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
     stmtReadBuffByUserIdx.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
     stmtReadBuffByUserIdx.setString(argIdx++, SecUserId.toString());
     try {
       resultSet = stmtReadBuffByUserIdx.executeQuery();
     } catch (SQLException e) {
       if (e.getErrorCode() != 1329) {
         throw e;
       }
       resultSet = null;
     }
     List<CFSecuritySecDeviceBuff> buffList = new LinkedList<CFSecuritySecDeviceBuff>();
     while ((resultSet != null) && resultSet.next()) {
       CFSecuritySecDeviceBuff buff = unpackSecDeviceResultSetToBuff(resultSet);
       buffList.add(buff);
     }
     int idx = 0;
     CFSecuritySecDeviceBuff[] retBuff = new CFSecuritySecDeviceBuff[buffList.size()];
     Iterator<CFSecuritySecDeviceBuff> iter = buffList.iterator();
     while (iter.hasNext()) {
       retBuff[idx++] = iter.next();
     }
     return (retBuff);
   } catch (SQLException e) {
     throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
   } finally {
     if (resultSet != null) {
       try {
         resultSet.close();
       } catch (SQLException e) {
       }
       resultSet = null;
     }
   }
 }
Beispiel #17
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();
     }
   }
 }
Beispiel #18
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();
     }
   }
 }
  static void task1() throws FileNotFoundException, IOException, SQLException {
    // Read Input
    System.out.println("Task1 Started...");
    BufferedReader br = new BufferedReader(new FileReader(inputFile));
    br.readLine();
    String task1Input = br.readLine();
    br.close();
    double supportPercent =
        Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim());
    if (supportPercent >= 0) {
      System.out.println("Task1 Support Percent :" + supportPercent);
      // Prepare query
      String task1Sql =
          "select  temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent"
              + " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i"
              + " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*"
              + supportPercent / 100
              + " from trans)"
              + ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent";

      PreparedStatement selTask1 = con.prepareStatement(task1Sql);
      ResultSet rsTask1 = selTask1.executeQuery();

      BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1"));
      while (rsTask1.next()) {
        bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%");
        bw.newLine();
      }
      rsTask1.close();
      bw.close();
      System.out.println("Task1 Completed...\n");
    } else System.out.println("Support percent should be a positive number");
  }
Beispiel #20
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;
    }
  }
Beispiel #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);
      }
    }
  }
Beispiel #22
0
  /**
   * Get waiting User name;
   *
   * @return An waiter ArrayList
   */
  public static ArrayList<String> getWaiter() throws SQLException {
    ArrayList<String> waiterArray = new ArrayList<String>();
    Connection conn = DBConn.GetConnection();
    PreparedStatement st = conn.prepareStatement("SELECT * FROM cs_conversion where isstart=?");
    st.setInt(1, 1);
    ResultSet rs = st.executeQuery();
    while (rs.next()) {
      if (rs.getString("username").length() != 0) {

        if (waiterArray.size() == 0) {
          waiterArray.add(rs.getString("username"));
        } else {
          for (int i = 0; i < waiterArray.size(); i++) {
            if (!rs.getString("username").equals(waiterArray.get(i).toString())) {
              waiterArray.add(rs.getString("username"));
            }
          }
        }
      }
    }
    rs.close();
    st.close();
    conn.close();
    return waiterArray;
  }
Beispiel #23
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;
  }
 public void deleteTSecGroupByTenantVisIdx(
     CFSecurityAuthorization Authorization, long TenantId, boolean IsVisible) {
   final String S_ProcName = "deleteTSecGroupByTenantVisIdx";
   if (!schema.isTransactionOpen()) {
     throw CFLib.getDefaultExceptionFactory()
         .newUsageException(getClass(), S_ProcName, "Transaction not open");
   }
   ResultSet resultSet = null;
   try {
     Connection cnx = schema.getCnx();
     String sql = "exec sp_delete_tsecgrp_by_tenantvisidx ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?";
     if (stmtDeleteByTenantVisIdx == null) {
       stmtDeleteByTenantVisIdx = cnx.prepareStatement(sql);
     }
     int argIdx = 1;
     stmtDeleteByTenantVisIdx.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
     stmtDeleteByTenantVisIdx.setString(
         argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
     stmtDeleteByTenantVisIdx.setString(
         argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
     stmtDeleteByTenantVisIdx.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
     stmtDeleteByTenantVisIdx.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
     stmtDeleteByTenantVisIdx.setLong(argIdx++, TenantId);
     if (IsVisible) {
       stmtDeleteByTenantVisIdx.setString(argIdx++, "Y");
     } else {
       stmtDeleteByTenantVisIdx.setString(argIdx++, "N");
     }
     Object stuff = null;
     boolean moreResults = stmtDeleteByTenantVisIdx.execute();
     while (stuff == null) {
       try {
         moreResults = stmtDeleteByTenantVisIdx.getMoreResults();
       } catch (SQLException e) {
         throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
       }
       if (moreResults) {
         try {
           stuff = stmtDeleteByTenantVisIdx.getResultSet();
         } catch (SQLException e) {
         }
       } else if (-1 == stmtDeleteByTenantVisIdx.getUpdateCount()) {
         break;
       }
     }
   } catch (SQLException e) {
     throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
   } finally {
     if (resultSet != null) {
       try {
         resultSet.close();
       } catch (SQLException e) {
       }
       resultSet = null;
     }
   }
 }
  public int delete(ValueObject obj) throws SQLException {

    if ((obj instanceof PT_KICA_ERR_LOGEntity) == false) {
      throw new SQLException("DAO 에러(1): PT_KICA_ERR_LOG : delete() ");
    }
    PT_KICA_ERR_LOGEntity entity = (PT_KICA_ERR_LOGEntity) obj;

    Connection conn = null;
    PreparedStatement ps = null;
    int result = 0;

    StringBuffer sb = new StringBuffer();
    sb.append("delete from PT_KICA_ERR_LOG  where  1=1")
        .append(" and SEQ = ")
        .append(toDB(entity.getSEQ()));

    KJFLog.sql(sb.toString());

    try {

      conn = this.getConnection();
      ps = conn.prepareStatement(sb.toString());

      result = ps.executeUpdate();

    } catch (SQLException e) {
      throw e;
    } finally {
      if (ps != null) ps.close();
      this.release(conn);
    }

    return result;
  }
  /**
   * Returns the messages which ID starts with the given ID
   *
   * @param ID message ID
   * @return text and ID of the message
   */
  public synchronized Hashtable getMessagesWithId(String ID) {
    //        System.out.println("getMessagesWithId:"+ID);

    Hashtable res = new Hashtable();
    Enumeration enm = dict.keys();
    while (enm.hasMoreElements()) {
      String key = "" + enm.nextElement();
      if (key.startsWith(ID)) res.put(key, dict.get(key));
    }
    if (res.size() == 0) {
      Connection connection = null;
      try {
        connection = getMessageDBConnection();
        PreparedStatement proc =
            connection.prepareStatement("SELECT tkey,txt FROM messages_texts WHERE tkey like ?");
        proc.setString(1, ID + "%");
        ResultSet rs = proc.executeQuery();
        while (rs.next()) {
          res.put(rs.getString(1), rs.getString(2));
        }
        connection.close();
      } catch (Exception e) {
        /*not message connection*/
        e.printStackTrace();
      }
    }

    return res;
  }
 public int updateBook(Book bo) {
   int x = 0;
   try {
     con = JdbcUtil.getMysqlConnection();
     System.out.println("update 2");
     ps =
         con.prepareStatement(
             "update jlcbooks set bname=?,author=?,pub=?,cost=?,edition=?,isbn=? where bid=?");
     System.out.println("update 3");
     System.out.println(bo.getBid());
     ps.setString(7, bo.getBid());
     ps.setString(1, bo.getBname());
     ps.setString(2, bo.getAuthor());
     ps.setString(3, bo.getPub());
     ps.setString(4, bo.getCost());
     ps.setString(5, bo.getEdi());
     ps.setString(6, bo.getIsbn());
     x = ps.executeUpdate();
   } catch (Exception e) {
     e.printStackTrace();
   } finally {
     JdbcUtil.cleanup(ps, con);
   }
   return x;
 }
  public void deleteISOTimezone(
      CFSecurityAuthorization Authorization, CFSecurityISOTimezoneBuff Buff) {
    final String S_ProcName = "deleteISOTimezone";
    ResultSet resultSet = null;
    try {
      Connection cnx = schema.getCnx();
      short ISOTimezoneId = Buff.getRequiredISOTimezoneId();

      String sql =
          "SELECT "
              + schema.getLowerDbSchemaName()
              + ".sp_delete_isotz( ?, ?, ?, ?, ?"
              + ", "
              + "?"
              + ", "
              + "?"
              + " ) as DeletedFlag";
      if (stmtDeleteByPKey == null) {
        stmtDeleteByPKey = cnx.prepareStatement(sql);
      }
      int argIdx = 1;
      stmtDeleteByPKey.setLong(
          argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
      stmtDeleteByPKey.setString(
          argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
      stmtDeleteByPKey.setString(
          argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
      stmtDeleteByPKey.setLong(
          argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
      stmtDeleteByPKey.setLong(
          argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
      stmtDeleteByPKey.setShort(argIdx++, ISOTimezoneId);
      stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
      ;
      resultSet = stmtDeleteByPKey.executeQuery();
      if (resultSet.next()) {
        boolean deleteFlag = resultSet.getBoolean(1);
        if (resultSet.next()) {
          throw CFLib.getDefaultExceptionFactory()
              .newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
        }
      } else {
        throw CFLib.getDefaultExceptionFactory()
            .newRuntimeException(
                getClass(),
                S_ProcName,
                "Expected 1 record result set to be returned by delete, not 0 rows");
      }
    } catch (SQLException e) {
      throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
      if (resultSet != null) {
        try {
          resultSet.close();
        } catch (SQLException e) {
        }
        resultSet = null;
      }
    }
  }
 public CFSecurityTSecGroupBuff lockBuff(
     CFSecurityAuthorization Authorization, CFSecurityTSecGroupPKey PKey) {
   final String S_ProcName = "lockBuff";
   if (!schema.isTransactionOpen()) {
     throw CFLib.getDefaultExceptionFactory()
         .newUsageException(getClass(), S_ProcName, "Transaction not open");
   }
   ResultSet resultSet = null;
   try {
     Connection cnx = schema.getCnx();
     long TenantId = PKey.getRequiredTenantId();
     int TSecGroupId = PKey.getRequiredTSecGroupId();
     String sql =
         "SELECT * FROM "
             + schema.getLowerDbSchemaName()
             + ".sp_lock_tsecgrp( ?, ?, ?, ?, ?"
             + ", "
             + "?"
             + ", "
             + "?"
             + " )";
     if (stmtLockBuffByPKey == null) {
       stmtLockBuffByPKey = cnx.prepareStatement(sql);
     }
     int argIdx = 1;
     stmtLockBuffByPKey.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
     stmtLockBuffByPKey.setString(
         argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
     stmtLockBuffByPKey.setString(
         argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
     stmtLockBuffByPKey.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
     stmtLockBuffByPKey.setLong(
         argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
     stmtLockBuffByPKey.setLong(argIdx++, TenantId);
     stmtLockBuffByPKey.setInt(argIdx++, TSecGroupId);
     resultSet = stmtLockBuffByPKey.executeQuery();
     if (resultSet.next()) {
       CFSecurityTSecGroupBuff buff = unpackTSecGroupResultSetToBuff(resultSet);
       if (resultSet.next()) {
         throw CFLib.getDefaultExceptionFactory()
             .newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
       }
       return (buff);
     } else {
       return (null);
     }
   } catch (SQLException e) {
     throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
   } finally {
     if (resultSet != null) {
       try {
         resultSet.close();
       } catch (SQLException e) {
       }
       resultSet = null;
     }
   }
 }
Beispiel #30
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();
      }
    }
  }