CODEWITHSUNDEEP
javamysqlsorting
Peter Kaufman
Peter Kaufman

Reputation: 1102

Collection.sort Not Returning Expected Result

I am trying to determine the order of columns in a MySQL database in Java. I have the create statement(this.create) of the table and the name of the columns (column.getName()). I then find the index of the column name in the create statement. I assign these values to a List, and then, I sort them using Collectio.sort(). This isthen used to determine the order of the columns. The only issue is that I am not getting the expected result. Here is my code: 

 public void orderColumns() {

        List<Integer> cols = new ArrayList();
        for ( column column : columns ) {

            cols.add( this.create.indexOf( "`" + column.getName() + "`"));
        }

        Collections.sort( cols );

        for ( column column : columns ) {
            for ( int i = 0; i < cols.size(); i++) {
                if ( this.create.indexOf( "`" + column.getName() + "`") == cols.get( i )) {
                    this.order_of_columns.add( column );
                }
            }
        }
        System.out.println(this.create);
        for( column column : order_of_columns ) {

            System.out.println( this.name + " : " + this.create.indexOf( "`" + column.getName() + "`") + " " + column.getName());
        }
    }

Here is the output for one table: 

CREATE TABLE `versions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  `version` varchar(10) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;
versions : 77 name
versions : 119 version
versions : 28 id

Any ideas of what I am doing wrong? Thank you so much for the help!

Upvotes: 1

Views: 98

Answers (2)

Usagi Miyamoto
Usagi Miyamoto

Reputation: 6329

Try something like this:

 public void orderColumns()
 {
    Map<Integer, Column> cols = new SortedMap<>();
    for ( Column column : columns )
    {
        cols.put( this.create.indexOf(  "`" + column.getName() + "`" ), column );
    }

    System.out.println( this.create );
    for( Map.Entry<Integer, Column> entry : cols )
    {
        System.out.printf( "%s: %d %s%n", this.name entry.getKey(),
                                          entry.getValue().getName() );
    }
}

Upvotes: 2

ajb
ajb

Reputation: 31699

The sort is working fine, but you're not actually using the order that the sort produced. After the sort, here's what you do:

    for ( column column : columns ) {
        for ( int i = 0; i < cols.size(); i++) {
            if ( this.create.indexOf( "`" + column.getName() + "`") == cols.get( i )) {
                this.order_of_columns.add( column );
            }
        }
    }

That is, you go through the columns in the original order. For each column, you go through cols to see if the indexOf result is in the cols array. But for this purpose, the order of elements in the cols array won't matter, since all you're doing is checking to see whether the index is somewhere in that array. You're actually adding things to order_of_columns in the same order that they appear in columns. So you haven't rearranged anything, and your sort is useless.

However, if you go through cols first, then you'll be adding to order_of_columns based on the order in cols. Therefore, I think that if you just switch the two for's, you'll get the answer you want.

There are better ways to do this, though. Your code is doing a lot of duplicate work by sorting just the integers, and then going through and figuring out what columns correspond to those integers--work you've already done. Better would be to create a POJO class ColumnAndIndex that has an Integer and a column, and make it a Comparable class whose compareTo() compares just the integer (which will be the indexOf value). Instead of a List<Integer>, create a List<ColumnAndIndex> that stores both the column and the index. Use sort on that; that will sort on the indexes. Now just go through the result, and the columns will already be there--no extra work needed.

Upvotes: 4

Related Questions