How to Convert Java List or ArrayList into ResultSet
At this project i found an obstacle, which i need to convert a reporting module from fetching data from database into fetching data from Webservices. At first I think it was easy, I just fetch strings from Webservice, and put it in a bean or in a map and put it all in an ArrayList, just like JasperReport. But I forgot, this isn’t JasperReport I’m using, I’m using bloody hell CrystalReport. And my Crystal-Report-calling method only accepting ResultSet for parameter, so somehow I need to convert strings from webservice into ResultSet.
My idea is adding strings from webservices into an ArrayList<Object> and adding this ArrayList into ArrayList<ArrayList<Object>> and converting it into rows of ResultSet. And im using MockRunner library to help me. This is how I do it :
I create a class to convert a list into result set
package com.edw.rs;
import com.mockrunner.mock.jdbc.MockResultSet;
import java.sql.ResultSet;
import java.util.List;
/**
*
* @author edw
*/
public class MyResultSet {
public ResultSet getResultSet(List<String> headers, List<List<Object>> data) throws Exception {
// validation
if (headers == null || data == null) {
throw new Exception("null parameters");
}
if (headers.size() != data.size()) {
throw new Exception("parameters size are not equals");
}
// create a mock result set
MockResultSet mockResultSet = new MockResultSet("myResultSet");
// add header
for (String string : headers) {
mockResultSet.addColumn(string);
}
// add data
for (List<Object> list : data) {
mockResultSet.addRow(list);
}
return mockResultSet;
}
}
and i tested it with my junit class
package com.edw.rs.test;
import com.edw.rs.MyResultSet;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.junit.Test;
/**
*
* @author edw
*/
public class MyResultSetTest {
private Logger logger = Logger.getLogger(MyResultSetTest.class);
public MyResultSetTest() {
}
@Test
public void testMyResultSet() throws Exception {
MyResultSet myResultSet = new MyResultSet();
List<String> headers = new ArrayList<String>();
headers.add("id");
headers.add("name");
headers.add("salary");
headers.add("age");
List<List<Object>> data = new ArrayList<List<Object>>();
for (int i = 0; i < 4; i++) {
List<Object> objects = new ArrayList<Object>();
objects.add(new Integer(i));
objects.add("name " + i);
objects.add(new Double(i));
objects.add(new Integer(20 + i));
data.add(objects);
}
ResultSet rs = myResultSet.getResultSet(headers, data);
while (rs.next()) {
logger.debug(rs.getString("id"));
logger.debug(rs.getString(2));
logger.debug(rs.getDouble("salary"));
logger.debug(rs.getInt(4));
logger.debug("------------------------");
}
}
}
this is the result on my IDE console
2010-08-18 18:13:09,112 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 0 2010-08-18 18:13:09,131 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 0 2010-08-18 18:13:09,132 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 0.0 2010-08-18 18:13:09,132 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 20 2010-08-18 18:13:09,133 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------ 2010-08-18 18:13:09,133 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 1 2010-08-18 18:13:09,189 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 1 2010-08-18 18:13:09,189 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 1.0 2010-08-18 18:13:09,189 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 21 2010-08-18 18:13:09,190 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------ 2010-08-18 18:13:09,190 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 2 2010-08-18 18:13:09,191 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 2 2010-08-18 18:13:09,218 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 2.0 2010-08-18 18:13:09,219 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 22 2010-08-18 18:13:09,219 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------ 2010-08-18 18:13:09,219 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:42 - 3 2010-08-18 18:13:09,220 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:43 - name 3 2010-08-18 18:13:09,225 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:44 - 3.0 2010-08-18 18:13:09,226 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:45 - 23 2010-08-18 18:13:09,226 [MyResultSetTest] DEBUG com.edw.rs.test.MyResultSetTest:46 - ------------------------
this is my project structure, FYI im using Netbeans 6.9.
all i can say is, Thank You mockrunner, you saved my life.
12 Comments
Serge
about 9 years agoIt'is very good expirience, but U have little mistake in MyResultSet row 20. This test work correctly only in this sample. Correction: for (List list : data) { if (list.size()==headers.size()) { mockResultSet.addRow(list); } }
Replyedwin
about 9 years agoHi Serge thank you for your comment, but could you elaborate more about class MyResultSet row 20? Because on row 20 is
if (headers.size() != data.size()) { throw new Exception("parameters size are not equals"); }AjAx
about 7 years agoHi Edwin, this method is so great, I've used it in my Class, otherwise there is one problem on the execpetion throwing on MyResultSet row 20, the if condition should not be the header size equals the data size but the data.list's size.
Replyedwin
about 7 years agohi AjAx thank you for your information :D
Habib Ali Machpud
about 6 years agoSomething strange in line 20, if (headers.size() != data.size()) Do you want to compare header size with data size? I think the right comparison is between header size and data.list size.
Replyedwin
about 6 years agoYes, sorry bib, i was wrong :D
ccab
about 6 years agoYou can also use the pojoSample.jar api which implements resultset.. Much more thorough solution. http://scn.sap.com/docs/DOC-6147
Replyedwin
about 6 years agoHi ccab, thank you for your information :)
Anupam Das
about 3 years agoHello Edwin, Greetings! Can you please share the code for "MockResultSet ". I am not able to add Column in the resultset and I want to refer your code for converting the arraylist to resultset.
Replypritesh
about 2 years agovery nice article. thanks a lot
Replypritesh
about 2 years agothanks for the lovely article ...really helped a lot. i just converted the above code in scala import java.sql.ResultSet import com.mockrunner.mock.jdbc.MockResultSet import scala.collection.mutable.ListBuffer class MyResultSet { @throws[Exception] def getResultSet(headers: List[String], data: ListBuffer[List[Object]]): ResultSet = { // validation if (headers == null || data == null) throw new Exception("null parameters") if (headers.size != data.size) throw new Exception("parameters size are not equals") // create a mock result set val mockResultSet = new MockResultSet("myResultSet") // add header for (clmn <- headers) { mockResultSet.addColumn(clmn) } // add data import scala.collection.JavaConversions._ for (list <- data) { mockResultSet.addRow(list) } mockResultSet } } import scala.collection.mutable.ListBuffer object TestMockResultSet { def main(args: Array[String]): Unit = { val myResultSet: MyResultSet = new MyResultSet val headers = List("id","name","salary","age") var data = new ListBuffer[List[Object]] for (i <- 0 to 3) { val objects:List[Object] = List(new Integer(i),"name"+i,new Integer(i),new Integer(20+i)) data += objects } val rs = myResultSet.getResultSet(headers, data) while(rs.next()){ println(rs.getObject("id")) println(rs.getObject("name")) println(rs.getObject("age")) } } }
ReplyAnkita
about 2 days agoHi Ed, I need help on below where I want to return all records from a table as resultset. but showing error here is my code public class Test { int i; String s; ResultSet resultSetSubject ; Statement stm; List lSubject ; Connection con; public Test()throws SQLException { con= DbUtil.getConnection(); } public ResultSet getAllSubjects() throws SQLException{ try { stm = con.createStatement(); resultSetSubject = stm.executeQuery("select * from subject"); while (resultSetSubject.next()) { resultSetSubject.getInt(1); resultSetSubject.getString(2); //System.out.println(resultSetSubject.getInt("id")+" "+resultSetSubject.getString("name")); } } catch (Exception e) { System.out.println(e); } return resultSetSubject; } here is my Junit test code ResultSet rs = T.getAllSubjects(); rs.next(); assertEquals(rs.getInt("id"),1); assertEquals(rs.getString("name"),"Maths"); rs.next();
Reply