java.sql.SQLException when trying to count rows in Oracle

Question

I have a problem in Java managed bean which I cannot find.

    import java.io.Serializable;
import javax.enterprise.context.SessionScoped;
// or import javax.faces.bean.SessionScoped;
import javax.inject.Named;
/*
 * include SQL Packages
 */
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import javax.annotation.PostConstruct;
import javax.sql.DataSource;
import javax.annotation.Resource;
import javax.faces.bean.ViewScoped;
// or import javax.faces.bean.ManagedBean;   

import org.glassfish.osgicdi.OSGiService;

import org.primefaces.model.chart.CartesianChartModel;
import org.primefaces.model.chart.ChartSeries;

@Named("DashboardController")
@SessionScoped
public class Dashboard implements Serializable
{

    /*
     * Call the Oracle JDBC Connection driver
     */
    @Resource(name = "jdbc/Oracle")
    private DataSource ds;
    private CartesianChartModel categoryModel;

    public Dashboard() throws SQLException
    {
        createCategoryModel();
    }

    public CartesianChartModel getCategoryModel()
    {
        return categoryModel;
    }

    private void createCategoryModel() throws SQLException
    {
        categoryModel = new CartesianChartModel();

        // Active Accounts

        ChartSeries ActiveAccounts = new ChartSeries();
        ActiveAccounts.setLabel("Active Accounts");

        ActiveAccounts.set("Active Accounts", CheckDataDB("USERS","USERSTATUS","Active"));

        categoryModel.addSeries(ActiveAccounts);

        // Blocked Accounts

        ChartSeries BlockedAccounts = new ChartSeries();
        BlockedAccounts.setLabel("Blocked Accounts");

        BlockedAccounts.set("Blocked Accounts", CheckDataDB("USERS","USERSTATUS","Blocked"));

        categoryModel.addSeries(BlockedAccounts);

        // Active Groups

        ChartSeries ActiveGroups = new ChartSeries();
        ActiveGroups.setLabel("Active Groups");

        ActiveGroups.set("Active Groups", CheckDataDB("GROUPS","GROUPSTATUS","Active"));

        categoryModel.addSeries(ActiveGroups);

        // Blocked Groups

        ChartSeries BlockedGroups = new ChartSeries();
        BlockedGroups.setLabel("Blocked Groups");

        BlockedGroups.set("Blocked Groups", CheckDataDB("GROUPS","GROUPSTATUS","Blocked"));

        categoryModel.addSeries(BlockedGroups);



    }

    // Database data

    public int CheckDataDB(String DBtablename, String DBStatus, String DBArgument) throws SQLException {

        int count = 0;
        String SQLStatement = null;

        if (ds == null) {
            throw new SQLException();
        }

        Connection conn = ds.getConnection();
        if (conn == null) {
            throw new SQLException();
        }

        PreparedStatement ps = null;

        try {
            conn.setAutoCommit(false);
            boolean committed = false;
            try {
                SQLStatement = "SELECT count(*) FROM ? WHERE ? = ?";

                ps = conn.prepareStatement(SQLStatement);
                ps.setString(1, DBtablename);
                ps.setString(2, DBStatus);                
                ps.setString(3, DBArgument);

                ResultSet result = ps.executeQuery();

                if (result.next()) {
                    count = result.getInt(1);
                }

                conn.commit();
                committed = true;
            } finally {
                if (!committed) {
                    conn.rollback();
                }
            }
        } finally {
            /* Release the resources */
            ps.close();
            conn.close();
        }

        return count;
    }
}

When I try to run the code I get this error stack:

com.sun.faces.mgbean.ManagedBeanCreationException: Cant instantiate class: com.DX_57.AC_57.Dashboard.
    at com.sun.faces.mgbean.BeanBuilder.newBeanInstance(BeanBuilder.java:193)
    at com.sun.faces.mgbean.BeanBuilder.build(BeanBuilder.java:102)
    at com.sun.faces.mgbean.BeanManager.createAndPush(BeanManager.java:409)
    at com.sun.faces.mgbean.BeanManager.create(BeanManager.java:269)
    at com.sun.faces.el.ManagedBeanELResolver.resolveBean(ManagedBeanELResolver.java:244)
    at com.sun.faces.el.ManagedBeanELResolver.getValue(ManagedBeanELResolver.java:116)
    at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:176)
    at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:203)
    at com.sun.el.parser.AstIdentifier.getValue(AstIdentifier.java:103)
    at com.sun.el.parser.AstValue.getValue(AstValue.java:179)
    at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:224)
    at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:109)
    at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194)
    at org.primefaces.component.chart.UIChart.getValue(UIChart.java:75)
    at org.primefaces.component.chart.bar.BarChartRenderer.encodeOptions(BarChartRenderer.java:62)
    at org.primefaces.component.chart.bar.BarChartRenderer.encodeScript(BarChartRenderer.java:51)
    at org.primefaces.component.chart.bar.BarChartRenderer.encodeEnd(BarChartRenderer.java:36)
    at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:875)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1786)
    at javax.faces.render.Renderer.encodeChildren(Renderer.java:168)
    at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1779)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1782)
    at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1782)
    at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:424)
    at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:125)
    at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:594)
    at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
    at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
    at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
    at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
    at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
    at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
    at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
    at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
    at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
    at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
    at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
    at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
    at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
    at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
    at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
    at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
    at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
    at java.lang.Thread.run(Thread.java:722)
Caused by: java.sql.SQLException
    at com.DX_57.AC_57.Dashboard.CheckDataDB(Dashboard.java:116)
    at com.DX_57.AC_57.Dashboard.createCategoryModel(Dashboard.java:73)
    at com.DX_57.AC_57.Dashboard.<init>(Dashboard.java:56)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
    at java.lang.Class.newInstance0(Class.java:372)
    at java.lang.Class.newInstance(Class.java:325)
    at com.sun.faces.mgbean.BeanBuilder.newBeanInstance(BeanBuilder.java:188)
    ... 53 more

Can you help me to solve the problem? I want to make the code more compact using one Java method to count the rows.

This has completely nothing to do with JSF. Please learn to separate the concerns and create standalone/reuseable classes with a clear own responsibility instead of tight coupling everything into a single class. DB code doesn't belong directly in a JSF backing bean class. A standalone class can easily be unit-tested using a plain Java application class with main() method and problems/causes can more easily be isolated. — BalusC
– BalusC, Commented Jul 24, 2012 at 11:39

Mikko Maunu · Accepted Answer · 2012-07-23 18:35:02Z

What will never work is current approach of setting name of table and name of column to the PreparedStatement in following piece of code:

String SQLStatement = null;
...
SQLStatement = "SELECT count(*) FROM ? WHERE ? = ?";
ps = conn.prepareStatement(SQLStatement);
ps.setString(1, DBtablename);
ps.setString(2, DBStatus);                
ps.setString(3, DBArgument);

Reason being that prepared statement should be constructed with syntactically correct SQL (and typically parametric) and then values for parameters should be set. setString(int, String) is not generic tool to glue SQL statement together, it is for setting value for parameter.

What will work is something like following (as a fast fix to this code, not as a any example of good style):

//first we construct reasonable SQL statement with one parameter
SQLStatement = "SELECT count(*) FROM "+DBtablename+ " WHERE "+DBStatus+"=?";
//then we set value for parameter
ps.setString(1, DBArgument);

Zarathustra · Accepted Answer · 2012-07-23 15:22:20Z

1

You don't have a SQLException the problem happeny much earlier:

com.sun.faces.mgbean.ManagedBeanCreationException: Cant instantiate class: com.DX_57.AC_57.Dashboard.

Thats your Exception to search for.

answered Jul 23, 2012 at 15:22

Zarathustra

2,9634 gold badges36 silver badges63 bronze badges

3 Comments

user1285928 Over a year ago

When I remove the Java method public int CheckDataDB(String DBtablename, String DBStatus, String DBArgument) the JSF is working. Any idea what part of the code is not working?

Mikko Maunu Over a year ago

No, ManagedBeanCreationException is not exception to search for. In this case (and in general quite often) one exception occurs because of other, other occurs because of third. Exception is said to be cause of other exception. If everything in chain is well behaving and stack trace is not violently cut, good way take a look to original cause is to start from last occurrence of "Caused by: ".

BalusC Over a year ago

You're completely misinterpreting the problem.

Collectives™ on Stack Overflow

java.sql.SQLException when trying to count rows in Oracle

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related