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

The following examples show how to use org.springframework.jdbc.core.simple.ParameterizedRowMapper. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example #1
Source File: PaginationHelper.java    From diamond with Apache License 2.0 5 votes vote down vote up
/**
 * 取分页
 * 
 * @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;
}
 
Example #2
Source File: PaginationHelper.java    From diamond with Apache License 2.0 5 votes vote down vote up
/**
 * 取分页
 * 
 * @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;
}
 
Example #3
Source File: SimpleJdbcClinic.java    From cacheonix-core with GNU Lesser General Public License v2.1 5 votes vote down vote up
/**
 * 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);
			}
		}
	}
}
 
Example #4
Source File: DefaultImageDatabase.java    From cacheonix-core with GNU Lesser General Public License v2.1 5 votes vote down vote up
@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);
		    }
	    });
}
 
Example #5
Source File: HumanResourcesPayrollDaoJdbc.java    From kfs with GNU Affero General Public License v3.0 5 votes vote down vote up
/**
 * 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);
}