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); } }
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); } }
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); } }
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); } }
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); } }
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); } }
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); } }
@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); }
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); } }
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); } }
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); } }
// ============================================================= 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); } }
public static void actualizar(Usuario u) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; try { cs = connection.prepareCall("{ call actualizar_usuario(?, ?, ?, ?, ?, ?) }"); cs.setString(1, u.getNombreUsuario()); cs.setString(2, u.getApellidoPaterno()); cs.setString(3, u.getApellidoMterno()); cs.setByte(4, u.getSexo()); cs.setInt(5, u.getSucursal().getIdSucursal()); cs.setInt(6, u.getIdUsuario()); cs.execute(); } catch (Exception ex) { ex.printStackTrace(); } finally { DBUtil.closeStatement(cs); pool.freeConnection(connection); } }
public static void insertarVentaVenta(Venta v, int cantidad) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { cs = connection.prepareCall("{ call insertVentaVenta(?, ?, ?, ?, ?) }"); cs.setInt(1, v.getIdVenta()); cs.setInt(2, v.getCantidadVenta()); cs.setInt(3, v.getArticuloVenta().getIdArticulo()); cs.setInt(4, v.getDepartamentoVenta().getIdDepartamento()); cs.setInt(5, cantidad); cs.executeQuery(); } catch (Exception ex) { ex.printStackTrace(); } finally { DBUtil.closeStatement(cs); pool.freeConnection(connection); } }
public static UserInfo selectUser(String username) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; ResultSet rs = null; String query = "SELECT * FROM user_info " + "WHERE username = ?"; try { ps = connection.prepareStatement(query); ps.setString(1, username); rs = ps.executeQuery(); UserInfo user = null; 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")); } return user; } catch (SQLException e) { e.printStackTrace(); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } }
public static Study getStudy(String studyCode) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; ResultSet rs = null; String query = "SELECT * FROM study " + "WHERE code = ?"; try { ps = connection.prepareStatement(query); ps.setString(1, studyCode); rs = ps.executeQuery(); Study study = null; if (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")); } return study; } catch (SQLException e) { System.out.println(e); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } }
// ----- public static int insert(UserInfo user) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); PreparedStatement ps = null; ResultSet rs = null; // This method adds a new record to the Users table in the database String query = "INSERT INTO user_info (firstName, lastName, address, affiliation, " + "memDur, username, password, cardType, cardNumber, email) " + "VALUES (?, ?, ?, ?, " + "?, ?, ?, ?, ?, ?)"; try { ps = connection.prepareStatement(query); ps.setString(1, user.getFirstName()); ps.setString(2, user.getLastName()); ps.setString(3, user.getAddress()); ps.setString(4, user.getAffiliation()); ps.setString(5, user.getMemDur()); ps.setString(6, user.getUsername()); ps.setString(7, user.getPassword()); ps.setString(8, user.getCardType()); ps.setString(9, user.getCardNumber()); ps.setString(10, user.getEmail()); return ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); return 0; } finally { DBUtil.closeResultSet(rs); DBUtil.closePreparedStatement(ps); pool.freeConnection(connection); } }
public static List<Venta> buscaVentas( String fechaInicial, String fechaFinal, int idPagoV, int idDepartamento, int idSucursalV, int idUsuarioV) { ConnectionPool pool = ConnectionPool.getInstance(); Connection connection = pool.getConnection(); CallableStatement cs = null; ResultSet rs = null; try { List<Venta> ventas = new ArrayList(); cs = connection.prepareCall("{ call reporteVenta(?,?,?,?,?,?) }"); cs.setString(1, fechaInicial); cs.setString(2, fechaFinal); cs.setInt(3, idUsuarioV); cs.setInt(4, idSucursalV); cs.setInt(5, idDepartamento); cs.setInt(6, idPagoV); rs = cs.executeQuery(); while (rs.next()) { Venta ven = new Venta( rs.getInt("cantidadVenta"), rs.getDouble("subtotal"), rs.getString("fechaVenta")); Sucursal suc = new Sucursal(rs.getInt("idSucursalVenta"), rs.getString("nombreSucursal")); Departamento dep = new Departamento(rs.getInt("idDepartamentoVenta"), rs.getString("nombreDepartamento")); Usuario usu = new Usuario(rs.getInt("idUsuarioVenta"), rs.getString("nombreUsuario")); Pago pag = new Pago(rs.getInt("idMetodoPagoVenta"), rs.getString("nombreMetodoPago")); Articulo art = new Articulo( rs.getInt("idArticuloVenta"), rs.getString("descripcionCorta"), rs.getString("descripcionLarga"), rs.getString("codigoArticulo"), rs.getFloat("precioPublico"), rs.getFloat("descuento"), rs.getFloat("impuestos")); ven.setSucursalVenta(suc); ven.setDepartamentoVenta(dep); ven.setUsuarioVenta(usu); ven.setPagoVenta(pag); ven.setArticuloVenta(art); ventas.add(ven); } return ventas; } catch (Exception ex) { ex.printStackTrace(); return null; } finally { DBUtil.closeResultSet(rs); DBUtil.closeStatement(cs); pool.freeConnection(connection); } }