[MySQL]Hibernate NativeSQL连接查询无法识别关联表名称相同列

MySQL版本：5.6.19

MySQL JDBC驱动版本：mysql-connector-java-5.1.30-bin

Hibernate版本：3.6.0

create table user (
  id int not null,
  name varchar(10),
  areaid int,
  primary key (id)
);

insert into user values(1, 'sean', 1);

create table area (
  areaid int not null,
  name varchar(10),
  primary key (areaid)
);

insert into area values(1, 'Nanjing');

hibernate.cfg.xml：

<?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="show_sql">false</property>  
      
        <property name="dialect">org.hibernate.dialect.MySQLDialect</property>  
        <property name="connection.driver_class">com.mysql.jdbc.Driver</property>  
        <property name="connection.url">jdbc:mysql://127.0.0.1:3306/test</property>  
        <property name="connection.username">root</property>  
        <property name="connection.password">123456</property>  
    </session-factory>  
</hibernate-configuration> 

HibernateTest.java：

package com.sean;

import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HibernateTest {
	public static void main(String[] args) {
		Configuration conf = new Configuration();    
        SessionFactory sessionFactory = conf.configure().buildSessionFactory();    
        Session session = sessionFactory.openSession();    
        StringBuilder strB = new StringBuilder();
        strB.append("select u.name as userN, a.name as areaN ");
        strB.append("from user u left outer join area a ");
        strB.append("on u.areaid = a.areaid ");
        String sql = strB.toString(); 
        System.out.println(sql);
        SQLQuery query = session.createSQLQuery(sql);  
        List<Object[]> result = query.list();  
        for(Object[] objs : result)
        	System.out.println(objs[0] + " " + objs[1]);
	}
}

执行结果如下：

SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
select u.name as userN, a.name as areaN from user u left outer join area a on u.areaid = a.areaid 
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.loader.Loader.doList(Loader.java:2536)
	at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
	at org.hibernate.loader.Loader.list(Loader.java:2271)
	at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
	at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1842)
	at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
	at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:157)
	at com.sean.HibernateTest.main(HibernateTest.java:22)
Caused by: java.sql.SQLException: Column 'name' not found.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1084)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:987)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:973)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
	at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1162)
	at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5728)
	at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:61)
	at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:64)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:249)
	at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:234)
	at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:505)
	at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:451)
	at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:348)
	at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:639)
	at org.hibernate.loader.Loader.doQuery(Loader.java:829)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
	at org.hibernate.loader.Loader.doList(Loader.java:2533)
	... 7 more

实际上查询语句在MySQL中是可以正常执行的

可以将查询语句中的别名去掉，程序可以正常运行，但是查询结果不正确

select u.name, a.name from user u left outer join area a on u.areaid = a.areaid 
sean sean

 

解决方式：

最好的方式还是修改字段名称，如果不想修改字段名称，可以通过使用子查询的方式在表做关联之前，改变字段名称

select u.userN, a.areaN from (select name as userN, areaid from user) u left outer join (select areaid, name as areaN from area) a on u.areaid = a.areaid 
sean Nanjing

特意进行了验证以排除JDBC驱动的问题

package com.sean;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCTest {
	public static void main(String[] args) throws Exception{
		 Class.forName("com.mysql.jdbc.Driver");
         Connection conn = DriverManager.getConnection(  
                     "jdbc:mysql://127.0.0.1:3306/test",   
                     "root", 
                     "123456");   
         Statement stat =conn.createStatement(); 
         StringBuilder strB = new StringBuilder();
         strB.append("select u.name as userN, a.name as areaN ");
         strB.append("from user u left join area a ");
         strB.append("on u.areaid = a.areaid");
         String sql = strB.toString();
         System.out.println(sql);
         ResultSet rs = stat.executeQuery(sql);
         while(rs.next()){
        	 System.out.println(rs.getString(1) + " " + rs.getString(2));
         }
	}
}

执行结果如下：

select u.name as userN, a.name as areaN from user u left join area a on u.areaid = a.areaid
sean Nanjing