Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Read All Result Set

 
Gehan Fernando
Greenhorn
Posts: 11
Eclipse IDE Oracle Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Java Experts

This is my first java experience, I am developing an application to review data using Microsoft SQL and Spring framework, one of stored procedures return multiple result sets. I want to read all the result set data in java side. I try to fix this last 48 hours but I am fail. Can anybody help me?

private SimpleJdbcCall jdbcCall;

public List<Instrument> execute(Instrument instrument) {

Map<String, Object> inputs = new HashMap<String, Object>();

inputs.put("source", null);
inputs.put("date", null);
inputs.put("time", null);
inputs.put("name_id", null);

Map<String, Object> result = jdbcCall.execute(inputs);

List<Instrument> iList = (List<Instrument>)result.get("DMapper");

return iList;
}

After execute the Map<String, Object> result = jdbcCall.execute(inputs);
It displays 3 result sets {#result-set-8=[], #result-set-9=[], #result-set-10=[]}, I want to read all the result-sets and want to my List<Instrument> iList

How can I do this?
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch.

While we don't know what "SimpleJdbcCall" is supposed to do (making it hard to speculate what might or might not be going on), setting all inputs to "null" strikes me as odd. I would imagine they'd need valid values to be used for retrieving the data from the DB.
 
Gehan Fernando
Greenhorn
Posts: 11
Eclipse IDE Oracle Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It comes with org.springframework.jdbc.core.simple.SimpleJdbcCall, all null means my SQL Stored Procedure Parameter values set as null.
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So it's a stored procedure that takes 4 parameters, but uses none of them?
 
Gehan Fernando
Greenhorn
Posts: 11
Eclipse IDE Oracle Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes You're correct. but it depends on the business requirement. by the way There in no issue with stored procedure.
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Maybe SimpleJdbcCall can't handle null parameters, or handles them differently than you expect?
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SimpleJdbcCall has a returningResultSet method for you to define what to do with a result set that is returned.
You give that call a RowMapper, and can then get the resultant Map<> back for each result set.

Had to pull some of this from memory, but section 14.5.9 here should help with an example.
Essentially, the order your SP returns result sets is the order in which you need to declare them in the SimpleJdbcCall instance, and the name you give is just the name you use to access it from the Map<> returned by the execute() call.
 
Gehan Fernando
Greenhorn
Posts: 11
Eclipse IDE Oracle Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey Guys, Thanks for your replies but your answers not satisfy my question.

In My Stored Procedure

SLECT * FROM Customer;
GO
SLECT * FROM Supplier;
GO
SLECT * FROM Details;


So my SP returns 3 resultsets, I want to capture all 3 resultsets in

Map<String, Object> result = jdbcCall.execute(inputs);

And I use [ RowMapper ] class to get details.

Is there have way to capture all resultset.


Thanks
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is not a copy/paste code, but should give you an idea of the structure expected:

The type of firstRSData depends on your mapper, but looks like a List of <YourClass> from your OP.
 
Gehan Fernando
Greenhorn
Posts: 11
Eclipse IDE Oracle Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Firstly I want to tank for everyone who gave me support to do this. After research some URLs I found the answer.

public List<Customer> GetAll() throws SQLException{

Connection conn = this.dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement("{call GetAllCustomers}");

boolean hasResult = stmt.execute();

List<Customer> customerList = new ArrayList<Customer>();

while(hasResult){
ResultSet res = stmt.getResultSet();
while(res.next()){
customerList.add(CustomerMapper.mapRow(res));
}
hasResult = stmt.getMoreResults();
}
return customerList;
}

public Customer Get(Integer cno) throws SQLException{

Connection conn = this.dataSource.getConnection();
PreparedStatement stmt = conn.prepareStatement("{call GetCustomer(?)}");

stmt.setInt(1, cno);

boolean hasResult = stmt.execute();

List<Customer> customerList = new ArrayList<Customer>();

while(hasResult){
ResultSet res = stmt.getResultSet();
while(res.next()){
customerList.add(CustomerMapper.mapRow(res));
}
hasResult = stmt.getMoreResults();
}

if (customerList.size() == 0){
return null;
}else {
return customerList.get(0);
}
}
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh.
From your original post I thought using Spring was a requirement.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic