Hibernate can’t find an existing column, ERRORCODE=-4460

Question

I am having a difficulty in my system where SQL with Hibernate can not find a specific column by Java, the same query returns the right result when rotated by SQL. IDE used is Netbeans.

The query is the following:

SELECT DE_CONTEUDO FROM ASP.PDI_EMAIL_MODELO WHERE CD_EMAIL_MODELO = 1;

When run by SQL:

When run by Java and Hibernate the program issues the following error:

Parâmetro inválido: nome de coluna desconhecido CD_EMAIL_MODELO. ERRORCODE=-4460, SQLSTATE=null

The Java method is this:

public List pesquisar(){
    String sql = "SELECT DE_CONTEUDO FROM ASP.PDI_EMAIL_MODELO WHERE CD_EMAIL_MODELO = 1";
    Session session = HibernateUtil.open();
    List query = session.createSQLQuery(sql).addEntity(EmailModelo.class).list();
    session.close();    
    return query;
}

The Entity is as follows:

@Entity
@Table(name = "ASP.PDI_EMAIL_MODELO")
public class EmailModelo implements Serializable {

    @Id
    @Column(name = "CD_EMAIL_MODELO")
    private long cdEmailModelo;

    @Column(name = "DE_CONTEUDO")
    private String conteudo;

    // Getters e setters simples ...
}

And Hibernate.cfg is mapped this way:

<mapping class="persistencia.EmailModelo"/>

Additional details:

• Searches with non-existent rows in the table return an empty list without error in the program. Ex: CD_EMAIL_MODELO = 20
• I’ve had problems with querys in this IDE that the solution was to literally write the same query in low box but tried this case and did not solve

Answer 1

There are some Hibernate version combinations with the DB2 connection driver version that may generate this error.

The explanation is that older versions of Hibernate (3.x) read the table metadata column name using the field columnName, where the JDBC specification requires the field to be used columnLabel.

Older versions of JDBC were unclear as to this distinction, so it was normal for the drivers to be implemented differently across databases. IBM changed this behavior in driver version 9.5.

The solution, it seems, are two: upgrade Hibernate to a 4.x version or update the connection driver. If for compatibility reasons the driver update is not possible, parameter useJDBC4ColumnNameAndLabelSemantics=2 can be used with the newest connection driver to resolve this issue and maintain compatibility.