CODEWITHSUNDEEP
javamysql
Majid NK
Majid NK

Reputation: 311

How to find length of the longest field in each column from resultset

I know I can find the longest field in a column from specific table using by executing :

Statement s;
ResultSet r; 
r=s.executeQuery("SELECT MAX(LENGTH(field_to_query)) FROM table_to_query;");

But is there a way to get longest field in each column of obtained ResultSet after executing a query in Java:

Example: I have executed:

r=s.executeQuery("select * from emlployee,department where d_id=dept_id and dept_name="Research");

now I want to get length of longest field in each column in r.

Thank you

Upvotes: 0

Views: 644

Answers (3)

achabahe
achabahe

Reputation: 2565

here you go i have created i utility class that will give you an array of int that represents the max size of each column

import java.sql.ResultSet;
import java.sql.SQLException;

public class MyDbUtility {
    private MyDbUtility(){}
    public static int[] maxColumns(final ResultSet resultSet) throws SQLException {
        int columnNumber = resultSet.getMetaData().getColumnCount();
        int[] max = new int[columnNumber];
        int currentRow = resultSet.getRow();
        int fetchDirection = resultSet.getFetchDirection();
        if (fetchDirection == ResultSet.FETCH_FORWARD) {
            resultSet.beforeFirst();
        }else {
            resultSet.afterLast();
        }

        while (resultSet.next()) {
            for (int i = 1; i <= columnNumber; i++) {
                if (resultSet.getString(i).length() > max[i-1]) {
                    max[i-1] = resultSet.getString(i).length();
                }
            }
        }
        resultSet.absolute(currentRow);
        return max;
    }
}

usage

    int[] max=MyDbUtility.maxColumns(reslut);

Upvotes: 2

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can cascade it with GREATEST like this:

SELECT 
  GREATEST(
    MAX(LENGTH(field_to_query1)), GREATEST( MAX(LENGTH(field_to_query2)), MAX(LENGTH(field_to_query3)) ))
FROM table_to_query;

Upvotes: 0

achabahe
achabahe

Reputation: 2565

use the same query and find the max of every column , you are so close

    SELECT MAX(LENGTH(field_to_query1))as f1 , MAX(LENGTH(field_to_query2))as f2 FROM table_to_query;

Upvotes: 2

Related Questions