by

Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    Per-Åke Minborg
    @minborg
    @solangepaz Which Speedment version are you using? I would use the aggregator for this problem (requires “free” or “enterprise”)
    raghav-chamarthy
    @raghav-chamarthy
    Hey guys, we have an application that reads XML data, parses it using JAXB, does some transformation and validation and writes it to dynamo DB.
    The problem is we need to write custom code to onboard every new entity , and that makes us slow, can I use speedment to generate auto mapper code or any other open source library?? Thank you
    Per-Åke Minborg
    @minborg
    Yes you can. Speedment can generate standard SQL code and also you can plug in your own code generators. Contact me at minborg@speedment.com and I can tell you more.
    solangepaz
    @solangepaz
    @minborg, I am using the opensource version, so this query can not reproduce?
    Per-Åke Minborg
    @minborg
    You can but it is a bit tricky. You can create a stream over the line items and then apply collect(groupingBy(…)))
    You can create a custom “down stream collector” that does all the avg(), sum() etc.
    There is an open issue for creating these queries in an easier way.
    solangepaz
    @solangepaz
    @minborg Thank you. And is this query possible?
    select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from PART, SUPPLIER, PARTSUPP, NATION, REGION 
    where p_partkey = ps_partkey 
    and s_suppkey = ps_suppkey 
    and p_size = 30 
    and p_type like '%STEEL' 
    and s_nationkey = n_nationkey 
    and n_regionkey = r_regionkey 
    and r_name = 'ASIA' 
    and ps_supplycost
     = (select min(ps_supplycost) from PARTSUPP, SUPPLIER, NATION, REGION 
     where p_partkey = ps_partkey 
     and s_suppkey = ps_suppkey 
     and s_nationkey = n_nationkey 
     and n_regionkey = r_regionkey 
     and r_name = 'ASIA')
     order by s_acctbal desc, n_name, s_name, p_partkey limit 100;
    I already have this
     JoinComponent joinComponent = app.getOrThrow(JoinComponent.class);
            Join<Tuple5<Partsupp, Part, Supplier, Nation, Region>> join = joinComponent
                    .from(PartsuppManager.IDENTIFIER)
                    .where(Partsupp.PS_SUPPLYCOST.equal())
                    .innerJoinOn(Part.P_PARTKEY).equal(Partsupp.PS_PARTKEY)
                    .where(Part.P_SIZE.equal(30).and(Part.P_TYPE.endsWith("STEEL")))
                    .innerJoinOn(Supplier.S_SUPPKEY).equal(Partsupp.PS_SUPPKEY)
                    .innerJoinOn(Nation.N_NATIONKEY).equal(Supplier.S_NATIONKEY)
                    .innerJoinOn(Region.R_REGIONKEY).equal(Nation.N_REGIONKEY)
                    .where(Region.R_NAME.equal("ASIA"))
                    .build(Tuples::of);
    But I can not do the subquery
    Per-Åke Minborg
    @minborg
    I suggest that you do the sub query first and store the min value in a separate variable. This vaiable is then used in a where clause.
    solangepaz
    @solangepaz

    Thank you. And how do I get this query?

    SELECT avg (l_quantity) as avg_qty
    FROM
         lineitem
    ONDE
         l_shipdate <= date '1998-12-01' - interval '90' day
    GROUP BY
         l_returnflag,
         l_linestatus;

    I already have this, but I can not return to the average:

    
            LineitemManager lineitem = app.getOrThrow(LineitemManager.class);
            Map<Tuple2<String, String>, Long> grouped = lineitem.stream().filter(Lineitem.L_SHIPDATE.lessOrEqual(sqlDate))
                    .collect(groupingBy(t->Tuples.of(t.getLReturnflag(), t.getLLinestatus()), ));
    
    
            grouped.forEach((k, v) -> {
                System.out.format("%-32s, %,d%n", k, v);
            });
    solangepaz
    @solangepaz
    I've tried this, but it can not. Because the values I want to get as average are not recognized
    Per-Åke Minborg
    @minborg
    What type is returned by getLQuantity() ?
    solangepaz
    @solangepaz
    BigDecimal
    Per-Åke Minborg
    @minborg
    Ahhh. Ok.
    Have you tried something like: Collectors.averagingDouble(t -> t.getLQuantity().doubleValue())
    solangepaz
    @solangepaz
    It worked, thank you :)
    Per-Åke Minborg
    @minborg
    Great!
    solangepaz
    @solangepaz
    Another question, is this part possible to implement?
    sum (case
             when o_orderpriority = '1-URGENT'
                 OR o_orderpriority = '2-HIGH'
                 then 1
             else 0
         end) the high_line_count,
         sum (case
             when o_orderpriority <> '1-URGENT'
                 AND o_orderpriority <> '2-HIGH'
                 then 1
             else 0
         end) AS low_line_count
    Per-Åke Minborg
    @minborg
    They appear to be each others' inverse logic.
    In this case I would use a .collect(partitionBy(o -> o.getOrderPriority.equals(“1-URGENT”) ||o.getOrderPriority.equals(“2-HIGH”), counting()) statement
    solangepaz
    @solangepaz

    This does not return a correct result. The query would be this:

    SELECT
        l_shipmode,
        sum (case
            when o_orderpriority = '1-URGENT'
                OR o_orderpriority = '2-HIGH'
                then 1
            else 0
        end) the high_line_count,
        sum (case
            when o_orderpriority <> '1-URGENT'
                AND o_orderpriority <> '2-HIGH'
                then 1
            else 0
        end) AS low_line_count
    FROM
        orders
        lineitem
    ONDE
        o_orderkey = l_orderkey
        AND l_shipmode in ('MAIL', 'SHIP')
        AND l_receiptdate> = date '1994-01-01'
        AND l_receiptdate <date '1994-01-01' + interval '1' year
    GROUP BY
        l_shipmode

    And I have this:

    Join <Tuple2 <Orders, Lineitem >> joinFirst = joinComponent
                    .from (OrdersManager.IDENTIFIER)
                    .innerJoinOn (Lineitem.L_ORDERKEY) .equal (Orders.O_ORDERKEY)
                    .where (Lineitem.L_SHIPMODE.in ("MAIL", "SHIP"))
                    .where (Lineitem.L_RECEIPTDATE.greaterOrEqual (sqlDate) .and (Lineitem.L_RECEIPTDATE.lessThan (sqlDate2)))
                    .build (Tuples :: of);
    
    
            Map <Boolean, Long> grouped = joinFirst.stream ()
                    .collect (Collectors.partitioningBy (t> t.get0)) getOOrderpriority (). equals ("1-URGENT") || t.get0.) getOOrderpriority (). equals ("2-HIGH"), counting ()));
    
    
            grouped.forEach ((k, v) -> {
                System.out.format ("% - 32s,%, d% n", k, v);
            });
    Per-Åke Minborg
    @minborg
    Hard to spot the problem. The collect line seams to mis some characters?
    You only get the high_line_count. How does it differ?
    solangepaz
    @solangepaz

    I do not know, but for example, this returns the same result:

      Map<Boolean, Long> grouped= joinFirst.stream()
                    .collect(partitioningBy(t->t.get0().getOOrderpriority().equals("1-URGENT") || t.get0().getOOrderpriority().equals("2-HIGH"), counting()));
    Map <Boolean, Long> grouped = joinFirst.stream ()
                     .collect (partitioningBy (t> t.get0)) getOOrderpriority (). equals ("1-URGENT"), counting ()));

    and I guess that should make it different

    solangepaz
    @solangepaz
    It is counting all the rows and not through t->t.get0().getOOrderpriority().equals("2-HIGH")
    Per-Åke Minborg
    @minborg
    You should get a Map with 2 entries (True and False) that counts of all that matches the predicate and the one that does not.
    solangepaz
    @solangepaz

    Now i have this:

            Map<String ,Map<Boolean,Long>> grouped= joinFirst.stream()
                    .collect(Collectors.groupingBy(t->t.get1().getLShipmode(),
                            partitioningBy(t-> t.get0().getOOrderpriority().equals("1-URGENT") || t.get0().getOOrderpriority().equals("2-HIGH") , counting())));

    And the result is this:

    MAIL       false 13209
    MAIL       true 0
    SHIP       false 13224
    SHIP       true 0

    But it should be:

    MAIL       false 0
    MAIL       true 5376
    SHIP       false 0
    SHIP       true 5346
    Per-Åke Minborg
    @minborg
    ok. Make sure that your predicate in the partitioningBy clause really works by manually printing out the result for some items.
    solangepaz
    @solangepaz
    I think the error is as I'm doing group by, because the values are correct, that is, the total of true is correct, but it is not divided by "MAIL" and "SHIP"
    Maarten Winkels
    @mwinkels_gitlab
    Hi, I'm trying to run the "speedment:tool" goal for a SQLite database, but I get this exception:
    Caused by: java.util.NoSuchElementException: No value present
    at java.util.Optional.get(Optional.java:135)
    at com.speedment.runtime.connector.sqlite.internal.SqliteMetadataHandler.lambda$null$23(SqliteMetadataHandler.java:358)
    Can this be caused by a 'TEXT' type foreign key?
    Thanks!
    Emil Forslund
    @Pyknic
    @mwinkels_gitlab Hm, I think the exception indicates that the database has a primary key (not a foreign key) but the connector can't find the associated column. It could indicate a bug. Do you know which table it is, and if so, what primary keys does it have?
    solangepaz
    @solangepaz
    Hi, I have a question. Is speedment supposed to return different results for the same query?
    For example, I ran a query three times and those three results are different.
    Per-Åke Minborg
    @minborg
    @solangepaz Hi @solangepaz . Strictly speaking, the contract for a Manager’s stream method states that the order is unspecified:
         *The order in which elements are returned when the stream is eventually
         * consumed <em>is unspecified</em>. The order may even change from one
         * invocation to another. Thus, it is an error to assume any particular
         * element order even though is might appear, for some stream sources, that
         * there is a de-facto order.
         * <p>
         * If a deterministic order is required, then make sure to invoke the
         * {@link Stream#sorted(java.util.Comparator)} method on the {@link Stream}
         * returned.
         * <p>
    The reason is that different databases have different orders and order guarantees. Add a sorted() operation if you need a deterministic order (this will cost performance though).
    solangepaz
    @solangepaz
    This message was deleted

    But the results are very different. For example, this query in Postgresql:

    select
        l_returnflag,
        l_linestatus,
        sum(l_extendedprice) as sum_charge,
        avg(l_tax)
    from
        lineitem
    where
        l_shipdate <= date '1998-12-01'
    group by
        l_returnflag,
        l_linestatus
    order by
        l_returnflag,
        l_linestatus;

    It has the following output:
    image.png

    With the speedment the output is this:

    A F 1,20E+10 0,039955076
    N F 3,23E+08 0,040103375
    N O 1,91E+10 0,040013735
    R F 1,20E+10 0,039975758

    Per-Åke Minborg
    @minborg
    Average values looks good but not the sum(). Do you have the Java code?
    solangepaz
    @solangepaz
    Yes, the code is this:
        Calendar cal = Calendar.getInstance();
        cal.set(Calendar.DAY_OF_MONTH,1);
        cal.set(Calendar.MONTH,Calendar.DECEMBER);
        cal.set(Calendar.YEAR,1998);
    
         java.sql.Date sqlDate = new java.sql.Date(cal.getTimeInMillis());
         LineitemManager lineitem = app.getOrThrow(LineitemManager.class);
    
         Map<Tuple2<String, String>, AbstractMap.SimpleEntry<Double, Double>> grouped = lineitem.stream().filter(Lineitem.L_SHIPDATE.lessOrEqual(sqlDate))
                    .collect(groupingBy(t->Tuples.of(t.getLReturnflag(), t.getLLinestatus()),
                            Collectors.collectingAndThen(Collectors.toList(),
                                    list-> {double first =
                                            list
                                                    .stream()
                                                    .mapToDouble(t -> t.getLExtendedprice().get().doubleValue()).sum();
                                        double second =
                                                list
                                                        .stream()
                                                        .collect(averagingDouble(t->t.getLTax().get().doubleValue()));
    
                                        return new AbstractMap.SimpleEntry<>(first, second);})
                    ));
    
    
            grouped.forEach((key, value) -> System.out.println(key + ", " + value));
    Per-Åke Minborg
    @minborg
    Can’t spot the problem. Have you checked that the Date looks ok and filters out the right elements?
    solangepaz
    @solangepaz
    Yeah, it looks okay. Even because I have already tested on two machines with the same database. The first machine returns the result just like PostgreSQL. The second machine returns these wrong results.
    The only difference is here:.mapToDouble (t -> t.getLExtendedprice (). Get (). DoubleValue ()). Sum ();
    The first machine (where it works ok) only accepts .mapToDouble (t -> t.getLExtendedprice (). DoubleValue ()). Sum () ;. And the second machine needs get () before doubleValue ()
    Per-Åke Minborg
    @minborg
    So have you set the column l_extendedprice to nullable on one but not on the other?
    In the speedment Tool I mean.
    solangepaz
    @solangepaz
    Hi, does speedment not support an H2 database? I am using SQLite, but it is very slow.
    solangepaz
    @solangepaz
    And is it possible to use this through the mutator?
    Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:"); connection.createStatement().executeUpdate("restore from database.db");
    Per-Åke Minborg
    @minborg
    Currently, there is no support for H2. However, since we now have support for SQLite, the effort of writing an H2 driver would be much less. Anyone up to the challenge?