How to getString(Table.Column) in ResultSet JayBird

Question

I need to use the database Firebird and for this I use the Jaybird 2.2.9.

When I used the MySQL driver, to converter of ResultSet to Object this way:

empresa.setBairro(rs.getString("empresa.bairro")); // (Table.Column)
empresa.setCep(rs.getString("empresa.cep")); // (Table.Column)
empresa.setCidade(rs.getString("empresa.cidade")); // (Table.Column)

But with Jaybird the resultSet don't return rs.getString("Table.Column")

I need this way when I have inner join in SQL.

Anyone help me?

This is my full code

public ContaLivros converterContaLivros(ResultSet rs, Integer linha) throws Exception {
    if (rs.first()) {

        rs.absolute(linha);

        ContaLivros obj = new ContaLivros();

        obj.setId(rs.getLong("cad_conta.auto_id"));
        obj.setNome(rs.getString("cad_conta.nome"));


        if (contain("cad_banco.auto_id", rs)) {

            obj.setBancoLivros(converterBancoLivros(rs, linha));
        } else {

            obj.setBancoLivros(new BancoLivros(rs.getLong("cad_conta.banco"), null, null, null));
        }
        obj.setAgencia(rs.getInt("cad_conta.agencia"));
        obj.setAgenciaDigito(rs.getInt("cad_conta.agencia_digito"));
        obj.setConta(rs.getInt("cad_conta.conta"));
        obj.setContaDigito(rs.getInt("cad_conta.conta_digito"));
        obj.setLimite(rs.getDouble("cad_conta.limite"));
        obj.setAtivo(rs.getString("cad_conta.ativo"));

        return obj;
    } else {
        return null;
    }
}

Do you have code for resultset to object mapper? You will not have two object fields with same name as well? — Jan, Dec 29 '15 at 18:31
If I use rs.getString("Table.Column") as mysql driver, I never have two object fields with same name. — Tiago Roque de Carvalho, Dec 29 '15 at 18:41
And how did you map table1.col and table2.col into the object? If you showed part of your code we could think of a better way. By the way: most object-relatuonal mappers i know do it witj aliases — Jan, Dec 29 '15 at 18:51
If you have no **duplicate** column names you can simply do rs.getInt("agencia")); and so on?! — Jan, Dec 29 '15 at 19:17
I have the column auto_id duplicate in table cad_conta and cad_banco — Tiago Roque de Carvalho, Dec 29 '15 at 19:30
The way the MySQL driver allows you to request a column with `.` is non-standard (it is not specified in the JDBC specification). The JDBC specification also specifies that if there are multiple columns with the same name, then it should return the first column with that name — Mark Rotteveel, Dec 30 '15 at 08:23

Jan · Answer 1 · 2015-12-29T19:18:20.530

2

The name in jdbc will not have the table in it.

You can either

work with positional parameters ( getString (1); and so on )

Or

define column name alias in your select (select a.name namefroma from tableone a )

Or

simply do rs.getString ("column"); without the table prefix if name is unambigous

edited Dec 29 '15 at 19:18

answered Dec 29 '15 at 17:13

Jan

13,738
3
30
55

I used your second suggestion. But I think this way is very work, all sql I need to use alias, because I have one converter for table to object. Don't have another way as mysql driver? – Tiago Roque de Carvalho Dec 29 '15 at 17:41
If your tables have lots of column names in common I think you have to go down that hard road. – Jan Dec 29 '15 at 17:44

score 2 · Accepted Answer · answered Dec 30 '15 at 08:44

2

You can't. Jaybird retrieves the columns by its label as specified in JDBC 4.2, section 15.2.3. In Firebird the column label is either the original column name, or the AS alias, the table name isn't part of this. The extension of MySQL that you can prefix the table name for disambiguation is non-standard.

Your options are to specify aliases in the query and retrieve by this aliasname, or to process the result set metadata to find the right indexes for each column and retrieve by index instead.

However note that in certain queries (for example UNION), the ResultSetMetaData.getTableName cannot return the table name, as Firebird doesn't "know" it (as you could be applying a UNION to selects from different tables).

answered Dec 30 '15 at 08:44

Mark Rotteveel

100,966
191
140
197

I understood, but this solution facilitated my live with mysql driver. With JayBird I will use alias in my sql. If you can implement this solution in JayBird I think that help many programmers as I. Tanks @Mark Rotteveel – Tiago Roque de Carvalho Dec 30 '15 at 12:17
@TiagoRoquedeCarvalho If you create an improvement request in the tracker at http://tracker.firebirdsql.org/browse/JDBC then I will consider it (no promises though, there is more to do and my time is limited, and I am not sure I like the idea of adding non-standard features). – Mark Rotteveel Dec 30 '15 at 12:29
I will create an improvement request, Thanks for your help. – Tiago Roque de Carvalho Dec 30 '15 at 15:44

How to getString(Table.Column) in ResultSet JayBird

2 Answers2