How to create a dataTable without knowing the columns (simple database table display)
0 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   Arnold_Maderthaner
Posted On:   Tuesday, December 11, 2007 04:01 AM

Hi ! I want to display a database result (ResultSet) in a JSF table and I'm trying this the last 2 days. Here is a description: I learned that I have to use ResultSetDataModel to display a java.sql.ResultSet in a JSF dataTable. I added a Bean which executes the sql command and where the dataTable can grab the data. The problem is that I get following exception: 09:27:59,136 ERROR [[facesservlet]] Servlet.service() for servlet facesservlet threw exception javax.faces.FacesException: javax.faces.FacesException: java.sql.SQLException: The result set is closed. at org.ajax4jsf.component.UIDataAdaptor.iterate(UIDataAdaptor.java:952) at org.ajax4jsf.component.UIDataAdaptor.processDecodes(UIDataA   More>>

Hi !


I want to display a database result (ResultSet) in a JSF table and I'm trying this the last 2 days.
Here is a description:
I learned that I have to use ResultSetDataModel to display a java.sql.ResultSet in a JSF dataTable. I added a Bean which executes the sql command and where the dataTable can grab the data. The problem is that I get following exception:

			
09:27:59,136 ERROR [[facesservlet]] Servlet.service() for servlet facesservlet threw exception
javax.faces.FacesException: javax.faces.FacesException: java.sql.SQLException: The result set is closed.
at org.ajax4jsf.component.UIDataAdaptor.iterate(UIDataAdaptor.java:952)
at org.ajax4jsf.component.UIDataAdaptor.processDecodes(UIDataAdaptor.java:975)
at org.ajax4jsf.component.UIDataAdaptor.processDecodes(UIDataAdaptor.java:985)
at com.sun.facelets.component.UIRepeat.process(UIRepeat.java:363)
at com.sun.facelets.component.UIRepeat.processDecodes(UIRepeat.java:434)
at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:602)
at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:602)
at org.ajax4jsf.component.UIInclude.processDecodes(UIInclude.java:153)
at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:602)
at org.richfaces.component.UISwitchablePanel.processDecodes(UISwitchablePanel.java:155)
at org.richfaces.component.UITabPanel.processDecodes(UITabPanel.java:122)
at javax.faces.component.UIComponentBase.processDecodes(UIComponentBase.java:602)
at javax.faces.component.UIViewRoot.processDecodes(UIViewRoot.java:135)
at org.ajax4jsf.component.AjaxViewRoot.access$001(AjaxViewRoot.java:57)
at org.ajax4jsf.component.AjaxViewRoot$1.invokeRoot(AjaxViewRoot.java:260)
at org.ajax4jsf.context.JsfOneOneInvoker.invokeOnRegionOrRoot(JsfOneOneInvoker.java:56)
at org.ajax4jsf.context.AjaxContextImpl.invokeOnRegionOrRoot(AjaxContextImpl.java:173)
at org.ajax4jsf.component.AjaxViewRoot.processDecodes(AjaxViewRoot.java:276)
at org.apache.myfaces.lifecycle.LifecycleImpl.applyRequestValues(LifecycleImpl.java:219)
at org.apache.myfaces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:71)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:137)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:141)
at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:281)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(ReplyHeaderFilter.java:96)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:175)
at org.jboss.web.tomcat.security.JaccContextValve.invoke(JaccContextValve.java:74)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.jboss.web.tomcat.tc5.jca.CachedConnectionValve.invoke(CachedConnectionValve.java:156)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)
at org.apache.tomcat.util.net.MasterSlaveWorkerThread.run(MasterSlaveWorkerThread.java:112)
at java.lang.Thread.run(Thread.java:613)
Caused by: javax.faces.FacesException: java.sql.SQLException: The result set is closed.
at javax.faces.model.ResultSetDataModel.isRowAvailable(ResultSetDataModel.java:130)
at javax.faces.model.ResultSetDataModel.setRowIndex(ResultSetDataModel.java:173)
at org.ajax4jsf.model.SequenceDataModel.walk(SequenceDataModel.java:99)
at org.ajax4jsf.component.UIDataAdaptor.walk(UIDataAdaptor.java:968)
at org.ajax4jsf.component.UIDataAdaptor.iterate(UIDataAdaptor.java:950)
... 43 more
Caused by: java.sql.SQLException: The result set is closed.
at org.jboss.resource.adapter.jdbc.WrappedResultSet.checkState(WrappedResultSet.java:1939)
at org.jboss.resource.adapter.jdbc.WrappedResultSet.absolute(WrappedResultSet.java:111)
at javax.faces.model.ResultSetDataModel.isRowAvailable(ResultSetDataModel.java:126)
... 47 more



For getting the database connection I have to use a javax.sql.DataSource from JBoss. Here is my code for getting data:

			

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.faces.model.DataModel;
import javax.faces.model.ResultSetDataModel;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class WrappedResultSet {
DataModel dataFromResultSet = null;

List metaDataColumnNamesAsList = null;

public WrappedResultSet(ResultSet result) {
try {
setResultSet(result);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

public WrappedResultSet(String dsJndiName, String sql) {
Connection con = null;
Statement stmt = null;
DataSource ds;
try {
Context jndiCtx = new InitialContext();
try {
ds = (DataSource) jndiCtx.lookup(dsJndiName);
} finally {
try {
jndiCtx.close();
} catch (NamingException ignore) {
}
}
} catch (NamingException ne) {
throw new RuntimeException("Failed to access Data Source: "
+ dsJndiName, ne);
}
try {
con = ds.getConnection();
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
setResultSet(stmt.executeQuery(sql));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ignore) {
}
stmt = null;
}
if (con != null) {
try {
con.close();
} catch (SQLException ignore) {
}
con = null;
}
}

public void setResultSet(ResultSet rs) throws SQLException {
this.dataFromResultSet = new ResultSetDataModel(rs);
ResultSetMetaData metaData = rs.getMetaData();
this.metaDataColumnNamesAsList = new ArrayList ();
for (int i = 1; i <= metaData.getColumnCount(); i++) {
this.metaDataColumnNamesAsList.add(metaData.getColumnLabel(i));
}
dataFromResultSet.setRowIndex(dataFromResultSet.getRowCount());
dataFromResultSet.setRowIndex(0);
}

public List getMetaDataColumnNamesAsList() {
System.out.println("metadata:" + metaDataColumnNamesAsList);
return metaDataColumnNamesAsList;
}

public DataModel getDataFromResultSet() {
System.out.println("datafromresultset:" + dataFromResultSet);
return dataFromResultSet;
}
}



and this is my JSF code:
			


















databaseQueryResults gives me a List of WrappedResultSets as I want to query more then one database.

aneony could help me ?

yours

Arnold    <<Less
About | Sitemap | Contact