Java Code Examples for org.springframework.jdbc.core.ResultSetExtractor

Following code examples demonstrate how to use org.springframework.jdbc.core.ResultSetExtractorfrom spring-framework. These examples are extracted from various highly rated open source projects. You can directly use these code snippets or view their entire linked source code. These snippets are extracted to provide contextual information about how to use this class in the real world. These samples also let you understand some good practices on how to use org.springframework.jdbc.core.ResultSetExtractorand various code implementation of this class.

	public ArrayList<String> getTitleDemandCount() {
		String sql = "select count(title.titleId) as issueCount, title.titleId as titleId, title.titleName as titleName from transactions join title on transactions.titleId = title.titleId where requestDate between  DATEADD(day,-30,GETDATE()) and GETDATE() group by title.titleId,titleName order by issueCount desc";
		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<String>>() {
			
			public ArrayList<String> extractData(ResultSet rs) throws SQLException, DataAccessException {
				HashMap<Title, Integer> counts = new HashMap<Title, Integer>();
				ArrayList<String> bod = new ArrayList<String>();
				while (rs.next()) {
					Title title = new Title();
					int titleCount = 0;
					title.setTitleName(rs.getString("titleName"));
					title.setTitleId(rs.getInt("titleId"));
					titleCount = rs.getInt("issueCount");
					bod.add(rs.getString("titleName"));
					counts.put(title, titleCount);
				}
				return bod;
			}

		});
	} 


	public Transaction getTransactionDetails(final int transactionId) {
		String sql = "use Library ; select * from transactions where transactionId = ?";

		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, transactionId);
			}

		}, new ResultSetExtractor<Transaction>() {

			
			public Transaction extractData(ResultSet rs) throws SQLException, DataAccessException {
				Transaction transaction = null;
				while (rs.next()) {
					transaction = new Transaction();
					transaction.setTransactionId(rs.getInt("transactionId"));
					transaction.setResourceId(rs.getInt("resourceId"));
					transaction.setEmailId(rs.getString("emailId"));
					transaction.setIssueDate(rs.getDate("issueDate"));
					transaction.setReturnDate(rs.getDate("returnDate"));
					transaction.setRequestDate(rs.getDate("requestDate"));
					transaction.setStatus(rs.getString("status"));
					transaction.setTitleId(rs.getInt("titleId"));
				}
				return transaction;
			}

		});
	} 

	public ArrayList<Transaction> borrowListByLocation(String locationName) {
		final String escapeLocationName = locationName.replace("!", "!!").replace("%", "!%").replace("_", "!_")
				.replace("[", "![");

		String sql = "select transactionId ,trct.emailId as emailId, cityName, firstName, titleName, resourceId, status from libraryUser join (select transactionId, cityName, titleName, tr.resourceId, emailId, status from transactions as tr join (select titleName, resourceId, cityName from title as t join (select cityName, resourceId, titleId from city join resource on city.cityId = resource.locationId where city.cityName like ?) as c on c.titleId = t.titleId) as ct on ct.resourceId = tr.resourceId where tr.status = 'approved' or tr.status = 'return') as trct on libraryUser.emailId = trct.emailId";
		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, '%' + escapeLocationName + '%');

			}

		}, new ResultSetExtractor<ArrayList<Transaction>>() {

			
			public ArrayList<Transaction> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Transaction> arrayList = new ArrayList<Transaction>();
				Transaction transaction = null;
				while (rs.next()) {
					transaction = new Transaction();
					transaction = new Transaction();
					transaction.setEmailId(rs.getString("emailId"));
					transaction.setName(rs.getString("firstName"));
					transaction.setStatus(rs.getString("status"));
					transaction.setTransactionId(rs.getInt("transactionId"));
					transaction.setTitleName(rs.getString("titleName"));
					transaction.setResourceId(rs.getInt("resourceId"));
					transaction.setLocationName(rs.getString("cityName"));
					arrayList.add(transaction);
				}
				return arrayList;
			}

		});
	} 

	public boolean didRequestEarlier(final int titleId, final String emailId) {
		System.out.println("in did request earlier");
		String sql = "select * from transactions where titleId = ? and emailId = ? and (status='processing' or status='approved')";
		
		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, titleId);
				ps.setString(2, emailId);

			}

		},new ResultSetExtractor<Boolean>() {
			
			public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException {
				int count = 0;
				while(rs.next()){
					count += 1;
				}
				System.out.println(count);
				if(count > 0){
					return true;
				}
				else{
					return false;
				}
				
			}

		});
		
} 

	public ArrayList<Location> summaryByLocation() {


		String sql = "select c.cityId, c.cityName,count(c.cityId) as locationCount from city as c join (select r.locationId as cityId from resource as r join transactions as t on r.resourceId = t.resourceId where t.status = 'approved' or t.status='return') as trans on trans.cityId = c.cityId group by c.cityName, c.cityId";

		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<Location>>() {

			
			public ArrayList<Location> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Location> arrayList = new ArrayList();
				while (rs.next()) {
					Location location = new Location();
					location.setLocationId(rs.getInt(1));
					location.setLocationName(rs.getString(2));
					location.setCount(rs.getInt(3));
					arrayList.add(location);
				}
				return arrayList;
			}

		});
	} 

	public int isBookAvailable(final int titleId) {
		String sql = "use Library ; select top(1) * from resource where titleId = ? and allocated = 0";

		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, titleId);
			}

		}, new ResultSetExtractor<Integer>() {

			
			public Integer extractData(ResultSet rs) throws SQLException, DataAccessException {
				int resourceId = -1;
				while (rs.next()) {
					resourceId = rs.getInt("resourceId");
				}
				return resourceId;
			}

		});

	} 

	public ArrayList<Topic> summaryByTopic() {

		System.out.println(jdbcTemplate);

		String sql = "select topic.topicId as topicId, topic.topicName as topicName, count(topic.topicName) as topicCount from topic join (select title.topicId as topicId from title join (select b.titleId as titleId from resource as b join transactions as t on t.resourceId = b.resourceId where t.status = 'approved' or t.status = 'return') as trans on trans.titleId = title.titleId ) as trans on trans.topicId = topic.topicId group by topic.topicId, topic.topicName";

		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<Topic>>() {

			
			public ArrayList<Topic> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Topic> t = new ArrayList();
				while (rs.next()) {
					Topic topic = new Topic();
					topic.setTopicId(rs.getInt(1));
					topic.setTopicName(rs.getString(2));
					topic.setCount(rs.getInt(3));
					t.add(topic);
				}
				return t;
			}

		});
	} 

	public ArrayList<Transaction> getAllProcessingTransaction() {
		String sql = "use Library ; select * from transactions join title on transactions.titleId = title.titleId where status = 'processing'";

		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<Transaction>>() {

			
			public ArrayList<Transaction> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Transaction> arrayList = new ArrayList<Transaction>();
				while (rs.next()) {
					transaction = new Transaction();
					transaction.setTransactionId(rs.getInt("transactionId"));
					transaction.setResourceId(rs.getInt("resourceId"));
					transaction.setEmailId(rs.getString("emailId"));
					transaction.setIssueDate(rs.getDate("issueDate"));
					transaction.setReturnDate(rs.getDate("returnDate"));
					transaction.setRequestDate(rs.getDate("requestDate"));
					transaction.setStatus(rs.getString("status"));
					transaction.setTitleId(rs.getInt("titleId"));
					transaction.setTitleName(rs.getString("titleName"));
					arrayList.add(transaction);
				}
				return arrayList;
			}

		});

	} 

	public ArrayList<Transaction> borrowListByTitle(String titleName) {
		final String escapeTitleName = titleName.replace("!", "!!").replace("%", "!%").replace("_", "!_").replace("[",
				"![");

		String sql = "use library; select * from libraryUser as l, (select transactionId, title.titleId, title.titleName, emailId, issueDate, requestDate, returnDate, resourceId,status from title join transactions on title.titleId = transactions.titleId where title.titleName like ? and (transactions.status = 'return' or transactions.status='approved')) as t where l.emailId = t.emailId";
		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, '%' + escapeTitleName + '%');

			}

		}, new ResultSetExtractor<ArrayList<Transaction>>() {

			
			public ArrayList<Transaction> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Transaction> arrayList = new ArrayList<Transaction>();
				Transaction transaction = null;
				while (rs.next()) {
					transaction = new Transaction();
					transaction = new Transaction();
					transaction.setEmailId(rs.getString("emailId"));
					transaction.setIssueDate(rs.getDate("issueDate"));
					transaction.setName(rs.getString("firstName"));
					transaction.setReturnDate(rs.getDate("returnDate"));
					transaction.setStatus(rs.getString("status"));
					transaction.setRequestDate(rs.getDate("requestDate"));
					transaction.setTransactionId(rs.getInt("transactionId"));
					transaction.setTitleName(rs.getString("titleName"));
					transaction.setResourceId(rs.getInt("resourceId"));
					arrayList.add(transaction);
				}
				return arrayList;
			}

		});

	} 

	public ArrayList<Resource> getAllBooksTopic(final String topicName) {

		final String escapeTopicName = topicName
			    .replace("!", "!!")
			    .replace("%", "!%")
			    .replace("_", "!_")
			    .replace("[", "![");
		
		String sql = "select r.resourceId, t.titleId, t.titleName, r.allocated from resource as r join (select t.titleId, t.titleName from title as t join topic on t.topicId = topic.topicId where topic.topicName like ? escape '!') as t on t.titleId = r.titleId";
		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, '%'+escapeTopicName+'%');

			}

		}, new ResultSetExtractor<ArrayList<Resource>>() {

			
			public ArrayList<Resource> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Resource> arrayList = new ArrayList<Resource>();
				Resource resource = null;
				while (rs.next()) {
					resource = new Resource();
					resource.setResourceId(rs.getInt(1));
					resource.setTitleId(rs.getInt(2));
					resource.setTitleName(rs.getString(3));
					resource.setAllocated(rs.getInt(4));
					arrayList.add(resource);
				}
				return arrayList;
			}

		});

	} 

	public ArrayList<Topic> getAllTopics() {
		String sql = "select * from topic order by topicName asc";

		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<Topic>>() {

			
			public ArrayList<Topic> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Topic> arrayList = new ArrayList<Topic>();
				while (rs.next()) {
					Topic topic = new Topic();
					topic.setTopicId(rs.getInt(1));
					topic.setTopicName(rs.getString(2));
					arrayList.add(topic);
				}
				return arrayList;

			}

		});
	} 

	public ArrayList<Resource> getAllBooksLocation(final String locationName) {

		final String escapeLocationName = locationName
			    .replace("!", "!!")
			    .replace("%", "!%")
			    .replace("_", "!_")
			    .replace("[", "![");
		String sql = "use Library ; select resourceId, t.titleId as titleId, titleName, allocated, cityName, locationId, typeId from title as t join (select resourceId, titleId, allocated, city.cityName, locationId, typeId from resource join city on resource.locationId = city.cityId where city.cityName like ? ESCAPE '!') as r on t.titleId = r.titleId";

		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, '%'+escapeLocationName+'%');

			}

		}, new ResultSetExtractor<ArrayList<Resource>>() {

			
			public ArrayList<Resource> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Resource> arrayList = new ArrayList<Resource>();
				Resource resource = null;
				while (rs.next()) {
					resource = new Resource();
					resource.setResourceId(rs.getInt("resourceId"));
					resource.setTitleId(rs.getInt("titleId"));
					resource.setTitleName(rs.getString("titleName"));
					resource.setAllocated(rs.getInt("allocated"));
					resource.setCityName(rs.getString("cityName"));
					resource.setLocationId(rs.getInt("locationId"));
					resource.setTypeId(rs.getInt("typeId"));
					arrayList.add(resource);
					System.out.println(resource.toString());
				}
				
				
				
				return arrayList;
			}

		});

	} 

	public List<Integer> getAllReviewTitleId(String emailId) {

		return jdbcTemplate.query(" use Library ; Select titleId from dbo.review where emailId= " + emailId,
				new ResultSetExtractor<List<Integer>>() {
					public List<Integer> extractData(ResultSet rs) throws SQLException, DataAccessException {
						List<Integer> list = new ArrayList<Integer>();
						while (rs.next()) {
							list.add(rs.getInt(1));
						}
						return list;
					}
				});
	} 

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setInt(1, resourceId);

			} 

	public ArrayList<Author> getAllAuthors() {
		String sql = "select * from author order by authorName asc";

		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<Author>>() {

			
			public ArrayList<Author> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Author> arrayList = new ArrayList<Author>();
				while (rs.next()) {
					Author author = new Author();
					author.setAuthorId(rs.getInt(1));
					author.setAuthorName(rs.getString(2));
					arrayList.add(author);
				}
				return arrayList;

			}

		});
	} 

	public ArrayList<ResourceType> getAllResourceTypes() {

		String sql = "select * from resourceType order by resourceName asc";

		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<ResourceType>>() {

			
			public ArrayList<ResourceType> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<ResourceType> arrayList = new ArrayList<ResourceType>();
				while (rs.next()) {
					ResourceType resourceType = new ResourceType();
					resourceType.setTypeId(rs.getInt(1));
					resourceType.setResourceName(rs.getString(2));
					arrayList.add(resourceType);
				}
				return arrayList;

			}

		});
	} 

	public ArrayList<Resource> getAllBooksTitle(String titleName) {

		final String escapeTitleName = titleName
			    .replace("!", "!!")
			    .replace("%", "!%")
			    .replace("_", "!_")
			    .replace("[", "![");
		String sql = "use Library ; Select resourceId , resource.titleId, titleName, allocated from resource join title on resource.titleId=title.titleId where title.titleName like ? ESCAPE '!'";

		return jdbcTemplate.query(sql, new PreparedStatementSetter() {

			public void setValues(PreparedStatement ps) throws SQLException {
				ps.setString(1, '%'+escapeTitleName+'%');

			}

		}, new ResultSetExtractor<ArrayList<Resource>>() {

			
			public ArrayList<Resource> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Resource> arrayList = new ArrayList<Resource>();
				Resource resource = null;
				while (rs.next()) {
					resource = new Resource();
					resource.setResourceId(rs.getInt(1));
					resource.setTitleId(rs.getInt(2));
					resource.setTitleName(rs.getString(3));
					resource.setAllocated(rs.getInt(4));
					arrayList.add(resource);
				}
				return arrayList;
			}

		});

	} 

	public ArrayList<Title> getAllTitles() {
		String sql = "select * from title order by titleName asc";

		return jdbcTemplate.query(sql, new ResultSetExtractor<ArrayList<Title>>() {

			
			public ArrayList<Title> extractData(ResultSet rs) throws SQLException, DataAccessException {
				ArrayList<Title> arrayList = new ArrayList<Title>();
				while (rs.next()) {
					Title title = new Title();
					title.setTitleId(rs.getInt(1));
					title.setTitleName(rs.getString(2));
					title.setAuthorId(rs.getInt(3));
					title.setTopicId(rs.getInt(4));
					arrayList.add(title);
				}
				return arrayList;

			}

		});

	} 

Advertisement
Javadoc
Callback interface used by JdbcTemplate's query methods. Implementations of this interface perform t

he actual work of extracting results from a java.sql.ResultSet, but don't need to worry about exception handling. java.sql.SQLException SQLExceptions will be caught and handled by the calling JdbcTemplate.

This interface is mainly used within the JDBC framework itself. A RowMapper is usually a simpler choice for ResultSet processing, mapping one result object per row instead of one result object for the entire ResultSet.

Note: In contrast to a RowCallbackHandler, a ResultSetExtractor object is typically stateless and thus reusable, as long as it doesn't access stateful resources (such as output streams when streaming LOB contents) or keep result state within the object. @author Rod Johnson @author Juergen Hoeller @since April 24, 2003 @param the result type @see JdbcTemplate @see RowCallbackHandler @see RowMapper @see org.springframework.jdbc.core.support.AbstractLobStreamingResultSetExtractor

Read More
Advertisement