[FIXED] Error creating table in MSSQL server in Microsoft Azure: Spring Boot and Hibernate

Issue

I have created a Spring Boot Web Application with Hibernate. I am using Microsoft Azure and SQL Database to deploy my application. I have configured the database on Azure and in my code. Configuration code is-
1. application.properties file

#to  automatically create/update tables for any entities
spring.jpa.hibernate.ddl-auto=update
#to show the table operation query in the console
spring.jpa.show-sql=true

#change port
server.port=8085

#File related all configurations
spring.servlet.multipart.max-file-size=10MB
spring.servlet.multipart.max-request-size=10MB

project.image=images/

#get all the debugging logs for spring security
logging.level.org.springframework.security=DEBUG

2. application.yml file

spring:
  datasource:
    url: jdbc:sqlserver:<url>:1433;database=blogging-application-db;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;
    username: <username>
    password: <password>
  jpa:
    show-sql: true
    hibernate:
      ddl-auto: update
    properties:
      dialect : org.hibernate.dialect.SQLServer2012Dialect
    #   hibernate:
    #     '[globally_quoted_identifiers: true]'
    #   properties:
    #     dialect : org.hibernate.dialect.SQLServer2012Dialect

server:
  port: 8085

I am creating several tables in my database.

All the tables are getting created, but one that needs to be created automatically for a many to many relationship between user and role is not getting created. The table is named as user_role. I am getting the following errors for it-

Hibernate: create table user_role (user int not null, role int not null, primary key (user, role))  
2022-10-29 22:22:39.192  WARN 18348 --- [  restartedMain] o.h.t.s.i.ExceptionHandlerLoggedImpl     : 
GenerationTarget encountered exception accepting command : 
Error executing DDL "create table user_role (user int not null, role int not null, primary key (user, role))" via JDBC Statement

and

Hibernate: alter table user_role add constraint FKlduspqw8rg0gbcpludbfadw6l foreign key (user) references users  
2022-10-29 22:22:39.852  WARN 18348 --- [  restartedMain] o.h.t.s.i.ExceptionHandlerLoggedImpl     : 
GenerationTarget encountered exception accepting the command : 
Error executing DDL "alter table user_role add constraint FKlduspqw8rg0gbcpludbfadw6l foreign key (user) references users" via JDBC Statement

and

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'user'.

I am not able to figure out what is the problem in creating the user_role table which should be created automatically by the hibernate. Also, the code was working perfectly when I was using MySQL with localhost. Now, when I am trying to use Microsoft Azure and MSSQL database, I get the error. Please help me to figure this out!

Solution

• you can create these table using entities class too where you can create a join table using the many- to-many annotation.

• The many-to-many annotation will help in creating a new table though join where you can pick and choose which columns will part of the new table.

package com.example.demo;  

 import javax.persistence.*;  
 import java.util.HashSet;  
 import java.util.Set;  
  
@Entity  
@Table(name="t1")  
public class t1 {  
    @Id  
 @GeneratedValue(strategy = GenerationType.AUTO)  
    @Column(name = "id", nullable = false)  
    private Long id;  
  
 public Long getId() {  
        return id;  
  }  
  
    public void setId(Long id) {  
        this.id = id;  
  }  
  
    @Column(name = "name")  
    private String name ;  
  
  @ManyToMany(cascade = { CascadeType.ALL })  
    @JoinTable(  
            name = "t3",  
  joinColumns = { @JoinColumn(name = "id") },  
  inverseJoinColumns = { @JoinColumn(name = "newid") }  
    )  
    Set<t2> t1 = new HashSet<>();   
}

• Here the many-to-many will connect the two table and create a new table called t3 . Here we are also giving a reference of the table t2 as set.

package com.example.demo;  
  
import javax.persistence.*;  
import java.util.HashSet;  
import java.util.Set;  
  
@Entity  
@Table(name="t2")  
public class t2 {  
    @Id  
 @GeneratedValue(strategy = GenerationType.AUTO)  
    @Column(name = "newid", nullable = false)  
    private Long newid;  
  
 public Long getNewid() {  
        return newid;  
  }  
  
    public void setNewid(Long newid) {  
        this.newid = newid;  
  }  
  
    @Column(name = "newname")  
    private String newname ;  
  
  @ManyToMany(mappedBy = "t1")  
    Set<t1> t2 = new HashSet<>();  
}

• Similar to t1 here we are also mapping t2 to t1 and giving a reference of t1 as a set .

dependency (pom.xml):

<dependency>  
 <groupId>org.springframework.boot</groupId>  
 <artifactId>spring-boot-starter-data-jpa</artifactId>  
</dependency>  
<dependency>  
 <groupId>org.springframework.boot</groupId>  
 <artifactId>spring-boot-starter-web</artifactId>  
</dependency>  
  
<dependency>  
 <groupId>org.springframework.boot</groupId>  
 <artifactId>spring-boot-starter-test</artifactId>  
 <scope>test</scope>  
</dependency>

Now run you project, and it will create the tables.

Refer this article Zeger Hendrikse on this

Answered By - MohitGanorkar
Answer Checked By - Willingham (JavaFixing Volunteer)