[Solved] Unknown column XXX in field list (Hibernate and JPA)

I this post, I’d like to share some solutions to fix the error: Unknown column XXX in field list, where XXX can be a table alias name, a dot and the field name.

When developing Java applications with the Hibernate framework and Java Persistence API (JPA), you may encounter the following error at runtime (for example):

o.h.engine.jdbc.spi.SqlExceptionHelper: SQL Error: 1054, SQLState: 42S22
o.h.engine.jdbc.spi.SqlExceptionHelper: Unknown column 'c1_0.raw_client_secret' in 'field list'
...
java.sql.SQLSyntaxErrorException: Unknown column 'c1_0.raw_client_secret' in 'field list'

This error can be caused by improper mapping between a field or property in an entity class and a column in a database table. Try the following suggested solutions:

1. Correct column mapping in the entity class

This is quite obvious - somehow you have a typo in the field-column mapping in your entity class, like this:

@Entity
@Table(name = "users")
public class User implements Serializable {
	
	...
	
	@Column(name = "paasword", nullable = false, length = 70)
	private String password;
	
}

Here, you specify the mapped column name as paasword, whereas the actual name in the database table is password. So just fix the typo in the @Column annotation:

@Column(name = "password", nullable = false, length = 70)

Also, double check field names in the entity class. If you don’t use the @Column annotation, Hibernate will use the field name as the column name in the database table. For example:

private String emal;

This will map to the column named emal, but the actual column name in database is email. You need to correct it like this:

private String email;

2. Correct column name in the database table

If the field-column mapping in the entity class is correct, double-check the column names in the database table and modify them if something is wrong there. You can you MySQL Workbench to alter the table and change the column name to match the mapping name in the entity class:

You’re familiar with the command syntax of your database system, you can also alter the column names in the command line, such as using MySQL Command Line Client program.

3. Add @Transient annotation

If you have a property or field in the entity class but it is not persistent (i.e., not mapped to any column), it will cause the Unknown column … in field list error. This is because Hibernate consider all fields in an entity class to be mapped to columns in the database table. If you still want to keep the field without mapping it to a column, mark the field with the @Transient annotation. For example:

@Entity
@Table(name = "client_apps")
public class ClientApp {

	...
	
	@Transient
	private String rawClientSecret;
	
}

This is a possible solution to consider. Check this post to learn more about using the @Transient annotation.

4. Delete field in the entity class

An unused and unmapped field in the entity class can also cause this issue. In this case, you can fix it by simply deleting the field from the entity class.

5. Correct getter method name

By default, Hibernate infers getter methods in an entity class for column mapping. If you use a getter for field-column mapping, double-check the method name and modify it if something is wrong. For example:

public String getFulName() {
	return firstName + “ “ + lastName;
}

This method will map to the column ful_name in the database, but the actual column name is full_name. You can fix this by correcting the typo in the method name, as follows:

public String getFullName() {
	…
}

So far, I have shared five possible solutions you can consider to fix the Unknown column … in field list error. I hope you find this post helpful.

How to fix other errors:

• Unable to acquire JDBC Connection with Hibernate and MySQL
• Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set
• No Persistence provider for EntityManager named XXX

Recommended Hibernate Tutorials:

• How to use c3p0 database connection pooling library with Hibernate/JPA
• How to configure Proxool with Hibernate/JPA
• Java Hibernate JPA Annotations Tutorial for Beginners
• Hibernate Hello World Tutorial for Beginners with Eclipse and MySQL
• Hibernate One-to-One Association on Primary Key Annotations Example
• Hibernate One-to-Many Using Join Table XML Mapping Example
• Hibernate Many-to-Many Association with Extra Columns in Join Table Example
• Hibernate Enum Type Mapping Example
• Hibernate Binary Data and BLOB Mapping Example
• Hibernate Basics - 3 ways to delete an entity from the datastore
• Hibernate Query Language (HQL) Example
• Java Hibernate Reverse Engineering Tutorial with Eclipse and MySQL

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.