Java Help - bill of materials

Author

bill of materials

steve

2006-06-05, 7:11 pm

Hi,

I find myself needing to write a system to display edit & store a BOM.
Are there any resources or example code available on the net for performing
this function.

yes i have googled , but most hits are high level papers on the interrelation
ship of data-models with the real world.

Steve

Rhino

2006-06-17, 8:30 am

"steve" <steve@aol.com> wrote in message
news:e62bmp0cmd@news3.newsguy.com...
> Hi,
>
> I find myself needing to write a system to display edit & store a BOM.
> Are there any resources or example code available on the net for
> performing
> this function.
>
> yes i have googled , but most hits are high level papers on the
> interrelation
> ship of data-models with the real world.
>

BOMs are typically implemented in relational databases, at least in my
limited experience. DB2, for instance, can be used to store BOMs and SQL can
be written to extract the relevant rows from the tables. I once spoke to a
user of a different database - I think it was Oracle but I'm not really
sure - who also stored and queried BOMs on his database. Perhaps many of the
other major databases also support them?

Here is an excerpt from the Information Center (online help) for DB2 for
Windows, Unix, and Linux, Version 8.2, which addresses your question from
the database side. It illustrates the SQL you would use to get various parts
of a BOM from a table that contained the entire BOM. As long as you use a
JDBC Driver that supports SQL of the kind illustrated, you should be able to
do these queries from Java programs easily enough. I haven't tried any BOM
queries from Java but I've done plenty from DB2 and they work fine.

-------------------------------------------------------------------------------------------------------------------
Recursion example: bill of materials
Bill of materials (BOM) applications are a common requirement in many
business environments. To illustrate the capability of a recursive common
table expression for BOM applications, consider a table of parts with
associated subparts and the quantity of subparts required by the part. For
this example, create the table as follows:

CREATE TABLE PARTLIST
(PART VARCHAR(8),
SUBPART VARCHAR(8),
QUANTITY INTEGER); To give query results for this example,
assume that the PARTLIST table is populated with the following values:

PART SUBPART QUANTITY
-------- -------- -----------
00 01 5
00 05 3
01 02 2
01 03 3
01 04 4
01 06 3
02 05 7
02 06 6
03 07 6
04 08 10
04 09 11
05 10 10
05 11 10
06 12 10
06 13 10
07 14 8
07 12 8 Example 1: Single level explosion

The first example is called single level explosion. It answers the question,
"What parts are needed to build the part identified by '01'?". The list will
include the direct subparts, subparts of the subparts and so on. However, if
a part is used multiple times, its subparts are only listed once.

WITH RPL (PART, SUBPART, QUANTITY) AS
( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
)
SELECT DISTINCT PART, SUBPART, QUANTITY
FROM RPL
ORDER BY PART, SUBPART, QUANTITY; The above query includes a common table
expression, identified by the name RPL, that expresses the recursive part of
this query. It illustrates the basic elements of a recursive common table
expression.

The first operand (fullselect) of the UNION, referred to as the
initialization fullselect, gets the direct children of part '01'. The FROM
clause of this fullselect refers to the source table and will never refer to
itself (RPL in this case). The result of this first fullselect goes into the
common table expression RPL (Recursive PARTLIST). As in this example, the
UNION must always be a UNION ALL.

The second operand (fullselect) of the UNION uses RPL to compute subparts of
subparts by having the FROM clause refer to the common table expression RPL
and the source table with a join of a part from the source table (child) to
a subpart of the current result contained in RPL (parent). The result goes
back to RPL again. The second operand of UNION is then used repeatedly until
no more children exist.

The SELECT DISTINCT in the main fullselect of this query ensures the same
part/subpart is not listed more than once.

The result of the query is as follows:

PART SUBPART QUANTITY
-------- -------- -----------
01 02 2
01 03 3
01 04 4
01 06 3
02 05 7
02 06 6
03 07 6
04 08 10
04 09 11
05 10 10
05 11 10
06 12 10
06 13 10
07 12 8
07 14 8 Observe in the result that from part '01' we
go to '02' which goes to '06' and so on. Further, notice that part '06' is
reached twice, once through '01' directly and another time through '02'. In
the output, however, its subcomponents are listed only once (this is the
result of using a SELECT DISTINCT) as required.

It is important to remember that with recursive common table expressions it
is possible to introduce an infinite loop. In this example, an infinite loop
would be created if the search condition of the second operand that joins
the parent and child tables was coded as:

PARENT.SUBPART = CHILD.SUBPART This example of causing an infinite loop
is obviously a case of not coding what is intended. However, care should
also be exercised in determining what to code so that there is a definite
end of the recursion cycle.

The result produced by this example query could be produced in an
application program without using a recursive common table expression.
However, this approach would require starting of a new query for every level
of recursion. Furthermore, the application needs to put all the results back
in the database to order the result. This approach complicates the
application logic and does not perform well. The application logic becomes
even harder and more inefficient for other bill of material queries, such as
summarized and indented explosion queries.

Example 2: Summarized explosion

The second example is a summarized explosion. The question posed here is,
what is the total quantity of each part required to build part '01'. The
main difference from the single level explosion is the need to aggregate the
quantities. The first example indicates the quantity of subparts required
for the part whenever it is required. It does not indicate how many of the
subparts are needed to build part '01'.

WITH RPL (PART, SUBPART, QUANTITY) AS
(
SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
)
SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
FROM RPL
GROUP BY PART, SUBPART
ORDER BY PART, SUBPART; In the above query, the select list of the second
operand of the UNION in the recursive common table expression, identified by
the name RPL, shows the aggregation of the quantity. To find out how much of
a subpart is used, the quantity of the parent is multiplied by the quantity
per parent of a child. If a part is used multiple times in different places,
it requires another final aggregation. This is done by the grouping over the
common table expression RPL and using the SUM column function in the select
list of the main fullselect.

The result of the query is as follows:

PART SUBPART Total Qty Used
-------- -------- --------------
01 02 2
01 03 3
01 04 4
01 05 14
01 06 15
01 07 18
01 08 40
01 09 44
01 10 140
01 11 140
01 12 294
01 13 150
01 14 144 Looking at the output, consider the line
for subpart '06'. The total quantity used value of 15 is derived from a
quantity of 3 directly for part '01' and a quantity of 6 for part '02' which
is needed 2 times by part '01'.

Example 3: Controlling depth

The question may come to mind, what happens when there are more levels of
parts in the table than you are interested in for your query? That is, how
is a query written to answer the question, "What are the first two levels of
parts needed to build the part identified by '01'?" For the sake of clarity
in the example, the level is included in the result.

WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
(
SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
FROM PARTLIST ROOT
WHERE ROOT.PART = '01'
UNION ALL
SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
FROM RPL PARENT, PARTLIST CHILD
WHERE PARENT.SUBPART = CHILD.PART
AND PARENT.LEVEL < 2
)
SELECT PART, LEVEL, SUBPART, QUANTITY
FROM RPL; This query is similar to example 1. The column LEVEL was
introduced to count the levels from the original part. In the initialization
fullselect, the value for the LEVEL column is initialized to 1. In the
subsequent fullselect, the level from the parent is incremented by 1. Then
to control the number of levels in the result, the second fullselect
includes the condition that the parent level must be less than 2. This
ensures that the second fullselect only processes children to the second
level.

The result of the query is:

PART LEVEL SUBPART QUANTITY
-------- ----------- -------- -----------
01 1 02 2
01 1 03 3
01 1 04 4
01 1 06 3
02 2 05 7
02 2 06 6
03 2 07 6
04 2 08 10
04 2 09 11
06 2 12 10
06 2 13
10 -------------------------------------------------------------------------------------------------------------------

You will probably find that the SQL to handle a BOM varies considerably from
database to database. For example, when I was talking to the Oracle (?) user
about his BOMs, he showed me one of the queries that he did and the SQL was
very different from the SQL that would give the same result in DB2. So it is
probably best to choose which database you want to use, then look up "bill
of materials" in its help system to see how the SQL is written for BOMs in
that database. But be careful: some databases may not handle BOMs at all so
don't just choose a database on the theory that all databases support BOMs!
It would probably be best if you asked first on a newsgroup like
comp.lang.java.databases for names of databases that handle BOMs, then
examine those suggestions until you find something you like. Then learn how
to write BOM-handling SQL for that database.

If you don't want to use a database to solve your problem, I don't know what
to suggest. There may be a way to do it with normal objects and data
structures but I don't know how to do it myself.

--
Rhino

Oliver Wong

2006-06-17, 8:30 am

"Rhino" <no.offline.contact.please@nospam.com> wrote in message
news:uSghg.848$Wy.67495@news20.bellglobal.com...
>
> "steve" <steve@aol.com> wrote in message
> news:e62bmp0cmd@news3.newsguy.com...
>
> BOMs are typically implemented in relational databases,
[snipped long, but very informative, explanation of RDBs]

That covers the editing and storing part. For the displaying part,
you'll probably want to use a reporting engine. A popular one is Crystal
Reports, but it's a Microsoft (and thus implicitly non-Java) solution. If
you want to stick with Java, I've heard BIRT is a good tool
(http://www.eclipse.org/birt/phoenix/), but I've never used it myself.

- Oliver

steve

2006-06-17, 8:30 am

On Tue, 6 Jun 2006 23:07:11 +0800, Rhino wrote
(in article <uSghg.848$Wy.67495@news20.bellglobal.com> ):

>
> "steve" <steve@aol.com> wrote in message
> news:e62bmp0cmd@news3.newsguy.com...
>
> BOMs are typically implemented in relational databases, at least in my
> limited experience. DB2, for instance, can be used to store BOMs and SQL can
> be written to extract the relevant rows from the tables. I once spoke to a
> user of a different database - I think it was Oracle but I'm not really
> sure - who also stored and queried BOMs on his database. Perhaps many of the
> other major databases also support them?
>
> Here is an excerpt from the Information Center (online help) for DB2 for
> Windows, Unix, and Linux, Version 8.2, which addresses your question from
> the database side. It illustrates the SQL you would use to get various parts
> of a BOM from a table that contained the entire BOM. As long as you use a
> JDBC Driver that supports SQL of the kind illustrated, you should be able to
> do these queries from Java programs easily enough. I haven't tried any BOM
> queries from Java but I've done plenty from DB2 and they work fine.
>
>
------------------------------------------------------------------------------

> -------------------------------------
> Recursion example: bill of materials
> Bill of materials (BOM) applications are a common requirement in many
> business environments. To illustrate the capability of a recursive common
> table expression for BOM applications, consider a table of parts with
> associated subparts and the quantity of subparts required by the part. For
> this example, create the table as follows:
>
> CREATE TABLE PARTLIST
> (PART VARCHAR(8),
> SUBPART VARCHAR(8),
> QUANTITY INTEGER); To give query results for this example,
> assume that the PARTLIST table is populated with the following values:
>
> PART SUBPART QUANTITY
> -------- -------- -----------
> 00 01 5
> 00 05 3
> 01 02 2
> 01 03 3
> 01 04 4
> 01 06 3
> 02 05 7
> 02 06 6
> 03 07 6
> 04 08 10
> 04 09 11
> 05 10 10
> 05 11 10
> 06 12 10
> 06 13 10
> 07 14 8
> 07 12 8 Example 1: Single level explosion
>
> The first example is called single level explosion. It answers the question,
> "What parts are needed to build the part identified by '01'?". The list will
> include the direct subparts, subparts of the subparts and so on. However, if
> a part is used multiple times, its subparts are only listed once.
>
> WITH RPL (PART, SUBPART, QUANTITY) AS
> ( SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
> FROM PARTLIST ROOT
> WHERE ROOT.PART = '01'
> UNION ALL
> SELECT CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
> FROM RPL PARENT, PARTLIST CHILD
> WHERE PARENT.SUBPART = CHILD.PART
> )
> SELECT DISTINCT PART, SUBPART, QUANTITY
> FROM RPL
> ORDER BY PART, SUBPART, QUANTITY; The above query includes a common table
> expression, identified by the name RPL, that expresses the recursive part of
> this query. It illustrates the basic elements of a recursive common table
> expression.
>
> The first operand (fullselect) of the UNION, referred to as the
> initialization fullselect, gets the direct children of part '01'. The FROM
> clause of this fullselect refers to the source table and will never refer to
> itself (RPL in this case). The result of this first fullselect goes into the
> common table expression RPL (Recursive PARTLIST). As in this example, the
> UNION must always be a UNION ALL.
>
> The second operand (fullselect) of the UNION uses RPL to compute subparts of
> subparts by having the FROM clause refer to the common table expression RPL
> and the source table with a join of a part from the source table (child) to
> a subpart of the current result contained in RPL (parent). The result goes
> back to RPL again. The second operand of UNION is then used repeatedly until
> no more children exist.
>
> The SELECT DISTINCT in the main fullselect of this query ensures the same
> part/subpart is not listed more than once.
>
> The result of the query is as follows:
>
> PART SUBPART QUANTITY
> -------- -------- -----------
> 01 02 2
> 01 03 3
> 01 04 4
> 01 06 3
> 02 05 7
> 02 06 6
> 03 07 6
> 04 08 10
> 04 09 11
> 05 10 10
> 05 11 10
> 06 12 10
> 06 13 10
> 07 12 8
> 07 14 8 Observe in the result that from part '01' we
> go to '02' which goes to '06' and so on. Further, notice that part '06' is
> reached twice, once through '01' directly and another time through '02'. In
> the output, however, its subcomponents are listed only once (this is the
> result of using a SELECT DISTINCT) as required.
>
> It is important to remember that with recursive common table expressions it
> is possible to introduce an infinite loop. In this example, an infinite loop
> would be created if the search condition of the second operand that joins
> the parent and child tables was coded as:
>
> PARENT.SUBPART = CHILD.SUBPART This example of causing an infinite loop
> is obviously a case of not coding what is intended. However, care should
> also be exercised in determining what to code so that there is a definite
> end of the recursion cycle.
>
> The result produced by this example query could be produced in an
> application program without using a recursive common table expression.
> However, this approach would require starting of a new query for every level
> of recursion. Furthermore, the application needs to put all the results back
> in the database to order the result. This approach complicates the
> application logic and does not perform well. The application logic becomes
> even harder and more inefficient for other bill of material queries, such as
> summarized and indented explosion queries.
>
> Example 2: Summarized explosion
>
> The second example is a summarized explosion. The question posed here is,
> what is the total quantity of each part required to build part '01'. The
> main difference from the single level explosion is the need to aggregate the
> quantities. The first example indicates the quantity of subparts required
> for the part whenever it is required. It does not indicate how many of the
> subparts are needed to build part '01'.
>
> WITH RPL (PART, SUBPART, QUANTITY) AS
> (
> SELECT ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
> FROM PARTLIST ROOT
> WHERE ROOT.PART = '01'
> UNION ALL
> SELECT PARENT.PART, CHILD.SUBPART, PARENT.QUANTITY*CHILD.QUANTITY
> FROM RPL PARENT, PARTLIST CHILD
> WHERE PARENT.SUBPART = CHILD.PART
> )
> SELECT PART, SUBPART, SUM(QUANTITY) AS "Total QTY Used"
> FROM RPL
> GROUP BY PART, SUBPART
> ORDER BY PART, SUBPART; In the above query, the select list of the second
> operand of the UNION in the recursive common table expression, identified by
> the name RPL, shows the aggregation of the quantity. To find out how much of
> a subpart is used, the quantity of the parent is multiplied by the quantity
> per parent of a child. If a part is used multiple times in different places,
> it requires another final aggregation. This is done by the grouping over the
> common table expression RPL and using the SUM column function in the select
> list of the main fullselect.
>
> The result of the query is as follows:
>
> PART SUBPART Total Qty Used
> -------- -------- --------------
> 01 02 2
> 01 03 3
> 01 04 4
> 01 05 14
> 01 06 15
> 01 07 18
> 01 08 40
> 01 09 44
> 01 10 140
> 01 11 140
> 01 12 294
> 01 13 150
> 01 14 144 Looking at the output, consider the line
> for subpart '06'. The total quantity used value of 15 is derived from a
> quantity of 3 directly for part '01' and a quantity of 6 for part '02' which
> is needed 2 times by part '01'.
>
> Example 3: Controlling depth
>
> The question may come to mind, what happens when there are more levels of
> parts in the table than you are interested in for your query? That is, how
> is a query written to answer the question, "What are the first two levels of
> parts needed to build the part identified by '01'?" For the sake of clarity
> in the example, the level is included in the result.
>
> WITH RPL (LEVEL, PART, SUBPART, QUANTITY) AS
> (
> SELECT 1, ROOT.PART, ROOT.SUBPART, ROOT.QUANTITY
> FROM PARTLIST ROOT
> WHERE ROOT.PART = '01'
> UNION ALL
> SELECT PARENT.LEVEL+1, CHILD.PART, CHILD.SUBPART, CHILD.QUANTITY
> FROM RPL PARENT, PARTLIST CHILD
> WHERE PARENT.SUBPART = CHILD.PART
> AND PARENT.LEVEL < 2
> )
> SELECT PART, LEVEL, SUBPART, QUANTITY
> FROM RPL; This query is similar to example 1. The column LEVEL was
> introduced to count the levels from the original part. In the initialization
> fullselect, the value for the LEVEL column is initialized to 1. In the
> subsequent fullselect, the level from the parent is incremented by 1. Then
> to control the number of levels in the result, the second fullselect
> includes the condition that the parent level must be less than 2. This
> ensures that the second fullselect only processes children to the second
> level.
>
> The result of the query is:
>
> PART LEVEL SUBPART QUANTITY
> -------- ----------- -------- -----------
> 01 1 02 2
> 01 1 03 3
> 01 1 04 4
> 01 1 06 3
> 02 2 05 7
> 02 2 06 6
> 03 2 07 6
> 04 2 08 10
> 04 2 09 11
> 06 2 12 10
> 06 2 13
> 10
>
------------------------------------------------------------------------------

> -------------------------------------
>
> You will probably find that the SQL to handle a BOM varies considerably from
> database to database. For example, when I was talking to the Oracle (?) user
> about his BOMs, he showed me one of the queries that he did and the SQL was
> very different from the SQL that would give the same result in DB2. So it is
> probably best to choose which database you want to use, then look up "bill
> of materials" in its help system to see how the SQL is written for BOMs in
> that database. But be careful: some databases may not handle BOMs at all so
> don't just choose a database on the theory that all databases support BOMs!
> It would probably be best if you asked first on a newsgroup like
> comp.lang.java.databases for names of databases that handle BOMs, then
> examine those suggestions until you find something you like. Then learn how
> to write BOM-handling SQL for that database.
>
> If you don't want to use a database to solve your problem, I don't know what
> to suggest. There may be a way to do it with normal objects and data
> structures but I don't know how to do it myself.
>
> --
> Rhino
>
>

Thanks Rhino,

it's not the sql that is a problem, what i wanted was a something to save
me the time of laying out a GUi & coding up buttons for add/ remove /save
functions.

Steve

steve

2006-06-17, 8:30 am

On Tue, 6 Jun 2006 23:58:46 +0800, Oliver Wong wrote
(in article <WChhg.13703$771.3615@edtnps89> ):

>
> "Rhino" <no.offline.contact.please@nospam.com> wrote in message
> news:uSghg.848$Wy.67495@news20.bellglobal.com...
> [snipped long, but very informative, explanation of RDBs]
>
> That covers the editing and storing part. For the displaying part,
> you'll probably want to use a reporting engine. A popular one is Crystal
> Reports, but it's a Microsoft (and thus implicitly non-Java) solution. If
> you want to stick with Java, I've heard BIRT is a good tool
> (http://www.eclipse.org/birt/phoenix/), but I've never used it myself.
>
> - Oliver
>

thanks
BIRT is ok, but it is a bit complex to implement, I already use jasperreports
& itext.

it's a java class /.jar file i was looking for , to implement the user GUI

Steve

Sponsored Links