3

I am using hibernate and java to create and manage relational database MySQL. Now I am stuck in the situation where tables has been created earlier and these table are containing data too. My current requirement is that I need to increase size of column. Earlier I have given size 50 for java.lang.String type but now I need to increase and make it 80. Is there any way in hibernate by which I can do this ? I can't use hibernate.hbm2ddl.auto=create because it will truncate entire data in table and update it directly from database level is not feasible in my case. I don't think Need clarity on hibernate.hbm2ddl.auto=update is going to help me in this case. Please suggest if there is any way in hibernate by which I can do this. Thanks in advance..

Jain
  • 65
  • 2
  • 8
  • 2
    Hibernate is not the good tool to deal with schema and data migration. Use Flyway or Liquibase. Whatever your solution, you should test it on a copy of your production database before applying it, to avoid a disaster. – JB Nizet May 08 '14 at 05:32
  • @JBNizet... I agree with you. But currently its not in my hand to change ORM. But is there any way or workaround in hibernate ? – Jain May 08 '14 at 05:34
  • Flyway and Liquibase are database migration tools. Hibernate is an ORM. You shouldn't change your ORM. Use an ORM for object relational mapping, and a database migration tool to migrate your database. – JB Nizet May 08 '14 at 05:45

2 Answers2

4

Set hiberate.hbm2ddl.auto=validate and manually change the size of the column on the actual database yourself using MySQLWorkbench or any similar tool. Also see here for an explanation on what the different values do. This is if you actually want to keep the data in the database persisted. I'm pretty sure that using update will still drop the entire table to replace the column length so I wouldn't use that. Using validate will cause Hibernate to validate your database schema against your annotations to ensure that your database is set up correctly. Never ever ever ever use anything other than validate for production code.

EDIT: To actually answer the question, no Hibernate cannot do this without dropping and re-creating the table, which will cause any data in there to be lost.

Community
  • 1
  • 1
JamesENL
  • 6,400
  • 6
  • 39
  • 64
  • 2
    The [default value for hiberbate.hbm2ddl.auto is validate](http://stackoverflow.com/questions/438146/hibernate-hbm2ddl-auto-possible-values-and-what-they-do) or better yet you could set it to none. – hd1 May 08 '14 at 05:36
  • @James... I've tried UPDATE. But it doesn't even affect any thing where data loss can happen. In short UPDATE is doing nothing in my case – Jain May 08 '14 at 05:39
  • Have you set the max column size in the `@Column(length = 255)` annotation? – JamesENL May 08 '14 at 05:43
  • @JamesMassey... No I have set it's length to 50 in hbm.xml file. – Jain May 08 '14 at 05:47
  • Actually, I think that the length of columns only applies when you are creating the database or validating the columns. It won't actually update the length because it would have to drop the entire table – JamesENL May 08 '14 at 05:54
  • That's why you need to do it manually using MySQLWorkbench or something. – JamesENL May 08 '14 at 06:02
  • @JamesMassey... Yeah . I mean doing it manually doesn't have any obstacle. But eventually came to know that hibernate can't help in this situation. – Jain May 08 '14 at 07:16
  • 2
    Sorry, I should have been more specific in my answer. It still confuses me as to what annotations (read mapping file entries) actually do. Basically all your column definitions don't do anything if you aren't using Hibernate to create your schema. It does no validation around column sizes, uniqueness etc. It only validates relationships and column names etc – JamesENL May 08 '14 at 08:22
0

Worst comes to worst, you can use a Work unit as follows:

sessionObject.doWork(new Work() {
    public void execute(Connection c) throws SQLException {
       c.setAutoCommit(false);
       ResultSet tblContent = c.createStatement("select * from tbl", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
       // assume col1 is a final variable indicating the column name you want to trim
       while (tblContent.isLast() == false) {
           tblContent.updateString(col1, tblContent.getString(col1).substring(0, 49)); // or whatever
           tblContent.updateRow();
           tblContent.next();
       }
       c.commit();
    }
}

Hope it helps...

hd1
  • 33,938
  • 5
  • 80
  • 91