org.springframework.jdbc.core.simple.ParameterizedRowMapper Java Examples

/**
 * 取分页
 * 
 * @param jt
 *            jdbcTemplate
 * @param sqlCountRows
 *            查询总数的SQL
 * @param sqlFetchRows
 *            查询数据的sql
 * @param args
 *            查询参数
 * @param pageNo
 *            页数
 * @param pageSize
 *            每页大小
 * @param rowMapper
 * @return
 */
public Page<E> fetchPage(final JdbcTemplate jt, final String sqlCountRows, final String sqlFetchRows,
        final Object args[], final int pageNo, final int pageSize, final ParameterizedRowMapper<E> rowMapper) {
    if (pageSize == 0) {
        return null;
    }

    // 查询当前记录总数
    final int rowCount = jt.queryForInt(sqlCountRows, args);

    // 计算页数
    int pageCount = rowCount / pageSize;
    if (rowCount > pageSize * pageCount) {
        pageCount++;
    }

    // 创建Page对象
    final Page<E> page = new Page<E>();
    page.setPageNumber(pageNo);
    page.setPagesAvailable(pageCount);
    page.setTotalCount(rowCount);

    if (pageNo > pageCount)
        return null;
    // 取单页数据，计算起始位置
    final int startRow = (pageNo - 1) * pageSize;
    // TODO 在数据量很大时， limit效率很低
    final String selectSQL = sqlFetchRows + " limit " + startRow + "," + pageSize;
    jt.query(selectSQL, args, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            final List<E> pageItems = page.getPageItems();
            int currentRow = 0;
            while (rs.next()) {
                pageItems.add(rowMapper.mapRow(rs, currentRow++));
            }
            return page;
        }
    });
    return page;
}
 

/**
 * 取分页
 * 
 * @param jt
 *            jdbcTemplate
 * @param sqlCountRows
 *            查询总数的SQL
 * @param sqlFetchRows
 *            查询数据的sql
 * @param args
 *            查询参数
 * @param pageNo
 *            页数
 * @param pageSize
 *            每页大小
 * @param rowMapper
 * @return
 */
public Page<E> fetchPage(final JdbcTemplate jt, final String sqlCountRows, final String sqlFetchRows,
        final Object args[], final int pageNo, final int pageSize, final ParameterizedRowMapper<E> rowMapper) {
    if (pageSize == 0) {
        return null;
    }

    // 查询当前记录总数
    final int rowCount = jt.queryForInt(sqlCountRows, args);

    // 计算页数
    int pageCount = rowCount / pageSize;
    if (rowCount > pageSize * pageCount) {
        pageCount++;
    }

    // 创建Page对象
    final Page<E> page = new Page<E>();
    page.setPageNumber(pageNo);
    page.setPagesAvailable(pageCount);
    page.setTotalCount(rowCount);

    if (pageNo > pageCount)
        return null;
    // 取单页数据，计算起始位置
    final int startRow = (pageNo - 1) * pageSize;
    // TODO 在数据量很大时， limit效率很低
    final String selectSQL = sqlFetchRows + " limit " + startRow + "," + pageSize;
    jt.query(selectSQL, args, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            final List<E> pageItems = page.getPageItems();
            int currentRow = 0;
            while (rs.next()) {
                pageItems.add(rowMapper.mapRow(rs, currentRow++));
            }
            return page;
        }
    });
    return page;
}
 

/**
 * Refresh the cache of Vets that the Clinic is holding.
 * @see org.springframework.samples.petclinic.Clinic#getVets()
 */
@ManagedOperation
@Transactional(readOnly = true)
public void refreshVetsCache() throws DataAccessException {
	synchronized (this.vets) {
		this.logger.info("Refreshing vets cache");

		// Retrieve the list of all vets.
		this.vets.clear();
		this.vets.addAll(this.simpleJdbcTemplate.query(
				"SELECT id, first_name, last_name FROM vets ORDER BY last_name,first_name",
				ParameterizedBeanPropertyRowMapper.newInstance(Vet.class)));

		// Retrieve the list of all possible specialties.
		final List<Specialty> specialties = this.simpleJdbcTemplate.query(
				"SELECT id, name FROM specialties",
				ParameterizedBeanPropertyRowMapper.newInstance(Specialty.class));

		// Build each vet's list of specialties.
		for (Vet vet : this.vets) {
			final List<Integer> vetSpecialtiesIds = this.simpleJdbcTemplate.query(
					"SELECT specialty_id FROM vet_specialties WHERE vet_id=?",
					new ParameterizedRowMapper<Integer>() {
						public Integer mapRow(ResultSet rs, int row) throws SQLException {
							return Integer.valueOf(rs.getInt(1));
						}},
					vet.getId().intValue());
			for (int specialtyId : vetSpecialtiesIds) {
				Specialty specialty = EntityUtils.getById(specialties, Specialty.class, specialtyId);
				vet.addSpecialty(specialty);
			}
		}
	}
}
 

@Transactional(readOnly=true)
public List<ImageDescriptor> getImages() throws DataAccessException {
	return getSimpleJdbcTemplate().query(
	    "SELECT image_name, description FROM imagedb",
	    new ParameterizedRowMapper<ImageDescriptor>() {
		    public ImageDescriptor mapRow(ResultSet rs, int rowNum) throws SQLException {
			    String name = rs.getString(1);
			    String description = lobHandler.getClobAsString(rs, 2);
			    return new ImageDescriptor(name, description);
		    }
	    });
}
 

/**
 * Retrieves record for position key from PS_POSITION_DATA and returns populated <code>PositionData</code> business object.
 */
public Collection<PositionData> getPositionData(String positionNumber) {
    StringBuilder sqlBuilder = new StringBuilder();
    sqlBuilder.append("SELECT POSITION_NBR, EFFDT, JOBCODE, POS_EFF_STATUS, DESCR, \n");
    sqlBuilder.append("        DESCRSHORT, BUSINESS_UNIT, DEPTID, POSN_STATUS, STATUS_DT, \n");
    sqlBuilder.append("        BUDGETED_POSN, STD_HRS_DEFAULT, STD_HRS_FREQUENCY, POS_REG_TEMP, \n");
    sqlBuilder.append("        POS_FTE, POS_SAL_PLAN_DFLT, POS_GRADE_DFLT \n");
    sqlBuilder.append(" FROM PS_POSITION_DATA \n");
    sqlBuilder.append(" WHERE POSITION_NBR = ? \n");

    String sqlString = sqlBuilder.toString();

    ParameterizedRowMapper<PositionData> mapper = new ParameterizedRowMapper<PositionData>() {

        public PositionData mapRow(ResultSet rs, int rowNum) throws SQLException {
            PositionData positionData = new PositionData();
            positionData.setPositionNumber(rs.getString("POSITION_NBR"));
            positionData.setEffectiveDate(rs.getDate("EFFDT"));
            positionData.setJobCode(rs.getString("JOBCODE"));
            positionData.setPositionEffectiveStatus(rs.getString("POS_EFF_STATUS"));
            positionData.setDescription(rs.getString("DESCR"));
            positionData.setShortDescription(rs.getString("DESCRSHORT"));
            positionData.setBusinessUnit(rs.getString("BUSINESS_UNIT"));
            positionData.setDepartmentId(rs.getString("DEPTID"));
            positionData.setPositionStatus(rs.getString("POSN_STATUS"));
            positionData.setStatusDate(rs.getDate("STATUS_DT"));
            positionData.setBudgetedPosition(rs.getString("BUDGETED_POSN"));
            positionData.setStandardHoursDefault(rs.getBigDecimal("STD_HRS_DEFAULT"));
            positionData.setStandardHoursFrequency(rs.getString("STD_HRS_FREQUENCY"));
            positionData.setPositionRegularTemporary(rs.getString("POS_REG_TEMP"));
            positionData.setPositionFullTimeEquivalency(rs.getBigDecimal("POS_FTE"));
            positionData.setPositionSalaryPlanDefault(rs.getString("POS_SAL_PLAN_DFLT"));
            positionData.setPositionGradeDefault(rs.getString("POS_GRADE_DFLT"));

            return positionData;
        }
    };

    return this.getSimpleJdbcTemplate().query(sqlString, mapper, positionNumber);
}