org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Column with name «name» isn't present in this «ResultSet» Hibernate with PostgreSQL JDBC

Question

As the subject says, I have that error in Hibernate 5 with a PostgreSQL DB.

The query that Hibernate returns to me is: SELECT u.email, u.password FROM utenti u WHERE u.email = ? (so it's correct) and the table is:

utenti(email (pk), password, nome, cognome, datanascita, sesso)

Moreover, I get another error: Exception starting filter struts2

Unable to load configuration. - action - file:<path>/NetBeansProjects/DBIIProject/target/DBIIProject-1.0-SNAPSHOT/WEB-INF/classes/struts.xml:15:34

My struts.xml is:

    <?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE struts PUBLIC
    "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
    "http://struts.apache.org/dtds/struts-2.3.dtd">
<struts>

    <constant name="struts.enable.DynamicMethodInvocation" value="false"/>
    <constant name="struts.devMode" value="true"/>

    <!-- Cambio l'estensione delle Action da .action a .do -->
    <constant name="struts.action.extension" value="do" />

    <package name="default" extends="struts-default" namespace="/">
        <action name="Login" class="org.altervista.cdpjavaexamples.dbiiproject.actions.LoginAction" method="execute">
            <result name="success">index.jsp</result>
            <result name="input">login.jsp</result>
        </action>
        <action name="Logout" class="org.altervista.cdpjavaexamples.dbiiproject.actions.LogoutAction" method="execute">
            <result name="success">index.jsp</result>
        </action>
        <action name="PasswordRecovery" class="org.altervista.cdpjavaexamples.dbiiproject.actions.PasswordRecoveryAction" method="execute">
            <result name="success">password_recovery.jsp</result>
            <result name="input">password_recovery.jsp</result>
        </action>
    </package>
</struts>

My pom.xml is:

    <?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>org.altervista.cdpjavaexamples</groupId>
    <artifactId>DBIIProject</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>war</packaging>
    <name>DBIIProject</name>

    <properties>
        <struts2.version>2.3.24.1</struts2.version>
        <log4j2.version>2.2</log4j2.version>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.apache.struts</groupId>
            <artifactId>struts-taglib</artifactId>
            <version>1.3.10</version>
        </dependency>

        <dependency>
            <groupId>org.apache.struts</groupId>
            <artifactId>struts2-convention-plugin</artifactId>
            <version>2.3.24.1</version>
        </dependency>

        <dependency>
            <groupId>com.sun.mail</groupId>
            <artifactId>javax.mail</artifactId>
            <version>1.5.5</version>
        </dependency>

        <dependency>
            <groupId>jstl</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>

        <dependency>
            <groupId>javax.mail</groupId>
            <artifactId>javax.mail-api</artifactId>
            <version>1.5.5</version>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>9.4.1207.jre7</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.0.7.Final</version>
        </dependency>

        <dependency>
            <groupId>org.apache.struts</groupId>
            <artifactId>struts2-core</artifactId>
            <version>${struts2.version}</version>
        </dependency>

        <dependency>
            <groupId>org.apache.struts</groupId>
            <artifactId>struts2-junit-plugin</artifactId>
            <version>${struts2.version}</version>
            <scope>test</scope>
        </dependency>


        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>${log4j2.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>${log4j2.version}</version>
        </dependency>

        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.8.2</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.4</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.0</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.3</version>
                <configuration>
                    <encoding>UTF-8</encoding>
                    <source>1.5</source>
                    <target>1.5</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.mortbay.jetty</groupId>
                <artifactId>jetty-maven-plugin</artifactId>
                <version>8.1.16.v20140903</version>
                <configuration>
                    <stopKey>CTRL+C</stopKey>
                    <stopPort>8999</stopPort>
                    <systemProperties>
                        <systemProperty>
                            <name>xwork.loggerFactory</name>
                            <value>com.opensymphony.xwork2.util.logging.log4j2.Log4j2LoggerFactory</value>
                        </systemProperty>
                    </systemProperties>
                    <scanIntervalSeconds>10</scanIntervalSeconds>
                    <webAppSourceDirectory>${basedir}/src/main/webapp/</webAppSourceDirectory>
                    <webAppConfig>
                        <descriptor>${basedir}/src/main/webapp/WEB-INF/web.xml</descriptor>
                    </webAppConfig>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

Finally my Hibernate's files are:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
    <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
    <property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/testdb</property>
    <property name="hibernate.connection.username">test</property>
    <property name="hibernate.connection.password">test</property>
    <property name="hibernate.show_sql">true</property>
    <mapping class="" file="" jar="" package="" resource="org/altervista/cdpjavaexamples/dbiiproject/model/Utenti.hbm.xml"/>
  </session-factory>
</hibernate-configuration>

and:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
  <!-- Mappatura classe - tabella -->
  <class name="org.altervista.cdpjavaexamples.dbiiproject.model.Utenti" table="utenti">
    <meta attribute="class-description">
          Descrizione della classe
      </meta>
    <!-- Chiave primaria -->
    <id column="email" name="email" type="text"/>
    <!-- Campi -->
    <property column="nome" name="nome" not-null="true" type="text"/>
    <property column="cognome" name="cognome" not-null="true" type="text"/>
    <property column="password" name="password" not-null="true" type="text"/>
    <property column="sesso" name="sesso" not-null="true" type="boolean"/>
    <property column="datanascita" name="dataNascita" not-null="true" type="date"/>
  </class>

  <!-- Query -->
  <sql-query name="selectEmailPassword">
      <return class="org.altervista.cdpjavaexamples.dbiiproject.model.Utenti"/>
      <![CDATA[
        SELECT email, password FROM utenti WHERE email = :email
      ]]>
  </sql-query>
  <sql-query name="selectNomeCognomePassword">
      <return class="org.altervista.cdpjavaexamples.dbiiproject.model.Utenti"/>
      <![CDATA[
        SELECT nome, cognome, password FROM utenti WHERE email = :email
      ]]>
  </sql-query>
  <sql-query name="emailExists">
      <return class="org.altervista.cdpjavaexamples.dbiiproject.model.Utenti"/>
      <![CDATA[
        SELECT * FROM utenti WHERE email = :email
      ]]>
  </sql-query>
  <!-- Fine query -->
</hibernate-mapping>

Project's structure

I have already searched in the forum, but the solutions I found weren't good to solve my problem.

It's nome. ;) I typo it when I translated it from italian to english. — cdprete, Feb 11 '16 at 13:35

score 0 · Answer 1 · answered Feb 11 '16 at 06:00

0

You define <return class="org.altervista.cdpjavaexamples.dbiiproject.model.Utenti"/> and it looks like the class has name property (e.g. marked to used "name" column). When your SQL is executed hibernate tries to fill the entity but ResultSet does not have column to fill the name.

answered Feb 11 '16 at 06:00

StanislavL

56,971
9
68
98

So, how can I get only some columns with named queries? – cdprete Feb 11 '16 at 13:36
Your SQL query should include all columns mentioned in the entity – StanislavL Feb 11 '16 at 14:08
All you declare in your entity. Or you can declare another entity with less columns – StanislavL Feb 11 '16 at 14:29
Ok, I understood. But why if I use Criteria I don't get that error? And, instead, I get an error if I try to retrive all the data for a row? Criteria cr = session.createCriteria(Utenti.class) .add(Restrictions.eq("email", email)) .setResultTransformer(Transformers.aliasToBean(Utenti.class)); List results = cr.list(); exists = !results.isEmpty(); – cdprete Feb 11 '16 at 14:48

Roman C · Accepted Answer · 2016-02-11T09:23:38.393

0

You have used native SQL queries in Hibernate mapping with <sql-query>. These kind of query have to work with native SQL dialect specific to the database on which this query could be executed. So if you make an error in SQL code, or change the database, your query might not work. There's no point to use native SQL query in your code, and they should be replaced with named queries.

Using a mapping document can be configured using the <query> node. Remember to use a CDATA section if your query contains characters that could be interpreted as markup.

Here's an example of such query

<query name="selectEmailPassword"><![CDATA[
    from org.altervista.cdpjavaexamples.dbiiproject.model.Utenti as u
    WHERE u.email = :email
]]></query>

Parameter binding

Query q = sess.getNamedQuery("selectEmailPassword");
q.setString("email", email);
List<Utenti> l = q.list();

edited Feb 11 '16 at 09:23

answered Feb 11 '16 at 09:09

Roman C

49,761
33
66
176

I want to use native queries so that the DBA can change them without have to touch Hibernate ;) – cdprete Feb 11 '16 at 13:39
DBA can only change the query if it's located on the server. He can't change the application code.The client's queries have to change by the developer. – Roman C Feb 11 '16 at 16:40
For this reason I want to put the queries inside Utenti.hbm.xml e not inside the code.. – cdprete Feb 11 '16 at 17:21
Understood. But why if I use Criteria objects it works? For example this works without problems: `Criteria cr = session.createCriteria(Utenti.class); cr.setProjection(Projections.projectionList() .add(Projections.property("email"), "email") .add(Projections.property("password"), "password")) .add(Restrictions.eq("email", email)) .setResultTransformer(Transformers.aliasToBean(Utenti.class)); List results = cr.list();` – cdprete Feb 11 '16 at 18:08
You can also use Criteria instead of native SQL, even better without projections and transformations. – Roman C Feb 11 '16 at 18:18
There's another way to select only some columns with Criteria without using Projections? – cdprete Feb 11 '16 at 18:30
If you have another question you can ask it on another page. If this answer helped you to solve your problem, please mark it as accepted. I don't see a reason to continue this discussion. – Roman C Feb 11 '16 at 18:41
Sure, but I still get the error if I use named native queries. – cdprete Feb 11 '16 at 18:58

score 0 · Answer 3 · answered Feb 14 '16 at 17:42

0

Thanks to all. At the end I used Criteria objects to perform queries.

answered Feb 14 '16 at 17:42

cdprete

53
1
9

org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Column with name «name» isn't present in this «ResultSet» Hibernate with PostgreSQL JDBC

3 Answers3