SQL postgres - How to rename each column after join because their are too many fields

Question

I want to join two tables and read all the columns within java. In java the resultset contains all the fields but they have the same names for both tables. Their are about 65 columns and I do not want to specify each column after the select.

SQL query:

select * from Tab a join Tab b on a.id = b.id;

In java I want:

private MyObject map(ResultSet rs) throws SQLException {
    myObj.setNameA(rs.getString("a_name"));
    myObj.setNameB(rs.getString("b_name"));
}

My idea was to rename each column with the variables of the tables like 'a_name' and 'b_name' and then to read the fields in java.

Can I rename each field of the table without specifying each column after the select? (like 'select a.name as a_name, b.name as b_name,...')

Or is their any better solution to get all the columns in java?

Yes you can do it in SQL only just you need to write simple query in sql — Iqbal
– Iqbal, Commented Sep 29, 2016 at 7:57

dsp_user · Accepted Answer · 2016-09-29 08:20:25Z

You can use column numbers instead of column names. The Javadoc explicitly states (http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html)

When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.

Using column numbers would, of course, require knowing the exact positions (1 based) of your columns.

private MyObject map(ResultSet rs) throws SQLException {
    myObj.setNameA(rs.getString(1));
    myObj.setNameB(rs.getString(66));
}

Collectives™ on Stack Overflow

SQL postgres - How to rename each column after join because their are too many fields

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related