java - 尝试对Oracle中的行进行计数时出现java.sql.SQLException

我在Java托管bean中有一个找不到的问题。

    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;
    }
}

当我尝试运行代码时，出现此错误堆栈：

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

您能帮我解决问题吗？我想使用一种Java方法对行进行计数，从而使代码更紧凑。

最佳答案

在下面的代码中，将表名和列名设置为PreparedStatement的当前方法永远是行不通的：

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

原因是应该使用语法正确的SQL（通常是参数化的）构造准备好的语句，然后设置参数值。 setString(int, String)不是将SQL语句粘合在一起的通用工具，它是用于设置参数值的。

可以使用的方法类似于以下内容（作为对此代码的快速修复，而不是任何具有良好风格的示例）：

//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);