Can't do more one query in Camunda Java delegate

22 Views Asked by At

JDK - Liberica 11.0.22 Camunda version - 7.17.0

Datasource is configured in tomcat context.xml

<Resource name="jdbc/tstECOM" auth="Container"
          type="javax.sql.DataSource" driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/ecom"
          username="user" password="password" maxTotal="20" maxIdle="10" maxWaitMillis="10000"/>

also in web.xml in WEB-INF of Camunda app

<resource-ref>
        <description>tstECOM</description>
        <res-ref-name>jdbc/tstECOM</res-ref-name>
        <res-type>javax.sql.DataSource</res-type>
        <res-auth>Container</res-auth>
    </resource-ref>

I have Java delegate which get new instance of Store object and fill them by different values on some procedures, but normally works only first, if i add another Camunda returns me next.

Cannot instantiate process definition Process_1avqn50:15:002afbf8-e4e3-11ee-9456-0242ac110002: An exception occurred in the persistence layer. Please check the server logs for a detailed message and the entire exception stack trace. [ start-instance-error ]

Code of delegate:

package com.stockee.delegates;

import com.stockee.essentials.Store;
import org.camunda.bpm.engine.delegate.DelegateExecution;
import org.camunda.bpm.engine.delegate.JavaDelegate;
import org.camunda.bpm.engine.delegate.BpmnError;
import javax.naming.InitialContext;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class getStore implements JavaDelegate{
    @Override
    public void execute(DelegateExecution execution) throws Exception{
        //подключение к БД ЕКОМ получим из контекста Tomcat
        InitialContext ctx = new InitialContext();
        DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/tstECOM");
        try (Connection connection = ds.getConnection()) {
            try {
                Store Store = new Store(connection);
                execution.setVariable("store", Store.serializeStore());
            } catch (Exception e) {
                throw new BpmnError("cantCreateInstanceOfStore", "Не удалось создать объект");
            }
        } catch (SQLException e) {
            throw new BpmnError("cantGetStoreFromECOM", "Нет подключения к БД ЕКОМ!");
        }
    }
}

Code of Store class

package com.stockee.essentials;

import com.fasterxml.jackson.databind.ObjectMapper;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Random;

public class Store {
    String ID; // АУ
    Stock Stock = new Stock(); //собственный остаток
    private HashMap<String,List<Hub>> Hub = new HashMap<String,List<Hub>>(); //хабы и их остатки
    static java.sql.Connection ECOM = null;

    public Store(Connection ECOM) throws SQLException{
        this.ECOM = ECOM;
        getStoreFromECOM();
        getStocksForStore();
    }

    /*
        Метод подключается к ЕКОМ, забирает все сторы к которым привязаны хабы
        и отдает случайный из них
     */

    public void getStoreFromECOM(){
        List<String> Stores = new ArrayList<String>();
        try{
            Statement st = ECOM.createStatement();
            ResultSet rs;
            rs = st.executeQuery("SELECT s.store_id, hts.hub_store_id, hts.level_id\n" +
                    "FROM stores s\n" +
                    "INNER JOIN hub_to_stores hts ON s.store_id = hts.hub_store_id\n" +
                    "WHERE hts.level_id = 0 OR hts.level_id = 1 OR hts.level_id = 2\n" +
                    "GROUP BY hub_store_id, level_id\n");
            while (rs.next()){
                Stores.add(rs.getString("store_id"));
            }
        } catch (SQLException e) {
            e.getMessage();
        }
        Random random = new Random();
        this.ID = Stores.get(random.nextInt(Stores.size()));
    }
    /*
        Метод для заполнения стора собственными остатками
     */
    public void getStocksForStore() throws SQLException{
        try{
            this.Stock.Owner = this.ID; //связали остатки с стором
            this.Stock.getStocksForThisStore(ECOM);
        } catch (SQLException e) {
            e.getMessage();
        }
    }
    /*
        Метод для получения связанных с стором хабов и заполнения их остатками
     */
    public void getHubForThisStore() throws SQLException{
        try {
            String query = "SELECT hub_store_id, level_id FROM\n" +
                    "hub_to_stores\n" +
                    "WHERE store_id = ?\n";
            PreparedStatement pst = ECOM.prepareStatement(query);
            pst.setString(1, this.ID);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                Hub Hub = new Hub();
                List<Hub> List = new ArrayList<Hub>();
                Hub.setID(rs.getString("hub_store_id"));
                Hub.getStocksForStore();
                this.Hub.put(rs.getString("level_id"),List); //заводим подсписок сторов
                this.Hub.get(rs.getString("level_id")).add(Hub); //кладем если ключ совпал
            }
        } catch (SQLException e) {
            e.getMessage();
        }
    }

    public String serializeStore() throws Exception{
        ObjectMapper mapper = new ObjectMapper();
        return mapper.writerWithDefaultPrettyPrinter().writeValueAsString(this);
    }
    /*
        Блок для сериализации - десериализации объекта
        геттеры-сеттеры для сериализатора
     */
    public void setID(String ID){
        this.ID = ID;
    }
    public String getID(){
        return this.ID;
    }

    public void setStock(Stock Stock){
        this.Stock = Stock;
    }
    public Stock getStock(){
        return this.Stock;
    }

    public void setHub(HashMap<String,List<Hub>> Hub){
        this.Hub = Hub;
    }
    public HashMap<String,List<Hub>> getHub(){
        return this.Hub;
    }
}

Code of Stock class

package com.stockee.essentials;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Stock {
    String Owner = null; //отношение к стору
    List<SKU> SKU = new ArrayList<SKU>(); //строки остатков

    public Stock(){ 
    }

    public void setSKU (List<SKU> SKU){
        this.SKU = SKU;
    }
    public List<SKU> getSKU(){
        return this.SKU;
    }

    //метод забора остатка из ЕКОМ
    public void getStocksForThisStore(Connection connection) throws SQLException{
        try {
            String query = "SELECT good_id, quantity, reserve_quantity,\n" +
                    "available_quantity\n" +
                    "FROM original_stock\n" +
                    "WHERE store_id = ?\n";
            PreparedStatement pst = connection.prepareStatement(query);
            pst.setString(1, this.Owner);
            ResultSet rs = pst.executeQuery();
            while (rs.next()){
                SKU SKU = new SKU();
                SKU.goodID = rs.getInt("good_id");
                SKU.quantity = rs.getFloat("quantity");
                SKU.reserve = rs.getFloat("reserve_quantity");
                SKU.available = rs.getFloat("available_quantity");
                this.SKU.add(SKU);
            }
        } catch (SQLException e) {
            e.getMessage();
        }
    }

    // класс обертка для хранения остатка по SKU
    // get/set для сериализации
    private class SKU {
        Integer goodID;
        Float quantity;
        Float reserve;
        Float available;

        public void setGoodID(Integer GoodID){
            this.goodID = GoodID;
        }
        public Integer getGoodID(){
            return this.goodID;
        }
        public void setQuantity(Float Quantity){
            this.quantity = Quantity;
        }
        public Float getQuantity(){
            return this.quantity;
        }
        public void setReserve(Float Reserve){
            this.reserve = Reserve;
        }
        public Float getReserve(){
            return this.reserve;
        }
        public void setAvailable(Float Available){
            this.available = Available;
        }

        public Float getAvailable(){
            return this.available;
        }
    }
}

Hub class

package com.stockee.essentials;

import java.sql.Connection;
import java.sql.SQLException;

public class Hub extends Store{
    public Hub() throws SQLException {
       super(ECOM);
    }
}

I think something get wrong with connection when code get to getStocksForStore(), because first method works correctly. I tried to put all Stocks methods to constructor, use it in delegate each by one in order - but correctly works only first method

0

There are 0 best solutions below