JPA自定义SQL Column ××× not found.

项目场景：

在一次合并Spring JPA实体类时，由于两个实体类功能相近，只是个别字段名不一样，于是我就把两个实体类合并为了一个实体类。结果在使用JPA中自定义的sql进行数据查询时，出现了Column ××× not found 错误。

---

问题描述

如下是错误提示： 

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)

..........................
Caused by: java.sql.SQLException: Column 'parent_id' not found.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)

---

原因分析：

从上面的结果看是说，不能执行查询，造成这种情况的原因是 parent_id字段找不到

1.我第一想法是sql中存在parent_id而数据库中没有，通过对比发现sql中不存在parent_id所以排除这种可能。

2.通过查看实体类字段，发现实体类中存在parentId字段，推测可能是实体类中与sql字段不一致导致，于是注释掉该字段，发现确实起作用了。

由此可以判断确实是实体类字段与查询sql中的字段不一致导致的。

---

解决方案：

原因找到了，问题在于我不能去除实体类中的字段（因为其他地方会用到该字段），同时我也不想从数据库中查询出该字段信息（因为这个字段需要通过连表查询）。这时可以通过在select 语句中返回一个常量解决。解决方法如下：

SELECT
	id,
	'' parent_id 
FROM
	tb_user

查询结果：

 到此问题解决了。但是，为什么出现这个问题，有兴趣的小伙伴可以继续往下看。

异常堆栈信息源码追踪：

完整错误日志如下：

Caused by: java.sql.SQLException: Column 'parent_id' not found.
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
    at com.mysql.cj.jdbc.result.ResultSetImpl.findColumn(ResultSetImpl.java:575)
    at com.mysql.cj.jdbc.result.ResultSetImpl.getString(ResultSetImpl.java:882)
    at com.zaxxer.hikari.pool.HikariProxyResultSet.getString(HikariProxyResultSet.java)
    at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:62)
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:47)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:257)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:253)
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:243)
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:329)
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:3130)
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1869)
    at org.hibernate.loader.Loader.hydrateEntityState(Loader.java:1797)
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1770)
    at org.hibernate.loader.Loader.getRow(Loader.java:1622)
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:740)
    at org.hibernate.loader.Loader.getRowsFromResultSet(Loader.java:1039)
    at org.hibernate.loader.Loader.processResultSet(Loader.java:990)
    at org.hibernate.loader.Loader.doQuery(Loader.java:959)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:349)
    at org.hibernate.loader.Loader.doList(Loader.java:2849)
    ... 63 common frames omitted

从上面错误信息可以看到错误原因可能是hibernate调用了mysql驱动异常出现了问题。问题的出现的关键点可能就是hibernate和mysql数据交互这块。查看ResultSetImpl.getString(ResultSetImpl.java:882)中代码及注释：

​​​​​​​

    @Override
    public String getString(String columnName) throws SQLException {
        return getString(findColumn(columnName));
    }

    // Methods for accessing results by column label
    //通过字段名访问结果集中的数据

    /**
     * Retrieves the value of the designated column in the current row
     * of this <code>ResultSet</code> object as
     * a <code>String</code> in the Java programming language.
     *
    
     //根据字段名检索当前行中指定值，返回java类型的数据

     * @param columnLabel the label for the column specified with the SQL AS clause.  If the SQL AS clause was not specified, then the label is the name of the column
     * @return the column value; if the value is SQL <code>NULL</code>, the
     * value returned is <code>null</code>
     * @exception SQLException if the columnLabel is not valid;
     * if a database access error occurs or this method is
     *            called on a closed result set

    //当字段名不正确或者数据库访问出现问题或者调用一个已经关闭的数据集时抛出异常
     */
    String getString(String columnLabel) throws SQLException;

//查找数据集中是否包含columnName字段，如果不存在抛出异常

    @Override
    public int findColumn(String columnName) throws SQLException {
        synchronized (checkClosed().getConnectionMutex()) {
            Integer index = this.columnDefinition.findColumn(columnName, this.useColumnNamesInFindColumn, 1);

            if (index == -1) {
                throw SQLError.createSQLException(
                        Messages.getString("ResultSet.Column____112") + columnName + Messages.getString("ResultSet.___not_found._113"),
                        MysqlErrorNumbers.SQL_STATE_COLUMN_NOT_FOUND, getExceptionInterceptor());
            }

            return index;
        }
    }

    /**
     * Maps the given <code>ResultSet</code> column label to its
     * <code>ResultSet</code> column index.
     *
     * @param columnLabel the label for the column specified with the SQL AS clause.  If the SQL AS clause was not specified, then the label is the name of the column
     * @return the column index of the given column name
     * @exception SQLException if the <code>ResultSet</code> object
     * does not contain a column labeled <code>columnLabel</code>, a database access error occurs
     *  or this method is called on a closed result set
//如果数据集中不包含字段或者访问数据库出现异常，抛出异常
     */
    int findColumn(String columnLabel) throws SQLException;

从上面可以得出结论是因为我们需要查询的字段没有出现在结果集中，导致出现了异常。到此异常出现的原因已经了解了。作为一个优秀的jpa框架，hibernate是否还提供了其他的扩展来避免这种异常呢（比如说通过配置的方式决定，如果实体类中字段不存在并不抛出异常，而是赋值默认值呢）。带着这个疑问我们继续查看上层代码：

//从这里看出 我们只是把参数 javaTypeDescriptor 进行封装转换为ValueExtractor对象，至于
// ValueExtractor的作用我们看源码注释可暂时理解为一个从jdbc结果集中提取数据的的对象。源码注释如下：Contract for extracting value via JDBC (from {@link ResultSet} or as output param from {@link CallableStatement}).

@Override
	public <X> ValueExtractor<X> getExtractor(final JavaTypeDescriptor<X> javaTypeDescriptor) {
		return new BasicExtractor<X>( javaTypeDescriptor, this ) {
			@Override
			protected X doExtract(ResultSet rs, String name, WrapperOptions options) throws SQLException {
				return javaTypeDescriptor.wrap( rs.getString( name ), options );
			}

			@Override
			protected X doExtract(CallableStatement statement, int index, WrapperOptions options) throws SQLException {
				return javaTypeDescriptor.wrap( statement.getString( index ), options );
			}

			@Override
			protected X doExtract(CallableStatement statement, String name, WrapperOptions options) throws SQLException {
				return javaTypeDescriptor.wrap( statement.getString( name ), options );
			}
		};
	}

/**
	 * Get the extractor (pulling out-going values from JDBC objects) capable of handling values of the type described
	 * by the passed descriptor.
	 *
	 * @param javaTypeDescriptor The descriptor describing the types of Java values to be extracted
	 *
	 * @return The appropriate extractor
	 */
	<X> ValueExtractor<X> getExtractor(JavaTypeDescriptor<X> javaTypeDescriptor);

/**
	 * Unmarshal the fields of a persistent instance from a result set,
	 * without resolving associations or collections. Question: should
	 * this really be here, or should it be sent back to Loader?
	 */
	public Object[] hydrate(
			final ResultSet rs,
			final Serializable id,
			final Object object,
			final Loadable rootLoadable,
			final String[][] suffixedPropertyColumns,
			final boolean allProperties,
			final SharedSessionContractImplementor session) throws SQLException, HibernateException {

........省略无关代码
			final String[] propNames = getPropertyNames();
			final Type[] types = getPropertyTypes();
			final Object[] values = new Object[types.length];
			final boolean[] laziness = getPropertyLaziness();
			final String[] propSubclassNames = getSubclassPropertySubclassNameClosure();

            //根据实体类映射字段获取结果集中的数据
			for ( int i = 0; i < types.length; i++ ) {

				if ( !propertySelectable[i] ) {
					values[i] = PropertyAccessStrategyBackRefImpl.UNKNOWN;
				}
				else if ( allProperties || !laziness[i] ) {
					//decide which ResultSet to get the property value from:
					final boolean propertyIsDeferred = hasDeferred &&
							rootPersister.isSubclassPropertyDeferred( propNames[i], propSubclassNames[i] );
					if ( propertyIsDeferred && sequentialSelectEmpty ) {
						values[i] = null;
					}
					else {
						final ResultSet propertyResultSet = propertyIsDeferred ? sequentialResultSet : rs;
						final String[] cols = propertyIsDeferred ?
								propertyColumnAliases[i] :
								suffixedPropertyColumns[i];
                    //在此根据 cols 获取jdbc结果集中的数据
						values[i] = types[i].hydrate( propertyResultSet, cols, session, object );
					}
				}
				else {
					values[i] = LazyPropertyInitializer.UNFETCHED_PROPERTY;
				}
			}

			if ( sequentialResultSet != null ) {
				session.getJdbcCoordinator().getResourceRegistry().release( sequentialResultSet, sequentialSelect );
			}

			return values;

		}
		......省略
	}

/**
	 * Retrieve property values from one row of a result set

//从结果集中抽取一行数据
	 */
	Object[] hydrate(
			ResultSet rs,
			Serializable id,
			Object object,
			Loadable rootLoadable,
			String[][] suffixedPropertyColumns,
			boolean allProperties,
			SharedSessionContractImplementor session) throws SQLException, HibernateException;

从上面的hydrate方法看到并没有出现我们想要的扩展点，到此我们的也就没必要对源码进行继续追踪了。