Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'c.type' in 'field

Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column ‘c.type’ in ‘field list’

这是我XML文件里面的代码：

	<sql id="companyColumns">
		a.id AS "id",
		a.name AS "name",
		a.address AS "address",
		a.number AS "number",
		c.type AS "type",
		a.begindate AS "begindate",
		a.remarks AS "remarks"
	</sql>
    
	<select id="get" resultType="Company">
		SELECT 
			<include refid="companyColumns"/>
		FROM company a,companytype c
		<include refid="companyJoins"/>
		WHERE a.id = #{id} AND a.typeid = c.typeid
	</select>

报错信息如下：

 Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'c.type' in 'field list'
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'c.type' in 'field list'
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:231)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
	at com.sun.proxy.$Proxy28.selectList(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:205)
	at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
	at com.sun.proxy.$Proxy62.findList(Unknown Source)

解决方法

首先重新编写了SQL语句，使用了refid

	<sql id="companyColumns">
		a.id AS "id",
		a.name AS "name",
		a.address AS "address",
		a.number AS "number",
		c.type AS "type",
		a.begindate AS "begindate",
		a.remarks AS "remarks"
	</sql>
	
	<sql id="companyJoins">
		LEFT JOIN companytype c ON a.typeid = c.typeid
	</sql>
    
	<select id="get" resultType="Company">
		SELECT 
			<include refid="companyColumns"/>
		FROM company a 
		<include refid="companyJoins"/>
		WHERE a.id = #{id}
	</select>
	
	<select id="findList" resultType="Company">
		SELECT 
			<include refid="companyColumns"/>
		FROM company a
		<include refid="companyJoins"/>
		<where>
			<if test="name != null and name != ''">
				AND a.name LIKE 
					<if test="dbName == 'oracle'">'%'||#{name}||'%'</if>
					<if test="dbName == 'mssql'">'%'+#{name}+'%'</if>
					<if test="dbName == 'mysql'">concat('%',#{name},'%')</if>
			</if>
		</where>
		<choose>
			<when test="page !=null and page.orderBy != null and page.orderBy != ''">
				ORDER BY ${page.orderBy}
			</when>
			<otherwise>
			</otherwise>
		</choose>
	</select>

接着在对应实体类中添加了属性typeid（SQL中所有属性都在实体类中进行了定义）