Exemplo n.º 1
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;
  }
  @Test
  public void testPreparedStatementPooling() throws Exception {
    Connection conn = getConnection();
    assertNotNull(conn);

    PreparedStatement stmt1 = conn.prepareStatement("select 'a' from dual");
    assertNotNull(stmt1);

    PreparedStatement stmt2 = conn.prepareStatement("select 'b' from dual");
    assertNotNull(stmt2);

    assertTrue(stmt1 != stmt2);

    // go over the maxOpen limit
    PreparedStatement stmt3 = null;
    try (PreparedStatement ps = conn.prepareStatement("select 'c' from dual")) {
      fail("expected SQLException");
    } catch (SQLException e) {
    }

    // make idle
    stmt2.close();

    // test cleanup the 'b' statement
    stmt3 = conn.prepareStatement("select 'c' from dual");
    assertNotNull(stmt3);
    assertTrue(stmt3 != stmt1);
    assertTrue(stmt3 != stmt2);

    // normal reuse of statement
    stmt1.close();
    PreparedStatement stmt4 = conn.prepareStatement("select 'a' from dual");
    assertNotNull(stmt4);
  }
Exemplo n.º 3
0
Arquivo: SQLDB.java Projeto: Serios/Fe
  protected void saveAccount(String name, double money) {
    if (accountExists(name)) {
      String sql = "UPDATE " + accounts + " SET " + pbalance + "=? WHERE " + pname + "=?";

      try {
        PreparedStatement prest = database.prepare(sql);

        prest.setDouble(1, money);

        prest.setString(2, name);

        prest.executeUpdate();

        prest.close();
      } catch (SQLException e) {

      }
    } else {
      String sql = "INSERT INTO " + accounts + " (" + pname + ", " + pbalance + ") VALUES (?, ?)";

      try {
        PreparedStatement prest = database.prepare(sql);

        prest.setString(1, name);

        prest.setDouble(2, money);

        prest.executeUpdate();

        prest.close();
      } catch (SQLException e) {

      }
    }
  }
Exemplo n.º 4
0
  /**
   * @param conn
   * @param fingerprint
   * @param sampleID
   * @return
   * @throws SQLException
   */
  private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID)
      throws SQLException {
    if (fingerprint.getSampleSetID() == null) {
      /*
       * Insert whole new SampleSetID.
       */
      String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);";
      PreparedStatement insertSampleSet =
          conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);

      insertSampleSet.setInt(1, sampleID);
      insertSampleSet.execute();

      ResultSet rs = insertSampleSet.getGeneratedKeys();
      if (rs.next()) {
        fingerprint.setSampleSetID(rs.getInt(1));
      }
      rs.close();
      insertSampleSet.close();
    } else {
      /*
       * Insert new SampleID for existing SampleSetID.
       */
      String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);";
      PreparedStatement insertSampleSet = conn.prepareStatement(query);

      insertSampleSet.setInt(1, fingerprint.getSampleSetID());
      insertSampleSet.setInt(2, sampleID);
      insertSampleSet.execute();

      insertSampleSet.close();
    }
  }
Exemplo n.º 5
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
Exemplo n.º 6
0
  public void test_prepCall_1() throws Exception {
    DruidPooledConnection conn = dataSource.getConnection().unwrap(DruidPooledConnection.class);

    MockPreparedStatement raw = null;
    {
      PreparedStatement stmt =
          conn.prepareCall("SELECT 1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      raw = stmt.unwrap(MockPreparedStatement.class);
      stmt.close();
    }
    {
      PreparedStatement stmt =
          conn.prepareCall("SELECT 1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
      Assert.assertEquals(raw, stmt.unwrap(MockPreparedStatement.class));
      stmt.close();
    }

    conn.getConnectionHolder().toString();
    conn.getConnectionHolder().setLastActiveTimeMillis(0);
    conn.getConnectionHolder().toString();
    conn.getConnectionHolder().getUseCount();
    conn.getConnectionHolder().getTimeMillis();

    conn.close();
  }
Exemplo n.º 7
0
  public static String saveSearchResult(String uuid, String building_ids, int srid, Connection conn)
      throws Exception {
    PreparedStatement psDelete = null;
    PreparedStatement psAdd = null;
    PreparedStatement psGet = null;
    ResultSet rs = null;
    try {
      psDelete =
          conn.prepareStatement(
              "delete from cust_search_result where uu_id=? or create_time<now() - cast('1 day' as interval)");
      psDelete.setString(1, uuid);
      psDelete.executeUpdate();
      if (building_ids.length() > 0) {

        psAdd =
            conn.prepareStatement(
                "insert into cust_search_result (buid,uu_id) select distinct building_id,? from maps.building_to_customers "
                    + "where building_id = ANY(string_to_array(?,',')::int[])");

        psAdd.setString(1, uuid);
        psAdd.setString(2, building_ids);
        psAdd.executeUpdate();
        psGet =
            conn.prepareStatement(
                "select ST_AsText(ST_transform(ST_Buffer(ST_Envelope (ST_Collect(the_geom)),100),?)) from buildings b"
                    + " where buid= ANY(string_to_array(?,',')::int[])");

        psGet.setInt(1, srid);
        psGet.setString(2, building_ids);
        rs = psGet.executeQuery();
        if (rs.next()) {
          String res = rs.getString(1);
          return res;
        }
      }
      return "";

    } finally {
      try {
        psDelete.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        psAdd.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        psGet.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        rs.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
    }
  }
Exemplo n.º 8
0
  public boolean existeUsuario(Usuario usuario) {

    String sql = "select * from usuario where usuario= ?  and senha= ?";

    try {
      PreparedStatement stmt = this.connection.prepareStatement(sql);

      stmt.setString(1, usuario.getUsuario());
      stmt.setString(2, usuario.getSenha());

      ResultSet rs = stmt.executeQuery();

      // verifica se existe retorno na consulta
      if (rs.next()) {
        stmt.close();
        return true;
      } else {
        stmt.close();
        return false;
      }

    } catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }
  @Override
  public void update(Connection conn, StatsRecordDAO r) throws SQLException {
    if (r.getWord() == null) return;

    PreparedStatement updateWords =
        conn.prepareStatement(
            "UPDATE words SET d = ?, nick = ?, word = ?, "
                + "repetitions = (repetitions+1) WHERE d = ? AND nick = ? "
                + "AND word = ?");

    updateWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
    updateWords.setString(2, r.getNick());
    updateWords.setString(3, r.getWord());
    updateWords.setDate(4, new java.sql.Date(r.getDate().getTime()));
    updateWords.setString(5, r.getNick());
    updateWords.setString(6, r.getWord());

    int ret = updateWords.executeUpdate();
    updateWords.close();
    if (ret == 0) {
      PreparedStatement insertWords = conn.prepareStatement("INSERT INTO words values(?, ?, ?, 1)");

      insertWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
      insertWords.setString(2, r.getNick());
      insertWords.setString(3, r.getWord());

      insertWords.executeUpdate();
      insertWords.close();
    }
  }
  public String createVideo(String videoname, String videourl) throws SQLException {

    PreparedStatement preparedStatement =
        mysqlCon.prepareStatement("SELECT * FROM `IKSvideotag`.`video` WHERE `url`=?");
    preparedStatement.setString(1, videourl);
    ResultSet dataSet = preparedStatement.executeQuery();
    String videoid = "";
    if (dataSet.next()) {
      videoid = dataSet.getString(1);
      preparedStatement.close();
      preparedStatement =
          mysqlCon.prepareStatement("UPDATE `IKSvideotag`.`video` SET `name`=? WHERE `url`=?");
      preparedStatement.setString(1, videoname);
      preparedStatement.setString(2, videourl);
      preparedStatement.executeUpdate();
      preparedStatement.close();
    } else {
      preparedStatement.close();
      preparedStatement =
          mysqlCon.prepareStatement(
              "INSERT INTO `IKSvideotag`.`video` (`name`,`url`) VALUES (?, ?)",
              Statement.RETURN_GENERATED_KEYS);
      preparedStatement.setString(1, videoid);
      preparedStatement.setString(2, videourl);
      preparedStatement.executeUpdate();
      ResultSet rs = preparedStatement.getGeneratedKeys();
      if (rs.next()) {
        videoid = String.valueOf(rs.getInt(1));
      }
      preparedStatement.close();
    }
    return videoid;
  }
Exemplo n.º 11
0
  /**
   * Close the specified database connection.
   *
   * @param dbConnection The connection to be closed
   */
  protected void close(Connection dbConnection) {

    // Do nothing if the database connection is already closed
    if (dbConnection == null) return;

    // Close our prepared statements (if any)
    try {
      preparedCredentials.close();
    } catch (Throwable f) {;
    }
    try {
      preparedRoles.close();
    } catch (Throwable f) {;
    }

    // Close this database connection, and log any errors
    try {
      dbConnection.close();
    } catch (SQLException e) {
      log(sm.getString("jdbcRealm.close"), e); // Just log it here
    }

    // Release resources associated with the closed connection
    this.dbConnection = null;
    this.preparedCredentials = null;
    this.preparedRoles = null;
  }
Exemplo n.º 12
0
  @Override
  public AuthToken createAuthToken(String userid) throws SQLException {
    Connection connection = null;
    PreparedStatement stmt = null;
    String token = null;
    AuthToken authToken = null;
    try {
      connection = Database.getConnection();

      stmt = connection.prepareStatement(AuthTokenDAOQuery.UUID);
      ResultSet rs = stmt.executeQuery();
      if (rs.next()) token = rs.getString(1);
      else throw new SQLException();

      stmt.close();
      stmt = connection.prepareStatement(AuthTokenDAOQuery.CREATE_TOKEN);
      stmt.setString(1, userid);
      stmt.setString(2, token);

      stmt.executeUpdate();

      authToken = new AuthToken();
      authToken.setToken(token);
      authToken.setUserid(userid);
    } catch (SQLException e) {
      throw e;
    } finally {
      if (stmt != null) stmt.close();
      if (connection != null) connection.close();
    }
    return authToken;
  }
Exemplo n.º 13
0
  @Override
  public UserInfo getUserByAuthToken(String token) throws SQLException {
    UserInfo userInfo = null;
    Connection connection = null;
    PreparedStatement stmt = null;

    try {
      connection = Database.getConnection();

      stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_USER_BY_TOKEN);
      stmt.setString(1, token);
      ResultSet rs = stmt.executeQuery();

      if (rs.next()) {
        userInfo = new UserInfo();
        userInfo.setName(rs.getString("id"));
        stmt.close();

        stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_ROLES_OF_USER);
        stmt.setString(1, userInfo.getName());
        rs = stmt.executeQuery();
        while (rs.next()) {
          String role = rs.getString("role");
          userInfo.getRoles().add(Role.valueOf(role));
        }
      }
    } catch (SQLException e) {
      throw e;
    } finally {
      if (stmt != null) stmt.close();
      if (connection != null) connection.close();
    }

    return userInfo;
  }
  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();
    }
  }
Exemplo n.º 15
0
  /* ------------------------------------------------------------ */
  @Override
  protected UserIdentity loadUser(String username) {
    try {
      if (null == _con) connectDatabase();

      if (null == _con) throw new SQLException("Can't connect to database");

      PreparedStatement stat = _con.prepareStatement(_userSql);
      stat.setObject(1, username);
      ResultSet rs = stat.executeQuery();

      if (rs.next()) {
        int key = rs.getInt(_userTableKey);
        String credentials = rs.getString(_userTablePasswordField);
        stat.close();

        stat = _con.prepareStatement(_roleSql);
        stat.setInt(1, key);
        rs = stat.executeQuery();
        List<String> roles = new ArrayList<String>();
        while (rs.next()) roles.add(rs.getString(_roleTableRoleField));

        stat.close();
        return putUser(
            username,
            Credential.getCredential(credentials),
            roles.toArray(new String[roles.size()]));
      }
    } catch (SQLException e) {
      LOG.warn("UserRealm " + getName() + " could not load user information from database", e);
      closeConnection();
    }
    return null;
  }
Exemplo n.º 16
0
  public boolean adicionaStatus(StatusChamado status) throws BusinessException {
    Connection con = null;
    String sql = null;

    try {
      con = Conexao.getInstance().obterConexao();
      con.setAutoCommit(false);

      String origem = Conexao.getInstance().obterOrigem();
      sql = FabricaSql.getSql(origem + INSERIR_STATUS);

      if (DEBUG) System.out.println(sql);

      PreparedStatement stmt = con.prepareStatement(sql);
      // TODO -colocar os parametros

      int qtd = stmt.executeUpdate();

      if (DEBUG) System.out.println("QTDE: " + qtd);

      if (qtd != 1) {
        con.rollback();
        stmt.close();
        throw new BusinessException("Quantidade de linhas afetadas inválida: " + qtd);
      } else con.commit();
      stmt.close();
    } catch (SQLException e) {
      SQLExceptionHandler.tratarSQLException(this.getClass().getName(), e);
      return false;

    } finally {
      Conexao.getInstance().fecharConexao(con);
    }
    return true;
  }
  public boolean isStopWord(String word) throws SQLException {

    String query = "select labels from Word where content like ?";
    PreparedStatement statement = connection.prepareStatement(query);

    statement.setString(1, word);
    ResultSet set = statement.executeQuery();

    if (!set.next()) {

      set.close();
      statement.close();

      return false;

    } else {

      String label = set.getString(1);
      set.close();
      statement.close();

      if (label == null) return false;
      else if (label.indexOf("stop") != -1) return true;
      else return false;
    }
  }
  public static void insert(String sUserID, String accessCode) {
    PreparedStatement ps = null;

    String sInsert =
        "INSERT INTO AccessLogs (accessid, userid, accesstime,accesscode) VALUES (?, ?, ?,?)";

    Connection ad_conn = MedwanQuery.getInstance().getAdminConnection();
    try {
      ps = ad_conn.prepareStatement(sInsert);
      ps.setInt(1, MedwanQuery.getInstance().getOpenclinicCounter("AccessLogs"));
      ps.setInt(2, Integer.parseInt(sUserID));
      ps.setTimestamp(3, ScreenHelper.getSQLTime());
      ps.setString(4, accessCode);
      ps.executeUpdate();
      ps.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (ps != null) ps.close();
        ad_conn.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
  }
Exemplo n.º 19
0
  private void saveBuildings(int building_id, String customers, Connection conn) throws Exception {
    PreparedStatement psDelete = null;
    PreparedStatement psAdd = null;
    try {
      psDelete =
          conn.prepareStatement("delete from maps.building_to_customers where building_id=?");
      psDelete.setInt(1, building_id);
      psDelete.executeUpdate();
      if (customers.length() > 0) {
        String cusIds[] = customers.split(",");
        psAdd =
            conn.prepareStatement(
                "insert into maps.building_to_customers (building_id,cusid) values (?,?)");

        for (String cusId : cusIds) {
          psAdd.setInt(1, building_id);
          psAdd.setInt(2, new Integer(cusId));
          psAdd.addBatch();
        }
        psAdd.executeBatch();
      }

    } finally {
      try {
        psDelete.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
      try {
        psAdd.close();
      } catch (Exception e2) {
        // TODO: handle exception
      }
    }
  }
  public static List getLastAccess(String patientId, int nb) {
    PreparedStatement ps = null;
    ResultSet rs = null;
    List l = new LinkedList();
    String sSelect = " SELECT * FROM AccessLogs a WHERE accesscode = ? ORDER BY accessid DESC";

    Connection ad_conn = MedwanQuery.getInstance().getAdminConnection();
    try {
      ps = ad_conn.prepareStatement(sSelect);
      ps.setString(1, patientId);

      rs = ps.executeQuery();
      int i = 0;
      while (rs.next()) {
        if (nb == 0 || i <= nb) {
          Object sReturn[] = {rs.getTimestamp("accesstime"), rs.getString("userid")};
          l.add(sReturn);
        }
        i++;
      }
      rs.close();
      ps.close();
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (rs != null) rs.close();
        if (ps != null) ps.close();
        ad_conn.close();
      } catch (Exception e) {
        e.printStackTrace();
      }
    }
    return l;
  }
Exemplo n.º 21
0
  public void test_prepCall() throws Exception {
    DruidPooledConnection conn = dataSource.getConnection().unwrap(DruidPooledConnection.class);

    MockPreparedStatement raw = null;
    {
      PreparedStatement stmt =
          conn.prepareCall(
              "SELECT 1",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY,
              ResultSet.HOLD_CURSORS_OVER_COMMIT);
      raw = stmt.unwrap(MockPreparedStatement.class);
      stmt.close();
    }
    {
      PreparedStatement stmt =
          conn.prepareCall(
              "SELECT 1",
              ResultSet.TYPE_FORWARD_ONLY,
              ResultSet.CONCUR_READ_ONLY,
              ResultSet.HOLD_CURSORS_OVER_COMMIT);
      Assert.assertEquals(raw, stmt.unwrap(MockPreparedStatement.class));
      stmt.close();
    }

    conn.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();
    }
  }
Exemplo n.º 23
0
 public void copyData(Connection conn) throws SQLException {
   ResultSet rs = null;
   PreparedStatement stmt = null;
   PreparedStatement stmt2 = null;
   String q2 = "insert into modmodlinks2 (pModID,cModID,courseID) values (?,?,?)";
   try {
     String q =
         "select parentModID,childModID,courseID from modmodulelinks order by parentModId,childModID";
     stmt = conn.prepareStatement(q);
     rs = stmt.executeQuery();
     while (rs.next()) {
       int p = rs.getInt(1);
       int c = rs.getInt(2);
       int crs = rs.getInt(3);
       stmt2 = conn.prepareStatement(q2);
       stmt2.setInt(1, p);
       stmt2.setInt(2, c);
       stmt2.setInt(3, crs);
       System.out.println("Insert p=" + p + " c=" + c + " crs=" + crs);
       stmt2.executeUpdate();
       stmt2.close();
     }
   } finally {
     if (stmt != null) stmt.close();
     if (rs != null) rs.close();
   }
 }
Exemplo n.º 24
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;
  }
  /**
   * @param date
   * @param vipType 黄钻为0,红钻为1
   * @param type 活跃用户为0,新增用户为1
   * @param list
   * @throws SQLException
   */
  private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
    PreparedStatement pstmt;
    for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
      for (int vipLevel = 0; vipLevel < 8; vipLevel++) {

        pstmt =
            con.prepareStatement(
                "delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.execute();
        pstmt.close();

        pstmt =
            con.prepareStatement(
                "insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
        pstmt.setDate(1, new java.sql.Date(date.getTime()));
        pstmt.setInt(2, type);
        pstmt.setInt(3, isYearVip);
        pstmt.setInt(4, vipLevel);
        pstmt.setInt(5, vipType);
        pstmt.setInt(6, list[isYearVip][vipLevel]);
        pstmt.execute();
        pstmt.close();
      }
    }
  }
Exemplo n.º 26
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;
  }
Exemplo n.º 27
0
  /**
   * 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) {
      }
    }
  }
Exemplo n.º 28
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;
  }
Exemplo n.º 29
0
  /**
   * Get Details
   *
   * @return array of details
   */
  public MCommissionDetail[] getDetails() {
    String sql = "SELECT * FROM C_CommissionDetail WHERE C_CommissionAmt_ID=?";
    ArrayList<MCommissionDetail> list = new ArrayList<MCommissionDetail>();
    PreparedStatement pstmt = null;
    try {
      pstmt = DB.prepareStatement(sql, get_TrxName());
      pstmt.setInt(1, getC_CommissionAmt_ID());
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) list.add(new MCommissionDetail(getCtx(), rs, get_TrxName()));
      rs.close();
      pstmt.close();
      pstmt = null;
    } catch (Exception e) {
      log.error(sql, e);
    }
    try {
      if (pstmt != null) pstmt.close();
      pstmt = null;
    } catch (Exception e) {
      pstmt = null;
    }

    //	Convert
    MCommissionDetail[] retValue = new MCommissionDetail[list.size()];
    list.toArray(retValue);
    return retValue;
  } //	getDetails
Exemplo n.º 30
0
  public void excluir(Balanca balanca) throws ClassNotFoundException {
    this.balanca = balanca;

    String sql = "delete from BALANCA" + " where IDBALANCA = ?";

    try {
      Connection con = new ConnectionFactory().getConnection();
      PreparedStatement stmt = con.prepareStatement(sql);

      stmt.setInt(1, this.balanca.getIdBalanca());

      stmt.executeUpdate();
      stmt.close();
      con.close();

      // SQL Server
      Connection conS = new ConnectionFactory().getServer();
      stmt = conS.prepareStatement(sql);

      stmt.setInt(1, this.balanca.getIdBalanca());

      stmt.executeUpdate();
      stmt.close();
      conS.close();
    } catch (SQLException e) {
      throw new RuntimeException(e + "Erro na Conexão");
    }
  }