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

The following are top voted examples for showing how to use org.springframework.jdbc.core.simple.ParameterizedRowMapper. These examples are extracted from open source projects. You can vote up the examples you like and your votes will be used in our system to generate more good examples.
Example 1
Project: diamond-v2.1.1   File: PaginationHelper.java   Source Code and License 7 votes vote down vote up
/**
    * 取分页
    * 
    * @param jt
    *            jdbcTemplate
    * @param sqlFetchRows
    *            查询数据的sql
    * @param args
    *            查询参数
    * @param size
    *            大小
    * @param rowMapper
    * @return
    */
   @SuppressWarnings({ "unchecked", "rawtypes" })
public List<E> fetchList(final JdbcTemplate jt, final String sqlFetchRows,
		final Object args[], final int size, final ParameterizedRowMapper<E> rowMapper) {
	if (size == 0) {
		return null;
	}
	final Page<E> page = new Page<E>();
	// TODO 在数据量很大时, limit效率很低
	final String selectSQL = sqlFetchRows + " limit " + size;
	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 pageItems;
		}
	});
	return page.getPageItems();
}
 
Example 2
Project: diamond-v2.1.1   File: PaginationHelper.java   Source Code and License 6 votes vote down vote up
/**
    * 取分页
    * 
    * @param jt
    *            jdbcTemplate
    * @param sqlFetchRows
    *            查询数据的sql
    * @param args
    *            查询参数
    * @param size
    *            大小
    * @param rowMapper
    * @return
    */
   @SuppressWarnings({ "unchecked", "rawtypes" })
public List<E> fetchList(final JdbcTemplate jt, final String sqlFetchRows,
		final Object args[], final int size, final ParameterizedRowMapper<E> rowMapper) {
	if (size == 0) {
		return null;
	}
	final Page<E> page = new Page<E>();
	// TODO 在数据量很大时, limit效率很低
	final String selectSQL = sqlFetchRows + " limit " + size;
	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 pageItems;
		}
	});
	return page.getPageItems();
}
 
Example 3
Project: firefly   File: QueryFileInfoProcessor.java   Source Code and License 6 votes vote down vote up
protected FileInfo loadData(ServerRequest sr) throws IOException, DataAccessException {

        if (!(sr instanceof TableServerRequest)) {
            throw new IllegalArgumentException("FileInfoProcessor.loadData Requires an TableServerRequest");
        }
        TableServerRequest request= (TableServerRequest)sr;
        SimpleJdbcTemplate jdbc = JdbcFactory.getSimpleTemplate(getDbInstance());

        String sql = getSql(request);
        Object[] params = getSqlParams(request);

        final FileInfoRowMapper fim = makeRowMapper(request);
        ParameterizedRowMapper<FileInfo> mapper = new ParameterizedRowMapper<FileInfo>() {
            public FileInfo mapRow(ResultSet resultSet, int i) throws SQLException {
                return fim.mapRow(resultSet, i);
            }
        };

        LOGGER.info("Executing SQL query: " + sql,
                 "         Parameters: " + "{" + CollectionUtil.toString(params) + "}");
        FileInfo val = jdbc.queryForObject(sql, mapper, params);

        return val;

    }
 
Example 4
Project: muleebmsadapter   File: AbstractEbMSDAO.java   Source Code and License 6 votes vote down vote up
private List<DataSource> getAttachments(long messageId) throws DAOException
{
	try
	{
		return simpleJdbcTemplate.query(
			"select name, content_type, content" + 
			" from ebms_attachment" + 
			" where ebms_message_id = ?",
			new ParameterizedRowMapper<DataSource>()
			{
				@Override
				public DataSource mapRow(ResultSet rs, int rowNum) throws SQLException
				{
					ByteArrayDataSource result = new ByteArrayDataSource(rs.getBytes("content"),rs.getString("content_type"));
					result.setName(rs.getString("name"));
					return result;
				}
			},
			messageId
		);
	}
	catch (DataAccessException e)
	{
		throw new DAOException(e);
	}
}
 
Example 5
Project: spring-petclinic-study   File: JdbcVisitRepositoryImpl.java   Source Code and License 6 votes vote down vote up
@Override
public List<Visit> findByPetId(Integer petId) {
    final List<Visit> visits = this.jdbcTemplate.query(
            "SELECT id, visit_date, description FROM visits WHERE pet_id=?",
            new ParameterizedRowMapper<Visit>() {
                @Override
                public Visit mapRow(ResultSet rs, int row) throws SQLException {
                    Visit visit = new Visit();
                    visit.setId(rs.getInt("id"));
                    Date visitDate = rs.getDate("visit_date");
                    visit.setDate(new DateTime(visitDate));
                    visit.setDescription(rs.getString("description"));
                    return visit;
                }
            },
            petId);
    return visits;
}
 
Example 6
Project: spring-in-action   File: SimpleJdbcSupportSpitterDao.java   Source Code and License 6 votes vote down vote up
public Spitter getSpitterById(long id) {
  return getSimpleJdbcTemplate().queryForObject(
          SQL_SELECT_SPITTER_BY_ID,
      new ParameterizedRowMapper<Spitter>() {
        public Spitter mapRow(ResultSet rs, int rowNum) 
            throws SQLException {
          Spitter spitter = new Spitter();
          spitter.setId(rs.getLong(1));
          spitter.setUsername(rs.getString(2));
          spitter.setPassword(rs.getString(3));
          spitter.setFullName(rs.getString(4));
          spitter.setEmail(rs.getString(5));
          return spitter;
        }
      }, id);
}
 
Example 7
Project: spring-in-action   File: SimpleJdbcTemplateSpitterDao.java   Source Code and License 6 votes vote down vote up
public Spitter getSpitterById(long id) {
  return jdbcTemplate.queryForObject(//<co id="co_query"/>
          SQL_SELECT_SPITTER_BY_ID,
      new ParameterizedRowMapper<Spitter>() {
        public Spitter mapRow(ResultSet rs, int rowNum) 
            throws SQLException {
          Spitter spitter = new Spitter();//<co id="co_map"/>
          spitter.setId(rs.getLong(1));
          spitter.setUsername(rs.getString(2));
          spitter.setPassword(rs.getString(3));
          spitter.setFullName(rs.getString(4));
          return spitter;
        }
      }, 
      id //<co id="co_bind"/>
      );
}
 
Example 8
Project: spring-in-action   File: JdbcSpitterDao.java   Source Code and License 6 votes vote down vote up
public List<Spittle> getRecentSpittle() {
  DateTime dt = new DateTime().minusDays(1);
  
  return getSimpleJdbcTemplate().query(SQL_SELECT_RECENT_SPITTLE, 
        new ParameterizedRowMapper<Spittle>() {
    public Spittle mapRow(ResultSet rs, int rowNum) throws SQLException {
      Spittle spittle = new Spittle();
      
      spittle.setId(rs.getLong(1));
      spittle.setSpitter(getSpitterById(rs.getLong(2)));
      spittle.setText(rs.getString(3));
      spittle.setWhen(rs.getDate(4));
      
      return spittle;
    }
  }, dt.toDate());
}
 
Example 9
Project: gnvc-ims   File: FishMealDaoImpl.java   Source Code and License 6 votes vote down vote up
public String findAdjusted(String date, int b) {
    String productCode = "";
    
    switch(b) {
        case 1: {
            productCode = "FISH_MEAL";
        } break;
        case 2: {
            productCode = "FISH_OIL";
        } break;
    }
    
    List<String> ls = jdbcTemplate.query("SELECT log FROM stock_inventory_log WHERE product_code = ? AND created_date = ?", new ParameterizedRowMapper<String>() {
        public String mapRow(ResultSet rs, int i) throws SQLException {
            return rs.getString(1);
        }
    }, productCode, date);
    
    return ls.isEmpty() ? null : ls.get(0);
}
 
Example 10
Project: gnvc-ims   File: FishOilDaoImpl.java   Source Code and License 6 votes vote down vote up
public String findAdjusted(String date, int b) {
    String productCode = "";
    switch (b) {
        case 1: {
            productCode = "FISH_MEAL";
        }
        break;
        case 2: {
            productCode = "FISH_OIL";
        }
        break;
    }
    List<String> ls = jdbcTemplate.query("SELECT log FROM stock_inventory_log WHERE product_code = ? AND created_date = ?", new ParameterizedRowMapper<String>() {
        public String mapRow(ResultSet rs, int i) throws SQLException {
            return rs.getString(1);
        }
    }, productCode, date);
    return ls.isEmpty() ? null : ls.get(0);
}
 
Example 11
Project: cananolab   File: CurationServiceJDBCImpl.java   Source Code and License 6 votes vote down vote up
public ParameterizedRowMapper<DataReviewStatusBean> getDataReviewStatusRowMapper()
		throws SQLException {
	dataReviewStatusRowMapper = new ParameterizedRowMapper<DataReviewStatusBean>() {
		public DataReviewStatusBean mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			DataReviewStatusBean dataStatusBean = new DataReviewStatusBean();
			dataStatusBean.setDataId(rs
					.getString(REVIEW_STATUS_TABLE_DATA_ID_COL));
			dataStatusBean.setDataName(rs
					.getString(REVIEW_STATUS_TABLE_DATA_NAME_COL));
			dataStatusBean.setDataType(rs
					.getString(REVIEW_STATUS_TABLE_DATA_TYPE_COL));
			dataStatusBean.setReviewStatus(rs
					.getString(REVIEW_STATUS_TABLE_STATUS_COL));
			dataStatusBean.setSubmittedBy(rs
					.getString(REVIEW_STATUS_TABLE_SUBMITTED_BY_COL));
			dataStatusBean.setSubmittedDate(rs
					.getDate(REVIEW_STATUS_TABLE_SUBMITTED_DATE_COL));
			return dataStatusBean;
		}
	};
	return dataReviewStatusRowMapper;
}
 
Example 12
Project: pub-service   File: JdbcDaoSupport.java   Source Code and License 5 votes vote down vote up
public T queryForObject(String sql, ParameterizedRowMapper mapper,
		Object... args) {
	try {
		T t = (T) this.simpleJdbcTemplate.queryForObject(sql, mapper, args);
		return t;
	} catch (RuntimeException e) {
		return null;
	}
}
 
Example 13
Project: diamond   File: PaginationHelper.java   Source Code and License 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 14
Project: cacheonix-core   File: SimpleJdbcClinic.java   Source Code and License 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 15
Project: cacheonix-core   File: DefaultImageDatabase.java   Source Code and License 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 16
Project: diamond   File: PaginationHelper.java   Source Code and License 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 17
Project: spring-petclinic-study   File: JdbcVetRepositoryImpl.java   Source Code and License 5 votes vote down vote up
/**
 * Refresh the cache of Vets that the ClinicService is holding.
 *
 * @see org.springframework.samples.petclinic.model.service.ClinicService#findVets()
 */
@Override
public Collection<Vet> findAll() throws DataAccessException {
    List<Vet> vets = new ArrayList<Vet>();
    // Retrieve the list of all vets.
    vets.addAll(this.jdbcTemplate.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.jdbcTemplate.query(
            "SELECT id, name FROM specialties",
            ParameterizedBeanPropertyRowMapper.newInstance(Specialty.class));

    // Build each vet's list of specialties.
    for (Vet vet : vets) {
        final List<Integer> vetSpecialtiesIds = this.jdbcTemplate.query(
                "SELECT specialty_id FROM vet_specialties WHERE vet_id=?",
                new ParameterizedRowMapper<Integer>() {
                    @Override
                    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);
        }
    }
    return vets;
}
 
Example 18
Project: spring-in-action   File: JdbcSpitterDao.java   Source Code and License 5 votes vote down vote up
public Spitter getSpitterById(long id) {
  return getSimpleJdbcTemplate().queryForObject(
          SQL_SELECT_SPITTER_BY_ID,
      new ParameterizedRowMapper<Spitter>() {
        public Spitter mapRow(ResultSet rs, int rowNum) 
            throws SQLException {
          Spitter spitter = new Spitter();
          spitter.setId(rs.getLong(1));
          spitter.setUsername(rs.getString(2));
          spitter.setPassword(rs.getString(3));
          spitter.setFullName(rs.getString(4));
          return spitter;
        }
      }, id);
}
 
Example 19
Project: AGIA   File: HibernateNodeReader.java   Source Code and License 5 votes vote down vote up
/**
 * Retrieves nodes' IDs
 * 
 * @return a collection of IDs
 */
private List<Long> retrieveKeys() {

    synchronized (lock) {
        MapSqlParameterSource aParams = new MapSqlParameterSource();
        aParams.addValue(JOBSTEP_PARAM, currentStep);
        return jdbcTemplate.query(sqlQuery, aParams, new ParameterizedRowMapper<Long>() {
            public Long mapRow(ResultSet rs, int rowNum) throws SQLException {
                return rs.getLong(1);
            }
        });
    }

}
 
Example 20
Project: kfs   File: HumanResourcesPayrollDaoJdbc.java   Source Code and License 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);
}
 
Example 21
Project: CAM   File: JdbcTemplateDaoImpl.java   Source Code and License 5 votes vote down vote up
public List<Authorization> getAllAuthorization() {
    return jdbcTemplate.query("select * from cbam_authorization",new ParameterizedRowMapper<Authorization>() {
        @Override
        public Authorization mapRow(ResultSet rs, int rowNum) throws SQLException {
            Authorization a = new Authorization();
            a.setId(rs.getString("id"));
            a.setRoleId(rs.getString("role_id"));
            a.setPermissionId(rs.getString("permission_id"));
            a.setAuthorizedBy(rs.getString("authorized_by"));
            a.setUpdateTime(rs.getDate("update_time"));
            return a;
        }
    });
}
 
Example 22
Project: CAM   File: JdbcTemplateDaoImpl.java   Source Code and License 5 votes vote down vote up
public List<Role> getAllRole() {
    return jdbcTemplate.query("select * from cbam_role",new ParameterizedRowMapper<Role>() {
        @Override
        public Role mapRow(ResultSet rs, int rowNum) throws SQLException {
            Role r = new Role();
            r.setId(rs.getString("id"));
            r.setName(rs.getString("name"));
            r.setUserCriteria(rs.getString("user_criteria"));
            return r;
        }
    });
}
 
Example 23
Project: gnvc-ims   File: TsDaoImpl.java   Source Code and License 5 votes vote down vote up
public List<String> findProductCodeWithRR(String productCategory, Date asOf) {
    List<String> resultList = null;
    String sqlQuery = "select distinct tsd.product_code from dbo.ts ts left join dbo.ts_detail tsd on "
            + "ts.ts_code = tsd.ts_code left join dbo.product prod on "
            + "prod.product_code = tsd.product_code where prod.product_category = ? AND "
            + "(ts.ts_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, -1, ?) - 1, 0) AND ? )";

    resultList = (List<String>) jdbcTemplate.query(sqlQuery, new ParameterizedRowMapper<String>() {

        public String mapRow(ResultSet rs, int arg1) throws SQLException {
            return rs.getString(1);
        }
    }, productCategory, asOf, asOf);
    return resultList;
}
 
Example 24
Project: gnvc-ims   File: DrDaoImpl.java   Source Code and License 5 votes vote down vote up
public List<String> findProductCodeWithRR(String productCategory, Date asOf) {
    List<String> resultList = null;
    String sqlQuery = "select distinct drd.product_code from dbo.dr dr left join dbo.dr_detail drd on "
            + "dr.dr_code = drd.dr_code left join dbo.product prod on "
            + "prod.product_code = drd.product_code where prod.product_category = ? AND "
            + "(dr.dr_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, -1, ?) - 1, 0) AND ?)";

    resultList = (List<String>) jdbcTemplate.query(sqlQuery, new ParameterizedRowMapper<String>() {

        public String mapRow(ResultSet rs, int arg1) throws SQLException {
            return rs.getString(1);
        }
    }, productCategory, asOf, asOf);
    return resultList;
}
 
Example 25
Project: gnvc-ims   File: ReceiveReportDaoImpl.java   Source Code and License 5 votes vote down vote up
public List<String> findProductCodeWithRR(String productCategory, Date asOf) {
    List<String> resultList = null;
    String sqlQuery = "select distinct rrd.product_code from dbo.rr rr left join dbo.rr_detail rrd on "
            + "rr.rr_code = rrd.rr_code left join dbo.product prod on "
            + "prod.product_code = rrd.product_code where prod.product_category = ? AND "
            + "(rr.rr_date BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, -1, ?) - 1, 0) AND ?) ";

    resultList = (List<String>) jdbcTemplate.query(sqlQuery, new ParameterizedRowMapper<String>() {

        public String mapRow(ResultSet rs, int arg1) throws SQLException {
            return rs.getString(1);
        }
    }, productCategory, asOf, asOf);
    return resultList;
}
 
Example 26
Project: gnvc-ims   File: StockInventoryDaoImpl.java   Source Code and License 5 votes vote down vote up
public List<String> findByProductCategoryAndNotEmpty(String productCategory) {
    List<String> resultList = null;
    String sqlQuery = "select si.product_code from stock_inventory si left join product p on "
            + "si.product_code = p.product_code where p.product_category = ? and si.balance > 0";

    resultList = (List<String>) jdbcTemplate.query(sqlQuery, new ParameterizedRowMapper<String>() {

        public String mapRow(ResultSet rs, int arg1) throws SQLException {
            return rs.getString(1);
        }
    }, productCategory);
    return resultList;
}
 
Example 27
Project: tb_diamond   File: PaginationHelper.java   Source Code and License 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 28
Project: diamond-v2.1.1   File: PaginationHelper.java   Source Code and License 4 votes vote down vote up
/**
    * 取分页
    * 
    * @param jt
    *            jdbcTemplate
    * @param sqlCountRows
    *            查询总数的SQL
    * @param sqlFetchRows
    *            查询数据的sql
    * @param args
    *            查询参数
    * @param pageNo
    *            页数
    * @param pageSize
    *            每页大小
    * @param rowMapper
    * @return
    */
   @SuppressWarnings({ "unchecked", "rawtypes", "deprecation" })
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 29
Project: diamond-v2.1.1   File: PaginationHelper.java   Source Code and License 4 votes vote down vote up
/**
    * 取分页
    * 
    * @param jt
    *            jdbcTemplate
    * @param sqlCountRows
    *            查询总数的SQL
    * @param sqlFetchRows
    *            查询数据的sql
    * @param args
    *            查询参数
    * @param pageNo
    *            页数
    * @param pageSize
    *            每页大小
    * @param rowMapper
    * @return
    */
   @SuppressWarnings({ "unchecked", "rawtypes", "deprecation" })
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 30
Project: opennmszh   File: JdbcWebOutageRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 31
Project: opennmszh   File: JdbcWebAlarmRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 32
Project: opennmszh   File: JdbcWebEventRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 33
Project: opennmszh   File: JdbcWebNotificationRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 34
Project: OpenNMS   File: JdbcWebOutageRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 35
Project: OpenNMS   File: JdbcWebAlarmRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 36
Project: OpenNMS   File: JdbcWebEventRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 37
Project: OpenNMS   File: JdbcWebNotificationRepository.java   Source Code and License 4 votes vote down vote up
private <T> List<T> queryForList(String sql, PreparedStatementSetter setter, ParameterizedRowMapper<T> rm) {
    return jdbc().query(sql, setter, new RowMapperResultSetExtractor<T>(rm));
}
 
Example 38
Project: pub-service   File: IDaoSupport.java   Source Code and License votes vote down vote up
public T queryForObject(String sql, ParameterizedRowMapper mapper, Object... args) ;