CODEWITHSUNDEEP
javamysqljpaorm
Ashok
Ashok

Reputation: 1952

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

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.

Upvotes: 11

Views: 26354

Answers (2)

Chase
Chase

Reputation: 3183

varchar columns support lengths of dynamic size (no padding like with char) but you have to specify the max column size if you want something besides 255. Different dbs have different limits on char and varchar size (8k or 65k is common).

If you want to go beyond this limit you need to add a @Lob annotation to you string so that it'll map to a CLOB column type (assuming you are letting JPA generate tables). CLOB/BLOB columns can be much larger than varchar (again, exact limits depend on your DB). But LOBs have limitations such as not being primary keys or used in WHERE clauses.

Upvotes: 7

Vineet Reynolds
Vineet Reynolds

Reputation: 76719

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.

Upvotes: 16

Related Questions