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>

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

Answer 1

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();