4

Similar to here I'm annotating my class with 

@Table(indexes = {@Index(columnList = "name")})

which attempts to create a non-unique index with the maximum length of the varchar column. Unfortunately that's not possible because it's a varchar(255) column of type utf8mb4. phpMyAdmin added KEY '...' (name(191)) by clicking on the respective buttons in the UI, so at least my software runs efficient queries now.

Now I was wondering if it's possible to have my Java class auto-generate the index with limited length upon creating the database schema? The code builds on spring-boot-starter-data-jpa:1.4.2.RELEASE.

Community
  • 1
  • 1
oschlueter
  • 2,596
  • 1
  • 23
  • 46
  • 1
    That is clearly database specific feature, and thus should not be covered by JPA (database agnostic) tool. As for creating such indexes, and tracking overall database structure, I would recommend to use liquibase, which is already integrated in spring-boot. – Ilya Dyoshin Feb 28 '17 at 12:18
  • Is there a way to include vendor-specifics similar to native queries? – oschlueter Feb 28 '17 at 13:40
  • 1
    hibernate provides a possibility to run arbitrary sql statements on startup. you can introduce custom code in `import.sql` file (default location). commands from this file will be executed every time the hibernate bootstraps. Thus you should consider declaring statements like 'create index if not exists' – Ilya Dyoshin Feb 28 '17 at 13:47

2 Answers2

2

There are other answers than trying to get the 3rd party software to do something it may or may not allow for.

  • Live with 191 limitation on the column size. Or, do you really have a max between 191 and 255.
  • Change to utf8 (from utf8mb4). And lose the ability to store Emoji and some Chinese characters.
  • There is a clumsy process in 5.6 to raise the 767 limit you are bumping into.
  • Upgrade to 5.7, which makes virtually eliminates the problem.
Rick James
  • 135,179
  • 13
  • 127
  • 222
1

You should only use the JPA generated table scripts as a starting point, and you should never use JPA to create you tables in production.

If you have "create table" privileges, so you don't need a DBA to create and modify the database, then I recommend that you use Flyway to manage database creation and migration. If you need to be database agnostic, and like long XML files, you can also use LiquideBase.

With flyway, you would add a new script every time you add one or more entities. I typically let JPA create the script, and then copy what I need, and maybe do some modifications - for instance varchar(255) means 255 bytes on some databases, so you may want modify that if you are storing something other than Latin-1.

Flyway is very simple to use, and it is fully integrated into Spring boot, so you just add the unique index the way you want it in the first (or later) flyway script src/main/resources/db/migration/V1__initial_script.sql.

Klaus Groenbaek
  • 4,820
  • 2
  • 15
  • 30