Results 1 to 7 of 7
  1. #1
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default Store count of thf rows to arraylist

    Hello,

    I tried to count the rows of each attribute which called "wbuch", any way i use the following sql request:
    Java Code:
    SELECT wbuch, COUNt(*) FROM myTable  GROUP BY wbuch;
    And the result was the following table:
    +-------+----------+-----+
    | wbuch | COUNt(*) |
    +-------+-----------------+
    | 12 | 15 |
    | 13 | 334 |
    | 15 | 20 |
    | 16 | 488 |
    | 17 | 16 |
    | 20 | 252 |
    | 21 | 24 |
    | 23 | 599 |
    | 30 | 599 |
    | 31 | 508 |
    | 33 | 599 |
    | 34 | 491 |
    | 35 | 571 |
    | 36 | 643 |
    | 37 | 152 |
    | 46 | 419 |
    +-------+------------------+

    any way i want to store the row number's of each "wbuch" in arry list, but i didnt get it
    Java Code:
    public List<Integer> WbSeiten(){
            private List<Integer> wbList = new ArrayList<>();
            int rowCount = 0;
            try (Statement stmt = con.createStatement();) {
                String sql = "SELECT wbuch, COUNt(*) FROM myTable "
                        + "GROUP BY wbuch";
                ResultSet result = stmt.executeQuery(sql);
                while (result.next()) {
                    rowCount =result.getInt("COUNt(*)");
                    wbList.add(rowCount);
               }
            } catch (SQLException e) {
            }
            return wbList;
        }
    what is wrong with my snippet ?? And how to do it correctly
    Last edited by vector_ever; 05-26-2014 at 06:16 PM.

  2. #2
    kneitzel is offline Senior Member
    Join Date
    Feb 2014
    Posts
    447
    Rep Power
    1

    Default Re: Store count of thf rows to arraylist

    Hi,

    I think this is a sql topic.

    You want the rowmumber? Without an ORDER BY clause the ordering is not guaranteed so the rownumber is not fixed. Or did you have such a field in your table? Them you might be able to get that in a query.

    But then it is important which database you are using. Mysql seems to be very limited. No rownum, no cte expressions. How do people develop with that?

    Rownum could be done with a variable in your selection and you could do that workaround in your java code, too:
    Java Code:
     int rownum = 1;
     while (result.next()) {
                    rowCount =result.getInt("COUNt(*)");
                    // use rowNum here if you want
                    wbList.add(rowCount);
                    rowNum++;
               }
    Or is your problem not a row number. Instead you have problems to get the count?
    Give the column a real name. Select count(*) as count .... or something like that.

    With kind regards,

    Konrad

  3. #3
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default Re: Store count of thf rows to arraylist

    Hi Konrad,
    thank you for your answer

    Somehow your answer is not obvious. I have Column in my table called "wbuch". this attributes have 16 different value but each one of this different value iterate many times
    Any way i used mysql request
    Java Code:
    SELECT wbuch, COUNt(*) FROM myTable  GROUP BY wbuch;
    and i got the table in the first post.

    Any way i want i to store the result in the table under coulumn "COUNt(*)" (the row count) in array list

    i tried but i didn't get it correctly, somehow there is mistake, but yes i want the row count of column "wbuch" in my table

  4. #4
    kneitzel is offline Senior Member
    Join Date
    Feb 2014
    Posts
    447
    Rep Power
    1

    Default Re: Store count of thf rows to arraylist

    Ahh ok ... i got confused with row numbers.

    Did you try to change it to something like
    SELECT wbuch, COUNt(*) as wbuchcount FROM myTable GROUP BY wbuch;
    and
    rowCount =result.getInt("wbuchcount");

    That way cou give the second column a real name. This count(*) is not a real column name. At least it is that way in ms sql server.

    But maybe you can give us more details about the error you get. Without exact error messages it is always guessing.

    Konrad

  5. #5
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    Default Re: Store count of thf rows to arraylist

    Did you try to change it to something like
    SELECT wbuch, COUNt(*) as wbuchcount FROM myTable GROUP BY wbuch;
    what is "wbuchcount"? there is not such command in mysql (i know you mean wb counter) bust also give error

    Another thing, the error which i get it
    XML Code:
    java.io.WriteAbortedException: writing aborted; java.io.NotSerializableException: java.lang.Object
    that because the mysql command is not correct

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

    Default Re: Store count of thf rows to arraylist

    SELECT wbuch, COUNt(*) AS wbuchcount FROM myTable GROUP BY wbuch;

    The SQL command 'AS' gives an otherwise calculated column a name that makes sense to you (and JDBC).

    Also, can you post the full exception you are getting, including stack trace, but not in code tags.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    kneitzel is offline Senior Member
    Join Date
    Feb 2014
    Posts
    447
    Rep Power
    1

    Default Re: Store count of thf rows to arraylist

    When you want to access the columns of a resultset, then you can access the columns by columnIndex. That is a save way.
    If you want to access columns by name, then it simply requires some valid names. I still do not have experience with mysql but from my experience with SQL Server calculated columns do not have a valid name. And from my sql experience it is even a bad practice to not properly name your columns that you generate. (SQL Queries can be really complex so you really have to keep up good names! Ok, maybe mysql is to limited for that with no CTEs but I would be lost with some queries if I didn't name stuff correctly....)

    Maybe this also helps a little bit to understand my point. But Tolls pointed out the core point of my reply already.

Similar Threads

  1. store certain column in ArrayList
    By vector_ever in forum Apache POI
    Replies: 8
    Last Post: 07-12-2013, 05:06 PM
  2. Count number of rows
    By Kenan_89 in forum Advanced Java
    Replies: 11
    Last Post: 01-26-2012, 01:52 PM
  3. Replies: 37
    Last Post: 07-04-2011, 08:01 PM
  4. How to Store ArrayList into an Object?
    By TheGame in forum New To Java
    Replies: 6
    Last Post: 10-10-2010, 11:38 PM
  5. how to store an ArrayList<string> into cookies?
    By perplexingtrax in forum Java Servlet
    Replies: 7
    Last Post: 01-12-2009, 01:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •