java - 启动通过 Apache Cayenne ORM 返回数据的存储过程不起作用

Question

我正在尝试在我的 MariaDB 数据库中启动存储过程。我使用 Apache Cayenne 作为 ORM。错误消息表明它找不到存储过程，但对于不返回数据的过程，我已经可以使用此方法了。任何熟悉存储过程和 Apache Cayenne 的人都可以提供一些指导吗？

存储过程可在 Cayenne 使用的 datamap.map.xml 文件中找到:

<procedure name="copyRecipe" catalog="foodbase" returningValue="true">
    <procedure-parameter name="id_in" type="INTEGER" direction="in"/>
    <procedure-parameter name="id_new" type="INTEGER" direction="out"/>
</procedure>

我使用以下代码来启动 SP。

  public void copyRecipes() {
        ProcedureQuery query = new ProcedureQuery("copyRecipe");

        // Set "IN" parameter values
        query.addParameter("id_in", recipes.getId());

        // run query
        System.out.println("TEst 1");
        try {
        QueryResponse result = context.performGenericQuery(query);

        System.out.println("Test 2");

        for (result.reset(); result.next();) {
            if (result.isList()) {
                List<String> objIdList = (List<String>) result.currentList();
                Iterator<String> it = objIdList.iterator();


                recipes = getRecipes(it.next());
            } else {
                FacesContext context = FacesContext.getCurrentInstance();

                context.addMessage(null, new FacesMessage("Copy recipe failed"));

            }
        }

        } catch (Exception e) {
            FacesContext context = FacesContext.getCurrentInstance();

            context.addMessage(null, new FacesMessage("Copy recipe failed"));
        }



  }

启动方法 copyRecipes() 时，Tomcat 返回以下错误消息:

TEst 1
mar 30, 2016 6:16:12 FM org.apache.cayenne.log.CommonsJdbcEventLogger logBeginTransaction
INFO: --- transaction started.
mar 30, 2016 6:16:12 FM org.apache.cayenne.log.CommonsJdbcEventLogger logQuery
INFO: {? = call copyRecipe(?)} [bind: 1:425, 2:'[OUT]']
mar 30, 2016 6:16:12 FM org.mariadb.jdbc.internal.mysql.MySQLProtocol getResult
WARNING: Could not execute query select copyRecipe(@_jdbc_var_2) into @_jdbc_var_1: FUNCTION foodbase.copyRecipe does not exist
mar 30, 2016 6:16:12 FM org.apache.cayenne.log.CommonsJdbcEventLogger logQueryError
INFO: *** error.
java.sql.SQLSyntaxErrorException: FUNCTION foodbase.copyRecipe does not exist
    at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:138)
    at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:106)
    at org.mariadb.jdbc.MySQLStatement.executeQueryEpilog(MySQLStatement.java:252)
    at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:278)
    at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:369)
    at org.mariadb.jdbc.MySQLCallableStatement.execute(MySQLCallableStatement.java:1251)
    at org.apache.cayenne.dba.mysql.MySQLProcedureAction.performAction(MySQLProcedureAction.java:58)
    at org.apache.cayenne.access.DataNodeQueryAction.runQuery(DataNodeQueryAction.java:87)
    at org.apache.cayenne.access.DataNode.performQueries(DataNode.java:280)
    at org.apache.cayenne.access.DataDomainQueryAction.runQuery(DataDomainQueryAction.java:453)
    at org.apache.cayenne.access.DataDomainQueryAction.access$000(DataDomainQueryAction.java:70)
    at org.apache.cayenne.access.DataDomainQueryAction$2.transform(DataDomainQueryAction.java:426)
    at org.apache.cayenne.access.DataDomain.runInTransaction(DataDomain.java:877)
    at org.apache.cayenne.access.DataDomainQueryAction.runQueryInTransaction(DataDomainQueryAction.java:423)
    at org.apache.cayenne.access.DataDomainQueryAction.execute(DataDomainQueryAction.java:122)
    at org.apache.cayenne.access.DataDomain.onQueryNoFilters(DataDomain.java:758)
    at org.apache.cayenne.access.DataDomain$DataDomainQueryFilterChain.onQuery(DataDomain.java:1009)
    at org.apache.cayenne.access.DataDomain.onQuery(DataDomain.java:748)
    at org.apache.cayenne.util.ObjectContextQueryAction.runQuery(ObjectContextQueryAction.java:350)
    at org.apache.cayenne.util.ObjectContextQueryAction.executePostCache(ObjectContextQueryAction.java:106)
    at org.apache.cayenne.util.ObjectContextQueryAction.execute(ObjectContextQueryAction.java:93)
    at org.apache.cayenne.access.DataContext.onQuery(DataContext.java:989)
    at org.apache.cayenne.access.DataContext.performGenericQuery(DataContext.java:948)
    at controller.RecepieController.copyRecipes(RecepieController.java:172)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at org.apache.el.parser.AstValue.invoke(AstValue.java:278)
    at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:274)
    at org.jboss.weld.util.el.ForwardingMethodExpression.invoke(ForwardingMethodExpression.java:40)
    at org.jboss.weld.el.WeldMethodExpression.invoke(WeldMethodExpression.java:50)
    at com.sun.faces.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:105)
    at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:87)
    at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:102)
    at javax.faces.component.UICommand.broadcast(UICommand.java:315)
    at javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:790)
    at javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:1282)
    at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:81)
    at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
    at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:198)
    at javax.faces.webapp.FacesServlet.service(FacesServlet.java:646)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:100)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1041)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:603)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:722)
Caused by: org.mariadb.jdbc.internal.common.QueryException: FUNCTION foodbase.copyRecipe does not exist
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:984)
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1038)
    at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:1020)
    at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:271)
    ... 57 more

编辑:以下是存储过程的定义方式:

CREATE DEFINER=`foodbase_admin`@`localhost` PROCEDURE `copyRecipe`(IN `id_in` INT, OUT `id_new` INT)
    NO SQL
    COMMENT 'Copy a recipe w associated ingredients, structure and categories'
BEGIN
#DECLARE id_new INT DEFAULT 0;

INSERT INTO recipes (Username, ID_privileges,Comment,Name,No_of_servings,Instruction,Source,Rating) SELECT Username,ID_privileges,Comment,CONCAT(Name," - copy") AS Name,No_of_servings,Instruction,Source,Rating FROM recipes WHERE ID = id_in;

SELECT LAST_INSERT_ID() INTO id_new;

INSERT INTO ingredients (
 Username,ID_privileges,Comment,ID_recipes,ID_groceries,ID_units,Amount)
    SELECT Username,ID_privileges,Comment,id_new AS ID_recipes,ID_groceries,ID_units,Amount FROM
    ingredients WHERE ID_recipes = id_in;

INSERT INTO recipe_structures (
 Username,ID_privileges,Comment,ID_recipes_super,ID_recipes_sub,Factor,Date)
SELECT Username,ID_privileges,Comment,id_new AS ID_recipes_super,ID_recipes_sub,Factor,Date FROM recipe_structures
WHERE ID_recipes_super = id_in;

INSERT INTO recipe_categories (
    Username,ID_privileges,Comment,ID_recipes,ID_categories)
    SELECT Username,ID_privileges,Comment,id_new AS ID_recipes,ID_categories FROM recipe_categories WHERE ID_recipes = id_in;



END

编辑2:我将此用于另一个程序。它看起来像这样:

CREATE DEFINER=`foodbase_admin`@`localhost` PROCEDURE `copyStructRecipeIngredients`(IN `id_in` INT, IN `factor` DOUBLE, IN `shoplist_in` INT)
    NO SQL
    SQL SECURITY INVOKER
BEGIN

CALL StructRecipeWeighted(id_in,factor);

#SELECT * FROM structrecipesweighted;

DROP TABLE IF EXISTS tmptbl10;
DROP TABLE IF EXISTS tmptbl11;

CREATE TEMPORARY TABLE tmptbl10 (
Username VARCHAR(255),
ID_privileges int,
ID_groceries int,
ID_units int,
Amount double
    );

INSERT INTO tmptbl10 (Username, ID_privileges, ID_groceries, ID_units, Amount)
SELECT ingredients.Username AS Username, ingredients.ID_privileges AS ID_privileges, ingredients.ID_groceries AS ID_groceries, ingredients.ID_units AS ID_units, structrecipesweighted.Weight*ingredients.Amount AS Amount FROM ingredients
JOIN structrecipesweighted ON ingredients.ID_recipes = structrecipesweighted.ID
    ;

#SELECT * from tmptbl10;

CREATE TEMPORARY TABLE tmptbl11 (
Username VARCHAR(255),
ID_privileges int,
ID_groceries int,
ID_units int,
Amount double,
ID_statuses int
    );

INSERT INTO tmptbl11 (Username, ID_privileges, ID_groceries, ID_units, Amount, ID_statuses)
    SELECT tmptbl10.Username AS Username, tmptbl10.ID_privileges AS ID_privileges, tmptbl10.ID_groceries AS ID_groceries, tmptbl10.ID_units AS ID_units, tmptbl10.Amount AS Amount, groceries.ID_statuses AS ID_statuses
    FROM tmptbl10 JOIN groceries ON tmptbl10.ID_groceries = groceries.ID;

#SELECT * from tmptbl11;

INSERT INTO shoppinglist_items (Username, ID_privileges, ID_shoppinglists, ID_groceries, ID_units, ID_statuses, Amount) 
    SELECT Username AS Username, ID_privileges AS ID_privileges, shoplist_in AS ID_shoppinglists, ID_groceries AS ID_groceries, ID_units AS ID_units, ID_statuses AS ID_statuses, SUM(Amount) AS Amount FROM tmptbl11
    GROUP BY ID_groceries, ID_units
    ;

#SELECT * from shoppinglist_items WHERE ID_shoppinglists = shoplist_in;


END

我以同样的方式调用它:

@SuppressWarnings("unused")
private void copyIngredients(Shoppinglists sl, Recipes base) {

    try {

        ProcedureQuery query = new ProcedureQuery("copyStructRecipeIngredients");
        System.out.println("test e");

        // Set "IN" parameter values
        query.addParameter("id_in", base.getId());
        query.addParameter("factor", base.getNoOfServings());
        query.addParameter("shoplist_in", sl.getId());

        // run query
        QueryResponse result = contextShopList.performGenericQuery(query);
    } catch (Exception e) {
        FacesContext context = FacesContext.getCurrentInstance();

        context.addMessage(null, new FacesMessage("Creation of shopping list fail.\n"+e.getLocalizedMessage()));

    }

Answer 1

调用返回值的存储过程时似乎存在一些错误。在 datamap.xml 文件中，我删除了 xml 标记过程中名为 returnedValue="true"的属性。然后存储过程在数据库中执行。

以下是我修改 datamap.xml 文件的方法。与我的问题中的比较。

<procedure name="copyRecipe" catalog="foodbase">
    <procedure-parameter name="id_in" type="INTEGER" direction="in"/>
    <procedure-parameter name="id_new" type="INTEGER" direction="out"/>

</procedure>