How to increase string length in mysql when matching using JPA

Question

I have problems with JPA. I would be very grateful if someone could provide a solution. WIth JPA (I use MySQL DB), let's say I have a class displayed as follows:

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

when matching with a table, I see that String maps to varchar(255) in Mysql. However, let's say if I have an employee with a name of more than 255 characters, it shows a data truncation error.

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

 @column(length=1000) String employeeName; 

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

Answer 1

when matching with a table, I see that String maps to varchar (255) in Mysql

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

I thought that if we just map to String in java, the database will dynamically assign lengths.

This is a false assumption. The JPA provider will create tables only once and will not dynamically change the length of the base table throughout the application life cycle, and only if you configure the provider to create / update table definitions in the first place. Moreover, the default String mapping is the SQL VARCHAR type.

You seem to have configured the JPA provider to create tables as needed (after they have been reset) during the initialization process. If you use Hibernate, this is done using the hibernate.hbm2ddl.auto property specified in persistence.xml with the value update , create or create-drop . With EclipseLink, you must specify the eclipselink.ddl-generation property with a value of create-tables or drop-and-create-tables .

Both of these properties are not recommended for use in a production environment . The ideal approach is to create DDL scripts to create tables. Since you are using VARCHAR , you must specify the appropriate length in the column definition to match the maximum length of the user input. In addition, since you use VARCHAR over CHAR , the database engine ensures that the allocated storage space will depend on the size of the stored records.

If you do not need String for the default VARCHAR collation and use a different valid collation instead, you should use the columnDefinition attribute of the columnDefinition annotation. An example of using Calendar to match the TIMESTAMPTZ SQL data type is shown in the JPA WikiBook ; You will need to change this to suit your needs.

Answer 2

varchar columns support dynamic size lengths (no padding such as char), but you must specify a maximum column size if you want something other than 255. Different dbs have different char limits and varchar size (8k or 65k is common).

If you want to go beyond this limit, you need to add the @Lob annotation to you so that it maps to the CLOB column type (assuming you let JPA generate tables). CLOB / BLOB columns can be much larger than varchar (again, the exact limits depend on your database). But LOBs have restrictions such as non-primary keys or used in WHERE clauses.