How to increase length of a String in mysql while mapping using JPA

Question

I'm having some trouble in JPA. I would be really thankful if someone could provide a solution. WIth JPA (I'm using MySQL DB), lets say, I have a class mapped as follows:

@Entity    
class Employee{    
   int id;    
   String employeeName;    
 //getters and setters...   
 }

when mapping to the table, I see that String is mapped with varchar(255) in Mysql. However, lets say if I have an employee whose name is more than 255 chars, it shows data truncation error.

I know we could solve this by adding "length" attribute to the Employee column as:

@column(length=1000)    
String employeeName;

Is this the only possible way? I thought, if we just map to String in java, the database would dynamically assign length.

Answer 1

when mapping to the table, I see that String is mapped with varchar(255) in Mysql

This is because the default length of VARCHAR columns in DDL statements created by most JPA providers (including Hibernate and EclipseLink) is 255. Specifying the length attribute to the @Column annotation helps override the value, so that the new value is picked up by the schema generator of the JPA provider.

I thought, if we just map to String in java, the database would dynamically assign length.

This is an incorrect assumption. The JPA provider will create tables only once, and will not dynamically change the length of the underlying table during the lifetime of the application, and only if you configure the provider to create/update the table definitions in the first place. Moreover, the default mapping of String is the SQL VARCHAR type.

You seem to have configured the JPA provider to create tables as needed (after possibly dropping them), during the initialization process. If you're using Hibernate, this is done using the hibernate.hbm2ddl.auto property specified in persistence.xml with a value of update, create or create-drop. With EclipseLink, you would be specifying the property eclipselink.ddl-generation with a value of create-tables or drop-and-create-tables.

Both of the above properties are not recommended for use in a production environment. The ideal approach is to have DDL scripts to create the tables. Since, you are using VARCHAR, you ought to specify a suitable length in the column definition, to fit the maximum length of the user input. Additionally, since you are using VARCHAR over CHAR, the database engine will ensure that the storage space allocated will depend on the size of the records being stored.

If you do not need a String to the default VARCHAR mapping and instead use another valid mapping, then you must use columnDefinition attribute of the @Column annotation. An example usage for mapping Calendar to the TIMESTAMPTZ SQL datatype is shown in the JPA WikiBook; you'll need to modify this to suit your need.