How can i count this variables in my DB?

Question

Hello

Colud you please help me about this problem ?

I have 4 variables

1. XTSM is between 0~200
2. XTS is between 0~2
3. XRX is 0~3
4. XHAN is 0~7
5. ZHTYPE is one of these : (1)TCHF_HLF (2)TCHSD (3)TCHFULL

they are related to their with this shape .

XTSM->XTS->XRX->XHAN->ZHTYPE (Just Logical)

this means :

• Each XTSM has 3 XTS (0-2)
• Each XTS has 4 XRX (0-3)
• Each XRX has 8 XCHAN (0-7)
• and Each line has a type of ZHTYPE

I have a DB file with 14000 lines; in this file these variables specified, For example XTSM is between 0~200 but usually it's less than 200 and maybe for example specified 0~60 but it's never greater than 200; and it's true for XTS,XRX and XCHAN of course ... so we must make all variables as a dynamic ;

this is a part of file (for example UPDATED) : http://www.4shared.com/file/210566155/c080d93a/db_online.html

I want get this output ? (I want to show XTSM and XTS to output and in the backgroud must calculate all of them) please help me

----Type------------------Total---------------------Total of ZHTYPE-----------------
XTSM:0/XTS:0               in this case is : 29     TCHF_HLF:28 TCHSD:1 TCHFULL:0
XTSM:0/XTS:1               No. of found   
XTSM:0/XTS:2               No. of found   
XTSM:1/XTS:0               No. of found   
XTSM:1/XTS:1               No. of found   
XTSM:1/XTS:2               No. of found 

I don't know but i think it's maybe this ; No ? It's just pseudo code :

for(i ; i< (Total of XTSM) ; i++){
        for(j ; j< (Total of XTS) ; j++){
            for(k ; k< (Total of XRX) ; k++){
                for(l ; l< (Total of XCHAN) ; l++)
                    Print( Total of XTSM:x/XTS:y);
            }
        }

}

Thanks for your help ...

Answer 1

It doesn't make a lot of sense to do this in Java, since what you're showing is just record processing and minimal text manipulation. As Kaleb Brasee indicated in er comment, COBOL might be appropriate. But I don't know COBOL.

Here's a quick Perl solution:

my $records = {};

for (<>) {
  next unless m{XTSM:(\d+)/XTS:(\d+)/XRX:(\d+)/XHAN:(\d+)};
  $$records{"XTSM:$1/XTS:$2"} ||= [];
  push @{$$records{"XTSM:$1/XTS:$2"}}, "$3/$4";
}

print "Type\t\t\tTotal\n";

for (sort keys %$records) {
  print "$_\t\t" . scalar @{$$records{$_}} . "\n";
} 

Example output with the supplied file:

Type                    Total
XTSM:0/XTS:0            29
XTSM:0/XTS:1            29
XTSM:0/XTS:2            29
XTSM:1/XTS:0            29
XTSM:1/XTS:1            29
XTSM:1/XTS:2            29
XTSM:10/XTS:0           14
XTSM:10/XTS:1           14

Answer 2

Ok, I think I'm still trying to understand the problem. But I think that, while you access the database you could save the totals for XTSM, XTS, XRX and XHAN.

Or simply calculate the COUNT where XTSM is "?" (in a PreparedStatment). I understand that maybe this is a LOT of charge to a database that is already quite big.

Anyway, you could also do this in java since in the last loop of the for, i == COUNT. By the way, your pseudocode needs to define XTS in XTSM, becouse if not it will travel through all XTS in database not all XTS related to your specific XTSM.

More ideas, in your four fors (I always wanted to say that :) ), you could add some kind of ifs to the count, so you whould know if you are looping thorugh a XTS that has a logic relation with XTSM and again at the end of the loop i = COUNT.

A little bit more of info would be greatfull as I think I am not undestanding the problem at length. How do you retrive the data from the database? Have you got any class that stores this? Maybe you could do some methods in it to calculate this.

I think that a simple class

public class Foo{ int XTSM; int XTS; int XRX; int XHAN; }

and then storing all rows in a List (or a vector, or a map, something iterable), counting elements in the list that have XTSM = x and XTS = y Would this make more or less what you are trying to do?

Edit: maybe this code helps, don't know if it's what you are looking for... allthought I think maybe some PL (if you are in ORACLE) whould do it faster. Anyway, some jave code...

public class Foo {
int XTSM; 
int XTS; 
int XRX; 
int XHAN;
LinkedList lk;//maybe not a good idea to have it here
//but hey, it's a 30seconds thought...

public void getItAll() throws Exception{//mostly SQLException, but any you need
    Connection conn=null;
    //make the connection

    String query="SELECT XTSM, XTS, XRX, XHAN " +
            " FROM your_table" +
            " WHERE 1=1" +
            " AND 1=1";//add stuff you need to the query

    Statement stmt= conn.createStatement();
    ResultSet rst = stmt.executeQuery(query);
    Foo f = null;
    while(rst.next()){
        f = new Foo();
        //yeah, you should have getters and setters
        f.XTSM = rst.getInt("XSTM");
        f.XTS = rst.getInt("XST");
        f.XRX = rst.getInt("XRX");
        f.XHAN = rst.getInt("XHAN");
        this.lk.add(f);
        //this gives you all the table in a list (especifically a linkedlist
    }
}
public void calculateItAll() throws Exception{//some exception, probably IO?
    Foo f = null;
    int count =0;
    for(int i=0; i< this.lk.size(); i++){//we iterate through all the table
        f = new Foo();
        f = (Foo)this.lk.get(i);
        count = 0;
        for(int j=0; j<=200;j++){//XSTM
            if(f.XTSM == j){//I keep thinking that getters are good
                for(int k=0; k<=2;k++){//XST
                    if(f.XTS == k){//do the getters, really
                        for(int m=0;m<=3;m++){//XRX
                            if(f.XRX==m){//add funny comment about getters
                                for(int n=0;n<=7;n++){//XHAN
                                    if(f.XHAN==n){//man, so good that is almost finished
                                        count++;
                                    }//end of if
                                }//end of XHAN
                            }//end of if
                        }//end of XRX
                    }//end of if
                }//end ofXST
            }//end of if
        }//end of XSTM
        //here you write the count and all the Foo somewhere
    }
}}

Answer 3

Please see my solution below. The test file test1.txt you gave seems to have some special chars at beginning of file.

.

Solution

public class Test {

    public static void main(String[] args) throws IOException {
        Test test = new Test();
        test.execute();
    }

    private static String TYPE_XTSM = "XTSM";
    private static String TYPE_XTS = "XTS";
    private static String TYPE_XRX = "XRX";
    private static String TYPE_XHAN = "XHAN";

    private void execute() throws IOException {
        InputStream in = null;
        BufferedReader br = null;
        TreeMap<Integer, TreeMap<Integer, Integer>> xtsmMap = new TreeMap<Integer, TreeMap<Integer, Integer>>();
        try {
            in = Test.class.getResourceAsStream("test1.txt");
            br = new BufferedReader(new InputStreamReader(in));
            String line;
            while ((line = br.readLine()) != null) {
                Record rec = new Record(line);
                processRecord(xtsmMap, rec);
            }
        } finally {
            if (br != null) {
                br.close();
            }
        }
        printResults(xtsmMap);
    }

    private void processRecord(
            TreeMap<Integer, TreeMap<Integer, Integer>> xtsmMap, Record rec) {
        TreeMap<Integer, Integer> xtsMap;
        if (xtsmMap.containsKey(rec.getXtsm())) {
            xtsMap = xtsmMap.get(rec.getXtsm());
        } else {
            xtsMap = new TreeMap<Integer, Integer>();
            xtsmMap.put(Integer.valueOf(rec.getXtsm()), xtsMap);
        }
        if (xtsMap.containsKey(rec.getXts())) {
            Integer count = xtsMap.get(rec.getXts());
            xtsMap.put(Integer.valueOf(rec.getXts()), Integer.valueOf(count
                    .intValue() + 1));
        } else {
            xtsMap.put(Integer.valueOf(rec.getXts()), Integer.valueOf(1));
        }
    }

    private void printResults(
            TreeMap<Integer, TreeMap<Integer, Integer>> xtsmMap) {
        System.out.println("Type\t\tTotal");
        Set<Integer> xtsmSet = xtsmMap.navigableKeySet();
        for (Integer xtsm : xtsmSet) {
            TreeMap<Integer, Integer> xtsMap = xtsmMap.get(xtsm);
            Set<Integer> xtsSet = xtsMap.navigableKeySet();
            for (Integer xts : xtsSet) {
                Integer count = xtsMap.get(xts);
                String outputLine = TYPE_XTSM + ":" + xtsm + "/" + TYPE_XTS
                        + ":" + xts + "\t" + count;
                System.out.println(outputLine);
            }
        }
    }

    private static class Record {

        private Integer xtsm, xts, xrk, xhan;

        Record(String line) {
            StringTokenizer st = new StringTokenizer(line, "/");
            while (st.hasMoreTokens()) {
                String token = st.nextToken();
                String type = token.substring(0, token.indexOf(":"));
                String valueStr = token.substring(token.indexOf(":") + 1, token
                        .length());
                Integer value = Integer.valueOf(valueStr);
                if (TYPE_XTSM.equals(type)) {
                    xtsm = value;
                } else if (TYPE_XTS.equals(type)) {
                    xts = value;
                } else if (TYPE_XRX.equals(type)) {
                    xrk = value;
                } else if (TYPE_XHAN.equals(type)) {
                    xhan = value;
                }
            }
        }

        public Integer getXtsm() {
            return xtsm;
        }

        public Integer getXts() {
            return xts;
        }

        public Integer getXrk() {
            return xrk;
        }

        public Integer getXhan() {
            return xhan;
        }
    }
}

.

Output

Type        Total
XTSM:0/XTS:0    29
XTSM:0/XTS:1    29
XTSM:0/XTS:2    29
XTSM:1/XTS:0    29
XTSM:1/XTS:1    29
XTSM:1/XTS:2    29
XTSM:2/XTS:0    29
XTSM:2/XTS:1    29
XTSM:2/XTS:2    29
XTSM:3/XTS:0    14
XTSM:3/XTS:1    14
XTSM:3/XTS:2    14
XTSM:4/XTS:0    13
XTSM:4/XTS:1    13
XTSM:4/XTS:2    13
XTSM:5/XTS:0    14
XTSM:5/XTS:1    14
XTSM:5/XTS:2    14
XTSM:6/XTS:0    21
XTSM:6/XTS:1    21
XTSM:6/XTS:2    21
XTSM:7/XTS:0    29
XTSM:7/XTS:1    29
XTSM:7/XTS:2    29
XTSM:8/XTS:0    14
XTSM:8/XTS:1    21
XTSM:9/XTS:0    21
XTSM:9/XTS:1    21
XTSM:9/XTS:2    21
XTSM:10/XTS:0   14
XTSM:10/XTS:1   14
XTSM:10/XTS:2   14
XTSM:11/XTS:0   14
XTSM:11/XTS:1   14
XTSM:11/XTS:2   14
XTSM:12/XTS:0   14
XTSM:12/XTS:1   14
XTSM:12/XTS:2   14
XTSM:13/XTS:0   29
XTSM:13/XTS:1   29
XTSM:13/XTS:2   29
XTSM:14/XTS:0   29
XTSM:14/XTS:1   29
XTSM:15/XTS:0   29
XTSM:15/XTS:1   29
XTSM:15/XTS:2   29
XTSM:16/XTS:0   29
XTSM:16/XTS:1   29
XTSM:16/XTS:2   29
XTSM:17/XTS:0   29
XTSM:17/XTS:1   29
XTSM:17/XTS:2   29
XTSM:18/XTS:0   29
XTSM:18/XTS:1   29
XTSM:18/XTS:2   29
XTSM:19/XTS:0   29
XTSM:19/XTS:1   29
XTSM:19/XTS:2   29
XTSM:21/XTS:0   29
XTSM:21/XTS:1   29
XTSM:21/XTS:2   29
XTSM:22/XTS:0   29
XTSM:22/XTS:1   29
XTSM:22/XTS:2   29
XTSM:23/XTS:0   29
XTSM:23/XTS:1   29
XTSM:23/XTS:2   29
XTSM:24/XTS:0   29
XTSM:24/XTS:1   29
XTSM:24/XTS:2   29
XTSM:25/XTS:0   29
XTSM:25/XTS:1   29
XTSM:25/XTS:2   29
XTSM:26/XTS:0   14
XTSM:26/XTS:1   14
XTSM:26/XTS:2   14
XTSM:28/XTS:0   15
XTSM:28/XTS:1   15
XTSM:28/XTS:2   15
XTSM:29/XTS:0   13
XTSM:29/XTS:1   13
XTSM:29/XTS:2   13
XTSM:30/XTS:0   14
XTSM:30/XTS:1   14
XTSM:31/XTS:0   14
XTSM:31/XTS:1   13
XTSM:31/XTS:2   13
XTSM:32/XTS:0   13
XTSM:32/XTS:1   14
XTSM:32/XTS:2   13
XTSM:33/XTS:0   14
XTSM:33/XTS:1   14
XTSM:33/XTS:2   14
XTSM:34/XTS:0   14
XTSM:34/XTS:1   14
XTSM:34/XTS:2   14
XTSM:35/XTS:0   29
XTSM:35/XTS:1   29
XTSM:35/XTS:2   29
XTSM:36/XTS:0   29
XTSM:36/XTS:1   21
XTSM:36/XTS:2   21
XTSM:37/XTS:0   14
XTSM:37/XTS:1   14
XTSM:37/XTS:2   14
XTSM:38/XTS:0   14
XTSM:38/XTS:1   14
XTSM:38/XTS:2   14
XTSM:39/XTS:0   21
XTSM:39/XTS:1   21
XTSM:39/XTS:2   21
XTSM:40/XTS:0   29
XTSM:40/XTS:1   29
XTSM:40/XTS:2   7
XTSM:41/XTS:0   29
XTSM:41/XTS:1   29
XTSM:41/XTS:2   29