Example #1
0
  /**
   * Consulta as fatos do prestador passado em um determinado periodo
   *
   * @param strCdContrato - o codigo do contrato do prestado do qual se deseja obter as fatos
   * @param strNumPeriodo - o periodo de referencia do qual se deseja obter os fatos
   * @return um array de fatos do prestador fornecido como paramentro
   */
  public static final ResumoFato[] buscaResumoFato(String strCdContrato, String strNumPeriodo)
      throws Exception {

    Connection con = ConnectionPool.getConnection();
    ResumoFato[] fatos = null;
    PreparedStatement pst;
    ResultSet rset;
    int qtdeFatos = 0;

    try {

      pst = con.prepareStatement(CONSULTA_RESUMO_FATO);
      pst.setString(1, strCdContrato);
      pst.setString(2, strNumPeriodo);
      pst.setString(3, strCdContrato);
      pst.setString(4, strNumPeriodo);
      pst.setString(5, strCdContrato);
      pst.setString(6, strNumPeriodo);
      pst.setString(7, strCdContrato);
      pst.setString(8, strNumPeriodo);
      pst.setString(9, strCdContrato);
      pst.setString(10, strNumPeriodo);

      rset = pst.executeQuery();

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

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

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

      fatos = new ResumoFato[qtdeFatos];

      rset = pst.executeQuery();

      qtdeFatos = 0;

      while (rset.next()) {
        fatos[qtdeFatos] = montaResumoFato(rset);
        qtdeFatos++;
      } // 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 fatos;
  } // buscaResumoFato()
  public static int updateStudy(String code, Study study) {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;

    String query =
        "UPDATE study SET "
            + "name = ?, description = ?, question = ? , imageURL = ?, requestedParticipants = ?,"
            + "numOfParticipants = ?, status = ?"
            + "WHERE code = ?";

    try {
      ps = connection.prepareStatement(query);

      ps.setString(1, study.getName());
      ps.setString(2, study.getDescription());
      ps.setString(3, study.getQuestion());
      ps.setString(4, study.getImageURL());
      ps.setInt(5, study.getRequestedParticipants());
      ps.setInt(6, study.getNumOfParticipants());
      ps.setString(7, study.getStatus());
      ps.setString(8, code);

      return ps.executeUpdate();
    } catch (SQLException e) {
      System.out.println(e);
      return 0;
    } finally {
      DBUtil.closePreparedStatement(ps);
      pool.freeConnection(connection);
    }
  }
Example #3
0
  public static List<Departamento> buscarDepartamentos() {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
      List<Departamento> departamentos = new ArrayList();
      cs = connection.prepareCall("{ call listaDepartamento() }");
      rs = cs.executeQuery();
      while (rs.next()) {
        Departamento dpo =
            new Departamento(rs.getInt("idDepartamento"), rs.getString("nombreDepartamento"));

        departamentos.add(dpo);
      }
      return departamentos;
    } catch (Exception ex) {
      ex.printStackTrace();
      return null;

    } finally {
      DBUtil.closeResultSet(rs);
      DBUtil.closeStatement(cs);
      pool.freeConnection(connection);
    }
  }
Example #4
0
  public static List<Sucursal> buscarSucursales() {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
      List<Sucursal> sucursales = new ArrayList();
      cs = connection.prepareCall("{ call listaSucursal() }");
      rs = cs.executeQuery();
      while (rs.next()) {
        Sucursal suc = new Sucursal(rs.getInt("idSucursal"), rs.getString("nombreSucursal"));

        sucursales.add(suc);
      }
      return sucursales;
    } catch (Exception ex) {
      ex.printStackTrace();
      return null;

    } finally {
      DBUtil.closeResultSet(rs);
      DBUtil.closeStatement(cs);
      pool.freeConnection(connection);
    }
  }
Example #5
0
  public static List<Pago> buscarPagos() {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
      List<Pago> pagos = new ArrayList();
      cs = connection.prepareCall("{ call listaPago() }");
      rs = cs.executeQuery();
      while (rs.next()) {
        Pago pag = new Pago(rs.getInt("idMetodoPago"), rs.getString("nombreMetodoPago"));

        pagos.add(pag);
      }
      return pagos;
    } catch (Exception ex) {
      ex.printStackTrace();
      return null;

    } finally {
      DBUtil.closeResultSet(rs);
      DBUtil.closeStatement(cs);
      pool.freeConnection(connection);
    }
  }
Example #6
0
  public static List<Usuario> buscarUsuarios() {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
      List<Usuario> usuas = new ArrayList();
      cs = connection.prepareCall("{ call cajeroReporte() }");
      rs = cs.executeQuery();
      while (rs.next()) {
        Usuario usa =
            new Usuario(
                rs.getString("nombreUsuario"),
                rs.getString("apellidoPaterno"),
                rs.getInt("idUsuario"));

        usuas.add(usa);
      }
      return usuas;
    } catch (Exception ex) {
      ex.printStackTrace();
      return null;

    } finally {
      DBUtil.closeResultSet(rs);
      DBUtil.closeStatement(cs);
      pool.freeConnection(connection);
    }
  }
Example #7
0
  public static int insertarVenta(Venta v) {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    CallableStatement cs = null;
    ResultSet rs = null;
    Venta ven = null;
    try {
      cs = connection.prepareCall("{ call insertVenta(?, ?, ?, ?) }");
      cs.setInt(1, v.getUsuarioVenta().getId());
      cs.setDouble(2, v.getSubtotal());
      cs.setInt(3, v.getPagoVenta().getIdPago());
      cs.setInt(4, v.getUsuarioVenta().getSucursal().getIdSucursal());

      rs = cs.executeQuery();
      while (rs.next()) {
        ven = new Venta(rs.getInt("idVenta"));
      }

      return ven.getIdVenta();

    } catch (Exception ex) {
      ex.printStackTrace();
      return 0;

    } finally {
      DBUtil.closeStatement(cs);
      pool.freeConnection(connection);
    }
  }
  public static int addStudy(Study study) {

    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;

    String query =
        "INSERT INTO study (name,description,creatorEmail, dateCreated, "
            + "question, imageURL, requestedParticipants, numOfParticipants, status) "
            + "VALUES (?,?,?,?,?,?,?,?,?)";
    try {
      ps = connection.prepareStatement(query);
      ps.setString(1, study.getName());

      ps.setString(2, study.getDescription());
      ps.setString(3, study.getCreatorEmail());
      ps.setTimestamp(4, study.getDateCreated());
      ps.setString(5, study.getQuestion());
      ps.setString(6, study.getImageURL());
      ps.setInt(7, study.getRequestedParticipants());
      ps.setInt(8, study.getNumOfParticipants());
      ps.setString(9, study.getStatus());

      return ps.executeUpdate();
    } catch (SQLException e) {
      System.out.println(e);
      return 0;
    } finally {
      DBUtil.closePreparedStatement(ps);
      pool.freeConnection(connection);
    }
  }
Example #9
0
  public UserInfo getUser(String username) {
    UserInfo user = null;
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {

      ps = connection.prepareStatement("select * from user_info where username=?");

      ps.setString(1, username);

      rs = ps.executeQuery();

      if (rs.next()) {
        user = new UserInfo();
        user.setFirstName(rs.getString("firstName"));
        user.setLastName(rs.getString("lastName"));
        user.setAddress(rs.getString("address"));
        user.setAffiliation(rs.getString("affiliation"));
        user.setMemDur(rs.getString("memDur"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setCardType(rs.getString("cardType"));
        user.setCardNumber(rs.getString("cardNumber"));
        user.setCardNumber(rs.getString("email"));
      }

    } catch (Exception e) {
      System.out.println(e);
    } finally {
      if (connection != null) {
        try {
          connection.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (ps != null) {
        try {
          ps.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          e.printStackTrace();
        }
      }
    }
    return user;
  }
 /**
  * Public method that deletes rows which match a whereClause from a given table
  *
  * @param table Table from where the rows will be deleted
  * @param whereClause Where clause to be matched
  * @return <b>True</b> if delete successful.
  * @throws SQLException
  */
 public static boolean delete(String table, String whereClause) throws SQLException {
   init();
   Connection con = pool.getConnection();
   String query = "DELETE FROM " + table;
   if (!whereClause.isEmpty()) {
     query += " WHERE " + whereClause;
   }
   Statement st = con.createStatement();
   return (st.executeUpdate(query) != 0);
 }
 /**
  * Method that does a INSERT INTO <tt>table</tt> VALUES (<tt>values</tt>)
  *
  * @param values Array of strings containing the values to be inserted.
  * @param table Table where the values should be inserted into.
  * @return <b>True</b> if successful.
  * @throws SQLException
  */
 public static boolean insert(String[] values, String table) throws SQLException {
   init();
   Connection con = pool.getConnection();
   String query = "INSERT INTO " + table + " VALUES ('" + values[0] + "'";
   for (int i = 1; i < values.length; i++) {
     query += ", '" + values[i] + "'";
   }
   query += ")";
   Statement st = con.createStatement();
   return (st.executeUpdate(query) != 0);
 }
Example #12
0
 // Returns all existing coupons of a certain type
 @Override
 public Collection<Coupon> getCouponByType(CouponType couponType)
     throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
         ConnectionCloseException {
   // Establish connection
   Connection connection;
   try {
     connection = pool.getConnection();
   } catch (GetConnectionWaitInteruptedException e) {
     throw new WaitingForConnectionInterrupted();
   }
   // Prepare ArrayList to return
   ArrayList<Coupon> allCouponsFound = null;
   // Prepare and execute statement
   PreparedStatement statement = null;
   ResultSet couponsFound = null;
   // Prepare sql request
   String sqlRequest;
   try {
     sqlRequest = "SELECT * FROM APP.COUPON WHERE COUPON_TYPE='" + couponType + "'";
     statement = connection.prepareStatement(sqlRequest);
     // Get all coupons in a ResultSet
     couponsFound = statement.executeQuery();
     // Prepare Collection
     allCouponsFound = new ArrayList<Coupon>();
     // Move all coupons from ResultSet to an ArrayList
     while (couponsFound.next()) {
       // Prepare temp coupon
       Coupon tempCoupon = new Coupon();
       tempCoupon.setId(couponsFound.getLong("ID"));
       tempCoupon.setTitle(couponsFound.getString("TITLE"));
       tempCoupon.setStartDate(couponsFound.getDate("START_DATE"));
       tempCoupon.setEndDate(couponsFound.getDate("END_DATE"));
       tempCoupon.setAmount(couponsFound.getInt("AMOUNT"));
       tempCoupon.setType(CouponType.valueOf(couponsFound.getString("COUPON_TYPE")));
       tempCoupon.setMessage(couponsFound.getString("MESSAGE"));
       tempCoupon.setPrice(couponsFound.getDouble("PRICE"));
       // Add coupon to the Collection
       allCouponsFound.add(tempCoupon);
     }
   } catch (SQLException e) {
     throw new ClosedConnectionStatementCreationException();
   }
   // Close connections
   try {
     couponsFound.close();
     statement.close();
   } catch (SQLException e) {
     throw new ConnectionCloseException();
   }
   pool.returnConnection(connection);
   // returns NULL, when no coupons found
   return allCouponsFound;
 }
Example #13
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()
  public synchronized void delete() {
    // Drop main table and any other tables with the main table prefix

    // First get a list of the tables to drop
    ArrayList<String> tablesToDrop = new ArrayList<String>();

    Connection con;
    try {
      con = connectionPool.getConnection();
    } catch (ConnectionException e) {
      throw new BeanFactoryException(e);
    }

    try {
      Statement stmt = con.createStatement();
      if (printSQL != null) printDebug("deleteTable: SHOW TABLES");
      ResultSet rs = stmt.executeQuery("SHOW TABLES");

      while (rs.next()) {
        String s = rs.getString(1);
        if (File.separatorChar == '\\') {
          // It's windows...case insensitive matching
          String lower = s.toLowerCase();
          if (lower.equalsIgnoreCase(tableName)) tablesToDrop.add(s);
          if (lower.startsWith(tableName.toLowerCase() + "_")) tablesToDrop.add(s);
        } else {
          // It's Unix...case counts
          if (s.equals(tableName)) tablesToDrop.add(s);
          if (s.startsWith(tableName + "_")) tablesToDrop.add(s);
        }
      }

      rs.close();
      stmt.close();

      for (String name : tablesToDrop) {
        stmt = con.createStatement();
        String sql = "DROP TABLE " + name;
        if (printSQL != null) printDebug("deleteTable: " + sql);
        stmt.executeUpdate(sql);
        stmt.close();
      }

      connectionPool.releaseConnection(con);
    } catch (SQLException e) {
      try {
        con.close();
      } catch (SQLException e2) {
      }
      throw new BeanFactoryException(e);
    }
  }
 /**
  * Method that executes an update on a group of fields.
  *
  * @param fields String array which should contain the field and values to be updated as a key
  *     pair.
  * @param table The table to be updated.
  * @param whereClause Should the update use a where clause, it should be in the format <b>ID = 1
  *     AND SecondID = 2</b> and so on, regular SQL operations apply.
  * @return <b>True</b> if the update is successful.
  * @throws SQLException
  */
 public static boolean update(String[][] fields, String table, String whereClause)
     throws SQLException {
   init();
   Connection con = pool.getConnection();
   String query = "UPDATE " + table + " SET " + fields[0][0] + "='" + fields[0][1] + "'";
   for (int i = 1; i < fields.length; i++) {
     query += ", " + fields[i][0] + "='" + fields[i][1] + "'";
   }
   if (!whereClause.isEmpty()) {
     query += " WHERE " + whereClause;
   }
   Statement st = con.createStatement();
   return (st.executeUpdate(query) != 0);
 }
Example #16
0
 // Returns collection of all existing coupons
 @Override
 public Collection<Coupon> getAllCoupons()
     throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
         ConnectionCloseException {
   // Establish db connection
   Connection connection;
   try {
     connection = pool.getConnection();
   } catch (GetConnectionWaitInteruptedException e) {
     throw new WaitingForConnectionInterrupted();
   }
   // Prepare and execute SELECT
   Statement statement;
   ArrayList<Coupon> coupons;
   ResultSet rs;
   try {
     statement = connection.createStatement();
     coupons = new ArrayList<Coupon>();
     String sql = "SELECT * FROM APP.COUPON  ";
     rs = statement.executeQuery(sql);
     while (rs.next()) {
       Coupon coupon = new Coupon();
       coupon.setAmount(rs.getInt("AMOUNT"));
       coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
       coupon.setEndDate(rs.getDate("END_DATE"));
       coupon.setId(rs.getLong("ID"));
       coupon.setImage(rs.getString("IMAGE"));
       coupon.setMessage(rs.getString("MESSAGE"));
       coupon.setPrice(rs.getDouble("PRICE"));
       coupon.setTitle(rs.getString("TITLE"));
       coupon.setStartDate(rs.getDate("START_DATE"));
       coupons.add(coupon);
       // System.out.println(coupon.toString());
       coupons.add(coupon);
     }
   } catch (SQLException e) {
     throw new ClosedConnectionStatementCreationException();
   }
   // Close connections
   try {
     rs.close();
     statement.close();
   } catch (SQLException e) {
     throw new ConnectionCloseException();
   }
   pool.returnConnection(connection);
   return coupons;
 }
 /**
  * Method that selects and specific <tt>field</tt> on a <tt>table</tt> according to if present a
  * <tt>whereClause</tt>.
  *
  * @param fields <tt>String</tt> array which should contain the fields to be SELECTED.
  * @param table The table to be selected.
  * @param whereClause Should the SELECT use a where clause, it should be in the format <b>ID = 1
  *     AND SecondID = 2</b> and so on, regular SQL operations apply.
  * @return A <tt>ResultSet</tt> containing the result of the SELECT
  * @throws SQLException
  */
 public static ResultSet select(String[] fields, String table, String whereClause)
     throws SQLException {
   init();
   Connection con = pool.getConnection();
   String query = "SELECT " + fields[0];
   for (int i = 1; i < fields.length; i++) {
     query += ", " + fields[i];
   }
   query += " FROM " + table;
   if (!whereClause.isEmpty()) {
     query += " WHERE " + whereClause;
   }
   Statement st = con.createStatement();
   System.out.println(query);
   return st.executeQuery(query);
 }
  @Override
  protected void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    // get a connection
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();

    String sqlStatement = request.getParameter("sqlStatement");
    String sqlResult = "";
    try {
      // create a statement
      Statement statement = connection.createStatement();

      // parse the SQL string
      sqlStatement = sqlStatement.trim();
      if (sqlStatement.length() >= 6) {
        String sqlType = sqlStatement.substring(0, 6);
        if (sqlType.equalsIgnoreCase("select")) {
          // create the HTML for the result set
          ResultSet resultSet = statement.executeQuery(sqlStatement);
          sqlResult = SQLUtil.getHtmlTable(resultSet);
          resultSet.close();
        } else {
          int i = statement.executeUpdate(sqlStatement);
          if (i == 0) {
            sqlResult = "<p>The statement executed successfully.</p>";
          } else { // an INSERT, UPDATE, or DELETE statement
            sqlResult = "<p>The statement executed successfully.<br>" + i + " row(s) affected.</p>";
          }
        }
      }
      statement.close();
      connection.close();
    } catch (SQLException e) {
      sqlResult = "<p>Error executing the SQL statement: <br>" + e.getMessage() + "</p>";
    } finally {
      pool.freeConnection(connection);
    }

    HttpSession session = request.getSession();
    session.setAttribute("sqlResult", sqlResult);
    session.setAttribute("sqlStatement", sqlStatement);

    String url = "/index.jsp";
    getServletContext().getRequestDispatcher(url).forward(request, response);
  }
Example #19
0
 // Returns coupon by Title
 @Override
 public Coupon getCoupon(String title)
     throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
         ConnectionCloseException {
   Connection connection;
   try {
     connection = pool.getConnection();
   } catch (GetConnectionWaitInteruptedException e) {
     throw new WaitingForConnectionInterrupted();
   }
   // Prepare and execute coupon
   Statement statement;
   ResultSet rs;
   String sql;
   Coupon coupon = null;
   ;
   try {
     statement = connection.createStatement();
     // Prepare SQL message to get the Coupon by the id
     sql = "SELECT * FROM APP.COUPON WHERE TITLE='" + title + "'";
     // getting the values into a result set
     rs = statement.executeQuery(sql);
     coupon = new Coupon();
     if (rs.next()) {
       coupon.setAmount(rs.getInt("AMOUNT"));
       coupon.setId(rs.getLong("ID"));
       coupon.setImage(rs.getString("IMAGE"));
       coupon.setMessage(rs.getString("MESSAGE"));
       coupon.setPrice(rs.getDouble("PRICE"));
       coupon.setTitle(rs.getString("TITLE"));
       coupon.setEndDate(rs.getDate("END_DATE"));
       coupon.setStartDate(rs.getDate("START_DATE"));
       coupon.setType(CouponType.valueOf(rs.getString("COUPON_TYPE")));
     }
   } catch (SQLException e) {
     throw new ClosedConnectionStatementCreationException();
   }
   // Close connections
   try {
     rs.close();
     statement.close();
   } catch (SQLException e) {
     throw new ConnectionCloseException();
   }
   pool.returnConnection(connection);
   return coupon;
 }
 /**
  * Method that does a INSERT INTO <tt>table</tt> VALUES (<tt>values</tt>)
  *
  * @param fields Array of strings containing the key pairs {{COLUMN, VALUE},{COLUMN, VALUE}} to be
  *     inserted.
  * @param table Table where the values should be inserted into.
  * @return <b>True</b> if successful.
  * @throws SQLException
  */
 public static boolean insert(String[][] fields, String table) throws SQLException {
   init();
   Connection con = pool.getConnection();
   String query = "INSERT INTO " + table + "";
   String columns = " (" + fields[0][0] + "";
   String values = " ('" + fields[0][1] + "'";
   for (int i = 1; i < fields.length; i++) {
     columns += ", " + fields[i][0];
     values += ", '" + fields[i][1] + "'";
   }
   columns += ")";
   values += ")";
   query += columns + " VALUES" + values;
   Statement st = con.createStatement();
   System.out.println(query);
   return (st.executeUpdate(query) != 0);
 }
  private String[] getPrimaryKeyNamesFromTable() {
    Connection con;
    try {
      con = connectionPool.getConnection();
    } catch (ConnectionException e) {
      throw new BeanFactoryException(e);
    }

    try {
      Statement stmt = con.createStatement();
      String sql = "DESCRIBE " + tableName;
      if (printSQL != null) printDebug("getPrimaryKeyNamesFromTable: " + sql);
      ResultSet rs = stmt.executeQuery(sql);

      ArrayList<String> list = new ArrayList<String>();
      while (rs.next()) {
        String name = rs.getString(1);
        // String sqlType = rs.getString(2);
        // boolean nonNull = !rs.getBoolean(3);
        boolean primaryKey = rs.getString(4).equalsIgnoreCase("PRI");
        if (primaryKey) {
          if (!name.contains(Property.META_SEPARATOR)) {
            list.add(name);
          } else {
            String prefix = name.substring(0, name.indexOf(Property.META_SEPARATOR));
            if (!list.contains(prefix)) list.add(prefix);
          }
        }
      }

      stmt.close();
      connectionPool.releaseConnection(con);

      // Don't check anymore now that tables with only one row and no primary key are allowed.
      //          if (list.size() == 0) throw new BeanFactoryException("Could not find any primary
      // key in table: "+tableName);

      return list.toArray(new String[list.size()]);
    } catch (SQLException e) {
      try {
        con.close();
      } catch (SQLException e2) {
      }
      throw new BeanFactoryException(e);
    }
  }
Example #22
0
 // Update existing coupon
 @Override
 public void updateCoupon(Coupon coupon)
     throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
         ConnectionCloseException {
   // Establish connection
   Connection connection;
   try {
     connection = pool.getConnection();
   } catch (GetConnectionWaitInteruptedException e) {
     throw new WaitingForConnectionInterrupted();
   }
   PreparedStatement preparedStatement;
   // Prepare and execute the update
   try {
     // Prepare SQL message to remove the Coupon
     String updateSQL =
         "UPDATE APP.COUPON SET "
             + "AMOUNT=?,MESSAGE=?,PRICE=?,TITLE=?,END_DATE=?,START_DATE=?,IMAGE=?,COUPON_TYPE=? "
             + "WHERE ID=?";
     // Prepare statement
     preparedStatement = connection.prepareStatement(updateSQL);
     preparedStatement.setInt(1, coupon.getAmount());
     preparedStatement.setString(2, coupon.getMessage());
     preparedStatement.setDouble(3, coupon.getPrice());
     preparedStatement.setString(4, coupon.getTitle());
     preparedStatement.setDate(5, (java.sql.Date) coupon.getEndDate());
     preparedStatement.setDate(6, (java.sql.Date) coupon.getStartDate());
     preparedStatement.setString(7, coupon.getImage());
     preparedStatement.setString(8, coupon.getType().name());
     preparedStatement.setLong(9, coupon.getId());
     // update the Coupon
     preparedStatement.execute();
     // Log
     System.out.println(coupon.toString() + " was updated");
   } catch (SQLException e) {
     throw new ClosedConnectionStatementCreationException();
   }
   // Close Connections
   try {
     preparedStatement.close();
   } catch (SQLException e) {
     throw new ConnectionCloseException();
   }
   pool.returnConnection(connection);
 }
Example #23
0
 // Removes relevant rows from CUSTOMER_COUPON, COMPANY_COUPON as well as coupon itself
 @Override
 public void removeCoupon(Coupon coupon)
     throws WaitingForConnectionInterrupted, ClosedConnectionStatementCreationException,
         ConnectionCloseException {
   Connection connection;
   try {
     connection = pool.getConnection();
   } catch (GetConnectionWaitInteruptedException e) {
     throw new WaitingForConnectionInterrupted();
   }
   // Get coupon ID from DB
   Statement statement;
   ResultSet idFound;
   try {
     statement = connection.createStatement();
     String sqlRequest = "SELECT ID FROM APP.COUPON WHERE TITLE='" + coupon.getTitle() + "'";
     idFound = statement.executeQuery(sqlRequest);
     idFound.next();
     // coupon.setId(idFound.getLong("ID"));
     // Prepare message to remove from purchase history
     String removeSQL = "DELETE FROM APP.CUSTOMER_COUPON WHERE COUPON_ID =" + coupon.getId();
     // Remove coupon from purchase history
     statement.execute(removeSQL);
     // Prepare message to remove from company's coupons
     removeSQL = "DELETE FROM APP.COMPANY_COUPON WHERE COUPON_ID=" + coupon.getId();
     // Remove coupon from company
     statement.execute(removeSQL);
     // Prepare SQL message to remove the Coupon
     removeSQL = "DELETE FROM APP.COUPON WHERE ID=" + coupon.getId();
     // Remove the Coupon himself
     statement.execute(removeSQL);
     System.out.println(coupon.toString() + " was deleted");
   } catch (SQLException e) {
     throw new ClosedConnectionStatementCreationException();
   }
   // Close connections
   try {
     idFound.close();
     statement.close();
   } catch (SQLException e) {
     throw new ConnectionCloseException();
   }
   pool.returnConnection(connection);
 }
 /** checks database for this user */
 private boolean realAuthentication(HttpServletRequest request, ConnectionPool conPool)
     throws SQLException {
   // user authentication is required!
   User user = (User) request.getSession().getAttribute(StringInterface.USERATTR);
   if (user == null) {
     return false;
   }
   Connection con = conPool.getConnection();
   boolean authenticated = false;
   try {
     authenticated = userUtils.confirmUserWithEncrypted(user.getID(), user.getEncrypted(), con);
   } catch (Exception e) {
     throw new SQLException(e.getMessage());
   } finally {
     conPool.free(con);
     con = null;
   }
   return authenticated;
 } // end realAuthentication
Example #25
0
  public static void eliminar(int id) {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    CallableStatement cs = null;
    try {
      cs = connection.prepareCall("{ call UsuarioBorrar(?, ?) }");
      cs.setInt(1, id);
      cs.registerOutParameter(2, Types.BIT);
      cs.execute();
      byte res = cs.getByte(2);
      System.out.println("[Borrar] Resultado=" + res);

    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      DBUtil.closeStatement(cs);
      pool.freeConnection(connection);
    }
  }
 /**
  * Method that does a INSERT INTO <tt>table</tt> VALUES (<tt>values</tt>) and returns the new
  * generated key for the values inserted.
  *
  * @param fields Array of strings containing the key pairs {{COLUMN, VALUE},{COLUMN, VALUE}} to be
  *     inserted.
  * @param table Table where the values should be inserted into.
  * @return a ResultSet containing the generated keys.
  * @throws SQLException
  */
 public static int insertAndGetKey(String[][] fields, String table) throws SQLException {
   init();
   Connection con = pool.getConnection();
   String query = "INSERT INTO `" + table + "`";
   String columns = "(`" + fields[0][0] + "`";
   String values = " (" + fields[0][1] + "";
   for (int i = 1; i < fields.length; i++) {
     columns += ", `" + fields[i][0] + "`";
     values += "," + fields[i][1] + "";
   }
   columns += ")";
   values += ")";
   query += columns + " VALUES" + values;
   PreparedStatement st = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
   st.executeUpdate();
   ResultSet rs = st.getGeneratedKeys();
   rs.next();
   return rs.getInt(1);
 }
Example #27
0
  public static void insertar(Usuario U) {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    CallableStatement cs = null;
    try {
      cs = connection.prepareCall("{ call UsuarioInsertar(?, ?, ?, ?, ?) }");
      cs.setString(1, U.getNombreUsuario());
      cs.setString(2, U.getApellidoPaterno());
      cs.setString(3, U.getApellidoMterno());

      cs.execute();

    } catch (Exception ex) {
      ex.printStackTrace();

    } finally {
      DBUtil.closeStatement(cs);
      pool.freeConnection(connection);
    }
  }
Example #28
0
 private static synchronized Connection getConnect() throws SQLException {
   if (connectionPool == null) {
     connectionPool = new ConnectionPool(driverClassName, url, username, password);
     connectionPool.setTestTable("dual");
     if (initConn > 0 && incConn > 0 && maxConn > 0 && maxConn > initConn) {
       connectionPool.setInitialConnections(initConn);
       connectionPool.setIncrementalConnections(incConn);
       ;
       connectionPool.setMaxConnections(maxConn);
       ;
     }
     try {
       connectionPool.createPool();
     } catch (Exception e) {
       logger.fatal("can not create connection pool!system will exit..", e);
       System.exit(1);
     }
   }
   return connectionPool.getConnection();
 }
  public static ArrayList<Study> getOpenAndNotParticipated(String emailAddress) {

    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    String query =
        "SELECT * FROM study WHERE NOT EXISTS(SELECT * FROM answer WHERE study.code = answer.code AND email = ?)"
            + "AND status = ? ";
    try {
      ps = connection.prepareStatement(query);
      ps.setString(1, emailAddress);
      ps.setString(2, "open");
      rs = ps.executeQuery();
      Study study = null;
      ArrayList<Study> studies = new ArrayList<Study>();
      while (rs.next()) {
        study = new Study();
        study.setName(rs.getString("name"));
        study.setCode(rs.getString("code"));
        study.setDescription(rs.getString("description"));
        study.setCreatorEmail(rs.getString("creatorEmail"));
        study.setDateCreated(rs.getTimestamp("dateCreated"));
        study.setQuestion(rs.getString("question"));
        study.setRequestedParticipants(rs.getInt("requestedParticipants"));
        study.setNumOfParticipants(rs.getInt("numOfParticipants"));
        study.setStatus(rs.getString("status"));
        study.setImageURL(rs.getString("imageURL"));
        studies.add(study);
      }
      return studies;
    } catch (SQLException e) {
      System.out.println(e);
      return null;
    } finally {
      DBUtil.closeResultSet(rs);
      DBUtil.closePreparedStatement(ps);
      pool.freeConnection(connection);
    }
  }
Example #30
0
  // =============================================================
  public static boolean UserExists(String username) {
    ConnectionPool pool = ConnectionPool.getInstance();
    Connection connection = pool.getConnection();
    PreparedStatement ps = null;
    ResultSet rs = null;

    String query = "SELECT username FROM user_info " + "WHERE username = ?";
    try {
      ps = connection.prepareStatement(query);
      ps.setString(1, username);
      rs = ps.executeQuery();
      return rs.next();
    } catch (SQLException e) {
      e.printStackTrace();
      return false;
    } finally {
      DBUtil.closeResultSet(rs);
      DBUtil.closePreparedStatement(ps);
      pool.freeConnection(connection);
    }
  }