/** Populates a DTO with data from a ResultSet */ protected void populateDto(Projects dto, ResultSet rs) throws SQLException { dto.setIdprojects(rs.getInt(COLUMN_IDPROJECTS)); dto.setName(rs.getString(COLUMN_NAME)); dto.setDescription(rs.getString(COLUMN_DESCRIPTION)); dto.setStartDate(rs.getDate(COLUMN_START_DATE)); dto.setEndDate(rs.getDate(COLUMN_END_DATE)); dto.setStatus(rs.getInt(COLUMN_STATUS)); if (rs.wasNull()) { dto.setStatusNull(true); } dto.setType(rs.getInt(COLUMN_TYPE)); if (rs.wasNull()) { dto.setTypeNull(true); } dto.setCompanyId(rs.getInt(COLUMN_COMPANY_ID)); if (rs.wasNull()) { dto.setCompanyIdNull(true); } dto.setBranchId(rs.getInt(COLUMN_BRANCH_ID)); if (rs.wasNull()) { dto.setBranchIdNull(true); } }
/** Inserts a new row in the projects table. */ public ProjectsPk insert(Projects dto) throws ProjectsDaoException { long t1 = System.currentTimeMillis(); // declare variables final boolean isConnSupplied = (userConn != null); Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { // get the user-specified connection or get a connection from the ResourceManager conn = isConnSupplied ? userConn : ResourceManager.getConnection(); stmt = conn.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS); int index = 1; stmt.setInt(index++, dto.getIdprojects()); stmt.setString(index++, dto.getName()); stmt.setString(index++, dto.getDescription()); stmt.setDate( index++, dto.getStartDate() == null ? null : new java.sql.Date(dto.getStartDate().getTime())); stmt.setDate( index++, dto.getEndDate() == null ? null : new java.sql.Date(dto.getEndDate().getTime())); if (dto.isStatusNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getStatus()); } if (dto.isTypeNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getType()); } if (dto.isCompanyIdNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getCompanyId()); } if (dto.isBranchIdNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getBranchId()); } System.out.println("Executing " + SQL_INSERT + " with DTO: " + dto); int rows = stmt.executeUpdate(); long t2 = System.currentTimeMillis(); System.out.println(rows + " rows affected (" + (t2 - t1) + " ms)"); // retrieve values from auto-increment columns rs = stmt.getGeneratedKeys(); if (rs != null && rs.next()) { dto.setIdprojects(rs.getInt(1)); } reset(dto); return dto.createPk(); } catch (Exception _e) { _e.printStackTrace(); throw new ProjectsDaoException("Exception: " + _e.getMessage(), _e); } finally { ResourceManager.close(stmt); if (!isConnSupplied) { ResourceManager.close(conn); } } }
/** Updates a single row in the projects table. */ public void update(ProjectsPk pk, Projects dto) throws ProjectsDaoException { long t1 = System.currentTimeMillis(); // declare variables final boolean isConnSupplied = (userConn != null); Connection conn = null; PreparedStatement stmt = null; try { // get the user-specified connection or get a connection from the ResourceManager conn = isConnSupplied ? userConn : ResourceManager.getConnection(); System.out.println("Executing " + SQL_UPDATE + " with DTO: " + dto); stmt = conn.prepareStatement(SQL_UPDATE); int index = 1; stmt.setInt(index++, dto.getIdprojects()); stmt.setString(index++, dto.getName()); stmt.setString(index++, dto.getDescription()); stmt.setDate( index++, dto.getStartDate() == null ? null : new java.sql.Date(dto.getStartDate().getTime())); stmt.setDate( index++, dto.getEndDate() == null ? null : new java.sql.Date(dto.getEndDate().getTime())); if (dto.isStatusNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getStatus()); } if (dto.isTypeNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getType()); } if (dto.isCompanyIdNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getCompanyId()); } if (dto.isBranchIdNull()) { stmt.setNull(index++, java.sql.Types.INTEGER); } else { stmt.setInt(index++, dto.getBranchId()); } stmt.setInt(10, pk.getIdprojects()); int rows = stmt.executeUpdate(); reset(dto); long t2 = System.currentTimeMillis(); System.out.println(rows + " rows affected (" + (t2 - t1) + " ms)"); } catch (Exception _e) { _e.printStackTrace(); throw new ProjectsDaoException("Exception: " + _e.getMessage(), _e); } finally { ResourceManager.close(stmt); if (!isConnSupplied) { ResourceManager.close(conn); } } }