Java tutorial
/** * Este arquivo parte do Biblivre3. * * Biblivre3 um software livre; voc pode redistribu-lo e/ou * modific-lo dentro dos termos da Licena Pblica Geral GNU como * publicada pela Fundao do Software Livre (FSF); na verso 3 da * Licena, ou (caso queira) qualquer verso posterior. * * Este programa distribudo na esperana de que possa ser til, * mas SEM NENHUMA GARANTIA; nem mesmo a garantia implcita de * MERCANTIBILIDADE OU ADEQUAO PARA UM FIM PARTICULAR. Veja a * Licena Pblica Geral GNU para maiores detalhes. * * Voc deve ter recebido uma cpia da Licena Pblica Geral GNU junto * com este programa, Se no, veja em <http://www.gnu.org/licenses/>. * * @author Alberto Wagner <alberto@biblivre.org.br> * @author Danniel Willian <danniel@biblivre.org.br> * */ package biblivre3.acquisition.request; import mercury.*; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import org.apache.commons.lang.StringUtils; public class RequestDAO extends DAO { public static final int REQUEST_PENDING = 1; public static final int REQUEST_CLOSED = 2; public static final int REQUEST_BOTH = 3; public boolean insertRequest(RequestDTO dto) { Connection conInsert = null; try { conInsert = getDataSource().getConnection(); final String sqlInsert = " INSERT INTO acquisition_requisition ( " + " requisition_date, responsable, author_type, " + " author, num_prename, author_title, item_title, item_subtitle, " + " edition_number, publisher, obs, status, requester, quantity) " + " VALUES (localtimestamp, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '0', ?, ?); "; PreparedStatement pstInsert = conInsert.prepareStatement(sqlInsert); pstInsert.setString(1, dto.getUser()); pstInsert.setString(2, dto.getAuthorType()); pstInsert.setString(3, dto.getAuthor()); pstInsert.setString(4, dto.getAuthorNumeration()); pstInsert.setString(5, dto.getAuthorTitle()); pstInsert.setString(6, dto.getTitle()); pstInsert.setString(7, dto.getSubtitle()); pstInsert.setString(8, dto.getEditionNumber()); pstInsert.setString(9, dto.getPublisher()); pstInsert.setString(10, dto.getObs()); pstInsert.setString(11, dto.getRequester()); pstInsert.setInt(12, dto.getQuantity()); return pstInsert.executeUpdate() > 0; } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(conInsert); } } public ArrayList<RequestDTO> listRequests(String status, int offset, int limit) { ArrayList<RequestDTO> requestList = new ArrayList<RequestDTO>(); Connection con = null; try { con = getDataSource().getConnection(); boolean setStatus = StringUtils.isNotBlank(status) && (status.equals("0") || status.equals("1")); StringBuilder sql = new StringBuilder(" SELECT * FROM acquisition_requisition "); if (setStatus) { sql.append(" WHERE status = ? "); } sql.append(" ORDER BY serial_requisition ASC offset ? limit ? "); final PreparedStatement pst = con.prepareStatement(sql.toString()); int i = 1; if (setStatus) { pst.setString(i++, status); } pst.setInt(i++, offset); pst.setInt(i++, limit); final ResultSet rs = pst.executeQuery(); if (rs == null) { return requestList; } while (rs.next()) { RequestDTO dto = this.populateDto(rs); requestList.add(dto); } } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(con); } return requestList; } public boolean updateRequest(RequestDTO dto) { Connection conInsert = null; try { conInsert = getDataSource().getConnection(); final String sqlInsert = " UPDATE acquisition_requisition " + " SET author_type = ?, " + " author = ?, num_prename = ?, author_title = ?, item_title = ?, " + " item_subtitle = ?, edition_number = ?, publisher = ?, obs = ?, " + " requester = ?, quantity = ? " + " WHERE serial_requisition = ?;"; PreparedStatement pstInsert = conInsert.prepareStatement(sqlInsert); pstInsert.setString(1, dto.getAuthorType()); pstInsert.setString(2, dto.getAuthor()); pstInsert.setString(3, dto.getAuthorNumeration()); pstInsert.setString(4, dto.getAuthorTitle()); pstInsert.setString(5, dto.getTitle()); pstInsert.setString(6, dto.getSubtitle()); pstInsert.setString(7, dto.getEditionNumber()); pstInsert.setString(8, dto.getPublisher()); pstInsert.setString(9, dto.getObs()); pstInsert.setString(10, dto.getRequester()); pstInsert.setInt(11, dto.getQuantity()); pstInsert.setInt(12, dto.getSerial()); return pstInsert.executeUpdate() > 0; } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(conInsert); } } public boolean updateRequestStatus(Integer buyOrderSerial, String status) { Connection conInsert = null; try { conInsert = getDataSource().getConnection(); final String sqlInsert = " UPDATE acquisition_requisition SET status = ? " + " WHERE serial_requisition IN (" + " SELECT r.serial_requisition FROM acquisition_requisition r " + " INNER JOIN acquisition_item_quotation i " + " ON i.serial_requisition = r.serial_requisition " + " INNER JOIN acquisition_quotation q " + " ON q.serial_quotation = i.serial_quotation " + " INNER JOIN acquisition_order o " + " ON o.serial_quotation = q.serial_quotation " + " WHERE o.serial_order = ?); "; PreparedStatement pstInsert = conInsert.prepareStatement(sqlInsert); pstInsert.setString(1, status); pstInsert.setInt(2, buyOrderSerial); pstInsert.executeUpdate(); } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(conInsert); } return true; } public boolean deleteRequest(RequestDTO dto) { Connection conInsert = null; try { conInsert = getDataSource().getConnection(); final String sqlInsert = " DELETE FROM acquisition_requisition " + " WHERE serial_requisition = ?; "; PreparedStatement pstInsert = conInsert.prepareStatement(sqlInsert); pstInsert.setInt(1, dto.getSerial()); return pstInsert.executeUpdate() > 0; } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(conInsert); } } public ArrayList<RequestDTO> searchRequest(RequestDTO example, int offset, int limit) { ArrayList<RequestDTO> requestList = new ArrayList<RequestDTO>(); Connection con = null; try { con = getDataSource().getConnection(); final StringBuilder sql = new StringBuilder(" SELECT * FROM acquisition_requisition "); if (example.getSerial() != null && example.getSerial() != 0) { sql.append(" WHERE serial_requisition = ? "); } else if (StringUtils.isNotBlank(example.getRequester())) { sql.append(" WHERE requester ilike ? "); } else if (example.getRequestDate() != null) { sql.append(" WHERE requisition_date = ? "); } else if (StringUtils.isNotBlank(example.getAuthor())) { sql.append(" WHERE author ilike ? "); } else if (StringUtils.isNotBlank(example.getTitle())) { sql.append(" WHERE item_title ilike ? "); } if (StringUtils.isNotBlank(example.getStatus())) { if (example.getStatus().equals("0") || example.getStatus().equals("1")) { if (sql.toString().contains("WHERE")) { sql.append(" AND status = ? "); } else { sql.append(" WHERE status = ? "); } } } sql.append(" ORDER BY serial_requisition ASC offset ? limit ? "); final PreparedStatement pst = con.prepareStatement(sql.toString()); int i = 1; if (example.getSerial() != null && example.getSerial() != 0) { pst.setInt(i++, example.getSerial()); } else if (StringUtils.isNotBlank(example.getRequester())) { pst.setString(i++, "%" + example.getRequester() + "%"); } else if (example.getRequestDate() != null) { pst.setDate(i++, new java.sql.Date(example.getRequestDate().getTime())); } else if (StringUtils.isNotBlank(example.getAuthor())) { pst.setString(i++, "%" + example.getAuthor() + "%"); } else if (StringUtils.isNotBlank(example.getTitle())) { pst.setString(i++, "%" + example.getTitle() + "%"); } if (StringUtils.isNotBlank(example.getStatus())) { if (example.getStatus().equals("0") || example.getStatus().equals("1")) { pst.setString(i++, example.getStatus()); } } pst.setInt(i++, offset); pst.setInt(i++, limit); final ResultSet rs = pst.executeQuery(); if (rs == null) { return requestList; } while (rs.next()) { RequestDTO dto = this.populateDto(rs); requestList.add(dto); } } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(con); } return requestList; } public int getSearchCount(RequestDTO example) { Connection con = null; try { con = getDataSource().getConnection(); final StringBuilder sql = new StringBuilder(" SELECT COUNT(*) FROM acquisition_requisition "); if (example.getSerial() != null && example.getSerial() != 0) { sql.append(" WHERE serial_requisition = ? "); } else if (StringUtils.isNotBlank(example.getRequester())) { sql.append(" WHERE requester ilike ? "); } else if (example.getRequestDate() != null) { sql.append(" WHERE requisition_date = ? "); } else if (StringUtils.isNotBlank(example.getAuthor())) { sql.append(" WHERE author ilike ? "); } else if (StringUtils.isNotBlank(example.getTitle())) { sql.append(" WHERE item_title ilike ? "); } if (StringUtils.isNotBlank(example.getStatus())) { if (example.getStatus().equals("0") || example.getStatus().equals("1")) { if (sql.toString().contains("WHERE")) { sql.append(" AND status = ? "); } else { sql.append(" WHERE status = ? "); } } } final PreparedStatement pst = con.prepareStatement(sql.toString()); int i = 1; if (example.getSerial() != null && example.getSerial() != 0) { pst.setInt(i++, example.getSerial()); } else if (StringUtils.isNotBlank(example.getRequester())) { pst.setString(i++, "%" + example.getRequester() + "%"); } else if (example.getRequestDate() != null) { pst.setDate(i++, new java.sql.Date(example.getRequestDate().getTime())); } else if (StringUtils.isNotBlank(example.getAuthor())) { pst.setString(i++, "%" + example.getAuthor() + "%"); } else if (StringUtils.isNotBlank(example.getTitle())) { pst.setString(i++, "%" + example.getTitle() + "%"); } if (StringUtils.isNotBlank(example.getStatus())) { if (example.getStatus().equals("0") || example.getStatus().equals("1")) { pst.setString(i++, example.getStatus()); } } final ResultSet rs = pst.executeQuery(); if (rs == null) { return 0; } while (rs.next()) { return rs.getInt(1); } } catch (Exception e) { log.error(e.getMessage(), e); throw new DAOException(e.getMessage()); } finally { closeConnection(con); } return 0; } private RequestDTO populateDto(ResultSet rs) throws Exception { final RequestDTO dto = new RequestDTO(); dto.setSerial(rs.getInt("serial_requisition")); dto.setRequestDate(rs.getDate("requisition_date")); dto.setUser(rs.getString("responsable")); dto.setAuthorType(rs.getString("author_type")); dto.setAuthor(rs.getString("author")); dto.setAuthorNumeration(rs.getString("num_prename")); dto.setAuthorTitle(rs.getString("author_title")); dto.setTitle(rs.getString("item_title")); dto.setSubtitle(rs.getString("item_subtitle")); dto.setEditionNumber(rs.getString("edition_number")); dto.setPublisher(rs.getString("publisher")); dto.setObs(rs.getString("obs")); dto.setStatus(rs.getString("status")); dto.setRequester(rs.getString("requester")); dto.setQuantity(rs.getInt("quantity")); return dto; } public int getTotalNroRecords() { Connection con = null; try { con = getDataSource().getConnection(); final Statement st = con.createStatement(); final String query = "SELECT count(*) FROM acquisition_requisition;"; final ResultSet rs = st.executeQuery(query); if (rs.next()) { return rs.getInt(1); } return 0; } catch (Exception e) { log.error(e.getMessage(), e); throw new ExceptionUser("REQUEST_DAO_EXCEPTION"); } finally { closeConnection(con); } } }